ASP 调用 Oracle 存储过程返回结果集

vgeejykiyd · 发表于 2016-8-6 07:50:38

Oracle 存储过程返回结果集用 ref cursor 实现。asp 使用 ADODB.RecordSet 来接收返回的 ref cursor。文件 adovbs.inc 是一些 ado 变量定义，写过asp的应该都知道，这里就不再列出其内容。试验步骤如下：

1. 建立Oracle包

CREATE OR REPLACE PACKAGE pkg_employees
AS
TYPE empcur IS REF CURSOR;

PROCEDURE getemprecords (
indeptno IN NUMBER,
p_cursor OUT empcur,
p_errorcode OUT NUMBER
);
END pkg_employees;
/

CREATE OR REPLACE PACKAGE BODY pkg_employees
AS
PROCEDURE getemprecords (
indeptno IN NUMBER,
p_errorcode OUT NUMBER,
p_cursor OUT empcur
)
IS
BEGIN
p_errorcode := 0;

OPEN p_cursor FOR
SELECT * FROM emp WHERE deptno = indeptno ORDER BY ename;
EXCEPTION WHEN OTHERS THEN
p_errorcode := SQLCODE;
END getemprecords;
END pkg_employees;
/

2. 测试驱动：Microsoft OLE DB Provider for Oracle

<%@LANGUAGE="VBSCript"%>
<% Option Explicit
Response.Buffer = True
%>

<% Dim objConn, objCmd, objRs
Set objConn = Server.CreateObject("ADODB.Connection")
Set objCmd = Server.CreateObject("ADODB.Command")
Set objRs = Server.CreateObject("ADODB.RecordSet")
Dim strConnection
' Use the correct SQL*NET connect string!!!!
strConnection = "PROVIDER=MSDAORA;DATA SOURCE=beq-local;USER ID=scott;PASSWORD=tiger;"
objConn.Open strConnection
objCmd.ActiveConnection = objConn
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "pkg_employees.getemprecords"
' Used dept 30 as an example
objCmd.Parameters.Append objCmd.CreateParameter("param1", adVarChar, adParamInput, 10, "30")
objCmd.Parameters.Append objCmd.CreateParameter("param3", adVarChar, adParamOutput, 10)
Set objRs = objCmd.Execute
%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft FrontPage 4.0">
<TITLE></TITLE>
</HEAD>
<BODY>
<B>ADO REF CURSOR DEMO Using the Microsoft OLE DB Provider for Oracle</B>
<P>
<TABLE BORDER="1">
<TR>
<TH>Emp No.</TH>
<TH>Emp Name</TH>
</TR>
<% Do While Not objRs.EOF %>
<TR>
<TD><%=objRs.Fields("empno").Value%></TD>
<TD><%=objRs.Fields("ename").Value%></TD>
</TR>
<% objRs.MoveNext
Loop
objRs.Close
Set objRs = Nothing
Set objCmd = Nothing
objConn.Close
Set objConn = Nothing
%>
</TABLE>
</BODY>
</HTML>

3. 测试驱动：Oracle Provider for OLE DB

<%@LANGUAGE="VBSCript"%>
<% Option Explicit
Response.Buffer = True
%>

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft FrontPage 4.0">
<TITLE></TITLE>
</HEAD>
<BODY>
<% Dim objConn, objCmd, objRs
Set objConn = Server.CreateObject("ADODB.Connection")
Set objCmd = Server.CreateObject("ADODB.Command")
Set objRs = Server.CreateObject("ADODB.RecordSet")
Dim strConnection
'Use the correct SQL*NET connect string!
strConnection="PROVIDER=OraOLEDB.Oracle;DATA SOURCE=beq-local;USER ID=scott;PASSWORD=tiger;PLSQLRSet=1"
objConn.Open strConnection
objCmd.ActiveConnection = objConn
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "pkg_employees.getemprecords"
objCmd.Parameters.Append objCmd.CreateParameter("param1", adVarChar, adParamInput, 10, "30")
objCmd.Parameters.Append objCmd.CreateParameter("param3", adVarChar, adParamOutput, 10)
Set objRs = objCmd.Execute
%>
<p>
<B>ADO REF CURSOR DEMO Using the Oracle Provider for OLE DB
Beta</B>
<P>
<TABLE BORDER="1">
<TR>
<TH>Emp No.</TH>
<TH>Emp Name</TH>
</TR>
<% Do While Not objRs.EOF %>
<TR>
<TD><%=objRs.Fields("empno").Value%></TD>
<TD><%=objRs.Fields("ename").Value%></TD>
</TR>
<% objRs.MoveNext
Loop
objRs.Close
Set objRs = Nothing
Set objCmd = Nothing
objConn.Close
Set objConn = Nothing
%>
</TABLE>
</BODY>
</HTML>

账号		自动登录	找回密码
密码			立即注册

大疆运维招人啦，

C++ :try 语句块和异常处理

C++的多态

Red Hat RHCE 8 (EX294) Cert Guide

Java/C++ 区别：看完这一篇，就够用！

别再用过时库了！这 13 个顶级 C++ 库才是

c++ size_t 和 int 的区别

[经验分享] ASP 调用 Oracle 存储过程返回结果集

浏览过的版块

扫码加入运维网微信交流群