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) );
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;
