oracle 批量改表名/插入记录

 

 

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;