非常急的ORACLE存储过程调用

kaka1978 2004-05-12 07:45:09
我的存储过程编译没问题，可通过ASP调的时候并不显示传出参数，但也不报错。为什么啊？有哪位可以给一个调用oracle存储过程的例子，要带传入传出参数的那种，谢谢

str = "{call DBO.PROTEST(?,?,?,?{resultset 1000,themsg,thescts,theaddr,themomt})}"
'str = "DBO.PROTEST"
With cmd
.CommandText = str
.ActiveConnection = conn
.CommandType = 4 '1
.Parameters.Append cmd.CreateParameter("m_tablename",200,1,15,"KF_MT_20040301")
.Parameters.Append cmd.CreateParameter("m_ms",200,1,15,request.Form("ms"))
.Parameters.Append cmd.CreateParameter("m_addr",200,1,10,"168")
.Parameters.Append cmd.CreateParameter("m_momt",131,1,,"")
'.Parameters.Append cmd.CreateParameter("themsg",200,2,15)
'.Parameters.Append cmd.CreateParameter("thescts",200,2)
'.Parameters.Append cmd.CreateParameter("theaddr",200,2)
'.Parameters.Append cmd.CreateParameter("themomt",131,2)
.CommandTimeout = 5
End With

rs.CursorLocation = 3'adUseClient
set rs = cmd.execute
response.Write(cmd.Parameters("themsg").Value )
response.End()
...全文
196 4 打赏 收藏 转发到动态 举报
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
kaka1978 2004-05-14
  • 打赏
  • 举报
 回复
我怎么觉得上面写的不是针对oracle的存储过程的调用呢？我错大了？！
lawdoor 2004-05-12
  • 打赏
  • 举报
 回复
【几种存储过程调用】
1，调用没有参数的存储过程
<%
set conn=server.CreateObject("adodb.connection")
set cmd=server.CreateObject("adodb.command")
strconn="dsn=pubs;uid=sa;pwd"

conn.Open strconn
set cmd.ActiveConnection=conn

cmd.CommandText="{call nono}"

'set rs=cmc.exe 或者cmd.execute

set rs=cmd.Execute()

%>
2，一个输入的参数的存储过程
<%
set conn=server.CreateObject("adodb.connection")
set cmd=server.CreateObject("adodb.command")
strconn="dsn=pubs;uid=sa;pwd"

conn.Open strconn
set cmd.ActiveConnection=conn

cmd.CommandText="{call oneinput(?)}"
cmd.Parameters.Append cmd.CreateParameter("@aaa",adInteger ,adParamInput )
cmd("@aaa")=100

cmd.Execute()

%>
3，一个输入参数和一个输出的参数
<%
set conn=server.CreateObject("adodb.connection")
set cmd=server.CreateObject("adodb.command")
strconn="dsn=pubs;uid=sa;pwd"

conn.Open strconn
set cmd.ActiveConnection=conn

cmd.CommandText = "{call oneinout(?,?)}"
cmd.Parameters.Append cmd.CreateParameter("@aaa",adInteger,adParamInput)
cmd("@aaa")=10
cmd.Parameters.Append cmd.CreateParameter("@bbb",adInteger,adParamOutput)

cmd.Execute()

bbb=cmd("@bbb")
%>
4，一个输入参数，一个输出参数，和一个返回值
<%
set conn=server.CreateObject("adodb.connection")
set cmd=server.CreateObject("adodb.command")
strconn="dsn=pubs;uid=sa;pwd"

conn.Open strconn
set cmd.ActiveConnection=conn

cmd.CommandText="{?=call onereturn(?,?)}"

cmd.Parameters.Append cmd.CreateParameter("@return_value",adInteger,adParamReturnValue )
cmd.Parameters.Append cmd.CreateParameter("@aaa",adInteger,adParamInput )
cmd("@aaa")=10
cmd.Parameters.Append cmd.CreateParameter("@bbb",adInteger,adParamOutput)

cmd.Execute()

bbb=cmd("@bbb")
rrr=cmd("@return_value")
%>
lawdoor 2004-05-12
  • 打赏
  • 举报
 回复
<!--#include virtual="/testsite/global_include.asp" -->
<%
Dim conn 'As ADODB.Connection
Dim cmd 'As ADODB.Command
Dim prm 'As ADODB.Parameter
Dim rs 'As ADODB.Recordset
Dim ret 'As Long
Dim proc 'As String
Dim allData() 'As Variant
Dim colNames() 'As Variant
Dim i 'As Long
Dim datetime 'As DateTime

Const StoredProcedure = "[dbo].[DataTypeTester]"
Const titleString = "<html><head><title>ADO Parameter Test 3 / Multiple Recordset Tester</title><link rel=""stylesheet"" href=""/Templates/style.css"" type=""text/css"" /></head><body><div align=""left""><h3>A example of how to retrieve multiple recordsets from ADO and how to set parameters in ADO for SQL Server Stored Procedures</h3>"

ReDim allData(0) ' initialize array dimension

datetime = Now()

Response.Write titleString

Set conn = Server.CreateObject("ADODB.Connection")
Set cmd = Server.CreateObject("ADODB.Command")

conn.Open Application("connectionString")

With cmd
Set .ActiveConnection = conn
.CommandText = StoredProcedure

' always use ADO constants
.CommandType = adCmdStoredProc

' Check into the NamedParameters property at some point
' It doesn't require the order to be enforced, but it is always a good idea to enforce it anyway (for the documentation aspect of coding)

' RETURN parameter needs to be first
.Parameters.Append cmd.CreateParameter("RETURN", adInteger, adParamReturnValue, 4)

.Parameters.Append .CreateParameter("@myBigInt", adBigInt, adParamInput, 8, 996857543543543)
.Parameters.Append .CreateParameter("@myInt", adInteger, adParamInput, 4, 543543)
.Parameters.Append .CreateParameter("@mySmallint", adSmallInt, adParamInput, 2, 32765)
.Parameters.Append .CreateParameter("@myTinyint", adTinyInt, adParamInput, 1, 254)
.Parameters.Append .CreateParameter("@myBit", adBoolean, adParamInput, 4, True)

' Only Decimal and Numeric needs Precision and NumericScale
.Parameters.Append .CreateParameter("@myDecimal", adDecimal, adParamInput, 9, 765.5432321)
With .Parameters.Item("@myDecimal")
.Precision = 10
.NumericScale = 7
End With

Set prm = .CreateParameter("@myNumeric", adNumeric, adParamInput, 5, 432.6544)
prm.Precision = 7
prm.NumericScale = 4
.Parameters.Append prm

Set prm = Nothing

.Parameters.Append .CreateParameter("@myMoney", adCurrency, adParamInput, 8, 543.1234)
.Parameters.Append .CreateParameter("@mySmallMoney", adCurrency, adParamInput, 4, 543.1234)

.Parameters.Append .CreateParameter("@myFloat", adDouble, adParamInput, 8, 5.4E+54)
.Parameters.Append .CreateParameter("@myReal", adSingle, adParamInput, 4, 2.43E+24)

.Parameters.Append .CreateParameter("@myDatetime", adDBTimeStamp, adParamInput, 8, datetime)
.Parameters.Append .CreateParameter("@mySmallDatetime", adDBTimeStamp, adParamInput, 4, datetime)

.Parameters.Append .CreateParameter("@myChar", adChar, adParamInput, 4, "QWE")
.Parameters.Append .CreateParameter("@myVarchar", adVarchar, adParamInput, 10, "Variable!")

.Parameters.Append .CreateParameter("@myText", adLongVarChar, adParamInput, Len(titleString))
.Parameters.Item("@myText").AppendChunk titleString

.Parameters.Append .CreateParameter("@myNChar", adWChar, adParamInput, 4, "WIDE")
.Parameters.Append .CreateParameter("@myNVarchar", adVarWchar, adParamInput, 10, "")

.Parameters.Append .CreateParameter("@myNText", adLongVarWChar, adParamInput, Len(titleString))
.Parameters.Item("@myNText").AppendChunk titleString

' note the difference in these - without the {} the string implicitly converts
' the adVarChar version is of course commented out
'.Parameters.Append .CreateParameter("@myGuid", adVarChar, adParamInput, 36, "58F94A80-B839-4B35-B73C-7F4B4D336C3C")
.Parameters.Append .CreateParameter("@myGuid", adGUID, adParamInput, 16, "{58F94A80-B839-4B35-B73C-7F4B4D336C3C}")

Set rs = .Execute

'get column names
ReDim colNames(rs.Fields.Count - 1)
For i = 0 to rs.Fields.Count - 1
colNames(i) = rs.Fields.Item(i).Name
Next

Do While Not (rs Is Nothing)

' get initial recordset
If Not rs.EOF Then
' for retrieving more than about 30 or so recordsets you would probably want to use a collection
allData(UBound(allData)) = rs.GetRows(adGetRowsRest)
End If

' this will be nothing if no recordset is returned
Set rs = rs.NextRecordset

' resize array if needed
If Not (rs Is Nothing) Then ReDim Preserve allData(UBound(allData) + 1)
Loop

' must release the recordset before retrieving output parameters and/or the return value
ReleaseObj rs, True, True

ret = CStr(.Parameters.Item("RETURN").Value)
End With

ReleaseObj cmd, False, True
ReleaseObj conn, True, True

' show stored procedure
proc = GetStoredProcedureDefinition(StoredProcedure)

With Response
outputNamedGetRowsArray allData, colNames
.Write "<br />"
.Write "Return Value: " & ret & "<br /><br />"
.Write "<pre>" & proc & "</pre>"
End With

displayAspFile Server.MapPath("adodb.command3.asp")

Response.Write "</div></body></html>"
%>
lawdoor 2004-05-12
  • 打赏
  • 举报
 回复
使用command对象，如下：

Set Cmd=server.CreateObject("Adodb.Command")
Cmd.ActiveConnection=conn
Cmd.CommandText="ycuu_gb_getmsg"
Cmd.CommandType=4'adCmdStoredProc
cmd.prepared=true'
set param=Cmd.CreateParameter("@iPageNo",adInteger,1,2,Page)
Cmd.Parameters.Append param
set param=Cmd.CreateParameter("@iPageSize",adInteger,1,2,PageSizeConst)
Cmd.Parameters.Append param
set rs=Cmd.execute
21天学通Oracle
8.3.3 调用程序包中的函数/存储过程 157 8.3.4 程序包中的变量 158 8.4 本章实例 159 8.5 本章小结 161 8.6 习题 161 …… 第9章 游标（教学视频：36分钟） 162 第10章 触发器（教学视频：58分钟） 178 第11...
Oracle存储过程及调用
Oracle存储过程语法 Oracle的存储过程语法如下： ? 1 2 3 4 5 6 create procedure 存储过程名称（随便取） is 在这里可以定义常量、变量、游标、复杂数据类型这里...
赵强老师：Oracle数据库从10g到11g（5）过程、函数和触发器
学习并掌握使用PL/SQL程序开发Oracle数据库的存储过程、存储函数和触发器；并掌握如何在Java程序中调用存储过程、存储函数。
ORACLE存储过程调用WebService
最近在ESB项目中，客户在各个系统之间的服务调用大多都是在oracle存储过程中进行的，本文就oracle存储过程调用web service来进行说明。其他主流数据库，比如mysql和sql service，调用web service的方法这里就不做...
oracle存储过程游标调用,oracle存储过程调用游标例子-Oracle
oracle存储过程调用游标例子。1：首先你需要创建一个包，并定义你返回的游标的类型、存储过程create or replace package TEST_PKG is--定义游标type t_cur is REF CURSOR;--存储过程声明procedure test_proc (p_cur ...
ASP

28,391

社区成员

357,059

社区内容

发帖
与我相关
我的任务
社区描述
ASP即Active Server Pages，是Microsoft公司开发的服务器端脚本环境。
社区管理员
  • ASP
  • 无·法
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章