Oracle PL/SQL Subprogram Conditionals and Variables

Stored procedures can both accept input parameters and return parameters back to the caller. However, for each parameter, you need to declare the name and the datatype and whether it will be used to pass information into the procedure, pass information back out of the procedure, or perform both duties.

Note  Although stored functions can accept both input and output parameters in the parameter list, they only support input parameters and must return one and only one value if referenced from aSELECTstatement. Therefore, when declaring input parameters for stored functions, be sure to include just the name and type if you are only going to reference the stored functions fromSELECTstatements. Oracle best practices discourages the use of function parameters returning values to the calling program; if you must return more than one value from a subprogram, a stored procedure is more suitable.

Perhaps not surprisingly, the datatypes supported as parameters or return values for stored procedures correspond to those supported by Oracle, plus a few specific to PL/SQL. Therefore, you’re free to declare a parameter to be of any datatype you might use when creating a table.

To declare a parameter’s purpose, use one of the following three keywords:

1. IN: These parameters are intended solely to pass information into a procedure. You cannot modify these values within the procedure. 
    
2. OUT: These parameters are intended solely to pass information back out of a procedure. You cannot pass a constant for a parameter defined asOUT. 
    
3. IN OUT: These parameters can pass information into a procedure, have its value changed, and then be referenced again from outside of the procedure.

Consider the following example to demonstrate the use ofINandOUT. First, create a stored procedure calledRAISE_SALARYthat accepts an employee ID and a salary increase amount and returns the employee name to confirm the salary increase:

create PROCEDURE raise_salary
  (emp_id IN NUMBER, amount IN NUMBER, emp_name OUT VARCHAR2) AS
BEGIN
   UPDATE employees SET salary = salary + amount WHERE employee_id = emp_id;
   SELECT last_name INTO emp_name FROM employees WHERE employee_id = emp_id;
END raise_salary;

Next, use an anonymous PL/SQL block to increase the salary of employee number 105 by $200 per month:

DECLARE
  emp_num      NUMBER(6) := 105;
  sal_inc      NUMBER(6) := 200;
  emp_last     VARCHAR2(25);
BEGIN
  raise_salary(emp_num, sal_inc, emp_last);
  DBMS_OUTPUT.PUT_LINE('Salary has been updated for: ' || emp_last);
END;

The results are as follows:

--------------------------------------------
Salary has been updated for: Austin

Statement processed.
--------------------------------------------