Composite data types PLSQL table Composite data types

Composite data types_ PL/SQL table

Composite data types • PL/SQL provides three composite data types : TABLE , ROCORD , and VARRAY. • Objects of type TABLE are called PL/SQL tables , which are modeled as database tables. • PL/SQL tables use a primary key as array access to rows. • The size of a PL/SQL is un constrained, i. e, the table size grows as new rows are added. • PL/SQL tables can have one column and a primary key , neither of these can named. • The column can have any data type , but the primary key must be of the type BINARY_INTEGER. • You can visualize a PL/SQL to be a single dimension vertical array , which can hold unlimited elements

Declaring PL/SQL • Declaration of PL/SQL tables is done in two steps : 1. Define a TYPE. 2. Declare PL/SQL tables of that type. • TABLE types can be defined in the declarative part of any block , subprogram, or package using following syntax : • TYPE<type name> IS TABLE OF {column type | varible datatype} [not null] INDEX BY BINARY _INTERGER;

• The clause INDEX BY BINARY_INTEGER is a mandatory feature of the PL/SQL table declaration. • A PL/SQL table of the type defined can be declared in the following way : <plsql_table_variablename><typename>; • For example , in the declarion section a type of PL/SQL table can be defined as follows: TYPE nametabtyp IS TABLE OF CHAR(10) INDEX BY BINARY_INTEGER; • Now PL/SQL tables of the type nametabtyp can be declared as follows: Name_tab nametabtyp;

Referencing PL/SQL tables • To reference rows in a PL/SQL table , a primary key value has to be specified using the following array _ like syntax : • <plsql_tab_variablename>(index_subscript) • For example , to reference rows in PL/SQL table name_tab, you would use: name_tab(3); • To assign the value of a PL/SQL expression to a specific row you can use the following syntax : <plsql_tab_variablename>(index_subscript): =plsql_ expression; • Example: name_tab(5): =‘MICKY’;

Composite data types _ PL/SQL RECORD types • Objects of type RECORD are called records. • Unlike PL/SQL tables , records have uniquely name fields , which can belong to different data types.

DECLARE TYPE nametabtyp IS TABLE OF CHAR(10) INDEX BY BINARY_INTEGER; Name_tab nametabtyp; BEGIN Num: =1; LOOP Name_table (num) : =‘Mikey’; num: = num+1; Exit when num >5 ; END LOOP; FOR i IN 1. . 5 LOOP DBMS_OUTPUT. PUT_LINE(‘Name’|| I ||’: ’|| name_tab(i)); END LOOP; END; /

• Declaring Records Like PL/SQL tables , records must be declared in two steps: 1. Define the RECORD type. 2. Declare user_defined records of that type. • The RECORD type can be declared in the declarative part of any block, subprogram , or package using the following syntax: TYPE typename IS RECORD (fieldname 1 { fieldtype | variable%TYPE | table. column%TYPE| table%ROWTYPE} [not null], ………. . fieldname. N { fieldtype | variable%TYPE | table. column%TYPE| table%ROWTYPE} [not null]); • The %TYPE and %ROWTYPE attribute can be used to specify a field data type.

Example • For example , to store the values of route_code and the fares as a structure , a type of record can be declared as follows: DECLARE TYPE route_rec_typ IS RECORD (route_code varchar 2(7) NOT NULL : =‘SAN_LOU’; First_fare. first_fare%TYPE, Bus_fare. bus_fare%TYPE, Eco_fare. eco_fare%TYPE);

• Note that the field declarations are like a variable declaration. Each field has a unique name and specific data type. • The NOT NULL constraint can be added to any field declaration , provided they are initialized. • In the above example , once type route_rec_typ is defined , records can be declared of that type as follows : • Route_rec route_rec_typ;

• Unlike PL/SQL tables , a record can be intialized in its declaration as shown below: DECLARE TYPE timetyp IS RECORD (second SMALLINT : = 0, Minute SMALLINT: = 0, Hour SMALLINT : = 0);

Referncing Records • To reference individual field in a record , dot notation and the following syntax are to be used: Record_name. field_name; • Instead of assigning values separately to each field in a record, values can be assigned to all field at once. This can be done in two ways. • First , assign one record to another if they belong to the same datatype. DECLARE TYPE route_rec_typ IS RECORD(…. . ); Route_rec 1 route_rec_typ; Route_rec 2 route_rec_typ; …………. The following assignment is legal. BEGIN Route_rec 1: = route_rec 2;

• Second , a list of column values can be assigned to a record by using the SELECT or FETCH statement. Just make sure the column names appear in the same order as the fields in the record. BEGIN SELECT route_code, first_fare, bus_fare, eco_fare INTO route_rec 1 From fare Where route_code= ‘SAN_LOU’; ……… END; ( cursor )ﺗﺴﺘﺨﺪﻡ ﻣﻊ ﺍﻝ FETCH

Nested records • PL/SQL allows for declaration and reference of nested records. • A record can be the component of another record. • For example:

DECLARE TYPE timetyp IS RECORD (minute SMALLINT, Hour SMALLINT); TYPE meetingtyp IS RECORD (day date, Time timetyp, -- nested record Place CHAR(20), Purpose CHAR(50)); TYPE partytyp IS RECORD (Day DATE, Time timetyp, -- nested record Bus_fare CHAR(15)); Meeting MEETINGTYP; Seminar MEETINGTYP; Party PARTYTYP; …… BEGIN Meeting. day: =’ 26 -JUN-91’; Meeting. time. minute: =45; Meeting. time. hour: =10; …… END;

• PL/SQL allows for an assignment of one nested record to another belonging to the same data type. • For example : Seminar. time: = meeting. time; • PL/SQL also allows for an assignment of one nested record to another if the containing records belong to different data types • For example : Party. time: = meeting. time ;