欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

几个常用的存储过程,适用于ORACLE 博客分类: 数据库  

程序员文章站 2024-03-25 09:22:58
...

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

 

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 extend。在ORACLE 11g R2以上版本中,如果需要备份数据,请执行。

 

--ORACLE 11G R2 增加table extend
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;