SQL SERVER 批量效率的读取游标数据？

最新推荐文章于 2020-12-04 20:43:44 发布

rockywu

最新推荐文章于 2020-12-04 20:43:44 发布

阅读量1.5k

点赞数

分类专栏： SQL SERVER 文章标签： sql server exception oracle integer insert table

本文链接：https://blog.csdn.net/rockywu/article/details/5811927

版权

SQL SERVER 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

Oracle 中使用 fetch bulk collect into 批量效率的读取游标数据，效率得到大幅提升，但SQL SERVER有这功能吗?
先看ORACLE PL/SQL
PROCEDURE snap_inv_proc IS
   contract_                 VARCHAR2(20);
   --
   CURSOR snap_inv_stock IS
          SELECT inv.contract                              contract,
                 inv.part_no                               part_no,
                 inv.configuration_id                      configuration_id,
                 inv.location_no                           location_no,
                 inv.lot_batch_no                          lot_batch_no,
                 inv.eng_chg_level                         eng_chg_level,
                 inv.serial_no                             serial_no,
                 inv.waiv_dev_rej_no                       waiv_dev_rej_no
          FROM inv_part_tab inv;

   TYPE InvStock IS TABLE OF snap_inv_stock%ROWTYPE
       INDEX BY BINARY_INTEGER;
   inv_stock_               InvStock;
--声明需要集合类型及变量，参照字段的 type 来声明类型
BEGIN

   OPEN snap_inv_stock;
   FETCH snap_inv_stock BULK COLLECT INTO inv_stock_;
   CLOSE snap_inv_stock;

   IF inv_stock_.COUNT > 0 THEN
      FOR i_ IN inv_stock_.FIRST .. inv_stock_.LAST LOOP

             INSERT INTO Info_Services_Rpt( &contract,
                                            &part_no,
                                            &configuration_id,
                                            &lot_batch_no,
                                            &location_no,
                                            &eng_chg_level,
                                            &waiv_dev_rej_no,
                                            &serial_no,
                                            &quantity)
                          VALUES(inv_stock_(i_).contract,
                                  inv_stock_(i_).part_no,
                                  inv_stock_(i_).configuration_id,
                                  inv_stock_(i_).lot_batch_no,
                                  inv_stock_(i_).location_no,
                                  inv_stock_(i_).eng_chg_level,
                                  inv_stock_(i_).waiv_dev_rej_no,
                                  inv_stock_(i_).serial_no);
      END LOOP;
   END IF;
   inv_stock_.DELETE;
EXCEPTION
   WHEN OTHERS THEN
        inv_stock_.DELETE;
        RAISE;
END snap_inv_proc;
那么SQL SERVER T-SQL如何处理？