Oracle存储过程

存储过程是在大型数据库系统中,一组为了完成特定功能的SQL语句集,它存储在数据库中,一次编译后永久有效,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升
以下存储过程的整理参考来源

  1. 创建存储过程
    可从工具栏中一次选择File-New-program window-procedure或者在左侧找到procedures文件夹右键点击New来创建存储过程
  2. 存储过程语法解释
    刚新建的存储过程包含的语法如下:其中test是存储过程名称,test后面的()为存储过程的参数,参数可有可无但有参数时需要指定参数类型,参数共有三种模式:in(输入,默认,可省略)、out(输出,不能省略)、in out(一般不用)。begin与end之间为需要执行的sql语句,注:sql语句中的表名和存储过程名称不能相同
    create or replace procedure test(Name in out type,Name in out type,...) is
    begin
    
    end test;
    
  3. 一个简单完整的无参数存储过程和有参数存储过程语法
    • 其中存储过程涉及的表创建如下:
    create table test
    ( id number,
      flag varchar2(10),
      age number
      )
    
    • 无参数:
     create or replace procedure p_d_test is
     begin
       insert into test values(4,'dgrhyj',23);
       commit;
     end p_d_test;
    
    • 有参数:
     create or replace procedure p_d_test2(id number,flag varchar2,age number) is
     begin
       insert into test values(id,flag,age);
       commit;
     end p_d_test2;
    
  4. 存储过程的测试
    当存储过程的脚本编译完成之后点击执行(齿轮)按钮如果出现编译成功的提示,即表明语句不存在语法错误,之后可以进行测试验证脚本逻辑的正确性。
    首先在procedure的文件夹中找到对应的存储过程名称,右键点击test
    image
    image
    有参数的存储过程首先再上图1中输入对应的参数值,然后点击开始测试按钮,之后可以单步进入的按钮可以查看存储过程执行过程的中间情况。
  5. 存储过程的调用
    调用写好的存储过程,只需要使用关键字call后接存储过程的名字即可
    call p_d_test
    call p_d_test2(4,'egreth',23)
  6. 变量定义及赋值
    存储过程的中间sql语句可能需要用到参数(如时间、日期等),变量的定义在is/as之后,begin之前,变量的赋值在begin之后,变量的赋值使用:=而不是=。
  7. 条件判断
    • if条件判断(if,elsif,else,end if),具体语法如下。注:其中如果在if中出现了return关键词,则跳出if结束整个存储过程的执行
    if 条件1 then
      语句1;
    elsif 条件2 then
      语句2;
    else 
      语句3;
    end if;
    
    • case when 条件判断
    case num(变量)
     when 条件1 then
      语句1;
     when 条件2 then
      语句2;
     else
      语句3;
     end case;
    
  8. 异常的捕获和处理
    一般在存储过程的最后加上sql异常的处理exception,具体语法如下:
    exception
    when others then
     begin
      rollback;
     end;
    
posted @ 2021-08-17 15:11  蔓越煤  阅读(242)  评论(0编辑  收藏  举报