Oracle PLSQL Functions

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 Oracle 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

IS

       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

Parameters Modes in Oracle Functions

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

Example 1

               

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

   RETURN NUMBER

IS

   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

IS

   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 Oracle 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

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 Functions

The object privileges applicable to functions are

EXECUTE

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

Syntax

          GRANT EXECUTE ON functions_name

  TO username[, username….]|PUBLIC;

Example

   GRANT EXECUTE ON emp_salary_increase TO PUBLIC;

DEBUG

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

Syntax

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

Example

  GRANT DEBUG ON emp_salary_increase TO PUBLIC

Revoking Privileges on functions

To take back a privilege that is granted to a user

Syntax

   REVOKE EXECUTE/DEBUG ON functions_name FROM

   Username[,username…..] |PUBLIC;

Example

   REVOKE  EXECUTE ON emp_salary_increase FROM itp_jul_01, itp_jul_02;

Dropping FUNCTIONS 

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

AS

   CURSOR c_allemp

   IS

      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

   IS

      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

)

IS

BEGIN

   INSERT INTO employee

               (ename, sal, comm

               )

        VALUES (p_name, p_sal, p_comm

               );

   COMMIT;

ENDadd_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

You May Like:

PL/SQL Bulk Collect 

PL/SQL Bulk bind

PL/SQL Index by tables (or) PL/SQL tables (or) associative arrays

PL/SQL Nested tables

PLSQL Record

PL/SQL Varray 

PL/SQL Cursors 

Difference between Procedures and Functions

Oracle Stored Procedures