时间戳

首页 > 解决方案 > 存储过程，如果存在则返回 ID 或插入并返回 ID

sql - 存储过程，如果存在则返回 ID 或插入并返回 ID

问题描述

我有一个非常简单的 DB2 存储过程，我在其中接收参数字符串并将其插入到表中，它工作正常，但我确实需要扩展它。目前它看起来像这样：

    IN_TAG_DATA


    P1 : BEGIN ATOMIC

    INSERT INTO SCHEMA.TAGS(TAG_DATA)
    VALUES(IN_TAG_DATA);

    END P1

我现在需要做的是放入相同的字符串参数，但基于该参数，我需要检查名称是否已经存在，如果存在，则选择/返回它的 id。如果它不存在，我需要插入它，然后返回创建的 ID。

我应该如何改变它才能在这里获得适当的功能？

    IN_TAG_DATA
    OUT_TAG_ID

    P1 : BEGIN ATOMIC

    DECLARE V_TAG_ID INTEGER;

    SELECT CAST(NEXT VALUE FOR SCHEMA . TAG_ID_SEQ AS INTEGER)
    INTO V_TAG_ID
    FROM SYSIBM . SYSDUMMY1;

    /*
    IF EXISTS BASED ON IN_TAG_DATA, GET ID ELSE INSERT AND RETURN created ID
    */


    SET OUT_TAG_ID
    END P1

标签： sqlstored-proceduresdb2

解决方案

--#SET TERMINATOR @
CREATE TABLE TAGS(TAG_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY, TAG_DATA VARCHAR(20) NOT NULL) IN USERSPACE1@

CREATE OR REPLACE PROCEDURE TAGS(P_TAG_DATA VARCHAR(20), OUT P_TAG_ID INT)
BEGIN

FOR C1 AS 
  WITH E (TAG_ID) AS (
    SELECT TAG_ID
    FROM TAGS
    WHERE TAG_DATA=P_TAG_DATA
  )
  , I AS (
    SELECT TAG_ID
    FROM NEW TABLE (
      INSERT INTO TAGS (TAG_DATA) 
      SELECT P_TAG_DATA FROM TABLE(VALUES 1)
      WHERE NOT EXISTS (SELECT 1 FROM E)
    )
  )
  SELECT COALESCE(E.TAG_ID, I.TAG_ID) TAG_ID
  FROM I FULL JOIN E ON 1=1

  DO
    SET P_TAG_ID = C1.TAG_ID;
  END FOR;

END@

A little bit complicated, but...
All actions needed can be performed by this single statement. E contains a TAG_ID value if it exists. We insert a new row only if it doesn't exist, and retrieve inserted TAG_ID using SELECT FROM data-change-table-reference. Finally, we join existing row and inserted one. Only one of them exists, and we get one of these TAG_IDs.

If you are at Db2 for LUW 11.1, then FOR loop is not needed - the SELECT INTO statement supports CTE, so one can use just a single statement with SELECT COALESCE(E.TAG_ID, I.TAG_ID) INTO P_TAG_ID without a loop. Prior Db2 versions don't support it, so, we have to use FOR loop just to retrieve a single value.

推荐阅读