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也很类似；

6.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 …;

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

8.用FETCH INTO使用CURSOR
LOOP
FETCH CUR_NAME INTO V_COL1,V_COL2,…;
…
EXIT WHEN CUR_NAME%NOTFOUND;
END LOOP;

9.用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;

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