Block Structure


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.




Oracle Database 10g SQL
Oracle Database 10g SQL (Osborne ORACLE Press Series)
ISBN: 0072229810
EAN: 2147483647
Year: 2004
Pages: 217

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net