OCP复习 - PLSQL(18-25) - 存储过程/函数/包

一、PL/SQL程序的类型
PL/SQL程序有下面5种类型：
1. 匿名PLSQL块
2. 存储过程/函数
3. 包 //相关联存储过程的组合
4. 触发器
5. 应用程序过程/函数 //Developer2000组件
6. 应用程序触发器 //Developer2000组件

 

二、PL/SQL匿名块和存储过程/函数/包的结构

匿名PLSQL块结构是：
DECLARE //可选，声明变量
BEGIN
    EXCEPTION //可选，异常处理
END

存储过程结构是：
CREATE OR REPLACE PROCEDURE procedure_name 
  ( para1 IN NUMBER,
  para2 OUT NUMBER,
  para3 IN OUT NUMBER )
IS
BEGIN
 ...
EXCEPTION
 ...
END procedure_name;

函数的结构是：
CREATE OR REPLACE FUNCTION function_name
 ( para1 IN NUMBER,
para2 IN NUMBER )
RETURN datatype
IS
BEGIN
 ...
   EXCEPTION
 ...
END function_name;

package就是用来封装过程，函数用的，一个package可以存放多个过程，函数。调用方法是：
包名.过程名1
包名.过程名2
如：execute package_name.procedure_name;

三、PL/SQL程序的标量变量
PL/SQL程序的标量变量分为：
1. 基本类型变量
包括以下类型：
BINARY_INTEGER: 整型值
NUMBER(p,s)
CHAR
LONG
LONG RAW
VARCHAR2
DATE
BOOLEAN

变量的定义方法有两种：
1. v_gender CHAR(1) := 'M';
2. 基于已经存在的数据库表列数据类型、或已经存在的变量数据类型，使用%TYPE来定义。
v_tmp_gender v_gender%TYPE := 'M';
v_employee_id tbl_employee.employee_id%TYPE := 0;

变量的赋值语法：
v_max_sal := v_sal;

2. 复合类型变量（TABLE和RECORD）
复合类型变量包括TABLE和RECORD。
TABLE类似于一个一维数组，必须包含一个标量成员和一个整型索引。作为一种自定义数据类型，它的定义语法是：
TYPE type_name IS TABLE OF scalar_datatype [NOT NULL]
 INDEX BY BINARY_INTEGER;

定义一个TABLE类型变量的语法：
v_my_table type_name;

如：
TYPE name_table_type IS TABLE OF VARCHAR2(25)
INDEX BY BINARY_INTEGER;
first_name_table name_table_type;
last_name_table name_table_type;

RECORD类似于一个数据库记录，包含多个标量成员。作为一种自定义数据类型，它的定义语法是：
TYPE type_name IS RECORD
 (field1 scalar_type,
  field2 scalar_type,
 ...);

定义一个RECORD类型变量的语法：
v_my_rec type_name;

类似于%TYPE，也可以通过%ROWTYPE引用一个数据库表结构来定义一个RECORD类型变量。
v_rec tbl_employee_info%ROWTYPE;


三、PL/SQL程序中的SELECT/UPDATE/INSERT/DELETE SQL语法和隐式游标

使用SELECT INTO语法从数据库中提取一行记录：
SELECT select_list
INTO variable_name | record_name
FROM table
WHERE condition;

这个语句可能会抛出NO_DATA_FOUND或TOO_MANY_ROWS异常。

UPDATE/INSERT/DELETE SQL语法和普通SQLPLUS中的SQL语句一样。在这些语句中，使用了ORACLE的隐式游标。隐式游标有几个属性：
SQL%ROWCOUNT; //整型，SQL语句影响的行数。
SQL%FOUND; //布尔，TRUE表示SQL语句影响到了实际的记录。
SQL%NOTFOUND;
SQL%ISOPEN; //永远为FALSE，因为隐式游标自动关闭。

四、程序流控制语法

1. GOTO可以设置标签，然后用GOTO语句转跳到标签处。
<>
...
GOTO label_a;
...
2. 条件判断
IF x=0 THEN
  y := 1;
ELSEIF condition THEN
  ...;
ELSE
  ...;
END IF;
3. 三种循环（LOOP/WHILE/FOR）
LOOP
  ...
     EXIT [WHEN condition];
END LOOP;

FOR variable IN [REVERSE] lower_bound..upper_bound LOOP
 ...
 ...
END LOOP;

WHILE condition LOOP
   ...
...
END LOOP;

使用LABEL支持嵌套循环：
BEGIN
<>LOOP
v_counter :=v_counter+1;
EXIT WHEN v_counter>10;
<>LOOP
...
EXIT Outer_loop WHEN total_done = ’YES’;
–– Leave both loops
EXIT WHEN inner_done = ’YES’;
–– Leave inner loop only
...
END LOOP Inner_Loop;
...
END LOOP Outer_loop;
END;

五、显式游标
使用显式游标的4个步骤：DECLARE - OPEN - FETCH - CLOSE
DECLARE
 CURSOR cursor_name IS
 SELECT .....;
OPEN cursor_name;
FETCH cursor_name INTO scalar_variable1, scalar_variable2, ...;
CLOSE cursor_name;

判断游标执行有效的属性同隐式游标，包括：
cursor_name%ROWCOUNT
cursor_name%FOUND
cursor_name%NOTFOUND
cursor_name%ISOPEN

六、异常处理方法
在PL/SQL程序块中的EXCEPTION块中处理异常：
WHEN exception1 THEN
 ...;
 ...;
WHEN exception2 OR exception3 THEN
 ...;
WHEN OTHERS THEN
 ...;

常用异常：
NO_DATA_FOUND
TOO_MANY_ROWS
INVALID_CURSOR
ZERO_DIVIDE
DUP_VAL_ON_INDEX

异常的两个常用属性是SQLCODE和SQLERRM，分别表示异常的ID和错误信息文本。也可以自定义一个异常，然后在需要的时候抛出并处理。
例子：
DECLARE
 my_exception EXCEPTION; //定义一个异常
BEGIN
 ...
 IF condition THEN
 RAISE my_exception; //抛出一个异常
 END IF;
 ...
 EXCEPTION
 WHEN my_exception THEN //处理一个自定义异常
 ...;
 WHEN OTHERS THEN
 ...;
END