学步园

 

 

declare
  v_sql varchar2(1000);
  cursor cur is select 'alter table "'||t1.table_name||'" rename to '||upper(t1.table_name) as sqlstr
              from user_tables t1 
             where table_name<>upper(table_name) 
               and not exists (select 1 from user_tables t2 where t2.table_name=upper(t1.table_name) );
begin
  for rur in cur loop
    v_sql := rur.sqlstr;
    execute immediate v_sql;
  end loop;
end;

 

 

测试代码：

scott@SZTYORA> create table "ttt" as select * from t;
表已创建。
已用时间:  00: 00: 00.07
scott@SZTYORA> create table ttt as select * from t;
表已创建。
已用时间:  00: 00: 00.06
scott@SZTYORA> commit;
提交完成。
已用时间:  00: 00: 00.01
scott@SZTYORA> select 'alter table "'||t1.table_name||'" rename to '||upper(t1.table_name) as sqlstr
  2                from user_tables t1
  3               where table_name<>upper(table_name)
  4                 and not exists (select 1 from user_tables t2 where t2.table_name=upper(t1.table_name) );
未选定行
已用时间:  00: 00: 00.06
scott@SZTYORA> drop table ttt;
表已删除。
已用时间:  00: 00: 00.01
scott@SZTYORA> commit;
提交完成。
已用时间:  00: 00: 00.00
scott@SZTYORA> select 'alter table "'||t1.table_name||'" rename to '||upper(t1.table_name) as sqlstr
  2                from user_tables t1
  3               where table_name<>upper(table_name)
  4                 and not exists (select 1 from user_tables t2 where t2.table_name=upper(t1.table_name) );
SQLSTR
------------------------------------------------------------------------------------------------------------------------
alter table "ttt" rename to TTT
已用时间:  00: 00: 00.07
scott@SZTYORA> declare
  2    v_sql varchar2(1000);
  3    cursor cur is select 'alter table "'||t1.table_name||'" rename to '||upper(t1.table_name) as sqlstr
  4                from user_tables t1
  5               where table_name<>upper(table_name)
  6                 and not exists (select 1 from user_tables t2 where t2.table_name=upper(t1.table_name) );
  7  begin
  8    for rur in cur loop
  9      v_sql := rur.sqlstr;
 10      execute immediate v_sql;
 11    end loop;
 12  end;
 13  /
PL/SQL 过程已成功完成。
已用时间:  00: 00: 00.39
scott@SZTYORA> desc "ttt";
ERROR:
ORA-04043: 对象 "ttt" 不存在
scott@SZTYORA> desc ttt;
 名称                                                              是否为空? 类型
 ----------------------------------------------------------------- -------- --------------------------------------------
 COL_NAME                                                                   VARCHAR2(20)
scott@SZTYORA>

 

改成存储过程：

 

create or replace procedure alertTableName
AS  
    v_sql varchar2(1000);
    counter number;
    cursor cur is select 'alter table '||t1.table_name||' rename to old_'||upper(t1.table_name) as sqlstr
                from user_tables t1 
               where table_name<>'old_'||table_name 
                 and not exists (select 1 from user_tables t2 where t2.table_name='old_'||table_name );
  begin
    counter:=1;            
    for rur in cur loop
      v_sql := rur.sqlstr;
      execute immediate v_sql;
      DBMS_OUTPUT.PUT_LINE(counter||'---'||v_sql);
      counter:=counter+1;
    end loop; 
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
     ROLLBACK ;
     RETURN;  
  end;

 

http://topic.csdn.net/u/20100705/16/f3a3301d-7759-4fca-9138-f1406e195196.html?177234244

 

---

 

 

批量插入数据，每500条commit一次~

 

CREATE OR REPLACE PROCEDURE insert_tbl_cus_proc(info_list    IN  tbl_cus_table_type,
                              out_message  OUT VARCHAR2) IS
BEGIN
   FOR i IN 1.. info_list.count LOOP
      INSERT INTO tbl_cus(ID,cusname,phone,state)
      VALUES (info_list(i).id,info_list(i).cusname,info_list(i).phone,info_list(i).state);
      IF MOD(i,500) = 0 THEN
         COMMIT;
      END IF;
   END LOOP;
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      out_message := 'ERROR!';
      ROLLBACK;
END;

 

http://topic.csdn.net/u/20100823/16/b68064fd-1b93-41ce-b3b7-a7e679993958.html

 

---

insert into TABLE1
(a, b, c, d)
select id1,id2,id3,id4 
from TABLE2;