PL/SQL

PL/SQL stands for procedural language extensions to SQL. It is available as an enabling technology within other Software Products; It does not exists as a stand- alone language. With PL/SQL, we can use SQL statements to manipulate oracle data and flow of control statements to process the data. Also we can declare constants and variables defined in subprograms and trap runtime errors. Thus PL/SQL combines the data manipulating power of SQL with the data processing power of procedural languages.

Why use PL/SQL?

PL/SQL is a complete transaction processing language that provides the following advantages. 1. It acts as a support for SQL. Since it supports SQL data manipulation statement, SQL transaction processing statement, SQL functions and SQL predicates we can access the ORACLE database and manipulate its data flexibly and easily.

2. PL/SQL allows to declare variables and constants and use them in SQL and procedural statements,

any where an expression can be used. PL/SQL variables and constants have attributes (%Type, %Rowtype) by which we can reference the data type and structure of an object with out repeating its definition.

3. Control structures are the most important PL/SQL extension of SQL. It allows you to process the data using the conditional control statement (IF-THEN-ELSE), iterative control statement (LOOP END LOOP, FOR-MOP and WHILE-LOOP) and sequential control statement (GOTO), with the help of these statements we can handle any situation.

4. A PL/SQL cursor gives you a way to fetch and process database information, There are two types of cursors EXPLICIT and IMPLICIT CURSORS.

PL/SQL declares cursor implicitly for all SQL manipulation statements including queries that returns one row. FOR queries that returns more than one row an explicit cursor has to be declared.

5. Modularity is promoted because PL/SQL lets you break an application down into manageable, well-defined logic modules.

6. Errors are easily detected and handled.

How to use PL/SQL?

Without PL/SQL, the Oracle server must process each SQL statement individually. Each statement results in high performance overhead due to the additional call for each statement. With PL/SQL, an entire block of statements can be sent to Oracle at one time. This reduces the network traffic between your application and the Oracle server.

Domain Name Search