Working with Composite Datatypes
- Composite Datatypes
- These can hold multiple values not like scalar datatypes which can hold only a single value.
- Are of two types :
- PL/SQL Records
- PL/SQL Collections
a. VARRAY
b. Nested Tables
a. INDEX BY Tables
PL/SQL Record : A PL/SQL Record can have variables of different types. Ex PL/SQL Record to hold Employee Details of each Employee.
PL/SQL Collections : Collections are used to treat data as a single unit.
A Composite Datatype is used to group all related data as a single unit.
Ex. A single bag to hold all the Laptop components instead of having seperate bag for each component.
Use PL/SQL Records to store values of different data types which are logically related.
If you create a PL/SQL Record to hold employee details indicate that all values stored are related because they provide information about a particular employee.
Use PL/SQL Collections to store values of the same data type.These are very similiar to arrays in Java and C++ languages.
A PL/SQL Record is a composite datatype, which means that it can hold more than one piece of information, as compared to a scalar datatype, such as a number or string.
- Creating a PL/SQL Record
Syntax :
TYPE type_name IS RECORD
(field_declaration[,field_declaration]…);
identifier type_name;
field_declaration
field_name {field_type | variable%TYPE | table.column%TYPE | table%ROWTYPE} [[NOT NULL] {:= | DEFAULT} expr]
Declare Variables to store name,job and salary of a new employee using PL/SQL Record
TYPE emp_rec_type IS RECORD
(ename VARCHAR2(30),
job VARCHAR2(30),
sal NUMBER));
emp_record emp_rec_type;
A Record Type EMP_REC_TYPE is defined to hold values for Employee Name,Job and his Salary.
A Variable EMP_RECORD of datatype EMP_REC_TYPE is declared
TYPE emp_rec_type IS RECORD
(ename emp.ename%TYPE,
job emp.job%TYPE,
sal emp.sal%TYPE));
Fields in a Record are accessed with the name of the Record.
To reference or initialize field use the
record_name.field_name;
emp_record.job;
Example:
— Example of creating a Record Structure of related Employee Data and then create a variable to hold the — record structure and print the entire record structure —
DECLARE
TYPE rec_type IS RECORD(
last_name VARCHAR2(25),
department VARCHAR2(25),
salary NUMBER);
rec rec_type;
BEGIN
rec.last_name:=’James’;
rec.department:=’Marketing’;
rec.salary:=5000;
DBMS_OUTPUT.PUT_LINE(‘Employee Record Structure’);
DBMS_OUTPUT.PUT_LINE(rec.last_name);
DBMS_OUTPUT.PUT_LINE(rec.department);
DBMS_OUTPUT.PUT_LINE(rec.salary);
END;
/
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
TYPE emp_rec_type IS RECORD(ename emp.ename%TYPE);
emp_record emp_rec_type; — name record
v_empno emp.empno%type:=7369;
BEGIN
SELECT ename
INTO emp_record FROM emp WHERE empno = v_empno;
DBMS_OUTPUT.PUT_LINE(emp_record.ename);
END;
/
DECLARE
TYPE emp_rec_type IS RECORD(employee_name emp.ename%TYPE,salary emp.sal%TYPE);
emp_record emp_rec_type; — name record
v_empno emp.empno%type:=7369;
BEGIN
SELECT ename,sal
INTO emp_record FROM emp WHERE empno = v_empno;
DBMS_OUTPUT.PUT_LINE(‘Employee Name ‘||emp_record.employee_name||’ and his Salary is ‘||emp_record.salary);
END;
/
DECLARE
TYPE emp_rec_type IS RECORD(rowid_id rowid,employee_name emp.ename%TYPE,salary emp.sal%TYPE);
emp_record emp_rec_type; — name record
v_empno emp.empno%type:=7369;
BEGIN
SELECT rowid,ename,sal
INTO emp_record FROM emp WHERE empno = v_empno;
DBMS_OUTPUT.PUT_LINE(‘ROW ID ‘||emp_record.rowid_id||’ Employee Name ‘||emp_record.employee_name||’ and his Salary is ‘||emp_record.salary);
END;
/
- %ROWTYPE Attribute
%ROWTYPE is used to declare a Record with the same types as found in the specified table.
The %ROWTYPE attribute is used to declare a record that can hold an entire row of a table.
The fields in the record take their names and datatypes from the columns of the table.
The record can also store an entire row of data fetched from a cursor.
The %ROWTYPE attribute is useful when you want to retrieve an entire row from a table.
In the absence of this attribute you would be forced to declare a variable for each of the columns retrieved by the select statement.
DECLARE
r_emp emp%rowtype;
BEGIN
SELECT INITCAP(ename),job
INTO r_emp.ename,r_emp.job
FROM emp
WHERE empno = 7369;
DBMS_OUTPUT.PUT_LINE(‘Employee Name :’||r_emp.ename);
DBMS_OUTPUT.PUT_LINE(‘Job :’||r_emp.job);
END;
/
PL/SQL Block to select those employee records who have resigned and those records to be inserted into resignation employee table using %ROWTYPE
— Create a new Table using the existing EMP Table —
CREATE TABLE emp_redleaf AS SELECT * FROM emp
/
— Add a new column RESIGNATION in the newly created table stating whether the existing employee has resigned —
ALTER TABLE emp_redleaf ADD(resigned VARCHAR2(1))
/
— Update the RESIGNED Flag to ‘Y’ for Employees belonging to Department 10 —
UPDATE emp_redleaf SET resigned = ‘Y’ WHERE deptno = 10
/
— Update the RESIGNED Flag to ‘N’ for rest of the Employees —
UPDATE emp_redleaf SET resigned = ‘N’ WHERE deptno <> 10
/
— Create Table RETIRED_EMP —
CREATE TABLE retired_emp(empno NUMBER,ename VARCHAR2(40))
/
SELECT * FROM emp_redleaf WHERE resigned = ‘Y’
/
— PL/SQL Block to allow the user to enter the Employee No. at runtime and move that employee detail to RETIRED_EMP Table using %ROWTYPE —
DELETE retired_emp
/
DECLARE
emp_rec emp_redleaf%ROWTYPE;
BEGIN
SELECT * INTO emp_rec FROM emp_redleaf
WHERE empno = &empno AND resigned = ‘Y’;
INSERT INTO retired_emp(empno,ename)VALUES(emp_rec.empno,emp_rec.ename);
DBMS_OUTPUT.PUT_LINE(‘Total No.of Records inserted into Retired Emp Table : ‘||SQL%ROWCOUNT);
END;
/
SELECT * FROM retired_emp
/
— PL/SQL Block to allow the user to enter the Employee No. at runtime and move that employee detail to RETIRED_EMP Table using %ROWTYPE —
DELETE retired_emp
/
DECLARE
retired_rec retired_emp%ROWTYPE;
BEGIN
SELECT empno,ename INTO retired_rec FROM emp_redleaf
WHERE empno = &empno AND resigned = ‘Y’;
INSERT INTO retired_emp VALUES retired_rec;
DBMS_OUTPUT.PUT_LINE(‘Total No.of Records inserted into Retired Emp Table : ‘||SQL%ROWCOUNT);
END;
/
SELECT * FROM retired_emp
/
DROP TABLE emp_redleaf
/
DROP TABLE retired_emp
/
DECLARE
CURSOR emp_cursor IS SELECT * FROM emp WHERE deptno = 10;
emp_rec emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_rec;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emp_rec.empno||’ ‘||emp_rec.ename||’ ‘||emp_rec.deptno);
END LOOP;
CLOSE emp_cursor;
END;
/