【安博培训技术】Oracle7 子程序和程序包20130912

Oracle7 子程序和程序包


回顾
游标用于处理查询结果集中的数据
游标类型有：隐式游标、显式游标和 REF 游标
隐式游标由 PL/SQL 自动定义、打开和关闭
显式游标用于处理返回多行的查询
显式游标可以删除和更新活动集中的行
要处理结果集中所有记录时，可使用循环游标
在声明 REF 游标时，不需要将 SELECT 语句与其关联


目标
了解和使用子程序 
了解和使用程序包


子程序 2-1
命名的 PL/SQL 块，编译并存储在数据库中。
子程序的各个部分：
声明部分
可执行部分
异常处理部分(可选)
子程序的分类：
过程 － 执行某些操作
函数 － 执行操作并返回值


子程序 2-2


子程序的优点：
模块化
将程序分解为逻辑模块
可重用性
可以被任意数目的程序调用
可维护性
简化维护操作
安全性
通过设置权限，使数据更安全


过程 8-1
过程是用于完成特定任务的子程序 
例如：
前往售票厅 询问关于车票的信息 排队等候 在柜台购买车票  
与存储过程对比学习：定义、执行


过程 8-2
创建过程的语法：
CREATE [OR REPLACE] PROCEDURE 
   <procedure name> [(<parameter list>)]
IS|AS 
   <local variable declaration>
BEGIN
   <executable statements>
[EXCEPTION
   <exception handlers>]
END;


[说明]与普通PL/SQL语句块的区别
Is和As没有区别，其他地方亦然
[注]过程体内不能使用查询语句，只能用于赋值（SQL语句块都如此）
     如果过程体语句有错误也能创建成功
     没有参数就不写，不用()


过程 8-3
create or replace procedure test4(v_id varchar2)
is
   v_ename emp.ename%type;
   v_sal emp.sal%type;
begin
   select ename, sal into v_ename,v_sal from emp where empno=v_id;
   dbms_output.put_line(v_ename||'   '||v_sal);   
end;


过程 8-4
执行过程的语法：
  EXECUTE procedure_name(parameters_list);


SQL> SET SERVEROUTPUT ON
SQL> EXEC proc_stu(‘007');
[说明]此例为根据输入项目编号，显示项目说明
execute可简写为exec
[注]调用带有输出参数的过程时，无需再写exec


过程 8-5
过程参数的三种模式：
IN
用于接受调用程序的值
默认的参数模式
OUT
用于向调用程序返回值 
IN OUT
用于接受调用程序的值，并向调用程序返回更新的值
[说明]没有返回值，但可以有Out、In Out参数，In参数为默认
在过程执行过程中，如需要返回可直接输入return
过程创建后，可在OEM中查看相应信息


过程 8-6
create or replace procedure test5(
    x in number,
    y in number,
    z out number
)
as 
begin
    z:=x+y;
end;
[例]In、Out参数的使用，输入学号，输出学生平均成绩；并写PL/SQL块该过程


过程 8-7
create or replace procedure test6(
       a in out number,
       b in out number
)
is 
begin
    dbms_output.put_line(a);
    dbms_output.put_line(b);
    a:=100;
    b:=100;
end;


SQL> SET SERVEROUT ON
SQL> declare
  num1 number := 100;
  num2 number := 500;
begin
  p_swap(num1,num2);
  dbms_output.put_line('num1='||num1);
  dbms_output.put_line('num2='||num2);
end;
/


[例]输入两个数，交换位置
create or replace procedure p_swap(p1 In Out number,p2 In Out number)
As
  v_temp number;
begin
  v_temp := p1;
  p1 := p2;
  p2 := v_temp;
end;
/
--调用


过程 8-8
将过程的执行权限授予其他用户：
SQL> GRANT EXECUTE ON proc_stu TO SCTOO;
SQL> GRANT EXECUTE ON proc_swap TO PUBLIC;
删除过程：
SQL> DROP PROCEDURE proc_swap;


函数 4-1
函数是可以返回值的命名的 PL/SQL 子程序。 
创建函数的语法：
  CREATE [OR REPLACE] FUNCTION 
  <function name> [(param1,param2)]
RETURN <datatype>  IS|AS 
  [local declarations]
BEGIN
  Executable Statements;
  RETURN result;
EXCEPTION
  Exception handlers;
END;
[说明]与过程对比：必须声名返回值类型，并在函数体里显式返回


函数 4-2
定义函数的限制：
函数只能接受 IN 参数，而不能接受 IN OUT 或 OUT 参数
形参不能是 PL/SQL 类型
函数的返回类型也必须是数据库类型
访问函数的两种方式：
使用 PL/SQL 块
使用 SQL 语句


函数 4-3
创建函数：
CREATE OR REPLACE FUNCTION func_hello
  RETURN  VARCHAR2
IS
BEGIN
  RETURN '朋友，您好';
END;
/
从 SQL 语句调用函数：
SQL> SELECT func_hello FROM DUAL;
【说明】执行不需要exec


函数 4-4
create or replace function get_sal(v_ename in varchar2)
return number
is
       v_sal number;
begin
       select sal into v_sal from emp where upper(ename)=upper(v_ename);
       return v_sal;
exception
       when no_data_found then
            raise_application_error(-20000,'员工不存在');
end;


过程和函数的比较
过 程 函  数
作为 PL/SQL 语句执行 作为表达式的一部分调用
在规格说明中不包含  RETURN 子句 必须在规格说明中包含 RETURN 子句
不返回任何值 必须返回单个值
可以包含 RETURN 语句，但是与函数不同，它不能用于返回值 必须包含至少一条 RETURN 语句


程序包
程序包是对相关过程、函数、变量、游标和异常等对象的封装
程序包由规范和主体两部分组成
规范 声明程序包中公共对象。包括类型、变量、常量、异常、游标规范和子程序规范等
主体 声明程序包私有对象和实现在包规范中声明的子程序和游标


[说明]与Java对比，package、interface
(1)规范：与应用程序的接口
(2)只有在规范中声名游标或子程序时才需要主体
[说明]由于我们已经在前面说明了一些常用的内置包：dbms_output、dbms_lob、dbms_random，因此我们不再单独介绍内置包了


创建程序包 2-1


程序包规范
CREATE [OR REPLACE]
  PACKAGE 
  package_name IS|AS
[Public item declarations]
[Subprogram specification]
END [package_name];


程序包主体
CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS
[Private item declarations]
[Subprogram bodies]
[BEGIN
Initialization]
END [package_name];


创建程序包 2-2
create or replace package body pack_stu As
procedure proc_stu(v_id varchar2)
Is
  v_name t_student.f_name%type;
  v_dept t_student.f_department%type;
  v_class t_student.f_class%type;
begin
  v_globalid := v_id;
  select f_name,f_department,f_class into v_name,v_dept,v_class from t_student where f_id=v_id;
  ...
end proc_stu;
function func_stu(v_id varchar2)
return varchar2 As
  ...
end func_stu;
end pack_stu;
/


[例]程序包：包含共有变量v_globalid；过程proc_stu：根据学号显示学生姓名、系别、班级；函数func_stu：根据学号显示学生姓名、系别、班级；


--程序包规范
create or replace package pack_stu As
  v_globalid char(8);
  procedure proc_stu(v_id varchar2);
  function func_stu(v_id varchar2) return varchar2;
end pack_stu;
/
--程序包主体
create or replace package body pack_stu As
procedure proc_stu(v_id varchar2)
Is
  v_name t_student.f_name%type;
  v_dept t_student.f_department%type;
  v_class t_student.f_class%type;
begin
  v_globalid := v_id;
  select f_name,f_department,f_class into v_name,v_dept,v_class from t_student where f_id=v_id;
  dbms_output.put_line('学号:'||v_name||'姓名:'||v_dept||'班级:'||v_class);
exception
  when no_data_found then
    dbms_output.put_line('未找到相应学生');
end proc_stu;
function func_stu(v_id varchar2)
return varchar2 As
  v_name t_student.f_name%type;
  v_dept t_student.f_department%type;
  v_class t_student.f_class%type;
begin
  v_globalid := v_id;
  select f_name,f_department,f_class into v_name,v_dept,v_class from t_student where f_id=v_id;
  return '学号:'||v_name||'姓名:'||v_dept||'班级:'||v_class;
exception
  when no_data_found then
    dbms_output.put_line('未找到相应学生');
end func_stu;
end pack_stu;
/


--使用程序包
select pack_stu.func_stu('001') from dual;


begin
  dbms_output.put_line(pack_stu.v_globalid);
end;
/


程序包的优点
模块化
更轻松的应用程序设计
信息隐藏
新增功能
性能更佳


新增功能：“重载”、全局变量和游标
性能更佳：程序包加载到内存中


程序包中的游标 2-1
游标的定义分为游标规范和游标主体两部分
在包规范中声明游标规范时必须使用 RETURN 子句指定游标的返回类型
RETURN子句指定的数据类型可以是：
用 %ROWTYPE 属性引用表定义的记录类型
程序员定义的记录类型
[说明]用户自定义类型可参考OEM中操作；注意程序包中的游标的特殊点，说明理由


程序包中的游标 2-2
SQL> CREATE OR REPLACE PACKAGE pack_cur As
  Cursor cur_stu(stuclass number) return t_student%rowtype;
  procedure proc_stu(stuclass number);
end;
/


SQL> CREATE OR REPLACE PACKAGE BODY pack_cur AS
Cursor cur_stu(stuclass number) return t_student%rowtype Is
select * from t_student where f_class = stuclass;
procedure proc_stu(stuclass number)
As
  rec_stu t_student%rowtype;
begin
  Open cur_stu(stuclass);
  loop
    Fetch cur_stu Into rec_stu;
    Exit when cur_stu%NotFound;
    dbms_output.put_line('学生姓名：'||rec_stu.f_name);
  end loop;
end proc_stu;
end;
/


[例]定义一游标：接收班级为参数，返回行属性；定义一过程：利用游标显示指定班级学生的姓名。
--调用程序包
exec pack_cur.proc_stu(1);


有关子程序和程序包的信息
USER_OBJECTS 视图包含用户创建的子程序和程序包的信息
SELECT object_name, object_type
FROM USER_OBJECTS
WHERE object_type IN ('PROCEDURE', 'FUNCTION',
 'PACKAGE', 'PACKAGE BODY');


USER_SOURCE 视图存储子程序和程序包的源代码
SELECT line, text FROM USER_SOURCE
WHERE NAME = 'PACK_CUR';


总结
子程序是命名的 PL/SQL 块，可带参数并可在需要时随时调用
有两种类型的PL/SQL子程序，即过程和函数
过程用户执行特定的任务，函数用于执行任务并返回值
程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装
程序包由两部分组成，即包规范和包主体
使用程序包的优点是：模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳