ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 用BULK COLLECT 减小LOOP的开销(1)

用BULK COLLECT 减小LOOP的开销(1)

原创 Linux操作系统 作者:taogchan 时间:2011-08-18 15:29:51 0 删除 编辑
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;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22392018/viewspace-705263/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-08-28

  • 博文量
    196
  • 访问量
    1198224