在oracle 9i 里写了一个存储过程,希望通过 2个参数,查询一些数据.
可是首先是存储过程编译不能通过. c#程序也就不能执行.
请大家帮忙看看.存储过程哪里错了,我是新手,不过项目紧,请大家帮帮我,多谢,多谢.存储过程:
set serveroutput onCREATE OR REPLACE PROCEDURE FindProduct(item_Serial_No IN varchar2(30),
item_Keya IN varchar2(25)
)ASBEGIN
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));}
可是首先是存储过程编译不能通过. c#程序也就不能执行.
请大家帮忙看看.存储过程哪里错了,我是新手,不过项目紧,请大家帮帮我,多谢,多谢.存储过程:
set serveroutput onCREATE OR REPLACE PROCEDURE FindProduct(item_Serial_No IN varchar2(30),
item_Keya IN varchar2(25)
)ASBEGIN
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));}
解决方案 »
- 有人知道哪家培训orcale10g面向企业级培训??
- 如何取得存储过程的参数信息
- 跪求各位热心大哥帮忙看下什么错
- 如何记录分割?
- 为何数据库安装完成之后重新启动服务器系统只在MOUNT下不会自动OPEN?
- 在function中如何实现对表的检索(表名是循环动态生成),原代码见内
- 在sql plus 中,运行dbms_output.put时提示SP2-0734: unknown command beginning 请问是何缘故?
- 小问题:一个已经建了索引的表,再imp入数据,还需要重建索引吗?
- 返回多条记录时出错!
- 联系oracle olap的前端工具是哪些
- 求一procedure的写法
- 请教?怎样在oracle中插入一个单引号
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();
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;
}
}
}
(
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;
CREATE OR REPLACE PACKAGE FindProduct_WSASTYPE 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_WSASPROCEDURE getproduct (
item_Serial_No IN nvarchar2(30),
item_Keya IN nvarchar2(25),
p_rc OUT myrctype )ISBEGINOPEN p_rc FORSELECT
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# 怎么调用返回记录的存储过程呢? 你给的第一程序,对与返回记录的过程,不适用的.如果程序能调试成功,分一定少不了给您.多谢.
要用到一个游标类型的输出参数一段用到的代码
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是我们自己写的一个访问数据库的类
你只要注意一下存储过程的参数据可以了
与OracleDataAdapter.Fill(dataTable)类似我看不到回复,也就只能说这么多了
实在不行可以发消息给我,改版了,也不知道是否能收到消息了
你好啊也不知道出什么问题了
回复根本看不到好像是别人回复了,才能看到