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

代码如下:

<%@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
%>

代码如下:

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