PL/SQL - SPLessons

PL/SQL Records

Chapter 26

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PL/SQL Records

PL/SQL Records

shape Description

Data structures are PLSQL Records that can dominant the components of various types. It comprises of various sections comparable to the database table containing the rows.

PLSQL Records are additional sort of database that oracle permits to be characterized as a plausibility. Complex datatypes are said to be records that represent a sequence of various scalar datatypes like varchar, number, and char. Every record in the scalar data sorts dominant the values. It can be imagined as a data containing the rows and consists of each and every essence of a row.

PL/SQL can deal with the following sorts of records:

Client characterized records

shape Description

PL/SQL gives client portrayed record sorts that permits to declare various structural records. This comprises of various fields like monitoring the catalogs in a library. Firstly, one needs to track the accompanying characteristics about every book:

shape Syntax

TYPE type_name IS RECORD
(field1 data_type1 [NOT NULL]:=[DEFAULT VALUE],
field2 data_type2 [NOT NULL]:=[DEFAULT VALUE],
….
fieldn data_type3 [NOT NULL]:=[DEFAULT VALUE]);

Type_name => Is a composite type function.

Data types =>The data types values assigned in the table.

NOT NULL => Should be assigned a value.

Default value => Is a built_in function.

shape Examples

The below example describes the Client characterized PLSQL Records.

SQL> SET SERVEROUTPUT ON SIZE 1000000'
Usage: SET SERVEROUTPUT { ON | OFF } [SIZE {n | UNL[IMITED]}]
             [ FOR[MAT] { WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED] } ]
SQL> DECLARE
  2  TYPE t_name IS RECORD(
  3  first_name employees.first_name%TYPE,
  4  last_name employees.last_name%TYPE);
  5  r_name t_name; --name record
  6  n_emp_id employees.employee_id%TYPE:=200;
  7  BEGIN
  8  SELECT first_name,
  9         last_name
 10  INTO r_name
 11  FROM employees
 12  WHERE employee_id = n_emp_id;
 13  -- print out the employee's name
 14  DBMS_OUTPUT.PUT_LINE(r_name.first_name||','||r_name.last_name);
 15  END;
 16  /
PL/SQL program successfully completed

Cursors based records

shape Description

Cursor based records can be placed on another cursor. Essentially, characterize a cursor and utilize %ROWTYPE to disclose a record with the cursor variable. The records of these fields are described as  segments in the cursors SELECT articulation. 

shape Examples

The below example describes the Cursors based PLSQL Records:

SQL> DECLARE
  2  CURSOR customer_cur is
  3  SELECT id,name,adderss
  4  from customer1;
  5  customer_rec customer_cur%rowtype;
  6  begin
  7  open customer_cur;
  8  loop
  9  FETCH customer_cur into customer_rec;
 10  EXIT WHEN customer_cur%notfound;
 11  DBMS_OUTPUT.PUT_LINE(customer_rec.id||' '||customer_rec.name);
 12  END LOOP;
 13  END;
 14  /
    CUST_ID       CUST_NAME           
 -----------   ---------------- 
         1         shah                 
         2         mike                 
         3         maddie              
PL/SQL program has successfully completed.

In the above example, the cursor_rec is combined with customer1 table, display cust_id and cust name values.

Table based records

shape Description

The %ROWTYPE attributes allows users to make cursor based and table based records.

shape Examples

The below example describes the table based records.

SQL> select * from customer1;

   CUST_ID CUST_NAME            ADDRESS
---------- -------------------- --------------------
         1 shah                 hongkong
         2 mike                 holland
         3 maddie               swiss

SQL> declare
  2  customer1_rec customer1%rowtype;
  3  begin
  4  select * into customer1_rec
  5  from customer1_rec
  6  where id=3;
  7  dbms_output.put_line('Cust_ID:'||customer1_rec.id);
  8  dbms_output.put_line('Cust_Name:||customer1_rec.name);
  9  dbms_output.put_line('Address:||customer1_rec.address);
 10  END;
 11  /
Cust_ID  Cust Name  Address
------- ---------  -------
  3       Maddie    Swiss
PL/SQL program successfully completed.

The above table based records program displays the customer id 3 records, cust id, cust name, and address values like 3, maddie and swiss.

Summary

shape Key Points

  • PLSQL Records – Data structures are the PL/SQL records.
  • Client characterized records – Allows you to define different record structures.
  • Cursor based records – It will place the records on another cursor.
  • Table based records – Allows the client to make a cursor based on table based records.