请问c# 调用 开发 oracle 存储过程的问题

[zuoliya]

在oracle 9i 里写了一个存储过程,希望通过 2个参数,查询一些数据.
可是首先是存储过程编译不能通过. c#程序也就不能执行.
请大家帮忙看看.存储过程哪里错了,我是新手,不过项目紧,请大家帮帮我,多谢,多谢.

存储过程:
set serveroutput on

CREATE OR REPLACE PROCEDURE FindProduct

(

item_Serial_No IN varchar2(30),
item_Keya IN varchar2(25)
)

AS

BEGIN
SELECT
ITEM.ITEM_N,
ITEM.ITEM_SERIAL_NO,
ITEM.ITEM_KEYA,
TO_CHAR(ITEM.DATE_INSTALLED, 'DD.MM.YY') DATE_INSTALLED,
ITEM.ITEM_STATUS_SC,
PRODUCT.PRODUCT_SC,
PRODUCT.PROD_CLS_N,
SECTN_DEPT.SECTN_SC
FROM SA.ITEM ITEM,
SA.PRODUCT PRODUCT,
SA.SUPPLIER SUPPLIER,
SA.SECTN_DEPT SECTN_DEPT
WHERE (ITEM.ITEM_ID>0) AND
(ITEM.STAT_FLAG='n') AND
(ITEM.PRODUCT_ID=PRODUCT.PRODUCT_ID) AND
(SUPPLIER.SUPPLIER_ID=PRODUCT.SUPPLIER_ID) AND
(ITEM.SECTN_DEPT_ID=SECTN_DEPT.SECTN_DEPT_ID) AND
(NOT(ITEM.ITEM_SC LIKE '%-UB')) AND
(PRODUCT.PROD_CLS_SC='FIREWALL') AND (ITEM.ITEM_SERIAL_NO=item_Serial_No) OR (ITEM.ITEM_KEYA=item_Keya);

return;

END;



c# 程序


[WebMethod(Description="this is web metod to publish Firewall Product infotmation")]
public Product[] FindProduct(string item_Serial_No, string item_Keya)
{
return this.SearchProduct(item_Serial_No,item_Keya);

}

private Product[] SearchProduct(string item_Serial_No, string item_Keya)
{
ArrayList results = new ArrayList();

using (OleDbConnection dataConnection = new OleDbConnection(ConnectionString))
//数据库连接字串
using (OleDbCommand dataCommand = new OleDbCommand("FindProduct", dataConnection)) //存储函数名


{


//访问数据库前给dataCommand添加参数 变量来自存储过程里面
dataCommand.CommandType = CommandType.StoredProcedure;
dataCommand.Parameters.Add("@item_Serial_No",OleDbType.VarChar,30).Value= item_Serial_No; //("@p1", OleDbType.Char, 3).Value = "a";

dataCommand.Parameters.Add("@item_Keya",OleDbType.VarChar,25).Value= item_Keya;

OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dataCommand);

DataTable data = new DataTable();

dataAdapter.Fill(data);

for (int i = 0; i < data.Rows.Count; i++)
{
results.Add(new Product(data.Rows[i]));

}

}

return (Product[])results.ToArray(typeof(Product));

}

[hongqi162]

OracleConnection conn = new OracleConnection(Connstr);
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.Parameters.Clear();
cmd.CommandText = "FindProduct";
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add( new OracleParameter( "item_Serial_No" ,OracleType.VarChar,30 ) ).Value = Litem_Serial_No;
cmd.Parameters.Add( new OracleParameter( "item_Keya IN",OracleType.VarChar,25 ) ).Value = item_Keya;
cmd.ExecuteNonQuery();

[hongqi162]

你也可以使用GotDotNet.ApplicationBlocks.Data 开发包
using System;
using System.Data;
using System.Data.OracleClient;
using GotDotNet.ApplicationBlocks.Data;

namespace PWeb.DataAccess.OptLog
{
/// <summary>
/// DAL_OptLog 的摘要说明。
/// </summary>
public class DAL_OptLog
{
private AdoHelper adoHelper;
private string Connstr=PWeb.Common.PWebConfiguration.ConnectionString;
public DAL_OptLog()
{
adoHelper = AdoHelper.CreateHelper("GotDotNet.ApplicationBlocks.Data","GotDotNet.ApplicationBlocks.Data.Oracle");
}

public void InsertOptLog( string aOptUserId ,string aOptaction, string aOptmemo )
{
OracleParameter[] oracleParameter = new OracleParameter[3];
oracleParameter[0] = new OracleParameter( "aOptUserId", OracleType.VarChar, 16 );
oracleParameter[0].Direction = ParameterDirection.Input;
oracleParameter[0].Value = aOptUserId ;
oracleParameter[1] = new OracleParameter( "aOptaction", OracleType.VarChar, 32 );
oracleParameter[1].Direction = ParameterDirection.Input;
oracleParameter[1].Value = aOptaction ;
oracleParameter[2] = new OracleParameter( "aOptmemo", OracleType.VarChar, 4000);
oracleParameter[2].Direction = ParameterDirection.Input;
oracleParameter[2].Value = aOptmemo ;

adoHelper.ExecuteNonQuery( Connstr,"PKG_PWEB_OptLog.InsertLog",oracleParameter );
return;
}
public DataSet QueryOptLog( string aQueryStr )
{
DataSet ds = new DataSet();
OracleParameter[] oracleParameter = new OracleParameter[2];
oracleParameter[0] = new OracleParameter( "aQueryStr",OracleType.VarChar,4000 );
oracleParameter[0].Direction = ParameterDirection.Input;
oracleParameter[0].Value = aQueryStr ;
oracleParameter[1] = new OracleParameter( "Rst",OracleType.Cursor );
oracleParameter[1].Direction = ParameterDirection.Output;
ds = adoHelper.ExecuteDataset( Connstr,"PKG_PWEB_OptLog.QueryLog",oracleParameter );
return ds;
}

public DataSet GetActionList()
{
DataSet ds = new DataSet();
OracleParameter[] oracleParameter = new OracleParameter[1];
oracleParameter[0] = new OracleParameter( "Rst",OracleType.Cursor );
oracleParameter[0].Direction = ParameterDirection.Output;
ds = adoHelper.ExecuteDataset( Connstr,"PKG_PWEB_OptLog.GetActionList",oracleParameter );
return ds;
}

public void DeleteLog( string aQueryStr )
{
OracleParameter[] oracleParameter = new OracleParameter[1];
oracleParameter[0] = new OracleParameter( "aDelQueryStr", OracleType.VarChar, 4000 );
oracleParameter[0].Direction = ParameterDirection.Input;
oracleParameter[0].Value = aQueryStr ;
adoHelper.ExecuteNonQuery( Connstr,"PKG_PWEB_OptLog.DeleteLog",oracleParameter );
return;
}
}
}

[hongqi162]

create table OPTLOG
(
OPTUSERID VARCHAR2(16),
OPTACTION VARCHAR2(32),
OPTDATETIME DATE,
OPTMEMO VARCHAR2(4000)
)
tablespace USERS
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);

create or replace package PKG_PWEB_OptLog
is
type ResultData is ref cursor;
procedure InsertLog( aOptUserId in OptLog.Optuserid%Type,
aOptAction in OptLog.Optaction%Type,
aOptMemo in OptLog.Optmemo%Type );
procedure DeleteLog( aDelQueryStr in varchar2 );
procedure QueryLog( aQueryStr in varchar2 ,Rst out ResultData);
procedure GetActionList ( Rst out ResultData );
end PKG_PWEB_OptLog;


create or replace package body PKG_PWEB_OptLog
is
procedure InsertLog( aOptUserId in OptLog.Optuserid%Type,
aOptAction in OptLog.Optaction%Type,
aOptMemo in OptLog.Optmemo%Type )
is
begin
insert into OptLog values ( aOptUserId, aOptAction, sysdate, aOptMemo);
end InsertLog;
procedure DeleteLog( aDelQueryStr in varchar2 )
is
begin
execute immediate 'delete from OptLog '||aDelQueryStr;
end DeleteLog;

procedure QueryLog( aQueryStr in varchar2 ,Rst out ResultData)
is
begin
open Rst for
'select * from OptLog' ||aQueryStr;
end;

procedure GetActionList ( Rst out ResultData )
is
begin
open Rst for
select distinct Optaction from OptLog;
end GetActionList;
end PKG_PWEB_OptLog;

[zuoliya]

谢谢楼上的回复,我今天把存储过程改写了,调试成功.使用了游标
CREATE OR REPLACE PACKAGE FindProduct_WS

AS

TYPE myrctype IS REF CURSOR;

PROCEDURE getproduct (
item_Serial_No IN nvarchar2(30),
item_Keya IN nvarchar2(25),
p_rc OUT myrctype

);

END FindProduct_WS;







CREATE OR REPLACE PACKAGE BODY FindProduct_WS

AS

PROCEDURE getproduct (
item_Serial_No IN nvarchar2(30),
item_Keya IN nvarchar2(25),
p_rc OUT myrctype

)

IS

BEGIN

OPEN p_rc FOR

SELECT
ITEM.ITEM_N,
ITEM.ITEM_SERIAL_NO,
ITEM.ITEM_KEYA,
TO_CHAR(ITEM.DATE_INSTALLED, 'DD.MM.YY') DATE_INSTALLED,
ITEM.ITEM_STATUS_SC,
PRODUCT.PRODUCT_SC,
PRODUCT.PROD_CLS_N,
SECTN_DEPT.SECTN_SC
FROM SA.ITEM ITEM,
SA.PRODUCT PRODUCT,
SA.SUPPLIER SUPPLIER,
SA.SECTN_DEPT SECTN_DEPT
WHERE (ITEM.ITEM_ID>0) AND
(ITEM.STAT_FLAG='n') AND
(ITEM.PRODUCT_ID=PRODUCT.PRODUCT_ID) AND
(SUPPLIER.SUPPLIER_ID=PRODUCT.SUPPLIER_ID) AND
(ITEM.SECTN_DEPT_ID=SECTN_DEPT.SECTN_DEPT_ID) AND
(NOT(ITEM.ITEM_SC LIKE '%-UB')) AND
(PRODUCT.PROD_CLS_SC='FIREWALL')AND (ITEM_SERIAL_NO=item_Serial_No)
OR (ITEM_KEYA=item_Keya) ;


END getproduct;

END FindProduct_WS;

请问楼上的高手,
c# 怎么调用返回记录的存储过程呢? 你给的第一程序,对与返回记录的过程,不适用的.

如果程序能调试成功,分一定少不了给您.多谢.

[icedut]

love_2008
你好啊

也不知道出什么问题了
回复根本看不到

好像是别人回复了，才能看到

[icedut]

c#调用带游标的存储过程
要用到一个游标类型的输出参数

一段用到的代码
public System.Data.DataTable buildOutInsByImmeReqs(Int32 storeNo,DateTime instructionDate, string userID, int typeIns)
{
string sql = "lgms_ins_other.build_out_ins_by_reqs";
DataTable dt = new DataTable();
OracleParameter[] parms = new OracleParameter[6];
parms[0] = new OracleParameter("p_store_no", OracleType.Int32);
parms[0].Value = storeNo;
parms[1] = new OracleParameter("p_date", OracleType.DateTime);
parms[1].Value = instructionDate;
parms[2] = new OracleParameter("p_isschedule", OracleType.Int32);
parms[2].Value =Convert.ToInt32(OutInstruction.IsSchedule.NO_SCHEDULE);
parms[3] = new OracleParameter("p_uid", OracleType.VarChar, 20);
parms[3].Value = userID;
parms[4] = new OracleParameter("p_type", OracleType.Int32);
parms[4].Value = typeIns;
parms[5] = new OracleParameter("p_cursor", OracleType.Cursor); //注意这个参数用于返回游标
parms[5].Direction = ParameterDirection.Output; //注意参数的方向
OracleHelper.FillDataTable(sql, CommandType.StoredProcedure, dt,
null, parms);

return dt;
}


//OracleHelper是我们自己写的一个访问数据库的类
你只要注意一下存储过程的参数据可以了

[icedut]

上面用到的oraclehelp.FillDataTable
与OracleDataAdapter.Fill(dataTable)类似

我看不到回复，也就只能说这么多了
实在不行可以发消息给我，改版了，也不知道是否能收到消息了

[wangzk0206]

晕 原来是icedut 还以为是哪个那？

呵呵