|
CREATE TABLE T (N1 NUMBER,C1 VARCHAR2(1));
INSERT INTO T VALUES (1,NULL);
INSERT INTO T VALUES (2,NULL);
INSERT INTO T VALUES (2,NULL);
INSERT INTO T VALUES (3,NULL);
INSERT INTO T VALUES (3,NULL);
INSERT INTO T VALUES (3,NULL);
INSERT INTO T VALUES (4,NULL);
INSERT INTO T VALUES (4,NULL);
DECLARE
TYPE num_t IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
lv_num num_t;
BEGIN
lv_num(1):=2;
lv_num(2):=3;
lv_num(3):=4;
lv_num(4):=1;
FORALL i IN 1..lv_num.COUNT ---- 批量执行UPDATE, 里面其实有三个UPDATE
UPDATE t SET c1=n1 WHERE n1=lv_num(i);
FOR i IN 1..lv_num.COUNT LOOP
--- SQL%ROWCOUNT(i)告诉你每个UPDATE执行了多少行
DBMS_OUTPUT.PUT_LINE('n1='||lv_num(i)||' '||SQL%BULK_ROWCOUNT(i)||' rows updated');
END LOOP;
END;
/
输出:
n1=2 2 rows updated
n1=3 3 rows updated
n1=4 2 rows updated
n1=1 1 rows updated
PL/SQL procedure successfully completed.
SELECT * FROM T;
N1 C
---------- -
1 1
2 2
2 2
3 3
3 3
3 3
4 4
4 4
8 rows selected.
DECLARE
TYPE num_t IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
lv_num num_t;
dml_errors EXCEPTION;
lv_err_cnt number := 0;
PRAGMA exception_init(dml_errors, -24381);
BEGIN
lv_num(1):=2;
lv_num(2):=3;
lv_num(3):=4;
lv_num(4):=1;
BEGIN
FORALL i IN 1 .. lv_num.count SAVE EXCEPTIONS
UPDATE t SET c1=n1*4 WHERE n1=lv_num(i); --- n1*4 在n1=3和4的时候将会溢出,因为c1定义是VARCHAR2(1)
EXCEPTION
WHEN dml_errors THEN
lv_err_cnt := SQL%BULK_EXCEPTIONS.COUNT;
DBMS_OUTPUT.PUT_LINE('There are '||lv_err_cnt||' errors');
FOR i IN 1..lv_err_cnt LOOP
DBMS_OUTPUT.PUT_LINE('error number '||i
||' happened in index '||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ---- lv_num数组的下标
||' data='||lv_num(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX)
||' error code='||SQL%BULK_EXCEPTIONS(i).ERROR_CODE
||' error msg='||SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE) ---- 根据错误代码反推错误信息
);
END LOOP;
END;
END pr_copy_data;
/
输出:
There are 2 errors
error number 1 happened in index 2 data=3 error code=12899 error msg=ORA-12899: value too large for column (actual: , maximum: )
error number 2 happened in index 3 data=4 error code=12899 error msg=ORA-12899: value too large for column (actual: , maximum: )
PL/SQL procedure successfully completed.
SELECT * FROM T;
N1 C
---------- -
1 4 ---- c1 被修改为 N1*4
2 8 ---- c1 被修改为 N1*4
2 8 ---- c1 被修改为 N1*4
3 3 ---- 以下 c1 保持原样因为 N1*4 溢出了
3 3
3 3
4 4
4 4
8 rows selected. |
|