PL/SQL 存储过程示例

本示例演示了通过调用存储过程，返回记录集的方法。

1 在某个包中定义记录集类型

create or replace package pkg_tancheng is
type pkg_rc is ref cursor;
end pkg_tancheng;

关键词	描述
pkg_rc	记录集类型名
ref cursor	记录集类型
sys_refcursor	系统定义的 is ref cursor 类型

2、建立存储过程示例如下：

create or replace procedure tancheng_1(
i in out integer,
o out sys_refcursor
) is
begin
open o for
select i as i, i+i as i2, i*i as i3 from dual;
end tancheng_1;

关键词	描述
pkg_tancheng.pkg_rc	引用包中的类型
open	打开游标返回记录集

3、调用存储过程示例如下：

调用环境

语法

PL/SQL 测试

begin
tancheng_1(i => :i, o => :o);
end;

变量名	类型	值
i	Integer	5
o	Cursor	<Cursor>

ASP

<%
Set objcmd = Server.CreateObject("ADODB.Command")
objcmd.ActiveConnection = "DSN=otec_it;user id=risousu;password=otec;"
objcmd.CommandType = 4
objcmd.CommandText = "TANCHENG_1"
objCmd.Parameters.append objCmd.CreateParameter("@i",3,1,4,8)
set rs = objcmd.Execute()
response.write "rs(0)=" & rs(0) & "<BR>"
response.write "rs(1)=" & rs(1) & "<BR>"
response.write "rs(2)=" & rs(2) & "<BR>"
objcmd.ActiveConnection = Nothing
set objcnn = Nothing
%>

Sub abc()
Set objcmd = CreateObject("ADODB.Command")
objcmd.ActiveConnection = "DSN=otec_it;user id=risousu;password=otec;"
objcmd.CommandType = 4
objcmd.CommandText = "TANCHENG_1"
objcmd.Parameters.append objcmd.CreateParameter("@i", 3, 1, 4, 8)
Set rs = objcmd.Execute()
MsgBox "rs(0)=" & rs(0)
MsgBox "rs(1)=" & rs(1)
MsgBox "rs(2)=" & rs(2)
objcmd.ActiveConnection = Nothing
Set objcnn = Nothing
End Sub

关键词	描述
3,1,4,8	3:整型数据 1:输入参数 4:参数长度 8:参数值