Saturday, October 8, 2011

PL/SQL: 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;
   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 ...

No comments: