Scribd
Upload
487 views2 pages

PLSQL 6 1 Practice

This document discusses using PL/SQL records to retrieve and store data from multiple columns in a database table. It defines a record as a composite data type consisting of related data items stored as fields. It provides an example of declaring a record type named rec_dep to represent the departments table, then using it to SELECT and output department data in a single variable rather than separate scalar variables for each column. Defining the record allows the code to still work if additional columns are added to the table later.

Uploaded by

Alfonso Torres
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
487 views2 pages

PLSQL 6 1 Practice

This document discusses using PL/SQL records to retrieve and store data from multiple columns in a database table. It defines a record as a composite data type consisting of related data items stored as fields. It provides an example of declaring a record type named rec_dep to represent the departments table, then using it to SELECT and output department data in a single variable rather than separate scalar variables for each column. Defining the record allows the code to still work if additional columns are added to the table later.

Uploaded by

Alfonso Torres
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

www.oracle.

com/academy

Database Programming with PL/SQL


6-1: User-Defined Records
Practice Activities
Vocabulary

Identify the vocabulary word for each definition below:

a composite data type consisting of a group of related data


PL/SQL RECORD
items stored as fields, each with its own name and data type

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_depa rec_dep;
BEGIN
SELECT department_id, department_name, manager_id, location_id
INTO v_rec_depa
FROM departments
WHERE department_id = 80;
DBMS_OUTPUT.PUT_LINE(v_rec_depa.id || ' ' || v_rec_depa.name || ' ' ||
v_rec_depa.manager_id || ' ' || v_rec_depa.location_id);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('This department does not exist');
END;

You might also like