Assigning All the Fields of a Record in One Statement
DECLARE
-- Two identical type declarations.
TYPE deptrec1 IS RECORD(dept_num NUMBER(2),
dept_name VARCHAR2(14));
TYPE deptrec2 IS RECORD(dept_num NUMBER(2),
dept_name VARCHAR2(14));
dept1_info DEPTREC1;
dept2_info DEPTREC2;
dept3_info DEPTREC2;
BEGIN
-- Not allowed; different datatypes, even though fields are the same.
-- dept1_info := dept2_info;
-- This assignment is OK because the records have the same type.
dept2_info := dept3_info;
END;
/
You can assign a %ROWTYPE record to a user-defined record if their fields match in number and order, and corresponding fields have the same datatypes:
DECLARE
TYPE recordtyp IS RECORD(LAST employees.last_name%TYPE,
id employees.employee_id%TYPE);
CURSOR c1 IS
SELECT last_name,
employee_id
FROM employees;
-- Rec1 and rec2 have different types. But because rec2 is based on a %ROWTYPE,
-- we can assign is to rec1 as long as they have the right number of fields and
-- the fields have the right datatypes.
rec1 RECORDTYP;
rec2 c1%ROWTYPE;
BEGIN
SELECT last_name,
employee_id
INTO rec2
FROM employees
WHERE ROWNUM < 2;
rec1 := rec2;
dbms_output.Put_line('Employee #'
||rec1.id
||' = '
||rec1.LAST);
END;
/
You can also use the SELECT or FETCH statement to fetch column values into a record.
The columns in the select-list must appear in the same order as the fields in your record.
Posts
0 comments:
Post a Comment