避免游标多次遍历

游标在数据库领域被广泛使用,尤其是对于需要将SQL语句返回的数据集进行逐行处理的时候。这为数据处理提供了极大的便利性,然游标的不当 使用从某种程度上而言会降低数据库的性能。下面的是一个来自生产环境的实际例子,由于使用了参数游标,所以引发了多次遍历。 一、源代码

-->下面的procedure用于为trade生成一个新的contract_num,并将其更新到对应的记录
PROCEDURE assign_contract_num( businessdate_in trade_client_tbl.trade_date%TYPE, err_num OUT NUMBER, err_msg OUT VARCHAR2 ) IS
   myspname   CONSTANT VARCHAR2( 100 ) := 'bo_trd_dayend_pkg.assign_contract_num';
   debugpos            bo_common_pkg.debug_pos_type := 0;

	 -->注意此处的游标,此为参数游标,定义了两个输入参数一个是orderside,一个是inputdate
	 -->参数游标的参数应当作为SQL语句的过滤条件,如本例中的where子句的谓词信息
   CURSOR validtradedate( orderside_in trade_client_tbl.order_side%TYPE, inputdate_in trade_client_tbl.input_date%TYPE ) IS
      SELECT trade_date
      FROM   trade_client_tbl
      WHERE  is_valid_trade = 'Y' AND input_date = inputdate_in AND order_side = orderside_in AND contract_num IS NULL
      GROUP BY trade_date
      ORDER BY trade_date;

	 -->再次定义了一个参数游标,此时带有三个参数
   CURSOR validtradecur( orderside_in    trade_client_tbl.order_side%TYPE
                       , tradedate_in    trade_client_tbl.trade_date%TYPE
                       , inputdate_in    trade_client_tbl.input_date%TYPE ) IS
      SELECT grp_ref_id, order_side
      FROM   trade_client_tbl
      WHERE  is_valid_trade = 'Y' AND trade_date = tradedate_in AND input_date = inputdate_in 
      AND order_side = orderside_in AND contract_num IS NULL
      GROUP BY grp_ref_id, order_side
      ORDER BY grp_ref_id;

   syscontract_idx     PLS_INTEGER;     -->声明变量
   syscontract_num     VARCHAR2( 20 );  -->声明变量
BEGIN
   err_num     := bo_common_pkg.c_suc_general;
   debugpos    := 5;

   -- *** Buy Trades Contract No. ***
   FOR trddaterec IN validtradedate( 'B', businessdate_in )  -->开始使用参数遍历游标
   LOOP
      BEGIN
         debugpos    := 10;

         SELECT MAX( contract_num )   -->求当月且类型为B,最大的contract_num号
         INTO   syscontract_num
         FROM   trade_client_vw
         WHERE  trade_date LIKE
                   SUBSTR( trddaterec.trade_date, 1, 6 )
                   || '%'
                AND order_side = 'B';

         IF syscontract_num IS NOT NULL THEN    -->当非空值时,调用函数从中取出contract_num的首部
            debugpos    := 15;
            syscontract_idx := extract_contract_num_idx( syscontract_num );
         ELSE
            syscontract_idx := 0;
         END IF;
      END;

      debugpos    := 20;

      FOR validtraderec IN validtradecur( 'B', trddaterec.trade_date, businessdate_in )  -->使用参数遍历第二个游标
      LOOP                                               -->注意此时的一个参数为上一个游标获得的结果集(trddaterec.trade_date)
         syscontract_idx :=                              -->此时syscontract_idx的值增加1
            syscontract_idx
            + 1;
         syscontract_num := format_contract_num( validtraderec.order_side, trddaterec.trade_date, syscontract_idx );-->格式化contract_num
         debugpos    := 25;
         DBMS_OUTPUT.put_line(   'B - '
                              || validtraderec.grp_ref_id
                              || ' - '
                              || syscontract_num );

         UPDATE trade_client_tbl                       -->将生成的syscontract_num更新到对应记录的contract_num
         SET    contract_num = syscontract_num
         WHERE  grp_ref_id = validtraderec.grp_ref_id;
      END LOOP;
   END LOOP;

   -- *** Sell Trades Contract No. ***  -->下面的这段代码则等同于上一个处理,所不同的是处理所有类型为S的记录
   FOR trddaterec IN validtradedate( 'S', businessdate_in )   
   LOOP
      BEGIN
         debugpos    := 35;

         SELECT MAX( contract_num )
         INTO   syscontract_num
         FROM   trade_client_vw
         WHERE  trade_date LIKE
                   SUBSTR( trddaterec.trade_date, 1, 6 )
                   || '%'
                AND order_side = 'S';

         IF syscontract_num IS NOT NULL THEN
            debugpos    := 40;
            syscontract_idx := extract_contract_num_idx( syscontract_num );
         ELSE
            syscontract_idx := 0;
         END IF;
      END;

      debugpos    := 45;

      FOR validtraderec IN validtradecur( 'S', trddaterec.trade_date, businessdate_in )
      LOOP
         syscontract_idx :=
            syscontract_idx
            + 1;
         syscontract_num := format_contract_num( validtraderec.order_side, trddaterec.trade_date, syscontract_idx );
         debugpos    := 50;
         DBMS_OUTPUT.put_line(   'S - '
                              || validtraderec.grp_ref_id
                              || ' - '
                              || syscontract_num );

         UPDATE trade_client_tbl                         
         SET    contract_num = syscontract_num
         WHERE  grp_ref_id = validtraderec.grp_ref_id;
      END LOOP;
   END LOOP;

   -- Update ACC_POS_HIST_TBL contract num field.   -->最后将所有新生存的contract_num更新到历史记录表
   BEGIN
      UPDATE acc_pos_hist_tbl aph
      SET    aph.contract_num =
                (SELECT DISTINCT tc.contract_num
                 FROM   trade_client_vw tc
                 WHERE  aph.trans_num = tc.grp_ref_id )
      WHERE      aph.contract_num IS NULL
             AND aph.trans_type_cd LIKE 'TD%'
             AND EXISTS
                    (SELECT 1
                     FROM   trade_client_vw tc
                     WHERE  aph.trans_num = tc.grp_ref_id AND tc.contract_num IS NOT NULL);
   END;
END;

-->分析:
-->1.源代码中的主要目的是为表trade_client_tbl中新的trade生成contract_num
-->2.由于存在两种不同类型的order_side,一个为B(Buy),一个为S(Sell),所有使用了参数游标来完成
-->3.使用了两次嵌套循环(loop)
-->4.代码存在冗余现象。即仅仅是类型的不同而导致了游标需要多次的访问表trade_client_tbl

二、代码改进

-->通过上面的分析考虑将类型为B和S的进行合并处理,避免多次扫描原始表。(仅列出改进部分)
-->由于不能确保游标trade_date_cur得到的记录唯一,所以在此仍然使用了两次嵌套,但避免针对不同类型B和S单独处理。
-->两次游标仍然是原来的SQL语句。如果得到的trade_date唯一,则主循环仅循环一次,否则得到的trade_date多次则循环多次。
FOR trade_date_cur IN (SELECT trade_date
                       FROM   trade_client_tbl
                       WHERE  is_valid_trade = 'Y' AND input_date = businessdate_in AND contract_num IS NULL
                       GROUP BY trade_date
                       ORDER BY trade_date )
LOOP
   FOR trade_cur
      IN (SELECT grp_ref_id, order_side, trade_date
          FROM   trade_client_tbl
          WHERE  is_valid_trade = 'Y' AND input_date = businessdate_in AND contract_num IS NULL 
          AND trade_date = trade_date_cur.trade_date
          GROUP BY grp_ref_id, trade_date, order_side
          ORDER BY 1, 2, 3 )
   LOOP
      v_counter   :=
               v_counter
               + 1;
      CASE                                          -->此处使用了case语句来进行区分order_side为B或S
         WHEN trade_cur.order_side = 'B' THEN       -->由于可知的是任意一条trade记录的order_side不是类型为B就是为S
            SELECT MAX( contract_num )              -->因此所需要解决的是根据类型的不同得到不同的最大的contract_num
            INTO   syscontract_num                  -->并在此基础之上来生成新的contract_num
            FROM   trade_client_vw
            WHERE  trade_date LIKE                  -->实际上这个谓词trade_date还可以缩小范围,因为每一天生成的首部是不同的
                      SUBSTR( trade_cur.trade_date, 1, 6 )
                      || '%'
                   AND order_side = 'B';
         ELSE
            SELECT MAX( contract_num )
            INTO   syscontract_num
            FROM   trade_client_vw
            WHERE  trade_date LIKE
                      SUBSTR( trade_cur.trade_date, 1, 6 )
                      || '%'
                   AND order_side = 'S';
      END CASE;

      IF syscontract_num IS NOT NULL THEN
         --   debugpos := 15;
         syscontract_idx := extract_contract_num_idx( syscontract_num );
      ELSE
         syscontract_idx := 0;
      END IF;
     
      syscontract_idx :=
         syscontract_idx
         + 1;
      syscontract_num := format_contract_num( trade_cur.order_side, trade_cur.trade_date, syscontract_idx );
      DBMS_OUTPUT.put_line( 'Current syscontract_num is '
                                 || syscontract_num );
      UPDATE trade_client_tbl
      SET    contract_num = syscontract_num
      WHERE  grp_ref_id = trade_cur.grp_ref_id;
   END LOOP;
END LOOP;

三、调整后结果比对

-->下面是调整前的测试
goex_admin@SYBO2> DECLARE
  2    businessdate_in CHAR(8);
  3    err_num NUMBER;
  4    err_msg VARCHAR2(32767);
  5  
  6  BEGIN
  7    businessdate_in := '20111228';
  8    err_num := NULL;
  9    err_msg := NULL;
 10  
 11    goex_admin.BO_TRD_DAYEND_PKG.assign_contract_num ( businessdate_in, err_num, err_msg );
 12  
 13    DBMS_OUTPUT.Put_Line('ERR_NUM = ' || TO_CHAR(err_num));
 14    DBMS_OUTPUT.Put_Line('ERR_MSG = ' || err_msg);
 15  
 16    DBMS_OUTPUT.Put_Line('');
 17  
 18    COMMIT;
 19  END;
 20  /
B - 03586401 - B1112009143
B - 03586404 - B1112009144
B - 03586416 - B1112009145
........

ERR_NUM = 0
ERR_MSG =

PL/SQL procedure successfully completed.

Elapsed: 00:04:21.97

-->下面是调整后测试的结果,将assign_contract_num封装到一个临时的包
goex_admin@SYBO2> DECLARE
  2    businessdate_in CHAR(8);
  3    err_num NUMBER;
  4    err_msg VARCHAR2(32767);
  5  
  6  BEGIN
  7    businessdate_in := '20111228';
  8    err_num := NULL;
  9    err_msg := NULL;
 10  
 11    goex_admin."BO_TRD_DAYEND_PKG_tmp".assign_contract_num ( businessdate_in, err_num, err_msg );
 12    COMMIT;
 13  END;
 14  /
Current syscontract_num is B1112009143
Current syscontract_num is S1112009340
Current syscontract_num is S1112009341
........
The total count is 797

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.06
goex_admin@SYBO2> spool off;

-->从上面的前后结果对比可知总计797条记录原来所需的时间为4:21.97s,调整之后仅为3.06s。如果记录多的话,差异应该更大
-->Author: Robinson Cheng          
-->Blog:   http://blog.csdn.net/robinson_0612

四、总结 1、参数游标从某种程度上而言,增加了游标的灵活性。即一次定义,多次调用。 2、对基于相同表使用参数游标将会导致对表对象的多次数据读取,增加了I/O开销。

3、尽可能的缩小数据中间结果集,如上面的获得最大的contract_num,由于生成的其唯一性,我们可以将搜索范围限制在一周或当天。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Java帮帮-微信公众号-技术文章全总结

MySQL全部知识点(1)

? 数据库 1 数据库概念(了解) 1.1 什么是数据库 数据库就是用来存储和管理数据的仓库! 数据库存储数据的优先: 可存储大量数据; 方便检索; 保持数据...

32050
来自专栏个人随笔

sql sever分组查询和连接查询

分组查询 select 查询信息 from 表名 where 条件 group by 按照列分组(可多个 ,隔开) order by 排序方式 (查询信息如果列...

37950
来自专栏杨建荣的学习笔记

not null constraint和check constriant的问题及分析(64天)

oracle的constraint有6类,如下。 但是基于列的constraint主要有 type 为C,P,R,U 接触比较多的。 今天来和大家讨论chec...

37870
来自专栏乐沙弥的世界

高效SQL语句必杀技

        No SQL,No cost. SQL语句是造成数据库开销最大的部分。而不良SQL写法直接导致数据库系统性能下降的情形比比皆是。那么如何才能称得...

10020
来自专栏Spark学习技巧

小白专属mysql入门

13940
来自专栏Java后端生活

MySQL(十一)DQL之联合查询

17860
来自专栏存储技术

MySQL查询索引分析

前一段时间修改数据表时,给一个表添加一个datetime字段,当时遇到了一个问题:我是否需要给该datetime字段上加索引呢?如果不给该字段加索引,当wher...

30460
来自专栏乐沙弥的世界

ORA-06502 assigning values from SQL to PL/SQL variables

    最近SQL查询返回的结果给PL/SQL变量出现ORA-06502错误。这个错误的描述是ORA-06502: PL/SQL: numeric or val...

7210
来自专栏java一日一条

SQL Server优化之SQL语句优化

2. ON: 对vt1表应用ON筛选器只有满足 join_condition 为真的行才被插入vt2

25520
来自专栏静默虚空的博客

Mysql 快速指南

20520

扫码关注云+社区

领取腾讯云代金券