Oracle Class8. 子程序和程序包
------------------------2013-5-19------------------------
子程序的类型
--过程，用于执行某项操作
--函数，用于执行某项操作并返回值
create or replace procedure <proc_name> --过程名称
[parameter list] --参数列表
is|as
<local declarations>; --局部声明
begin
(executable statements) --可执行语句
end;
参数模式：
- in 接受值，默认模式
- out 将值返回给子程序的调用程序
- in out 接受值并返回已更新的值
执行过程
execute Procedure_name(list of parameters)
execute可以不写。
删除过程
drop procedure Procedure_name;
函数
create or replace function <function_name> --函数名称
[argument list] --参数列表
return datatype is|as --数据类型
(local declaration) --局部声明
begin
(executable statements) --可执行语句
end;
访问函数
--用两种方式进行访问
使用pl/sql块 variable := function_name(parameter list);
使用sql语句 select function_name(parameter) from dual;
--仅接受in参数
过程与函数
过程 函数
作为pl/sql语句执行 作为表达式的一部分调用
在规格说明中不包含return子句 必须在规格说明中包含return子句
可以返回任何值 必须返回单个值
可以包含return语句，但是与函数 必须包含至少一条return语句
不同，它不能用于返回值。
pragma autonomous_transaction 用于标记子程序
程序包
相关对象的封装
程序包的各部分
- 程序包规格说明 声明子程序
- 程序包主体 定义子程序
程序包规格说明
- 使用create package命令进行创建
- 包含公用对象和类型
- 声明类型、常量、变量、异常、游标和子程序
- 可以在没有程序包主体的情况下存在
程序包主体
- 使用create package body命令进行创建
- 包含子程序和游标的定义
- 包含私有声明
- 不能在没有程序包规格说明的情况下存在
程序包的执行
Package-name.type-name 程序包名称.类型名称
Package-name.object-name 程序包名称.对象名称
Package-name.subprogram-name 程序包名称.子程序名称
程序包优点：
模块化，信息隐藏。
User_objects: 用于检查对象是否存在
User_source: 用于获取对象的代码
程序包 重载。方法名相同，形参不同，与方法名，参数模式，或返回类型无关。
程序包可以理解为java里面的类。思想。
程序包是一种数据库对象，它是相关对象的封装。
select * from user_procedures;
select * from user_objects
select * from user_source;
select * from user_source where name = 'ADDNEW';
create or replace procedure proInsertT
(
a int,
b int
)
as
begin
--a := a + a; --会报错!! 错误：PLS-00363: 表达式 'A' 不能用作赋值目标
--b := b + b; --错误：PLS-00363: 表达式 'B' 不能用作赋值目标
insert into c6 values (a, b);
end;
create or replace function GetCountBy(
x in int,
y in int,
z out int
)
return int
as
r int;
begin
z := x+y;
r :=0;
return (r);
end;
--调用--
declare
x int;
y int;
z int;
r int;
begin
x := 1;
y := 2;
z := 0;
r := GetCountBy(x,y,z);
-- select GetCountBy(x, y, z) from dual; --PL/SQL: ORA-06572: 函数 GETCOUNTBY 具有输出参数
dbms_output.put_line('z:' || z);
dbms_output.put_line('r:' || r);
end;
z:3
r:0
PL/SQL 过程已成功完成。
select sysdate from dual;
declare
d date;
begin
d := sysdate;
dbms_output.put_line('d:' || d);
end;
select * from user_source where name = upper('proGetEmpRecByEmpNo');
-- oracle是区分大小写的，使用upper函数来转换。--
variable g_price_num number;
variable g_type_char varchar2(12) --定义变量??
##包规格说明##
create or replace package pkg_Titles
is
type type_Title_rec is record
(
title titles.title%type,
price titles.price%type,
type titles.type%type
);
function FunGetMaxPrice
return type_Title_rec;
end pkg_Titles;
##包主体##
create or replace package body pkg_Titles
is
function FunGetMaxPrice
return type_Title_rec
is
/*type type_Title_rec is record
(
title titles.title%type,
price titles.price%type,
type titles.type%type
); */ --PLS-00498:在说明一个类型之前对其进行了非法使用。
-- 因为在包规格说明中已经进行了定义。
lv_title_rec type_Title_rec;
begin
select title, price, type into lv_title_rec
from titles
where price =
(
select max(price)
from titles
);
return lv_title_rec;
end FunGetMaxPrice;
end pkg_Titles;
##调用##
declare
lv_title_rec pkg_Titles.type_Title_rec;
begin
lv_title_rec := pkg_Titles.FunGetMaxPrice;
dbms_output.put_line(lv_title_rec.title);
dbms_output.put_line(lv_title_rec.price);
dbms_output.put_line(lv_title_rec.type);
end;
-- 使用scott架构(tiger)
create table emp as
select * from scott.emp;
-- 存储过程
-- 根据员工编号返回一条员工记录
create or replace procedure proGetEmpRec(
p_emp_no in emp.empno%type,
p_emp_rec out emp%rowtype
)
as
begin
select * into p_emp_rec from emp where empno = p_emp_no;
end;
-- 调用存储过程，接收返回的员工记录，并输出到缓存区
declare
lv_emp_rec emp%rowtype;
begin
proGetEmpRec('7499',lv_emp_rec);
dbms_output.put_line('EMP_NO:' || lv_emp_rec.empno);
dbms_output.put_line('EMP_NAME:' || lv_emp_rec.ename);
dbms_output.put_line('EMP_JOB:' || lv_emp_rec.job);
dbms_output.put_line('EMP_SAL:' || lv_emp_rec.sal);
end;
-- 函数
-- 根据给定的员工编号，返回相应的员工记录。
create or replace function funGetEmpRecByNo
(
p_emp_no emp.empno%type
)
return emp%rowtype
as
lv_emp_rec emp%rowtype;
begin
select * into lv_emp_rec from emp where empno = p_emp_no;
return (lv_emp_rec);
end;
-- 调用函数，接收返回的员工记录，并输出到缓存区
declare
lv_emp_rec emp%rowtype;
begin
lv_emp_rec := funGetEmpRecByNo('7499');
dbms_output.put_line('EMP_NO:' || lv_emp_rec.empno);
dbms_output.put_line('EMP_NAME:' || lv_emp_rec.ename);
dbms_output.put_line('EMP_JOB:' || lv_emp_rec.job);
dbms_output.put_line('EMP_SAL:' || lv_emp_rec.sal);
end;
show errors 查看错误
-- 视图 + 函数(根据书籍编号获取相应的总销量)
-- 视图 + 函数(根据书籍编号获取相应的总销量的排位)
##针对以上的过程和函数写入程序包中##
create or replace package pkg_emp
is --注意区别：is后面是没有begin，否则报错。 is as都可以。
procedure proGetEmpRec(
p_emp_no in emp.empno%type,
p_emp_rec out emp%rowtype
);
function funGetEmpRecByNo
(
p_emp_no emp.empno%type
)
return emp%rowtype;
end pkg_emp;
create or replace package body pkg_emp
is --注意区别：is后面是没有begin，否则报错。 is as都可以。
procedure proGetEmpRec(
p_emp_no in emp.empno%type,
p_emp_rec out emp%rowtype
)as
begin
select * into p_emp_rec from emp where empno = p_emp_no;
end; --可以写成end proGetEmpRec;
function funGetEmpRecByNo
(
p_emp_no emp.empno%type
)
return emp%rowtype
as
lv_emp_rec emp%rowtype;
begin
select * into lv_emp_rec from emp where empno = p_emp_no;
return (lv_emp_rec);
end; --可以写成end funGetEmpRecByNo;
end pkg_emp;
declare
lv_emp_rec emp%rowtype;
begin
pkg_emp.proGetEmpRec('7499',lv_emp_rec);
dbms_output.put_line('EMP_NO:' || lv_emp_rec.empno);
dbms_output.put_line('EMP_NAME:' || lv_emp_rec.ename);
dbms_output.put_line('EMP_JOB:' || lv_emp_rec.job);
dbms_output.put_line('EMP_SAL:' || lv_emp_rec.sal);
dbms_output.put_line('-------------我是分隔线-------------');
lv_emp_rec := pkg_emp.funGetEmpRecByNo('7499');
dbms_output.put_line('EMP_NO:' || lv_emp_rec.empno);
dbms_output.put_line('EMP_NAME:' || lv_emp_rec.ename);
dbms_output.put_line('EMP_JOB:' || lv_emp_rec.job);
dbms_output.put_line('EMP_SAL:' || lv_emp_rec.sal);
end;
--定义关联游标--
type cur_titles is ref cursor;
--给游标赋值的代码--
open p_titles_cursor for select * from titles;
select * from v$sqlarea; --查询结果返回的特别多--
