Procedures and Functions

Subprograms in PL/SQL

PL/SQL blocks are anonymous, cannot be called from other PL/SQL blocks

Subprogram is a named PL/SQL block that could be either

Procedure, or

Function, or

Package

Syntax for Creating a Procedure

Stored Procedure

A stored procedure is a named PL/SQL block that performs an action. It can be stored in the database as a database object for repeated execution.

CREATE [ OR REPLACE ] PROCEDURE proc_name[(argument [mode] data type, argument [mode] data type,……)]

-- PROCEDURES and FUNCTIONS may or may not accept arguments

Local PL/SQL variable declarations

BEGIN

Define action performed by the procedure

EXCEPTION

Handle exceptions, if any

END [ proc_name];

Advantage of Replace option

This option replaces the previous definition of the FUNCTION, PROCEDURE, PACKAGE in the schema with the same name. The advantage of using the “REPLACE” option over “Dropping and recreating an object with new definition” is that the object privileges granted on the object get automatically revoked when you drop the object and are to be explicitly granted again when you recreate it with the new definition where as when the REPLACE option is used the object privileges remain unchanged.

Proc_name : Name of the procedure

Argument : Name of the PL/SQL variable whose value is passed to, populated by the calling environment or both , depending on the mode being used.

Mode : Mode of the argument IN , OUT or IN OUT (explained later)

Datatype : Datatype of the argument

The REPLACE option indicates that if the procedure exist it’s definition will be overwritten by a new procedure body.

Eg # 1

CREATE OR REPLACE PROCEDURE raise_salary (p_eno emp.empno%TYPE)

BEGIN

UPDATE emp

SET sal = sal * 0.1

WHERE empno = p_eno;

END raise_salary;

RAISE_APPLICATION_ERROR Procedure

This procedure is used to display error messages along with error numbers

Example

RAISE_APPLICATION_ERROR(-20001, ‘Invalid Employee’);

Note:

When called, RAISE_APPLICATION_ERROR ends a subprogram, rolls back any database changes it made, and returns a user-defined error message to the application

Error numbers should be between -20000 and -20999

Parameters Modes in Procedures and Functions

Formal parameters can have three modes IN, OUT or IN OUT which decides the behavior of parameters

IN Parameter: Example

CREATE OR REPLACE PROCEDURE raise_salary (p_eno emp.empno%TYPE)

vsal emp.sal%TYPE;

BEGIN

SELECT sal

INTO vsal

FROM emp

WHERE empno = p_eno;

IF vsal < 2000

THEN

UPDATE emp

SET sal = sal + vsal * 0.1

WHERE empno = p_eno;

END IF;

-- An exception handler to raise error if empno is not valid

EXCEPTION

WHEN NO_DATA_FOUND

THEN

DBMS_OUTPUT.put_line ('Empno does not exist');

END raise_salary;

Invoking a Procedure

There are two methods to Invoke a Procedure

Invoke a Procedure from another PL/SQL block

Example:

BEGIN

raise_salary(1002);

END;

Invoke a procedure from SQL*Plus environment by using EXECUTE command

Example

-- PROCEDURE call with parameter

EXECUTE raise_salary(1002);

Note: If no parameters are specified for a procedure, directly specify procedure name without any parenthesis

e. g. EXECUTE procedure_name;

OUT Parameter: Example

CREATE OR REPLACE PROCEDURE query_emp (

p_eno IN emp.empno%TYPE,

p_name OUT emp.ename%TYPE,

p_sal OUT emp.sal%TYPE

)

BEGIN

SELECT ename, sal

INTO p_name, p_sal

FROM emp

WHERE empno = p_eno;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

raise_application_error (-20001, 'Employee does not exist');

END query_emp;

Note: In this example, p_eno parameter value cannot be changed, since it is defined as IN parameter. It receives a value from the calling environment. Parameters p_name and p_sal do not receive any value from the calling environment. The procedure query_emp assigns value to the two parameters and these values are passed back to the calling environment.

Invoking a Procedure Having OUT Parameters

SQL> VARIABLE name VARCHAR2(20)

SQL> VARIABLE salary NUMBER

SQL> EXECUTE query_emp(1001,:name,:salary)

SQL> PRINT name salary

Note: The use of colon (:) is to reference the host variable in the EXECUTE syntax

IN OUT Parameter: Example

CREATE OR REPLACE PROCEDURE emp_salary_increase (

p_emp_id IN emp.empno%TYPE,

p_salary IN OUT emp.sal%TYPE

)

BEGIN

IF p_salary BETWEEN 1000 AND 2000

THEN

p_salary := p_salary * 1.2;

ELSIF p_salary BETWEEN 2000 AND 3000

THEN

p_salary := p_salary * 1.3;

ELSIF p_salary > 3000

THEN

p_salary := p_salary * 1.4;

END IF;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (SQLERRM);

END;

This PL/SQL block shows how to execute the above 'emp_salary_increase' procedure

DECLARE

CURSOR updated_sal

SELECT empno, sal

FROM emp;

pre_sal emp.sal%TYPE;

BEGIN

FOR emp_rec IN updated_sal

LOOP

pre_sal := emp_rec.sal;

emp_salary_increase (emp_rec.empno, emp_rec.sal);

DBMS_OUTPUT.put_line ( ' The salary of '

|| emp_rec.empno

|| ' will be increased from '

|| emp_rec.sal

|| ' to '

|| (emp_rec.sal + pre_sal)

);

END LOOP;

END;

Syntax for Creating a Function

A function is a named PL/SQL block that performs a task and returns a value to the calling environment.

Syntax:

CREATE [ OR REPLACE ] FUNCTION function_name

[( argument [mode] data type, argument [mode] data type )]

RETURN data type

Local PL/SQL variable declarations

BEGIN

Define task performed by the function and return

result using RETURN statement

EXCEPTION

Handle exceptions if any

END [ function_name];

Functions in PL/SQL

Example 1

CREATE OR REPLACE FUNCTION cal_bonus (p_eno emp.empno%TYPE)

RETURN NUMBER

v_sal emp.sal%TYPE;

BEGIN

SELECT sal

INTO v_sal

FROM emp

WHERE empno = p_eno;

RETURN (v_sal * 0.1);

EXCEPTION

WHEN NO_DATA_FOUND

THEN

RETURN -1;

END;

Example 2

CREATE OR REPLACE FUNCTION chk_dept (p_deptno dept.deptno%TYPE)

RETURN BOOLEAN

v_deptno dept.deptno%TYPE;

BEGIN

SELECT deptno

INTO v_deptno

FROM dept

WHERE deptno = p_deptno;

RETURN TRUE;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

RETURN FALSE;

END;

Calling Stored Functions from Different Locations

Functions can be called from different locations through SELECT and DML statements like:

Column list of SELECT command

WHERE and HAVING Clause

ORDER BY, GROUP BY Clauses

VALUES clause of INSERT command

SET clause of UPDATE command

Note: Not all functions can be called from above mentioned locations.

Rules for Calling a Function through a SQL Statement

To be callable from SQL statement:

The function must be stored in database as either stand alone function or as a part of a package

The function can take only IN type of parameters

The formal parameters and return type of the function must be oracle data types

The function must not end the current transaction (commit or rollback) or rollback to a savepoint prior to the function execution

The function must not issue any alter session or alter system commands

Difference between Procedures and Functions

Referencing Objects from Other Schemas

To reference objects from other schemas we first need to have appropriate privileges on the objects

To refer to objects in schemas other than our own, prefix the schema name with the object name:

Schema_name.Object_name

Example: To get information from the emp table belonging to scott's schema the user HR should use the following command

SELECT * FROM scott.emp;

Note: The command will run successfully only if the user HR is granted SELECT right on Scott's emp table

To execute a procedure belonging to scott’s schema the user HR will use the following command

EXECUTE scott.procedure_name

Granting Privileges on Subprograms

The object privileges applicable to subprograms are

EXECUTE

Grants right to execute the subprogram belonging to the schema of the other user

Syntax

GRANT EXECUTE ON subprogram_name

TO username[, username….]|PUBLIC;

Example

GRANT EXECUTE ON emp_salary_increase TO PUBLIC;

DEBUG

Grants right to debug the subprogram belonging to the schema of the other user

Syntax

GRANT DEBUG ON subprogram_name TO username[, username….]|PUBLIC;

Example

GRANT DEBUG ON emp_salary_increase TO PUBLIC

Revoking Privileges on Subprograms

To take back a privilege that is granted to a user

Syntax

REVOKE EXECUTE/DEBUG ON subprogram_name FROM

Username[,username…..] |PUBLIC;

Example

REVOKE EXECUTE ON emp_salary_increase FROM itp_jul_01, itp_jul_02;

Dropping PROCEDURES and FUNCTIONS

To Delete a PROCEDURE:

DROP PROCEDURE <Procedurename>;

e.g.

DROP PROCEDURE emp_salary_increase;

To Delete a FUNCTION:

DROP FUNCTION < Functionname >;

e.g.

DROP FUNCTION chk_dept;

Local Procedures and Functions

The subprograms can also be defined within the declarative section of a block, such subprograms are known as Local Subprograms

Local subprograms can also be defined within an anonymous block

These subprograms follow the same scope and visibility rules as any other PL/SQL identifier

It is only visible in the block in which it is declared

No other block can call local subprograms, as they are not visible to any other block

Local Procedures and Functions within a Stored Subprogram

CREATE OR REPLACE PROCEDURE emp_pro

CURSOR c_allemp

SELECT deptno, ename

FROM emp;

v_dname dept.dname%TYPE;

-- Local function ,local to the procedure which will return the dept name for an employee

FUNCTION show_deptname (p_dno dept.deptno%TYPE)

RETURN VARCHAR2

v_dname dept.dname%TYPE;

BEGIN

SELECT dname

INTO v_dname

FROM dept

WHERE deptno = p_dno;

RETURN v_dname;

END show_deptname;

BEGIN

FOR v_rec IN c_allemp

LOOP

v_dname := show_deptname (v_rec.deptno);

DBMS_OUTPUT.put_line (v_rec.ename || ' belongs to ' || v_dname);

END LOOP;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

DBMS_OUTPUT.put_line

('Wrong department number');

END emp_pro;

Difference between Stand Alone and Local Subprograms

Methods of Passing Parameters

The various methods available for passing parameters to subprograms are:

Positional

Pass actual parameters in the same order as formal parameters

Named

Pass actual parameters in arbitrary order by associating each with its corresponding formal parameter using special syntax (=>)

Combination

Pass some of the actual parameters as positional and some as named

Note: While using combination the positional parameters should be passed first

Methods of Passing Parameters: Example

CREATE TABLE employee AS SELECT ename, sal, comm

FROM emp;

TRUNCATE TABLE employee;

CREATE OR REPLACE PROCEDURE add_employee (

p_name IN emp.ename%TYPE DEFAULT 'unknown',

p_sal IN emp.sal%TYPE DEFAULT 1000,

p_comm IN emp.comm%TYPE DEFAULT 0

)

BEGIN

INSERT INTO employee

(ename, sal, comm

)

VALUES (p_name, p_sal, p_comm

);

COMMIT;

END add_employee;

Methods of Passing Parameters: Example

BEGIN

add_employee ;

add_employee ('SMITH', 2000,600);

add_employee (p_sal=> 6000,p_comm=>200, p_name =>'STEVE');

add_employee (p_sal =>4000) ;

add_employee('MARK', p_sal=> 6000,p_comm=>200);

END;

SELECT * FROM employee;

Note: All the positional parameters should precede the named parameters in a subprogram call.

Data Dictionary View

USER_SOURCE

Is used to obtain the text of a stored procedure or a stored function

USER_ERRORS

Is used to find out the compilation errors in the subprogram, currently getting compiled

One can use the SQL*Plus command SHOW ERRORS, instead of firing a SELECT query on USER_ERRORS

USER_OBJECTS

Is used to get the details of all the objects created in a particular schema

USER_PROCEDURES

Is used to get the details of all procedures in that user’s schema

Oracle Applicatons

Pages

Thursday, 7 April 2016

Procedures and Functions

No comments:

Post a Comment