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;

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