PL/SQL programs are divided up into structures known as blocks, with each block containing PL/SQL and SQL statements. A typical PL/SQL block has the following structure:
[DECLARE declaration_statements ] BEGIN executable_statements [EXCEPTION exception_handling_statements ] END;
The declaration and exception blocks are optional, and the elements for the above syntax are as follows :
declaration_statements are contained within a declaration block, and declare the variables subsequently used in the rest of the block. These variables are local to that block, meaning that they cannot be referenced outside of that block. Declarations are always placed at the start of the block.
executable_statements are the actual executable statements for the block, which may include statements for performing tasks such as loops , conditional logic, and so on.
exception_handling_statements are statements that handle any errors that might occur due to the executable statements.
Every statement is terminated by a semicolon (;), and a block is terminated using the END keyword. Before I get into the details of PL/SQL, you ll see a simple example to get a feel of the language. The following PL/SQL example calculates the width of a rectangle given its area and height:
DECLARE width INTEGER; height INTEGER := 2; area INTEGER; BEGIN area := 6; width := area / height; DBMS_OUTPUT.PUT_LINE('width = ' width); EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('Division by zero'); END; /
As you can see, three INTEGER variables are declared: width, height, and area . The height variable is initialized to 2 when it is declared, and area is set to 6 later. The width variable is calculated by dividing area by height . The call to the DBMS_OUTPUT.PUT_LINE() method displays the value set for the area variable on the screen (you must enter the command SET SERVEROUTPUT ON in SQL*Plus to see the output). The EXCEPTION block handles any attempts to divide a number by zero by catching the ZERO_DIVIDE exception. In the example, no attempt is actually made to divide by zero, but if you change height to 0 and run the code you ll see the exception.
Note | The slash character (/) at the end of the example executes the PL/SQL. |
The following listing shows the block being entered and run in SQL*Plus:
SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 width INTEGER; 3 height INTEGER := 2; 4 area INTEGER; 5 BEGIN 6 area := 6; 7 width := area / height; 8 DBMS_OUTPUT.PUT_LINE('width = ' width); 9 EXCEPTION 10 WHEN ZERO_DIVIDE THEN 11 DBMS_OUTPUT.PUT_LINE('Division by zero'); 12 END; 13 / width = 3 PL/SQL procedure successfully completed.
Notice the slash character at line 13 runs the PL/SQL.