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 procedures and functions

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;

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;

Similarly 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

Reference

http://en.wikipedia.org/wiki/DUAL_table