PL/SQL 6-1: User-Defined Records

 Database Programming with PL/SQL

6-1: User-Defined Records

Practice Activities

Vocabulary

Declares a record with the same fields as the cursor on which it is based - PL/SQL record

Try It / Solve It

1. Copy and execute the following anonymous block. Then modify it to declare and use a single record

instead of a scalar variable for each column. Make sure that your code will still work if an extra column is

added to the departments table later. Execute your modified block and save your code.

DECLARE

 v_dept_id departments.department_id%TYPE;

 v_dept_name departments.department_name%TYPE;

 v_mgr_id departments.manager_id%TYPE;

 v_loc_id departments.location_id%TYPE;

BEGIN

 SELECT department_id, department_name, manager_id, location_id

 INTO v_dept_id, v_dept_name, v_mgr_id, v_loc_id

 FROM departments

 WHERE department_id = 80;

 DBMS_OUTPUT.PUT_LINE(v_dept_id || ' ' || v_dept_name

 || ' ' || v_mgr_id || ' ' || v_loc_id);

EXCEPTION

 WHEN NO_DATA_FOUND THEN

 DBMS_OUTPUT.PUT_LINE('This department does not exist');

END;

DECLARE

TYPE rec_dep IS RECORD(

id departments.department_id%TYPE,

name departments.department_name%TYPE,

manager_id departments.manager_id%TYPE,

location_id departments.location_id%TYPE);

v_rec_dep rec_dep;

BEGIN

 SELECT department_id, department_name, manager_id, location_id

 INTO v_rec_dep

 FROM departments

 WHERE department_id = 80;

 DBMS_OUTPUT.PUT_LINE(v_rec_dep.id || ' ' || v_rec_dep.name || ' ' || v_rec_dep.manager_id || ' ' || v_rec_dep.location_id);

EXCEPTION

 WHEN NO_DATA_FOUND THEN

 DBMS_OUTPUT.PUT_LINE('This department does not exist');

END;