6

what all can be done in PL SQL can also be done by embedding sql statements in an application langauage say PhP. Why do people still use PL SQL , Are there any major advantages.?

I want to avoid learning a new language and see if PHP can suffice.

codaddict
  • 445,704
  • 82
  • 492
  • 529
TopCoder
  • 4,206
  • 19
  • 52
  • 64
  • I'd say it belongs in programmers. – Madara's Ghost Sep 22 '11 at 17:12
  • 1
    You may also say that all that can be done using any RDMS, can also be done using flat text files. – a1ex07 Sep 22 '11 at 17:18
  • 2
    "I want to avoid learning a new language" - Why? It's one of the better/more useful things you can do, both to improve as a programmer and to aid your career. Plus, chances are it's nowhere as hard as the first language learnt. –  Sep 22 '11 at 17:19
  • I can see performance as one definite advantage. – codaddict Sep 22 '11 at 17:21
  • I am looking for one solid example which will encourage me to learn PL SQL ..which applications use PL SQL and why ? any obvious advantages which I am unable to understand.. – TopCoder Sep 22 '11 at 17:22
  • 1
    This question is *really* just "Why use Stored Procedures?" - PL/SQL is just the implementation used in Oracle. –  Sep 22 '11 at 17:24
  • 1
    possible duplicate of [What are the pros and cons to keeping SQL in Stored Procs versus Code](http://stackoverflow.com/questions/15142/what-are-the-pros-and-cons-to-keeping-sql-in-stored-procs-versus-code) –  Sep 22 '11 at 17:25
  • See also http://stackoverflow.com/questions/1199886/stored-procedures-vs-no-stored-procedures-security-viewpoint –  Sep 22 '11 at 17:25

3 Answers3

17

PL/SQL is useful when you have the opportunity to process large chunks of data on the database side with out having to load all that data in your application.

Lets say you are running complex reports on millions of rows of data. You can simply implement the logic in pl/sql and not have to load all that data to your application and then write the results back to the DB - saves bandwidth, mem and time.

It's a matter of being the right tool for the right job.
It's up to the developer to decide when is a best time to use PL/SQL.

LoudNPossiblyWrong
  • 3,855
  • 7
  • 33
  • 45
9

In addition to performing bulk operations on the DB end, certain IT setups have stringent security measures.

Instead of allowing applications to have direct access to tables, they control access through PL/SQL stored procedures. This way they know exactly how the data is being accessed instead of applications maintained by developers which may be subject to security attacks.

Jordan Parmer
  • 36,042
  • 30
  • 97
  • 119
4

I suppose advantages would include:

Tight integration with the database - Performance.

Security

Reduced network traffic

Pre-compiled (and natively compiled) code

Ability to create table triggers

Integration with SQL (less datatype conversion etc)

In the end though every approach and language will have its own advantages and disadvantages. Not learning PL/SQL just because you already know PHP would be a loss to yourself both personally and possibly career-wise. If you learn PL/SQL than you will understand where it has advantages over PHP and where PHP has advantages over PL/SQL but you will be in a better position to make the judgement.

Best of luck.

Ollie
  • 17,058
  • 7
  • 48
  • 59