1.创建一个表Test用来测试.
CREATE TABLE "TEST"
( "AAA" NUMBER(*,0),
"BBB" VARCHAR2(10 BYTE)
)
2.向Test表中插入测试数据
insert into Test values(1,'a');
insert into Test values(2,'b');
insert into Test values(3,'c');
insert into Test values(1,'aaaa');
insert into Test values(2,'bbbbb');
insert into Test values(3,'cccccc');
3.进入主题,创建存储过程
create or replace procedure pro1(
param nvarchar2,
mycur OUT sys_refcursor --游标,用于存储过程中返回结果集
)
as
begin
open mycur for select * from test where AAA=param ;
end;
4.执行存储过程
var type_cur refcursor
var para nvarchar2(20)
execute :para :='1';
exec pro1(:para,:type_cur);
print type_cur;
5.使用程序包package创建存储