Executing PL/SQL blocks

PL/SQL is Oracle's procedural extension to the SQL language. PL/SQL processes tasks that are more complicated than simple queries and SQL Data manipulation language statements. Without PL/SQL, Oracle would have to process SQL statements one at a time. Each SQL statement results in another call to Oracle and consequently higher performance overhead. In a networked environment, the overhead can become significant. Every time a SQL statement

is issued, it must be sent over the network, creating more traffic. However, with PL/SQL, an entire block of statements can be sent to Oracle at one time. This can greatly reduce communication between an application and Oracle.

PL/SQL allows a number of constructs to be grouped into a single block and executed as a unit. These include:

· One or more SQL statements

· Variable declarations

· Assignment statements

· Procedural control statements (IF...THEN...ELSE statements and loops)

· Exception handling

· PL/SQL blocks to call Oracle stored procedures and stored functions

· Combined procedural control statements and SQL statements to be executed as a single unit

· Special PL/SQL features such as records, tables, cursor FOR loops, and exception handling

· Statements to access and manipulate objects in an Oracle server

· Cursor variables

See the following:

· Using ExecuteSQL and CreateSQL to Execute PL/SQL Blocks

· Returning PL/SQL Tables

· Returning PL/SQL Cursor Variables