Example 3-11 Using SUBTYPE With %TYPE and %ROWTYPE

CREATE TABLE employees_temp (empid NUMBER(6) NOT NULL PRIMARY KEY, 
 deptid NUMBER(6) CONSTRAINT check_deptid CHECK (deptid BETWEEN 100 AND 200),
 deptname VARCHAR2(30) DEFAULT 'Sales');
 
DECLARE
   SUBTYPE v_empid_subtype IS employees_temp.empid%TYPE;
   SUBTYPE v_deptid_subtype IS  employees_temp.deptid%TYPE;
   SUBTYPE v_deptname_subtype IS employees_temp.deptname%TYPE;
   SUBTYPE v_emprec_subtype IS employees_temp%ROWTYPE;   
   v_empid    v_empid_subtype;
   v_deptid   v_deptid_subtype;
   v_deptname v_deptname_subtype;
   v_emprec   v_emprec_subtype;
BEGIN
   v_empid := NULL;  -- this works, null constraint is not inherited
-- v_empid := 10000002; -- invalid, number precision too large 
   v_deptid := 50; -- this works, check constraint is not inherited
-- the default value is not inherited in the following
   DBMS_OUTPUT.PUT_LINE('v_deptname: ' || v_deptname);
   v_emprec.empid := NULL;  -- this works, null constraint is not inherited
-- v_emprec.empid := 10000002; -- invalid, number precision too large
   v_emprec.deptid := 50; -- this works, check constraint is not inherited
-- the default value is not inherited in the following
   DBMS_OUTPUT.PUT_LINE('v_emprec.deptname: ' || v_emprec.deptname); 
END;
/

Example 3-11 Using SUBTYPE With %TYPE and %ROWTYPE

CREATE TABLE employees_temp (empid NUMBER(6) NOT NULL PRIMARY KEY, 
 deptid NUMBER(6) CONSTRAINT check_deptid CHECK (deptid BETWEEN 100 AND 200),
 deptname VARCHAR2(30) DEFAULT 'Sales');
 
DECLARE
   SUBTYPE v_empid_subtype IS employees_temp.empid%TYPE;
   SUBTYPE v_deptid_subtype IS  employees_temp.deptid%TYPE;
   SUBTYPE v_deptname_subtype IS employees_temp.deptname%TYPE;
   SUBTYPE v_emprec_subtype IS employees_temp%ROWTYPE;   
   v_empid    v_empid_subtype;
   v_deptid   v_deptid_subtype;
   v_deptname v_deptname_subtype;
   v_emprec   v_emprec_subtype;
BEGIN
   v_empid := NULL;  -- this works, null constraint is not inherited
-- v_empid := 10000002; -- invalid, number precision too large 
   v_deptid := 50; -- this works, check constraint is not inherited
-- the default value is not inherited in the following
   DBMS_OUTPUT.PUT_LINE('v_deptname: ' || v_deptname);
   v_emprec.empid := NULL;  -- this works, null constraint is not inherited
-- v_emprec.empid := 10000002; -- invalid, number precision too large
   v_emprec.deptid := 50; -- this works, check constraint is not inherited
-- the default value is not inherited in the following
   DBMS_OUTPUT.PUT_LINE('v_emprec.deptname: ' || v_emprec.deptname); 
END;
/