Using Cursors and Cursor Vars to Insert/update - How faster?

From: Ken Sproule <kenmn_at_tds.net>
Date: Thu, 23 Mar 2000 13:35:40 GMT
Message-ID: <b57kdscqe479ojom06uhn15q50vito9e7d@4ax.com>

Thanks in advance everyone for your help.

I am trying to figure out if it's possible to use a cursor row record or equivalent with cursor variables to ease the coding necessary to insert and update tables. Below is my pseudo-code for what I'd like to do, but which to this point haven't figured out how to do it.

create or replace procedure test
is

	cursor cur is select * from table_A;
	cv cur%ROWTYPE;

begin

	for cv in cur loop
		insert into TABLE_B values( CV ); -- somehow use the

cv row to supply all the values
without doing a cv.field1 , cv.field2 etc. , but do it automatically all at once.

        end loop;
end;
/

I would also like a way to accomplish a similar thing for updates, as in:

	update TABLE_B insert cv where current of table_A;  OR
	update table_A insert cv where current of table_A;

In the first one I am assuming that table_A and TABLE_B have exactly the same columns, etc.

I would think there would be a way to take advantage of the fact that Oracle already houses all the columns in the above "cv" rowtype. I would like think they have a simple way to utilize all the values for the columns all at once.

Does any one know how to do this?

Thanks again,

Ken Sproule
kenmn_at_tds.net

Ken Sproule
kenmn_at_tds.net Received on Thu Mar 23 2000 - 07:35:40 CST