Asp Oracle存储过程返回结果集的代码

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› ASP.NET›ASP.NET编程经验

原作者: [db:作者] 来自: [db:来源] 收藏邀请

经过摸索和实践，我把自己的解决方法，写在下面：
说明：
我的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