Блог

Я пытаюсь преобразовать тип записи, определенный в пакете PL / SQL, в JSON.

Я обнаружил, что в SQL я могу использовать select json_object(*) from SomeTable для возврата объектов, обладающих свойством для каждого столбца в таблице, но, похоже, я не могу сделать это с типами записей в коде PL / SQL.

Пример пакета с типами и функцией для возврата (сериализации) json на основе типа:

 create or replace package Customer as
  type RT_Address is record (
    Line1 varchar2(100),
    Line2 varchar2(100),
    City varchar2(100)
  );

  type RT_Customer is record (
    FirstName varchar2(100),
    LastName varchar2(100),
    Address RT_Address
  );

  function AsJson(P_Customer RT_Customer)
  return varchar2;

end;

create or replace package body devvanessa.Customer as

  function AsJson(P_Customer RT_Customer)
  return varchar2 is
    V_DOM jdom_t;
    V_JSON json_object_t;
    V_JSONBody varchar2(4000);
  begin

    V_JSON := json_object(P_Customer); -- PLS-00103: Encountered the symbol when expecting one of the following: . ( * @ % amp; -   / at mod remainder rem <een exponent (**)> || multiset value

    if V_DOM.append(P_CUSTOMER) then -- PLS-00306: wrong number or types of arguments
      null;
    end if;
    V_JSONBody := V_Json.STRINGIFY;

    return V_JSONBody;
  end;

end;
  

Вышесказанное немного упрощено, потому что я действительно хочу сохранить этот json и выполнить с ним некоторые другие действия, но это показывает суть моей проблемы:

Как мне преобразовать тип записи в Json в PL / SQL, не указывая все отдельные поля отдельно. Мне также любопытно, как это будет работать наоборот.

Я искал различные источники, такие как документация по функциям JSON, документация JSON Oracle 19 и просто подсказки по завершению кода, которые я получил по типам json_object_t и jdom_t , но пока я не могу найти никаких доказательств того, что это вообще возможно.

Это сработало бы:

     V_JSon.Put('FirstName', P_Customer.FirstName);
    -- repeated for each property
  

Таким образом, я получаю json, но для этого требуется, чтобы я указывал каждое поле по отдельности.

У JSON_OBJECT_T нет конструктора, который принимает тип записи, поэтому вам нужно будет явно определить каждый из ключей / значений для определения JSON. JDOM_T не требуется для того, что вы пытаетесь сделать. Ниже приведен пример того, как преобразовать ваши типы записей в JSON и обратно.

 DECLARE
    TYPE RT_Address IS RECORD
    (
        Line1    VARCHAR2 (100),
        Line2    VARCHAR2 (100),
        City     VARCHAR2 (100)
    );

    TYPE RT_Customer IS RECORD
    (
        FirstName    VARCHAR2 (100),
        LastName     VARCHAR2 (100),
        Address      RT_Address
    );

    l_customer1   rt_customer
        := rt_customer ('John', 'Doe', rt_address ('123 Main Street', 'Apartment# 2A', 'London'));
    l_customer2   rt_customer
        := rt_customer ('Jane', 'Smith', rt_address ('456 Broken Dreams Blvd', NULL, 'Greenville'));
    l_json        json_object_t;
    l_record      rt_customer;

    FUNCTION customer_record_to_json (P_Customer RT_Customer)
        RETURN json_object_t
    IS
        l_address    json_object_t := json_object_t ();
        l_customer   json_object_t := json_object_t ();
    BEGIN
        l_address.put ('Line1', p_customer.address.line1);
        l_address.put ('Line2', p_customer.address.line2);
        l_address.put ('City', p_customer.address.city);

        l_customer.put ('FirstName', p_customer.firstname);
        l_customer.put ('LastName', p_customer.lastname);
        l_customer.put ('Address', l_address);

        RETURN l_customer;
    END;

    FUNCTION customer_json_to_record (p_customer_json json_object_t)
        RETURN rt_customer
    IS
        l_address_json   json_object_t := json_object_t ();

        l_address        rt_address;
        l_customer       rt_customer;
    BEGIN
        l_address_json := p_customer_json.get_object ('Address');
        l_address.line1 := l_address_json.get_string ('Line1');
        l_address.line2 := l_address_json.get_string ('Line2');
        l_address.city := l_address_json.get_string ('City');

        l_customer.firstname := p_customer_json.get_string ('FirstName');
        l_customer.lastname := p_customer_json.get_string ('LastName');
        l_customer.address := l_address;
        RETURN l_customer;
    END;
BEGIN
    l_json := customer_record_to_json (l_customer1);
    DBMS_OUTPUT.put_line ('Customer 1 (JSON): ' || l_json.stringify);
    l_record := customer_json_to_record (l_json);
    DBMS_OUTPUT.put_line ('Customer 1 (Record) (FirstName): ' || l_record.firstname);
    DBMS_OUTPUT.put_line ('Customer 1 (Record) (LastName): ' || l_record.lastname);
    DBMS_OUTPUT.put_line ('Customer 1 (Record) (Line1): ' || l_record.address.line1);
    DBMS_OUTPUT.put_line ('Customer 1 (Record) (Line2): ' || l_record.address.line2);
    DBMS_OUTPUT.put_line ('Customer 1 (Record) (City): ' || l_record.address.city);

    l_json := customer_record_to_json (l_customer2);
    DBMS_OUTPUT.put_line ('Customer 2 (JSON): ' || l_json.stringify);
    l_record := customer_json_to_record (l_json);
    DBMS_OUTPUT.put_line ('Customer 2 (Record) (FirstName): ' || l_record.firstname);
    DBMS_OUTPUT.put_line ('Customer 2 (Record) (LastName): ' || l_record.lastname);
    DBMS_OUTPUT.put_line ('Customer 2 (Record) (Line1): ' || l_record.address.line1);
    DBMS_OUTPUT.put_line ('Customer 2 (Record) (Line2): ' || l_record.address.line2);
    DBMS_OUTPUT.put_line ('Customer 2 (Record) (City): ' || l_record.address.city);
END;
  

Обновить

Можно просто JSON_OBJECT(*) создать объект JSON, но расширение * не будет работать с типами записей, так как вы получите ORA-40579: star expansion is not allowed .

Если вы хотите использовать JSON_OBJECT для создания JSON вместо JSON_OBJECT_T, используемые вами типы должны быть предопределены (не в блоке pl / sql), и вам все равно нужно будет определить каждое поле в структуре JSON. Вам также нужно будет определить тип таблицы типа RT_CUSTOMER, чтобы вы могли выполнять запросы из нее.

 CREATE TYPE RT_Address AS OBJECT (Line1 VARCHAR2 (100), Line2 VARCHAR2 (100), City VARCHAR2 (100));

CREATE TYPE RT_Customer AS OBJECT
(
    FirstName VARCHAR2 (100),
    LastName VARCHAR2 (100),
    Address RT_Address
);

CREATE TYPE rt_customer_t AS TABLE OF rt_customer;

SELECT json_object (
           'firstname' VALUE firstname,
           'lastname' VALUE lastname,
           'address' VALUE
               json_object ('line1' VALUE TREAT (address AS rt_address).line1,
                            'line2' VALUE TREAT (address AS rt_address).line2,
                            'city' VALUE TREAT (address AS rt_address).city)) as customer
  FROM TABLE (
           rt_customer_t (
               RT_Customer ('John',
                            'Doe',
                            rt_address ('123 Main Street', 'Apartment# 2A', 'London')),
               RT_Customer ('Jane',
                            'Smith',
                            rt_address ('456 Broken Dreams Blvd', NULL, 'Greenville'))));