Re: Looking for a faster way to Update/Insert

A copy of this was sent to Sharkie <sharkie2_at_my-deja.com> (if that email address didn't require changing) On Mon, 27 Mar 2000 18:57:32 GMT, you wrote:

>or much easier with:
>
>insert into tableB select * from tableA [where clause optional].
>

I read the question as "how do i insert a plsql record into a table" and "how do I update from a plsql record". I assumed there would be some procedural logic in there somewhere precluding a simple insert into .. select * from.

So, if the question is "how do i insert a plsql record" or "how do I update from a plsql record", dynamic sql is the only way right now.

>
>In article <f71vdssgv2dfgtr4stek7heol6p1hbtd00_at_4ax.com>,
>tkyte_at_us.oracle.com wrote:
>> A copy of this was sent to Ken Sproule <kenmn_at_tds.net>
>> (if that email address didn't require changing)
>> On Mon, 27 Mar 2000 14:02:09 GMT, you wrote:
>>
>> >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
>>
>> If you are willing to put your row variables in a package spec, i can
>show you
>> how to do this with dynamic sql. It is 'bind variable friendly' and
>can work
>> for updates as well as inserts (i'll demonstrate the insert, the
>update is very
>> similar).
>>

[snip]

--
http://osi.oracle.com/~tkyte/  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Mar 27 2000 - 15:19:41 CST