C#使用ORACLE中的存储过程

首先创建一张表：

	create table scott_stumsg  
	(  
	    stuname varchar(20),  
	    stunum char(12)primary key,  
	    stusex char(2)  
	)  

向表中插入信息的存储过程：

	create procedure stu_pro_ins  
	(  
	   name in varchar,  
	   num in char,  
	   sex in char  
	)  
	as  
	begin  
	   insert into scott.stumsg   
	   values(name,num,sex);  
	   commit;  
	end;

修改表信息的存储过程：

	create or replace procedure stu_pro_upd  
	(  
	   name in varchar,  
	   num in char,  
	   sex in char  
	)  
	as  
	begin  
	update stumsg  
	set  
	stuname=name,stunum=num,stusex=sex  
	where stunum=num;  
	commit;  
	end;  

删除表信息的存储过程：

	create or replace procedure stu_pro_del  
	(  
	   num in char  
	)  
	as  
	begin  
	delete from stumsg  
	where stunum=num;  
	commit;  
	end;  
	-- 显示全部的学生信息的存储过程；  
	create or replace procedure stu_pro_sel_all  
	(result out sys_refcursor)  
	as  
	begin  
	open result for select * from stumsg;  
	end;  

C# 调用存储过程时的代码

//绑定显示所有信息
private void button1_Click(object sender, EventArgs e)  
	       {  
	           try  
	           {  
	               OracleConnection conn = new OracleConnection("server=Chweiorc;uid=scott;pwd=Chwei926a");// 建立一个新的连接对象  
	               OracleCommand cmd = new OracleCommand("stu_pro_sel_all", conn);  
	               // 声明 cmd的类型为 存储类型；  
	               cmd.CommandType = CommandType.StoredProcedure;  
	  
	               OracleParameter p1 = new OracleParameter("result", OracleType.Cursor);  
	               p1.Direction = System.Data.ParameterDirection.Output;  
	               cmd.Parameters.Add(p1);  
	  
	               OracleDataAdapter da = new OracleDataAdapter(cmd);  
	               DataSet ds = new DataSet();  
	               da.Fill(ds);  
	               this.dgvShowAll.DataSource = ds.Tables[0];  
	           }  
	           catch (Exception ex)  
	           {  
	               MessageBox.Show(ex.Message);  
	           }  
	       }  

 // 像数据库中插入数据；  
	       private void button2_Click(object sender, EventArgs e)  
	       {  
	           string stuName = tb_name.Text.Trim();  
	           string stuNum = tb_num.Text.Trim();  
	           string stuSex = tb_sex.Text.Trim();  
	  
	           MessageBox.Show(stuSex.Length.ToString());  
	  
	           //连接字符串  
	           string connStr = "Data Source=Chweiorc;User ID=scott;Password=Chwei926a";  
	           OracleConnection connection = new OracleConnection(connStr);  
	           OracleCommand cmd = connection.CreateCommand();  
	           
	           cmd.CommandType = CommandType.StoredProcedure; // 指明用调用存储过程的方式来操作数据库；  
	           cmd.CommandText = "stu_pro_ins";  
	  
	           cmd.Parameters.AddWithValue("name", stuName);  
	           cmd.Parameters.AddWithValue("num",stuNum);  
	           cmd.Parameters.AddWithValue("sex",stuSex);  
	  
	           connection.Open();  
	           try  
	           {  
	               MessageBox.Show(cmd.ExecuteNonQuery().ToString());  
	           }  
	           catch (Exception ex)  
	           {  
	               MessageBox.Show(ex.Message);  
	           }  
	           finally  
	           {  
	               connection.Close();  
	               cmd.Dispose();  
	               MessageBox.Show("更新数据完成~~~~");  
	           }  
	       }