Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Friday, 23 June 2017

Difference between Procedures and Functions

0 comments



 Difference between Procedures and Functions

Continue reading >>

Oracle PLSQL Functions

0 comments


PL/SQL blocks are anonymous, cannot be called from other PL/SQL blocks
Subprogram is a named PL/SQL block that could be either
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


Parameters Modes in Procedures and Functions



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



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
Continue reading >>