Monday, June 4, 2018

PL/SQL - Records

n 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('Customer ID: ' || customer_rec.id); 
   dbms_output.put_line('Customer Name: ' || customer_rec.name); 
   dbms_output.put_line('Customer Address: ' || customer_rec.address); 
   dbms_output.put_line('Customer Salary: ' || 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 || ' ' || 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_name1  datatype1  [NOT NULL]  [:= DEFAULT EXPRESSION], 
   field_name2   datatype2   [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); 
book1 books; 
book2 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); 
   book1 books; 
   book2 books; 
BEGIN 
   -- Book 1 specification 
   book1.title  := 'C Programming'; 
   book1.author := 'Nuha Ali ';  
   book1.subject := 'C Programming Tutorial'; 
   book1.book_id := 6495407;  
   -- Book 2 specification 
   book2.title := 'Telecom Billing'; 
   book2.author := 'Zara Ali'; 
   book2.subject := 'Telecom Billing Tutorial'; 
   book2.book_id := 6495700;  
  
  -- Print book 1 record 
   dbms_output.put_line('Book 1 title : '|| book1.title); 
   dbms_output.put_line('Book 1 author : '|| book1.author); 
   dbms_output.put_line('Book 1 subject : '|| book1.subject); 
   dbms_output.put_line('Book 1 book_id : ' || book1.book_id); 
   
   -- Print book 2 record 
   dbms_output.put_line('Book 2 title : '|| book2.title); 
   dbms_output.put_line('Book 2 author : '|| book2.author); 
   dbms_output.put_line('Book 2 subject : '|| book2.subject); 
   dbms_output.put_line('Book 2 book_id : '|| book2.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 Tutorial 
Book 2 book_id : 6495700  

PL/SQL procedure successfully completed. 

Records as Subprogram Parameters

You can pass a record as a subprogram parameter just as you pass any other variable. You can also access the record fields in the same way as you accessed in the above example −
DECLARE 
   type books is record 
      (title  varchar(50), 
      author  varchar(50), 
      subject varchar(100), 
      book_id   number); 
   book1 books; 
   book2 books;  
PROCEDURE printbook (book books) IS 
BEGIN 
   dbms_output.put_line ('Book  title :  ' || book.title); 
   dbms_output.put_line('Book  author : ' || book.author); 
   dbms_output.put_line( 'Book  subject : ' || book.subject); 
   dbms_output.put_line( 'Book book_id : ' || book.book_id); 
END; 
   
BEGIN 
   -- Book 1 specification 
   book1.title  := 'C Programming'; 
   book1.author := 'Nuha Ali ';  
   book1.subject := 'C Programming Tutorial'; 
   book1.book_id := 6495407;
   
   -- Book 2 specification 
   book2.title := 'Telecom Billing'; 
   book2.author := 'Zara Ali'; 
   book2.subject := 'Telecom Billing Tutorial'; 
   book2.book_id := 6495700;  
   
   -- Use procedure to print book info 
   printbook(book1); 
   printbook(book2); 
END; 
/  
When the above code is executed at the SQL prompt, it produces the following result −
Book  title : C Programming 
Book  author : Nuha Ali 
Book subject : C Programming Tutorial 
Book  book_id : 6495407 
Book title : Telecom Billing 
Book author : Zara Ali 
Book subject : Telecom Billing Tutorial 
Book book_id : 6495700  

PL/SQL procedure successfully completed. 

PL/SQL - Collections

In this chapter, we will discuss the Collections in PL/SQL. A collection is an ordered group of elements having the same data type. Each element is identified by a unique subscript that represents its position in the collection.
PL/SQL provides three collection types −
  • Index-by tables or Associative array
  • Nested table
  • Variable-size array or Varray
Oracle documentation provides the following characteristics for each type of collections −
Collection TypeNumber of ElementsSubscript TypeDense or SparseWhere CreatedCan Be Object Type Attribute
Associative array (or index-by table)UnboundedString or integerEitherOnly in PL/SQL blockNo
Nested tableUnboundedIntegerStarts dense, can become sparseEither in PL/SQL block or at schema levelYes
Variablesize array (Varray)BoundedIntegerAlways denseEither in PL/SQL block or at schema levelYes
We have already discussed varray in the chapter 'PL/SQL arrays'. In this chapter, we will discuss the PL/SQL tables.
Both types of PL/SQL tables, i.e., the index-by tables and the nested tables have the same structure and their rows are accessed using the subscript notation. However, these two types of tables differ in one aspect; the nested tables can be stored in a database column and the index-by tables cannot.

Index-By Table

An index-by table (also called an associative array) is a set of key-valuepairs. Each key is unique and is used to locate the corresponding value. The key can be either an integer or a string.
An index-by table is created using the following syntax. Here, we are creating an index-by table named table_name, the keys of which will be of the subscript_type and associated values will be of the element_type
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type; 
 
table_name type_name;

Example

Following example shows how to create a table to store integer values along with names and later it prints the same list of names.
DECLARE 
   TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20); 
   salary_list salary; 
   name   VARCHAR2(20); 
BEGIN 
   -- adding elements to the table 
   salary_list('Rajnish') := 62000; 
   salary_list('Minakshi') := 75000; 
   salary_list('Martin') := 100000; 
   salary_list('James') := 78000;  
   
   -- printing the table 
   name := salary_list.FIRST; 
   WHILE name IS NOT null LOOP 
      dbms_output.put_line 
      ('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name))); 
      name := salary_list.NEXT(name); 
   END LOOP; 
END; 
/
When the above code is executed at the SQL prompt, it produces the following result −
Salary of James is 78000 
Salary of Martin is 100000 
Salary of Minakshi is 75000 
Salary of Rajnish is 62000  

PL/SQL procedure successfully completed.

Example

Elements of an index-by table could also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the CUSTOMERS table stored in our database as −
Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+  
DECLARE 
   CURSOR c_customers is 
      select name from customers; 

   TYPE c_list IS TABLE of customers.Name%type INDEX BY binary_integer; 
   name_list c_list; 
   counter integer :=0; 
BEGIN 
   FOR n IN c_customers LOOP 
      counter := counter +1; 
      name_list(counter) := n.name; 
      dbms_output.put_line('Customer('||counter||'):'||name_lis t(counter)); 
   END LOOP; 
END; 
/ 
When the above code is executed at the SQL prompt, it produces the following result −
Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal  

PL/SQL procedure successfully completed

Nested Tables

nested table is like a one-dimensional array with an arbitrary number of elements. However, a nested table differs from an array in the following aspects −
  • An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically.
  • An array is always dense, i.e., it always has consecutive subscripts. A nested array is dense initially, but it can become sparse when elements are deleted from it.
A nested table is created using the following syntax −
TYPE type_name IS TABLE OF element_type [NOT NULL]; 
 
table_name type_name; 
This declaration is similar to the declaration of an index-by table, but there is no INDEX BY clause.
A nested table can be stored in a database column. It can further be used for simplifying SQL operations where you join a single-column table with a larger table. An associative array cannot be stored in the database.

Example

The following examples illustrate the use of nested table −
DECLARE 
   TYPE names_table IS TABLE OF VARCHAR2(10); 
   TYPE grades IS TABLE OF INTEGER;  
   names names_table; 
   marks grades; 
   total integer; 
BEGIN 
   names := names_table('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz'); 
   marks:= grades(98, 97, 78, 87, 92); 
   total := names.count; 
   dbms_output.put_line('Total '|| total || ' Students'); 
   FOR i IN 1 .. total LOOP 
      dbms_output.put_line('Student:'||names(i)||', Marks:' || marks(i)); 
   end loop; 
END; 
/  
When the above code is executed at the SQL prompt, it produces the following result −
Total 5 Students 
Student:Kavita, Marks:98 
Student:Pritam, Marks:97 
Student:Ayan, Marks:78 
Student:Rishav, Marks:87 
Student:Aziz, Marks:92  

PL/SQL procedure successfully completed. 

Example

Elements of a nested table can also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the CUSTOMERS table stored in our database as −
Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+ 
DECLARE 
   CURSOR c_customers is  
      SELECT  name FROM customers;  
   TYPE c_list IS TABLE of customerS.No.ame%type; 
   name_list c_list := c_list(); 
   counter integer :=0; 
BEGIN 
   FOR n IN c_customers LOOP 
      counter := counter +1; 
      name_list.extend; 
      name_list(counter)  := n.name; 
      dbms_output.put_line('Customer('||counter||'):'||name_list(counter)); 
   END LOOP; 
END; 
/ 
When the above code is executed at the SQL prompt, it produces the following result −
Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal  

PL/SQL procedure successfully completed. 

Collection Methods

PL/SQL provides the built-in collection methods that make collections easier to use. The following table lists the methods and their purpose −
S.NoMethod Name & Purpose
1
EXISTS(n)
Returns TRUE if the nth element in a collection exists; otherwise returns FALSE.
2
COUNT
Returns the number of elements that a collection currently contains.
3
LIMIT
Checks the maximum size of a collection.
4
FIRST
Returns the first (smallest) index numbers in a collection that uses the integer subscripts.
5
LAST
Returns the last (largest) index numbers in a collection that uses the integer subscripts.
6
PRIOR(n)
Returns the index number that precedes index n in a collection.
7
NEXT(n)
Returns the index number that succeeds index n.
8
EXTEND
Appends one null element to a collection.
9
EXTEND(n)
Appends n null elements to a collection.
10
EXTEND(n,i)
Appends n copies of the ith element to a collection.
11
TRIM
Removes one element from the end of a collection.
12
TRIM(n)
Removes n elements from the end of a collection.
13
DELETE
Removes all elements from a collection, setting COUNT to 0.
14
DELETE(n)
Removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.
15
DELETE(m,n)
Removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n)does nothing.

Collection Exceptions

The following table provides the collection exceptions and when they are raised −
Collection ExceptionRaised in Situations
COLLECTION_IS_NULLYou try to operate on an atomically null collection.
NO_DATA_FOUNDA subscript designates an element that was deleted, or a nonexistent element of an associative array.
SUBSCRIPT_BEYOND_COUNTA subscript exceeds the number of elements in a collection.
SUBSCRIPT_OUTSIDE_LIMITA subscript is outside the allowed range.
VALUE_ERRORA subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range.