PL/SQL: Overview

This is from http://www.comp.nus.edu.sg/~ooibc/courses/sql/index.htm


PL/SQL is the Oracle's extension to SQL with design features of programming languages. The data manipulation and query statements are included in the procedural units of codes. PL/SQL allows the applications to be written in a PL/SQL procedure or a package and stored at Oracle server, where these PL/SQL codes can be used as shared libraries, or applications, thus enhancing the integration and code reuse. Moreover, the Oracle  server pre-compiles PL/SQL codes prior to the actual code execution and thus improving the performance.
The basic PL/SQL code structure is :

• DECLARE -- optional, which declares and define variables, cursors and user-defined exceptions.
• BEGIN -- mandatory

- SQL statements
- PL/SQL statements

• EXCEPTION -- optional, which specifies what actions to take when error occurs.
• END; -- mandatory

For example, the following PL/SQL code block declares an integer v1, assigns it with value 3 and print out the value.

DECLARE
v1  NUMBER(3);

BEGIN
   v1 := 3;
   DBMS_OUTPUT.PUT_LINE('v1=' || v1);
END;

Note that DBMS_OUTPUT is an Oracle-supplied PL/SQL package and PUT_LINE is one of the packaged procedures. It displays the values on the SQL*Plus terminal  which must be enabled with SET SERVEROUTPUT ON first. To execute this code sample, login into SQL*Plus, and type

>SQL SET SERVEROUTPUT ON
>DECLARE
v1  NUMBER(3);

BEGIN
   v1 := 3;
   DBMS_OUTPUT.PUT_LINE('v1= ' || v1);
END;
/

Note that a PL/SQL block is terminated by a slash / or a line byitself.