ORACLE存储过程实战 入门、进阶（转）

入门篇
1.基本结构
CREATE OR REPLACE PROCEDURE 存储过程名字
(
    参数1 IN NUMBER,
    参数2 IN NUMBER,
    参数3 OUT NUMBER
) IS
变量1 INTEGER := 默认值;
变量2 DATE;
BEGIN
...
EXCEPTION
  WHEN 异常类别 THEN
      ...
  WHEN OTHERS THEN
    ...
END 存储过程名字;

2.基本操作
变量赋值 V_TEST := 1;
动态赋值 SELECT COL1,COL2,... INTO VAR1,VAR2,... FROM ... WHERE ...;
字符相加  'STRING1' || 'STRING2';
相等判断 =而不是==;
逻辑判断 AND,OR,>,<,<>;
打印输出 dbms_output.put_line();
是否为空 V_TEST IS (NOT) NULL;
提交回滚 COMMIT/ROLLBACK;
异常捕捉 BEGIN ... EXCEPTION WHEN ... THEN ... END;

3.IF 判断
  IF (判断条件) THEN
    BEGIN
       ...
    END;
  ELSIF THEN
   ...
  ELSE
   ...
  END IF;

4.WHILE 循环
  WHILE (判断条件) LOOP
   BEGIN
   ...
   END;
  END LOOP;

5.FOR 循环
FOR X IN (SELECT col1,col2 ...) LOOP
  BEGIN
   引用X.col1、X.col2
   ...
  END;
END LOOP;

6.常见FUNCTION
以下列出的是一些常用函数的普通用法，并不是按照ORACLE函数标准进行说明的，所以有些用法并不全面，只是列举了最常用的情况；
SYSDATE：用来得到系统的当前日期；
TO_NUMBER(STR)：将字符串转换成数字；若转换不成功，则抛错：ORA-01722: invalid number；
TO_CHAR(?)：?可支持很多的类型，比如NUM，DATE……；DATE用得比较多，一般来说有如下用法：
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')：将当前日期按照格式输出完整字符串；其中的日期格式可以自行定义；
TO_DATE(STR,FORMAT)：对字符串按照一定格式解析成Date型；
NVL(col,defaultValue)：对col判断是否为NULL,若为NULL，则返回defaultValue；
CONCAT(STR1,STR2)：连接两个字符串，功能与||相同；
LENGTH(STR)：返回字符串的长度，其中中文字符的长度计算要视DB的编码而定，在GBK编码下，中文也算1个字符；
  若需要中文字符长度算2个字符，则可以用LENGTHB(STR)代替；
  简单来说，LENGTH返回字符长度；LENGTHB返回字节长度；
SUBSTR(str,start,count)：截取子字符串,从start开始,取count个；
  start为0和1的效果是一样的，都是从str的第一个字符开始截取；
  若start>=LENGTH(str)，直接返回空，不会抛错；
INSTR(STR1,STR2,I,J)：在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
  STR1:被搜索的字符串
  STR2:希望搜索的字符串
  I:搜索的开始位置,默认为1
  J:第几次出现的位置,默认为1
LOWER/UPPER(STR)：将字符串全部转成小写或者大写；在GBK编码下，该函数对中文无效；
LTRIM/RTRIM(STR)：删除左边货右边出现的空格字符串；
FLOOR(NUMBER)：对给定的数字取整数；
MOD(num1,num2)：返回num1除以num2的余数；num1和num2可以带有小数位；返回的值也可能有小数位；
ROUND(num)：对num四舍五入取值；
TRUNC(num)：对num截取整数，去掉小数位，不进行四舍五入；功能与FLOOR类似；当然也可以在参数中指定精度；
TRUNC(Date)：对date截取到天，去掉小时分钟之类的，其返回类型仍然为Date；类似的，也可以在参数中指定Date截取级别，比如'hh'；
ADD_MONTHS(Date,num)：对Date加上num个月，num可以为负数，表示减去几个月；
  num可以有小数位，但是并不会起效，效果相当于ADD_MONTHS(Date,TRUNC(num))；
CHARTOROWID/ROWIDTOCHAR：将字符数据类型转换为ROWID类型或者相反；
AVG/MIN/MAX/SUM/COUNT(DISTINCT|ALL col)：对某字段取平均、最小、最大、求和、计数；默认为ALL，若需要对不重复值运算，则用DISTINCT参数；
CASE WHEN ... THEN ...
    WHEN ... THEN ...
    ELSE ...
END：根据不同条件取不同的值，与JAVA中的CASE也很类似；

游标篇

1.CURSOR 申明
无参数申明：CURSOR CUR_NAME IS SELECT COL1,COL2,... FROM ... WHERE ...;
带参数申明：CURSOR CUR_NAME(PARAM1 DATA_TYPE,PARAM2 DATA_TYPE,...) IS SELECT COL1,COL2,... FROM ... WHERE ...;

2.用FOR IN使用CURSOR
  FOR CUR_RS IN CUR_NAME LOOP
   BEGIN
    引用CUR_RS.COL1,CUR_RS.COL2,...
    ...
   END;
END LOOP;

3.用FETCH INTO使用CURSOR
  LOOP
   FETCH CUR_NAME INTO V_COL1,V_COL2,...;
   ...
   EXIT WHEN CUR_NAME%NOTFOUND;
  END LOOP;
 
4.用BULK COLLECT使用CURSOR，用于批量操作，提高效率
--申明TABLE数据类别
TYPE TYPE_NAME IS TABLE OF DATA_TYPE;
比如TYPE T_ROWID IS TABLE OF UROWID;

--申明变量
VAR_NAME TYPE_NAME;
比如VAR_ROWID T_ROWID;

LOOP
  --1000条一个批次
   FETCH CUR_NAME BULK COLLECT INTO VAR_ROWID,V_COL2,... LIMIT 1000;
    FORALL i IN VAR_ROWID.FIRST .. VAR_ROWID.LAST
     ...ONE SQL;
    COMMIT;
   EXIT WHEN CUR_NAME%NOTFOUND;
  END LOOP;

5.EXIT WHEN CUR_NAME%NOTFOUND 位置
在配合FETCH INTO语句使用时，EXIT WHEN CUR_NAME%NOTFOUND可以放在循环的开始处，也可以放在最后面；视具体场景而定；
WHEN CUR_NAME%NOTFOUND也可以用其他的判断条件替换；
比如在用BULK COLLECT时，就可以用EXIT WHEN VAR_ROWID.COUNT = 0;不过此时需要放在FETCH之后FORALL之前；

进阶篇

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代码量；

LOCK篇

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

在数据库中有两种基本的锁类型：排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。

根据保护的对象不同，Oracle数据库锁可以分为以下几大类：
DML锁（data locks，数据锁），用于保护数据的完整性；
DDL锁（dictionary locks，字典锁），用于保护数据库对象的结构，如表、索引等的结构定义；
内部锁和闩（internal locks and latches），保护数据库的内部结构，应用于SGA；

在我们实际应用开发中涉及较多的是DML锁，其他两种的话DBA会更加关心点；
DML锁的目的在于保证并发情况下的数据完整性，主要包括TM锁和TX锁，其中TM锁称为表级锁，TX锁称为事务锁或行级锁。

当Oracle执行DML语句时，系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后，系统再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志，而只需检查TM锁模式的相容性即可，大大提高了系统的效率。TM锁包括了SS、SX、S、X等多种模式，在数据库中用0－6来表示。不同的SQL操作产生不同类型的TM锁。如下图所示：  值 锁模式 锁描述 SQL
0 NONE    
1 NULL 空 SELECT
2 SS(ROW-S) 行级共享锁
其他对象只能查询这些数据行 SELECT FOR UPDATE、LOCK FOR UPDATE、
LOCK ROW SHARE
3 SX(ROW-X) 行级排它锁
在提交前不允许做DML操作 INSERT、UPDATE、DELETE、
LOCK ROW SHARE
4 S(SHARE) 共享锁 CREATE INDEX、LOCK SHARE
5 SSX(S/ROW-X) 共享行级排它锁 LOCK SHARE ROW EXCLUSIVE
6 X(eXclusive) 排它锁 ALTER TABLE、DROP TABLE、DROP INDEX、
TRUNCATE TABLE、LOCK EXCLUSIVE


在数据行上只有X锁（排他锁）。在 Oracle数据库中，当一个事务首次发起一个DML语句时就获得一个TX锁，该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行DML语句时，第一个会话在该条记录上加锁，其他的会话处于等待状态。当第一个会话提交后，TX锁被释放，其他会话才可以加锁。

在大概了解oracle的锁机制之后，我们来解决几个基本的问题：
1.UPDATE/DELETE操作会将RS锁定，直至操作被COMMIT或者ROLLBACK；
若操作未COMMIT之前其他session对同样的RS做变更操作，则操作会被hold，直至前session的UPDATE/DELETE操作被COMMIT；

2.session内外SELECT的RS范围
前提：INSERT、UPDATE操作未COMMIT之前进行SELECT；
若在同一session内，SELECT出来的RS会包括之前INSERT、UPDATE影响的记录；
若不在同一session内，SELECT出来的RS不会包括未被COMMIT的记录；

3.SELECT.... FOR UPDATE [OF cols] [NOWAIT/WAIT] [SKIP LOCKED]
OF cols：只锁定指定字段所在表的RS，而没有指定的表则不会锁定，只会在多表联合查询时出现；
NOWAIT：语句不会hold，而是直接返回错误ORA-00054: resource busy and acquire with NOWAIT specified；
WAIT N：语句被hold N秒，之后返回错误ORA-30006: resource busy; acquire with WAIT timeout expired；
SKIP LOCKED：不提示错误，而是直接返回no rows selected；
以上几个选项可以联合使用的，比较推荐的有：
SELECT.... FOR UPDATE NOWAIT：对同一RS执行该SQL时，直接返回错误；
SELECT.... FOR UPDATE NOWAIT SKIP LOCKED：对同一RS执行该SQL时，直接返回空行；
PS：当RS被LOCK住之后，只对同样请求LOCK的语句有效，对无需LOCK的SELECT语句并没有任何影响；

hint篇

hint中我们最常用到的有ordered、use_nl、use_hash、index、full这五种；

下面就介绍下这5种hint适用的主要场合：

1）use_nl主要用于在多表join的时候，nl的意思是nest loop，就是嵌套查询；

         规则为，根据参数中指定表名的顺序，根据where子句中的查询条件把所有记录都查出来，然后再循环这些记录与另外的表join；

         适应场合为：某表的查询结果集很小，而其他表的查询结果集很大；

         比如：select a.* from table1 a,table2 b where a.xx='1' and b.yy = '2' and a.x=b.x

         若根据a.xx='1'从table1中查询出来的结果集比根据b.yy = '2'从table2中查询出来的结果集小得多，则可以加上use_nl(a b)；

         使用的时候将小结果集放前面，大结果集放后面；

2）use_hash可以看作是use_nl的兄弟，也是用于在多表join的时候，但是他不使用嵌套，而是用hash join；

         其规则和use_nl完全一样；

         适应场合为：各表的查询结果集都比较大，而且大小差不多；

         比如：select a.* from table1 a,table2 b where a.xx='1' and b.yy = '2' and a.x=b.x

         若根据a.xx='1'从table1中查询出来的结果集和根据b.yy = '2'从table2中查询出来的结果集都比较大，而且大小差不多，则可以加上use_hash(a b)；

3）ordered用于强制指定DB按照FROM子句中各表的先后顺序进行数据查询；

         比如说SELECT A.* FROM TABLE1 A, TABLE2 B WHERE A.X=B.X ……中添加了/*+ORDERED */，则标识oracle会强制先查询table1中的记录，然后再与table2 join；

         一般来说这个hint都是与use_nl和use_hash联合使用的；

         比如1）中的语句可以为select /*+ORDERED use_nl(a b) */ a.* from table1 a,table2 b where a.xx='1' and b.yy = '2' and a.x=b.x

4）index主要用于强制指定使用某个索引，一般选择效率更高的索引;

         适应场合为：where子句中有几个索引可供选择、或者是用到了联合索引中的第一个字段；

         比如select a.* from table1 where column1 = 'xx' and column2 = 'yy'

         若table1中column1和column2两个字段都有索引，则可以强制指定使用哪个索引，比如index(table1 table1_ind_column1)

5）full用于指定全表扫描，这个实际中应用的较少；

         按照DBA的说法，如果使用index查出来的结果集>=全表记录的25%，那么使用index的效率并不高，还不如用全表扫描；

分析函数篇

有时候我们需要从DB中提取一些很复杂的数据，而标准SQL却对此无能为力，或者是执行效率非常的低；比如我们需要提取如下数据：
    逐行显示各个部门的累计工资，每行包括部门内前面所有人的工资总和；
    查找各个部门工资最高的前N个人；
    ……

语法
Function名称([参数]) OVER ([partition 子句][ order 子句] [window 子句])
  OVER为分析函数的关键字，用于区别普通的聚合函数；从语法格式上区分的话，没加over()即时聚合函数，加了over()就是分析函数。
Partition 子句：Partition by exp1[ ,exp2]...；
  主要用于分组，可以理解成select中的group by；不过它跟select语句后跟的group by 子句并不冲突；指定该子句之后，前面的函数起效范围就是该分组内，若不指定，则Function的起效范围是全部结果集。
Order 子句：Order by exp1[asc|desc] [ ,exp2 [asc|desc]]... [nulls first|last]；
  其参数基本与select中的order by相同；Nulls first|last是用来限定nulls在分组序列中的所在位置的，我们知道oracle中对于null的定义是未知，所以默认order by的时候nulls总会被排在最前面。如果想控制值为null的行显示位置，nulls first|last参数就能派上用场了。
Window 子句：该子句的语法比较复杂，具体可以见下图；

  该子句给出了一个定义变化或者固定的数据窗口方法，分析函数将对这些数据进行操作；默认情况下，一般用不上该子句，分析函数产生一个固定的窗口，影响的数据范围是从第一行到当前行，其效果和RANGE BETWEEN UNDOUNDED PRECEDING AND CURRENT ROW一样；若需要指定操作数据为当前行及其前两行，则可以用ROWS 2 PRECEDING来实现其效果；
其中用[]标注的子句都可以为空，一个最简单的分析函数可能是COUNT(*) OVER ()；

样例
逐行显示各个部门的累计工资，每行包括部门内前面所有人的工资总和：
SELECT EMP_NO,
       NAME,
       DEPT_NO,
       SUM(SAL) OVER(PARTITION BY DEPT_NO ORDER BY EMP_NO) DEPT_SAL_SUM
  FROM EMP
ORDER BY DEPT_NO, EMP_NO;

查找各个部门工资最高的前N个人：
SELECT *
  FROM (SELECT DEPT_NO,
               NAME,
               SAL,
               DENSE_RANK() OVER(PARTITION BY DEPT_NO ORDER BY SAL DESC) DR
          FROM EMP)
WHERE DR <= 3
ORDER BY DEPT_NO, SAL DESC;

注意点
1、分析函数与聚合函数非常相似，不同于聚合函数的地方在于它们每个分组序列均返回多行，而聚合函数返回一行；
2、带有分析函数的SQL列表中，除了order by子句之外，分析函数将在SQL语句中最后执行；因此，分析函数只能用于select的列或order by子句，而不能用于where、group by、having之类的语句中；
3、当分析函数中使用了distinct参数时，则只能使用partition子句，而不能指定order by子句；
4、SELECT语句中的ORDER BY子句与分析函数中的order by子句是互不影响的，但一般来说两者一致比较好，若两者不一致，则意味着分析函数需要对结果集进行多次排序，这将严重降低分析函数的执行效率；
5、dense_rank在做排序时如果遇到列有重复值，则重复值所在行的序列值相同，而其后的序列值依旧递增，rank则是重复值所在行的序列值相同，但其后的序列值从+重复行数开始递增，而row_number则不管是否有重复行，序列值始终递增；

函数列表
大致有26个函数可用，其中很多都是和聚合函数同名的，比如SUM、AVG、MIN、MAX……；其他是一些提供新功能的新函数；具体的函数列表如下：