PL/SQL Procedures/Functions

Writing PL/SQL Procedures/Functions


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.


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;