| Bulk Collect [message #434349] |
Tue, 08 December 2009 15:38  |
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  |
 |
BlackSwan
Messages: 26766
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 #434462 is a reply to message #434349] |
Wed, 09 December 2009 05:59 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You could rewrite the loop asFOR 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 |
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 #434706 is a reply to message #434539] |
Thu, 10 December 2009 05:22 |
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 .... |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
