Live ORACLE

DROP TABLE employees_temp CASCADE CONSTRAINTS PURGE;

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;

/

Using SUBTYPE With %TYPE and %ROWTYPE