This tutorial covers Developing Procedures and Functions, Creating a Procedure, Executing a Procedure, Creating a Function, Executing a Function, Passing Parameters – IN Parameters, OUT Parameters, IN OUT Parameters, Purity of a User-Defined Function and Positional and Named Notations.
Oracle 9i : Procedures and Functions
PL/SQL subprograms
A subprogram is a named block of PL/SQL. There are two types of subprograms in PL/SQL namely Procedures and Functions. Every subprogram will have a declarative part, an executable part or body, and an exception handling part, which is optional.
Declarative part contains variable declarations. Body of a subprogram contains executable statements of SQL and PL/SQL. Statements to handle exceptions are written in exception part.
When client executes a procedure are function, the processing is done in the server. This reduces network traffic. The subprograms are compiled and stored in the Oracle database as stored programs and can be invoked whenever required. As they are stored in compiled form when called they only need to be executed. Hence they save time needed for compilation.
Subprograms provide the following advantages
- They allow you to write PL/SQL program that meet our need
- They allow you to break the program into manageable modules.
- They provide reusability and maintainability for the code.
Procedures
Procedure is a subprogram used to perform a specific action. A procedure contains two parts specification and the body. Procedure specification begins with CREATE and ends with procedure name or parameters list. Procedures that do not take parameters are written without a parenthesis. The body of the procedure starts after the keyword IS or AS and ends with keyword END.
In the above given syntax things enclosed in between angular brackets (“< > “) are user defined and those enclosed in square brackets (“[ ]”) are optional.
OR REPLACE is used to overwrite the procedure with the same name if there is any.
AUTHID clause is used to decide whether the procedure should execute with invoker (current-user or person who executes it) or with definer (owner or person created) rights
Example
CREATE PROCEDURE MyProc (ENO NUMBER)
AUTHID DEFINER AS
BEGIN
DELETE FROM EMP
WHERE EMPNO= ENO;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘No employee with this number’);
END;
Let us assume that above procedure is created in SCOTT schema (SCOTT user area) and say is executed by user SEENU. It will delete rows from the table EMP owned by SCOTT, but not from the EMP owned by SEENU. It is possible to use a procedure owned by one user on tables owned by other users. It is possible by setting invoker-rights
AUTHID CURRENT_USER
PRAGMA AUTONOMOUS_TRANSACTION is used to instruct the compiler to treat the procedure as autonomous. i.e. commit or rollback the changes made by the procedure.
Parameter Modes
Parameters are used to pass the values to the procedure being called. There are 3 modes to be used with parameters based on their usage. IN, OUT, and IN OUT.
IN mode parameter used to pass the values to the called procedure. Inside the program IN parameter acts like a constant. i.e it cannot be modified.
OUT mode parameter allows you to return the value from the procedure. Inside Procedure the OUT parameter acts like an uninitialized variable. Therefore its value cannot be assigned to another variable.
IN OUT mode parameter allows you to both pass to and return values from the subprogram. Default mode of an argument is IN.
POSITIONAL vs. NOTATIONAL parameters
A procedure can be communicated by passing parameters to it. The parameters passed to a procedure may follow either positional notation or named notation.
Example
If a procedure is defined as
GROSS (ESAL NUMBER, ECOM NUMBER)
If we call this procedure as GROSS (ESA, ECO) then parameters used are called positional parameters. For Notational Parameters we use the following syntax
GROSS (ECOM => ECO, ESAL => ESA)
A procedure can also be executed by invoking it as an executable statement as shown below.
BEGIN
PROC1; — PROC1 is name of the procedure.
END;
/
{mospagebreak}
Functions:
A function is a PL/SQL subprogram, which is used to compute a value. Function is same like a procedure except for the difference that it have RETURN clause.
Syntax for Function
Examples
Function without arguments
Function with arguments. Different ways of executing the function.
Privileges for creation and execution of procedures To create a procedure in current user schema we use the syntax
CREATE OR REPLACE PROCEDURE
(<argslist) IS
To create procedure in any schema we use
——-
——-
CREATE OR REPLACE ANY PROCEDURE (<argslist) IS
——-
——-
To grant EXECUTE privilege to some user on a procedure we write
GRANT EXECUTE ON <procedure-name> TO <user-name>
SQL>SELECT * FROM USER_PROCEDURES;
Lists all the procedures, functions in current user.
SQL>SELECT * FROM USER_PROCEDURES;
Lists all the procedures, functions in all users.
SQL>DROP
PROCEDURE <procedure-name> ;
Drops or Removes a procedure from database.
SQL>DROP
FUNCTION <function-name> ;
Drops or Removes a function from database.
[catlist id=172].