Engineering Full Stack Apps with Java and JavaScript
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).[1] Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database.
PL/SQL programs consist of declaration statements (after DECLARE), executable statements (after BEGIN) and exception handling statements (after EXCEPTION) as in:
DECLARE
var1 INTEGER := 5;
var2 INTEGER := 0;
resultVar INTEGER;
BEGIN
resultVar := var1 / var2;
DBMS_OUTPUT.PUT_LINE('result = ' || resultVar);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Division by zero not allowed.');
END;
/
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.
Conditional logic
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';
END IF;
ELSIF var1 = 0 THEN
resultVar := 'var1 is zero';
ELSE
resultVar := 'var1 is negative';
END IF;
LOOP
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:
LOOP
IF var1 > 5 THEN
EXIT;
END IF;
var1 := var1 + 1;
EXIT WHEN var1 = 5;
END LOOP;
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;
END LOOP;
A FOR loop will also do the initialization and increment of variable in forward or reverse order:
FOR var1 IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE(var1);
END LOOP;
Unlike WHILE, you don’t have to declare var1 here.
Next we will see cursors, procedures, functions and packages.