More

    Procedures and Functions in Oracle DB

    Procedures and functions encapsulate a group of SQL and PL/SQL statements.

    A function is different from stored procedure in that a function must return a value.

    CREATING AND MODIFYING PROCEDURES

    You can create a procedure using the CREATE PROCEDURE statement as:

    CREATE PROCEDURE insert_emp_details_p(

    emp_id_var IN emp.emp_id%TYPE,

    emp_name_var IN emp.emp_name%TYPE

    ) AS

    BEGIN

    INSERT INTO emp values (emp_id_var, emp_name_var);

    COMMIT;

    EXCEPTION

      WHEN OTHERS THEN

    ROLLBACK;

    END insert_emp_details_p;

    You can modify a procedure using CREATE OR REPLACE PROCEDURE:

    CREATE OR REPLACE PROCEDURE insert_emp_details_p(

    emp_id_var IN emp.emp_id%TYPE,

    emp_name_var IN emp.emp_name%TYPE

    ) AS

    emp_count INTEGER;

    BEGIN

    INSERT INTO emp values (emp_id_var, emp_name_var);

    SELECT COUNT(*)

    INTO emp_count

    FROM emp;

    COMMIT;

    EXCEPTION

      WHEN OTHERS THEN

    ROLLBACK;

    END insert_emp_details_p;

    CREATING AND MODIFYING FUNCTIONS

    You can create a function using CREATE FUNCTION:

    CREATE FUNCTION add_num_f(

    num1 IN NUMBER,

    num2 IN NUMBER

    ) RETURN NUMBER AS

    num3 INTEGER;

    BEGIN

    num3:=num1+num2;

    RETURN num3;

    END add_num_f;

    Similar to procedures, you can modify a function using CREATE OR REPLACE FUNCTION as in the example for CREATE OR REPLACE PROCEDURE.

    Calling procedures and functions

    You can call a procedure using CALL statement:

    CALL insert_emp_details_p(1, ‘Heartin’);

    You can call a function passing the required arguments and assign the return value back to a variable within a PL/SQL:

    myvar:= add_num_f(2, 3);

    Outside PL/SQL, within a SELECT query, you can call your function like any other function as:

    SELECT add_num_f(2, 3) FROM dual;

    NOTE: The DUAL table is a special one-row, one-column table present by default in all Oracle database installations. The table has a single VARCHAR2(1) column called DUMMY that has a value of ‘X’. It is suitable for use in selecting a pseudo column such as SYSDATE or USER or for calling a function.

    A FUNCTION WITH A DDL, COMMIT OR ROLLBACK

    You cannot, by default, call a function with a DDL, commit or rollback as you cannot perform a DDL, commit or rollback inside a query or DML. As a work around you could add ‘PRAGMA AUTONOMOUS_TRANSACTION’ in that case as:

    CREATE OR REPLACE FUNCTION insert_emp_details_f(

    emp_id_var IN emp.emp_id%TYPE,

    emp_name_var IN emp.emp_name%TYPE

    ) RETURN NUMBER AS

    PRAGMA AUTONOMOUS_TRANSACTION;

    emp_count INTEGER;

    BEGIN

    INSERT INTO emp values (emp_id_var, emp_name_var);

    SELECT COUNT(*)

    INTO emp_count

    FROM emp;

    COMMIT;

    RETURN emp_count;

    EXCEPTION

      WHEN OTHERS THEN

    ROLLBACK;

    END insert_emp_details_f;

    You can now call it as:

    select insert_emp_details_f(2, ‘Jacob’) FROM dual;

    DROPPING PROCEDURE AND FUNCTION

    You can drop procedure and function using DROP PROCEDURE and DROP FUNCTION:

    DROP PROCEDURE insert_emp_details_p;

    DROP FUNCTION insert_emp_details_f;

    VIEWING ERRORS

    Compilation errors for procedures and functions can be viewed after a create procedure or create function as:

    SHOW ERRORS

    Recent Articles

    OAUTH – FREQUENTLY ASKED QUESTIONS FOR INTERVIEWS AND SELF EVALUATION

    Why is refresh token needed when you have access token? Access tokens are usually short-lived and refresh tokens are...

    SUMO LOGIC VIDEOS AND TUTORIALS

    Sumo Logic Basics - Part 1 of 2 (link is external) (Sep 29, 2016)Sumo Logic Basics - Part 2 of 2...

    GIT – USEFUL COMMANDS

    Discard all local changes, but save them for possible re-use later:  git stash Discarding local changes...

    DISTRIBUTED COMPUTING – RECORDED LECTURES (BITS)

    Module 1 - INTRODUCTION Recorded Lecture - 1.1 Introduction Part I – Definition

    BOOK REVIEW GUIDELINES FOR COOKBOOKS

    Whenever you add reviews for the book, please follow below rules. Write issues in an excel.Create an excel...

    Related Stories

    Leave A Reply

    Please enter your comment!
    Please enter your name here

    Stay on op - Ge the daily news in your inbox