oracle存储过程中forall,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代码量；