Procedures and Functions

For example, the following statement creates the CREDIT_ACCOUNT procedure, which credits money to a bank account:

CREATE PROCEDURE credit_account 

			(acct NUMBER, credit NUMBER) AS 

/* This procedure accepts two arguments: an account 

   number and an amount of money to credit to the specified 

   account. If the specified account does not exist, a 

   new account is created. */ 

	old_balance  NUMBER; 

	new_balance  NUMBER; 

   BEGIN 

	SELECT balance INTO old_balance FROM accounts 

	  WHERE acct_id = acct 

	  FOR UPDATE OF balance; 

	new_balance := old_balance + credit; 

	UPDATE accounts SET balance = new_balance 

	  WHERE acct_id = acct; 

	COMMIT; 

	EXCEPTION 

	  WHEN NO_DATA_FOUND THEN 

	    INSERT INTO accounts (acct_id, balance) 

		VALUES(acct, credit); 

	  WHEN OTHERS THEN 

	ROLLBACK; 

END credit_account; 

Notice that both SQL and PL/SQL statements are included in the CREDIT_ACCOUNT procedure.

Procedures and functions are nearly identical. The only differences are that functions always return a single value to the caller, while procedures do not. For simplicity, the term "procedure" is used in the remainder of this chapter to mean "procedures and functions," unless otherwise noted.

How Procedures Are Used

• Define procedures to complete a single, focused task. Do not define long procedures with several distinct subtasks, or subtasks common to many procedures might be duplicated unnecessarily in the code of several procedures.

• Do not define procedures that duplicate the functionality already provided by other features of Oracle. For example, do not define procedures to enforce simple data integrity rules that you could easily enforce using declarative integrity constraints.

Applications for Procedures

• security

• performance

• memory allocation

• productivity

• integrity

Security

For example, you can grant users access to a procedure that updates a table, but not grant them access to the table itself. When a user invokes the procedure, the procedure executes with the privileges of the procedure's owner. Users that have only the privilege to execute the procedure and not the privileges to query, update, or delete from the underlying tables, can invoke the procedure, but they cannot manipulate table data in any other way.

Performance

Memory Allocation

Productivity

For example, procedures can be written to insert, update, or delete rows from the EMP table. These procedures can then be called by any application without rewriting the SQL statements necessary to accomplish these tasks. If the methods of data management change, only the procedures need to be modified, not all of the applications that use the procedures.

Integrity

For example, you can test a procedure or function to guarantee that it returns an accurate result and, once it is verified, reuse it in any number of applications without testing it again. If the data structures referenced by the procedure are altered in any way, only the procedure needs to be recompiled; applications that call the procedure do not necessarily require any modifications.

Anonymous PL/SQL Blocks vs. Stored Procedures

Shared SQL allows a compiled anonymous PL/SQL block already in the shared pool to be reused and shared until it is flushed out of the shared pool.

Alternatively, a stored procedure is created and stored in the database as an object. Once created and compiled, it is a named object that can be executed without recompiling. Additionally, dependency information is stored in the data dictionary to guarantee the validity of each stored procedure.

In summary, by moving PL/SQL blocks out of a database application and into stored database procedures, you avoid unnecessary procedure recompilations by Oracle at runtime, improving the overall performance of the application and Oracle.

Standalone Procedures vs. Package Procedures

Dependency Tracking for Stored Procedures