Slide 13.2: Basic structure of PL/SQL

Basic Structure of PL/SQL

Question: Write a PL/SQL block to add up the integers from 1 to 100 and print the result.

 SQL> SET SERVEROUTPUT ON;
 SQL> DECLARE
   2    i      INTEGER;
   3    total  INTEGER := 0;
   4  BEGIN
   5    FOR  i  IN  1..100  LOOP
   6      total := total + i;
   7    END LOOP;
   8    dbms_output.put_line( total );
   9  END;
  10  /
 5050
 PL/SQL procedure successfully completed.

 SQL> @1.sql
 5050
 PL/SQL procedure successfully completed.

A PL/SQL program can be invoked either by typing it in sqlplus or by putting the code in a file and invoking the file; e.g., the file 1.sql stores the PL/SQL program as above. Comments about the basic structure:

The only SQL statements allowed in a PL/SQL program are SELECT, INSERT, UPDATE, DELETE and several other data manipulation statements plus some transaction control.

Data definition statements like CREATE, DROP, or ALTER are not allowed.

The executable section also contains constructs such as assignments, branches, loops, procedure calls, and triggers.

Assign values to variables by using the “:=” operator. The assignment can occur either immediately after the variable type is declared, or anywhere in the executable portion of the program.

PL/SQL is not case sensitive.

C style comments (/* ... */) may be used.