PL/SQL Records

A PL/SQL Record is a group of related data items stored in individual fields, each with its own attribute name and data type. To access any field of a record, we use the dot (.) operator.

PL/SQL can handle the following type of records:

• Table Based Record.
• Cursor Based Record.
• User Define Records.

Table Based Record:

%ROWTYPE attributes enables a programmer to create table based and cursor based records.

We are using the EMP table to show the concept of Table Based Record.

DECLARE 
  emp_rec emp%rowtype; 
BEGIN 
   SELECT * into emp_rec 
   FROM emp
   WHERE empno=7839 ;  
   dbms_output.put_line('Employee ID: ' || emp_rec.empno); 
   dbms_output.put_line('Employee Name: ' || emp_rec.ename); 
   dbms_output.put_line('Employee Department: ' || emp_rec.deptno); 
   dbms_output.put_line('Employee Salary: ' || emp_rec.sal); 
END;  

Cursor Based Record:

For Cursor Based Record also we are using  %ROWTYPE. To show the concept of Cursor Based Record we are using MEP table.

DECLARE 
   CURSOR emp_cur is 
      SELECT EMPNO, ENAME, SAL  
      FROM emp; 
   emp_rec emp_cur%rowtype; 
BEGIN 
   OPEN emp_cur; 
   LOOP 
      FETCH emp_cur into emp_rec; 
      EXIT WHEN emp_cur%notfound; 
      DBMS_OUTPUT.put_line(emp_rec.empno || ' ' || emp_rec.ename); 
   END LOOP; 
END;

User Define Record:

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 employee details. 

You might want to track the following attributes about each employee−

• Employee Name
• Employee ID
• Employee SAL
• Employee Department Number

Now we are creating the above record

DECLARE
TYPE employee_details_typ IS RECORD
(emp_name varchar2(50),
 emp_id   number,
emp_sal   number,
emp_dent_no number
);

/*Now we are declaring record-name  type_name;*/

EMP_record1 employee_details_typ ;
EMP_record2 employee_details_typ ;
.....

Example:

DECLARE 
  TYPE employee_details_typ IS RECORD
(emp_name varchar2(50),
 emp_id   number,
emp_sal   number,
emp_dent_no number
);
   emp_datils1 employee_details_typ;
   emp_datils2 employee_details_typ;
BEGIN 
   -- employee1 specification 
   emp_datils1.emp_name  := 'Rabindra'; 
   emp_datils1.emp_id := 2315;  
   emp_datils1.emp_sal := 50000; 
   emp_datils1.emp_dent_no := 10;  
   -- employee1  specification 
   emp_datils2.emp_name  := 'Samir'; 
   emp_datils2.emp_id := 2316;  
   emp_datils2.emp_sal := 50000; 
   emp_datils2.emp_dent_no := 20;   
  

END; 

BULK COLLECT in Oracle

The BULK COLLECT clause tell to SQL engine to bilk bind output collect before returning them to PL/SQL engine, we can use BULK COLLECT in SELECT INTO ,FETCH INTO ,RETURNING INTO clause.


BULK COLLECT in SELECT INTO Clause:

BULK COLLECT in FETCH INTO Clause:

BULK COLLECT in RETURNING Clause:

BULK COLLECT With LIMIT:
Collection is stored in the PGA memory, not the SGA memory. SGA memory is sared by all sessions connected by Oracle Database but PGA memory is allocated for each session. Thus if a program requires 5MB of memory to populate a collection and there are 100 simultaneous connections, that program causes the consumption of 500MB of PGA memory, i assition to the memory allocated to the SGA. 

To avoid this memory problem Oracle use the LIMIT clause. 
Example:

DECLARE

CURSOR emp_cur  IS 
 SELECT * FROM EMP;

TYPE emp_aat IS TABLE OF emp_cur%ROWTYPE
INDEX BY PLS_INTEGER;
l_emp emp_aat;
V_LIMIT_VAL NUMBER :=5;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur BULK COLLECT INTO l_emp LIMIT V_LIMIT_VAL;
FOR IDX IN 1..l_emp.COUNT
LOOP
 --write your logic
 null;
END LOOP;
END LOOP;
CLOSE emp_cur;

END;
/

Bulk Bind in Oracle

Bulk Bind or we can say FORALL in Oracle.
The assigning of values to PL/SQL variables in SQL statements is called binding.The binding of an entire collection at once is called bulk binding.

Oracle use two engine to process the PL/SQL code.The PL/SQL engine accepts any valid PL/SQL block or subprogram and SQL engine executes the SQL statements.

Check the below figure how context switch between the PL/SQL and SQL engines.

• PL/SQL engine executes procedural statements and sends SQL statements to the SQL engine.
• SQL engine executes the SQL statements and returns data to the PL/SQL engine(switch between the PL/SQL and SQL engines ).
• If context switch between the PL/SQL and SQL engines is many then it is performance issue.
• That can happen when SQL statements execute inside a loop using collection (index-by table, nested table, varray, or host array) elements as bind variables.

 Check the below example:

DECLARE
TYPE empno_type is table of emp.empno%type
index by pls_integer;
v_emp_no  empno_type;
BEGIN
select empno bulk collect into v_emp_no 
from emp where deptno in (10,20);
for i in v_emp_no.first..v_emp_no.last
loop
update emp set sal=sal+1000 where empno=v_emp_no(i).empno;
commit;
end loop;
END;

in this program UPDATE statement is sent to the SQL engine with each iteration of the FOR loop.

[NB:In such cases, if the SQL statement affects five or more database rows, the use of bulk binds can improve performance considerably.]

Using BULK BIND to improve performance:

• Bulk binds improve performance by minimizing the number of context switches between the PL/SQL and SQL engines. 
• With bulk binds, entire collections(not just individual elements) are passed to the SQL engines for UPDATE the record at once.

Example:

DECLARE
 TYPE empno_type is table of emp.empno%type
 index by pls_integer;
 v_emp_no  empno_type;
BEGIN
 select empno bulk collect into v_emp_no 
 from emp where deptno in (10,20);
 forall i in v_emp_no.first..v_emp_no.last
    update emp set sal=sal+1000 where empno=v_emp_no(i);
commit;

END;

FORALL Statement:

• The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine.

• It allows only DML (INSERT,UPDATE AND DELETE)

• The collection subscript cannot be an expression.

• All collection elements in the specified range must exist. If an element is missing or was deleted, you get an error.

Example:

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   depts NumList := NumList(10, 20, 30, 40);
BEGIN
   depts.DELETE(3);  -- delete third element
   FORALL i IN depts.FIRST..depts.LAST
      DELETE FROM emp WHERE deptno = depts(i);
      -- raises an "element does not exist" exception
END;

FORALL with SAVE EXCEPTION:

Add SAVE EXCEPTIONS to a FORALL statement, and Oracle Database will save any exceptions that are raised during the execution of individual insert, update, delete, or merge statements generated by the FORALL.

• Using FORALL  i am inserting 100 record into a table, after 50 record failed with error.Then remaining record will not insert into the table. 
• Using FORALL with SAVE EXCEPTION , the error record will ignore( or error record will insert into your error log table) and remaining record will insert .

Create one table call exception_test.
Example:

  DECLARE

  TYPE t_tab IS TABLE OF exception_test%ROWTYPE;

  l_tab          t_tab := t_tab();
  l_error_count  NUMBER;
  
  ex_dml_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);
BEGIN
  -- Fill the collection.
  FOR i IN 1 .. 100 LOOP
    l_tab.extend;
    l_tab(l_tab.last).id := i;
  END LOOP;

  -- Cause a failure.
  l_tab(50).id := NULL;
  l_tab(51).id := NULL;
  
  EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';

  -- Perform a bulk operation.
  BEGIN
    FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS
      INSERT INTO exception_test
      VALUES l_tab(i);
  EXCEPTION
    WHEN ex_dml_errors THEN
      l_error_count := SQL%BULK_EXCEPTIONS.count;
      DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
      FOR i IN 1 .. l_error_count LOOP
        DBMS_OUTPUT.put_line('Error: ' || i || 
          ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
          ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
      END LOOP;
  END;
END;
/