This section contains information about general aspects of stored procedures usage.
A stored procedure is a schema object that consists of a set of SQL statements and other PL/SQL constructs, grouped together, stored in the database, and run as a unit to solve a specific problem or perform a set of related tasks. Procedures let you combine the ease and flexibility of SQL with the procedural functionality of a structured programming language. Large or complex processing that might require the execution of several SQL statements is moved into stored procedures, and all applications call the procedures only.
Objects similar to stored procedures are stored functions. Almost everything that is true for procedures, holds for functions as well. The main difference between these objects is that function has a return value, and procedure has not.
A stored procedures and functions may have input, output, and input/output parameters.
Input parameter is a parameter whose value is passed into a stored procedure/function module. The value of an IN parameter is a constant; it can't be changed or reassigned within the module.
For example, the following procedure inserts a row into the Dept table:CREATE PROCEDURE dept_insert (pDeptno INTEGER, pDname VARCHAR, pLoc VARCHAR) ASBEGININSERT INTO dept(deptno, dname, loc) VALUES (pDeptno, pDname, pLoc); END;
It needs to receive the values to be inserted into the new record, and thus the procedure has three input parameters, corresponding to each field of the table. The procedure may be executed inside a PL/SQL block like follows:begin dept_insert (10, 'Accounting', 'New York'); end;
Output parameter is a parameter whose value is passed out of the stored procedure/function module, back to the calling PL/SQL block. An OUT parameter must be a variable, not a constant. It can be found only on the left-hand side of an assignment in the module. You cannot assign a default value to an OUT parameter outside of the module's body. In other words, an OUT parameter behaves like an uninitialized variable. In the following sample, the stored procedure returns the count of records in table Dept:CREATE PROCEDURE dept_count (cnt OUT INTEGER) ASBEGINSELECT COUNT(*) INTO cnt FROM dept; END;
An input/output parameter is a parameter that functions as an IN or an OUT parameter or both. The value of the IN/OUT parameter is passed into the stored procedure/function and a new value can be assigned to the parameter and passed out of the module. An IN/OUT parameter must be a variable, not a constant. However, it can be found on both sides of an assignment. In other words, an IN/OUT parameter behaves like an initialized variable.
Besides scalar variables, a stored procedure can return result sets, i.e. the results of a SELECT statement. In Oracle, the cursor variables are used for this case. A cursor may be interpreted as a reference to the result set. The following sample demonstrates how a simplest select statement can be wrapped in a stored procedure:CREATE PROCEDURE get_all_depts_proc (cur OUT SYS_REFCURSOR) ASBEGINOPEN cur FOR SELECT * FROM dept; END;
The same SELECT statement can be used via a stored function as follows:CREATE OR REPLACE FUNCTION get_all_depts_func RETURN SYS_REFCURSOR AS cur SYS_REFCURSOR; BEGINOPEN cur FOR SELECT * FROM dept; RETURN cur; END;
Here the cursor is passed as the return value instead of being an output parameter.
Working with Oracle Stored Procedures | Using Stored Procedures via the OracleCommand class | Using Package Procedures | Using Stored Procedures in DataSets
Using Oracle PL/SQL
Note: The material on triggers that was formerly in this document has been moved to A New Document on Constraints and Triggers.
PL/SQL stands for Procedural Language/SQL. PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks, which can be nested within each other. Typically, each block performs a logical action in he program. A block has the following structure: 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 , , , and several other data manipulation statements plus some transaction control. However, the statement has a special form in which a single tuple is placed in variables; more on this later. Data definition statements like , , or 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.
Basic Structure of PL/SQL
To execute a PL/SQL program, we must follow the program text itself by
- A line with a single dot (""), and then
- A line with
Information is transmitted between a PL/SQL program and the database through variables. Every variable has a specific type associated with it. That type can be
Variables and Types
- One of the types used by SQL for database columns
- A generic type used in PL/SQL such as
- Declared to be the same as the type of some database column
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 operator. For example:DECLARE myBeer Beers.name%TYPE; gives PL/SQL variable whatever type was declared for the column in relation .
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 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 be a record with fields and , assuming that the relation has the schema .
The initial value of any variable, regardless of its type, is . 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.
The simplest form of program has some declarations followed by an executable section consisting of one or more of the SQL statements with which we are familiar. The major nuance is that the form of the statement is different from its SQL form. After the clause, we must have an clause listing variables, one for each attribute in the clause, into which the components of the retrieved tuple must be placed.
Simple Programs in PL/SQL
Notice we said "tuple" rather than "tuples", since the 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 that has first component greater than 1, namely (2,4). The statement thus inserts (4,2) into .
PL/SQL allows you to branch and create loops in a fairly familiar way.
Control Flow in PL/SQL
An statement looks like:IF <condition> THEN <statement_list> ELSE <statement_list> END IF; The 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 should be an statement of the form EXIT WHEN <condition>; The loop breaks if is true. For example, here is a way to insert each of the pairs (1, 1) through (100, 100) into 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:
- by itself is an unconditional loop break. Use it inside a conditional if you like.
- A loop can be formed with WHILE <condition> LOOP <loop_body> END LOOP;
- A simple loop can be formed with: FOR <var> IN <start>..<finish> LOOP <loop_body> END LOOP; Here, can be any variable; it is local to the for-loop and need not be declared. Also, and are constants.
A cursor is a variable that runs through the tuples of some relation. This relation can be a stored table, or it can be the answer to some query. By fetching into the cursor each tuple of the relation, we can write a program to read and process the value of each such tuple. If the relation is stored, we can also update or delete the tuple at the current cursor position.
The example below illustrates a cursor loop. It uses our example relation 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 .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:
- Line (1) introduces the declaration section.
- Lines (2) and (3) declare variables and to have types equal to the types of attributes and of the relation . Although we know these types are , we wisely make sure that whatever types they may have are copied to the PL/SQL variables (compare with the previous example, where we were less careful and declared the corresponding variables to be of type ).
- Lines (4) through (8) define the cursor . It ranges over a relation defined by the -- query. That query selects those tuples of whose first component is less than the second component. Line (8) declares the cursor since we will modify using this cursor later on Line (14). In general, is unnecessary if the cursor will not be used for modification.
- Line (9) begins the executable section of the program.
- Line (10) opens the cursor, an essential step.
- Lines (11) through (16) are a PL/SQL loop. Notice that such a loop is bracketed by and . Within the loop we find:
- On Line (12), a fetch through the cursor into the local variables. In general, the statement must provide variables for each component of the tuple retrieved. Since the query of Lines (5) through (7) produces pairs, we have correctly provided two variables, and we know they are of the correct type.
- On Line (13), a test for the loop-breaking condition. Its meaning should be clear: after the name of a cursor is true exactly when a fetch through that cursor has failed to find any more tuples.
- On Line (14), a SQL statement that deletes the current tuple using the special condition .
- On Line (15), a SQL statement that inserts the reverse tuple into .
- Line (17) closes the cursor.
- Line (18) ends the PL/SQL program.
- Lines (19) and (20) cause the program to execute.
PL/SQL procedures behave very much like procedures in other programming language. Here is an example of a PL/SQL procedure that, given an integer , inserts the tuple into the following example relation: 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 followed by the procedure name and its parameters. An option is to follow by . 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 (read-only), (write-only), and (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, and are illegal; or 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 ( is a synonym). Then comes the body, which is essentially a PL/SQL block. We have repeated the name of the procedure after the , but this is optional. However, the section should not start with the keyword . Rather, following we have:... AS <local_var_declarations> BEGIN <procedure_body> END; . run; The 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 , 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 to : BEGIN addtuple2(10, 'abc'); END; . run; The following illustrates the use of an 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 or causes the corresponding input arguments to be written. Because of this, the input argument for an or parameter should be something with an "lvalue", such as a variable like in the example above. A constant or a literal argument should not be passed in for an / parameter.
We can also write functions instead of procedures. In a function declaration, we follow the parameter list by and the type of the return value:CREATE FUNCTION <func_name>(<param_list>) RETURN <return_type> AS ... In the body of the function definition, ";" exits from the function and returns the value of .
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>;
PL/SQL does not always tell you about compilation errors. Instead, it gives you a cryptic message such as "procedure created with compilation errors". If you don't see what is wrong immediately, try issuing the command show errors procedure <procedure_name>; Alternatively, you can type, (short for ) 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!
Sometimes we might want to print the value of a PL/SQL local variable. A ``quick-and-dirty'' way is to store it as the sole tuple of some relation and after the PL/SQL statement print the relation with a statement. A more couth way is to define a bind variable, which is the only kind that may be printed with a command. Bind variables are the kind that must be prefixed with a colon in PL/SQL statements, such as discussed in the section on triggers.
The steps are as follows:
- We declare a bind variable as follows: VARIABLE <name> <type> where the type can be only one of three things: , , or .
- We may then assign to the variable in a following PL/SQL statement, but we must prefix it with a colon.
- Finally, we can execute a statement PRINT :<name>; outside the PL/SQL statement
This document was written originally by Yu-May Chang and Jeff Ullman for CS145, Autumn 1997; revised by Jun Yang for Prof. Jennifer Widom's CS145 class in Spring, 1998; additional material by Jeff Ullman, Autumn 1998; further revisions by Jun Yang, Spring 1999; minor revisions by Jennifer Widom, Spring 2000.