Oracle存储过程创建及调用学习整理

男娘i 2022-05-20 10:36 553阅读 0赞

1.简单的存储过程创建及调用

create or replace procedure pro_add(v1 in number,v2 in number,v3 out number)
    is
    begin
    v3:=v1+v2;
    end;

declare
      v3 number(10);
    begin 
      v3:=0;
      pro_add(100,200,v3);
      DBMS_OUTPUT.PUT_LINE('输出结果：'||v3);
    END;

2.存储过程返回数据集创建及调用（包、游标、存储过程）

Oracle存储过程返回数据集，其实就是返回游标，这里需要了解这样几个概念，游标、存储过程的out参数、引用游标类型、Oracle的程序包，这里只说明具体怎么做，如果想了解以上概念请自行在网上搜索或关注我的博客。

首先说一下思路：

定义一个包（这相当于一个容器）（也可以理解为Java中的类）

包中包含一个存储过程来返回一个游标引用

包中包含一个自定义类型 游标引用（这个类型也可以应用到其它地方，这在调用存储过程时用到）

定义包分两部分：包头、包体

包头部分定义包中应该包含哪些内容（方法，存储过程等只做声明不做实现）（相当于Java中的接口）

create or replace package pkg_alen
    as
    type cursorRef is ref cursor; --定义游标引用类型
    procedure query(u_id number,cursor_ref out cursorRef); --定义存储过程声明
    end pkg_alen;

包体部分定义包头中的方法，存储过程等的实现（相当于Java中实现接口的类），注意必须实现包头中声明的所有方法和存储过程。

create or replace package body pkg_alen --注意这里的包名要和定义包头的包名一致
    as
    procedure query(u_id number,cursor_ref out cursorRef) --这里的存储过程要和包头中的一致参数列表中的参数名也要一致
    is
    begin
    if u_id=0 then --如果传入的id为0则查询所有数据
    open cursor_ref for select id,ck_name from dm_pac_ck;
    else   --如果传入的id为非0则查询指定id的数据
    open cursor_ref for select id,ck_name from dm_pac_ck where id=u_id; --根据查询打开游标
    end if;
    end query;
    end pkg_alen;

Ok，到些返回数据集的存储过程已经编写完成，这里需要注意的是：1、包头定义部分要先执行然后执行包体部分；2、cursorRef 类型为自定义类型 其原型为 ref coursor。

网上的资料很多都到此为止，但是有好多新手（也包括我在内）并不知道怎么调用，下面我就来和大家说一下怎么在SQL中调用（亲测可用）

使用SQL调用上面定义的存储过程，这里需要注意了，调用存储过程的参数和参数类型（这个困扰了我好长时间），第一个参数没什么可说的就一数字型，主要是第二个参数，这个参数的类型是pkg\_alen.cursorRef，没错就是这个自定义的类型（之前我尝试了 ref cursor 和定义一个新的ref cursor类型但是都报错）

set serveroutput on;
    declare
    return_cursor pkg_alen.cursorRef; --定义接收返回数据集的变量
    v_id number(4);   --定义接收id的变量
    uname varchar2(50);  --定义接收name的变量
    begin 
    pkg_alen.query(0,return_cursor);  --调用包中的存储过程返回游标引用
    loop fetch return_cursor into v_id,uname; --循环获取游标中每一行的数据
    exit when return_cursor%notfound;          --游标结束时退出
    dbms_output.put_line('id:'||v_id||' name:'||uname); --将获取到的值打印出来
    end loop;    --循环结束
    close return_cursor;  --关闭游标
    end;

这里注意游标我们并没有打开只有关闭，因为游标的打开是在存储过程中完成的。

java中如何通过mybatis调用存储过程[《Mybatis调用Oracle中的包和存储过程》][Mybatis_Oracle]

[Mybatis_Oracle]: https://blog.csdn.net/huxiangen/article/details/81112487