本节学习目标：
创建和使用子程序
创建和使用程序包
子程序
子程序为命名的 PL/SQL 块，编译并存储在数据库中。
子程序的各个部分：
1,声明部分
2,可执行部分
3,异常处理部分(可选)
子程序的分类：
1,过程－执行某些操作
2,函数－执行操作并返回值
子程序的优点：
模块化-将程序分解为逻辑模块
可重用性-可以被任意数目的程序调用
可维护性-简化维护操作
安全性-通过设置权限，使数据更安全
过程
过程是用于完成特定任务的子程序
创建过程的语法：
--创建过程，可指定运行过程需传递的参数
CREATE [OR REPLACE] PROCEDURE
<procedure name > [(<parameter list >)]
IS|AS
<local variable declaration >
BEGIN
--包括在过程中要执行的语句
<executable statements >
[EXCEPTION
--处理异常
<exception handlers >]
END;
示例：利用过程查询满足条件的雇员信息
CREATE OR REPLACE PROCEDURE
find_emp(emp_no NUMBER)
AS
empname VARCHAR2(20);
BEGIN
SELECT ename INTO empname
FROM EMP WHERE empno = emp_no;
DBMS_OUTPUT.PUT_LINE('雇员姓名是 '|| empname);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('雇员编号未找到');
END find_emp;
/
过程参数的三种模式：IN-用于接受调用程序的值,默认的参数模式
OUT-用于向调用程序返回值
IN OUT-用于接受调用程序的值，并向调用程序返回更新的值
执行过程的语法：
EXECUTE procedure_name(parameters_list);
示例：
SQL > CREATE OR REPLACE PROCEDURE
itemdesc(item_code IN VARCHAR2)
IS
v_itemdesc VARCHAR2(5);
BEGIN
SELECT itemdesc INTO v_itemdesc
FROM itemfile
WHERE itemcode = item_code;
DBMS_OUTPUT.PUT_LINE(item_code||
'项目的说明为'||v_itemdesc);
END;
/
SQL > SET SERVEROUTPUT ON
SQL > EXECUTE itemdesc('i201');
过程示例：
SQL > CREATE OR REPLACE PROCEDURE
test( value1 IN VARCHAR2,
value2 OUT NUMBER )
IS
identity NUMBER;
BEGIN
SELECT ITEMRATE INTO identity
FROM itemFile
WHERE itemcode = value1;
IF identity < 200 THEN
value2:=100;
END IF;
END;
DECLARE
value1 VARCHAR2(5) := 'i202';
value2 NUMBER;
BEGIN
test (value1, value2);
DBMS_OUTPUT.PUT_LINE('value2 的值为'
|| TO_CHAR(value2));
END;
/
CREATE OR REPLACE PROCEDURE
swap(p1 IN OUT NUMBER, p2 IN OUT NUMBER)
IS
v_temp NUMBER;
BEGIN
v_temp := p1;
p1 := p2;
p2 := v_temp;
END;
/
SQL > SET SERVEROUT ON
SQL > DECLARE
num1 NUMBER := 100;
num2 NUMBER := 200;
BEGIN
swap(num1, num2);
DBMS_OUTPUT.PUT_LINE('num1 = ' || num1);
DBMS_OUTPUT.PUT_LINE('num2 = ' || num2);
END;
/
将过程的执行权限授予其他用户：
SQL > GRANT EXECUTE ON find_emp TO MARTIN;
SQL > GRANT EXECUTE ON swap TO PUBLIC;
删除过程：
SQL > DROP PROCEDURE find_emp;
函数
函数是可以返回值的命名的 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;
定义函数的限制：
函数只能接受 IN 参数，而不能接受 IN OUT 或 OUT 参数,形参不能是 PL/SQL 类型,函数的返回类型也必须是数据库类型
访问函数的两种方式：
1,使用 PL/SQL 块
2,使用 SQL 语句
创建函数：
CREATE OR REPLACE FUNCTION fun_hello
RETURN VARCHAR2
IS
BEGIN
RETURN '朋友，您好';
END;
/
从 SQL 语句调用函数：
SQL > SELECT fun_hello FROM DUAL;
示例：利用函数查找满足条件的雇员名字
SQL >declare
name varchar(12);
begin
name:=getname('7902');
dbms_output.put_line(name);
end;
SQL > select getname ('7369') from dual;
SQL > Select * from emp where ename=getname('7369');
将函数的执行权限授予其他用户：
SQL > GRANT EXECUTE ON getname TO John;
删除函数：
SQL > DROP FUNCTION find_emp;
过程和函数的比较：
| 过程 |
函数 |
| 作为 PL/SQL 语句执行 |
作为表达式的一部分调用 |
| 在规格说明中不包含RETURN 子句 |
必须在规格说明中包含RETURN子句 |
| 不返回任何值 |
必须返回单个值 |
| 可以包含 RETURN 语句，但是与函数不同，它不能用于返回值 |
必须包含至少一条 RETURN语句 |
L