The PL/SQL programming language allows you to create a variety of named program units (containers for code). They include:
Procedures are program units that execute one or more statements and can receive or return zero or more values through their parameter lists. The syntax of a procedure is:
CREATE [OR REPLACE] PROCEDURE name [ (parameter [,parameter]) ] [AUTHID CURRENT_USER | DEFINER ] -- Oracle8i [DETERMINISTIC] -- Oracle8i IS | AS declaration_section BEGIN executable_section [EXCEPTION exception_section] END [name];
A procedure is called as a standalone executable PL/SQL statement:
apply_discount(new_company_id, 0.15) --15% discount
Functions are program units that execute one or more statements and return a value through the RETURN clause. Functions can also receive or return zero or more values through their parameter lists. The syntax of a function is:
CREATE [OR REPLACE] FUNCTION name [ (parameter [,parameter]) ] RETURN return_datatype [AUTHID CURRENT_USER | DEFINER ] -- Oracle8i [DETERMINISTIC] -- Oracle8i [PARALLEL_ENABLE] -- Oracle8i IS | AS [declaration_section] BEGIN executable_section [EXCEPTION exception_section] END [name];
A function must have at least one RETURN statement in the execution section. The RETURN clause in the function header specifies the datatype of the returned value.
See the Section 188.8.131.52, "Compiling stored PL/SQL programs " section for information on the key words OR REPLACE, AUTHID, DETERMINISTIC, and PARALLEL_ENABLE.
See the Section 184.108.40.206, "Privileges and stored PL/SQL " section for additional information on the key word AUTHID.
A function can be called anywhere an expression of the same type can be used. You can call a function:
sales95 := tot_sales(1995,'C');
DECLARE sales95 NUMBER DEFAULT tot_sales(1995,'C'); BEGIN
IF tot_sales(1995,'C') > 10000 THEN ...
SELECT first_name ,surname FROM sellers WHERE tot_sales(1995,'C') > 1000;
Here, for example, max_discount is a programmer-defined function and SYSDATE is a built-in function:
Procedures, functions, and cursors may have a parameter list. This list contains one or more parameters that allow you to pass information back and forth between the subprogram and the calling program. Each parameter is defined by its name, datatype, mode, and optional default value. The syntax for a parameter is:
parameter_name [mode] [NOCOPY] datatype [(:= | DEFAULT) value]
The datatype can be any PL/SQL or programmer-defined datatype, but cannot be constrained by a size (NUMBER is valid, NUMBER(10) is not valid). The actual size of the parameter is determined from the calling program or via a %TYPE constraint.
CREATE OR REPLACE PROCEDURE empid_to_name (in_id emp.emp_id%TYPE -- Compiles OK. ,out_last_name VARCHAR2 -- Compiles OK. ,out_first_name VARCHAR2(10) -- Won't compile. ) IS ...
The lengths of out_last_name and out_first_name are determined by the calling program:
DECLARE surname VARCHAR2(10); first_name VARCHAR2(10); BEGIN empid_to_name(10, surname, first_name); END;
The mode of a parameter specifies whether the parameter can be read from or written to, as shown in the following table.
If the mode is not explicitly defined, it defaults to IN.
OUT parameters can be written to. In Oracle7, OUT parameters can appear only on the left side of an assignment operation. In Oracle8 and above, OUT parameters are read/write and hence can appear on either side of an assignment. If an exception is raised during execution of a procedure or function, assignments made to OUT or IN OUT parameters get rolled back.
The NOCOPY (Oracle8 i ) compiler hint for parameters makes the parameter a call by reference instead of a call by value. Normally, PL/SQL passes IN/OUT parameters by value -- a copy of the parameter is created for the subprogram. When parameter items get large, like collections or objects, the copy can eat memory and slow the processing. NOCOPY directs PL/SQL to pass the parameter by reference, using a pointer to the single copy of the parameter. The disadvantage of NOCOPY is that when an exception is raised during execution of a program that has modified an OUT or IN OUT parameter, the changes to the actual parameters are not rolled back because the parameters were passed by reference instead of being copied.
IN parameters can be given default values. If an IN parameter has a default value, then you do not need to supply an argument for that parameter when you call the program unit. It automatically uses the default value. For example:
CREATE OR REPLACE PROCEDURE hire_employee (emp_id IN VARCHAR2 ,hire_date IN DATE := SYSDATE ,company_id IN NUMBER := 1 ) IS ... -- Example calls to the procedure. -- Use two default values. hire_employee(new_empno); -- Use one default value. hire_employee(new_empno,'12-Jan-1999'); -- Use non-trailing default value, named notation. hire_employee(emp_id=>new_empno, comp_id=>12);
Formal parameters are the names that are declared in the header of a procedure or function. Actual parameters are the values or expressions placed in the parameter list when a procedure or function is called. In the empid_to_name example shown earlier in the Section 220.127.116.11, "Datatype " section, the actual parameters to the procedure are in_id , out_last_name , and out_first_name . The formal parameters used in the call to this procedure are 10, surname , and first_name .
PL/SQL lets you use either of two styles for passing arguments in parameter lists: positional or named notation.
The call to the empid_to_name procedure is shown here with both notations:
BEGIN -- Implicit positional notation. empid_to_name(10, surname, first_name); -- Explicit named notation. empid_to_name(in_id=>10 ,out_last_name=>surname ,out_first_name=>first_name); END;
When calling stored functions from SQL, named notation is not supported.
A local program is a procedure or function that is defined in the declaration section of a PL/SQL block. The declaration of a local program must appear at the end of the declaration section, after the declarations of any types, records, cursors, variables, and exceptions. A program defined in a declaration section may only be referenced within that block's executable and exception sections. It is not defined outside that block.
The following program defines a local procedure and function:
PROCEDURE track_revenue IS PROCEDURE calc_total (year_in IN INTEGER) IS BEGIN calculations here ... END; FUNCTION below_minimum (comp_id IN INTEGER) RETURN BOOLEAN IS BEGIN ... END;
Local programs may be overloaded with the same restrictions as overloaded packaged programs.
PL/SQL allows you to define two or more programs with the same name within any declaration section, including a package specification or body. This is called overloading . If two or more programs have the same name, they must be different in some other way so that the compiler can determine which program should be used.
Here is an example of overloaded programs in a built-in package specification:
PACKAGE DBMS_OUTPUT IS PROCEDURE PUT_LINE (a VARCHAR2); PROCEDURE PUT_LINE (a NUMBER); PROCEDURE PUT_LINE (a DATE); END;
Each PUT_LINE procedure is identical, except for the datatype of the parameter. That is enough difference for the compiler.
To overload programs successfully, one or more of the following conditions must be true:
You cannot overload programs if:
Programs must be declared before they can be used. PL/SQL supports mutual recursion , in which program A calls program B, whereupon program B calls program A. To implement this mutual recursion, you must use a forward declaration of the programs. This technique declares a program in advance of the program definition, thus making it available for other programs to use. The forward declaration is the program header up to the IS/AS keyword:
PROCEDURE perform_calc(year_in IN NUMBER) IS /* Forward declaration for total_cost function. */ FUNCTION total_cost (...) RETURN NUMBER; /* The net_profit function can now use total_cost. */ FUNCTION net_profit(...) RETURN NUMBER IS BEGIN RETURN total_sales(...) - total_cost(...); END; /* The Total_cost function calls net_profit. */ FUNCTION total_cost (...) RETURN NUMBER IS BEGIN IF net_profit(...) < 0 THEN RETURN 0; ELSE RETURN...; END IF; END; BEGIN /* procedure perform_calc */ ... END perform_calc;
The following keywords are new with Oracle8 i :
PRAGMA RESTRICT REFERENCES (function_name, wnps, rnps, wnds, rnds);
Unless you're using an invoker's rights program in Oracle8 i , roles cannot provide object or system privileges that can be used inside stored PL/SQL. You must have privileges granted directly to you for objects that, rather than owning, you reference in stored SQL or PL/SQL (procedures, functions, packages, triggers, and views). This restriction arises from the manner in which the database obtains privileges and checks for objects referenced from SQL.
Direct GRANT and REVOKE privileges cannot be different for two concurrent sessions of the same user, while roles can be disabled in only one session. Privileges are checked when stored PL/SQL is compiled, and since only GRANT and REVOKE privileges can be relied upon to remain enabled, they are the only privileges checked.
This direct GRANT restriction does not apply for anonymous PL/SQL blocks because such blocks are compiled at runtime when all privileges are known. It also does not apply for procedures and functions with the AUTHID of CURRENT_USER (Oracle8 i ).
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.