本章内容：
目录
子程序
– 过程
– 函数
程序包
– 说明
– 主体
一、子程序：
命名的 PL/SQL 块,它可以封装sql语句、存储过程、函数、包等信息,实现对程序的多次访问
1.1、子程序的组成部分
– 声明部分
– 可执行部分
– 异常部分（可选）
1.2、子程序的优点
-模块化
-将程序分解为多个逻辑模块
-可重用性
-子程序在被执行之后，可以在任意数目的应用程序中使用
-可维护性
-子程序简化了维护
-具有可扩展性
1.3、子程序的类型
函数：用于执行某项操作并返回值
过程：用于执行某项操作
二、函数：
函数用于返回特定的数据，当建立函数时，在函数头部必须包含return 子句。而在函数体内必须包含return 语句返回的数据。我们可以使用create function来建立函数。
函数分为两个组成部分：
-定义部分：以关键字 FUNCTION 开始，以 RETURN 子句结束
-主体部分：以关键字 IS 开始，以关键字 END 结束，后面可以跟可选函数名称
2.1、函数分类：
-无参函数
-有参函数
2.2、创建函数：
语法结构：
CREATE [OR REPLACE] FUNCTION <函数名> [(参数1, …参数N) ] RETURN 数据类型
IS
[局部声明]
BEGIN
可执行语句 ;
[EXCEPTION]
RETURN 变量名称;
END [<函数名>];
RETURN语句:完成函数的执行，并将控制返回的结果给调用者。
参数的分类：
-无参的存储过程：
-有参的存储过程：
IN：输入参数
OUT：输出参数
OUT IN：输入输出参数
无参函数的使用：
示例-1：定义函数，返回员工编号为7839的工资。
create or replace function funSal return number
is
v_sal number(11);
begin
select sal into v_sal from emp where empno=7839;
return v_sal;
end;
调用：
SELECT funSal FROM DUAL;
2.3、函数调用的两种方法：
-使用PL/SQL语句：如果函数中带有“输出参数”，则必须使用这种方法调用函数。
begin
dbms_output.put_line(函数名(参数));
end;
如果没有参数，则直接写函数名即可。
-使用 SQL 语句：无参函数、输入参数的函数可以使用这种方法，输出参数的函数不能使用。
SELECT function_name(参数) FROM DUAL;
注意:如果函数创建过程有编译错误，可以使用show error;命令显示错误
示例-2：求1-10的数之和(无参函数)。
Create Or Replace Function funSum RETURN Number
IS
v_sum number(11):=0;
Begin
for i in 1..10 loop
v_sum :=v_sum+i;
end loop;
return v_sum;
end funSum;
调用：
begin
dbms_output.put_line(funSum);
end;
练习-1：求1-10之间的偶数之和，然后输出。
Create Or Replace Function funSum RETURN Number
IS
v_sum number(11):=0;
Begin
for i in 1..10 loop
if i mod 2=0 then
v_sum :=v_sum+i;
dbms_output.put_line('v_sum:' || v_sum);
end if;
end loop;
return v_sum;
end funSum; 调用：
begin
dbms_output.put_line(funSum);
end;
有参函数：
-输入参数(in)：定义输入参数可以使用“in”关键字来标注，如果没有in关键字，其默认也是输入参数。
-输出参数(out)：定义输出参数可以使用out关键字来标注，此关键字不能省略。
输入参数示例：
示例-3：求1-N的数之和(有参函数)。
Create Or Replace Function funSum(n number) RETURN Number
IS
v_sum number(11):=0;
Begin
for i in 1..n loop
v_sum :=v_sum+i;
end loop;
return v_sum;
end funSum;
调用：
begin
dbms_output.put_line(funSum(10));
end;
练习-2：输入雇员的姓名，返回该雇员的年薪
create or replace function funYearSal(name varchar2)
return number is
annual_salazy number(7,2);
begin
--执行部分
select sal*12+nvl(comm, 0) into annual_salazy from emp where ename=name;
return annual_salazy;
end funYearSal; 调用：select funYearSal('SCOTT') from dual;
注意：一般情况下，函数只需要返回单个数据。如果希望使用函数时返回多个数据（如同时返回雇员名和工资），则可以使用OUT输出参数来实现此功能。
2.4、删除函数：
DROP FUNCTION <函数名>;
三、存储过程：
1、存储过程：
-执行特定操作的子程序
-存储于数据库中并可由任意匿名块调用
-能够接受参数
-在参数声明中的数据类型区分符应为无限制的
2、存储过程的优点
-增强了数据安全性
-提高了数据库性能
-节省内存
-提高了开发的工作效率
-完整性
3、存储过程组成部分
具有两个部分:
-定义部分：以关键字 PROCEDURE 开始，以过程名或参数列表结束
-主体部分：以关键字 IS 开始，以关键字 END 结束，后面可以跟可选过程名称
创建过程时， Oracle 自动执行下列步骤：
–编译过程
–存储所编译的代码
–在数据库中存储过程
4、存储过程语法
语法
CREATE [OR REPLACE] PROCEDURE <过程名> [(参数1,… 参数N) ]
IS|AS
DECLARE
[局部声明]
BEGIN
可执行语句；
EXCEPTION
[例外处理程序]；
END [<过程名>];
参数说明：
变量名 [IN|OUT|IN OUT] 数据类型 [{:= | DEFAULT} 值]
参数的分类：
-无参：
-有参：
IN：输入参数
OUT：输入参数
OUT IN：输入输出参数
★ 无参存储过程的使用：
示例-4：使用无参的存储过程来输出helloworld.
create or replace procedure pro_helloworld
as
begin
dbms_output.put_line('helloworld');
end;
调用：
execute pro_helloworld;
或
exec pro_helloworld;
练习-3：查询工资最高的员工姓名、工资、部门名称。(使用无参存储过程)
create or replace procedure pro_MaxSal
is
v_ename emp.ename%type;
v_sal emp.sal%type;
v_dname dept.dname%type;
begin
select e.ename,e.sal,d.dname into v_ename,v_sal,v_dname
from emp e,dept d where e.deptno=d.deptno and e.sal=(select max(sal) from emp);
dbms_output.put_line('姓名：' || v_ename || ' 工资：' || v_sal || ' 部门名称：' || v_dname);
end pro_MaxSal;
★ “输入参数与输出参数”的存储过程：
示例-5：通过编号查询某个员工的工资.
create or replace procedure pro_empno(v_number in number,v_sal out number)
as
begin
select sal into v_sal from emp where empno=v_number;
end;
调用：
declare
v_no number(11):=&no;
v_sal number(11);
begin
pro_empno(v_no,v_sal);
dbms_output.put_line('工资：' || v_sal);
end;
练习-4：创建一个简单的表“mytest”,字段(uid,uname,upad),通过存储过程往里面插入3条记录。
创建表：
create table mytest(usid number(11),uname varchar2(20),upad varchar2(20));
创建存储过程
create or replace procedure pro_mytest(usid number,uname varchar2,upad varchar2)
is
begin
insert into mytest values(usid,uname,upad);
end pro_mytest;
调用：
exec pro_mytest(1,'tom','123');
5、过程与函数的比较
-过程：
1.作为PL/SQL语句执行
2.在规范中不包含return子句
3.不返回任何值
4.可以包含return语句，但是与函数不同，它不能用于返回值
-函数：
1.作为表达式的一部分调用
2.必须在规范中包含return子句
3.必须返回单个值
4.必须包含至少一条return子句
四、程序包：
程序包是一种数据库对象,是一个与应用程序的接口,它是对相关的PL/SQL类型、子程序、游标、异常、变量和常量的封装。
1、程序包包括两部分内容：
程序包规范：
在程序包规范中，可以声明类型、变量、常量、异常、游标和子程序，包规范中声明的内容全部都是公共的,可以在没有程序包主体的情况下存在。
程序包主体：
程序包主体用于实现在程序包规范中定义的游标、子程序等，同时在程序包主体中可以声明局部变量和私有过程
私有过程只能在同一个包内部调用，外部无法调用。
注意：包的规范只包含了过程和函数的声明，但是没有过程和函数的实现代码。包体用于实现包规范中的过程和函数。
2、程序包的优点
-模块化
-更轻松的应用程序设计
-信息隐藏
-新增功能性
-性能更佳
-重载
重载：程序包中的多个子程序可以具有相同的名称,它们的形参是不同的,如果子程序的参数仅名称或模式不同，则不能重载不能基于其返回类型重载子程序
3、创建程序包与包体的语法结构：
包创建的语法：
create [or replace] package 包名称 is | as
--存储过程
--函数
--变量
--记录类型
--游标
end 包名称;
包体创建的语法：
create or replace package body 包体名称 is
--1、对存储过程的实现
--2、对函数的实现
--......
end 包体名称;
注意事项：
-包体依赖与包而存在,包是作为声明的部分
-包体是作为实现的主体,包体中是对包定义的实现
-包体可以没有,但是包必需有.
-包体的名称要与包的名称一定要一致,包体中必须要对过程和函数进行实现.
示例-6:通过部门编号显示每个人的工资，姓名、及部门名称.
(1)、创建包:
create or replace package p_name is
--0、定义动态游标
type cur_emp is ref cursor;
--1、定义存储过程
procedure pro_empinfo(v_no in number,v_cur out cur_emp);
--2、定义函数
function fun_empinfo(v_no in number)return number;
--3、定义记录类型
type t_type is record(
v_sal varchar2(20),
v_ename varchar2(20),
v_dname varchar2(20)
);
--4、定义变量
v_record t_type;
end p_name;
(2)、创建包体:
create or replace package body p_name is
--1、对存储过程的实现
procedure pro_empinfo(v_no in number,v_cur out cur_emp)
is
v_sql varchar2(200);
begin
v_sql :='select e.sal,e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and e.deptno=' || v_no;
open v_cur for v_sql;--打开游标
--dbms_output.put_line('工资:' || v_record.v_sal || '姓名:' || v_record.v_ename || '部门名称' || v_record.v_dname);
end pro_empinfo;--结束过程的实现
--2、对函数的实现
function fun_empinfo(v_no in number)return number
is
begin
--select e.sal,e.ename,d.dname into v_record from emp e,dept d where e.deptno=d.deptno and e.empno=v_no;
null;
return 0;
end fun_empinfo;--结束函数的实现
end p_name; (3)、通过包中的存储过程
declare
v_deptno number :=&_no;
v_record p_name.t_type;
v_cursor p_name.cur_emp;
begin
p_name.pro_empinfo(v_deptno,v_cursor);
loop
fetch v_cursor into v_record;
exit when v_cursor%notfound;
dbms_output.put_line('工资:' || v_record.v_sal || '姓名:' || v_record.v_ename || '部门名称' || v_record.v_dname);
end loop;
close v_cursor;
end;
4、引用程序包对象：必须使用点符号引用程序包对象和子程序
– 程序包名.类型名
– 程序包名.对象名
– 程序包名.子程序名
5、删除程序包：DROP PACKAGE <程序包名>
总结：
-子程序是命名的 PL/SQL 块
-PL/SQL 支持两种类型的子程序，即过程和函数
-过程用于执行某项操作
-函数用于计算并返回值
-程序包是一种数据库对象，它是相关对象的封装
-程序包由两部分组成，即规格说明和主体
-允许重载打包的子程序
-使用程序包的优点是：模块化、更轻松的应用程序
-设计、信息隐藏、新增功能以及性能更佳
扫一扫
2173
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
