Asp Oracle存储过程返回结果集的代码
网络编程 2021-07-05 10:58www.168986.cn编程入门
对于 Oracle 这个庞然大物,Asp使用起来,确实是捉襟见肘的 。
尤其是要返回结果集(Recordset)的情况,更是让很多人犯难。
经过摸索和实践,我把自己的解决方法,写在狼蚁网站SEO优化:
说明:
我的Oracle客户端的版本是 oracle 9i, 安装client端的时候,不能用默认安装,一定要自定义, 然后选择所有 OLEDB 相关的内容,都装上,否则到狼蚁网站SEO优化的 Provider 的时候,会找不到。
<%@Language="VBSCRIPT" CodePage="936" LCID="2052"%>
<%Option Explicit%>
<!-- #include file="../adovbs.inc" -->
<%
Dim Ora
Function Connect2OracleServer
Dim conStr
conStr = "Provider=MSDAORA.Oracle;Data Source=xx;User Id=?;Password=?"
Set Ora = Server.CreateObject("ADODB.Connection")
Ora.CursorLocation = adUseClient '=3
On Error Resume Next
Ora.Open conStr
Connect2OracleServer = (Err.Number = 0)
End Function
Sub DisconnectFromOracleServer
If Not Ora is Nothing Then
If Ora.State = 1 Then
Ora.Close
End If
Set Ora = 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 = Ora
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
%>
狼蚁网站SEO优化是 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
说明:
我的Oracle客户端的版本是 oracle 9i, 安装client端的时候,不能用默认安装,一定要自定义, 然后选择所有 OLEDB 相关的内容,都装上,否则到狼蚁网站SEO优化的 Provider 的时候,会找不到。
代码如下:
<%@Language="VBSCRIPT" CodePage="936" LCID="2052"%>
<%Option Explicit%>
<!-- #include file="../adovbs.inc" -->
<%
Dim Ora
Function Connect2OracleServer
Dim conStr
conStr = "Provider=MSDAORA.Oracle;Data Source=xx;User Id=?;Password=?"
Set Ora = Server.CreateObject("ADODB.Connection")
Ora.CursorLocation = adUseClient '=3
On Error Resume Next
Ora.Open conStr
Connect2OracleServer = (Err.Number = 0)
End Function
Sub DisconnectFromOracleServer
If Not Ora is Nothing Then
If Ora.State = 1 Then
Ora.Close
End If
Set Ora = 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 = Ora
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
%>
狼蚁网站SEO优化是 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
上一篇:XMLHttp ASP远程获取网页内容代码
下一篇:ASP ajax分页教程一
编程语言
- 机械手焊接机器人编程 机械手焊接设备编程
- vb数据库编程实例,vb做数据库
- tiobe世界编程语言排行榜,全球编程语言排行
- 成都java编程培训 成都it编程培训中心
- 编程基础培训,编程基础知识培训
- flash游戏编程基础教程,flash游戏开发教程
- 不会英语能学编程吗 不会英语可以学程序员吗
- 爱心代码编程C语言 爱心代码编程c语言公式
- c语言编程学习入门 c语言编程快速入门
- 西门子plc编程培训 西门子plc编程培训班
- 哪里可以学编程 台州哪里可以学编程
- 少儿编程哪个好 少儿编程哪个品牌好
- 编程是什么意思,图形编程是什么意思
- c语言编程软件下载,c语言编程软件在哪下载
- java编程软件下载,java编程下载手机版
- ug编程培训学校,ug编程培训教程