oralce存储过程实现不同用户之间的表数据复制

create or replace procedure prc_test123 is

    temp_columns varchar2(4000);

    sqltemp varchar(20000);

    cursor cur is select table_name from  user_tables a where a.table_name like 'ZY%';

    c_row cur%rowtype;

    cursor t_cur(tablename varchar2) is select column_name from user_tab_columns a where a.table_name=tablename order BY column_name;

    --type table_cur_type is ref cursor;

    primaryLKey varchar2(20);

begin

      for c_row in cur loop

        temp_columns:='';

        for t_c_row in t_cur(c_row.table_name) loop

            if temp_columns is null or temp_columns='' then

                dbms_output.put_line(temp_columns);

                temp_columns:=t_c_row.column_name;

            else

                temp_columns:=temp_columns||','||t_c_row.column_name;

            end if;

        end loop;

        --取出主键

        select column_name into primaryLKey from user_cons_columns cu,

        user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name =c_row.table_name;

        --插入没有的主键数据

        sqltemp:='insert into '||c_row.table_name ||' a ('||temp_columns||')  SELECT  '||temp_columns||'  from data_manage.'||c_row.table_name ||' b where not exists(select 1 from '||c_row.table_name ||' c where c.'||primaryLKey||'=b.'||primaryLKey||')';

        execute immediate  sqltemp;

        --更新已有的主键数据

        sqltemp:='update '||c_row.table_name ||' a set ('||temp_columns||') = (SELECT  '||temp_columns||'  from data_manage.'||c_row.table_name ||' b where  b.'||primaryLKey||'=a.'||primaryLKey||') where exists(select 1 from data_manage.'||c_row.table_name||' y where a.'||primaryLKey||'=y.'||primaryLKey||')';

        execute immediate  sqltemp;

      end loop;

end;

　　对于不同用户下同一张表的数据进行复制，已有数据进行更新，没有的数据进行插入