aspose file tools*
The moose likes JDBC and the fly likes Regarding pl/sql Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Regarding pl/sql" Watch "Regarding pl/sql" New topic
Author

Regarding pl/sql

sudharani Savadi
Ranch Hand

Joined: Feb 23, 2011
Posts: 30

Can anybody tell me the advantages of PL/Sql with real life example and where exactly we can use it?. I am learning it by myself so please help me to get know more about it.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30764
    
156

Pl/SQL is a language for writing stored procedures. You would use it when you want to do processing in the database itself. For example when you would need to return too much data to calculate something.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
sudharani Savadi
Ranch Hand

Joined: Feb 23, 2011
Posts: 30

Thank you but you did not tell clearly, I asked an example about that...
bhanu chowdary
Ranch Hand

Joined: Mar 09, 2010
Posts: 256
sudharani Savadi wrote:Thank you but you did not tell clearly, I asked an example about that...


Jeanne has given an example
For example when you would need to return too much data to calculate something


what are you expecting?? A pl/sql block?
sudharani Savadi
Ranch Hand

Joined: Feb 23, 2011
Posts: 30

No, I am not expecting any block of pl/sql code and I know how to write that. I could not understand its usage i mean when and where exactly we can use this?
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1775
    
  16

Jeanne's reply is correct - it is for doing procedural logic in the database - but this simple description does not really tell you much.

PL/SQL has been around for about 20 years as a standard part of the Oracle RDBMS (it is also available on PostgreSQL these days). Before that, Oracle developers often had to use procedural languages outside the DB to implement complex data-oriented functionality, usually combining a 3GL like C or COBOL with chunks of SQL (a bit like basic Java/JDBC and SQL). This meant a lot of context-switching and traffic between the external process and the database, extra pre-compilation steps, complicated coding, and a general tendency to fall over in a smoking heap as soon as you changed anything. Great for people who like hunting for pointer errors, but not so much fun if you're trying to get an Oracle application built.

PL/SQL allowed Oracle developers to implement all of this complex logic in one place instead, using a uniform language that increasingly provided a lot of useful features - clean integration of SQL and procedural logic, functions/procedures/packages, table triggers, robust exception handling, anchored data types (a sort of semi-dynamic typing where you tell your variable it should use the type from a particular DB column as its data-type, so you don't have to change all the variable declarations if the column length changes for example), performance improvements based on optimisations within the DB engine, an ever-expanding set of package libraries, and so on. This allowed developers to encapsulate much of the complexity of their systems in the database behind relatively clean interfaces, compared to the complicated spaghetti of Pro*C in the bad old days. Many of these facilities exist on other DBs as well, and most enterprise DBs now have some kind of procedural SQL extension similar to PL/SQL, because it's really useful.

Today PL/SQL is massively powerful, heavily optimised and immensely flexible, and you can implement pretty much anything you want using PL/SQL within the database - business logic, data-tier logic, even presentation logic to a large extent. And using Oracle's PL/SQL based Application Express tool (free on all Oracle RDBMS platforms) you can even implement fully functional data-oriented web applications very easily.

Of course, different people have different ideas about when/where they want to use PL/SQL. If your system treats the RDBMS as basically a big dumb passive data-store, and you want to keep all your logic in the Java tiers and just read/write data via an ORM, then you probably won't have much use for PL/SQL. Or if you want to be able to switch DB platforms easily, the obviously you won't want to invest too much effort in Oracle-specific tools like PL/SQL.

But if you are committed to the Oracle DB platform and want to make the best use of the tools it provides, if you have a lot of data-intensive processing to do, if you want to avoid moving lots of data back and forth to the JEE server so your Java business logic can process it, or if you want to encapsulate your complex DB operations behind a simple set of services/interfaces, then you might seriously want to think about using PL/SQL to implement much of your data-oriented logic.

So good for you for taking the trouble to learn something about this powerful additional toolset for Oracle database application development!


No more Blub for me, thank you, Vicar.
sudharani Savadi
Ranch Hand

Joined: Feb 23, 2011
Posts: 30

Thank you very much for clearing my doubts very nice.. whenever I get doubts, i will post so do reply for that. Have a wonderful day.
 
 
subject: Regarding pl/sql