PL/SQL - (Function|Procedure) parameter

> Procedural Languages > PL/SQL - (Procedure Language|PL) SQL

1 - 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

2 - 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;
/
Advertising

3 - 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)

Do not use OUT and IN OUT for function parameters.

4 - 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
  )
lang/plsql/parameter.txt · Last modified: 2017/09/06 22:18 by gerardnico