Unit5 Stored Procedures Functions What are procedures and

Unit-5 Stored Procedures & Functions

What are procedures and functions? A procedure or function is a logically grouped set of SQL and PL/SQL statements that perform a specific task. A stored procedure and function is a named PL/SQL code block that have been compiled and stored in one of the oracle engine’s system table. A procedure or function are made up of: 1. A declarative part 2. An executable part, and 3. An optional exception-handling part 1. Declarative Part: The declarative may contain the declaration of cursors, constants, variables, exceptions and subprograms. These objects are local to the procedure or function. The object become invalid once the user exits from the procedure or the function.

2. Executable Part: The executable part is a PL/SQL block consisting of SQL and PL/SQL statements that assign values control execution and manipulate data. The action that the procedure or function is expected to perform is coded here. The data that is returned back to the calling environment is also returned from here. Variables declared are put to use in this block. 3. Exception handling: This part contain that performs required action to deal with exceptions that may be raised during the execution of code in the executable part. An oracle exception handler can be redirected to the exception handling section of the procedure or function where the procedure or function determines the actual action that must be carried out by oracle’s exception handler. One can’t transfer the flow of execution from the exception handling part to the executable part.

Where do stored procedures and function reside? Procedure and function are stored in oracle database. Before the procedure or function is stored, the oracle engine parses and compiles the procedure or function. How the oracle engine creates a procedure/function? When a procedure is created, the oracle engine automatically performs the following task: 1. Compiles the procedure or function. 2. Stores the procedure or function in the database. When the procedure or function is called, the oracle engine loads the compiled procedure or function in the memory area called the SGA. This allow the code to be executed quickly. Once loaded in the SGA other users also access the same procedure or function if they have been granted permission to do so.

How the oracle engine executes procedures/functions? The oracle engine performs the following steps to execute a procedure or function 1. Verifies user access 2. Verifies procedure or function validity. 3. Execute the procedure or function. The oracle engine checks if the user who called the procedure or function has the execute privilege for the procedure or function, if the user is invalid , then access is denied otherwise the oracle engine proceeds to check whether the called procedure or function is valid or not. The status of a procedure or function is seen by using a select statement as follows: SELECT object_name, object_type, status FROM user_object WHERE object_type=‘PROCEDURE’; OR

SELECT object_name, object_type, status FROM user_object WHERE object_type=‘FUNCTION’; Only if the status is valid, can a procedure or function be executed. Once found valid, the oracle engine then loads a procedure or function into memory and execute it.

Advantage of using a Procedure or function: 1. Security: It can help to enforce data security. 2. Performance: It improves the performance by following way: - amount of information sent over a n/w is less. - no compilation step is required to execute the code. - once the procedure or function is present in the shared pool of the SGA retrieval from disk is not required every time different users call the procedure or function. 3. Memory Allocation: It reduce the amount of memory. Only one copy of procedure needs to be loaded for execution by multiple users. Once a copy of the procedure or function is opened in the oracle’s memory, other users who have permission may access them when required.

4. Productivity: By writing procedure and functions redundant coding can be avoided, increasing productivity. 5. Integrity: A procedure or function needs to be tested only once to guarantee that it returns accurate result. Since procedures and functions are stored in the oracle engine’s they become a part of the engine’s resource. Hence the responsibility of maintaining their integrity rests with the oracle engine. Procedures v/s Functions: 1. A function must return a value back to the caller. A procedure may return one or more values through parameters or may not return at all. 2. Procedures are traditionally the workhorse of the coding world and functions are traditionally the smaller, more specific pieces of code.

Syntax for creating a procedure: CREATE OR REPLACE PROCEDURE [schema…] procedurename ( argument {IN, OUT, IN OUT} data type…. ) {IS, AS} variable declarations; constant declarations; BEGIN PL/SQL subprogram body; EXCEPTION exception PL/SQL block; END;

Keywords and Parameters: REPLACE: Repeat the procedure if it is already exists. This option is used to change the definition of an existing procedure without dropping , recreating and re-granting object privileges previously granted on it. If a procedure is redefined the oracle engine recompiles it. schema: is the schema to contain the procedure. The oracle engine take the default schema to be the current schema, if it is omitted. procedure: is the name of the procedure to be created. argument: is the name of an argument to the procedure. Parentheses can be omitted if no arguments are present.

IN: specifies that a value for the argument must be specified when calling the procedure. OUT: specifies that the procedure passes a value for this argument back to its calling environment after execution. IN OUT: specifies that a value for the argument must be specified when calling the procedure and that the procedure passes a value for this argument back to its calling environment after execution. By default it takes IN. Data type: is the data type of an argument. It supports any data type supported by PL/SQL.

Syntax for creating a function: CREATE OR REPLACE FUNCTION [schema. ] functionname (argument IN data type, …. . ) RETURN data type {IS, AS} variable declarations; constant declarations; BEGIN PL/SQL subprogram body; EXCEPTION exception PL/SQL block; END;

Keywords and Parameters: REPLACE: recreate the function if it is already exists. This option is used to change the definition of an existing function without dropping , recreating and re-granting object privileges previously granted on it. If a function is redefined the oracle engine recompiles it. Schema: is the schema to contain the function. Oracle takes the default schema to be the current schema, if it is omitted. function: is the name of the function to be created. argument: is the name of the argument to be function. Parentheses can be omitted if no arguments are present.

IN: specifies that a value for the argument must be specified when calling the function. RETURN data type: is the data type of the function’s return value. Because every function must return a value, this clause is required. It supports data type supported by PL/SQL. any