oracle适用于,几个惯用的存储过程，适用于ORACLE

几个常用的存储过程，适用于ORACLE

在工作中常用的存储过程，在此做一个总结。全部都是我自己写的，如果有问题欢迎指正。

1.清空所有表的数据，谨慎使用。

在搭建开发环境的时候，时常需要清除所有表的数据，而只留表结构，所以写了一个存储过程，使用它的前提是没有外键约束。

--清空表数据

create or replace procedure pro_clean_all_table as

v_sql varchar2(2000 char);

cursor cur is select table_name from user_tables order by table_name;

i integer;

begin

i:=0;

for rows in cur loop

v_sql:='truncate table "'||rows.table_name||'"';

i:=i+1;

execute immediate v_sql;

commit;

end loop;

dbms_output.put_line('cleaned '||i||' table(s).');

end pro_clean_all_table;

2.把所有的以字节定义的varchar2的长度改为以字符定义的varchar2长度。如果以字节定义会引起中文长度无法控制的问题。

create or replace procedure pro_fix_varchar as

cursor fieldList is

select T1.TABLE_NAME,T1.COLUMN_NAME,T1.DATA_LENGTH from USER_TAB_COLUMNS T1

left join user_tables T2 on T2.TABLE_NAME=T1.TABLE_NAME

where T2.TABLE_NAME is not null

and T2.TABLESPACE_NAME=(select DEFAULT_TABLESPACE from USER_USERS)

and CHAR_USED='B';

tblName varchar2(2000);

fieldName varchar2(2000);

dataLen varchar2(10);

sqlStr varchar2(2000);

cnt integer;

BEGIN

dbms_output.put_line('begin');

cnt:=0;

open fieldList;

loop

fetch fieldList into tblName,fieldName,dataLen;

exit when fieldList%notfound;

sqlStr:='alter table "'||tblName||'" modify ("'||fieldName||'" varchar2('||dataLen||' char))';

execute immediate sqlStr;

commit;

cnt:=cnt+1;

end loop;

close fieldList;

dbms_output.put_line('fixed '||cnt||' field(s).');

end pro_fix_varchar;

3.【重要】给所有表增加table txtend。在ORACLE 11g R2以上版本中，如果需要备份数据，请执行。

--ORACLE 11G R2 增加table txtend

create or replace procedure add_table_extend as

v_sql varchar2(2000 char);

cursor cur is

select TABLE_NAME from user_tables

where table_name not in

(select segment_name from user_segments where segment_type = 'TABLE');

i integer;

begin

i:=0;

for rows in cur loop

v_sql:='alter table '||rows.TABLE_NAME||' allocate extent (size 64k);';

execute immediate v_sql;

i:=i+1;

end loop;

commit;

dbms_output.put_line('fixed '||i||' table(s).');

end add_table_extend;

我的异常网推荐解决方案：oracle存储过程,http://www.myexceptions.net/oracle-develop/177537.html