oracle存储过程函数和程序包

理过程、函数、程序包
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
总结
l 过程和函数都是命名的PL/SQL程序块，合称子程序 l 过程有三种参数模式：IN、OUT和IN OUT l 过程用户执行特定的任务，函数用于执行任务并返
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
不同点：
过程与函数小结2-2
过程
函数
参数模式可以是IN、OUT或IN OUT 参数模式只能是IN模式
在语法规范中不包含RETURN子句 在语法规范中必须包含RETURN子句
在可执行语句部分可以有RETURN语 在可执行语句部分至少应该包含一条
函数4-4
l 删除函数 DROP FUNCTION f_dept_getname_byno
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
过程与函数小结2-1
l 共同点：两者的实质都是已命名的PL/SQL程 序块，即子程序，它们是子程序的两种类型， 存储在数据库中，可以从任何数据库客户端和 前台应用程序中调用它们。
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
程序包5-3
--函数f_num_range FUNCTION f_dept_getname_byno(i_deptno NUMBER) RETURN VARCHAR2 AS
l_dname VARCHAR2(14); BEGIN
SELECT dname INTO l_dname FROM dept WHERE deptno=i_deptno;
l 执行结果:
l 删除过程 l DROP PROCEDURE sp_helloWorld;
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
带输入参数的过程3-1
l 实现的功来自百度文库：向表dept中插入一条记录 l 创建带输入参数的过程，代码为：
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
CREATE OR REPLACE PROCEDURE sp_helloWorld AS BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World!'); END sp_helloWorld;
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
不带参数的过程2-2
l 执行过程 l EXECUTE sp_helloWorld;
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
程序包5-2
程序包主体部分的代码 l
CREATE OR REPLACE PACKAGE BODY pkg_dept AS --过程sp_dept_insert
PROCEDURE sp_dept_insert
(i_deptno NUMBER,i_dname VARCHAR2,i_loc VARCHAR2)
oracle存储过程函数和程序包
带输出参数的过程3-1
l 实现功能：通过输出参数count来得到dept表 中的记录数
l 创建带输出参数的过程
CREATE OR REPLACE PROCEDURE sp_getcount (o_count OUT NUMBER) AS BEGIN
SELECT COUNT(*) INTO o_count FROM dept; END sp_getcount;
CREATE OR REPLACE PACKAGE pkg_dept AS
PROCEDURE sp_dept_insert (i_deptno NUMBER,i_dname VARCHAR2,i_loc VARCHAR2); FUNCTION f_dept_getname_byno(i_deptno NUMBER) RETURN VARCHAR2; END pkg_dept;
AS
BEGIN
INSERT INTO dept VALUES(i_deptno,i_dname,i_loc);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('添加失败!原因为:'||SQLERRM);
ROLLBACK;
END sp_dept_insert;
l_count NUMBER;
lBES创GELIN建ECT带COINUNOT(*U) INTT参O l_数co的unt过FR程OM，dep代t W码HE为RE：
dname=io_value; IF(l_count>0) THEN
io_value:='存在'; ELSE
io_value:='不存在'; END IF; END sp_dept_dname_exist;
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
带IN OUT参数的过程2-2
l 执行过程：
DECLARE l_iotest varchar2(20):='ACCOUNTING';
BEGIN sp_dept_dname_exist(l_iotest); DBMS_OUTPUT.PUT_LINE('部门名称ACCOUNTING'||l_iotest||'!');
l 执行过程:
VARIABLE test_cur REFCURSOR; EXECUTE pkg_dept.sp_dept_getall(:test_cur); PRINT test_cur;
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
OEM中管理过程、函数、程序包
l 请老师用浏览器打开OEM，演示在OEM中管
l (i_deptno NUMBER)
l RETURN VARCHAR2
l AS
l l_dname VARCHAR2(14);
l BEGIN
l SELECT dname INTO l_dname FROM dept WHERE deptno=i_deptno;
l RETURN l_dname;
l EXCEPTION
小结：程序包的优点
l 使用程序包的优点：
Ø 信息隐藏 Ø 模块化 Ø 对多态的支持 Ø 性能更佳
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
过程返回结果集2-1
l 在Oracle中的过程不能象SQL SERVER那样 直接返回结果集，而必须借助于REF游标
l 程序包规范中的代码:
在 5. 使用函数查询部门信息 6. 使用程序包封装过程和函数
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
相关实践知识
l 从开始菜单中打开SQL*Plus工具，以SCOTT 用户的身份登录到数据库
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
不带参数的过程2-1
输入以下代码，创建一个最简单的过程 功能: 显示”Hello World!”
CREATE OR REPLACE PACKAGE pkg_dept AS TYPE deptcursor IS REF CURSOR; PROCEDURE sp_dept_getall( dept_cur OUT deptcursor); END pkg_dept;
学习改变命运,知 识创造未来
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
l 输出结果：
带输出参数的过程3-3
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
带IN OUT参数的过程2-1
lC(ioR实_EvAa现TluEe功OINRO能RUETP:查LVAACR询ECHP某ARRO个2C)E部DU门RE名sp_称dep在t_d表namdee_pextis中t 是否 IS 已经存在
带输入参数的过程3-2
CREATE OR REPLACE PROCEDURE sp_dept_insert (i_deptno NUMBER, i_dname VARCHAR2, i_loc VARCHAR2) AS BEGIN
INSERT INTO dept VALUES(i_deptno,i_dname,i_loc); COMMIT; EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('添加失败!原因为:'||SQLERRM); ROLLBACK; END sp_dept_insert;
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
带输入参数的过程3-3
l 分别输入两组数据来执行过程 ，结果如下：
学习改变命运,知 识创造未来
RETURN l_dname; EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN '错误!该编号的部门不存在!';
END f_dept_getname_byno; END pkg_dept;
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
程序包5-4
l 执行程序包中的过程和函数：
oracle存储过程函数和程序包
过程返回结果集2-2
l 程序包主体中的代码:
CREATE OR REPLACE PACKAGE BODY pkg_dept AS
PROCEDURE sp_dept_getall(dept_cur OUT deptcursor) IS BEGIN OPEN dept_cur FOR SELECT * FROM dept; END sp_dept_getall; END pkg_dept;
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
教学目标
l 掌握过程的用法 l 掌握函数的用法 l 理解过程与函数的相同点和不同点 l 理解程序包的概念并能熟练应用
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
工作任务
1. 用无参过程实现“Hello World！”程序 2. 用带输入参数的过程向表中插入记录 3. 用带输出参数的过程查询表中的记录数 4. 使用带输入输出参数的过程查询记录是否存
END;
l 输出结果： l 部门名称ACCOUNTING存在!
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
函数4-1
l 实现功能： 按部门编号查询出表dept中的部门名称
l 创建一个函数，代码为：
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
函数4-2
l CREATE OR REPLACE FUNCTION f_dept_getname_byno
oracle存储过程函数和 程序包
学习改变命运,知 识创造未来
2021年2月17日星期三
回顾
l 游标就是指向上下文区的句柄或指针。 l 游标有两种类型：显式游标、隐式游标。 l 四个游标属性 ：SQL％FOUND、SQL％
NOTFOUND、SQL％ROWCOUNT、SQL％ ISOPEN l 显式游标的使用步骤 ：４个 l 记录变量和%ROWTYPE l 带参数的游标 l 游标FOR循环（循环游标） l 游标中的更新和删除 l REF游标
句，但其后不能加任何表达式
RETURN expression语句
可以用EXECUTE语句来执行
不能用EXECUTE语句来执行
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
程序包5-1
l 利用程序包封装过程sp_dept_insert和函数 f_dept_getname_byno
l 程序包规范部分的代码：
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
带输出参数的过程3-2
l 编写一段匿名的PL/SQL块来执行过程
DECLARE cnt NUMBER;
BEGIN sp_getcount(cnt); DBMS_OUTPUT.PUT_LINE('dept表中的记录数为: '||cnt);
END;
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
l 删除程序包：
程序包5-5
l 只删除程序包主体： DROP PACKAGE BODY pkg_dept;
l 删除整个程序包（规范+主体）： DROP PACKAGE pkg_dept;
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
l WHEN NO_DATA_FOUND THEN
l
RETURN '错误!该编号的部门不存在!';
l END f_dept_getname_byno;
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
l 函数的调用及其输出结果 ：
函数4-3
学习改变命运,知 识创造未来
oracle存储过程函数和程序包