ORACLE 游标 异常 存储过程

游标的使用
    在 PL/SQL 程序中，对于处理多行记录的事务经常使用游标来实现。
§4.1 游标概念
  为了处理 SQL 语句，ORACLE 必须分配一片叫上下文( context area )的区域来处理所必需的信息，其中包括要处理的行的数目，一个指向语句被分析以后的表示形式的指针以及查询的活动集(active set)。
  游标是一个指向上下文的句柄( handle)或指针。通过游标，PL/SQL可以控制上下文区和处理语句时上下文区会发生些什么事情。
对于不同的SQL语句，游标的使用情况不同：
SQL语句 游标
非查询语句 隐式的
结果是单行的查询语句 隐式的或显示的
结果是多行的查询语句 显示的

§4.1.1 处理显式游标

1. 显式游标处理
显式游标处理需四个 PL/SQL步骤:
 定义游标：就是定义一个游标名，以及与其相对应的SELECT 语句。
格式：
CURSOR cursor_name[(parameter[, parameter]…)] IS select_statement;
游标参数只能为输入参数，其格式为：
parameter_name [IN] datatype [{:= | DEFAULT} expression]
在指定数据类型时，不能使用长度约束。如NUMBER(4)、CHAR(10) 等都是错误的。
 打开游标：就是执行游标所对应的SELECT 语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。如果游标查询语句中带有FOR UPDATE选项，OPEN 语句还将锁定数据库表中游标结果集合对应的数据行。
格式：
OPEN cursor_name[([parameter =>] value[, [parameter =>] value]…)];
在向游标传递参数时，可以使用与函数参数相同的传值方法，即位置表示法和名称表示     法。PL/SQL 程序不能用OPEN 语句重复打开一个游标。
 提取游标数据：就是检索结果集合中的数据行，放入指定的输出变量中。
格式：
FETCH cursor_name INTO {variable_list | record_variable };
 对该记录进行处理；
 继续处理，直到活动集合中没有记录；
 关闭游标：当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，并使该游标的工作区变成无效，不能再使用FETCH 语句取其中数据。关闭后的游标可以使用OPEN 语句重新打开。
格式：
CLOSE cursor_name;
注：定义的游标不能有INTO 子句。

例1. 游标参数的传递方法。
DECLARE
DeptRec dept%ROWTYPE;
Dept_name dept.dname%TYPE;
Dept_loc dept.loc%TYPE;
CURSOR c1 IS
SELECT dname, loc FROM dept WHERE deptno <= 30;
CURSOR c2(dept_no NUMBER DEFAULT 10) IS
SELECT dname, loc FROM dept WHERE deptno <= dept_no;
CURSOR c3(dept_no NUMBER DEFAULT 10) IS
SELECT * FROM dept WHERE deptno <=dept_no;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO dept_name, dept_loc;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(dept_name||’---‘||dept_loc);
END LOOP;
CLOSE c1;

OPEN c2;
LOOP
FETCH c2 INTO dept_name, dept_loc;
EXIT WHEN c2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(dept_name||’---‘||dept_loc);
END LOOP;
CLOSE c2;

OPEN c3(dept_no =>20);
LOOP
FETCH c3 INTO deptrec;
EXIT WHEN c3%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(deptrec.deptno||’---‘||deptrec.dname
||’---‘||deptrec.loc);
END LOOP;
CLOSE c3;
END;

2.游标属性
%FOUND       布尔型属性，当最近一次读记录时成功返回,则值为TRUE；
%NOTFOUND   布尔型属性，与%FOUND相反；
%ISOPEN       布尔型属性，当游标已打开时返回 TRUE；
%ROWCOUNT   数字型属性，返回已从游标中读取的记录数。

例2：给工资低于1200 的员工增加工资50。
DECLARE
   v_empno  emp.empno%TYPE;
   v_sal      emp.sal%TYPE;
   CURSOR c IS SELECT empno, sal FROM emp;
BEGIN
   OPEN c;
   LOOP
      FETCH c INTO v_empno, v_sal;
      EXIT WHEN C%NOTFOUND;
      IF v_sal<=1200 THEN
            UPDATE emp SET sal=sal+50 WHERE empno=v_empno;
            DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'工资已更新!');
END IF;
DBMS_OUTPUT.PUT_LINE('记录数:'||C%ROWCOUNT);
   END LOOP;
   CLOSE c;
END;

3. 游标的FOR循环
    PL/SQL语言提供了游标FOR循环语句，自动执行游标的OPEN、FETCH、CLOSE语句和循环语句的功能；当进入循环时，游标FOR循环语句自动打开游标，并提取第一行游标数据，当程序处理完当前所提取的数据而进入下一次循环时，游标FOR循环语句自动提取下一行数据供程序处理，当提取完结果集合中的所有数据行后结束循环，并自动关闭游标。
格式：
FOR index_variable IN cursor_name[value[, value]…] LOOP
-- 游标数据处理代码
END LOOP;
其中：
index_variable为游标FOR 循环语句隐含声明的索引变量，该变量为记录变量，其结构与游标查询语句返回的结构集合的结构相同。在程序中可以通过引用该索引记录变量元素来读取所提取的游标数据，index_variable中各元素的名称与游标查询语句选择列表中所制定的列名相同。如果在游标查询语句的选择列表中存在计算列，则必须为这些计算列指定别名后才能通过游标FOR 循环语句中的索引变量来访问这些列数据。
注：不要在程序中对游标进行人工操作；不要在程序中定义用于控制FOR 循环的记录。

例3：
DECLARE
   CURSOR c_sal IS SELECT empno, ename, sal FROM emp ;
BEGIN
--隐含打开游标
   FOR v_sal IN c_sal LOOP
   --隐含执行一个FETCH语句
  DBMS_OUTPUT.PUT_LINE( to_char(v_sal.empno)||’---‘||
v_sal.ename||’---‘||to_char(v_sal.sal)) ;
   --隐含监测c_sal%NOTFOUND
   END LOOP;
--隐含关闭游标
END;

例4：当所声明的游标带有参数时，通过游标FOR 循环语句为游标传递参数。
DECLARE
CURSOR c1(dept_no NUMBER DEFAULT 10) IS
SELECT dname, loc FROM dept WHERE deptno <= dept_no;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘dept_no参数值为30：’);
FOR c1_rec IN c1(30) LOOP
DBMS_OUTPUT.PUT_LINE(c1_rec.dname||’---‘||c1_rec.loc);
END LOOP;

DBMS_OUTPUT.PUT_LINE(CHR(10)||’使用默认的dept_no参数值10：’);
FOR c1_rec IN c1 LOOP
DBMS_OUTPUT.PUT_LINE(c1_rec.dname||’---‘||c1_rec.loc);
END LOOP;
END;

例5：PL/SQL还允许在游标FOR循环语句中使用子查询来实现游标的功能。
BEGIN
FOR c1_rec IN (SELECT dname, loc FROM dept) LOOP
DBMS_OUTPUT.PUT_LINE(c1_rec.dname||’---‘||c1_rec.loc);
END LOOP;
END;

§4.1.2 处理隐式游标
显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；而对于非查询语句，如修改、删除操作，则由ORACLE 系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标，隐式游标的名字为SQL，这是由ORACLE 系统定义的。对于隐式游标的操作，如定义、打开、取值及关闭操作，都由ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条SQL 语句所包含的数据。
格式调用为： SQL%

注：INSERT, UPDATE, DELETE, SELECT 语句中不必明确定义游标。

隐式游标属性
SQL%FOUND       布尔型属性,当最近一次读记录时成功返回，则值为true；
SQL%NOTFOUND   布尔型属性,与%found相反；
SQL %ROWCOUNT  数字型属性, 返回已从游标中读取得记录数；
SQL %ISOPEN    布尔型属性, 取值总是FALSE。SQL命令执行完毕立即关闭隐式游标。

例6: 删除EMP 表中某部门的所有员工，如果该部门中已没有员工，则在DEPT 表中删除该部门。
DECLARE
V_deptno emp.deptno%TYPE :=&p_deptno;
BEGIN
DELETE FROM emp WHERE deptno=v_deptno;
IF SQL%NOTFOUND THEN
DELETE FROM dept WHERE deptno=v_deptno;
END IF;
END;


§4.1.3  游标修改和删除操作
游标修改和删除操作是指在游标定位下，修改或删除表中指定的数据行。这时，要求游标查询语句中必须使用FOR UPDATE选项，以便在打开游标时锁定游标结果集合在表中对应数据行的所有列和部分列。
为了对正在处理(查询)的行不被另外的用户改动，ORACLE 提供一个 FOR UPDATE 子句来对所选择的行进行锁住。该需求迫使ORACLE锁定游标结果集合的行，可以防止其他事务处理更新或删除相同的行，直到您的事务处理提交或回退为止。
语法：
SELECT . . . FROM … FOR UPDATE [OF column[, column]…] [NOWAIT]

    如果另一个会话已对活动集中的行加了锁，那么SELECT FOR UPDATE操作一直等待到其它的会话释放这些锁后才继续自己的操作，对于这种情况，当加上NOWAIT子句时，如果这些行真的被另一个会话锁定，则OPEN立即返回并给出：
ORA-0054 ：resource busy  and  acquire with nowait specified.

    如果使用 FOR UPDATE 声明游标，则可在DELETE和UPDATE 语句中使用WHERE CURRENT OF cursor_name子句，修改或删除游标结果集合当前行对应的数据库表中的数据行。

例7：从EMP表中查询某部门的员工情况，将其工资最低定为 1500；

DECLARE
V_deptno emp.deptno%TYPE :=&p_deptno;
CURSOR emp_cursor IS SELECT empno, sal
FROM emp WHERE deptno=v_deptno FOR UPDATE OF sal NOWAIT;
BEGIN
FOR emp_record IN emp_cursor LOOP
IF emp_record.sal < 1500 THEN
UPDATE emp SET sal=1500 WHERE CURRENT OF emp_cursor;
END IF;
END LOOP;
-- COMMIT;
END;


异常错误处理
    一个优秀的程序都应该能够正确处理各种出错情况，并尽可能从错误中恢复。ORACLE 提供异常情况(EXCEPTION)和异常处理(EXCEPTION HANDLER)来实现错误处理。
§5.1 异常处理概念
异常情况处理(EXCEPTION)是用来处理正常执行过程中未预料的事件,程序块的异常处理预定义的错误和自定义错误,由于PL/SQL程序块一旦产生异常而没有指出如何处理时,程序就会自动终止整个程序运行.
有三种类型的异常错误：
1． 预定义 ( Predefined )错误
ORACLE预定义的异常情况大约有24个。对这种异常情况的处理，无需在程序中定义，由ORACLE自动将其引发。
2． 非预定义 ( Predefined )错误
即其他标准的ORACLE错误。对这种异常情况的处理，需要用户在程序中定义，然后由ORACLE自动将其引发。
3． 用户定义(User_define) 错误
程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义，然后显式地在程序中将其引发。

异常处理部分一般放在 PL/SQL 程序体的后半部,结构为:
EXCEPTION
   WHEN first_exception THEN  <code to handle first exception >
   WHEN second_exception THEN  <code to handle second exception >
   WHEN OTHERS THEN  <code to handle others exception >
END;
异常处理可以按任意次序排列,但 OTHERS 必须放在最后.

§5.1.1 预定义的异常处理
预定义说明的部分 ORACLE 异常错误
错误号 异常错误信息名称 说明
ORA-0001 Dup_val_on_index 试图破坏一个唯一性限制
ORA-0051 Timeout-on-resource 在等待资源时发生超时
ORA-0061 Transaction-backed-out 由于发生死锁事务被撤消
ORA-1001 Invalid-CURSOR 试图使用一个无效的游标
ORA-1012 Not-logged-on 没有连接到ORACLE
ORA-1017 Login-denied 无效的用户名/口令
ORA-1403 No_data_found SELECT INTO没有找到数据
ORA-1422 Too_many_rows SELECT INTO 返回多行
ORA-1476 Zero-divide 试图被零除
ORA-1722 Invalid-NUMBER 转换一个数字失败
ORA-6500 Storage-error 内存不够引发的内部错误
ORA-6501 Program-error 内部错误
ORA-6502 Value-error 转换或截断错误
ORA-6504 Rowtype-mismatch 缩主游标变量与 PL/SQL变量有不兼容行类型
ORA-6511 CURSOR-already-OPEN 试图打开一个已存在的游标
ORA-6530 Access-INTO-null 试图为null 对象的属性赋值
ORA-6531 Collection-is-null 试图将Exists 以外的集合( collection)方法应用于一个null pl/sql 表上或varray上
ORA-6532 Subscript-outside-limit 对嵌套或varray索引得引用超出声明范围以外
ORA-6533 Subscript-beyond-count 对嵌套或varray 索引得引用大于集合中元素的个数.
   
对这种异常情况的处理，只需在PL/SQL块的异常处理部分，直接引用相应的异常情况名，并对其完成相应的异常错误处理即可。

例1：更新指定员工工资，如工资小于1500，则加100；

DECLARE
   v_empno emp.empno%TYPE :=&empno;
   v_sal    emp.sal%TYPE;
BEGIN
   SELECT sal INTO v_sal FROM emp WHERE empno=v_empno;
   IF v_sal<=1500 THEN
        UPDATE emp SET sal=sal+100 WHERE empno=v_empno;
        DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已更新!');    
   ELSE
DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已经超过规定值!');
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND THEN 
      DBMS_OUTPUT.PUT_LINE('数据库中没有编码为'||v_empno||'的员工');
   WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标');
   WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END;

§5.1.2 非预定义的异常处理

对于这类异常情况的处理，首先必须对非定义的ORACLE错误进行定义。步骤如下：
1. 在PL/SQL 块的定义部分定义异常情况：
<异常情况>  EXCEPTION;

2. 将其定义好的异常情况，与标准的ORACLE错误联系起来，使用EXCEPTION_INIT语句：
PRAGMA EXCEPTION_INIT(<异常情况>, <错误代码>)；

3. 在PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。

例2：删除指定部门的记录信息，以确保该部门没有员工。

INSERT INTO dept VALUES(50, ‘FINANCE’, ‘CHICAGO’);

DECLARE
   v_deptno dept.deptno%TYPE :=&deptno;
   e_deptno_remaining EXCEPTION;
   PRAGMA EXCEPTION_INIT(e_deptno_remaining, -2292);
   /* -2292 是违反一致性约束的错误代码 */
BEGIN
   DELETE FROM dept WHERE deptno=v_deptno;
EXCEPTION
   WHEN e_deptno_remaining THEN
      DBMS_OUTPUT.PUT_LINE('违反数据完整性约束!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END;

§5.1.3 用户自定义的异常处理
当与一个异常错误相关的错误出现时，就会隐含触发该异常错误。用户定义的异常错误是通过显式使用 RAISE 语句来触发。当引发一个异常错误时，控制就转向到 EXCEPTION块异常错误部分，执行错误处理代码。

对于这类异常情况的处理，步骤如下：
1． 在PL/SQL 块的定义部分定义异常情况：
<异常情况>  EXCEPTION;

2． RAISE <异常情况>；

3． 在PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。

例3：更新指定员工工资，增加100；

DECLARE
   v_empno emp.empno%TYPE :=&empno;
   no_result  EXCEPTION;
BEGIN
   UPDATE emp SET sal=sal+100 WHERE empno=v_empno;
   IF SQL%NOTFOUND THEN
      RAISE no_result;
   END IF;
EXCEPTION
   WHEN no_result THEN
      DBMS_OUTPUT.PUT_LINE('你的数据更新语句失败了!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END;

§5.1.4  用户定义的异常处理
调用DBMS_STANDARD(ORACLE提供的包)包所定义的RAISE_APPLICATION_ERROR过程，可以重新定义异常错误消息，它为应用程序提供了一种与ORACLE交互的方法。

RAISE_APPLICATION_ERROR 的语法如下：
RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors] ) ;

这里的error_number 是从 –20,000 到 –20,999 之间的参数，
    error_message 是相应的提示信息(< 2048 字节)，
keep_errors 为可选，如果keep_errors =TRUE ,则新错误将被添加到已经引发的错误列表中。如果keep_errors=FALSE(缺省),则新错误将替换当前的错误列表。

例4：创建一个函数get_salary, 该函数检索指定部门的工资总和，其中定义了-20991和-20992号错误，分别处理参数为空和非法部门代码两种错误：

CREATE TABLE errlog(
Errcode NUMBER,
Errtext CHAR(40));

CREATE OR REPLACE FUNCTION get_salary (p_deptno NUMBER)
RETURN NUMBER AS
V_sal NUMBER;
BEGIN
IF p_deptno IS NULL THEN
RAISE_APPLICATION_ERROR(-20991, ’部门代码为空’);
ELSIF p_deptno<0 THEN
RAISE_APPLICATION_ERROR(-20992, ’无效的部门代码’);
ELSE
SELECT SUM(sal) INTO v_sal FROM EMP WHERE deptno=p_deptno;
RETURN V_sal;
END IF;
END;

DECLARE
V_salary NUMBER(7,2);
V_sqlcode NUMBER;
V_sqlerr VARCHAR2(512);
Null_deptno EXCEPTION;
Invalid_deptno EXCEPTION;
PRAGMA EXCEPTION_INIT(null_deptno,-20991);
PRAGMA EXCEPTION_INIT(invalid_deptno, -20992);
BEGIN
V_salary :=get_salary(10);
DBMS_OUTPUT.PUT_LINE(’10号部门工资：’||TO_CHAR(V_salary));

BEGIN
V_salary :=get_salary(-10);
EXCEPTION
WHEN invalid_deptno THEN
V_sqlcode :=SQLCODE;
V_sqlerr :=SQLERRM;
INSERT INTO errlog(errcode, errtext) VALUES(v_sqlcode, v_sqlerr);
COMMIT;
END inner1;

V_salary :=get_salary(20);
DBMS_OUTPUT.PUT_LINE(’20号部门工资：’||TO_CHAR(V_salary));

BEGIN
V_salary :=get_salary(NULL);
END inner2;

V_salary :=get_salary(30);
DBMS_OUTPUT.PUT_LINE(’30号部门工资：’||TO_CHAR(V_salary));

EXCEPTION
WHEN null_deptno THEN
V_sqlcode :=SQLCODE;
V_sqlerr :=SQLERRM;
INSERT INTO errlog(errcode, errtext) VALUES(v_sqlcode, v_sqlerr);
COMMIT;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END outer;

§5.2 异常错误传播
    由于异常错误可以在声明部分和执行部分以及异常错误部分出现，因而在不同部分引发的异常错误也不一样。

§5.2.1 在执行部分引发异常错误
    当一个异常错误在执行部分引发时，有下列情况：
 如果当前块对该异常错误设置了处理，则执行它并成功完成该块的执行，然后控制转给包含块。
 如果没有对当前块异常错误设置定义处理器，则通过在包含块中引发它来传播异常错误。然后对该包含块执行步骤1)。

§5.2.2 在声明部分引发异常错误
    如果在声明部分引起异常情况，即在声明部分出现错误，那么该错误就能影响到其它的块。比如在有如下的PL/SQL程序：

DECLARE
Abc number(3):=’abc’;
其它语句
BEGIN
其它语句
EXCEPTION
WHEN OTHERS THEN
其它语句
END;

例子中，由于Abc number(3)=’abc’; 出错，尽管在EXCEPTION中说明了WHEN OTHERS THEN语句，但WHEN OTHERS THEN也不会被执行。 但是如果在该错误语句块的外部有一个异常错误，则该错误能被抓住，如：

BEGIN
DECLARE
Abc number(3):=’abc’;
其它语句
   BEGIN
其它语句
   EXCEPTION
WHEN OTHERS THEN
其它语句
END;
EXCEPTION
WHEN OTHERS THEN
其它语句
END;
§5.3 异常错误处理编程
    在一般的应用处理中，建议程序人员要用异常处理，因为如果程序中不声明任何异常处理，则在程序运行出错时，程序就被终止，并且也不提示任何信息。下面是使用系统提供的异常来编程的例子。
§5.4  在 PL/SQL 中使用 SQLCODE, SQLERRM
    由于ORACLE 的错信息最大长度是512字节，为了得到完整的错误提示信息，我们可用 SQLERRM和 SUBSTR 函数一起得到错误提示信息。

SQLCODE 返回错误代码数字,
SQLERRM 返回错误信息.

如:  SQLCODE=+100   SQLERRM=’no_data_found ‘
    SQLCODE=0       SQLERRM=’normal, successfual completion’
例5. 将ORACLE错误代码及其信息存入错误代码表
CREATE TABLE errors (errnum NUMBER(4), errmsg VARCHAR2(100));

DECLARE
   err_msg  VARCHAR2(100);
BEGIN
   /*  得到所有 ORACLE 错误信息  */
   FOR err_num IN -100 .. 0 LOOP
      err_msg := SQLERRM(err_num);
      INSERT INTO errors VALUES(err_num, err_msg);
   END LOOP;
END;

DROP TABLE errors;

例6. 查询ORACLE错误代码；
BEGIN
   INSERT INTO emp(empno, ename, hiredate, deptno)
   VALUES(2222, ‘Jerry’, SYSDATE, 20);
   DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');
   INSERT INTO emp(empno, ename, hiredate, deptno)
   VALUES(2222, ‘Jerry’, SYSDATE, 20);
   DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||’---‘||SQLERRM);
END;



存储函数和过程
§6.1  引言
ORACLE 提供可以把PL/SQL 程序存储在数据库中，并可以在任何地方来运行它。这样就叫存储过程或函数。过程和函数统称为PL/SQL子程序，他们是被命名的PL/SQL块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。在本节中，主要介绍：
1． 创建存储过程和函数。
2． 正确使用系统级的异常处理和用户定义的异常处理。
3． 建立和管理存储过程和函数。
§6.2  创建函数
1. 建立内嵌函数
语法如下：
CREATE [OR REPLACE] FUNCTION function_name
[(argment [ { IN| IN OUT }] type,
    argment [ { IN | OUT | IN OUT } ] type]
RETURN return_type
{ IS | AS }
<类型.变量的说明>
BEGIN
FUNCTION_body
EXCEPTION
其它语句
END;


例1. 获取某部门的工资总和：

CREATE OR REPLACE FUNCTION get_salary(
Dept_no NUMBER,
Emp_count OUT NUMBER)
RETURN NUMBER IS
V_sum NUMBER;
BEGIN
SELECT SUM(sal), count(*) INTO V_sum, emp_count
FROM emp WHERE deptno=dept_no;
RETURN v_sum;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');
   WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END get_salary;

2. 内嵌函数的调用
函数声明时所定义的参数称为形式参数，应用程序调用时为函数传递的参数称为实际参数。应用程序在调用函数时，可以使用以下三种方法向函数传递参数：

第一种参数传递格式称为位置表示法，格式为：
argument_value1[,argument_value2 …]

例2：计算某部门的工资总和：

DECLARE
V_num NUMBER;
V_sum NUMBER;
BEGIN
V_sum :=get_salary(30, v_num);
DBMS_OUTPUT.PUT_LINE(’30号部门工资总和：’||v_sum||’，人数：’||v_num);
END;

第二种参数传递格式称为名称表示法，格式为：
argument => parameter [,…]
其中：argument 为形式参数，它必须与函数定义时所声明的形式参数名称相同。Parameter 为实际参数。
在这种格式中，形势参数与实际参数成对出现，相互间关系唯一确定，所以参数的顺序可以任意排列。
例3：计算某部门的工资总和：

DECLARE
V_num NUMBER;
V_sum NUMBER;
BEGIN
V_sum :=get_salary(emp_count => v_num, dept_no => 30);
DBMS_OUTPUT.PUT_LINE(’30号部门工资总和：’||v_sum||’，人数：’||v_num);
END;

第三种参数传递格式称为混合表示法：
即在调用一个函数时，同时使用位置表示法和名称表示法为函数传递参数。采用这种参数传递方法时，使用位置表示法所传递的参数必须放在名称表示法所传递的参数前面。也就是说，无论函数具有多少个参数，只要其中有一个参数使用名称表示法，其后所有的参数都必须使用名称表示法。

例4：
CREATE OR REPLACE FUNCTION demo_fun(
Name VARCHAR2,
Age INTEGER,
Sex VARCHAR2)
RETURN VARCHAR2
AS
V_var VARCHAR2(32);
BEGIN
V_var := name||’：‘||TO_CHAR(age)||’岁，’||sex;
RETURN v_var;
END;

DECLARE
Var VARCHAR(32);
BEGIN
Var := demo_fun(‘user1’, 30, sex => ‘男’);
DBMS_OUTPUT.PUT_LINE(var);

Var := demo_fun(‘user2’, age => 40, sex => ‘男’);
DBMS_OUTPUT.PUT_LINE(var);

Var := demo_fun(‘user3’, sex => ‘女’, age => 20);
DBMS_OUTPUT.PUT_LINE(var);
END;

无论采用哪一种参数传递方法，实际参数和形式参数之间的数据传递只有两种方法：传址法和传值法。所谓传址法是指在调用函数时，将实际参数的地址指针传递给形式参数，使形式参数和实际参数指向内存中的同一区域，从而实现参数数据的传递。这种方法又称作参照法，即形式参数参照实际参数数据。输入参数均采用传址法传递数据。
传值法是指将实际参数的数据拷贝到形式参数，而不是传递实际参数的地址。默认时，输出参数和输入/输出参数均采用传值法。在函数调用时，ORACLE将实际参数数据拷贝到输入/输出参数，而当函数正常运行退出时，又将输出形式参数和输入/输出形式参数数据拷贝到实际参数变量中。

3. 参数默认值
在CREATE OR REPLACE FUNCTION 语句中声明函数参数时可以使用DEFAULT关键字为输入参数指定默认值。

例5：
CREATE OR REPLACE FUNCTION demo_fun(
Name VARCHAR2,
Age INTEGER,
Sex VARCHAR2 DEFAULT ‘男’)
RETURN VARCHAR2
AS
V_var VARCHAR2(32);
BEGIN
V_var := name||’：‘||TO_CHAR(age)||’岁，’||sex;
RETURN v_var;
END;

具有默认值的函数创建后，在函数调用时，如果没有为具有默认值的参数提供实际参数值，函数将使用该参数的默认值。但当调用者为默认参数提供实际参数时，函数将使用实际参数值。在创建函数时，只能为输入参数设置默认值，而不能为输入/输出参数设置默认值。
DECLARE
Var VARCHAR(32);
BEGIN
Var := demo_fun(‘user1’, 30);
DBMS_OUTPUT.PUT_LINE(var);

Var := demo_fun(‘user2’, age => 40);
DBMS_OUTPUT.PUT_LINE(var);

Var := demo_fun(‘user3’, sex => ‘女’, age => 20);
DBMS_OUTPUT.PUT_LINE(var);
END;
§6.3  存储过程
§6.3.1  创建过程

建立存储过程
    在 ORACLE SERVER上建立存储过程,可以被多个应用程序调用,可以向存储过程传递参数,也可以向存储过程传回参数.

创建过程语法:
CREATE [OR REPLACE] PROCEDURE Procedure_name
[ (argment [ { IN | IN OUT }] Type,
      argment [ { IN | OUT | IN OUT } ] Type ]
{ IS | AS }
<类型.变量的说明>
BEGIN
<执行部分>
EXCEPTION
<可选的异常错误处理程序>
END;

例6．用户连接登记记录；

CREATE table logtable (userid VARCHAR2(10), logdate date);

CREATE OR REPLACE PROCEDURE logexecution IS
BEGIN
   INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE);
END;

例7．删除指定员工记录；

CREATE OR REPLACE PROCEDURE DelEmp(v_empno IN emp.empno%TYPE) AS
No_result EXCEPTION;
BEGIN
   DELETE FROM emp WHERE empno=v_empno;
   IF SQL%NOTFOUND THEN
      RAISE no_result;
   END IF;
   DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'的员工已被除名!');
EXCEPTION
   WHEN no_result THEN
      DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END DelEmp;


§6.3.2  调用存储过程

    存储过程建立完成后，只要通过授权，用户就可以在SQLPLUS 、ORACLE开发工具或第三方开发工具中来调用运行。ORACLE 使用EXECUTE 语句来实现对存储过程的调用：
EXEC[UTE]  Procedure_name( parameter1, parameter2…);

例8：
EXECUTE logexecution;


例9．计算指定部门的工资总和，并统计其中的职工数量。

CREATE OR REPLACE PROCEDURE proc_demo(
Dept_no NUMBER DEFAULT 10,
Sal_sum OUT NUMBER,
Emp_count OUT NUMBER)
IS
BEGIN
SELECT SUM(sal), COUNT(*) INTO sal_sum, emp_count
FROM emp WHERE deptno=dept_no;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END proc_demo;

调用方法:
DECLARE
V_num NUMBER;
V_sum NUMBER(8, 2);
BEGIN
Proc_demo(30, v_sum, v_num);
DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||’，人数：’||v_num);
Proc_demo(sal_sum => v_sum, emp_count => v_num);
DBMS_OUTPUT.PUT_LINE('10号部门工资总和：'||v_sum||’，人数：’||v_num);
END;

在PL/SQL 程序中还可以在块内建立本地函数和过程，这些函数和过程不存储在数据库中，但可以在创建它们的PL/SQL 程序中被重复调用。本地函数和过程在PL/SQL 块的声明部分定义，它们的语法格式与存储函数和过程相同，但不能使用CREATE OR REPLACE 关键字。

例10：建立本地过程，用于计算指定部门的工资总和，并统计其中的职工数量；

DECLARE
V_num NUMBER;
V_sum NUMBER(8, 2);
PROCEDURE proc_demo(
Dept_no NUMBER DEFAULT 10,
Sal_sum OUT NUMBER,
Emp_count OUT NUMBER)
IS
BEGIN
SELECT SUM(sal), COUNT(*) INTO sal_sum, emp_count
FROM emp WHERE deptno=dept_no;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END proc_demo;
BEGIN
Proc_demo(30, v_sum, v_num);
DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||’，人数：’||v_num);
Proc_demo(sal_sum => v_sum, emp_count => v_num);
DBMS_OUTPUT.PUT_LINE('10号部门工资总和：'||v_sum||’，人数：’||v_num);
END;

§6.3.3  开发存储过程步骤
    开发存储过程、函数、包及触发器的步骤如下：

§6.3.3.1  使用文字编辑处理软件编辑存储过程源码
    使用文字编辑处理软件编辑存储过程源码，要用类似WORD 文字处理软件进行编辑时，要将源码存为文本格式。

§6.3.3.2  在SQLPLUS或用调试工具将存储过程程序进行解释
    在SQLPLUS或用调试工具将存储过程程序进行解释；
    在SQL>下调试，可用START 或GET 等ORACLE命令来启动解释。如：
SQL>START c:\stat1.sql
    如果使用调式工具，可直接编辑和点击相应的按钮即可生成存储过程。

§6.3.3.3  调试源码直到正确
    我们不能保证所写的存储过程达到一次就正确。所以这里的调式是每个程序员必须进行的工作之一。在SQLPLUS下来调式主要用的方法是：
 使用 SHOW ERROR命令来提示源码的错误位置；
 使用 user_errors 数据字典来查看各存储过程的错误位置。

§6.3.3.4  授权执行权给相关的用户或角色
如果调式正确的存储过程没有进行授权，那就只有建立者本人才可以运行。所以作为应用系统的一部分的存储过程也必须进行授权才能达到要求。在SQL*PLUS下可以用GRANT命令来进行存储过程的运行授权。

GRANT语法：
GRANT system_privilege | role
TO user | role | PUBLIC [WITH ADMIN OPTION]

GRANT object_privilege | ALL ON schema.object
TO user | role | PUBLIC [WITH GRANT OPTION]

例子：

CREATE OR REPLACE PUBLIC SYNONYM dbms_job FOR dbms_job

GRANT EXECUTE ON dbms_job TO PUBLIC WITH GRANT OPTION

§6.3.4  与过程相关数据字典

USER_SOURCE, ALL_SOURCE, DBA_SOURCE, USER_ERRORS

相关的权限:
CREATE ANY PROCEDURE
DROP ANY PROCEDURE

在SQL*PLUS 中，可以用DESCRIBE 命令查看过程的名字及其参数表。

DESCRIBE Procedure_name;

DROP PROCEDURE logexecution;
DROP PROCEDURE delemp;
DROP PROCEDURE proc_demo;
DROP FUNCTION demo_fun;
DROP FUNCTION get_salary;