本文将示例使用C#的ADO.NET技术调用Oralce的存储过程和函数及操作Oracle数据库。

 1            string strConn = "Data Source=jayjay;User ID=hr;Password=oracle;Unicode=True"; 2  3             //调用存储过程 4             using (OracleConnection conn = new OracleConnection(strConn)) 5             { 6                 conn.Open(); 7                 using (OracleCommand cmd = new OracleCommand("INSERT_PROCEDURE", conn)) 8                 { 9                     cmd.CommandType = CommandType.StoredProcedure;10                     cmd.ExecuteNonQuery();11                 }12             }13             //显示调用存储过程后的数据14             using (OracleConnection conn = new OracleConnection(strConn))15             {16                 conn.Open();17                 using (OracleCommand cmd = new OracleCommand("select * from Jobs where JOB_ID=‘MY_JOB‘", conn))18                 {19                     cmd.CommandType = CommandType.Text;20                     OracleDataAdapter adapter = new OracleDataAdapter(cmd);21                     DataSet dataSet = new DataSet();22                     adapter.Fill(dataSet);//执行cmd.ExecuteReader();并将结果添加到DataSet容器中23                     DataRow row = dataSet.Tables[0].Rows[0];24                     Console.WriteLine("执行select * from Jobs where JOB_ID=MY_JOB之后得到的信息：");25                     for(int i =0 ;i<4;i++)26                     {27                         Console.WriteLine(row[i]);28                     }29                 }30             }31 32             //调用函数33             using (OracleConnection conn = new OracleConnection(strConn))34             {35                 conn.Open();36                 using (OracleCommand cmd = new OracleCommand("UPDATE_MIN_SALARY", conn))37                 {38                     cmd.CommandType = CommandType.StoredProcedure;39                     40                     // 将参数类型设置为 返回值类型41                     OracleParameter parameter = new OracleParameter();42                     parameter.Direction = ParameterDirection.ReturnValue;43                     parameter.OracleType = OracleType.Int32;44                     cmd.Parameters.Add(parameter);45 46                     cmd.ExecuteNonQuery();47                     Console.WriteLine("受影响的行数为：" + parameter.Value);48                 }49             }50 51             //显示调用函数后的数据52             using (OracleConnection conn = new OracleConnection(strConn))53             {54                 conn.Open();55                 using (OracleCommand cmd = new OracleCommand("select * from Jobs where MIN_SALARY=4000", conn))56                 {57                     cmd.CommandType = CommandType.Text;58                     OracleDataAdapter adapter = new OracleDataAdapter(cmd);59                     DataSet dataSet = new DataSet();60                     adapter.Fill(dataSet);61 62                     Console.WriteLine("执行select * from Jobs where MIN_SALARY=4000之后得到的行数：" + dataSet.Tables[0].Rows.Count);                  63                 }64             }65 66             Console.ReadKey();67         }