Examine the following command to create the table EMPLOYEES_TEMP and the PL/SQL block.
CREATE TABLE employees_temp (empid NUMBER(6) NOT NULL,
deptid NUMBER(6) CONSTRAINT c_emp_deptid CHECK (deptid BETWEEN 100 AND 200),
salary Number(8),
deptname VARCHAR2(30) DEFAULT ‘Sales’)
/
DECLARE
SUBTYPE v_emprec_subtype IS employees_temp%ROWTYPE;
v_emprec v_emprec_subtype;
BEGIN
v_emprec.empid := NULL; v_emprec.salary := 10000.002;
v_emprec.deptid := 50;
DBMS_OUTPUT.PUT_LINE(‘v_emprec.deptname: ‘ || v_emprec.deptname);
END;
/
Which statements are true about the above PL/SQL block? (Choose two.)
A.
V_EMPREC.DEPTNAME would display a null value because the default value is not inherited.
B.
Assigning null to V_EMPREC.EMPID would generate an error because the null constraint is
inherited.
C.
Assigning the value 1000.002 to V_EMPREC.SALARY would generate an error because of
the decimal.
D.
Assigning the value 50 to V_EMPREC.DEPTID would work because the check constraint is
not inherited.
A, D
You can use %TYPE or %ROWTYPE to specify the base type. When %TYPE provides the data type of a database column, the subtype inherits the size constraint (if any) of the column. The subtype does not inherit other kinds of column constraints, such as NOT NULL or check constraint, or the default value
http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/datatypes.htm#CIHBBDFJ
CREATE TABLE employees_temp (
empid NUMBER(6) NOT NULL,
deptid NUMBER(6) CONSTRAINT c_emp_deptid CHECK (deptid BETWEEN 100 AND 200),
salary Number(8),
deptname VARCHAR2(30) DEFAULT ‘Sales’
);
DECLARE
SUBTYPE v_emprec_subtype IS employees_temp%ROWTYPE;
v_emprec v_emprec_subtype;
BEGIN
v_emprec.empid := NULL;
v_emprec.salary := 10000.002;
v_emprec.deptid := 50;
DBMS_OUTPUT.PUT_LINE(‘v_emprec.deptname: ‘ || v_emprec.deptname);
DBMS_OUTPUT.PUT_LINE(‘v_emprec.empid: ‘ || v_emprec.empid);
DBMS_OUTPUT.PUT_LINE(‘v_emprec.salary: ‘ || v_emprec.salary);
DBMS_OUTPUT.PUT_LINE(‘v_emprec.deptid: ‘ || v_emprec.deptid);
END;
A , D
P.S i didn’t know you could subtype also a %rowtipe
why subtype it ?
just for the exam purpose :-), to test if you know the subType only inherit the size constraint from %ROWTYPE or %TYPE. and subType not inherit the other constraints like check between, not null and default. One possible exam item could be the var NUMBER(3) in %TYPE, and one subType inherit the size 3, then you assign the var=7777 exceed the size, then error occured,