sql - 从 oracle 过程返回 Json 字符串

Question

我需要在 PL/SQL 中有一个存储过程，它返回一个包含 json 对象的字符串。此对象必须是表示表行的对象列表。

例如我有一张 table MY_TABLE(ID, TB_VALUE)

输出必须是:

[
  {
    "id":"1",
    "value":"valueTest"
  },
  {
    "id":"2",
    "value":"valueTest2"
  },
  {
    "id":"3",
    "value":"valueTest3"
  }
]

我的存储过程是这样的:

CREATE OR REPLACE PACKAGE BODY "PKG_GETCOR" AS

PROCEDURE SelectProc(output OUT VARCHAR2) IS 
v_json_start CHAR(1) := '[';
v_json_end CHAR(1) := ']';
l_first_rec BOOLEAN := TRUE;

BEGIN
    output := v_json_end;
FOR output IN (SELECT * FROM MY_TABLE)
LOOP
    IF NOT l_first_rec
    THEN
        output := output || ',';
    END IF;
    output := output || JSON_OBJECT('id' VALUE ID, 'value' VALUE TB_VALUE));
END LOOP;   
output := output || (v_json_end); 
DBMS_OUTPUT.PUT_LINE(output);

END SelectProc;

END PKG_GETCOR;

实现此目标的最佳方法是什么？我应该使用 DBMS_OUTPUT.PUT_LINE 吗？

Answer 1

从 Oracle 12 开始，您不需要过程或创建自己的 JSON 函数。

SELECT JSON_ARRAYAGG(
         JSON_OBJECT( KEY 'id' VALUE id, KEY 'value' VALUE tb_value )
         FORMAT JSON
         ORDER BY id
         RETURNING CLOB
       ) AS json
FROM   my_table;

输出:

JSON
[{"id":1,"value":"valueTest"},{"id":2,"value":"valueTest2"},{"id":3,"value":"valueTest3"}]

---

如果你真的想把它放在一个包中，那么把它转换成一个函数，然后把查询包装起来:

CREATE PACKAGE PKG_GETCOR AS
  FUNCTION SelectFunc RETURN CLOB;
END;
/

CREATE PACKAGE BODY PKG_GETCOR AS

  FUNCTION SelectFunc RETURN CLOB
  IS 
    v_json CLOB;
  BEGIN
    SELECT JSON_ARRAYAGG(
             JSON_OBJECT( KEY 'id' VALUE id, KEY 'value' VALUE tb_value )
             FORMAT JSON
             ORDER BY id
             RETURNING CLOB
           )
    INTO   v_json
    FROM   my_table;
    
    RETURN v_json;
  END SelectFunc;
END PKG_GETCOR;
/

然后:

SELECT pkg_getcor.selectfunc() FROM DUAL;

输出:

PKG_GETCOR.SELECTFUNC()
[{"id":1,"value":"valueTest"},{"id":2,"value":"valueTest2"},{"id":3,"value":"valueTest3"}]

db<> fiddle here