PL SQL COMPOSITE DATA TYPE

DATA TYPE :

 1) Scalar Data Type :

a) Number

b) Date

c) Varchar2

d) blob

e) clob

2) Composite data Type :

1) PL/SQL Records :

SET SERVEROUTPUT ON;

DECLARE
    TYPE emprectype IS RECORD ( name     VARCHAR2(50),
    hire_date      date,
    dob      DATE ,
    emp_no   number);
    emprec emprectype;
BEGIN
  select first_name || last_name , hire_date,dob,emp_no
  into emprec
  from emp
  where emp_no=100;
    dbms_output.put_line('Employee Name ' || emprec.name || ' has hire dat as  ' || emprec.hire_date  || ' and date of birth is ' || emprec.dob);
END;

insert data into table using records

SET SERVEROUTPUT ON;
DECLARE
    dept_rec   dept%rowtype;
BEGIN
    dept_rec.deptno := 90;
    dept_rec.dname := 'FINANCE';
    INSERT INTO dept VALUES dept_rec;
    COMMIT;
END;

update data into table using records

SET SERVEROUTPUT ON;
DECLARE
    dept_rec   dept%rowtype;
BEGIN
    dept_rec.deptno := 90;
    dept_rec.dname := 'FIN-updated';
    dept_rec.loc := 'USA';
  update dept set row = dept_rec where  deptno=90;
    COMMIT;
END;

2) COLLECTIONS:

a) PL/SQL Table :

Already covered in previous posts

b) VArray :

example 1:

SET SERVEROUTPUT ON;

DECLARE
    TYPE varry_typ IS
        VARRAY ( 10 ) OF NUMBER;
    varr   varry_typ;
BEGIN
    varr := varry_typ ();
    varr.extend ();
    varr(1) := 85;
    varr.extend ();
    varr(2) := 88;
    dbms_output.put_line('varr 1 value is '
                           || varr(1)
                           || ' varr 2 value is '
                           || varr(2) );

END;

Example 2:

SET SERVEROUTPUT ON;

DECLARE
    TYPE varry_typ IS
        VARRAY ( 10 ) OF NUMBER;
    varr   varry_typ;
BEGIN
    varr := varry_typ (11,22,33,44,55,66,77);
    dbms_output.put_line('varr 1 value is '
                           || varr(1)
                           || ' varr 2 value is '
                           || varr(2) );

END;

Example 3:

varr.Exists(5) means it will written true if 5th element in the array exists. if not exist then will written value as false.

varr.first will return you first element sequence not value and varr.last will written the last available sequence  not value in varray.

varr.PRIOR(2) will give the value in the 1st element value.

varr.NEXT(2) will give the value of 3rd element

varr.EXTEND() will allocate the memory so that we can assign value tot he element further. without this we can't assign the value to any element of varray. If we want to assign the value to two elements then we have to write this twice.

varr.trim(1)  will delete the last available value from the varray.

varr.trim(2)  will delete the last two available values from the varray.

varr.count  will give you only those values which are not deleted yet. if its trimmed then it won't count those values further.

SET SERVEROUTPUT ON;
DECLARE
    TYPE varry_typ IS VARRAY ( 10 ) OF NUMBER;
    vemp   varry_typ := varry_typ(11,22,33,44,55,66,77);
BEGIN
dbms_output.put_line('vemp count ' || vemp.count);
dbms_output.put_line('vemp limit ' || vemp.limit);
dbms_output.put_line('vemp first ' || vemp.first);
dbms_output.put_line('vemp last ' || vemp.last);
vemp.trim(2);
dbms_output.put_line('after vemp count ' || vemp.count);
dbms_output.put_line('after vemp limit ' || vemp.limit);
dbms_output.put_line('after vemp first ' || vemp.first);
dbms_output.put_line('after vemp last ' || vemp.last);
dbms_output.put_line('vemp 1 ' || vemp(1));
END;

c) Nested tables:

only difference between nested table and varray is the size. in varray size is limited whereas in nested table size is not fixed at the time of design. Also nested tabke is not dense as we can delete any element even from the middle as well. There is no limit function in this as its unbounded.

SET SERVEROUTPUT ON;
DECLARE
--TYPE varry_typ is varray(10) of number;
    TYPE varry_typ IS table OF NUMBER;
    vemp   varry_typ ;
    cursor c1 is
    select emp_no from emp;
    counter number :=1;
BEGIN
vemp :=varry_typ();
for i in c1 loop
vemp.extend();
vemp(counter) := i.emp_no;
dbms_output.put_line( 'vemp(' || counter || ') ' || vemp(counter) );
counter := counter + 1;
end loop;
END;

d) index by tables: associative array;

it has both the feature of nested table and as well as varray too. Only good thing is that in this it s defined by key and value structure means key should always be unique and not null.There is no limit function in this as its unbounded.

SET SERVEROUTPUT ON;

DECLARE
    TYPE l_empnotab IS TABLE OF number INDEX BY PLS_INTEGER;
    l_emparr   l_empnotab;
BEGIN
l_emparr(1) := 56;
l_emparr(3) :=78;
END;

in the above example 1 and 3 are key and 78 and 56 are values.No need to allocate memory in this table.

l_emparr.exists(n) return the true if any key is matching with value on n.

l_emparr.first will return you the smallest key value.

l_emparr.prior(3) will return as 1 because 1 is the key which is below 3 value.

 l_emparr.delete(7,10) means delete the elements from 7 to 10 means delete 7,8,9,10.

Below example to use rowtype in pl/sql table type:

SET SERVEROUTPUT ON;

DECLARE
    TYPE l_empnotab IS
        TABLE OF emp%rowtype INDEX BY PLS_INTEGER;
    l_emparr   l_empnotab;
    i          NUMBER :=1;
    y          emp%rowtype;
BEGIN
    SELECT * BULK COLLECT
    INTO l_emparr
    FROM emp;
    LOOP
        dbms_output.put_line('employee number:' || l_emparr(i).emp_no);
        i := l_emparr.next(i);
        EXIT WHEN i IS NULL;
    END LOOP;
END;