Re: PlSql usage

On Jul 26, 6:18 am, kkrish <clearminded.kkr..._at_gmail.com> wrote:
> Hi all,
> I have some knowledge in PlSql, but I do not know where exactly this
> becomes handy.Where to use PlSql ? I have used triggers to
> automatically update some tables if some other tables are updated by a
> user.But I do not knoww where are the other concepts used.If anybody
> can point some real life examples , i will be helpful.
> Thanks in advance.

Probably the best use of PL/SQL is through stored functions, procedures, and packages is to encapsulate a unit of work, that is, all necessary SQL statements that perform a logical business function into one piece of code so that it can be executed as a unit. This helps prevent programming errors and to ensure the validity of the data.

In general you resort to using PL/SQL only where you cannot perform the target task as a single SQL statement (or series of simple SQL statements), you resort to using PL/SQL where the liklihood that one of more rows in a set operation will fail is high, but you need to process all passing rows (this is you need to handle error conditions), you need to use Oracle object types though with the newest edition you have much more flexibility to use objects in standard SQL statements than in the past, and where you have complex logic that cannot be efficiently processed as a single SQL statement (or simple series of SQL statements).

PL/SQL is often used for administrative tasks such as generating object statistics, managing the database resources, and obtaining system information via Oracle since Oracle provides packages that provide these services. Cover packages and routines can be written to provide tailored operations.

PL/SQL is also often used where data external to the database has to be read or written though the external table feature and sqlplus spool can often handle these requirements.

Tom Kyte has a couple of excellent books on advanced data manipulation tasks using PL/SQL such as displaying query results one column per line, concatentating rows into columns, working with LOB segments, and so on. You may want to consider purchasing one of his books.

HTH -- Mark D Powell -- Received on Thu Jul 26 2007 - 08:36:44 CDT