分类： Oracle
2010-10-19 12:05:56
写道
当绑定数组为稀疏数组或者包含有间隙时，
INDICES OF 会非常有用。该语句的语法结构为：
FORALL indx IN INDICES
OF sparse_collection
INSERT INTO my_table
VALUES sparse_collection (indx);
VALUES OF 用于一种不同的情况：绑定数组可以是稀疏数组，也可以不是，但我只想使用该数组中元素的一个子集。那么我就可以使用VALUES OF来指向我希望在DML操作中使用的值。该语句的语法结构为：
FORALL indx IN VALUES OF pointer_array
INSERT INTO my_table
VALUES binding_array (indx);
下面给以补充：
案例演示环境
-- EMPLOYEE
CREATE TABLE EMPLOYEE AS SELECT * FROM SCOTT.EMP;
ALTER TABLE EMPLOYEE ADD CONSTRAINT PK_EMPNO PRIMARY KEY (EMPNO);
--EMPLOYEE_HISTORY
CREATE TABLE EMPLOYEE_HISTORY AS SELECT EMPNO,SAL,HIREDATE,'DENIER' ACTIVITY FROM EMPLOYEE WHERE 1=0;
CREATE OR REPLACE PACKAGE COMP_ANALYSIS IS
-- EMPLOYEE IS ELIGIBLE
FUNCTION IS_ELIGIBLE(P_EMPNO IN EMPLOYEE.EMPNO%TYPE) RETURN BOOLEAN;
-- RAISE SALES {游标迭代]
PROCEDURE GIVE_RAISE_IN_DEPARTMENT(P_DEPTNO IN EMPLOYEE.DEPTNO%TYPE,
P_SAL IN EMPLOYEE.SAL%TYPE);
-- RAISE SALES EX1 [批量处理]
PROCEDURE GIVE_RAISE_IN_DEPARTMENT_EX1(P_DEPTNO IN EMPLOYEE.DEPTNO%TYPE,
P_SAL IN EMPLOYEE.SAL%TYPE);
END COMP_ANALYSIS;
/
CREATE OR REPLACE PACKAGE BODY COMP_ANALYSIS IS
-- EMPLOYEE IS ELIGIBLE
FUNCTION IS_ELIGIBLE(P_EMPNO IN EMPLOYEE.EMPNO%TYPE) RETURN BOOLEAN IS
V_JOB EMPLOYEE.JOB%TYPE;
BEGIN
SELECT JOB INTO V_JOB FROM EMPLOYEE WHERE EMPNO = P_EMPNO;
IF V_JOB = 'SALESMAN' THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END IS_ELIGIBLE;
-- RAISE SALES {游标迭代]
PROCEDURE GIVE_RAISE_IN_DEPARTMENT(P_DEPTNO IN EMPLOYEE.DEPTNO%TYPE,
P_SAL IN EMPLOYEE.SAL%TYPE) IS
BEGIN
FOR EMP IN (SELECT ROWID RD, EMPNO, SAL, HIREDATE FROM EMPLOYEE) LOOP
IF IS_ELIGIBLE(EMP.EMPNO) THEN
UPDATE EMPLOYEE SET SAL = SAL + P_SAL WHERE ROWID = EMP.RD;
--AND DEPTNO = P_DEPTNO;
ELSE
INSERT INTO EMPLOYEE_HISTORY
(EMPNO, SAL, HIREDATE, ACTIVITY)
VALUES
(EMP.EMPNO, EMP.SAL, EMP.HIREDATE, 'DENIER');
END IF;
END LOOP;
COMMIT;
END GIVE_RAISE_IN_DEPARTMENT;
-- RAISE SALES EX1 [批量处理]
PROCEDURE GIVE_RAISE_IN_DEPARTMENT_EX1(P_DEPTNO IN EMPLOYEE.DEPTNO%TYPE,
P_SAL IN EMPLOYEE.SAL%TYPE) IS
TYPE T_EMPNO IS TABLE OF EMPLOYEE.EMPNO%TYPE INDEX BY BINARY_INTEGER;
TYPE T_SAL IS TABLE OF EMPLOYEE.SAL%TYPE INDEX BY BINARY_INTEGER;
TYPE T_HIREDATE IS TABLE OF EMPLOYEE.HIREDATE%TYPE INDEX BY BINARY_INTEGER;
TYPE T_IDX IS TABLE OF PLS_INTEGER INDEX BY BINARY_INTEGER; --标识匹配数据的行号
--驱动数组
ALL_EMPNOS T_EMPNO;
ALL_SALS T_SAL;
ALL_HIREDATES T_HIREDATE;
--数据数组
APPROVED_EMPNOS T_IDX;
DENIED_EMPNOS T_IDX;
--ENIDE_SALS T_SAL;
--DENIDE_HIREDATES T_HIREDATE;
PROCEDURE RETRIEVE_EMPLOYEE_INFO IS
BEGIN
SELECT EMPNO, SAL, HIREDATE BULK COLLECT
INTO ALL_EMPNOS, ALL_SALS, ALL_HIREDATES
FROM EMPLOYEE;
--WHERE DEPTNO = P_DEPTNO;
END RETRIEVE_EMPLOYEE_INFO;
PROCEDURE PARTITION_BY_ELIGIBILITY IS
BEGIN
FOR I IN 1 .. NVL(ALL_EMPNOS.LAST, 0) LOOP
IF IS_ELIGIBLE(ALL_EMPNOS(I)) THEN
APPROVED_EMPNOS(I) := I;
ELSE
DENIED_EMPNOS(I) := I;
--DENIDE_SALS(I) := ALL_SALS(I);
--DENIDE_HIREDATES(I) := ALL_HIREDATES(I);
END IF;
END LOOP;
END PARTITION_BY_ELIGIBILITY;
PROCEDURE ADD_TO_HISTORY IS
BEGIN
FORALL I IN VALUES OF DENIED_EMPNOS --下标稀疏
INSERT INTO EMPLOYEE_HISTORY
(EMPNO, SAL, HIREDATE, ACTIVITY)
VALUES
(ALL_EMPNOS(I), ALL_SALS(I), ALL_HIREDATES(I), 'DENIER');
END ADD_TO_HISTORY;
PROCEDURE GIVE_THE_RAISE IS
BEGIN
FORALL I IN VALUES OF APPROVED_EMPNOS
UPDATE EMPLOYEE SET SAL = SAL + P_SAL WHERE EMPNO = ALL_EMPNOS(I);
END GIVE_THE_RAISE;
BEGIN
RETRIEVE_EMPLOYEE_INFO;
PARTITION_BY_ELIGIBILITY;
ADD_TO_HISTORY;
GIVE_THE_RAISE;
COMMIT;
END GIVE_RAISE_IN_DEPARTMENT_EX1;
END COMP_ANALYSIS;
/
