子程序和程序包
立此存照
子程序和程序包
子程序：是已命名的PL/SQL块，存储在数据库中。
命名的PL/SQL程序包括存储过程和函数，程序包是存储过程和函数的集合。
子程序具有声明部分、可执行部分和可选的异常处理部分；优点：模块化、可重用性、可维护性、安全性。
过程：是执行某些操作的子程序，是执行特定任务的模块。从根本上说，过程就是命名的PL/SQL程序块，可以被赋予参数，存储在数据库中，由应用程序或其他PL/SQL程序调用。
创建过程的格式如下：
CREATE [OR REPLACE] PROCEDURE PROC_NAME[(PARAMETER_LIST)]
AS ---PROC_NAME 过程名字 PARAMETER_LIST参数列表
[LOCAL_DECARATIONS] ----LOCAL_DECARATIONS是局部声明 置于关键字AS 和 BEGIN之间
BEGIN
EXECUTABLE_STATEMENTS -----EXECUTABLE_STATEMENTS 是可执行语句
[EXCEPTION]
EXCEPTION_HANDLERS ------异常处理语句
END PROC_NAME; ----END过程结束
示例：
-----创建过程 默认IN模式
CREATE PROCEDURE ADD_DEPT(DEPTNO DEPT.DEPTNO%TYPE,DNAME DEPT.DNAME%TYPE,LOC DEPT.LOC%TYPE )
AS
BEGIN
INSERT INTO DEPT VALUES(DEPTNO, DNAME, LOC )
END ADD_DEPT;
---在PL/SQL环境下执行过程
BEGIN
ADD_DEPT(50,’测试部’,’BEIJING’);
END;
------OUT模式
CREATE OR REPLACE PROCEDURE GET_NAME
(IN_EMPNO EMP.EMPNO%TYPE, OU_ENAME OUT EMP.ENAME%TYPE)
AS
BEGIN
SELECT ENAME INTO OU_ENAME FROM EMP WHERE EMPNO = IN_EMPNO;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘雇员编号没有找到’);
END GET_NAME;
---在PL/SQL环境下执行过程
DECLARE
OU_NAME EMP.ENAME%TYPE;
BEGIN
GET_NAME(7902,OU_NAME);
DBMS_OUTPUT.PUT_LINE(OU_NAME);
END;
执行过程：在SQL提示符下：EXECUTE PROC_NAME(PATAMETER_LIST);
在PL/SQL中 BEGIN LOCAL_DECATATIONS() END;
过程参数模式：调用程序通过参数向被调用的过程传递值，模式有3中： IN、 OUT、 IN OUT
语法：PARAMETER_NAME[IN|OUT|IN OUT] DATATYPE
参数IN模式是默认模式，OUT和IN OUT参数必须明确指定。
OUT用于向调用程序返回值
IN OUT用于接受调用程序的值，并向调用程序返回更新的值
将过程的执行权限授权其他用户
GRANT EXTCUTE ON GET_NAME TO ZHANGSAN;
删除过程：
DROP PROCEDURE GET_NAME;
函数：和过程类似，也是数据库中存储的已命名PL/SQL程序块，函数的主要特征是必须返回一个值，创建函数时通过RETURN字句指定函数返回值的数据类型。在函数体的任何地方都可以通过RETURN语句从函数返回。
CREATE [OR REPLACE] FUNCTION FU_NAME[(PARAMETER_LIST)]
RETURN DATATYPE -----定义函数的一些限制
AS ----形式参数必须只能使用数据库类型，不能使用PL/SQL类型
[LOCAL_DECARATIONS] ----函数的返回类型也必须是数据库类型
BEGIN ----必须要有RETURN子句
EXECUTABLE_STATEMENTS
[EXCEPTION]
EXCEPTION_HANDLERS
END FUN_NAME;
访问函数的两种方式：使用 PL/SQL 块 、使用 SQL 语句
示例：
CREATE OR REPLACE function moneyMaker(eid emp.empno%type)
RETURN varchar2
AS
salary emp.sal%type;
BEGIN
SELECT sal INTO salary FROM emp WHERE empno=eid;
if salary>3000 then
return '工资高于3000';
else
return '工资小于等于3000';
end if;
EXCEPTION
WHEN no_data_found THEN
return '没找到数据';
WHEN too_many_rows THEN
return '查询到多条数据';
WHEN others THEN
return '其他错误';
END;
----查询语句
BEGIN
DBMS_OUTPUT.PUT_LINE(moneyMAKER(7902));
END;
从SQL语句调用函数 SQL> SELECT MONEYMAKER(7369) FROM DUAL;
从PL/SQL程序调用函数：
DECLARE
v_desc varchar2(30);
BEGIN
v_desc := moneyMaker(7369) ;
DBMS_OUTPUT.PUT_LINE(v_desc);
END;
自主事务处理：由另一个事务处理（主事务处理）启动的独立事务处理。它可以暂停主事务处理并处理过程内的SQL操作，提交或回滚操作，然后恢复主事务处理。
程序包就是被组合在一起的相关对象的集合，当包中的任何函数或存储过程被调用时，包就被加载到内存中，包中的任何函数或存储过程的子程序的访问速度就大大加快。
程序包有两个部分组成：规范和包体。规范描述变量、常量、游标和子程序；包体完全定义子程序和游标。每个部件都被单独存储在数据字典中。
PRAGMA AUTONOMOUS_TRANSACTION 用于标记子程序为自主事务处理
示例：
CREATE OR REPLACE PROCUDURE TA
AS
a varchar2(50);
BEGIN
select ename into a from emp where empno=7369;
dbmds_output.put_line(a);
rollback;
END;
CREATE OR REPLACE PROCUDURE TB
AS
b varchar2(50);
BEGIN
update emp set ename=‘jack’ where empno=7369;
TA();
select ename into b from emp where empno=7369;
dbmds_output.put_line(b);
END;
CREATE OR REPLACE PROCUDURE T2
AS
a varchar2(50);
Prama autonomous_transcation;
BEGIN
select ename into a from emp where empno=7369;
dbmds_output.put_line(a);
rollback;
END;
程序包规范包含了一些应用程序可见的公共对象和类型的声明，是与应用程序的接口。规范包含应用程序所需的程序包资源，如果程序包规范只声明类型、常量和异常，则不需要程序包主体。
语法：
CREATE [ OR REPLACE] PACKAGE PACKAGE_NAME -----PACKAGE_NAME 包的名称
IS | AS
[PUBLIC TYPE AND ITEM DECLARATIONS] -----PUBLIC TYPE AND ITEM DECLARATIONS 声明类型 常量异常和游标
[SUBPROGRAM SPECIFICATIONS] -------声明PL/SQL程序
END [PACKAGE_NAME];
示例：
CREATE OR REPLACE PACKAGE my_pack
AS
PROCEDURE show_dept_number;
FUNCTION get_avg_sal RETURN number;
END;
程序包主体：包含了程序包规范中声明的每一个游标和子程序的具体实现。私有声明可以包括在程序包主体中。
CREATE [ OR REPLACE] PACKAGE PACKAGE_NAME -----PACKAGE_NAME 包的名称
IS | AS
[PUBLIC TYPE AND ITEM DECLARATIONS] -----PUBLIC TYPE AND ITEM DECLARATIONS 声明类型 常量异常和游标
[SUBPROGRAM SPECIFICATIONS] -------声明PL/SQL程序
[BEGIN
INITIALIZATION_STATEMENTS]
END [PACKAGE_NAME];
示例：
CREATE OR REPLACE PACKAGE BODY my_pack
AS
PROCEDURE show_dept_number
IS
CURSOR dept_cursor
IS
SELECT deptno FROM dept;
BEGIN
FOR item IN dept_cursor
LOOP
DBMS_OUTPUT.PUT_LINE('部门编号：'|| item.deptno);
END LOOP;
END show_dept_number;
FUNCTION get_avg_sal RETURN number
IS
v_avg_number number;
BEGIN
SELECT avg(sal) INTO v_avg_number FROM emp;
RETURN v_avg_number;
END get_avg_sal;
END;/
在PL/SQL中执行程序包中的子程序
DECLARE
v_avg NUMBER;
BEGIN
v_avg:=my_pack.get_avg_sal();
DBMS_OUTPUT.PUT_LINE(v_avg);
END;
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='TEST';
