Home » SQL & PL/SQL » SQL & PL/SQL » Bulk Collect (Oracle 10)
Bulk Collect [message #434349] Tue, 08 December 2009 15:38 Go to next message
hiswapna
Messages: 6
Registered: December 2005
Location: Hyderabad
Junior Member
create or replace TYPE emp_arr as object (
emp_id NUMBER,
proj_id NUMBER
proj_start_date date,
proj_end_date date,
proj_location VARCHAR(100),
);

create or replace TYPE table_emp AS TABLE OF emp_arr;


CREATE OR REPLACE PROCEDURE emp_create (emp_det IN table_emp,
emp_id_out OUT number,
errmsg OUT number)
cnt number;
AS
begin

FOR i IN emp_det.first .. emp_det.last LOOP

SELECT COUNT(*)
INTO cnt
FROM emp_proj
WHERE emp_id = emp_det(i).emp_id
AND proj_id=emp_det(i).proj_id;

IF cnt>0 THEN
UPDATE emp_proj
SET prj_st_dt = emp_det(i).proj_start_date,
prj_end_dt=emp_det(i).proj_end_date
prj_loc= emp_det(i).proj_location
WHERE emp_id = emp_det(i).emp_id
AND proj_id=emp_det(i).proj_id;


INSERT INTO emp_proj(emp_id, prj_id, prj_st_dt, prj_end_dt, prj_loc) VALUES (emp_det(i).emp_id,emp_det(i).proj_id, emp_det(i).proj_start_date,
emp_det(i).proj_end_date,emp_det(i).proj_location)
END if;
END loop;
emp_id_out:=emp_det(i).emp_id
end;


Question : how can I use bulk collect to improve the performance of the procedure...or is there any other good way of writing the procedure ...(may be using cast(table...etc...

[Updated on: Tue, 08 December 2009 15:48]

Report message to a moderator

Re: Bulk Collect [message #434351 is a reply to message #434349] Tue, 08 December 2009 16:58 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

You start by post code that only contains 100% valid syntax.
At least 3 different syntax errors exist in your post.

>UPDATE emp_proj
>INSERT INTO emp_proj

Please clarify & explain why doing UPDATE & INSERT into same table with the same data?

[Updated on: Tue, 08 December 2009 18:06]

Report message to a moderator

Re: Bulk Collect [message #434367 is a reply to message #434349] Tue, 08 December 2009 23:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Use SQL*Plus and copy and paste your session when you have a procedure that compiles.

Regards
Michel

Re: Bulk Collect [message #434457 is a reply to message #434349] Wed, 09 December 2009 05:54 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Look for merge command.

HTH.
Re: Bulk Collect [message #434462 is a reply to message #434349] Wed, 09 December 2009 05:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could rewrite the loop as
FOR i IN emp_det.first .. emp_det.last LOOP

UPDATE emp_proj
SET prj_st_dt = emp_det(i).proj_start_date,
prj_end_dt=emp_det(i).proj_end_date
prj_loc= emp_det(i).proj_location
WHERE emp_id = emp_det(i).emp_id
AND proj_id=emp_det(i).proj_id;

IF sql%rowcount = 0 then -- no rows updated, so none exist

INSERT INTO emp_proj(emp_id, prj_id, prj_st_dt, prj_end_dt, prj_loc) VALUES (emp_det(i).emp_id,emp_det(i).proj_id, emp_det(i).proj_start_date,
emp_det(i).proj_end_date,emp_det(i).proj_location)
END if;
END loop;


but really I'd follow @michael_bialik s advice and use MERGE for this - you can remove the whole loop and do the entire thing in a single piece of SQL.
Re: Bulk Collect [message #434529 is a reply to message #434462] Wed, 09 December 2009 10:00 Go to previous messageGo to next message
hiswapna
Messages: 6
Registered: December 2005
Location: Hyderabad
Junior Member
my apologies for pasting a syntax wrong code.

My intention is to avoid looping through all the elements ...if my nested table type contains 100 rows...instead of looping through 100 elements and then performing a update/insert...i would like to bulk collect all the elements in one shot and do the processing so as to improve the performance of the procedure...

kindly guide me how can I use bulk collect or improve the performance of the procedure
Re: Bulk Collect [message #434539 is a reply to message #434529] Wed, 09 December 2009 11:17 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>kindly guide me how can I use bulk collect or improve the performance of the procedure

use MERGE combined with SELECT that load TYPE table in first place.
It can be done in single SQL without any PL/SQL.
Re: Bulk Collect [message #434706 is a reply to message #434539] Thu, 10 December 2009 05:22 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You don't even need the original query. TABLE_EMP is a type defined in SQL, so you can just do:
MERGE INTO emp_proj  tgt
USING (SELECT emp_id
             ,proj_id
             ,proj_start_date
             ,proj_end_date
             ,proj_location
       FROM   table(emp_det)) src
ON (src.emp_id = tgt.emp_id AND src.proj_id = tgt.proj_id)
WHEN MATCHED THEN UPDATE SET ....
WHEN NOT MATCHED THEN INSERT ....
Previous Topic: Ref Cursor
Next Topic: SELECT INTO error (merged 3)
Goto Forum:
  


Current Time: Fri Dec 09 15:11:52 CST 2016

Total time taken to generate the page: 0.31162 seconds