[oracle package]sequence处理(批量创建、删除sequence,将sequence恢复至指定值)

之前对于oracle的包不太熟悉,最近深入学习了plsql中的包。在做项目过程中,操作数据库时,存在大量重复的工作,正好写个package来巩固下所学。

一.先对写的东西碰到的问题作下说明,也方便以后自己回顾解决问题的过程:

1.如何给存储过程或函数传递数组参数

https://blog.csdn.net/zzkongfu/article/details/7480958

https://blog.csdn.net/wonder4/article/details/649869

2.如果直接运行创建sequence的语句可以正常运行,用的也是dba权限,但是在存储过程、函数、包中写的创建sequence的语句运行后却提示没有权限

https://blog.csdn.net/jerryitgo/article/details/79220598

http://www.cnblogs.com/yhoralce/p/6817010.html?utm_source=itdadao&utm_medium=referral

3.如何给序列做回退操作

(1)网上有说无法回退,只能删掉重新创建;

(2)其实还有别的方法,可以利用sequence本身的increment值来处理

https://blog.csdn.net/u010999809/article/details/79943924 这个说的是将数据库里的所有序列统一回退某个值(比如统一减少100)

https://blog.csdn.net/pete_emperor/article/details/82853277 这个说的是将数据库里某个序列恢复至指定值

下面写的是第二个链接的情况(借鉴的也是第二种。这两个链接实现效果不同,但原理都是一样的,都利用了sequence本身的increment值来处理)

二.接下来,直接上代码:

create or replace package myutil_pkg authid current_user as

  --定义通用的字符串数组参数类型(传入或返回参数)
  type tab_str is table of varchar2(30);

  /**
  * 将序列的当前值恢复至指定数字
  * seqName 序列名称
  * num 需要恢复到哪个数字
  **/
  --使用示例:
  /**
  * begin
  *   myutil_pkg.updateSeqToNum('seq_mytest',1);
  * end;
  *
  * 运行select seq_mytest.currval from dual;可以进行验证
  */
  procedure updateSeqToNum(seqName varchar2, num number);
  
  /**
  * 批量创建序列
  * seqNames 序列名称数组
  **/
  --使用示例:
  /*
  declare
    seqNames myutil_pkg.tab_str:=myutil_pkg.tab_str(null);
  begin
    seqNames.extend(2,1);
    seqNames(1):='seq_mytest1';
    seqNames(2):='seq_mytest2';
    seqNames(3):='seq_mytest3';
    myutil_pkg.createSeqs(seqNames);
  end;
  */
  procedure createSeqs(seqNames tab_str);
  
  /**
  * 批量删除序列
  * seqNames 序列名称数组
  **/
  --使用示例:
  /*
  declare
    seqNames myutil_pkg.tab_str:=myutil_pkg.tab_str(null);
  begin
    seqNames.extend(2,1);
    seqNames(1):='seq_mytest1';
    seqNames(2):='seq_mytest2';
    seqNames(3):='seq_mytest3';
    myutil_pkg.dropSeqs(seqNames);
  end;
  */
  procedure dropSeqs(seqNames tab_str);

end myutil_pkg;
create or replace package body myutil_pkg as

  /**
  * 将序列的当前值恢复至指定数字
  * seqName 序列名称
  * num 需要恢复到哪个数字
  **/
  procedure updateSeqToNum(seqName varchar2, num number) as
    n number;
    comm_exception exception;
  begin
    if num < 1 then
      raise comm_exception;  
    else
      --https://blog.csdn.net/u010999809/article/details/79943924
      --https://blog.csdn.net/pete_emperor/article/details/82853277
      --获取序列的下一个值
      execute immediate 'select '||seqName||'.nextval from dual' into n;
      
      --修改序列的minvalue参数
      execute immediate 'alter sequence '||seqName||' minvalue 1';
    
      if n>1 then
        n:=-(n-num); --这里是要恢复到num(num>=1)
        --修改increment参数
        execute immediate 'alter sequence '||seqName||' increment by '||n;
        execute immediate 'select '||seqName||'.nextval from dual' into n;
        
        --恢复increment参数值
        execute immediate 'alter sequence '||seqName||' increment by 1';
      end if;
    end if;
  exception
    when comm_exception then
      raise_application_error(-20001,'序列数字不能小于1');
      
  end updateSeqToNum;
  
  /**
  * 批量创建序列
  * seqNames 序列名称数组
  **/
  procedure createSeqs(seqNames tab_str) as
  begin
    --https://blog.csdn.net/zzkongfu/article/details/7480958
    --https://blog.csdn.net/wonder4/article/details/649869
    --提示权限不足
    --https://blog.csdn.net/jerryitgo/article/details/79220598
    --http://www.cnblogs.com/yhoralce/p/6817010.html?utm_source=itdadao&utm_medium=referral
    --循环table中的数据
    for i in 1 .. seqNames.count loop
      --如果存放的不是空字符串,则拼接语句执行创建序列
      if seqNames(i) is not null then
        --dbms_output.put_line('++++++'||seqNames(i));
        execute immediate 'create sequence '||seqNames(i)||' '||
        'minvalue 1 start with 1 '||
        'increment by 1 cache 20';
      end if;
    end loop;
  end createSeqs;
  
  /**
  * 批量删除序列
  * seqNames 序列名称数组
  **/
  procedure dropSeqs(seqNames tab_str) as
  begin
    for i in 1 .. seqNames.count loop
      if seqNames(i) is not null then
        execute immediate 'drop sequence '||seqNames(i);
      end if;
    end loop;
  end dropSeqs;
end myutil_pkg;

 

没有更多推荐了,返回首页

私密
私密原因:
请选择设置私密原因
  • 广告
  • 抄袭
  • 版权
  • 政治
  • 色情
  • 无意义
  • 其他
其他原因:
120
出错啦
系统繁忙,请稍后再试