Asp Oracle存储步骤返回 结果集的代码

经过摸索和实践，我把自己的解决方法，写在下面：

说明：

我的Oracle客户端的版本是 oracle 9i， 安装client端的时候，不能用默认安装，一定要自定义， 然后选择所有 OLEDB 相关的内容，都装上，否则到下面的 Provider 的时候，会找不到。

代码如下:

<%@Language="VBSCRIPT" CodePage="936" LCID="2052"%>

<%Option Explicit%>

<!-- #include file="../adovbs.inc" -->

<%

Dim cnOra

Function Connect2OracleServer

Dim conStr

conStr = "Provider=MSDAORA.Oracle;Data Source=xx;User Id=?;Password=?"

Set cnOra = Server.CreateObject("ADODB.Connection")

cnOra.CursorLocation = adUseClient '=3

On Error Resume Next

cnOra.Open conStr

Connect2OracleServer = (Err.Number = 0)

End Function

Sub DisconnectFromOracleServer

If Not cnOra is Nothing Then

If cnOra.State = 1 Then

cnOra.Close

End If

Set cnOra = Nothing

End If

End Sub

Sub Echo(str)

Response.Write(str)

End Sub

Sub OutputResult

Dim cmdOra

Dim rs

Set cmdOra = Server.CreateObject("ADODB.Command")

With cmdOra

.CommandType = adCmdText '=1

.CommandText = "{call PKG_TEST.GetItem(?,?)}"

.Parameters.Append cmdOra.CreateParameter("p1", adNumeric, adParamInput, 10, 1)

.Parameters.Append cmdOra.CreateParameter("p2", adVarChar, adParamInput, 10, "xx")

.ActiveConnection = cnOra

Set rs = cmdOra.Execute

If Not rs.Eof Then

While Not rs.Eof

Echo rs(0)

Echo "--"

Echo rs(1)

Echo "<br>"

rs.MoveNext

Wend

rs.Close

End If

Set rs = Nothing

Set cmdOra = Nothing

End With

DisconnectFromOracleServer

End Sub

If Connect2OracleServer Then

OutputResult

Else

Response.Write(Err.Description)

End If

%>

下面是 Oracle 的 sql 脚本

--------------------------------------SQL Script----------------------------------

--建包-----------------------------------

代码如下:

Create Or Replace Package PKG_TEST

IS

TYPE rfcTest IS REF CURSOR ;

PROCEDURE GETITEM

( p1 IN NUMBER,

p2 IN VARCHAR2,

p3 OUT rfcTest

);

END; -- Package Specification PKG_TEST

---------------------------------------------------

--建包体-----------------------------------

Create Or Replace Package Body PKG_TEST

IS

PROCEDURE GETITEM

( p1 IN NUMBER,

p2 IN VARCHAR2,

p3 OUT rfcTest

)

IS

BEGIN

OPEN p3 FOR

SELECT * FROM tablename WHERE id = p1 AND name=p2 AND rownum < 10 ;

EXCEPTION

WHEN OTHERS THEN

NULL ;

END;

END; -- Package Body PKG_TEST

（编辑：银川站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!