1. Establish package specifications
Create or replace package QX_GDJTJ is
-- Author: liuwei
-- Created: 10:20:05
-- Purpose: Count master device defects
TYPE T_CURSOR is ref cursor;
PROCEDURE GETGDJQXTJ
(
Cur_id in varchar,
Cur_GDJQXTJ1 OUT T_CURSOR,
Cur_GDJQXTJ2 OUT T_CURSOR,
Cur_GDJQXTJ3 OUT T_CURSOR
);
End QX_GDJTJ;
2. Create a package
Create or replace package body QX_GDJTJ is
PROCEDURE GETGDJQXTJ
(
Cur_id in varchar,
Cur_GDJQXTJ1 OUT T_CURSOR,
Cur_GDJQXTJ2 OUT T_CURSOR,
Cur_GDJQXTJ3 OUT T_CURSOR
)
IS
BEGIN
OPEN cur_GDJQXTJ1
Select (select count (0) from HVM_VIEW_QX where voltage = '500kv 'and gdjid = cur_id)-(select count (0) from HVM_VIEW_QX where voltage = '500kv 'and gdjid = cur_id and cljg like' % qualified % 'and cljg not like' % unqualified % ') from dual;
OPEN cur_GDJQXTJ2
Select (select count (0) from HVM_VIEW_QX where voltage = '220k' and gdjid = cur_id)-(select count (0) from HVM_VIEW_QX where voltage = '220k' and gdjid = cur_id and cljg like '% qualified %' and cljg not like '% unqualified %') from dual;
OPEN cur_GDJQXTJ3
Select (select count (0) from HVM_VIEW_QX where voltage = '110kv 'and gdjid = cur_id)-(select count (0) from HVM_VIEW_QX where voltage = '110kv 'and gdjid = cur_id and cljg like' % qualified % 'and cljg not like' % unqualified % ') from dual;
End GETGDJQXTJ;
End QX_GDJTJ;
3. C # Call, return result set
Code 1 public static DataSet GetDataSet (string gdjId, string proName, string [] cursors)
2 {
3 OracleConnection Conn = GetConn ();
4 DataSet ds = new DataSet ();
5 try
6 {
7 OracleCommand cmd = new OracleCommand ();
8 cmd. Connection = Conn;
9 cmd. CommandText = proName;
10 cmd. CommandType = CommandType. StoredProcedure;
11 cmd. Parameters. Add ("cur_id", OracleType. VarChar). Value = gdjId;
12 for (int I = 0; I <cursors. Length; I ++)
13 {
14 cmd. Parameters. Add (cursors [I], OracleType. Cursor). Direction = ParameterDirection. Output;
15}
16 OracleDataAdapter da = new OracleDataAdapter (cmd );
17 da. Fill (ds );
18}
19 catch (System. Data. OracleClient. OracleException ex)
20 {
21 throw new Exception (ex. Message );
22}
23 finally
24 {
25 Conn. Close ();
26}
27 return ds;
28}
