• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Regarding pl/sql

 
Ranch Hand
Posts: 30
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
sudharani Savadi
Ranch Hand
Posts: 30
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you but you did not tell clearly, I asked an example about that...
 
Ranch Hand
Posts: 256
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 30
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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!
 
sudharani Savadi
Ranch Hand
Posts: 30
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
If I'd had more time, I would have written a shorter letter. -T.S. Eliot such a short, tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic