plsql存储过程、函数

629

-----------------------------plsql概述-------------------------------

过程语言
PLSQL的优势是：SQL语言可以直接写到PLSQL的“块”中或者是PLSQL的过程、函数中。
存储过程、函数、数据库触发器，Package包
PLSQL的另一个显著好处在于它可以通过减少来回交互减轻网络流量压力、节省时间
-------------------------------------------------------------------

------------------------------plsql的块-------------------------------
plsql每一段程序都是由block组成
块结构关键字（DECLARE, BEGIN,EXCEPTION 后面不跟分号；END后面需带分号；）
plsql的块包括三种：匿名块、存储过程、函数
------------------------------------------------------------------------


-------------------------------plsql变量------------------------------
PLSQL的变量类型：
1、系统内置的常规简单变量类型： 比如大多数数据库表的字段类型都可以作为变量类型；
2、用户自定义复杂变量类型： 比如记录类型；
3、引用类型：保存了一个指针值；
4、大对象类型（ LOB）：保存了一个指向大对象的地址；

plsql的变量声明
1、变量命名建议遵循通用规则，比如v_name 表示一个变量，c_name表示一个常量；
2、一般建议每一行声明一个变量，这样程序的可读性比较好；
3、如果声明了变量，但未进行初始化，则在没有赋值之前该变量的值为NULL; 一个好的编程习惯是对所有声明的变量进行初始化赋值。
4、在同一个块中，避免命名与数据库表中的字段名相同的变量；

PLSQL特有的%TYPE属性来声明与XX类型一致的变量类型

DBMS_OUTPUT.PUT_LINE()

PLSQL中的注释语句：
1、多行注释类似于java 或者C ， 使用/* 和*/
2、单行注释是在语句后面使用–

块嵌套和变量范围：
1、PLSQL的块是可以嵌套的，变量的作用范围与其他语言类似
2、使用限定词：《outer》、《inner》
-----------------------------------------------------------------------------

-----------------------------------plsql控制语句------------------------------
1、基本循环
LOOP
statement1;
. . .
EXIT [WHEN condition];
END LOOP;

2、for循环
FOR counter IN [REVERSE]
lower_bound..upper_bound LOOP
statement1;
statement2;
. . .
END LOOP;

3、while循环
WHILE condition LOOP
statement1;
statement2;
. . .
END LOOP;
-----------------------------------------------------------------------------

----------------------------plsql复杂自定义数据类型--------------------------
PLSQL中常用的自定义类型就两种： 记录类型、PLSQL内存表类型
1、记录类型
TYPE type_name IS RECORD
      (field_declaration[, field_declaration]…);

identifier type_name;


2、%ROWTYPE属性：在PLSQL中%ROWTYPE 表示某张表的记录类型或者是用户指定以的记录类型


3、内存表
     1、使用BINARY_INTEGER 类型构成的索引主键；
     2、另外一个简单类型或者用户自定义类型的字段作为具体的数组元素。
      这种类型可以自动增长，所以也类似于可变长数组。
TYPE type_name IS TABLE OF
         {column_type | variable%TYPE
          | table.column%TYPE} [NOT NULL]
          | table.%ROWTYPE
          [INDEX BY BINARY_INTEGER];
--BINARY_INTEGER这种数据类型的值在-2147483647 ... 2147483647范围内
identifier type_name;
----------------------------------------------------------------------------

---------------------------------plsql游标-----------------------------------
1、隐式游标的几个属性
SQL%ROWCOUNT 受最近的SQL语句影响的行数
SQL%FOUND 最近的SQL语句是否影响了一行以上的数据
SQL%NOTFOUND 最近的SQL语句是否未影响任何数据
SQL%ISOPEN 对于隐式游标而言永远为FALSE

2、显式游标：对于返回多行结果的SQL语句的返回结果，可使用显式游标独立的处理器中每一行的数据。
     1、一行一行的处理返回的数据。
     2、保持当前处理行的一个跟踪，像一个指针一样指示当前的处理的记录。
     3、允许程序员在PLSQL块中人为的控制游标的开启、关闭、上下移动；
  过程
  声明游标：declare
  打开游标：open
  提取当前行到变量：fetch
  关闭游标：close
DECLARE
        v_empno employees.employee_id%TYPE;
        v_ename employees.last_name%TYPE;
        CURSOR emp_cursor IS
           SELECT employee_id, last_name
        FROM employees;
BEGIN
     OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO v_empno, v_ename;
     EXIT WHEN emp_cursor%ROWCOUNT > 10 OR
                        emp_cursor%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_empno)
                           ||' '|| v_ename);
      END LOOP;
     CLOSE emp_cursor;
END ;

--for循环

FOR record_name IN cursor_name LOOP
        statement1;
        statement2;
        . . .
END LOOP;

--游标带参数

CURSOR cursor_name
[(parameter_name datatype, ...)]
IS
select_statement;

--FOR UPDATE NOWAIT语句
使用for update nowait语句，倘若锁定失败我们就停止不再继续，以免
出现长时间等待资源的死锁情况。
SELECT ...
FROM ...
FOR UPDATE [OF column_reference][NOWAIT];

--WHERE CURRENT OF cursor
在循环体内做Update 或者Delete时需要有Where指向游标的当前记录
-------------------------------------------------------------------------------

-----------------------------plsql例外处理--------------------------------------
--PLSQL中的例外一般有两种：
1、Oracle 内部错误抛出的例外：这又分为预定义例外（有错误号+常量定义） 和非预定义例外
（仅有错误号，无常量定义）
2、程序员显式的抛出的例外

EXCEPTION
  WHEN exception1 [OR exception2 . . .] THEN
    statement1;
    statement2;
    . . .
  [WHEN OTHERS THEN
   statement1;
   statement2;
   . . .]

--处理预定义的例外

–NO_DATA_FOUND
–TOO_MANY_ROWS
–INVALID_CURSOR
–ZERO_DIVIDE
–DUP_VAL_ON_INDEX

--others的处理
Oracle 提供了两个内置函数SQLCODE 和SQLERRM 分别用来返回Oracle 错误号和错误描述

--处理非预定义的oracle错误
使用PRAGMA EXCEPTION_INIT 把错误号和例外名称联系起来
e_emps_remaining EXCEPTION;
PRAGMA EXCEPTION_INIT(e_emps_remaining, -2292);

--处理用户自定义的错误
DECLARE
e_invalid_department EXCEPTION;
BEGIN
UPDATE departments
SET department_name = &p_department_desc
WHERE department_id = &p_department_number;
IF SQL%NOTFOUND THEN
RAISE e_invalid_department;
END IF;
COMMIT;
EXCEPTION
WHEN e_invalid_department THEN
DBMS_OUTPUT.PUT_LINE('No such department id.');
END;

--RAISE_APPLICATION_ERROR() 函数
对于用户自定义的业务错误，如果觉得先定义再使用很麻烦，那么
也可以简单的使用raise_application_error() 来简化处理

--例外传递
当前块中不处理，传递到外层
-----------------------------------------------------------------------------------------


-------------------------------plsql的存储过程---------------------------------------
1、语法
CREATE [OR REPLACE] PROCEDURE procedure_name
       [(parameter1 [mode1] datatype1,
        parameter2 [mode2] datatype2,
        . . .)]
IS|AS
PL/SQL Block;

2、存储过程的参数模式
   -in 默认
   -out
   -in out

3、参数传递方式
按顺序传递或者使用=>符号传递
add_dept;
add_dept ('TRAINING', 2500);
add_dept ( p_loc => 2400, p_name =>'EDUCATION');

4、删除存储过程
DROP PROCEDURE procedure_name
--------------------------------------------------------------------------------------

------------------------------------plsql函数-----------------------------------------
1、语法
CREATE [OR REPLACE] FUNCTION function_name
        [(parameter1 [mode1] datatype1,
        parameter2 [mode2] datatype2,
        . . .)]
RETURN datatype
IS|AS
PL/SQL Block;

2、哪些sql语句中可以使用用户自定义函数
   -Select 语句
? -Where条件和Having子句
? -CONNECT BY, START WITH, ORDER BY, 和GROUP BY 子句
? -INSERT的Values子句
? -UPDATE的Set子句

▲3、用户自定义函数的限制
   -必须是个函数（不能是过程-Procedure）
? -只能用IN 模式的参数（不能有OUT, IN OUT 模式的参数）
? -只能接收SQL数据类型的参数，不能接收PLSQL 中特有的参数（比如记录、PLSQL内
    存表）
? -函数返回的数据类型也必须是有效的数据类型，而不能是PLSQL特有的数据类型
? -在SQL中使用的函数，其函数体内部不能有DML语句。
? -在UPDATE/DELETE语句中调用的函数，其函数体内部不能有针对同一张表的查询语句
? -在SQL中调用的函数，其函数体内部不能有事务结束语句（比如Commit，Rollback)

4、删除存储函数
DROP FUNCTION function_name

5、函数过程对数据访问的权限
-定义者权限：函数执行时，对表的访问默认使用定义者权限。
-调用者权限：AUTHID CURRENT_USER