紧急求助!vb.net 取不到oracle存储过程返回值

zhangfengyi 2013-02-19 10:36:37
b/s架构vb.net程序调用oracle存储过程,同一个程序其他模块调用没问题,这一个模块调用也没问题,但取不到返回值,不知道什么原因,求高手解答,着急!
vb.net代码

Public Structure ST_trans_Search
<DataContract()> _
Structure ST_trans_Search_info
<DataMember()> _
Dim mcht_id As String
<DataMember()> _
Dim Settle_date As String
<DataMember()> _
Dim orderno As String
<DataMember()> _
Dim cardno As String
<DataMember()> _
Dim amt As Double
<DataMember()> _
Dim TranDate As String
<DataMember()> _
Dim TranTime As String
<DataMember()> _
Dim trace As String
<DataMember()> _
Dim page_count As String

End Structure
Dim page_count As Integer
Dim trans_info As List(Of ST_trans_Search_info)
End Structure

<OperationContract()> _
Public Function Trans_Search(ByVal Merch_id As String, ByVal Settle_date As String, ByVal orderno As String, ByVal cardno As String, ByVal amt As Double, ByVal TranDate As String, ByVal TranTime As String, ByVal trace As String, ByVal PageSize As Integer, ByVal CurrPage As Integer) As ST_trans_Search
On Error GoTo go_Err

Dim s_trans As ST_trans_Search
Dim s_trans_info() As ST_trans_Search.ST_trans_Search_info = Nothing
'=================带参数存储过程
Dim myconnection As OracleConnection
Dim strsql = "User ID=card;Password=card;Data Source=orcltest" 'OK测试大机Oracle test_Esa_Web_Opr_Orcl
myconnection = New OracleConnection(strsql)
myconnection.Open()

Dim oraclecomm As OracleCommand = Nothing
oraclecomm = New OracleCommand("trans_search", myconnection)
oraclecomm.CommandType = CommandType.StoredProcedure
oraclecomm.Parameters.Add("Merch_id", OracleType.VarChar, 50)
oraclecomm.Parameters("Merch_id").Direction = ParameterDirection.Input
oraclecomm.Parameters("Merch_id").Value = Merch_id

oraclecomm.Parameters.Add("Settle_date", OracleType.VarChar, 50)
oraclecomm.Parameters("Settle_date").Direction = ParameterDirection.Input
oraclecomm.Parameters("Settle_date").Value = Settle_date

oraclecomm.Parameters.Add("orderno", OracleType.VarChar, 50)
oraclecomm.Parameters("orderno").Direction = ParameterDirection.Input
oraclecomm.Parameters("orderno").Value = orderno

oraclecomm.Parameters.Add("cardno", OracleType.VarChar, 50)
oraclecomm.Parameters("cardno").Direction = ParameterDirection.Input
oraclecomm.Parameters("cardno").Value = cardno

oraclecomm.Parameters.Add("amt", OracleType.Number)
oraclecomm.Parameters("amt").Direction = ParameterDirection.Input
oraclecomm.Parameters("amt").Value = amt

oraclecomm.Parameters.Add("TranDate", OracleType.VarChar, 50)
oraclecomm.Parameters("TranDate").Direction = ParameterDirection.Input
oraclecomm.Parameters("TranDate").Value = TranDate

oraclecomm.Parameters.Add("TranTime", OracleType.VarChar, 50)
oraclecomm.Parameters("TranTime").Direction = ParameterDirection.Input
oraclecomm.Parameters("TranTime").Value = TranTime

oraclecomm.Parameters.Add("trace", OracleType.VarChar, 50)
oraclecomm.Parameters("trace").Direction = ParameterDirection.Input
oraclecomm.Parameters("trace").Value = trace

'oraclecomm.Parameters.Add("respcode", OracleType.VarChar, 50)
'oraclecomm.Parameters("respcode").Direction = ParameterDirection.Input
'oraclecomm.Parameters("respcode").Value = respcode

oraclecomm.Parameters.Add("pagesize", OracleType.Number)
oraclecomm.Parameters("pagesize").Direction = ParameterDirection.Input
oraclecomm.Parameters("pagesize").Value = PageSize

oraclecomm.Parameters.Add("currpage", OracleType.Number)
oraclecomm.Parameters("currpage").Direction = ParameterDirection.Input
oraclecomm.Parameters("currpage").Value = CurrPage

oraclecomm.Parameters.Add("recordcount", OracleType.Number)
oraclecomm.Parameters("recordcount").Direction = ParameterDirection.InputOutput

oraclecomm.Parameters.Add("pp_cursor", OracleType.Cursor)
oraclecomm.Parameters("pp_cursor").Direction = ParameterDirection.Output

'Dim aa12 As System.IO.StreamWriter = New System.IO.StreamWriter("D:\程序\ESA_OPR_NEW\ESA_WEB_MER\File_MerReport\aaa\" & Merch_id.ToString & "__" & Year(Now()) & Month(Now()) & Day(Now()) & Hour(Now()) & Minute(Now()) & Second(Now()) & "__" & "__ .txt", False, System.Text.Encoding.Unicode)
'aa12.WriteLine(oraclecomm.Parameters("recordcount").Value.ToString() + "aaa") ' ds.Rows(0).Item("name") MyReader("name") 输出参数oraclecomm.Parameters("recordcount").Value.ToString()
'aa12.Close()
'aa12.Dispose()

Dim da1 As New OracleDataAdapter()
da1.SelectCommand = oraclecomm
Dim ds As New DataTable()
da1.Fill(ds)
myconnection.Close()

For j = 0 To ds.Rows.Count - 1
If s_trans_info Is Nothing Then
ReDim s_trans_info(0)
Else
ReDim Preserve s_trans_info(UBound(s_trans_info) + 1)
End If
s_trans_info(UBound(s_trans_info)).mcht_id = Trim(ds.Rows(j).Item("merch_id"))
s_trans_info(UBound(s_trans_info)).Settle_date = Trim(ds.Rows(j).Item("Settle_date"))

If Trim(ds.Rows(j).Item("orderno")) = "0" Then
s_trans_info(UBound(s_trans_info)).orderno = ""
Else
s_trans_info(UBound(s_trans_info)).orderno = Trim(ds.Rows(j).Item("orderno"))
End If

If Trim(ds.Rows(j).Item("cardno")) = "0" Then
s_trans_info(UBound(s_trans_info)).cardno = ""
Else
s_trans_info(UBound(s_trans_info)).cardno = Trim(ds.Rows(j).Item("cardno"))
End If

s_trans_info(UBound(s_trans_info)).TranDate = FormatDateTime(ds.Rows(j).Item("trandate"), DateFormat.ShortDate)

If Trim(ds.Rows(j).Item("amt")) = "0" Then
s_trans_info(UBound(s_trans_info)).amt = ""
Else
s_trans_info(UBound(s_trans_info)).amt = Trim(ds.Rows(j).Item("amt"))
End If

If Trim(ds.Rows(j).Item("TranDate")) = "0" Then
s_trans_info(UBound(s_trans_info)).TranDate = ""
Else
s_trans_info(UBound(s_trans_info)).TranDate = Trim(ds.Rows(j).Item("TranDate"))
End If

If Trim(ds.Rows(j).Item("TranTime")) = "0" Then
s_trans_info(UBound(s_trans_info)).TranTime = ""
Else
s_trans_info(UBound(s_trans_info)).TranTime = Trim(ds.Rows(j).Item("TranTime"))
End If

Next
s_trans_info(0).page_count = oraclecomm.Parameters("recordcount").Value.ToString() '记录总数

go_exit:
s_trans.trans_info = s_trans_info.ToList
Return s_trans
Exit Function
go_Err:
s_trans_info = Nothing
GoTo go_exit
End Function


...全文
52 点赞 收藏 2
写回复
2 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
zhangfengyi 2013-02-20
问题解决了,amt类型设置的有问题,其他都正常
回复
zhangfengyi 2013-02-19
存储过程代码:

--zfy 商户查询 2013-2-7
create or replace package pak_trans_search as
type trans_cursor_r is ref cursor;
end pak_trans_search;
/
CREATE OR REPLACE PROCEDURE "CARD"."TRANS_SEARCH"
(
Merch_id in varchar,            --商户号
Settle_date in varchar,         --清算日期
orderno in varchar,             --订单号
cardno in varchar,              --卡号/帐号
amt in number,                  --金额
TranDate in varchar,            --交易日期
TranTime in varchar,            --开始时间
trace in varchar,               --流水号
pagesize in number,             --页尺寸
currpage in number,             --当前页
recordcount out number,         --记录数
pp_cursor out pak_trans_search.trans_cursor_r   --返回结果集
)
as
v_startRowNum NUMBER;         --分页起始记录编号
v_endRowNum NUMBER;           --分页结束记录编号
strsql varchar2(4000);        --查询语句
sqlwhere varchar2(4000);

begin

--update proce set name=Settle_date;

  --查询条件
  sqlwhere:=' where merch_id='''
           ||Merch_id
           ||'''';

  if length(Settle_date)>0 then
    sqlwhere:=sqlwhere
            ||' and Settle_date='''
            ||Settle_date
            ||'''';
  end if;

  if to_number(amt)!=0 then
    sqlwhere:=sqlwhere
            ||' and amt='
            ||to_number(amt)*0.01;
  end if;

  if length(orderno)>0 then
    sqlwhere:=sqlwhere
            ||' and orderno='''
            ||orderno
            ||'''';
  end if;

  if length(cardno)>0 then
    sqlwhere:=sqlwhere
            ||' and cardno='''
            ||cardno
            ||'''';
  end if;

  if length(TranDate)>0 then
    sqlwhere:=sqlwhere
            ||' and TranDate='''
            ||TranDate
            ||'''';
  end if;

  if length(TranTime)>0 then
    sqlwhere:=sqlwhere
            ||' and TranTime='''
            ||TranTime
            ||'''';
  end if;

  if length(trace)>0 then
    sqlwhere:=sqlwhere
            ||' and trace='''
            ||trace
            ||''''; 
  end if;

  --if length(respcode)>0 then
  --  sqlwhere:=sqlwhere
  --          ||' and respcode='''
  --          ||respcode
  --          ||'''';
  --end if;
--update proce set name=sqlwhere;

  --统计记录总数
  strsql:='select count(*) from tran_inq_rec'
          ||sqlwhere; 
  execute immediate strsql into recordcount;
  --recordcount:=10;
update proce set name=recordcount;

  --确定起始页与结束页
  --v_startRowNum:=(currpage-1)*pagesize+1;  
  --if currpage*pagesize>recordcount then
  --  v_endRowNum:=recordcount;
  --else
  --  v_endRowNum:=currpage*pagesize;
  --end if;

  --update proce set name=recordcount;
 
  --查询结果
  strsql:='select nvl(merch_id,0) merch_id,nvl(settle_date,0) settle_date,nvl(orderno,0) orderno,nvl(cardno,0) cardno,nvl(amt,0) amt,nvl(TranDate,0) TranDate,nvl(TranTime,0) TranTime,nvl(trace,0) trace from tran_inq_rec'
         ||sqlwhere;
         --||' and rn between '
         --||(currpage-1)*pagesize+1
         --||' and '
         --||(currpage*pagesize);
         --||' and  rn<='
         --||v_endRowNum
         --||' and rn>='
         --||v_startRowNum;
  OPEN pp_cursor FOR strsql;
  --CLOSE p_cursor;
  --update proce set name=settle_date||' '||merch_id;
  update proce set name=strsql;
end trans_search;
/
取返回值的语句:

        oraclecomm.Parameters.Add("recordcount", OracleType.Number)
        oraclecomm.Parameters("recordcount").Direction = ParameterDirection.InputOutput

        oraclecomm.Parameters.Add("pp_cursor", OracleType.Cursor)
        oraclecomm.Parameters("pp_cursor").Direction = ParameterDirection.Output
回复
相关推荐
发帖
VB
创建于2007-09-28

1.6w+

社区成员

VB技术相关讨论,主要为经典vb,即VB6.0
申请成为版主
帖子事件
创建了帖子
2013-02-19 10:36
社区公告
暂无公告