Writing PL/SQL Procedures/Functions

Submitted by:Tim Kelly

Date added:01 December, 2011

Category:PL SQL

PL/SQL functions returns a scalar value and PL/SQL procedures return nothing. Both can take zero or more number of parameters as input or output. The special feature about PL/SQL is that a procedure/function argument can be of input (indicating the argument is read-only), output (indicating the argument is write-only) or both (both readable and writable).

For example, the following is a PL/SQL procedure and a function.

Tags: procedures , functions

Code Snippet:

    PROCEDURE hire_employee (emp_id INTEGER, name VARCHAR2) IS
BEGIN
INSERT INTO employee VALUES (emp_id, name, 1000);
END hire_employee;

FUNCTION sal_ok (salary REAL, title REAL) RETURN BOOLEAN IS
min_sal REAL;
max_sal REAL;
BEGIN
SELECT losal, hisal INTO min_sal, max_sal
FROM sals
WHERE job = title;
RETURN (salary >= min_sal) AND (salary <= max_sal);
END sal_ok;


A function is called as part of an expression. For example, the function sal_ok might be called as follows:

IF sal_ok(new_sal, new_title) THEN ...
 
 

Comments