1. 一次数据库连接，返回多个结果集
1.1 建立包规范
create or replace package QX_GDJTJ is -- Author : xxx -- Created : 2012-1-1 -- Purpose : 统计主设备缺陷 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;
1.2 建立包体
create or replace package body QX_GDJTJ isPROCEDURE GETGDJQXTJ( cur_id in varchar, cur_GDJQXTJ1 OUT T_CURSOR, cur_GDJQXTJ2 OUT T_CURSOR, cur_GDJQXTJ3 OUT T_CURSOR)ISBEGINOPEN cur_GDJQXTJ1 FORselect (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 '%合格%' and cljg not like '%不合格%') from dual; OPEN cur_GDJQXTJ2 FOR select (select count(0) from HVM_VIEW_QX where voltage='220kV' and gdjid=cur_id )-(select count(0) from HVM_VIEW_QX where voltage='220kV' and gdjid=cur_id and cljg like '%合格%' and cljg not like '%不合格%') from dual; OPEN cur_GDJQXTJ3 FOR 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 '%合格%' and cljg not like '%不合格%') from dual; end GETGDJQXTJ; end QX_GDJTJ;
1.3 C#调用，返回结果集
public static DataSet GetDataSet(string gdjId, string proName, string[] cursors) { OracleConnection Conn = GetConn(); DataSet ds = new DataSet(); try { OracleCommand cmd = new OracleCommand(); cmd.Connection = Conn; cmd.CommandText = proName; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("cur_id", OracleType.VarChar).Value = gdjId; for (int i = 0; i < cursors.Length; i++) { cmd.Parameters.Add(cursors[i], OracleType.Cursor).Direction = ParameterDirection.Output; } OracleDataAdapter da = new OracleDataAdapter(cmd); da.Fill(ds); } catch (System.Data.OracleClient.OracleException ex) { throw new Exception(ex.Message); } finally { Conn.Close(); } return ds; }
2. 基础知识
在给出具体的方法之前，我们先来看看以下几个名词的含义。
2.1 包的组成
包头(package)：包头部分申明包内数据类型，常量，变量，游标，子程序和异常错误处理，这些元素为包的公有元素。
包主体(package body)：包主体则是包定义部分的具体实现，它负责为包头中所声明子程序提供具体的实现，在包主体中还可以声明包的私有元素。
包头和包主体分开编译，并作为两个分开的对象分别存放在数据库字典中。
2.2 包的语法规则
包头的语法如下：
create or replace package 包名 As | IS procedure 过程名(); Function 函数名() return 数据类型; 变量定义; 异常定义; 光标定义; ........... ........... End 包名;
包体创建的语法：
create or replace Package Body 包名 As | IS Procedure 过程定义; Procedure 过程定义; Function 函数定义; Function 函数定义; .........; end 包名;
2.3 示例
示例1
create or replace package circle_packageaspi number(6,2):=3.14;function fun_get_circle_area(param_r number) return number;function fun_get_circle_c(param_r number) return number;end circle_package; /create or replace package body circle_packageasfunction fun_get_circle_area(param_r number) return numberasbeginreturn pi*power(param_r,2);end;function fun_get_circle_c(param_r number) return numberasbeginreturn pi*param_r*2;end;end circle_package; /declarev_area number(6,2);v_c number(6,2);v_r number(5):=3;beginv_area:=circle_package.fun_get_circle_area(v_r);v_c:=circle_package.fun_get_circle_c(v_r);Dbms_output.put_line('圆的半径：'||v_r );Dbms_output.put_line('圆的周长：'||v_c );Dbms_output.put_line('圆的面积：'||v_area );end; SQL> set serveroutput on; SQL> /
圆的半径：3
圆的周长：18.84 圆的面积：28.26PL/SQL procedure successfully completed
示例2
下面是student 和SC表的定义：
CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) NOT NULL, Ssex CHAR(4), Sage SMALLINT, Sdept CHAR(20)) tablespace starivespace; CREATE TABLE SC (Sno CHAR(9) NOT NULL, Cno CHAR(6) NOT NULL, Grade SMALLINT, PRIMARY KEY (Sno,Cno), constraint f1 FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Cno) REFERENCES Course(Cno) ) tablespace starivespace
实例：定义一个包，实现如果功能： 输入学号，分别返回该学生的所在系和相应的选课成绩（如果有多门成绩，那么就输出多门成绩）。定义包头：
create or replace package sdept_or_grade as procedure print_sdept(psno char); procedure print_grade(psno char); end; /
- 程序包已创建。
创建包体：
create or replace package body sdept_or_grade as procedure print_sdept(psno char) as psdept student.sdept%type; begin select sdept into psdept from student where sno=psno; dbms_output.put_line(psdept); exception when no_data_found then dbms_output.put_line(\'Invalid student number\'); end; procedure print_grade(psno char) as pgrade SC.grade%type; cursor printgrade is select grade into pgrade from sc where sno=psno; begin open printgrade; loop fetch printgrade into pgrade; dbms_output.put_line(pgrade); exit when printgrade%notfound; end loop; close printgrade; exception when no_data_found then dbms_output.put_line(\'Invalid student number\'); end; end; /
程序包体已创建。获取结果：
SQL> set serveroutput on; SQL> SQL> execute sdept_or_grade.print_sdept(\'0201\'); cs PL/SQL 过程已成功完成。 SQL> SQL> execute sdept_or_grade.print_grade(\'0201\'); 89 64 50 50 PL/SQL 过程已成功完成。
参考文章
，。
，