存储过程使用体验

1 使用存储过程循环复制表到新创建的表  并且更新新表数据

 

1 循环100次创建新表 并复制旧表数据 新表命名table_1,table_2...
2 更新 每一个新表中的colunm1 和 colunm2数据

create or replace PROCEDURE TABLE_CREATEANDCOPYDATE  AS
 
cSql varchar2(2000);
uSql1 varchar2(2000);
uSql2 varchar2(2000);
BEGIN
for n in  1..100 loop

    cSql:= 'create table  table_'||TO_CHAR(n)||'  as select * from table';
    dbms_output.put_line('cSql:'||cSql);
    execute immediate cSql;
 
    uSql1 :='update  table_'||TO_CHAR(n) || ' set colunm1 = ' || 
        ('colunm1'||'||'||''''||to_char(n)||'''');
    dbms_output.put_line('uSql1:'||uSql1);

    uSql2 :='update  table_'||TO_CHAR(n) || ' set colunm2 = ' || 
        ('colunm2'||'||'||''''||to_char(n)||'''');
    dbms_output.put_line('uSql2:'||uSql2);

    execute immediate uSql1;  
    execute immediate uSql2;  
  end loop;

END TABLE_CREATEANDCOPYDATE;

2 批量删除表

2 批量删除表

SET SERVEROUTPUT ON;

DECLARE
LNAME VARCHAR2(200);
BEGIN
FOR X IN (select TABLE_NAME from user_tables
where TABLE_NAME like 'table_20190219_%'
) LOOP
LNAME:= X.TABLE_NAME;
EXECUTE IMMEDIATE 'DROP TABLE '|| X.TABLE_NAME ;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(LNAME);
RAISE;
END;

COMMIT;