Record Type - Table-based, Cursor based and programmer defined
A record is a composite data structure composed of more than one atomic elements/fields and each with its own value.In PL/SQL, Record type is local type(declared in PL/SQL block or in package). Records in PL/SQL are very similar in concept and structure to the rows of a database table. Each filed of record can be accessed by name.Different types of PL/SQL Record :-
We have three different types of records.1. Table based - A record based on structure of columns of database tables. (TABLE%ROWTYPE)
2.Cursor based - A record based on the cursor's SELECT statement.(CURSOR%ROWTYPE)
3. Programmer/Used defined records - Programmer defines structure of record.
Lets see each of them one by one and understand how it is used in PL/SQL.
Table based record :- A record based on table type(termed as table record) is created using %ROWTYPE attribute of table. general syntax is as follows:
--rcord_name is a valid identifier, table_name is database table
<record_name> <table_name>%ROWTYPE;
DECLARE --TABLE BASED RECORD V_LOC_REC LOCATIONS%ROWTYPE; BEGIN SELECT * into V_LOC_REC FROM LOCATIONS WHERE POSTAL_CODE = '500081'; DBMS_OUTPUT.PUT_LINE(V_LOC_REC.STREET_ADDRESS || ' ' || V_LOC_REC.STATE_PROVINCE); END;
Cursor based record :- A record whose structure are drawn from the SELECT list of a cursor is termed as cursor record. General syntax of cursor based record is :
/*Create a cursor*/
CURSOR cursor_name IS <SELECT_STMT_with_fields_retrival>;
/*Create a record based cursor- record name is a valid identifier*/
<record_name> <cursor_name>%ROWTYPE;
--cursor based record DECLARE --Declare an parameterized explicit cursor emp_cur CURSOR emp_cur(emp_id NUMBER) IS SELECT FIRST_NAME, EMAIL FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = emp_id; emp_rec emp_cur%ROWTYPE; BEGIN -- Open cursor by passing a parameter OPEN emp_cur(7); --Fetch record and pass it to record of cursor type FETCH emp_cur INTO emp_rec; IF emp_rec.FIRST_NAME IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE(emp_rec.FIRST_NAME || ' and ' || emp_rec.EMAIL); else DBMS_OUTPUT.PUT_LINE(' NO record found!!!' ); END IF; CLOSE emp_cur; END;
Programmer/user defined record :- PL/SQL offers programmer to create their own record structure(complete control over - number, names, and datatypes of fields in the record.) and it is handy, when we deal with multiple tables and views.Below is the general syntax of creating programmer record:
TYPE <type_name> IS RECORD
(<field_name> <datatype>,
<field_name> <datatype>,
...
<field_name> <datatype>
);
DECLARE TYPE emp_compny_record_type IS RECORD( EMP_NAME varchar(50), DEPARTMENT_ID VARCHAR(34), EMAIL employees.email%TYPE, MANAGER_ID EMPLOYEES.MANAGER_ID%TYPE ); emp_compny_rec emp_compny_record_type; BEGIN SELECT first_name||' '||last_name "Name", DEPARTMENT_ID,EMAIL, MANAGER_ID INTO emp_compny_rec FROM employees WHERE EMPLOYEE_ID = 7; DBMS_OUTPUT.put_line('NAME AND EMAIL OF EMPLOYEE is: ' || emp_compny_rec.EMP_NAME || ' and ' || emp_compny_rec.EMAIL||'@devinline.com'); END;
- A RECORD type defined in a PL/SQL block is a local type.A RECORD type defined in a package specification is a public item, can be accessed in PL/SQL context with pkgname[.]record_name.
- A RECORD type defined in a package specification is incompatible with an identically defined local RECORD type. i.e: Package level record type cannot be same as local record even if name is name.
- A RECORD type cannot be created at schema level. Record type is not standalone type.
- A record can be part of another record that is termed as nested record. Following code sahow how it can be declared and used:
DECLARE TYPE location_rec_type IS RECORD( LOCATION_ID NUMBER(9), ORGANIZATION_NAME VARCHAR(23) ); TYPE emp_compny_record_type IS RECORD( EMP_NAME varchar(50), DEPARTMENT_ID VARCHAR(34), EMAIL employees.email%TYPE, MANAGER_ID EMPLOYEES.MANAGER_ID%TYPE, location_rec location_rec_type --Nested record ); emp_compny_record emp_compny_record_type; BEGIN --access nested record like this emp_compny_record.location_rec.LOCATION_ID := 123; emp_compny_record.location_rec.ORGANIZATION_NAME := 'ABC'; DBMS_OUTPUT.put_line('Nested record LOCATION_ID and ORGANIZATION_NAME ' || emp_compny_record.location_rec.LOCATION_ID ||'and'||emp_compny_record.location_rec.ORGANIZATION_NAME); END;
- Record is only accessible in PL/SQL context, not in SQL context except when INTO clause is used with select statement.The INTO clause of an implicit query is the only part of a SQL DML statement in which a PL/SQL record can be referenced.
-- emp_rec is PL/SQL record emp_rec employees%ROWTYPE SELECT * INTO emp_rec FROM EMPLOYEES;
- Default values for individual fields in a record can be assigned with the DEFAULT or := syntax.
TYPE emp_rec_type IS RECORD ( ORG_NAME employees.orgname%TYPE DEFAULT 'CMK', Address employees.address%TYPE := 'XYZ, #306' );
- Record be NOT NULL (in which case you must also assign a default value).
- NULL assignment is allowed for record, all fields reset to NULL.(valid from oracle 7.3)
Values assignment to and from Record
There are four ways to alter record(modify values of fields) in PL/SQL. Consider following record for further description.- Direct field assignment with the assignment operator :- Using assignment operator (:= ) values of each filed can be modified. Refer case 1 in following code lines.
- SELECT INTO from an implicit cursor:- INTO clause of select statement passes query result into record. Query result and record should be structurally similar.Refer case 2 in following code line.
- FETCH INTO from an explicit cursor:- Explicit cursor uses FETCH to populate record and it can be done in two ways - directly fetch and assign to record or assign value to each field of record. Refer below code line case 3.
- Aggregate assignment:- Values of one record can be assigned to another in one shot, no explicit for each field mapping(provided structure of both records are same).
DECLARE TYPE emp_compny_record_type IS RECORD( FIRST_NAME employees.FIRST_NAME%TYPE, EMAIL employees.email%TYPE ); emp_record emp_compny_record_type; emp_record_duplicate emp_compny_record_type; CURSOR emp_cur(emp_id NUMBER) IS SELECT FIRST_NAME, EMAIL FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = emp_id; BEGIN /*case 1-Direct field assignment */ emp_record.FIRST_NAME := 'NIKHIL'; emp_record.EMAIL := 'DUMMY@devinline.com'; /*case 2 - SELECT INTO from an implicit cursor*/ SELECT FIRST_NAME,EMAIL INTO emp_record from HR.EMPLOYEES where EMPLOYEE_ID = 7; /*case 3 - FETCH INTO from an explicit cursor*/ OPEN emp_cur(7); FETCH emp_cur INTO emp_record; --OR FETCH emp_cur INTO emp_record.FIRST_NAME, emp_record.EMAIL; /*case 4 - Aggregate assignment - no field by field mapping*/ emp_record_duplicate := emp_record; DBMS_OUTPUT.put_line('Duplicate record FIRST_NAME and EMAIL '|| emp_record_duplicate.FIRST_NAME || ' and ' ||emp_record_duplicate.EMAIL); END;
This is all about record type in PL/SQL. In next post we will learn about reference type in PL/SQL- PL/SQL cursors.
Previous: PL/SQL Collections(Array, VARRAY and Nested Tables) Next: PL/SQL Implicit and Explicit cursor
0 comments:
Post a Comment