DECLARE
-- ALL_ROWS NUMBER(10);
TYPE LAST_NAME_TAB IS RECORD(
    sorder_no   VARCHAR2(30),
    mem_id      VARCHAR2(50),
    createddt   DatE,
    usertype    integer,
    delegate_id VARCHAR2(50));
LAST_NAME_ARR LAST_NAME_TAB;
TYPE T_SORDER IS TABLE OF LAST_NAME_TAB;
VAR_EDM T_SORDER := T_SORDER();
CURSOR P_CUR IS(
    SELECT DISTINCT decode(a.order_type,
                           'ReturnOrder',
                           a.ref_order_no,
                           sorder_no) sorder_no,
                    mem_id,
                    createddt,
                    b.usertype,
                    a.delegate_id
      FROM user);

BEGIN
OPEN p_cur;
FETCH P_CUR BULK COLLECT
INTO VAR_EDM;

FOR I IN 1 .. VAR_EDM.COUNT() LOOP
    /*INSERT \* +APPEND *\
    INTO PTL_SYSTEM_MESSAGE
      (USERID, CREATEDBY, MAILADDRESS, TEMPLATE_ID)
    VALUES
      (VAR_EDM(I).USERID, VAR_EDM(I).CREATEDBY, VAR_EDM(I).MAILADDRESS, VAR_EDM(I)
        .TEMPLATE_ID);
    COMMIT;*/
    DBMS_OUTPUT.PUT_LINE(VAR_EDM(I).mem_id || '---' || VAR_EDM(I)
                         .CREATEDDT || '---' || VAR_EDM(I)
                         .usertype || '---' || VAR_EDM(I)
                         .delegate_id || '---' || VAR_EDM(I).SORDER_NO);
END LOOP;
END;

-------------------------------------------------------------------------------------------------------------

透過bulk collect減少loop處理的開銷

採用bulk collect可以將查詢結果一次性地載入到collections中。

而不是透過cursor一條一條地處理。

可以在select into,fetch into,returning into語句使用bulk collect。

注意在使用bulk collect時，所有的into變數都必須是collections.

舉幾個簡單的例子：

--在select into語句中使用bulk collect

DECLARE
TYPE SalList IS TABLE OF 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 IS TABLE OF 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

CREATE TABLE emp2 AS SELECT * FROM employees;
DECLARE
TYPE NumList IS TABLE OF employees.employee_id%TYPE;
enums NumList;
TYPE NameList IS TABLE OF employees.last_name%TYPE;
names NameList;
BEGIN
DELETE FROM 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;
/
DROP TABLE emp2;

用BULK COLLECT 減小LOOP的開銷(1)

相關文章