DB2存储过程返回ID

3xiyfsfu 于 2023-03-29 发布在 DB2

关注(0)|答案(1)|浏览(203)

我希望有一个存储过程来创建几个记录，并将ID作为数组返回，最好是作为单列结果集。到目前为止，我能够从单个输入参数json创建项目，并返回一个硬编码的整数，我缺少获取IDS并将其发送回去的位。我可以使用FINAL TABLE方法获取ID，实际上只是如何处理ID的位数组。
这是我目前所拥有的

CREATE OR REPLACE PROCEDURE INSERT_ITEMS (IN JSON_VALUE_IN CLOB(16777216))
LANGUAGE SQL
MODIFIES SQL DATA

BEGIN
DECLARE intEventId BIGINT;

DECLARE C2 CURSOR WITH RETURN FOR SELECT intEventId AS EVENTID FROM (VALUES(intEventId));

    INSERT INTO ITEMS (
        TITLE
    )
    SELECT
        JSON_VAL(SYSTOOLS.JSON2BSON(ITEMS.value), 'title', 's:100')
    
    FROM
        TABLE(
            SYSTOOLS.JSON_TABLE(SYSTOOLS.JSON2BSON(JSON_VALUE_IN), 'items', 's:32000')
        ) ITEMS
    SET intEventId = 1;
    
OPEN C2;

    
END
@

db2

来源：https://stackoverflow.com/questions/75841526/db2-stored-procedure-return-ids

1条答案

按热度按时间

xdnvmnnf1#

试试这个：

CREATE TABLE ITEMS 
(
  ID INT NOT NULL GENERATED BY DEFAULT AS IDENTITY
, TITLE VARCHAR (100)
)@

CREATE OR REPLACE PROCEDURE INSERT_ITEMS (IN JSON_VALUE_IN CLOB(16777216))
LANGUAGE SQL
DYNAMIC RESULT SETS 1
MODIFIES SQL DATA
BEGIN
  DECLARE C1 CURSOR WITH RETURN FOR  
  WITH T AS
  (
    -- You get a list of generated IDs with this SELECT
    SELECT ID
    FROM
    NEW TABLE
    (
      INSERT INTO ITEMS (TITLE)
      SELECT VALUE
      -- SP input is converted to a string representation of a JSON document;
      -- JSON document is passed to the JSON_TABLE function
      -- which tokenizes the input: you get N rows with 1 title each
      FROM TABLE (SYSTOOLS.JSON_TABLE (SYSTOOLS.JSON2BSON ('{"e":[' || JSON_VALUE_IN || ']}'), 'e', 's:100')) T
    )
  )
  -- The list of generated IDs is converted to a JSON array of these IDs;
  -- The output has 1 row and 1 column
  VALUES JSON_ARRAY ((SELECT ID FROM T) FORMAT JSON);

  OPEN C1;
END
@

CALL INSERT_ITEMS ('"Title1", "Title2", "Title3"')@

|1      |
|-------|
|[1,2,3]|

SELECT * FROM ITEMS@

|ID |TITLE |
|---|------|
|1  |Title1|
|2  |Title2|
|3  |Title3|

赞(0）回复(0）举报 2023-03-29

我来回答

DB2存储过程返回ID

1条答案

相关问题

热门标签

最新问答