ORACLE存储过程实战之三：进阶

1.SELECT INTO语句要求SELECT出来的RESULT至少有一条记录；
 若RS为NULL,则会抛出NO_DATA_FOUND的EXCEPTION；所以需要进行异常捕获；

2.进行UPDATE、DELETE操作时可以用ROWID替换PK可以提高执行效率；
 因为ROWID是直接定位到物理磁盘地址，而无需先从PK Index中查询，从而有效降低IO次数；
 比如UPDATE MD_USER SET NAME = XX WHERE ROWID = ?;
 不过用ROWID时需要小心，因为1)ROWID会被重用；2)在有GROUP BY语句的条件中无法使用；

3.EXCEPTION捕获之后若不需要做任何事情，则可以如下处理：
 BEGIN ... EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END;

4.EXCEPTION能够捕获的常见类别有：
 NO_DATA_FOUND：代码块中某条SELECT INTO语句返回的RS为NULL；
 TOO_MANG_ROWS：SELECT INTO语句符合条件的记录有多条返回；
 DUP_VAL_ON_INDEX：对于数据库表中的某一列，该列已经被限制为唯一索引，程序试图存储两个重复的值；
 VALUE_ERROR：在转换字符类型，截取或长度受限时，会发生该异常，如一个字符分配给一个变量，而该变量声明的长度比该字符短，就会引发该异常；
 STORAGE_ERROR：内存溢出；
 ZERO_DIVIDE：除数为零；
 CASE_NOT_FOUND：对于选择case语句，没有与之相匹配的条件，同时，也没有else语句捕获其他的条件；
 CURSOR_ALREADY_OPEN：程序试图打开一个已经打开的游标；
 TIMEOUT_ON_RESOURCE：系统在等待某一资源，时间超时；
 OTHERS：所有；
 除此之外，还可以用RAISE来抛出一个自定义的EXCEPTION；比如
 DECLARE MY_ERROR EXCEPTION;
 BEGIN
  IF (...) THEN
   RAISE MY_ERROR;
  END IF;
 EXCEPTION WHEN MY_ERROR THEN
  ...
  WHEN OTHERS THEN
  ...
 END;

5.批量操作BULK COLLECT INTO情况下，FORALL下面只能有一条SQL操作，也不能是存过调用；若需要执行多条SQL，则需要用多个FORALL，比如
 ...
 LOOP
  --1000条一个批次
   FETCH CUR_NAME BULK COLLECT INTO VAR_ROWID,V_COL2,... LIMIT 1000;
    FORALL i IN VAR_ROWID.FIRST .. VAR_ROWID.LAST
     INSERT INTO ...;
    FORALL i IN VAR_ROWID.FIRST .. VAR_ROWID.LAST
     UPDATE TABLE_NAME SET ...;
    COMMIT;
   EXIT WHEN CUR_NAME%NOTFOUND;
  END LOOP;

6.重复数据高效清理SQL：
 DELETE FROM TABLE_NAME tn
     WHERE tn.ROWID >
           (SELECT MIN(x.ROWID)
              FROM TABLE_NAME x
             WHERE x.col1 = tn.col1
               AND x.col2 = tn.col2);
  --根据col1、col2两个字段清理，清理后两个字段满足unique约束；

7.一条SQL搞定如下场景：当数据存在进行更新，否则进行新增；
 MERGE INTO TABLE_NAME tn
   USING (SELECT v_pk AS pk FROM DUAL) tmps
   ON (tn.pk = tmps.pk)
   WHEN MATCHED THEN
      UPDATE
         SET col1 = ?, col2 = ? ,...
   WHEN NOT MATCHED THEN
      INSERT (col1, col2, ...)
      VALUES (v_col1, v_col2, ...);
 注意：ON子句中的字段不能在UPDATE子句中进行SET操作；
 
8.CONNECT BY语句使用
 SQL> SELECT rownum rn FROM dual CONNECT BY rownum <= 5;
 
         RN
 ----------
          1
          2
          3
          4
          5
 该语句一般配合笛卡尔积使用，一条语句搞定很多条INSERT语句的事情，可以大大减少SQL代码量；