Re: Looking for a faster way to Update/Insert

From: Sharkie <sharkie2_at_my-deja.com>
Date: Mon, 27 Mar 2000 18:57:32 GMT
Message-ID: <8boaui$jd1$1@nnrp1.deja.com>

or much easier with:

insert into tableB select * from tableA [where clause optional].

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).
>
> Lets say you create a function:
>
> ops$tkyte_at_8i> create or replace procedure dyn_insert( p_package_name
in
> varchar2,
> 2 p_var_name in varchar2,
> 3 p_tname in varchar2 )
> 4 as
> 5 l_clist long;
> 6 l_vlist long;
> 7 l_stmt long;
> 8 l_sep char(1);
> 9 begin
> 10 for x in ( select column_name from user_tab_columns where
table_name =
> upper(p_tname) )
> 11 loop
> 12 l_clist := l_clist || l_sep || x.column_name;
> 13 l_vlist := l_vlist || l_sep || p_package_name || '.' || p_var_name
> || '.' || x.column_name;
> 14 l_sep := ',';
> 15 end loop;
> 16
> 17 l_stmt := 'begin insert into ' || p_tname ||
> 18 '(' || l_clist || ') values (' || l_vlist || '); end;';
> 19
> 20 execute immediate l_stmt;
> 21 end;
> 22 /
>
> Procedure created.
>
> (execute immediate is an Oracle8i, release 8.1 feature, below I'll
supply an
> execute_immediate() function for 8.0 and before).
>
> That routine needs to know the name of a package spec (P_PACKAGE_NAME)
that has
> row variable (P_VAR_NAME) that is to be inserted into a table
(P_TNAME).
>
> Now I:
>
> ops$tkyte_at_8i> create table emp as select * from scott.emp;
> Table created.
>
> for some demo data and then I:
>
> ops$tkyte_at_8i> create or replace package row_types
> 2 as
> 3 g_emp_row emp%rowtype;
> 4 end;
> 5 /
> Package created.
>
> thats the spec that'll hold my global variable. Now I can simply:
>
> ops$tkyte_at_8i>
> ops$tkyte_at_8i>
> ops$tkyte_at_8i> begin
> 2 select * into row_types.g_emp_row from emp where rownum = 1;
> 3
> 4 row_types.g_emp_row.empno := 1;
> 5
> 6 dyn_insert( 'row_types', 'g_emp_row', 'emp' );
> 7 end;
> 8 /
>
> PL/SQL procedure successfully completed.
>
> and to show it worked:
>
> ops$tkyte_at_8i>
> ops$tkyte_at_8i> select * from emp where rownum = 1;
>
> EMPNO ENAME JOB MGR HIREDATE SAL COMM
> DEPTNO
> ---------- ---------- --------- ---------- --------- ----------

---

> ----------
> 7369 SMITH CLERK 7902 17-DEC-80 800
> 20
>
> ops$tkyte_at_8i> select * from emp where empno = 1;
>
> EMPNO ENAME JOB MGR HIREDATE SAL COMM
> DEPTNO
> ---------- ---------- --------- ---------- --------- ----------

---

> ----------
> 1 SMITH CLERK 7902 17-DEC-80 800
> 20
>
> It 'copied' that row for me. execute_immediate in 8.0 and before can
be:
>
> create or replace procedure execute_immediate( p_sql in varchar2 )
> is
> cursor_name pls_integer default dbms_sql.open_cursor;
> ignore pls_integer;
> BEGIN
> dbms_sql.parse(cursor_name, p_sql, dbms_sql.native);
> ignore := dbms_sql.execute(cursor_name);
> dbms_sql.close_cursor(cursor_name);
> END;
> /
>
> --
> 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
>

--
If the human brain were so simple
that we could understand it,
we would be so simple we couldn't.
-Makes Sense... don't it?

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Mar 27 2000 - 12:57:32 CST