1. What is Bulk Collect In PL/SQL?
Using Oracle BULK COLLECT technique enables the PL/SQL engine to collect many rows at once and place them in a collection of array.
A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection.
You should have heard that BULK COLLECT together with FORALL can help a PL/SQL to perform better in terms of average execution time.
The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. FORALL does not require a LOOP command.
BULK COLLECT and FORALL statements can drastically improve the performance. For example, I have rewritten a PL/SQL procedure used in a batch job and in result the execution time has been reduced from 40 minutes to 30 seconds only by using BULK COLLECT and FORALL.
DECLARE
-- Begin Cursor Definition
CURSOR bulk_table_select IS
SELECT TAB.object_id,
TAB.apps_id,
TAB.batch_id
FROM table_select TAB_SELECT;
TYPE object_id_tab IS TABLE OF table_select.object_id%TYPE INDEX BY BINARY_INTEGER;
TYPE apps_id_tab IS TABLE OF table_select.apps_id%TYPE INDEX BY BINARY_INTEGER;
TYPE batch_id_tab IS TABLE OF table_select.batch_id%TYPE INDEX BY BINARY_INTEGER;
lt_object_id object_id_tab;
lt_apps_id apps_id_tab;
lt_batch_id batch_id_tab;
BEGIN
-- Begin Bulk Select & Delete
OPEN bulk_table_select;
LOOP
FETCH bulk_table_select BULK COLLECT INTO lt_object_id,lt_apps_id,lt_batch_id
LIMIT 5000;
EXIT WHEN lt_batch_id.COUNT = 0;
FORALL i IN lt_batch_id.FIRST..lt_batch_id.LAST
DELETE FROM table_delete TAB_DELETE
WHERE batch_id = lt_batch_id(i)
AND apps_id = lt_apps_id(i);
END LOOP;
CLOSE bulk_table_select;
commit;
END;
Another Example with runtime limit clause parameter:
CREATE OR REPLACE PROCEDURE update_rows_with_limit (p_commit_row_count NUMBER)
IS
stat VARCHAR2 (32000);
TYPE ref_cur IS REF CURSOR;
c ref_cur;
TYPE myarray IS TABLE OF VARCHAR2 (500)
INDEX BY BINARY_INTEGER;
rid myarray;
tot_rows NUMBER := 0;
BEGIN
stat := 'select rowid rid from emp e where sal<3000';
OPEN c FOR stat;
LOOP
FETCH c
BULK COLLECT INTO rid LIMIT p_commit_row_count;
IF rid.FIRST > 0
THEN
FORALL i IN rid.FIRST .. rid.LAST
EXECUTE IMMEDIATE 'update emp set sal=sal+1000 where rowid=:rno'
USING rid (i);
COMMIT;
END IF;
tot_rows := tot_rows + rid.LAST;
EXIT WHEN c%NOTFOUND;
END LOOP;
END;
ORA-04030: out of process memory when trying to allocate 16396 bytes (koh-kghu call ,pl/sql vc2)
OPEN c FOR stat;
LOOP
FETCH c
BULK COLLECT INTO q_alias_name LIMIT 1000;
IF q_alias_name.FIRST > 0
THEN
FORALL i IN q_alias_name.FIRST .. q_alias_name.LAST
INSERT INTO EP_COLUMN_NAMES
(ep_pdsu_id, entity, column_alias_name,
when_created, who_created, when_updated, who_updated
)
VALUES (pdsu_id, p_entity, q_alias_name (i),
SYSDATE, p_userid, SYSDATE, p_userid
);
EXIT WHEN c%NOTFOUND;
END IF;
END LOOP;
Here q_alias_name.FIRST > 0 means if stat return values then above code works fine..otherwise it will go into infinite loop. so u need to put EXIT WHEN c%NOTFOUND; at outside of the end if like below.
EXIT WHEN c%NOTFOUND;--(wrong)
END IF;
EXIT WHEN c%NOTFOUND;
END LOOP;
Decode:
1.It’s a function
2.can compare only discrete vaules
3.Pl/Sql standards
4.cannot process null
Case:
1.It's an Expression
2.can hande range values
3.ANSI Standards
4.Processing time is faster when compared to Decode
5.can process null
Decode
The default operator for comparison is '=',
for e.g select deptno,decode
(deptno,10,'dept10',20,dept20,default)
so if the deptno=10 then the value is replaced by dept10
and so on,
whereas in Case:
we can use any operator other than '=',
for e.g
select deptno,
case when deptno=10 and sal>1000 then 'dept10'
else
'dept20'
Decode: using for reporting purpose. and it is implemented
by if stmt.
Ex: select decode(job,'CLERK','executive',job) from emp;
Case : implemented by if & elsif condition. it is using
pl/sql equality conditionality.
Ex : select case job='CLERK' then 'executive' else 'no' end
a from emp;
it is also used for multipul colms & multi conditions.
in above stmt a is reffered alias name.
There is one more Important difference between CASE and DECODE
DECODE can be used Only inside SQL statement....
But CASE can be used any where even as a paramtre of a function/procedure
Eg:-
Code: [Select all] [Show/ hide]SQL> create or replace procedure pro_01(n number) is
2 begin
3 dbms_output.put_line(' The number = '||n);
4 End;
5 /
Procedure created.
SQL> set serverout on
SQL> var a varchar2(5);
SQL> Begin
2 :a := 'ONE';
3 End;
4 /
PL/SQL procedure successfully completed.
SQL> Begin
2 pro_01(Decode(:a,'ONE',1,0));
3 End;
4 /
pro_01(Decode(:a,'ONE',1,0));
*
ERROR at line 2:
ORA-06550: line 2, column 9:
PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL
statement only
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored
SQL> Begin
2 pro_01(case :a when 'ONE' then 1 else 0 end);
3 End;
4 /
The number = 1
PL/SQL procedure successfully completed.
