ORACLE解析游标生成JSON

ORACLE解析游标生成JSON

1. 背景

存储过程中使用oracleutl_http调用rest接口,并以JSON的方式传输数据.此需求下,业务和环境有如下限制:

• 业务已经通过sys_refcursor生成了业务数据,不希望重新编写存储过程
  
• 当前ORACLE版本为11G,不支持JSON操作
  

2. 思路

需要将游标转换为文本,有以下两个方法

• 使用游标生成XML,从XML转JSON,比较繁琐,如何生成XML,可参考ORACLE游标序列化
  
• 直接解析sys_refcursor,生成JSON数据,比较合理
  

第一种方法,适合接口为xml正文的接口,比如SOAP协议接口.若需要接口为json正文,还需要将XML转为JSON.需要掌握Oracle中的XML操作
第二种方法,直接转成JSON文本,需要借助DBMS_SQL解析游标数据
不管使用哪种方法,接口传输多为大文本,需要使用DBMS_LOB对文本进行文本操作
3. 实现

主要分为以下几个步骤

• 使用 DBMS_SQL.to_cursor_number 获取游标ID
  
• 使用 DBMS_SQL.DESCRIBE_COLUMNS 获取列数以及列信息
  
• 使用 DBMS_SQL.DEFINE_COLUMN 循环定义列类型
  
• 使用 DBMS_SQL.FETCH_ROWS 遍历数据
  
• 使用 DBMS_SQL.COLUMN_VALUE 获取每一列值
  
• 使用 DBMS_SQL.CLOSE_CURSOR 关闭游标
  

其中能获取到到列信息如下

1. -- author : herbert 公众号: 小满小慢 日期: 2025-11-11
2.   type desc_rec is record (
3.         col_type            binary_integer := 0,
4.         col_max_len         binary_integer := 0,
5.         col_name            varchar2(32)   := '',
6.         col_name_len        binary_integer := 0,
7.         col_schema_name     varchar2(32)   := '',
8.         col_schema_name_len binary_integer := 0,
9.         col_precision       binary_integer := 0,
10.         col_scale           binary_integer := 0,
11.         col_charsetid       binary_integer := 0,
12.         col_charsetform     binary_integer := 0,
13.         col_null_ok         boolean        := TRUE);

复制代码

具体测试代码如下

1. declare
2.   v_cursor_id   NUMBER;
3.   v_col_count   BINARY_INTEGER;
4.   v_col_desc    DBMS_SQL.DESC_TAB;
5.   v_value       VARCHAR2(4000);
6.   v_row_data    VARCHAR2(4000);
7.   v_p_refcursor sys_refcursor;
8. BEGIN
9.   open v_p_refcursor FOR
10.     select '小游戏1' F_A, '地心侠士' F_B
11.       from dual
12.     union all
13.     select '小游戏2', '地心侠士'
14.       from dual;
15.   v_cursor_id := DBMS_SQL.to_cursor_number(v_p_refcursor);
16.   DBMS_SQL.DESCRIBE_COLUMNS(v_cursor_id, v_col_count, v_col_desc);
17.   FOR i IN 1 .. v_col_count LOOP
18.     DBMS_SQL.DEFINE_COLUMN(v_cursor_id, i, v_value, 4000);
19.   END LOOP;
20.   WHILE DBMS_SQL.FETCH_ROWS(v_cursor_id) > 0 LOOP
21.     v_row_data := '';
22.     FOR i IN 1 .. v_col_count LOOP
23.       DBMS_SQL.COLUMN_VALUE(v_cursor_id, i, v_value);
24.       v_row_data := v_row_data ||v_col_desc(i).col_name|| ': ' || v_value ;
25.     END LOOP;
26.     DBMS_OUTPUT.PUT_LINE(v_row_data);
27.   END LOOP;
28.   DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
29. EXCEPTION
30.   WHEN OTHERS THEN
31.     IF DBMS_SQL.IS_OPEN(v_cursor_id) THEN
32.       DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
33.     END IF;
34.     RAISE;
35. END;

复制代码

输出内容如下

1. F_A : 小游戏F_B : 地心侠士
2. F_A : 公众号F_B : 小满小慢

复制代码

我们最终想要的JSON格式如下

1. [{
2.         "F_A": "小游戏",
3.         "F_B": "地心侠士"
4. }, {
5.         "F_A": "公众号",
6.         "F_B": "小满小慢"
7. }]

复制代码

通过上边的示列代码简单修改就完全可以实现了.
4. 总结

我在ORACLE游标序列化中实现了游标转XML文本,可以实现多个动态游标的合并.当时就考虑如何生成JSON文本,没有找到合适的方法.当真实的业务诉求出现以后,结合AI问答,找到了一个实际可行的方法.
需要完整游标转JSON的过程,请关注公众号小满小慢,回复游标转JSON获取完整代码.
原文地址: https://mp.weixin.qq.com/s/9pE3C6TURYNoKVvyhvPj8A

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

收藏一下   不知道什么时候能用到