Note: The material on triggers that was formerly in this document has been moved to A New Document on Constraints and Triggers.
DECLARE /* Declarative section: variables, types, and local subprograms. */ BEGIN /* Executable section: procedural and SQL statements go here. */ /* This is the only section of the block that is required. */ EXCEPTION /* Exception handling section: error handling statements go here. */ END;Only the executable section is required. The other sections are optional. 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. However, the SELECT statement has a special form in which a single tuple is placed in variables; more on this later. 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, which are all described below (except triggers). PL/SQL is not case sensitive. C style comments (/* ... */) may be used.
To execute a PL/SQL program, we must follow the program text itself by
DECLARE price NUMBER; myBeer VARCHAR(20);Note that PL/SQL allows BOOLEAN variables, even though Oracle does not support BOOLEAN as a type for database columns.
Types in PL/SQL can be tricky. In many cases, a PL/SQL variable will be used to manipulate data stored in a existing relation. In this case, it is essential that the variable have the same type as the relation column. If there is any type mismatch, variable assignments and comparisons may not work the way you expect. To be safe, instead of hard coding the type of a variable, you should use the %TYPE operator. For example:
DECLARE myBeer Beers.name%TYPE;gives PL/SQL variable myBeer whatever type was declared for the name column in relation Beers.
A variable may also have a type that is a record with several fields. The simplest way to declare such a variable is to use %ROWTYPE on a relation name. The result is a record type in which the fields have the same names and types as the attributes of the relation. For instance:
DECLARE beerTuple Beers%ROWTYPE;makes variable beerTuple be a record with fields name and manufacture, assuming that the relation has the schema Beers(name, manufacture).
The initial value of any variable, regardless of its type, is NULL. We can assign values to variables, using the ":=" operator. The assignment can occur either immediately after the type of the variable is declared, or anywhere in the executable portion of the program. An example:
DECLARE a NUMBER := 3; BEGIN a := a + 1; END; . run;This program has no effect when run, because there are no changes to the database.
Notice we said "tuple" rather than "tuples", since the SELECT statement in PL/SQL only works if the result of the query contains a single tuple. The situation is essentially the same as that of the "single-row select" discussed in Section 7.1.5 of the text, in connection with embedded SQL. If the query returns more than one tuple, you need to use a cursor, as described in the next section. Here is an example:
CREATE TABLE T1( e INTEGER, f INTEGER ); DELETE FROM T1; INSERT INTO T1 VALUES(1, 3); INSERT INTO T1 VALUES(2, 4); /* Above is plain SQL; below is the PL/SQL program. */ DECLARE a NUMBER; b NUMBER; BEGIN SELECT e,f INTO a,b FROM T1 WHERE e>1; INSERT INTO T1 VALUES(b,a); END; . run;Fortuitously, there is only one tuple of T1 that has first component greater than 1, namely (2,4). The INSERT statement thus inserts (4,2) into T1.
An IF statement looks like:
IF <condition> THEN <statement_list> ELSE <statement_list> END IF;The ELSE part is optional. If you want a multiway branch, use:
IF <condition_1> THEN ... ELSIF <condition_2> THEN ... ... ... ELSIF <condition_n> THEN ... ELSE ... END IF;The following is an example, slightly modified from the previous one, where now we only do the insertion if the second component is 1. If not, we first add 10 to each component and then insert:
DECLARE a NUMBER; b NUMBER; BEGIN SELECT e,f INTO a,b FROM T1 WHERE e>1; IF b=1 THEN INSERT INTO T1 VALUES(b,a); ELSE INSERT INTO T1 VALUES(b+10,a+10); END IF; END; . run;Loops are created with the following:
LOOP <loop_body> /* A list of statements. */ END LOOP;At least one of the statements in <loop_body> should be an EXIT statement of the form
EXIT WHEN <condition>;The loop breaks if <condition> is true. For example, here is a way to insert each of the pairs (1, 1) through (100, 100) into T1 of the above two examples:
DECLARE i NUMBER := 1; BEGIN LOOP INSERT INTO T1 VALUES(i,i); i := i+1; EXIT WHEN i>100; END LOOP; END; . run;Some other useful loop-forming statements are:
WHILE <condition> LOOP <loop_body> END LOOP;
FOR <var> IN <start>..<finish> LOOP <loop_body> END LOOP;Here, <var> can be any variable; it is local to the for-loop and need not be declared. Also, <start> and <finish> are constants.
The example below illustrates a cursor loop. It uses our example relation T1(e,f) whose tuples are pairs of integers. The program will delete every tuple whose first component is less than the second, and insert the reverse tuple into T1.
1) DECLARE /* Output variables to hold the result of the query: */ 2) a T1.e%TYPE; 3) b T1.f%TYPE; /* Cursor declaration: */ 4) CURSOR T1Cursor IS 5) SELECT e, f 6) FROM T1 7) WHERE e < f 8) FOR UPDATE; 9) BEGIN 10) OPEN T1Cursor; 11) LOOP /* Retrieve each row of the result of the above query into PL/SQL variables: */ 12) FETCH T1Cursor INTO a, b; /* If there are no more rows to fetch, exit the loop: */ 13) EXIT WHEN T1Cursor%NOTFOUND; /* Delete the current tuple: */ 14) DELETE FROM T1 WHERE CURRENT OF T1Cursor; /* Insert the reverse tuple: */ 15) INSERT INTO T1 VALUES(b, a); 16) END LOOP; /* Free cursor used by the query. */ 17) CLOSE T1Cursor; 18) END; 19) . 20) run;Here are explanations for the various lines of this program:
CREATE TABLE T2 ( a INTEGER, b CHAR(10) ); CREATE PROCEDURE addtuple1(i IN NUMBER) AS BEGIN INSERT INTO T2 VALUES(i, 'xxx'); END addtuple1; . run;A procedure is introduced by the keywords CREATE PROCEDURE followed by the procedure name and its parameters. An option is to follow CREATE by OR REPLACE. The advantage of doing so is that should you have already made the definition, you will not get an error. On the other hand, should the previous definition be a different procedure of the same name, you will not be warned, and the old procedure will be lost.
There can be any number of parameters, each followed by a mode and a type. The possible modes are IN (read-only), OUT (write-only), and INOUT (read and write). Note: Unlike the type specifier in a PL/SQL variable declaration, the type specifier in a parameter declaration must be unconstrained. For example, CHAR(10) and VARCHAR(20) are illegal; CHAR or VARCHAR should be used instead. The actual length of a parameter depends on the corresponding argument that is passed in when the procedure is invoked.
Following the arguments is the keyword AS (IS is a synonym). Then comes the body, which is essentially a PL/SQL block. We have repeated the name of the procedure after the END, but this is optional. However, the DECLARE section should not start with the keyword DECLARE. Rather, following AS we have:
... AS <local_var_declarations> BEGIN <procedure_body> END; . run;The run at the end runs the statement that creates the procedure; it does not execute the procedure. To execute the procedure, use another PL/SQL statement, in which the procedure is invoked as an executable statement. For example:
BEGIN addtuple1(99); END; . run;The following procedure also inserts a tuple into T2, but it takes both components as arguments:
CREATE PROCEDURE addtuple2( x T2.a%TYPE, y T2.b%TYPE) AS BEGIN INSERT INTO T2(a, b) VALUES(x, y); END addtuple2; . run;Now, to add a tuple (10, 'abc') to T2:
BEGIN addtuple2(10, 'abc'); END; . run;The following illustrates the use of an OUT parameter:
CREATE TABLE T3 ( a INTEGER, b INTEGER ); CREATE PROCEDURE addtuple3(a NUMBER, b OUT NUMBER) AS BEGIN b := 4; INSERT INTO T3 VALUES(a, b); END; . run; DECLARE v NUMBER; BEGIN addtuple3(10, v); END; . run;Note that assigning values to parameters declared as OUT or INOUT causes the corresponding input arguments to be written. Because of this, the input argument for an OUT or INOUT parameter should be something with an "lvalue", such as a variable like v in the example above. A constant or a literal argument should not be passed in for an OUT/INOUT parameter.
We can also write functions instead of procedures. In a function declaration, we follow the parameter list by RETURN and the type of the return value:
CREATE FUNCTION <func_name>(<param_list>) RETURN <return_type> AS ...In the body of the function definition, "RETURN <expression>;" exits from the function and returns the value of <expression>.
To find out what procedures and functions you have created, use the following SQL query:
select object_type, object_name from user_objects where object_type = 'PROCEDURE' or object_type = 'FUNCTION';To drop a stored procedure/function:
drop procedure <procedure_name>; drop function <function_name>;
show errors procedure <procedure_name>;Alternatively, you can type, SHO ERR (short for SHOW ERRORS) to see the most recent compilation error.
Note that the location of the error given as part of the error message is not always accurate!
The steps are as follows:
VARIABLE <name> <type>where the type can be only one of three things: NUMBER, CHAR, or CHAR(n).
PRINT :<name>;outside the PL/SQL statement
VARIABLE x NUMBER BEGIN :x := 1; END; . run; PRINT :x;