时间戳

首页 > 解决方案 > PL SQL“数组类型”到TSQL的翻译

sql-server - PL SQL“数组类型”到TSQL的翻译

问题描述

这是我最初的 PL/SQL 代码：

    TYPE VarcharArray IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;

我在以下代码中使用它：

 PROCEDURE Create(inFatherId IN VARCHAR2, inBarcode IN VarcharArray, inItemId IN VarcharArray)
 IS
    myCount NUMBER(38);
    sampleId_FromDb NUMBER(38);
    itemId_FromDb NUMBER(38);
 BEGIN
    myCount := inBarcode.COUNT;
    FOR i IN 1..myCount
    LOOP
        SELECT ITEM.Id INTO itemId_FromDb FROM ITEM WHERE FatherId = inFatherId AND CampaignItemId = inItemId(i);
        SELECT SAMPLE_SEQUENCE.NEXTVAL INTO sampleId_FromDb FROM DUAL;
        INSERT INTO CAMPAIGN_SAMPLES(Id, Barcode, ItemId) VALUES(sampleId_FromDb, inBarcode(i), itemId_FromDb);
    END LOOP;
 END;

我已经看到可以使用表值参数将数组类型转换为 MS SQL，但是我如何以类似的方式进行迭代，以便在迭代中包含你的操作？

在当前的 PL/SQL 实现中，我在数组中发送了多达 50.000 个元素，并且性能不错。我希望在 MS SQL 中也有类似的东西。

标签： sql-servertsqlplsql

解决方案

无需一次循环和插入一行。这只是使您的代码变慢的一种方法。由于表格中没有任何顺序，因此您需要添加一列来定义顺序。你的类型会是这样的：

CREATE TYPE VarcharArray AS TABLE(ID int, Item VARCHAR(100));

然后，您可以将您的过程重写为单个 INSERT 语句。

CREATE PROCEDURE SomeProcedure(
    @FatherId AS VARCHAR, --This might need a length or will be defaulted to length 1
    @Barcode  AS VarcharArray READONLY, 
    @ItemId   AS VarcharArray READONLY
)
AS
    INSERT INTO CAMPAIGN_SAMPLES(Id, Barcode, ItemId) 
    SELECT NEXT VALUE FOR SAMPLE_SEQUENCE,
        bc.Item,
        i.Id 
    FROM ITEM i
    JOIN @ItemId ii ON i.CampaignItemId = ii.Item
    JOIN @Barcode bc ON ii.ID = bc.ID
    WHERE i.FatherId = @FatherId;

您还可以创建一个包含两个值的表，并防止可能发生的任何排序问题。

CREATE TYPE BarcodeItems AS TABLE(Item VARCHAR(100), Barcode VARCHAR(100));

GO
CREATE PROCEDURE SomeProcedure(
    @FatherId AS VARCHAR, --This might need a length or will be defaulted to length 1
    @BarcodeItems  AS BarcodeItems READONLY
)
AS
    INSERT INTO CAMPAIGN_SAMPLES(Id, Barcode, ItemId) 
    SELECT NEXT VALUE FOR SAMPLE_SEQUENCE,
        bi.Item,
        i.Id 
    FROM ITEM i
    JOIN @BarcodeItems bi ON i.CampaignItemId = bi.Item
    WHERE i.FatherId = @FatherId;

推荐阅读