Stored Procedures - General Information

In This Topic

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) AS
BEGIN
  INSERT 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)
AS
BEGIN
  SELECT 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) AS
BEGIN
  OPEN 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;
BEGIN
  OPEN cur FOR SELECT * FROM dept;
  RETURN cur;
END;

Here the cursor is passed as the return value instead of being an output parameter.

See Also