如何使用APEX_JSON获取PL/SQL中JSON表结构的数组对象

先看看你提供的原始PL/SQL代码片段：

declare l_json_doc VARCHAR2(32767); l_numcols number; l_numrows number;
begin
l_json_doc := '{ "table": { "name": "sometablename", "numofcolumns": 5, "numofrows": 5, "colheadings": [{ "colname": "customcol1", "coltype": "number" }, { "colname": "customcol2", "coltype": "varchar2" }, { "colname": "customcol3", "coltype": "varchar2" }, { "colname": "customcol4", "coltype": "varchar2" }, { "colname": "customcol5", "coltype": "number" } ], "data": [{ "customcol1": "datacolumn1", "customcol2": "datac..." }]}';

要提取里面的colheadings和data数组，咱们可以用APEX_JSON的解析和遍历功能，直接上完整的实现代码，我会在里面加注释说明：

declare 
  l_json_doc VARCHAR2(32767); 
  l_numcols number; 
  l_numrows number;
  l_col_count number; -- 存储colheadings数组的元素数量
  l_data_count number; -- 存储data数组的元素数量
  i number; -- 循环计数器
begin
  l_json_doc := '{ "table": { "name": "sometablename", "numofcolumns": 5, "numofrows": 5, "colheadings": [{ "colname": "customcol1", "coltype": "number" }, { "colname": "customcol2", "coltype": "varchar2" }, { "colname": "customcol3", "coltype": "varchar2" }, { "colname": "customcol4", "coltype": "varchar2" }, { "colname": "customcol5", "coltype": "number" } ], "data": [{ "customcol1": "datacolumn1", "customcol2": "datac..." }]}';
  
  -- 第一步：解析JSON字符串，加载到APEX_JSON的解析器中
  apex_json.parse(l_json_doc);
  
  -- 提取colheadings数组
  dbms_output.put_line('=== 提取colheadings数组内容 ===');
  -- 获取colheadings数组的元素个数
  l_col_count := apex_json.get_count(p_path => 'table.colheadings');
  -- 循环遍历每个数组元素
  for i in 1..l_col_count loop
    -- 提取当前元素的colname和coltype
    dbms_output.put_line('第' || i || '个列标题:');
    dbms_output.put_line('  colname: ' || apex_json.get_varchar2(p_path => 'table.colheadings[%d].colname', p0 => i));
    dbms_output.put_line('  coltype: ' || apex_json.get_varchar2(p_path => 'table.colheadings[%d].coltype', p0 => i));
  end loop;
  
  -- 提取data数组
  dbms_output.put_line(chr(10) || '=== 提取data数组内容 ===');
  -- 获取data数组的元素个数
  l_data_count := apex_json.get_count(p_path => 'table.data');
  -- 循环遍历每个数组元素
  for i in 1..l_data_count loop
    dbms_output.put_line('第' || i || '行数据:');
    -- 这里可以根据colheadings里的列名来提取对应的数据，或者直接按路径取
    dbms_output.put_line('  customcol1: ' || apex_json.get_varchar2(p_path => 'table.data[%d].customcol1', p0 => i));
    dbms_output.put_line('  customcol2: ' || apex_json.get_varchar2(p_path => 'table.data[%d].customcol2', p0 => i));
    -- 如果有更多列，可以继续添加提取语句
  end loop;
  
end;
/

关键知识点说明： #

• apex_json.parse(l_json_doc)：把JSON字符串加载到解析器中，后续才能通过路径访问里面的元素。
• apex_json.get_count(p_path)：获取指定路径下数组的元素数量，用来确定循环的范围。
• apex_json.get_varchar2(p_path, p0)：通过带占位符%d的路径，结合循环计数器i（p0参数）来访问数组的第i个元素，提取对应的字段值。如果字段是数字类型，可以用get_number方法。

你可以把这段代码放到PL/SQL编辑器里运行，开启DBMS输出就能看到提取后的结果啦。

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