Composite data types PLSQL table 9 1 Composite

Composite data types_ PL/SQL table 9 ﻣﺤﺎﺿﺮﺓ 1

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 2

Declaring PL/SQL � 1. 2. � � Declaration of PL/SQL tables is done in two steps : Define a TYPE. 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; 3

� 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; 4

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’; 5

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. 6

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; / 7

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. � 8

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); 9

�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; 10

�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); 11

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; 12

� 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; FETCH )ﺗﺴﺘﺨﺪﻡ ﻣﻊ ﺍﻝ cursor( 13

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

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; 15

�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 ; 16