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

最新推荐文章于 2024-07-17 13:58:11 发布

lvlingwy

最新推荐文章于 2024-07-17 13:58:11 发布

阅读量1k

点赞数

分类专栏： asp技术文章标签： oracle asp 存储 microsoft vbscript generator

asp技术专栏收录该内容

50 篇文章 0 订阅

订阅专栏

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>

lvlingwy

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
ASP 调用 Oracle 存储过程返回结果集

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

扫一扫