Oracle PL/SQL进阶编程（第一弹：子程序结构）

子程序简介

一般来说，过程和函数被称为子程序。过程是一段不具有返回值的代码块，而函数会返回一个值。子程序与匿名块的最大不同是它可以存储到数据库的数据字典中，以便重用。
过程一旦被创建，就以编译的形式被存储在数据库中，这样就可以从别的PL/SQL命名块或是匿名块中调用了。

子程序的优点

子程序被创建后，就可以在user_object数据字典中查询：

SELECT object_type 对象类型, object_name 对象名称, status 状态 FROM user_objects WHERE object_type IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')ORDER BY object_type, status, object_name;

除了可重用以外，子程序还有如下优点：
- 提供模块化的功能。
- 更强的可维护性。
- 更强的可读性。
- 更强的稳定性。

创建过程

基本语法如下：

[CREATE [OR REPLACEC]]PROCEDURE procedure_name[(parameter[,parameter]...)]    [AUTHID {DEFINER | CURRENT_USER}]{IS | AS}    [PRAGMA AUTONOMOUS_TRANSACTION;][local declarations]BEGIN    executable statements[EXCEPTION     exception handlers]END [name];

• 可选的CREATE语句表示将在数据字典中创建一个独立的过程，可选的OR REPLACE表示创建时将替换现有的过程定义。通常使用OR REPLACE子句，以便在创建之后进行修改时可以直接替换掉原有的过程。
• PROCEDURE表示要创建一个过程。
• parameter用来指定参数。
• AUTHID子句决定了存储过程是按所有者权限（默认）调用还是按当前用户权限执行，也能决定在没有限定修饰词的情况下，对所引用的对象是按所有者模式进行解析还是按当前用户模式进行解析。可以指定CURRENT_USER来覆盖掉程序的默认行为。
• 编译指示AUTONOMOUS_TRANSACTION会告诉PL/SQL编译器把过程标记为自治。自治事务能让我们把主事务挂起，执行SQL操作，提交或回滚自治事务，然后再恢复主事务。
• IS或AS之后的语句称为过程体，local declarations是局部变量定义区，可以定义任意的类型、变量、常量、异常等。在这里定义只具有本地作用域，当过程退出时所有的定义将被释放。
• BEGIN到END之间的语句是标准的PL/SQL语句块。

看一个例子：

CREATE OR REPLACE PROCEDURE newdept (   p_deptno IN  NUMBER,    --部门编号   p_dname  IN  VARCHAR2,     --部门名称   p_loc    IN  VARCHAR2        --位置)AS   v_deptcount     NUMBER(4);           --保存是否存在部门编号   e_duplication_dept EXCEPTION;BEGIN   SELECT COUNT (*) INTO v_deptcount FROM dept    WHERE deptno = p_deptno;       --查询在dept表中是否存在部门编号   IF v_deptcount > 0              --如果存在相同的部门记录   THEN                            --抛出异常      RAISE e_duplication_dept;   END IF;   INSERT INTO dept(deptno, dname, loc)          VALUES (p_deptno, p_dname, p_loc);--插入记录   COMMIT;                          --提交事务EXCEPTION      WHEN e_duplication_dept THEN      ROLLBACK;      raise_application_error (-20002, '出现了相同的员工记录');END;

创建函数

语法如下：

[CREATE [OR REPLACE]]FUNCTION funciton_nme[(parameter[,parameter]...)] RETURN datatype    [AUTHID {DEFINER | CURRENT_USER}]    [PRAGMA AUTONOMOUS_TRANSACTION;][local declarations]BEGIN    executable statements[EXCEPTION     exception handlers]END [name];

函数与过程基本相似，一个很重要的不同点是函数具有RETURN子句，指定函数的返回类型。

看一个例子：

CREATE OR REPLACE FUNCTION getraisedsalary (p_empno emp.empno%TYPE)   RETURN NUMBERIS   v_job           emp.job%TYPE;            --职位变量   v_sal           emp.sal%TYPE;            --薪资变量   v_salaryratio   NUMBER (10, 2);          --调薪比率BEGIN   --获取员工表中的薪资信息   SELECT job, sal INTO v_job, v_sal FROM emp WHERE empno = p_empno;   CASE v_job                               --根据不同的职位获取调薪比率      WHEN '职员' THEN         v_salaryratio := 1.09;      WHEN '销售人员' THEN         v_salaryratio := 1.11;      WHEN '经理' THEN         v_salaryratio := 1.18;      ELSE         v_salaryratio := 1;   END CASE;   IF v_salaryratio <> 1                    --如果有调薪的可能   THEN      RETURN ROUND(v_sal * v_salaryratio,2);         --返回调薪后的薪资   ELSE      RETURN v_sal;                         --否则不返回薪资   END IF;EXCEPTION   WHEN NO_DATA_FOUND THEN      RETURN 0;                             --如果没找到原工记录，返回0END;

RETURN语句

在过程或函数中都可以使用RETURN，但是有明显的区别：
- 过程中RETURN不返回值，它的作用是立即退出过程的执行，将控制权返回给过程的调用者。
- 函数中RETURN必须包含一个表达式，表达式的值会在RETURN语句执行时被计算，然后赋给在声明中的RETURN语句中指定的数据类型的变量，也就是函数标识符，再将控制权返回给调用者。

查看数据字典中的子程序信息

如果想查看已经创建的子程序或Oracle内置的子程序信息，可以通过如下3个视图进行查询：
- user_objects：包含当前登录用户的所有对象的信息，比如对象的名称、创建的时间、最后被修改的时间、对象类型、对象的有效性状态等。
- user_source：包含当前登录用户所拥有的对象的源代码，比如名称、类型、描述等信息。
- user_errors：包含当前登录用户当前所发生的错误信息，比如对象名称、类型、序列、发生错误的位置、文本等。使用这个视图可以轻松地知道当前编译的错误消息，谁用谁知道。

删除子程序

可以使用DROP FUNCTION function_name和DROP PROCEDURE procedure_name来删除函数和过程。要注意，删除者应该是函数或过程的创建者或拥有DROP ANY PROCEDURE系统权限的人。

DROP命令是一个DDL语句，隐式地带有一个COMMIT命令，因此一旦删除，就从数据库中永远移除了。一般我们使用CREATE OR REPLAE来重新编译和修改一个子程序。