【摘录】Calling Oracle stored procedures from Microsoft.NET-优快云博客

本文介绍如何使用Microsoft .NET Oracle提供程序从.NET调用Oracle存储过程及函数，并演示了通过不同示例执行存储过程和函数的方法。

Introduction

This article is intended to illustrate how to illustrate how to call Oracle stored procedures and functions from Microsoft.NET through the Microsoft.NET Oracle provider and its object model residing in the namespace System.Data.OracleClient. I will cover several possible scenarios with advanced examples.

Executing a stored procedure

Let's begin with definitions. A procedure is a module that performs one or more actions. A function is a module that returns a value and unlike procedures a call to a function can exist only as part of an executable such as an element in an expression or the value assigned as default in a declaration of a variable.

The first example illustrates how to call an Oracle procedure passing input parameters and retrieving value by output parameters. For all the examples, we're going to use the default database ORCL which comes with the Oracle database installation. The following code in Listing 1 shows how to create a procedure named count_emp_by_dept which receives as its input parameter the department number and sends as its output parameter the number of employees in this department.

create or replace procedure count_emp_by_dept(pin_deptno number, pout_count out number)
is
begin
select count(*) into pout_count
from scott.emp
where deptno=pin_deptno;
end count_emp_by_dept;

Listing 1: Creating the procedure count_emp_by_dept.

Now let's create a console application and add a reference to the assembly System.Data.OracleClient.dll to your project.

The code for this example is illustrated in Listing 2. The first thing to do is to import the object's class residing in the namespace System.Data.OracleClient with the using directive. Then you must set up the parameters and finally call the procedure using ExecuteNonQuery method of the OracleCommand object.

1 Using System;
2
3 using System.Collections.Generic;
4
5 using System.Text;
6
7 using System.Data.OracleClient;
8
9 using System.Data;
10
11
12
13 namespace CallingOracleStoredProc
14
15 {
16     class Program
17
18     {
19
20         static void Main(string[] args)
21
22         {
23
24             using (OracleConnection objConn = new OracleConnection("Data Source=ORCL; User ID=scott; Password=tiger"))
25
26             {
27
28                 OracleCommand objCmd = new OracleCommand();
29
30                 objCmd.Connection = objConn;
31
32                 objCmd.CommandText = "count_emp_by_dept";
33
34                 objCmd.CommandType = CommandType.StoredProcedure;
35
36                 objCmd.Parameters.Add("pin_deptno", OracleType.Number).Value = 20;
37
38                 objCmd.Parameters.Add("pout_count", OracleType.Number).Direction = ParameterDirection.Output;
39
40                 try
41
42                 {
43
44                     objConn.Open();
45
46                     objCmd.ExecuteNonQuery();
47
48                     System.Console.WriteLine("Number of employees in department 20 is {0}", objCmd.Parameters["pout_count"].Value);
49
50                 }
51
52                 catch (Exception ex)
53
54                 {
55
56                     System.Console.WriteLine("Exception: {0}",ex.ToString());
57
58                 }
59
60                 objConn.Close();
61
62             }
63
64         }
65
66     }
67
68 }

Listing 2: The application code calling the stored procedure.

Executing a function

As function is similar to procedures except they return a value, we need to set up a return parameter. Let's see the example.

The following code in Listing 3 shows how to create a function named get_count_emp_by_dept which receives as its input parameter the department number and returns the number of employees in this department. It's very similar to the former procedure in the previous section.

create or replace function get_count_emp_by_dept(pin_deptno number)
return number
is
var_count number;
begin
select count(*) into var_count
from scott.emp
where deptno=pin_deptno;
return var_count;
end get_count_emp_by_dept;

Listing 3: Creating an Oracle function.

Now let's see in the Listing 4 the application code which calls the function. As you can see, we need to define a return parameter to get the returned value. The other part of the code is similar for calling a procedure.

Listing 4: The application code calling the function.

Working with cursors

You can use the REF CURSOR data type to work with Oracle result set. To retrieve the result set, you must define a REF CURSOR output parameter in a procedure or a function to pass the cursor back to your application.

Now we're going to define a procedure which opens and sends a cursor variable to our application.

Let's define the package and procedure header as shown in Listing 5.

create or replace package human_resources
as
type t_cursor is ref cursor;
procedure get_employee(cur_employees out t_cursor);
end human_resources;

Listing 5: Creation of the package human_resources and the procedure get_employee.

And now the package definition as shown in Listing 6.

create or replace package body human_resources
as
procedure get_employee(cur_employees out t_cursor)
is
begin
open cur_employees for select * from emp;
end get_employee;
end human_resources;

Listing 6. The creation of the package body.

Now let's see in Listing 7 the application code calling the procedure inside the package. See the name syntax for calling the procedure contained within a package [package_name].[procedure_name]. In order to get a cursor, you need to define a cursor parameter with the ParameterDirection set up to Output and finally call the ExecuteReader method in the OracleCommand instance.

  1 Using System;
  2
  3 using System.Collections.Generic;
  4
  5 using System.Text;
  6
  7 using System.Data.OracleClient;
  8
  9 using System.Data;
10
11
12
13 namespace CallingOracleStoredProc
14
15 {
16
17     class Program
18
19     {
20
21         private static void prvPrintReader(OracleDataReader objReader)
22
23         {
24
25             for (int i = 0; i < objReader.FieldCount; i++)
26
27             {
28
29                 System.Console.Write("{0}\t",objReader.GetName(i));
30
31             }
32
33             System.Console.Write("\n");
34
35
36
37             while (objReader.Read())
38
39             {
40
41                 for (int i = 0; i < objReader.FieldCount; i++)
42
43                 {
44
45                     System.Console.Write("{0}\t", objReader[i].ToString());
46
47                 }
48
49                 System.Console.Write("\n");
50
51             }
52
53         }
54
55
56
57         static void Main(string[] args)
58
59         {
60
61             using (OracleConnection objConn = new OracleConnection("Data Source=ORCL; User ID=scott; Password=tiger"))
62
63             {
64
65                 OracleCommand objCmd = new OracleCommand();
66
67                 objCmd.Connection = objConn;
68
69                 objCmd.CommandText = "human_resources.get_employee";
70
71                 objCmd.CommandType = CommandType.StoredProcedure;
72
73                 objCmd.Parameters.Add("cur_employees", OracleType.Cursor).Direction = ParameterDirection.Output;
74
75                 try
76
77                 {
78
79                     objConn.Open();
80
81                     OracleDataReader objReader = objCmd.ExecuteReader();
82
83                     prvPrintReader(objReader);
84
85                 }
86
87                 catch (Exception ex)
88
89                 {
90
91                     System.Console.WriteLine("Exception: {0}",ex.ToString());
92
93                 }
94                 objConn.Close();
95
96             }
97
98         }
99
100
101
102     }
103
104 }

Listing 7: The application code.

If the procedure returns more than one cursor, the DataReader object accesses them by calling the NextResult method to advance the next cursor.

Let's see the following example.

Listing 8 shows how to create the package header.

create or replace package human_resources
as
type t_cursor is ref cursor;
procedure get_employee_department(cur_employees out t_cursor, cur_departments out t_cursor);
end human_resources;

Listing 8: Package reader.

The package body is shown in Listing 9.

create or replace package body human_resources
as
procedure get_employee_department(cur_employees out t_cursor, cur_departments out t_cursor)
is
begin
open cur_employees for select * from emp;
open cur_departments for select * from dept;
end get_employee_department;
end human_resources;

Listing 9: Creation of the package body.

Let's see the application code in Listing 10.

  1 using System;
  2
  3 using System.Collections.Generic;
  4
  5 using System.Text;
  6
  7 using System.Data.OracleClient;
  8
  9 using System.Data;
10
11
12
13 namespace CallingOracleStoredProc
14
15 {
16
17     class Program
18
19     {
20
21         private static void prvPrintReader(OracleDataReader objReader)
22
23         {
24
25             for (int i = 0; i < objReader.FieldCount; i++)
26
27             {
28
29                 System.Console.Write("{0}\t",objReader.GetName(i));
30
31             }
32
33             System.Console.Write("\n");
34
35
36
37             while (objReader.Read())
38
39             {
40
41                 for (int i = 0; i < objReader.FieldCount; i++)
42
43                 {
44
45                     System.Console.Write("{0}\t", objReader[i].ToString());
46
47                 }
48
49                 System.Console.Write("\n");
50
51             }
52
53         }
54
55
56
57         static void Main(string[] args)
58
59         {
60
61             using (OracleConnection objConn = new OracleConnection("Data Source=ORCL; User ID=scott; Password=tiger"))
62
63             {
64
65                 OracleCommand objCmd = new OracleCommand();
66
67                 objCmd.Connection = objConn;
68
69                 objCmd.CommandText = "human_resources.get_employee_department";
70
71                 objCmd.CommandType = CommandType.StoredProcedure;
72
73                 objCmd.Parameters.Add("cur_employees", OracleType.Cursor).Direction = ParameterDirection.Output;
74
75                 objCmd.Parameters.Add("cur_departments", OracleType.Cursor).Direction = ParameterDirection.Output;
76
77
78
79                 try
80
81                 {
82
83                     objConn.Open();
84
85                     OracleDataReader objReader = objCmd.ExecuteReader();
86
87                     prvPrintReader(objReader);
88
89                     objReader.NextResult();
90
91                     prvPrintReader(objReader);
92
93                 }
94
95                 catch (Exception ex)
96
97                 {
98
99                     System.Console.WriteLine("Exception: {0}",ex.ToString());
100
101                 }
102
103                 objConn.Close();
104
105             }
106
107         }
108
109     }
110
111 }

Listing 10: The application code.

Working with DataSet and DataAdapter

The final example shows how to fill and update a DataSet object through a DataAdapter object.

The first thing to do is create four CRUD procedure to the emp table. Listing 11 shows how to create the package header.

create or replace package human_resources
as
type t_cursor is ref cursor;
procedure select_employee(cur_employees out t_cursor);
procedure insert_employee(p_empno number, p_ename varchar2, p_job varchar2, p_mgr number, p_hiredate date, p_sal number, p_comm number, p_deptno number);
procedure update_employee(p_empno number, p_ename varchar2, p_job varchar2, p_mgr number, p_hiredate date, p_sal number, p_comm number, p_deptno number);
procedure delete_employee(p_empno number);
end human_resources;

Listing 11: The creation of the package header.

Now let's define the package body as shown in Listing 12

create or replace package body human_resources
as
procedure select_employee(cur_employees out t_cursor)
is
begin
   open cur_employees for select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp;
end select_employee;
procedure insert_employee(p_empno number, p_ename varchar2, p_job varchar2, p_mgr number, p_hiredate date, p_sal number, p_comm number, p_deptno number)
is
begin
   update emp
   set ename=p_ename, job=p_job, mgr=p_mgr, hiredate=p_hiredate, sal=p_sal, comm=p_comm, deptno=p_deptno
   where empno=p_empno;
end insert_employee;
procedure update_employee(p_empno number, p_ename varchar2, p_job varchar2, p_mgr number, p_hiredate date, p_sal number, p_comm number, p_deptno number)
is
begin
   insert into emp
   values(p_empno,p_ename,p_job,p_mgr,p_hiredate,p_sal,p_comm,p_deptno);
end update_employee;
procedure delete_employee(p_empno number)
is
begin
    delete from emp
    where empno=p_empno;
end delete_employee;
end human_resources;

Listing 12: The package body creation.

And finally, let's see the application code in Listing 13. As you can see, to fill the data table, we need to define the CRUD (create, read, update, delete) operations through the OracleCommand and associate it to the DataAdapter. I fill the data table, and print out a message with the number of employees so far, and then add a new row representing one employee entity.

  1 using System;
  2
  3 using System.Collections.Generic;
  4
  5 using System.Text;
  6
  7 using System.Data.OracleClient;
  8
  9 using System.Data;
10
11
12
13 namespace CallingOracleStoredProc
14
15 {
16
17     class Program
18
19     {
20
21         static void Main(string[] args)
22
23         {
24
25             using (OracleConnection objConn = new OracleConnection("Data Source=ORCL; User ID=scott; Password=tiger"))
26
27             {
28
29                 OracleDataAdapter objAdapter = new OracleDataAdapter();
30
31
32
33                 OracleCommand objSelectCmd = new OracleCommand();
34
35                 objSelectCmd.Connection = objConn;
36
37                 objSelectCmd.CommandText = "human_resources.select_employee";
38
39                 objSelectCmd.CommandType = CommandType.StoredProcedure;
40
41                 objSelectCmd.Parameters.Add("cur_employees", OracleType.Cursor).Direction = ParameterDirection.Output;
42
43                 objAdapter.SelectCommand = objSelectCmd;
44
45
46
47                 OracleCommand objInsertCmd = new OracleCommand();
48
49                 objInsertCmd.Connection = objConn;
50
51                 objInsertCmd.CommandText = "human_resources.insert_employee";
52
53                 objInsertCmd.CommandType = CommandType.StoredProcedure;
54
55                 objInsertCmd.Parameters.Add("p_empno", OracleType.Number, 4, "empno");
56
57                 objInsertCmd.Parameters.Add("p_ename", OracleType.VarChar, 10, "ename");
58
59                 objInsertCmd.Parameters.Add("p_job", OracleType.VarChar, 9, "job");
60
61                 objInsertCmd.Parameters.Add("p_mgr", OracleType.Number, 4, "mgr");
62
63                 objInsertCmd.Parameters.Add("p_hiredate", OracleType.DateTime,12, "hiredate");
64
65                 objInsertCmd.Parameters.Add("p_sal", OracleType.Number, 7, "sal");
66
67                 objInsertCmd.Parameters.Add("p_comm", OracleType.Number, 7, "comm");
68
69                 objInsertCmd.Parameters.Add("p_deptno", OracleType.Number, 7, "deptno");
70
71                 objAdapter.InsertCommand = objInsertCmd;
72
73
74
75                 OracleCommand objUpdateCmd = new OracleCommand();
76
77                 objUpdateCmd.Connection = objConn;
78
79                 objUpdateCmd.CommandText = "human_resources.update_employee";
80
81                 objUpdateCmd.CommandType = CommandType.StoredProcedure;
82
83                 objUpdateCmd.Parameters.Add("p_empno", OracleType.Number, 4, "empno");
84
85                 objUpdateCmd.Parameters.Add("p_ename", OracleType.VarChar, 10, "ename");
86
87                 objUpdateCmd.Parameters.Add("p_job", OracleType.VarChar, 9, "job");
88
89                 objUpdateCmd.Parameters.Add("p_mgr", OracleType.Number, 4, "mgr");
90
91                 objUpdateCmd.Parameters.Add("p_hiredate", OracleType.DateTime, 10, "hiredate");
92
93                 objUpdateCmd.Parameters.Add("p_sal", OracleType.Number, 7, "sal");
94
95                 objUpdateCmd.Parameters.Add("p_comm", OracleType.Number, 7, "comm");
96
97                 objUpdateCmd.Parameters.Add("p_deptno", OracleType.Number, 7, "deptno");
98
99                 objAdapter.UpdateCommand = objUpdateCmd;
100
101
102
103                 OracleCommand objDeleteCmd = new OracleCommand();
104
105                 objDeleteCmd.Connection = objConn;
106
107                 objDeleteCmd.CommandText = "human_resources.delete_employee";
108
109                 objDeleteCmd.CommandType = CommandType.StoredProcedure;
110
111                 objDeleteCmd.Parameters.Add("p_empno", OracleType.Number, 4, "empno");
112
113                 objAdapter.DeleteCommand = objDeleteCmd;
114
115
116
117                 try
118
119                 {
120
121                     DataTable dtEmp = new DataTable();
122
123                     objAdapter.Fill(dtEmp);
124
125                     System.Console.WriteLine("Employee count = {0}", dtEmp.Rows.Count );
126
127                     dtEmp.Rows.Add(7935, "John", "Manager", 7782, DateTime.Now,1300,0,10);
128
129                     objAdapter.Update(dtEmp);
130
131                 }
132
133                 catch (Exception ex)
134
135                 {
136
137                     System.Console.WriteLine("Exception: {0}",ex.ToString());
138
139                 }
140
141                 objConn.Close();
142
143             }
144
145         }
146
147     }
148
149 }

Listing 12: The application code.

转载于:https://www.cnblogs.com/IamEasy_Man/archive/2010/11/17/1879947.html