时间戳

首页 > 解决方案 > 从返回 SQL 结果的存储函数/过程生成 JSON？

json - 从返回 SQL 结果的存储函数/过程生成 JSON？

问题描述

我正在Oracle 12.1上测试 PLJSON ，目的是从表中生成 JSON。我已经实现了一个封装对该表的查询的包。我还验证了，使用pljson_dyn，我可以从 SQL 查询到表中正确生成 JSON。但是，如果我将查询替换为对返回表中数据的函数的调用，则会失败。有没有可能做出这个选择？

从选择返回 ok 的代码：

set serveroutput on;
declare 
  ret json_list;
begin
  ret := json_dyn.executeList('select id, denounidad from tgeounipoblacionales where tgeomunicipio_id = 6015');  
  ret.print;
end;
/

结果：

[{
  "ID" : 60150001,
  "DENOUNIDAD" : "Plantio (El)"
}, {
...many lines
...
}, {
  "ID" : 60150033,
  "DENOUNIDAD" : "Pilara (La)"
}]

返回封装的 SQL 结果的代码：

  DECLARE
        ret PAQ_TGEOUNIPOBLACIONALES.tipo_TABLA_POBLACIONES;
  BEGIN 

   ret := paq_tgeounipoblacionales.OBTENER_POR_MUNICIPIO ( 6015 );
   FOR indice IN ret.FIRST .. ret.LAST
   LOOP
        DBMS_OUTPUT.put_line ( TO_CHAR( ret (indice).ID ) || ', ' || ret (indice).DENOUNIDAD);
   END LOOP;
  END;

结果：

60150001, Plantio (El)
...
...
60150033, Pilara (La)

我尝试将函数调用封装在pljson_dyn中：

declare 
  ret json_list;
begin
  ret := json_dyn.executeList( 'paq_tgeounipoblacionales.OBTENER_POR_MUNICIPIO ( 6015 )' );
  ret.print;
end;
Informe de error -
ORA-00900: sentencia SQL no válida
ORA-06512: en "SYS.DBMS_SQL", línea 1120
ORA-06512: en "EDUCOMUNES.JSON_DYN", línea 61
ORA-06512: en línea 4
00900. 00000 -  "invalid SQL statement"
*Cause:    
*Action:

规范包（简化）是：

create or replace PACKAGE            PAQ_TGEOUNIPOBLACIONALES
IS
...
    TYPE tipo_TABLA_POBLACIONES IS TABLE OF TGEOUNIPOBLACIONALES%ROWTYPE
    INDEX BY PLS_INTEGER;
...
     FUNCTION OBTENER_POR_MUNICIPIO
     (
     pe_tgeomunicipio_id  IN TGEOMUNICIPIOS.ID%TYPE
     )
     RETURN tipo_TABLA_POBLACIONES;

...    
END PAQ_TGEOUNIPOBLACIONALES;

身体包（简化）是：

create or replace PACKAGE BODY            PAQ_TGEOUNIPOBLACIONALES
IS
...

     FUNCTION OBTENER_POR_MUNICIPIO
     (
     pe_tgeomunicipio_id  IN TGEOMUNICIPIOS.ID%TYPE
     )
     RETURN tipo_TABLA_POBLACIONES
     IS       
       l_poblaciones tipo_TABLA_POBLACIONES;       
     BEGIN
        SELECT *
          BULK COLLECT INTO l_poblaciones
          FROM TGEOUNIPOBLACIONALES
         WHERE TGEOMUNICIPIO_ID = pe_tgeomunicipio_id;

        RETURN l_poblaciones;

     END OBTENER_POR_MUNICIPIO;   
...     
END PAQ_TGEOUNIPOBLACIONALES;

表结构（简化）为：

 CREATE TABLE "EDUCOMUNES"."TGEOUNIPOBLACIONALES" 
   (    
    "DENOUNIDAD" VARCHAR2(200 BYTE), 
    "ID" NUMBER(*,0),                     => primary key
    "TGEOMUNICIPIO_ID" NUMBER(*,0)        => foreign key
  ...
  );

标签： jsonplsqloracle12cpljson

解决方案

尝试以下

（我不能自己尝试，因为我没有一个简单而完整的测试用例——包括表结构和数据——来测试，但它应该可以工作）

set serveroutput on;
declare 
  ret json_list;
begin
  ret := json_dyn.executeList('select * from table(paq_tgeounipoblacionales.OBTENER_POR_MUNICIPIO ( 6015 ));');  
  ret.print;
end;
/

我还建议您获取最新的 pljson 版本（https://github.com/pljson/pljson/tree/develop_v3）

推荐阅读