Batch change of table names/insert records in 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;

 

 

Test code:

Scott @ sztyora> Create Table "TTT" as select * from T;

The table has been created.

Used time: 00: 00: 00.07
Scott @ sztyora> Create Table TTT as select * from T;

The table has been created.

Used time: 00: 00: 00.06
Scott @ sztyora> commit;

Submitted.

Used time: 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 ));

Unselected row

Used time: 00: 00: 00.06
Scott @ sztyora> drop table TTT;

The table has been deleted.

Used time: 00: 00: 00.01
Scott @ sztyora> commit;

Submitted.

Used time: 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
Bytes ------------------------------------------------------------------------------------------------------------------------
Alter table "TTT" RENAME to ttt

Used time: 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/

The PL/SQL process is successfully completed.

Used time: 00: 00: 00.39
Scott @ sztyora> DESC "TTT ";
Error:
ORA-04043: the object "TTT" does not exist

Scott @ sztyora> desc ttt;
Is the name empty? Type
---------------------------------------------------------------------------------------------------------------------
Col_name varchar2 (20)

Scott @ sztyora>

 

Change to stored procedure:

 

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

 

 

 

Insert data in batches, once every 500 lines of Data ~

 

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;