PL/SQL data types are two types.
Scalar datatyoe,
Composite Datatypes.
Composite Data Types:
*********************
-->They are two types.
PL/SQL Record
--table based RECORD
--cursor based RECORD
--user defined record
COLLECTION
PL/SQL Record:
***************
-->It is an user defined type which is used to represent different data types into a single unit.
-->A record is a group of related data items stored in fields,each with its own name and datatype.
-->It saves the memory reserved for variable.
-->It is also reducing number of variable declarations.
-->It is also same as structures in C Language.
-->A record type variable is able to hold one single row at a time.
-->This is an user defined type so we are creating two step process.
Syntax:
********
type typename is record
(
attribute1 datatype(size),
attribute2 datatype(size),
......,
......
);
-->variablename typename;
-->A variable of record data type is declarded to hold data from multiple columns of a single record or row.
EX:
***
--SCALAR DATATYPE
DECLARE
LV_ENAME VARCHAR2(30);
LV_SAL NUMBER;
BEGIN
SELECT ENAME,SAL
INTO
LV_ENAME,LV_SAL
FROM EMP
WHERE EMPNO=7369;
DBMS_OUTPUT.PUT_LINE('EMP NAME =: '||LV_ENAME);
DBMS_OUTPUT.PUT_LINE('SALARY =: '||LV_SAL);
END;
/
--COMPOSITE DATATYPE
Table based record :
********************
-->A Table based record data type it can able to hold data of all columns in one record from table.
/
DECLARE
LV_EMP_REC EMP%ROWTYPE; --table based record
BEGIN
SELECT *
INTO
LV_EMP_REC
FROM EMP
WHERE EMPNO=7369;
DBMS_OUTPUT.PUT_LINE('EMP NAME =: '||LV_EMP_REC.ENAME);
DBMS_OUTPUT.PUT_LINE('SALARY =: '||LV_EMP_REC.SAL);
END;
/
declare
cursor c1 is select * from emp;
i emp%rowtype; --table based frecord
begin
open c1;
loop
fetch c1 into i;
dbms_output.put_line(i.empno||','||i.ename||','||i.sal);
exit when c1%notfound;
end loop;
close c1;
end;
/
declare
cursor c1 is select empno,ename,sal from emp;
i c1%rowtype; --cursor based record
begin
open c1;
loop
fetch c1 into i;
dbms_output.put_line(i.empno||','||i.ename||','||i.sal);
exit when c1%notfound;
end loop;
close c1;
end;
/
--USER DEFINED RECORD
DECLARE
TYPE BOOK_TYPE IS RECORD
(
BOOK_ID NUMBER,
TITLE VARCHAR2(20),
AUTHOR VARCHAR2(20),
SUBJECT VARCHAR2(20)
);
BOOK1 BOOK_TYPE;
BOOK2 BOOK_TYPE;
BEGIN
--ASSIGN DATA TO BOOK1
BOOK1.BOOK_ID :='100';
BOOK1.TITLE :='ORACLE #';
BOOK1.AUTHOR :='CHARLES';
BOOK1.SUBJECT :='ORACLE APPS';
--ASSIGN DATA TO BOOK2
BOOK2.BOOK_ID :='200';
BOOK2.TITLE :='PL/SQL #';
BOOK2.AUTHOR :='NELSON';
BOOK2.SUBJECT :='PL/SQL';
--TO PRINT THE DATA
DBMS_OUTPUT.PUT_LINE('BOOK1 DETAILS');
DBMS_OUTPUT.PUT_LINE('BOOK ID IS : '||BOOK1.BOOK_ID);
DBMS_OUTPUT.PUT_LINE('TITLE IS : '||BOOK1.TITLE);
DBMS_OUTPUT.PUT_LINE('AUTHOR IS :' ||BOOK1.AUTHOR);
DBMS_OUTPUT.PUT_LINE('SUBJECT IS :' ||BOOK1.SUBJECT);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('BOOK2 DETAILS');
DBMS_OUTPUT.PUT_LINE('BOOK ID IS : '||BOOK2.BOOK_ID);
DBMS_OUTPUT.PUT_LINE('TITLE IS : '||BOOK2.TITLE);
DBMS_OUTPUT.PUT_LINE('AUTHOR IS :' ||BOOK2.AUTHOR);
DBMS_OUTPUT.PUT_LINE('SUBJECT IS :' ||BOOK2.SUBJECT);
END;
/
DECLARE
TYPE ADDRESS IS RECORD
(
STREET_NAME VARCHAR2(20),
COUNTRY VARCHAR2(20),
STATE VARCHAR2(20),
CITY VARCHAR2(20),
ZIP VARCHAR2(20)
);
TYPE CUSTOMER IS RECORD
(
CUSTOMER_NAME VARCHAR2(20),
SHIP_TO ADDRESS,
BILL_TO ADDRESS
);
LV_CUSTOMER CUSTOMER;
BEGIN
LV_CUSTOMER.CUSTOMER_NAME :='MOHAN REDDY';
---Assign Address
LV_CUSTOMER.SHIP_TO.STREET_NAME :='ROAD NO 1';
LV_CUSTOMER.SHIP_TO.COUNTRY :='INDIA';
LV_CUSTOMER.SHIP_TO.STATE :='TELANGANA';
LV_CUSTOMER.SHIP_TO.CITY :='HYDERABAD';
LV_CUSTOMER.SHIP_TO.ZIP :='500072';
---Bill to Address is ame as Ship to Address
LV_CUSTOMER.bill_to :=LV_CUSTOMER.ship_to;
--Print Bill to Address
dbms_output.put_line(' ');
dbms_output.put_line('Ship To Address');
dbms_output.put_line('Customer Name is : '||LV_CUSTOMER.CUSTOMER_NAME);
dbms_output.put_line('Street Name is : '||LV_CUSTOMER.SHIP_TO.STREET_NAME);
dbms_output.put_line('Country Name is : '||LV_CUSTOMER.SHIP_TO.COUNTRY);
dbms_output.put_line('State Name is : '||LV_CUSTOMER.SHIP_TO.STATE);
dbms_output.put_line('City Name is : '||LV_CUSTOMER.SHIP_TO.CITY);
dbms_output.put_line('Zip Name is : '||LV_CUSTOMER.SHIP_TO.ZIP);
--Print Ship to Address
dbms_output.put_line(' ');
dbms_output.put_line('Bill To Address');
dbms_output.put_line('Street Name is : '||LV_CUSTOMER.BILL_TO.STREET_NAME);
dbms_output.put_line('Country Name is : '||LV_CUSTOMER.BILL_TO.COUNTRY);
dbms_output.put_line('State Name is : '||LV_CUSTOMER.BILL_TO.STATE);
dbms_output.put_line('City Name is : '||LV_CUSTOMER.BILL_TO.CITY);
dbms_output.put_line('Zip Name is : '||LV_CUSTOMER.BILL_TO.ZIP);
END;
/
DECLARE
TYPE T1 IS RECORD (LV_ENAME EMP.ENAME%TYPE,
LV_SAL EMP.SAL%TYPE);
V1 T1;
BEGIN
SELECT ENAME,SAL INTO V1 FROM EMP WHERE EMPNO=7369;
DBMS_OUTPUT.PUT_LINE(V1.LV_ENAME||','||V1.LV_SAL);
END;
/
--PL/SQL RECORD TYPE WITH SINGLE TABLE
DECLARE
TYPE EMP_REC IS RECORD(A1 NUMBER,A2 VARCHAR2(20),A3 VARCHAR2(20),A4 NUMBER);
LV_EMP_REC EMP_REC;
BEGIN
LV_EMP_REC.A1 :=:EMPNO;
SELECT EMPNO,ENAME,JOB,SAL
INTO LV_EMP_REC
FROM EMP
WHERE EMPNO=LV_EMP_REC.A1;
DBMS_OUTPUT.PUT_LINE(LV_EMP_REC.A1||','||LV_EMP_REC.A2||','||LV_EMP_REC.A3||','||LV_EMP_REC.A4);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('EMPNO DOSE NOT EXIST IN EMP TABLE');
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('INPUT VALUE INCOMPATABLE');
END;
/
PL/SQL RECORD TYPE WITH MULTIPLE TABLES.
DECLARE
TYPE EMP_REC IS RECORD(A1 NUMBER,A2 VARCHAR2(20),A3 VARCHAR2(20),A4 NUMBER,A5 VARCHAR2(20));
LV_EMP_REC EMP_REC;
BEGIN
LV_EMP_REC.A1 :=:EMPNO;
SELECT EMPNO,ENAME,JOB,SAL,DNAME
INTO LV_EMP_REC
FROM EMP E,DEPT D
WHERE 1=1
AND E.DEPTNO=D.DEPTNO
AND EMPNO=LV_EMP_REC.A1;
DBMS_OUTPUT.PUT_LINE(LV_EMP_REC.A1||','||LV_EMP_REC.A2||','||LV_EMP_REC.A3||','||LV_EMP_REC.A4||','||LV_EMP_REC.A5);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('DEPTNO DOSE NOT EXIST IN EMP TABLE');
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('INPUT VALUE INCOMPATABLE');
END;
/
DECLARE
TYPE EMP_REC IS RECORD(EMPNO NUMBER,ENAME VARCHAR2(20),JOB VARCHAR2(20),HIREDATE DATE,SAL NUMBER,DNAME VARCHAR2(20),DEPTNO NUMBER);
LV_EMP_REC EMP_REC;
BEGIN
LV_EMP_REC.EMPNO :=:EMPNO;
SELECT E.EMPNO,E.ENAME,E.JOB,E.HIREDATE,E.SAL,D.DNAME,D.DEPTNO
INTO LV_EMP_REC
FROM EMP E,DEPT D
WHERE 1=1
AND E.DEPTNO=D.DEPTNO
AND EMPNO=LV_EMP_REC.EMPNO;
DBMS_OUTPUT.PUT_LINE(LV_EMP_REC.EMPNO||','||LV_EMP_REC.ENAME||','||LV_EMP_REC.JOB||','||LV_EMP_REC.HIREDATE||','||LV_EMP_REC.SAL||','||LV_EMP_REC.DNAME||','||LV_EMP_REC.DEPTNO);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('DEPTNO DOSE NOT EXIST IN EMP TABLE');
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('INPUT VALUE INCOMPATABLE');
END;
/
DECLARE
TYPE EMP_REC IS RECORD(EMPNO EMP.EMPNO%TYPE,
ENAME EMP.ENAME%TYPE,
JOB EMP.JOB%TYPE,
HIREDATE EMP.HIREDATE%TYPE,
SAL EMP.SAL%TYPE,
DNAME DEPT.DNAME%TYPE,
DEPTNO DEPT.DEPTNO%TYPE);
LV_EMP_REC EMP_REC;
BEGIN
LV_EMP_REC.EMPNO :=:EMPNO;
SELECT E.EMPNO,E.ENAME,E.JOB,E.HIREDATE,E.SAL,D.DNAME,D.DEPTNO
INTO LV_EMP_REC
FROM EMP E,DEPT D
WHERE 1=1
AND E.DEPTNO=D.DEPTNO
AND EMPNO=LV_EMP_REC.EMPNO;
DBMS_OUTPUT.PUT_LINE(LV_EMP_REC.EMPNO||','||LV_EMP_REC.ENAME||','||LV_EMP_REC.JOB||','||LV_EMP_REC.HIREDATE||','||LV_EMP_REC.SAL||','||LV_EMP_REC.DNAME||','||LV_EMP_REC.DEPTNO);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('EMPNO DOSE NOT EXIST IN EMP TABLE');
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('INPUT VALUE INCOMPATABLE');
END;
/
DROP TABLE XX_EMP_RECORD;
/
CREATE TABLE XX_EMP_RECORD
(
EMPNO NUMBER,
ENAME VARCHAR2(20),
JOB VARCHAR2(20),
SAL NUMBER,
DEPTNO NUMBER
);
/
INSERT INTO XX_EMP_RECORD VALUES (1,'A','HR',1000,20);
/
COMMIT;
/
--insert the record type data
DECLARE
TYPE EMP_RECORD IS RECORD
(
EMPNO NUMBER,
ENAME VARCHAR2(20),
JOB VARCHAR2(20),
SAL NUMBER,
DEPTNO NUMBER
);
LV_EMP_RECORD EMP_RECORD;
BEGIN
LV_EMP_RECORD.EMPNO :=2;
LV_EMP_RECORD.ENAME :='B';
LV_EMP_RECORD.JOB :='SALES';
LV_EMP_RECORD.SAL :=2000;
LV_EMP_RECORD.DEPTNO :=20;
--TO PRINT THE DATA
DBMS_OUTPUT.PUT_LINE('EMPNO IS : '||LV_EMP_RECORD.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS : '||LV_EMP_RECORD.ENAME);
DBMS_OUTPUT.PUT_LINE('JOB IS : '||LV_EMP_RECORD.JOB);
DBMS_OUTPUT.PUT_LINE('SAL IS : '||LV_EMP_RECORD.SAL);
DBMS_OUTPUT.PUT_LINE('DEPTNO IS : '||LV_EMP_RECORD.DEPTNO);
--TO INSERT RECORD TYPE DATA INTO TABLE
INSERT INTO XX_EMP_RECORD
(
EMPNO,
ENAME,
JOB,
SAL,
DEPTNO
)
VALUES
(
LV_EMP_RECORD.EMPNO,
LV_EMP_RECORD.ENAME,
LV_EMP_RECORD.JOB,
LV_EMP_RECORD.SAL,
LV_EMP_RECORD.DEPTNO
);
COMMIT;
END;
/
SELECT * FROM XX_EMP_RECORD;
/
declare
cursor c1 is select ename,sal from emp;
type t1 is record(lv_ename varchar2(20),lv_sal number);
lv_t1 t1;
begin
for i in c1
loop
lv_t1.lv_ename :=i.ename;
lv_t1.lv_sal :=i.sal;
dbms_output.put_line(lv_t1.lv_ename||','||lv_t1.lv_sal);
end loop;
end;
/
CREATE OR REPLACE PROCEDURE P1(P_DEPTNO NUMBER)
IS
cursor c1 is select ename,sal,JOB from emp WHERE DEPTNO=P_DEPTNO;
type t1 is record(lv_ename varchar2(20),lv_sal number,LV_JOB VARCHAR2(20));
lv_t1 t1;
begin
for i in c1
loop
lv_t1.lv_ename :=i.ename;
lv_t1.lv_sal :=i.sal;
LV_T1.LV_JOB :=I.JOB;
dbms_output.put_line(lv_t1.lv_ename||','||lv_t1.lv_sal||','||LV_T1.LV_JOB);
end loop;
end;
/
EXEC P1(10);
/
BEHGIN
P1(10);
END;
/
DROP TABLE XX_EMP_RECORD;
/
CREATE TABLE XX_EMP_RECORD
(
EMPNO NUMBER,
ENAME VARCHAR2(20),
JOB VARCHAR2(20),
SAL NUMBER,
DEPTNO NUMBER
);
/
create or replace procedure p1
is
type t1 is record
(empno number,ename varchar2(20),job varchar2(10),sal number,deptno number);
v1 t1;
cursor c1 is select empno,ename,job,sal,deptno from emp;
lv_count number :=0;
begin
for i in c1
loop
v1.empno :=i.empno;
v1.ename :=i.ename;
v1.job :=i.job;
v1.sal :=i.sal;
v1.deptno :=i.deptno;
--dbms_output.put_line(v1.empno||','||v1.ename||','||v1.job||','||v1.sal||','||v1.deptno);
insert into xx_emp_record
(
empno,
ename,
job,
sal,
deptno
)
values
(
v1.empno,
v1.ename,
v1.job,
v1.sal,
v1.deptno
);
lv_count :=lv_count+1;
end loop;
commit;
dbms_output.put_line('no of rows inserted : '||lv_count);
end;
/
EXEC P1;
/
SELECT * FROM XX_EMP_RECORD;
/
create or replace package pkg1
is
procedure p1;
end;
/
create or replace package body pkg1
is
procedure p1
is
type t1 is record
(empno number,ename varchar2(20),job varchar2(10),sal number,deptno number);
v1 t1;
cursor c1 is select empno,ename,job,sal,deptno from emp;
lv_count number :=0;
begin
for i in c1
loop
v1.empno :=i.empno;
v1.ename :=i.ename;
v1.job :=i.job;
v1.sal :=i.sal;
v1.deptno :=i.deptno;
--dbms_output.put_line(v1.empno||','||v1.ename||','||v1.job||','||v1.sal||','||v1.deptno);
insert into xx_emp_record
(
empno,
ename,
job,
sal,
deptno
)
values
(
v1.empno,
v1.ename,
v1.job,
v1.sal,
v1.deptno
);
lv_count :=lv_count+1;
end loop;
commit;
dbms_output.put_line('no of rows inserted : '||lv_count);
end p1;
end pkg1;
/
truncate table xx_emp_record;
/
select * from xx_emp_record;
/
exec pkg1.p1;
/
COLLECTION:
***********
-->Collection is nothing but one variable.
-->A group of values stored under one variable is called as collection.
-->A collection is an ordered group of elements,all of the same type.
-->There are three types of collections.
INDEX BY TABLE OR ASSOCIATE ARRAY OR PL/SQL TABLE.
VARRAY
NESTED TABLE.
INDEX BY TABLE OR ASSOCIATIVE ARRAY OR PL/SQL TABLE:
****************************************************
-->This is an user defined type which is used to store multiple data items in a single unit.
-->An index by table can store arbitary number of data.
-->Subscript values can be integer or string.It can exists only in PL/SQL block.
-->Generally these tables are used to improve the performance of the application.
-->Basically these table contains key value pairs i.e value field is stored in actual data and key field is stored in indexes.
-->Key field values are either integer or character and also these values are either -ve or +ve.
-->These indexes key behaves like a primary key i.e does not accept null and duplicate values basically this key datatype is binary integer.
-->Index by table having following collection methods.
1)exists
2)first
3)last
4)prior
5)next
6)count
7)delete(range of indexes)
--varray and nested table
8)EXTEND
9)TRIM
10)LIMIT
SYNTAX:
*******
TYPE TYPENAME IS TABLE OF DATATYPE<SIZE> INDEX BY BINARY_INTEGER OR DATATYPE<SIZE>;
VARIABLENME TYPENAME;
EX:
***
DECLARE
TYPE T1 IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
V1 T1;
BEGIN
V1(1):=10;
V1(2):=20;
V1(3):=30;
V1(4):=40;
V1(5):=50;
DBMS_OUTPUT.PUT_LINE(V1(1));
DBMS_OUTPUT.PUT_LINE(V1(2));
DBMS_OUTPUT.PUT_LINE(V1(3));
DBMS_OUTPUT.PUT_LINE(V1(4));
DBMS_OUTPUT.PUT_LINE(V1(5));
END;
/
DECLARE
--TYPE TYPENAME IS TABLE OF DATATYPE <SIZE< INDEX BY DATATYPE;
TYPE T1 IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
V1 T1;
BEGIN
V1(1):=10;
V1(2):=20;
V1(3):=30;
V1(4):=40;
V1(5):=50;
DBMS_OUTPUT.PUT_LINE(V1(1));
DBMS_OUTPUT.PUT_LINE(V1.FIRST);
DBMS_OUTPUT.PUT_LINE(V1.LAST);
DBMS_OUTPUT.PUT_LINE(V1.PRIOR(3));
DBMS_OUTPUT.PUT_LINE(V1.NEXT(4));
DBMS_OUTPUT.PUT_LINE(V1.COUNT);
DBMS_OUTPUT.PUT_LINE(V1(5));
END;
/
DECLARE
TYPE T1 IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
V1 T1;
BEGIN
V1(1):=10;
V1(2):=20;
V1(3):=30;
V1(4):=40;
V1(5):=50;
DBMS_OUTPUT.PUT_LINE(V1.COUNT);
V1.DELETE(2,3);
DBMS_OUTPUT.PUT_LINE(V1.COUNT);
V1.DELETE;
DBMS_OUTPUT.PUT_LINE(V1.COUNT);
END;
/
DECLARE
TYPE V_ASSO_ARRAY_TYPE IS TABLE OF VARCHAR2(20) INDEX BY VARCHAR2(20); --USER DEFINED TYPE
V_COLOR_CODE V_ASSO_ARRAY_TYPE; --DECLARE VARIABLE
BEGIN
V_COLOR_CODE('WHITE'):='000';
V_COLOR_CODE('BLACK'):='111';
V_COLOR_CODE('RED'):='222';
V_COLOR_CODE('GREEN'):='333';
V_COLOR_CODE('YELLOW'):='444';
DBMS_OUTPUT.PUT_LINE('V_COLOR_CODE(WHITE) : '||V_COLOR_CODE('WHITE'));
DBMS_OUTPUT.PUT_LINE('V_COLOR_CODE(BLACK) : '||V_COLOR_CODE('BLACK'));
DBMS_OUTPUT.PUT_LINE('V_COLOR_CODE(RED) : '||V_COLOR_CODE('RED'));
DBMS_OUTPUT.PUT_LINE('V_COLOR_CODE(GREEN) : '||V_COLOR_CODE('GREEN'));
DBMS_OUTPUT.PUT_LINE('V_COLOR_CODE(YELLOW) : '||V_COLOR_CODE('YELLOW'));
END;
/
--WRITE A PL/SQL PROGRAM TO TRANSFER ALL EMPLOYEE NAMES FROM EMP TABLE AND STORE IT INTO INDEX BY TABLE AND ALSO DISPLAY DATA FROM INDEX BY TABLE.
--SIMPLE LOOP
/
DECLARE
CURSOR C1
IS
SELECT ENAME FROM EMP;
TYPE T1 IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
V1 T1;
N NUMBER :=1;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO V1(N);
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(V1(N));
N:=N+1;
END LOOP;
CLOSE C1;
END;
/
DECLARE
CURSOR C1
IS
SELECT ENAME FROM EMP;
TYPE T1 IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
V1 T1;
N NUMBER :=1;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO V1(N);
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(V1(N));
N:=N+1;
END LOOP;
CLOSE C1;
--FOR LOOP
FOR I IN V1.FIRST..V1.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(V1(I));
END LOOP;
END;
/
--BULK COLLECT
DECLARE
TYPE T1 IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
V1 T1;
BEGIN
SELECT ENAME BULK COLLECT INTO V1 FROM EMP;
FOR I IN V1.FIRST..V1.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(V1(I));
END LOOP;
END;
/
DECLARE
TYPE T1 IS TABLE OF DATE INDEX BY BINARY_INTEGER;
V1 T1;
BEGIN
FOR I IN 1..10
LOOP
V1(I):=SYSDATE+I;
END LOOP;
FOR I IN V1.FIRST..V1.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(V1(I));
END LOOP;
END;
/
--WRITE A PL/SQL PROGRAM TO TRANSFER ALL HIREDATES FROM EMP TABLE AND STORE IT INTO INDEX BY TABLE AND ALSO DISPLAY DATA FROM INDEX BY TABLE.
DECLARE
TYPE T1 IS TABLE OF DATE INDEX BY BINARY_INTEGER;
V1 T1;
BEGIN
SELECT HIREDATE BULK COLLECT INTO V1 FROM EMP;
FOR I IN V1.FIRST..V1.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(V1(I));
END LOOP;
END;
/
DECLARE
TYPE T1 IS TABLE OF VARCHAR2(20) INDEX BY VARCHAR2(20);
V1 T1;
X VARCHAR2(20);
BEGIN
V1('A'):='MOHAN';
V1('B'):='RAJA';
V1('C'):='RANI';
X:='A';
LOOP
DBMS_OUTPUT.PUT_LINE(V1(X));
X:=V1.NEXT(X);
EXIT WHEN X IS NULL;
END LOOP;
END;
/
DECLARE
TYPE T1 IS TABLE OF EMP%ROWTYPE;
V1 T1;
X NUMBER(10);
BEGIN
SELECT * BULK COLLECT INTO V1 FROM EMP;
X:=1;
LOOP
DBMS_OUTPUT.PUT_LINE(V1(X).EMPNO||','||V1(X).ENAME);
X:=V1.NEXT(X);
EXIT WHEN X IS NULL;
END LOOP;
END;
/
DECLARE
TYPE T1 IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER;
V1 T1 ;
BEGIN
SELECT * BULK COLLECT INTO V1 FROM EMP;
FOR I IN V1.FIRST..V1.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(V1(I).EMPNO||','||V1(I).ENAME);
END LOOP;
END;
/
VARRAY:
*******
-->VARRAY is an datatype its similar to an array.Varray has a fixed limit on size,specified as part of declaration.
-->It allows group of values /elements of same datatypes.
-->Varray should declare with size.
-->It is also same as arrays in normal language.
-->Here always index start with 1 and also indexes are consecutive.
-->We can also store varrays permanently into database.
-->from here we are also storing actual data we must use constructor.
-->maximum size of varray is 2gb.
SYNTAX:
******
1)type typename is varray(maxsize) of datatype(size);
2)variablename typename :=typename();
EX:
***
DECLARE
TYPE T1 IS VARRAY(100) OF NUMBER;
V1 T1 :=T1(10,20,30,40,50);
BEGIN
DBMS_OUTPUT.PUT_LINE(V1(1));
DBMS_OUTPUT.PUT_LINE(V1(4));
DBMS_OUTPUT.PUT_LINE(V1(2));
DBMS_OUTPUT.PUT_LINE(V1(5));
DBMS_OUTPUT.PUT_LINE(V1(3));
END;
/
DECLARE
TYPE T1 IS VARRAY(5) OF NUMBER;
V1 T1 :=T1(NULL,NULL,NULL,NULL,NULL);
BEGIN
V1(1) :=10;
V1(2) :=20;
V1(3) :=30;
V1(4) :=40;
V1(5) :=50;
DBMS_OUTPUT.PUT_LINE(V1(1));
DBMS_OUTPUT.PUT_LINE(V1(2));
DBMS_OUTPUT.PUT_LINE(V1(3));
DBMS_OUTPUT.PUT_LINE(V1(4));
DBMS_OUTPUT.PUT_LINE(V1(5));
END;
/
DECLARE
TYPE T1 IS VARRAY(10) OF NUMBER;
V1 T1 :=T1();
BEGIN
V1.EXTEND(6);
V1(1) :=10;
V1(2) :=20;
V1(3) :=30;
V1(4) :=40;
V1(5) :=50;
V1(6) :=60;
DBMS_OUTPUT.PUT_LINE(V1(1));
DBMS_OUTPUT.PUT_LINE(V1(2));
DBMS_OUTPUT.PUT_LINE(V1(3));
DBMS_OUTPUT.PUT_LINE(V1(4));
DBMS_OUTPUT.PUT_LINE(V1(5));
DBMS_OUTPUT.PUT_LINE(V1(6));
END;
/
DECLARE
TYPE T1 IS VARRAY(7) OF VARCHAR2(20);
V1 T1 :=T1();
BEGIN
v1.extend(5);
V1(1) :='MONDAY';
v1(2):='TUESDAY';
V1(3):='WEDNESDAY';
V1(4):='THURSDAY';
V1(5):='FRIDAY';
DBMS_OUTPUT.PUT_LINE(V1(1));
DBMS_OUTPUT.PUT_LINE(V1(2));
DBMS_OUTPUT.PUT_LINE(V1(3));
DBMS_OUTPUT.PUT_LINE(V1(4));
DBMS_OUTPUT.PUT_LINE(V1(5));
END;
/
DECLARE
TYPE T1 IS VARRAY(7) OF VARCHAR2(20);
V1 T1 :=T1();
BEGIN
v1.extend(7);
V1(1) :='MONDAY';
v1(2):='TUESDAY';
V1(3):='WEDNESDAY';
V1(4):='THURSDAY';
V1(5):='FRIDAY';
V1(6) :='SATURDAY';
V1(7) :='SUNDAY';
FOR I IN 1..4
LOOP
DBMS_OUTPUT.PUT_LINE(V1(I));
END LOOP;
END;
/
CREATE OR REPLACE PROCEDURE P1
IS
TYPE T1 IS VARRAY(10) OF NUMBER;
LV_T1 T1;
BEGIN
LV_T1 :=T1(10,20,30,40,50);
DBMS_OUTPUT.PUT_LINE(LV_T1(1));
DBMS_OUTPUT.PUT_LINE(LV_T1(2));
DBMS_OUTPUT.PUT_LINE(LV_T1(3));
DBMS_OUTPUT.PUT_LINE(LV_T1(4));
DBMS_OUTPUT.PUT_LINE(LV_T1(5));
END;
/
EXEC P1;
/
CREATE OR REPLACE PROCEDURE P1(V1 NUMBER)
IS
TYPE T1 IS VARRAY(10) OF NUMBER;
LV_T1 T1;
BEGIN
LV_T1 :=T1(10,20,30,40,50);
DBMS_OUTPUT.PUT_LINE(LV_T1(V1));
END;
/
EXEC P1(1);
/
DECLARE
TYPE T1 IS VARRAY(10) OF NUMBER;
V1 T1 :=T1();
BEGIN
V1.EXTEND(10);
V1(1) :=10;
V1(2) :=20;
V1(3) :=30;
V1(4) :=40;
V1(5) :=50;
V1(6) :=60;
V1(7) :=70;
DBMS_OUTPUT.PUT_LINE('FIRST IS : '||V1.FIRST);
DBMS_OUTPUT.PUT_LINE('LAST IS : '||V1.LAST);
DBMS_OUTPUT.PUT_LINE('PRIOR IS : '||V1.PRIOR(2));
DBMS_OUTPUT.PUT_LINE('NEXT IS : '||V1.NEXT(3));
DBMS_OUTPUT.PUT_LINE('COUNT IS : '||V1.COUNT);
DBMS_OUTPUT.PUT_LINE('LIMIT IS : '||V1.LIMIT);
/*DBMS_OUTPUT.PUT_LINE(V1(1));
DBMS_OUTPUT.PUT_LINE(V1(2));
DBMS_OUTPUT.PUT_LINE(V1(3));
DBMS_OUTPUT.PUT_LINE(V1(4));
DBMS_OUTPUT.PUT_LINE(V1(5));*/
FOR I IN V1.FIRST..V1.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(V1(I));
END LOOP;
END;
/
DECLARE
TYPE T1 IS VARRAY(7) OF VARCHAR2(20);
V1 T1 :=T1(NULL,NULL,NULL,NULL,NULL,NULL,NULL);
BEGIN
V1(1) :='MONDAY';
V1(2) :='TUESDAY';
V1(3) :='WEDNESDAY';
DBMS_OUTPUT.PUT_LINE(V1(1));
DBMS_OUTPUT.PUT_LINE(V1(2));
DBMS_OUTPUT.PUT_LINE(V1(3));
DBMS_OUTPUT.PUT_LINE('V1.LIMIT : '||V1.LIMIT);
DBMS_OUTPUT.PUT_LINE('V1.COUNT :'||V1.COUNT);
V1.TRIM();
DBMS_OUTPUT.PUT_LINE('V1.COUNT :'||V1.COUNT);
END;
/
DECLARE
TYPE T1 IS VARRAY(100) OF NUMBER;
V1 T1 :=T1(10,20,30,40,50);
BEGIN
DBMS_OUTPUT.PUT_LINE('FIRST : '||V1.FIRST);
DBMS_OUTPUT.PUT_LINE('LAST : '||V1.LAST);
DBMS_OUTPUT.PUT_LINE('PRIOR : '||V1.PRIOR(3));
DBMS_OUTPUT.PUT_LINE('NEXT : '||V1.NEXT(3));
DBMS_OUTPUT.PUT_LINE('COUNT : '||V1.COUNT);
FOR I IN V1.FIRST..V1.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(V1(I));
END LOOP;
END;
/
DECLARE
TYPE T1 IS VARRAY(20) OF VARCHAR2(20);
V1 T1 :=T1('A','B','C','D');
Z BOOLEAN;
BEGIN
DBMS_OUTPUT.PUT_LINE(V1.FIRST);
DBMS_OUTPUT.PUT_LINE(V1.LAST);
DBMS_OUTPUT.PUT_LINE(V1.PRIOR(3));
DBMS_OUTPUT.PUT_LINE(V1.NEXT(2));
DBMS_OUTPUT.PUT_LINE(V1.COUNT);
DBMS_OUTPUT.PUT_LINE(V1.LIMIT);
FOR I IN V1.FIRST..V1.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(V1(I));
END LOOP;
Z:=V1.EXISTS(3);
IF Z=TRUE THEN
DBMS_OUTPUT.PUT_LINE('YOUR INDEX EXISTS WITH AN ELEMENT : '||''||V1(3));
ELSE
DBMS_OUTPUT.PUT_LINE('INDEX3 DOES NOT EXISTS');
END IF;
V1.EXTEND;
V1(5) :='E';
V1.EXTEND(2);
V1(6):='F';
V1(7):='G';
--V1.EXTEND(3,2);
FOR I IN V1.FIRST..V1.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(V1(I));
END LOOP;
V1.TRIM(5);
DBMS_OUTPUT.PUT_LINE(V1.COUNT);
V1.DELETE;
DBMS_OUTPUT.PUT_LINE(V1.COUNT);
END;
/
DECLARE
TYPE T1 IS VARRAY(20) OF NUMBER;
V1 T1;
BEGIN
SELECT SAL BULK COLLECT
INTO V1
FROM EMP;
FOR I IN V1.FIRST..V1.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(V1(I));
END LOOP;
DBMS_OUTPUT.PUT_LINE(V1(14));
DBMS_OUTPUT.PUT_LINE(V1(10));
DBMS_OUTPUT.PUT_LINE(V1(1));
END;
/
DECLARE
TYPE T1 IS VARRAY(20) OF EMP%ROWTYPE;
V1 T1:=T1();
BEGIN
SELECT * BULK COLLECT INTO V1 FROM EMP;
FOR I IN V1.FIRST..V1.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(V1(I).EMPNO||','||V1(I).ENAME||','||V1(I).JOB);
END LOOP;
END;
/
DECLARE
CURSOR C1
IS
SELECT ENAME FROM EMP;
TYPE T1 IS VARRAY(20) OF VARCHAR2(20);
V1 T1 :=T1();
N NUMBER :=1;
BEGIN
V1.EXTEND(15);
OPEN C1;
LOOP
FETCH C1 INTO V1(N);
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(V1(N));
N:=N+1;
END LOOP;
CLOSE C1;
--FOR LOOP
FOR I IN V1.FIRST..V1.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(V1(I));
END LOOP;
END;
/
NESTED TABLE:
*************
-->A nested table is also a database table which has no limit on its size.
-->Elements are inserted into nested table starting at index 1.
-->
-->Elements can be deleted or added anywhere in nested table where as varray can only add or delete elements from the end of the varray.
-->It is also a user defined type which is used to store multiple data items in a single unit.
-->Nested tables and varrays always index start with 1
-->Nested tables are dense,where as index by tables are no need to allocate the memory explicitly.
SYNTAX:
*******
TYPE TYPENAME IS TABLE OF DATATYPE(SIZE);
VARIABLENAME TYPE NAME :=TYPENAME(); --CONSTRUCTOR NAME
EX:
****
/
DECLARE
TYPE T1 IS TABLE OF NUMBER;
V1 T1 :=T1();
BEGIN
V1.EXTEND(10);
V1(5):=50;
DBMS_OUTPUT.PUT_LINE(V1(5));
END;
/
DECLARE
TYPE T1 IS TABLE OF NUMBER;
V1 T1:=T1(10,20,30,40,50);
BEGIN
DBMS_OUTPUT.PUT_LINE(V1.FIRST);
DBMS_OUTPUT.PUT_LINE(V1.LAST);
DBMS_OUTPUT.PUT_LINE(V1.PRIOR(3));
DBMS_OUTPUT.PUT_LINE(V1.NEXT(3));
DBMS_OUTPUT.PUT_LINE(V1.COUNT);
FOR I IN 1..V1.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(V1(I));
END LOOP;
END;
/
DECLARE
TYPE STUDENT_NAME IS TABLE OF VARCHAR2(20);
TYPE STUDENT_MARKS IS TABLE OF NUMBER;
SNAME STUDENT_NAME :=STUDENT_NAME('KING','ADAMS','ALLEN','JAMES','SMITH');
SMARKS STUDENT_MARKS :=STUDENT_MARKS(100,90,85,90,100);
TOTAL_STUDENTS NUMBER;
BEGIN
TOTAL_STUDENTS :=SNAME.COUNT;
DBMS_OUTPUT.PUT_LINE('TOTAL STUDENTS : = '||TOTAL_STUDENTS);
FOR I IN 1..TOTAL_STUDENTS
LOOP
DBMS_OUTPUT.PUT_LINE('STUDENT : '||SNAME(I)||' GOT = ' ||SMARKS(I));
END LOOP;
END;
/
DECLARE
TYPE T1 IS TABLE OF NUMBER;
V1 T1:=T1();
BEGIN
V1.EXTEND(5);
V1(1):=10;
V1(2):=20;
V1(3):=30;
V1(4):=40;
V1(5):=50;
FOR I IN 1..V1.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(V1(I));
END LOOP;
END;
/
DECLARE
TYPE T1 IS TABLE OF VARCHAR2(20);
V1 T1 :=T1();
CURSOR C1 IS SELECT ENAME FROM EMP;
N NUMBER(5):=1;
BEGIN
FOR I IN C1
LOOP
V1.EXTEND();
V1(N):=I.ENAME;
DBMS_OUTPUT.PUT_LINE(V1(N));
N:=N+1;
END LOOP;
FOR I IN V1.FIRST..V1.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(V1(I));
END LOOP;
END;
/
DECLARE
TYPE T1 IS TABLE OF VARCHAR2(10);
V1 T1 :=T1();
BEGIN
SELECT ENAME BULK COLLECT INTO V1 FROM EMP;
FOR I IN 1..V1.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(V1(I));
END LOOP;
END;
/
--REAL TIME EXAMPLE
drop table XXEAM_VALUE_SETS_STG;
/
CREATE TABLE XXEAM_VALUE_SETS_STG
(
VALUE_SET_NAME VARCHAR2(100),
FLEX_VALUE_SET_ID NUMBER,
DESCRIPTION VARCHAR2(1000),
FORMAT_TYPE VARCHAR2(10),
LIST_TYPE VARCHAR2(10),
VALIDATION_TYPE VARCHAR2(10),
UPPER_CASE_ONLY VARCHAR2(10),
DEPENDANT_DEFAULT_VALUE VARCHAR2(100),
FLEX_VALUE VARCHAR2(240),
PROCESS_FLAG VARCHAR2(1),
CREATION_DATE DATE,
CREATED_BY NUMBER,
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER,
LAST_UPDATE_LOGIN NUMBER
);
/
DECLARE
CURSOR C_VALUE_SETS IS
select ffvs.FLEX_VALUE_SET_NAME VALUE_SET_NAME,
ffvs.DESCRIPTION,
ffvs.FORMAT_TYPE,
ffvs.LONGLIST_FLAG LIST_TYPE ,
ffvs.VALIDATION_TYPE,
ffvs.UPPERCASE_ONLY_FLAG UPPER_CASE_ONLY ,
ffvs.DEPENDANT_DEFAULT_VALUE,
ffv.flex_value,
FFV.FLEX_VALUE_SET_ID
from fnd_flex_value_sets ffvs,
fnd_flex_values ffv
where 1=1
and ffvs.flex_value_set_id=ffv.flex_value_set_id;
TYPE VALUE_SET_TYPE IS TABLE OF C_VALUE_SETS%ROWTYPE;
LV_VALUE_SET VALUE_SET_TYPE;
BEGIN
OPEN C_VALUE_SETS;
LOOP
FETCH C_VALUE_SETS BULK COLLECT INTO LV_VALUE_SET;
EXIT WHEN LV_VALUE_SET.COUNT=0;
FOR I IN 1..LV_VALUE_SET.COUNT
LOOP
INSERT INTO XXEAM_VALUE_SETS_STG
(
VALUE_SET_NAME,
DESCRIPTION,
FORMAT_TYPE,
LIST_TYPE,
VALIDATION_TYPE,
UPPER_CASE_ONLY,
DEPENDANT_DEFAULT_VALUE,
FLEX_VALUE,
FLEX_VALUE_SET_ID,
PROCESS_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
VALUES
(
LV_VALUE_SET(I).VALUE_SET_NAME,
LV_VALUE_SET(I).DESCRIPTION,
LV_VALUE_SET(I).FORMAT_TYPE,
LV_VALUE_SET(I).LIST_TYPE,
LV_VALUE_SET(I).VALIDATION_TYPE,
LV_VALUE_SET(I).UPPER_CASE_ONLY,
LV_VALUE_SET(I).DEPENDANT_DEFAULT_VALUE,
LV_VALUE_SET(I).FLEX_VALUE,
LV_VALUE_SET(I).FLEX_VALUE_SET_ID,
'N',
SYSDATE,
FND_GLOBAL.USER_ID, --FND_PROFILE.VALUE('USERID')
SYSDATE,
FND_GLOBAL.USER_ID
);
END LOOP;
END LOOP;
--COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Error Message is : '||SQLERRM);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error Message is : '||SQLERRM);
END;
/
SELECT * FROM XXEAM_VALUE_SETS_STG;
/
TRUNCATE TABLE XXEAM_VALUE_SETS_STG;
/
CREATE TABLE XX_OPERATING_UNITS AS SELECT * FROM HR_OPERATING_UNITS WHERE 1=2;
/
DECLARE
TYPE T1 IS TABLE OF HR_OPERATING_UNITS%ROWTYPE;
V1 T1;
LV_COUNT NUMBER :=0;
BEGIN
SELECT * BULK COLLECT
INTO V1
FROM HR_OPERATING_UNITS;
FOR I IN 1..V1.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(V1(I).ORGANIZATION_ID||','||V1(I).NAME);
INSERT INTO XX_OPERATING_UNITS
(
BUSINESS_GROUP_ID,
ORGANIZATION_ID,
NAME,
DATE_FROM,
DATE_TO,
SHORT_CODE,
SET_OF_BOOKS_ID,
DEFAULT_LEGAL_CONTEXT_ID,
USABLE_FLAG
)
VALUES
(
V1(I).BUSINESS_GROUP_ID,
V1(I).ORGANIZATION_ID,
V1(I).NAME,
V1(I).DATE_FROM,
V1(I).DATE_TO,
V1(I).SHORT_CODE,
V1(I).SET_OF_BOOKS_ID,
V1(I).DEFAULT_LEGAL_CONTEXT_ID,
V1(I).USABLE_FLAG
);
LV_COUNT :=LV_COUNT+1;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('NUMBER OF ROWS INSERTED : '||LV_COUNT);
END;
/
SELECT * FROM XX_OPERATING_UNITS;
/
DIFFERNECE BETWEEN CURSOR AND COLLECTION:
******************************************
/
--CURSOR
declare
cursor c1 is select ename from emp where deptno=10;
lv_ename varchar2(20);
begin
open c1;
loop
fetch c1 into lv_ename;
exit when c1%notfound;
dbms_output.put_line('ename is :'||lv_ename);
end loop;
end;
/
--COLLECTION
declare
type t1 is table of varchar2(20);
lv_t1 t1 :=t1();
begin
select ename
bulk collect into lv_t1
from emp
where deptno=10;
for i in lv_t1.first..lv_t1.last
loop
dbms_output.put_line('ename list ('||i||')='||lv_t1(i));
end loop;
dbms_output.put_line('Third ename is > '||lv_t1(3));
dbms_output.put_line('First ename is : '||lv_t1(1));
dbms_output.put_line('Second ename is : '||lv_t1(2));
end;
/
V1=(KING,BLAKE,CLARK,JONES,SCOTT,FORD,SMITH,ALLEN,WARD,MARTIN,TURNER,ADAMS,JAMES,MILLER)
/
--COLLECTION
DECLARE
TYPE T1 IS TABLE OF VARCHAR2(10);
V1 T1 :=T1();
BEGIN
SELECT ENAME BULK COLLECT INTO V1 FROM EMP;
DBMS_OUTPUT.PUT_LINE(V1(14));
DBMS_OUTPUT.PUT_LINE(V1(1));
END;
/
--CURSOR
DECLARE
CURSOR C1 IS SELECT ENAME FROM EMP;
LV_ENAME VARCHAR2(20);
BEGIN
OPEN C1;
FETCH C1 INTO LV_ENAME;
DBMS_OUTPUT.PUT_LINE(LV_ENAME);
FETCH C1 INTO LV_ENAME;
DBMS_OUTPUT.PUT_LINE(LV_ENAME);
CLOSE C1;
END;
/
CREATE OR REPLACE TYPE T1 IS TABLE OF VARCHAR2(30);
/
CREATE OR REPLACE PROCEDURE P1(P_DEPTNO IN NUMBER,P_TYPE_NAME OUT T1)
IS
LV_T1 T1 :=T1();
BEGIN
SELECT ENAME
BULK COLLECT INTO LV_T1
FROM EMP
WHERE DEPTNO=P_DEPTNO;
P_TYPE_NAME :=LV_T1;
END;
/
DECLARE
LV_NAME_LIST T1 :=T1();
BEGIN
P1(10,LV_NAME_LIST);
FOR I IN LV_NAME_LIST.FIRST..LV_NAME_LIST.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(LV_NAME_LIST(I));
END LOOP;
END;
/
No comments:
Post a Comment