DECLARE TYPE sallist ISTABLEOF emp.sal%TYPE; sals sallist; BEGIN -- Limit the number of rows to 100. SELECT sal BULK COLLECT INTO sals FROM emp WHERE ROWNUM <=100; -- Retrieve 10% (approximately) of the rows in the table. SELECT sal BULK COLLECT INTO sals FROM emp SAMPLE 10; END;
--在fetch into中使用bulk collect
DECLARE TYPE deptrectab ISTABLEOF dept%ROWTYPE; dept_recs deptrectab; CURSOR c1 IS SELECT deptno, dname, loc FROM dept WHERE deptno >10; BEGIN OPEN c1; FETCH c1 BULK COLLECT INTO dept_recs; END;
在returning into中使用bulk collect
CREATETABLE emp2 ASSELECT*FROM employees; DECLARE TYPE numlist ISTABLEOF employees.employee_id%TYPE; enums numlist; TYPE namelist ISTABLEOF employees.last_name%TYPE; names namelist; BEGIN DELETEFROM emp2 WHERE department_id =30 RETURNING employee_id, last_name BULK COLLECT INTO enums, names; DBMS_OUTPUT.put_line ('Deleted '|| SQL%ROWCOUNT||' rows:'); FOR i IN enums.FIRST .. enums.LAST LOOP DBMS_OUTPUT.put_line ('Employee #'|| enums (i) ||': '|| names (i)); END LOOP; END; / DROPTABLE emp2;
这里有一个很好的例子
CREATEORREPLACE PACKAGE comp_analysis IS FUNCTION is_eligible (id_in IN lots_of_employees.employee_id%TYPE) RETURN BOOLEAN; END comp_analysis; / CREATEORREPLACE PACKAGE BODY comp_analysis IS FUNCTION is_eligible (id_in IN lots_of_employees.employee_id%TYPE) RETURN BOOLEAN IS BEGIN RETURN MOD (id_in, 2) =0; END; END comp_analysis; / CREATEORREPLACEPROCEDURE give_raises_in_department1 ( dept_in IN lots_of_employees.department_id%TYPE , newsal IN lots_of_employees.salary%TYPE ) IS CURSOR emp_cur IS SELECT employee_id, salary, hire_date FROM lots_of_employees WHERE (department_id = dept_in OR dept_IN ISNULL); emp_rec emp_cur%ROWTYPE; BEGIN OPEN emp_cur; LOOP FETCH emp_cur INTO emp_rec; EXITWHEN emp_cur%NOTFOUND; IF comp_analysis.is_eligible (emp_rec.employee_id) THEN UPDATE lots_of_employees SET salary = newsal WHERE employee_id = emp_rec.employee_id; ELSE INSERTINTO employee_history (employee_id, salary , hire_date, activity ) VALUES (emp_rec.employee_id, emp_rec.salary , emp_rec.hire_date, 'RAISE DENIED' ); ENDIF; END LOOP; END give_raises_in_department1; / SHO ERR REM REM Pre-10g create multiple copies of collection REM for different purposes. REM CREATEORREPLACEPROCEDURE give_raises_in_department2 ( dept_in IN lots_of_employees.department_id%TYPE , newsal IN lots_of_employees.salary%TYPE ) IS TYPE employee_aat ISTABLEOF lots_of_employees.employee_id%TYPE INDEXBY PLS_INTEGER; TYPE salary_aat ISTABLEOF lots_of_employees.salary%TYPE INDEXBY PLS_INTEGER; TYPE hire_date_aat ISTABLEOF lots_of_employees.hire_date%TYPE INDEXBY PLS_INTEGER; employee_ids employee_aat; salaries salary_aat; hire_dates hire_date_aat; approved_employee_ids employee_aat; denied_employee_ids employee_aat; denied_salaries salary_aat; denied_hire_dates hire_date_aat; PROCEDURE retrieve_employee_info IS BEGIN SELECT employee_id, salary, hire_date BULK COLLECT INTO employee_ids, salaries, hire_dates FROM lots_of_employees WHERE (department_id = dept_in OR dept_IN ISNULL); END; PROCEDURE partition_by_eligibility IS BEGIN FOR indx IN employee_ids.FIRST .. employee_ids.LAST LOOP IF comp_analysis.is_eligible (employees (indx)) THEN approved_employee_ids (indx) := employees (indx); ELSE denied_employee_ids (indx) := employees (indx); denied_salaries (indx) := salaries (indx); denied_hire_dates (indx) := hire_dates (indx); ENDIF; END LOOP; END; PROCEDURE add_to_history IS BEGIN FORALL indx IN denied_employee_ids.FIRST .. denied_employee_ids.LAST INSERTINTO employee_history (employee_id , salary , hire_date, activity ) VALUES (denied_employee_ids (indx) , denied_salaries (indx) , denied_hire_dates (indx), 'RAISE DENIED' ); END; PROCEDURE give_the_raise IS BEGIN FORALL indx IN approved_employee_ids.FIRST .. approved_employee_ids.LAST UPDATE lots_of_employees SET salary = newsal WHERE employee_id = approved_employee_ids (indx); END; BEGIN retrieve_employee_info; partition_by_eligibility; add_to_history; give_the_raise; END give_raises_in_department2; / SHO ERR REM REM 10g usage of INDICES OF REM CREATEORREPLACEPROCEDURE give_raises_in_department3 ( dept_in IN lots_of_employees.department_id%TYPE , newsal IN lots_of_employees.salary%TYPE ) IS TYPE employee_aat ISTABLEOF lots_of_employees.employee_id%TYPE INDEXBY PLS_INTEGER; TYPE salary_aat ISTABLEOF lots_of_employees.salary%TYPE INDEXBY PLS_INTEGER; TYPE hire_date_aat ISTABLEOF lots_of_employees.hire_date%TYPE INDEXBY PLS_INTEGER; employee_ids employee_aat; salaries salary_aat; hire_dates hire_date_aat; TYPE guide_aat ISTABLEOF BOOLEAN INDEXBY PLS_INTEGER; approved_list guide_aat; denied_list guide_aat; PROCEDURE retrieve_employee_info IS BEGIN SELECT employee_id, salary, hire_date BULK COLLECT INTO employee_ids, salaries, hire_dates FROM lots_of_employees WHERE (department_id = dept_in OR dept_IN ISNULL); END; PROCEDURE partition_by_eligibility IS BEGIN FOR indx IN employee_ids.FIRST .. employee_ids.LAST LOOP IF comp_analysis.is_eligible (employees(indx)) THEN approved_list (indx) := TRUE; ELSE denied_list (indx) := TRUE; ENDIF; END LOOP; END; PROCEDURE add_to_history IS BEGIN FORALL indx IN INDICES OF denied_list INSERTINTO employee_history (employee_id , salary , hire_date, activity ) VALUES (employees (indx) , salaries (indx) , hire_dates (indx) , 'RAISE DENIED' ); END; PROCEDURE give_the_raise IS BEGIN FORALL indx IN INDICES OF approved_list UPDATE lots_of_employees SET salary = newsal , hire_date = hire_dates(indx) WHERE employee_id = employees(indx); END; BEGIN retrieve_employee_info; partition_by_eligibility; add_to_history; give_the_raise; END give_raises_in_department3; / SHO ERR REM REM 10g usage ofVALUESOF REM CREATEORREPLACEPROCEDURE give_raises_in_department4 ( dept_in IN lots_of_employees.department_id%TYPE , newsal IN lots_of_employees.salary%TYPE ) IS TYPE employee_aat ISTABLEOF lots_of_employees.employee_id%TYPE INDEXBY PLS_INTEGER; TYPE salary_aat ISTABLEOF lots_of_employees.salary%TYPE INDEXBY PLS_INTEGER; TYPE hire_date_aat ISTABLEOF lots_of_employees.hire_date%TYPE INDEXBY PLS_INTEGER; employee_ids employee_aat; salaries salary_aat; hire_dates hire_date_aat; TYPE guide_aat ISTABLEOF PLS_INTEGER INDEXBY PLS_INTEGER; approved_list guide_aat; denied_list guide_aat; PROCEDURE retrieve_employee_info IS BEGIN SELECT employee_id, salary, hire_date BULK COLLECT INTO employee_ids, salaries, hire_dates FROM lots_of_employees WHERE (department_id = dept_in OR dept_IN ISNULL); END; PROCEDURE partition_by_eligibility IS BEGIN FOR indx IN employee_ids.FIRST .. employee_ids.LAST LOOP IF comp_analysis.is_eligible (employees(indx)) THEN approved_list (indx) := indx; ELSE denied_list (indx) := indx; ENDIF; END LOOP; END; PROCEDURE add_to_history IS BEGIN FORALL indx INVALUESOF denied_list INSERTINTO employee_history (employee_id , salary , hire_date, activity ) VALUES (employees (indx) , salaries (indx) , hire_dates (indx) , 'RAISE DENIED' ); END; PROCEDURE give_the_raise IS BEGIN FORALL indx INVALUESOF approved_list UPDATE lots_of_employees SET salary = newsal , hire_date = hire_dates(indx) WHERE employee_id = employees(indx); END; BEGIN retrieve_employee_info; partition_by_eligibility; add_to_history; give_the_raise; END give_raises_in_department4; / SHO ERR SET TIMING ON BEGIN give_raises_in_department1 (NULL, 1000); END; / BEGIN give_raises_in_department2 (NULL, 1000); END; / BEGIN give_raises_in_department3 (NULL, 1000); END; /