CCIT $426$: Database $-2$

Lab $6$: Exception Handling and Triggers

LAB $6$ Content

PL$/$SQL $-$ Records

In this chapter, we will discuss Records in PL$/$SQL$.$ A$$record$$is a data structure that can hold data items of different kinds. Records consist of different fields, similar to a row of a database table.

For example, you want to keep track of your books in a library. You might want to track the following attributes about each book, such as Title, Author, Subject, Book ID$.$ A record containing a field for each of these items allows treating a BOOK as a logical unit and allows you to organize and represent its information in a better way.

PL$/$SQL can handle the following types of records $$

$$ Table$-$based

$$ Cursor$-$based records

$$ User$-$defined records

Table$-$Based Records

The $\%$ROWTYPE attribute enables a programmer to create$$table$-$based$$and$$cursorbased$$records$.$

The following example illustrates the concept of$$table$-$based$$records$.$ We will be using the CUSTOMERS table we had created and used in the previous chapters $$

DECLARE

customer$\_$rec customers$\%$rowtype;

BEGIN

SELECT $*$ into customer$\_$rec

FROM customers

WHERE id $=5$;

dbms$\_$output.put$\_$line$(\text{'}$Customer ID: $\text{'}||$ customer$\_$rec.id$)$;

dbms$\_$output.put$\_$line$(\text{'}$Customer Name: $\text{'}||$ customer$\_$rec.name$)$;

dbms$\_$output.put$\_$line$(\text{'}$Customer Address: $\text{'}||$ customer$\_$rec.address$)$;

dbms$\_$output.put$\_$line$(\text{'}$Customer Salary: $\text{'}||$ customer$\_$rec.salary$)$;

END;

$/$

When the above code is executed at the SQL prompt, it produces the following result $$

Customer ID: $5$

Customer Name: Hardik

Customer Address: Bhopal

Customer Salary: $9000$

PL$/$SQL procedure successfully completed.

Cursor$-$Based Records

The following example illustrates the concept of$$cursor$-$based$$records$.$ We will be using the CUSTOMERS table we had created and used in the previous chapters $$

DECLARE

CURSOR customer$\_$cur is

SELECT id$,$ name, address

FROM customers;

customer$\_$rec customer$\_$cur$\%$rowtype;

BEGIN

OPEN customer$\_$cur;

LOOP

FETCH customer$\_$cur into customer$\_$rec;

EXIT WHEN customer$\_$cur$\%$notfound;

DBMS$\_$OUTPUT.put$\_$line$($customer$\_$rec.id $||\text{'}\text{'}||$ customer$\_$rec.name$)$;

END LOOP;

END;

$/$

When the above code is executed at the SQL prompt, it produces the following result $$

$1$ Ramesh

$2$ Khilan

$3$ kaushik

$4$ Chaitali

$5$ Hardik

$6$ Komal

PL$/$SQL procedure successfully completed.

User$-$Defined Records

PL$/$SQL provides a user$-$defined record type that allows you to define the different record structures. These records consist of different fields. Suppose you want to keep track of your books in a library. You might want to track the following attributes about each book $$

$$ Title

$$ Author

$$ Subject

$$ Book ID

Defining a Record

The record type is defined as $$

TYPE

type$\_$name IS RECORD

$($ field$\_$name$1$ datatype$1[$NOT NULL$][$:$=$ DEFAULT EXPRESSION$],$

field$\_$name$2$ datatype$2[$NOT NULL$][$:$=$ DEFAULT EXPRESSION$],$

$...$

field$\_$nameN datatypeN $[$NOT NULL$][$:$=$ DEFAULT EXPRESSION$)$;

record$-$name type$\_$name;

The Book record is declared in the following way $$

DECLARE

TYPE books IS RECORD

$($title varchar$(50),$

author varchar$(50),$

subject varchar$(100),$

book$\_$id number$)$;

book$1$ books;

book$2$ books;

Accessing Fields

To access any field of a record, we use the dot$(.)$operator$.$ The member access operator is coded as a period between the record variable name and the field that we wish to access. Following is an example to explain the usage of record $$

DECLARE

type books is record

$($title varchar$(50),$

author varchar$(50),$

subject varchar$(100),$

book$\_$id number$)$;

book$1$ books;

book$2$ books;

BEGIN

$--$ Book $1$ specification

book$1.$title :$=\text{'}$C Programming';

book$1.$author :$=$ 'Nuha Ali $\text{'}$;

book$1.$subject :$=\text{'}$C Programming Tutorial';

book$1.$book$\_$id :$=6495407$;

$--$ Book $2$ specification

book$2.$title :$=$ 'Telecom Billing';

book$2.$author :$=$ 'Zara Ali';

book$2.$subject :$=$ 'Telecom Billing Tutorial';

book$2.$book$\_$id :$=6495700$;

$--$ Print book $1$ record

dbms$\_$output.put$\_$line$(\text{'}$Book $1$ title : $\text{'}||$ book$1.$title$)$;

dbms$\_$output.put$\_$line$(\text{'}$Book $1$ author : $\text{'}||$ book$1.$author$)$;

dbms$\_$output.put$\_$line$(\text{'}$Book $1$ subject : $\text{'}||$ book$1.$subject$)$;

dbms$\_$output.put$\_$line$(\text{'}$Book $1$ book$\_$id : $\text{'}||$ book$1.$book$\_$id$)$;

$--$ Print book $2$ record

dbms$\_$output.put$\_$line$(\text{'}$Book $2$ title : $\text{'}||$ book$2.$title$)$;

dbms$\_$output.put$\_$line$(\text{'}$Book $2$ author : $\text{'}||$ book$2.$author$)$;

dbms$\_$output.put$\_$line$(\text{'}$Book $2$ subject : $\text{'}||$ book$2.$subject$)$;

dbms$\_$output.put$\_$line$(\text{'}$Book $2$ book$\_$id : $\text{'}||$ book$2.$book$\_$id$)$;

END;

$/$

When the above code is executed at the SQL prompt, it produces the following result $$

Book $1$ title : C Programming

Book $1$ author : Nuha Ali

Book $1$ subject : C Programming Tutorial

Book $1$ book$\_$id : $6495407$

Book $2$ title : Telecom Billing

Book $2$ author : Zara Ali

Book $2$ subject : Telecom Billing Tutor