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 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;
/
Do not use OUT and IN OUT for function parameters.
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
)