PROCEDURES AND FUNCTIONS
AIM
To implement Procedures and Functions using queries.
DESCRIPTION
Procedures and functions are named PL/SQL programs that are stored in a compiled form in the
database. Functions take zero or more parameters and return a value. Procedures take zero or more
parameters and return no values. Both functions and procedures can receive or return zero or more values
through their parameter lists.
Pass parameters to procedures in three ways.
1) IN-parameters
2) OUT-parameters
3) IN OUT-parameters
A procedure may or may not return any value.
A function is a named PL/SQL Block which is similar to a procedure. The major difference
between a procedure and a function is, a function must always return a value, but a procedure may or may
not return a value.
PROCEDURE
General Syntax to create a procedure is
CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters]
IS
Declaration section
BEGIN
Execution section
EXCEPTION
Exception section
END;
The below example creates a procedure ‘employer_details’ which gives the details of the
employee.
CREATE OR REPLACE PROCEDURE employer_details
IS
CURSOR emp_cur IS
SELECT first_name, last_name, salary FROM emp_tbl;
emp_rec emp_cur%rowtype;
BEGIN
FOR emp_rec in sales_cur
LOOP
dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name
|| ' ' ||emp_cur.salary);
END LOOP;
END;
/
