PL/SQL - (Function|Procedure) parameter

Constraint

With PL/SQL Predefined Data Types, you can constraint parameters:

Example:

-- varchar length 7 not null
SUBTYPE License IS VARCHAR2(7) NOT NULL;
-- number 38 size, precision 0
SUBTYPE INTEGER IS NUMBER(38,0);

Example:

DECLARE
  SUBTYPE License IS VARCHAR2(7) NOT NULL;
  n  License := 'DLLLDDD';
 
  PROCEDURE p (x License) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(x);
  END;
 
BEGIN
  p('1ABC123456789');  -- Succeeds; size is not inherited
  p(NULL);             -- Raises error; NOT NULL is inherited
END;
/

p(NULL);             -- Raises error; NOT NULL is inherited
    *
ERROR at line 12:
ORA-06550: line 12, column 5:
PLS-00567: cannot pass NULL to a NOT NULL constrained formal parameter
ORA-06550: line 12, column 3:
PL/SQL: Statement ignored

Default

Default Values for IN Subprogram Parameters

DECLARE
  PROCEDURE raise_salary (
    emp_id IN employees.employee_id%TYPE,
    amount IN employees.salary%TYPE := 100, -- default to 100
    extra  IN employees.salary%TYPE := 50 -- default to 50
  ) IS
  BEGIN
    UPDATE employees
    SET salary = salary + amount + extra
    WHERE employee_id = emp_id;
  END raise_salary;
 
BEGIN
  raise_salary(120);       -- same as raise_salary(120, 100, 50)
  raise_salary(121, 200);  -- same as raise_salary(121, 200, 50)
END;
/

Mode (IN, OUT, IN OUT)

• IN (Default): Passes a CONSTANT value. Value may not be changed. Variable is passed by reference
• OUT: Returns a value. Variable initialized to the default value of its type (ie NULL except for a record type). Value passed by by value (or by reference with NOCOPY)
• IN OUT: Passes an initial value to the subprogram and returns an updated value to the invoker. Value passed by by value (or by reference with NOCOPY)

Copy / Reference

By default, the variable are passed by copy but you can pass them by reference with the NOCOPY symbol.

PROCEDURE change_job (
   new_job IN OUT NOCOPY VARCHAR2
  )