Bulk Mechanism:
***************
-->Bulk is one of the method which is used to improve the performance of the application.
-->Oracle introduce bulk bind process using collection.i.e in this processs we are putting all sql statement related values into collection and in this collection we are performing insert,update and delete at a time using for all statement.
-->In this bulk we have two options.
1)Bulk Collect
2)Bulk Bind
Normal Programs:
****************
DECLARE
LV_NAME VARCHAR2(20);
LV_EMPNO NUMBER :=7788;
BEGIN
IF LV_EMPNO IS NOT NULL THEN
SELECT ENAME
INTO LV_NAME
FROM EMP
WHERE EMPNO=LV_EMPNO;
DBMS_OUTPUT.PUT_LINE(LV_NAME);
END IF;
END;
/
cursor For loop:
****************
DECLARE
CURSOR C1 IS SELECT ENAME FROM EMP;
BEGIN
FOR I IN C1
LOOP
DBMS_OUTPUT.PUT_LINE(I.ENAME);
END LOOP;
END;
/
1)Bulk Collect:
***************
-->In this caluse we are used to fetch the data from database into collection.
-->SELECT statement that retrives multiple rows with a single fetch,improving speed of data retrieval.
-->It reduces the context switches between the sql engine and plsql engine.
-->This caluse is used in
1)select....into....clause
2)cursor....fetch....statement
1)Bulk Collect used in select....into....clause
***********************************************
syntax:
*******
select * bulk collect into collection_name from tablename where <cond>;
EX:
***
declare
type t1 is table of emp%rowtype;
lv_type t1;
begin
select * bulk collect into lv_type from emp;
for i in lv_type.first..lv_type.last
loop
dbms_output.put_line(lv_type(i).empno||','||lv_type(i).ename);
end loop;
end;
/
DECLARE
TYPE T1 IS TABLE OF EMP%ROWTYPE;
L_EMPLOYEES T1;
BEGIN
SELECT * BULK COLLECT INTO L_EMPLOYEES
FROM EMP;
DBMS_OUTPUT.PUT_LINE('TOTAL EMPLOYEES COUNT :'||L_EMPLOYEES.COUNT);
FOR I IN 1..L_EMPLOYEES.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE('ENAME :'||L_EMPLOYEES(I).ENAME);
END LOOP;
END;
/
DECLARE
TYPE T1 IS TABLE OF HR_OPERATING_UNITS%ROWTYPE;
L_EMPLOYEES T1;
BEGIN
SELECT * BULK COLLECT INTO L_EMPLOYEES
FROM HR_OPERATING_UNITS;
DBMS_OUTPUT.PUT_LINE('TOTAL EMPLOYEES COUNT :'||L_EMPLOYEES.COUNT);
FOR I IN 1..L_EMPLOYEES.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE('OUNAME IS :'||L_EMPLOYEES(I).NAME);
END LOOP;
END;
/
Bulk Collect used in cursor...fetch...statement
Syntax:
*******
fetch cursorname bulk collect into collection variable LIMIT.
EX:
***
DECLARE
TYPE T1 IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER;
LV_TYPE T1;
CURSOR C1 IS SELECT * FROM EMP;
BEGIN
OPEN C1;
FETCH C1 bulk collect INTO LV_TYPE;
CLOSE C1;
FOR I IN LV_TYPE.FIRST..LV_TYPE.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(LV_TYPE(I).ename||','||LV_TYPE(I).JOB);
END LOOP;
END;
/
DECLARE
TYPE T1 IS TABLE OF VARCHAR2(50);
LV_TYPE T1;
CURSOR C1 IS SELECT EMPNO FROM EMP;
BEGIN
OPEN C1;
LOOP
FETCH C1 BULK COLLECT INTO LV_TYPE LIMIT 5;
DBMS_OUTPUT.PUT_LINE('RESULTS FROM BULK FETCH');
FOR I IN 1..LV_TYPE.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID :'||LV_TYPE(I));
END LOOP;
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
END;
/
DECLARE
TYPE T1 IS TABLE OF VARCHAR2(50);
LV_TYPE T1;
CURSOR C1 IS SELECT SEGMENT1 FROM PO_HEADERS_ALL WHERE ROWNUM<=2000;
BEGIN
OPEN C1;
LOOP
FETCH C1 BULK COLLECT INTO LV_TYPE LIMIT 500;
DBMS_OUTPUT.PUT_LINE('RESULTS FROM BULK FETCH');
FOR I IN 1..LV_TYPE.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE('SEGMENT1 VALUE :'||LV_TYPE(I));
END LOOP;
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
END;
/
--Time program with out Bulk Collect
DECLARE
LV_TYPE VARCHAR2(50);
CURSOR C1 IS SELECT OBJECT_NAME FROM ALL_OBJECTS;
START_TIME NUMBER;
END_TIME NUMBER;
BEGIN
START_TIME:=DBMS_UTILITY.GET_TIME;
OPEN C1;
LOOP
FETCH C1 INTO LV_TYPE;
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
END_TIME:=DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE(START_TIME);
DBMS_OUTPUT.PUT_LINE(END_TIME);
DBMS_OUTPUT.PUT_LINE(END_TIME-START_TIME);
END;
/
--Time program with Bulk Collect
DECLARE
TYPE T1 IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
LV_TYPE T1;
CURSOR C1 IS SELECT OBJECT_NAME FROM ALL_OBJECTS;
START_TIME NUMBER(10);
END_TIME NUMBER(10);
BEGIN
START_TIME:=DBMS_UTILITY.GET_TIME;
OPEN C1;
LOOP
FETCH C1 BULK COLLECT INTO LV_TYPE;
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
END_TIME:=DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE(START_TIME);
DBMS_OUTPUT.PUT_LINE(END_TIME);
DBMS_OUTPUT.PUT_LINE(END_TIME-START_TIME);
END;
/
--nested table for loop using cursor
DECLARE
TYPE EMP_TYPE IS TABLE OF VARCHAR2(20);
LV_EMP_TYPE EMP_TYPE :=EMP_TYPE();
BEGIN
FOR I IN (SELECT * FROM EMP)
LOOP
LV_EMP_TYPE.EXTEND;
LV_EMP_TYPE(LV_EMP_TYPE.LAST):=I.ENAME;
END LOOP;
FOR I IN LV_EMP_TYPE.FIRST..LV_EMP_TYPE.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(LV_EMP_TYPE(I));
END LOOP;
END;
/
--nested table using bulk collect in for loop
DECLARE
TYPE EMP_TYPE IS TABLE OF VARCHAR2(20);
LV_EMP_TYPE EMP_TYPE :=EMP_TYPE();
BEGIN
SELECT ENAME BULK COLLECT INTO LV_EMP_TYPE FROM EMP;
FOR I IN LV_EMP_TYPE.FIRST..LV_EMP_TYPE.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(LV_EMP_TYPE(I));
END LOOP;
END;
/
DECLARE
TYPE EMP_TYPE IS TABLE OF VARCHAR2(20);
LV_EMP_TYPE EMP_TYPE :=EMP_TYPE();
LV_EMP_TYPE1 EMP_TYPE :=EMP_TYPE();
BEGIN
--SIMPLE FOR LOOP
FOR I IN (SELECT * FROM EMP)
LOOP
LV_EMP_TYPE.EXTEND;
LV_EMP_TYPE(LV_EMP_TYPE.LAST):=I.ENAME;
END LOOP;
FOR I IN LV_EMP_TYPE.FIRST..LV_EMP_TYPE.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(LV_EMP_TYPE(I));
END LOOP;
--BULK COLLECT
SELECT ENAME BULK COLLECT INTO LV_EMP_TYPE1 FROM EMP;
FOR I IN LV_EMP_TYPE1.FIRST..LV_EMP_TYPE1.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(LV_EMP_TYPE1(I));
END LOOP;
END;
/
BULK BIND:
**********
-->In bulk bind processs we are performing bulk of operations using collection i.e in this process we are using bulk update,bulk delete and bulk insert using for all statement.
Syntax:
*******
forall indexvariable in collectionvariable.first..collectionvariable.last
CREATE TABLE XX_BULK
(
SNO NUMBER
);
/
CREATE TABLE XX_BULK_BIND
(SNO NUMBER
);
/
--TO INSERT DATA INTO A TABLE USING FOR LOOP
DECLARE
START_TIME NUMBER;
END_TIME NUMBER;
BEGIN
START_TIME:=DBMS_UTILITY.GET_TIME;
FOR I IN 1..1000000
LOOP
INSERT INTO XX_BULK VALUES (I);
END LOOP;
COMMIT;
END_TIME:=DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE(START_TIME);
DBMS_OUTPUT.PUT_LINE(END_TIME);
DBMS_OUTPUT.PUT_LINE(END_TIME-START_TIME);
END;
/
--TO INSERT A DATA INTO TABLE USING FOR ALL
DECLARE
START_TIME NUMBER;
END_TIME NUMBER;
TYPE T1 IS TABLE OF XX_BULK%ROWTYPE;
V1 T1;
BEGIN
START_TIME:=DBMS_UTILITY.GET_TIME;
SELECT * BULK COLLECT INTO V1 FROM XX_BULK;
FORALL I IN 1..V1.COUNT
INSERT INTO XX_BULK_BIND VALUES V1(I);
COMMIT;
END_TIME:=DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE(START_TIME);
DBMS_OUTPUT.PUT_LINE(END_TIME);
DBMS_OUTPUT.PUT_LINE(END_TIME-START_TIME);
END;
EX:
***
--BULK UPDATE
DECLARE
TYPE T1 IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
V1 T1;
BEGIN
SELECT EMPNO BULK COLLECT INTO V1 FROM EMP1;
FORALL I IN V1.FIRST..V1.LAST
UPDATE EMP1
SET SAL=SAL+100 WHERE EMPNO=V1(I);
commit;
END;
/
--BULK DELETE
DECLARE
TYPE T1 IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
V1 T1;
BEGIN
SELECT EMPNO BULK COLLECT INTO V1 FROM EMP1;
FORALL I IN V1.FIRST..V1.LAST
DELETE FROM EMP1
WHERE EMPNO=V1(I);
commit;
END;
/
--BULK INSERT
CREATE TABLE XPO_DATA(PO_HEADER_ID VARCHAR2(20),SEGMENT1 VARCHAR2(20));
/
DECLARE
TYPE T1 IS TABLE OF XPO_DATA%ROWTYPE;
LV_PO_DATA T1;
CURSOR C1 IS SELECT PO_HEADER_ID,SEGMENT1 FROM PO_HEADERS_ALL WHERE ROWNUM<=100;
BEGIN
OPEN C1;
FETCH C1 BULK COLLECT INTO LV_PO_DATA;
CLOSE C1;
DBMS_OUTPUT.PUT_LINE('TOTAL COUNT :'||LV_PO_DATA.COUNT);
--FOR I IN 1..LV_PO_DATA.COUNT
--LOOP
--DBMS_OUTPUT.PUT_LINE('PO HEADER ID IS : '||LV_PO_DATA(I).PO_HEADER_ID);
--END LOOP;
FORALL I IN 1..LV_PO_DATA.count
--LV_PO_DATA.FIRST..LV_PO_DATA.LAST
INSERT INTO XPO_DATA
VALUES LV_PO_DATA(I);
commit;
DBMS_OUTPUT.PUT_LINE('NO.OF.ROWS INSERTED :' ||SQL%ROWCOUNT);
END;
No comments:
Post a Comment