Oracle PL/SQL中如何获取API返回的数组标签值并存储？

原代码存在的问题 #

• 语法错误：第一个JSON_TABLE语句缺少闭合的右括号，导致编译失败。
• 未声明变量：循环中使用的v_tag和v_tag_id未在DECLARE块中定义，会触发未声明变量的异常。
• 逻辑错误：条件判断v_tags IS NOT NULL OR v_tags.COUNT > 0存在风险——若v_tags为NULL，调用v_tags.COUNT会直接抛出空指针异常，应改为AND逻辑。
• 冗余操作：先将JSON数组收集到集合再循环插入，属于冗余操作，可直接通过INSERT...SELECT简化流程并提升效率。

修复基础错误的版本 #

DECLARE
 v_name         VARCHAR2(100);
 v_email        VARCHAR2(100);
 v_tag          VARCHAR2(100); -- 新增变量声明
 v_tag_id       NUMBER; -- 新增变量声明

 TYPE t_tags IS TABLE OF VARCHAR2(100);
 v_tags t_tags;
BEGIN
 -- 修复JSON_TABLE的语法错误，补充闭合括号
 SELECT name, email
 INTO v_name, v_email
 FROM JSON_TABLE(:body, '$'
    COLUMNS (
        name  VARCHAR2(100)  PATH '$.leads.name',
        email VARCHAR2(100)  PATH '$.leads.email'
    )
 );

 SELECT CAST(COLLECT(tag) AS t_tags)
 INTO v_tags
 FROM JSON_TABLE(:body, '$.leads.tags[*]'
     COLUMNS (tag VARCHAR2(100) PATH '$'));

 -- 修正条件判断逻辑，避免空指针异常
 IF v_tags IS NOT NULL AND v_tags.COUNT > 0 THEN
  FOR i IN 1 .. v_tags.COUNT LOOP
     v_tag := v_tags(i);
    
    INSERT INTO LEAD_TAG (TAG, COR, ETQ_CAT)
            VALUES (v_tag, 'TESTING', 'TESTING') -- 替换为实际标签值，原代码写死TESTING为测试用
            RETURNING ID INTO v_tag_id;
  END LOOP;
 END IF;
END;
/

优化版本（无需集合与循环） #

直接通过INSERT...SELECT从JSON数组读取数据插入，简化代码且效率更高：

DECLARE
 v_name         VARCHAR2(100);
 v_email        VARCHAR2(100);
BEGIN
 -- 读取name和email
 SELECT name, email
 INTO v_name, v_email
 FROM JSON_TABLE(:body, '$'
    COLUMNS (
        name  VARCHAR2(100)  PATH '$.leads.name',
        email VARCHAR2(100)  PATH '$.leads.email'
    )
 );

 -- 直接从JSON数组插入标签数据
 INSERT INTO LEAD_TAG (TAG, COR, ETQ_CAT)
 SELECT tag, 'TESTING', 'TESTING'
 FROM JSON_TABLE(:body, '$.leads.tags[*]'
     COLUMNS (tag VARCHAR2(100) PATH '$'));

 -- 若需批量获取插入的ID，可使用BULK COLLECT：
/*
 DECLARE
  TYPE tag_ids_t IS TABLE OF LEAD_TAG.ID%TYPE;
  v_tag_ids tag_ids_t;
 BEGIN
  -- 读取name和email逻辑...

  INSERT INTO LEAD_TAG (TAG, COR, ETQ_CAT)
  SELECT tag, 'TESTING', 'TESTING'
  FROM JSON_TABLE(:body, '$.leads.tags[*]'
       COLUMNS (tag VARCHAR2(100) PATH '$'))
  RETURNING ID BULK COLLECT INTO v_tag_ids;

  -- 可遍历v_tag_ids处理每个插入的ID
 END;
*/
END;
/

内容的提问来源于stack exchange，提问作者Devworld