.NET中使用ORACLE函数和过程并输出参数（2）

我们知道.NET中调用ORACLE 的过程没有调用MS-SQLSERVER中的存储过程那么方便，尤其用到参数输出的时候，但是ORACLE的功能强大不会这个不能处理，这个你懂得，呵呵。

2个解决方案，一个是ORACLE中的函数可以带参数输出的，这个比较吻合SQLSERVER中的存储过程（个人比较意见用 ORACLE中的函数应对SQLSERVER中的存储过程。

二就是用PROCEDURE 的OUT参数带出结果来解决这个问题。

首先是SQL语句如下创建 PROCEDURE

create or replace procedure sp_ACC_CREATEPERMISSION(CategoryID in NUMBER, Description in VARCHAR2, OutValue out number) is n number; begin INSERT INTO Accounts_Permissions (PERMISSIONID, CategoryID, Description) VALUES (Seq_Accounts_Permissions.Nextval, CategoryID, Description); COMMIT; SELECT Seq_Accounts_Permissions.currval into n FROM DUAL; OutValue:=n; end;

测试PROCEDUCE输出

protected void button2_Click(object sender, EventArgs e) { OracleConnection conn = new OracleConnection("Data Source=yu; Persist Security Info=True;User ID=xxxxxxx;Password=xxxxxxx;Unicode=True;"); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "sp_Acc_CreatePermission"; cmd.CommandType = CommandType.StoredProcedure; // add the parameters, including the return parameter to retrieve // the return value cmd.Parameters.Add("CategoryID", OracleType.Number).Value = 555; cmd.Parameters.Add("Description", OracleType.VarChar, 50).Value = "aaa1"; cmd.Parameters.Add("OutValue", OracleType.Number).Direction = ParameterDirection.Output; // execute the function conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); // output the result Response.Write("OutValue: " + cmd.Parameters["OutValue"].Value); }

 

测试PROCEDURE三层

protected void Button4_Click(object sender, EventArgs e) { Accounts.Data.Permission myperm = new Accounts.Data.Permission(); int i = myperm.Create1(555, "aaa3"); Response.Write(i.ToString()); }

 

调用的方法CREATE1 public int Create1(int categoryID, string description) { int rowsAffected; OracleParameter[] parameters = { new OracleParameter("CategoryID", OracleType.Number), new OracleParameter("Description", OracleType.VarChar,50) }; parameters[0].Value = categoryID; parameters[1].Value = description; return DbHelperOra.RunProcedure2("sp_Acc_CreatePermission", parameters, out rowsAffected); }

调用执行存储过程

public static int RunProcedure2(string storedProcName, IDataParameter[] parameters, out int rowsAffected) { using (OracleConnection connection = new OracleConnection(connectionString)) { int result; connection.Open(); OracleCommand command = BuildIntCommand2(connection, storedProcName, parameters); rowsAffected = command.ExecuteNonQuery(); result = int.Parse(command.Parameters["OutValue"].Value.ToString()); //Connection.Close(); return result; } }

专门针对ORACLE 的 PROCEDURE 来执行的ORACLE COMMAND

private static OracleCommand BuildIntCommand2(OracleConnection connection, string storedProcName, IDataParameter[] parameters) { OracleCommand command = BuildQueryCommand(connection, storedProcName, parameters);                  command.Parameters.Add(" OutValue", OracleType.Number).Direction = ParameterDirection.Output; return command; }