（求助）在C#中,使用OracleCliet执行存储过程的代码到底应该怎么写？

hfsyly 2003-03-12 06:12:51

Oracle9 表结构：

CREATE TABLE "NILE3"."PRODUCTS"
(
"BOOKID" NUMBER NOT NULL,
"SUBJECTID" NUMBER NOT NULL,
"BOOKTITLE" VARCHAR2(50 byte),
"AUTHOR" VARCHAR2(50 byte),
"PRICE" NUMBER(22, 7),
"RETAIL" NUMBER(22, 7),
"ISBN" VARCHAR2(50 byte),
"QUANTITYONHAND" NUMBER,
"SPECIALITEM" CHAR(1 byte) NOT NULL,
CONSTRAINT "ISBN_PRODUCTS" UNIQUE("ISBN")
);

存储过程的代码：

create or replace procedure sp_products
(
v_bookid number,
v_booktitle varchar2,
v_price number

)
as

v_subjectid number;
v_author varchar2(50);
v_retail number;
v_isbn varchar2(50);

begin

v_subjectid := v_bookid + 1000;
v_author := 'Author1';
v_retail := v_price - 300;
v_isbn := 'ISBN-' || v_bookid;

insert into nile3.products
(
bookid,
subjectid,
booktitle,
author,
price,
retail,
isbn,
quantityonhand,
specialitem
)
values
(
v_bookid,
v_subjectid,
v_booktitle,
v_author,
v_price,
v_retail,
v_isbn,
100,
1
);

commit;

end sp_products;

程序代码：
try
{
OracleConnection cnn = new OracleConnection();
cnn.ConnectionString = "Persist Security Info=False;User ID=system;Password=manager;Data Source=ora9";
cnn.Open();

OracleCommand cmd = new OracleCommand();
OracleParameter[] para= new OracleParameter[3];
para[0] = new OracleParameter("v_bookid",OracleType.Number);
para[1] = new OracleParameter("v_booktitle",OracleType.VarChar,50);
para[2] = new OracleParameter("v_price",OracleType.Number);

//赋值
para[0].Value = txtBookID.Text;
para[1].Value = "'" + txtBookTitle.Text + "'";
para[2].Value = txtPrice.Text;

cmd.Parameters.Add(para[0]);
cmd.Parameters.Add(para[1]);
cmd.Parameters.Add(para[2]);

//cmd.CommandText = "exec sys.sp_products (10012,'C# Test',50.00)";
cmd.Connection = cnn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_products";
cmd.ExecuteNonQuery(); //this row is always wrong, why?
}
catch(Exception exc)
{
MessageBox.Show(exc.Message);
}

cmd.ExecuteNonQuery() is always wrong, why?

...全文