Re: Looking for a faster way to Update/Insert

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 27 Mar 2000 11:01:17 -0500
Message-ID: <f71vdssgv2dfgtr4stek7heol6p1hbtd00@4ax.com>

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 Received on Mon Mar 27 2000 - 10:01:17 CST