PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation’s procedural language extension for SQL and the Oracle relational database.
PL/SQL is available in Oracle Database (since version 7), TimesTen in-memory database (since version 11.2.1), and IBM DB2 (since version 9.7).
Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database.
BASIC STRUCTURE OF PL/SQL
PL/SQL programs consist of declaration statements (after DECLARE), executable statements (after BEGIN) and exception handling statements (after EXCEPTION) as in:
var1 INTEGER := 5;
var2 INTEGER := 0;
resultVar := var1 / var2;
DBMS_OUTPUT.PUT_LINE(‘result = ‘ || resultVar);
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE(‘Division by zero not allowed.’);
The forward slash character (/) at the end marks the end of the PL/SQL block, and is needed if you are executing in SQL Plus, you should not use it while executing in clients such as DBVisualizer.
DECLARE and EXCEPTION blocks are optional.
DBMS_OUTPUT package is a built-in package that contains procedures that allow you to output values to the screen. DBMS_OUTPUT is useful when working with SQL PLUS.
BEGIN and EXCEPTION blocks can contain executable code, which include conditional logic and loops.
You can use the IF, THEN, ELSE, ELSIF, and END IF keywords to perform conditional logic as in:
IF var1 > 0 THEN
resultVar := ‘var1 is positive’;
IF var2 > 0 THEN
resultVar := ‘var1 and var2 are positive’;
ELSIF var1 = 0 THEN
resultVar := ‘var1 is zero’;
resultVar := ‘var1 is negative’;
You can use simple LOOPs, WHILE loops or FOR loops to execute code iteratively.
Simple loops have to be explicitly stopped using EXIT or an EXIT WHEN statement as in:
IF var1 > 5 THEN
var1 := var1 + 1;
EXIT WHEN var1 = 5;
Similar to EXIT or an EXIT WHEN, you can use CONTINUE and CONTINUE WHEN to skip the remainder of current iteration and move to the next iteration. This feature is available from 11g.
Unlike a simple LOOP, a WHILE loop can specify a condition in its definition and it will run as long as the condition is true:
WHILE var1 < 6 LOOP
var1 := var1 + 1;
A FOR loop will also do the initialization and increment of variable in forward or reverse order:
FOR var1 IN 1..5 LOOP
Unlike WHILE, you don’t have to declare var1 here.
Next we will see cursors, procedures, functions and packages