refer cursor,sql转json数据

最新推荐文章于 2021-09-30 22:17:23 发布
最新推荐文章于 2021-09-30 22:17:23 发布
阅读量2.5k 收藏 2
点赞数
分类专栏： oracle_basic oracle_cursor oracle_sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/rudygao/article/details/38341313
版权

1.前提条件,安装json的包

2.创建JSON_UTIL_PKG

CREATE OR REPLACE PACKAGE JSON_UTIL_PKG AUTHID CURRENT_USER AS

  /*
  
  Purpose:    JSON utilities for PL/SQL
  see http://ora-00001.blogspot.com/
  
  Remarks:
  
  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     30.01.2010  Created
  JKR     01.05.2010  Edited to fit in PL/JSON
  JKR     19.01.2011  Newest stylesheet + bugfix handling
  
  */

  -- generate JSON from REF Cursor
  FUNCTION REF_CURSOR_TO_JSON(P_REF_CURSOR IN SYS_REFCURSOR,
                              P_MAX_ROWS   IN NUMBER := NULL,
                              P_SKIP_ROWS  IN NUMBER := NULL) RETURN JSON_LIST;

  -- generate JSON from SQL statement
  FUNCTION SQL_TO_JSON(P_SQL IN VARCHAR2, P_MAX_ROWS IN NUMBER := NULL, P_SKIP_ROWS IN NUMBER := NULL) RETURN JSON_LIST;

END JSON_UTIL_PKG;
create or replace package body json_util_pkg
as
  scanner_exception exception;
  pragma exception_init(scanner_exception, -20100);
  parser_exception exception;
  pragma exception_init(parser_exception, -20101);

  /*

  Purpose:    JSON utilities for PL/SQL

  Remarks:    

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     30.01.2010  Created
  
  */


  g_json_null_object             constant varchar2(20) := '{ }';


function get_xml_to_json_stylesheet return varchar2
as
begin

  /*

  Purpose:    return XSLT stylesheet for XML to JSON transformation

  Remarks:    see http://code.google.com/p/xml2json-xslt/

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     30.01.2010  Created
  MBR     30.01.2010  Added fix for nulls
  
  */


  return q'^<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output indent="no" omit-xml-declaration="yes" method="text" encoding="UTF-8" media-type="text/x-json"/>
        <xsl:strip-space elements="*"/>
  <!--contant-->
  <xsl:variable name="d">0123456789</xsl:variable>

  <!-- ignore document text -->
  <xsl:template match="text()[preceding-sibling::node() or following-sibling::node()]"/>

  <!-- string -->
  <xsl:template match="text()">
    <xsl:call-template name="escape-string">
      <xsl:with-param name="s" select="."/>
    </xsl:call-template>
  </xsl:template>
  
  <!-- Main template for escaping strings; used by above template and for object-properties 
       Responsibilities: placed quotes around string, and chain up to next filter, escape-bs-string -->
  <xsl:template name="escape-string">
    <xsl:param name="s"/>
    <xsl:text>"</xsl:text>
    <xsl:call-template name="escape-bs-string">
      <xsl:with-param name="s" select="$s"/>
    </xsl:call-template>
    <xsl:text>"</xsl:text>
  </xsl:template>
  
  <!-- Escape the backslash (\) before everything else. -->
  <xsl:template name="escape-bs-string">
    <xsl:param name="s"/>
    <xsl:choose>
      <xsl:when test="contains($s,'\')">
        <xsl:call-template name="escape-quot-string">
          <xsl:with-param name="s" select="concat(substring-before($s,'\'),'\\')"/>
        </xsl:call-template>
        <xsl:call-template name="escape-bs-string">
          <xsl:with-param name="s" select="substring-after($s,'\')"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:call-template name="escape-quot-string">
          <xsl:with-param name="s" select="$s"/>
        </xsl:call-template>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
  
  <!-- Escape the double quote ("). -->
  <xsl:template name="escape-quot-string">
    <xsl:param name="s"/>
    <xsl:choose>
      <xsl:when test="contains($s,'"')">
        <xsl:call-template name="encode-string">
          <xsl:with-param name="s" select="concat(substring-before($s,'"'),'\"')"/>
        </xsl:call-template>
        <xsl:call-template name="escape-quot-string">
          <xsl:with-param name="s" select="substring-after($s,'"')"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:call-template name="encode-string">
          <xsl:with-param name="s" select="$s"/>
        </xsl:call-template>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
  <xsl:template name="encode-string">
    <xsl:param name="s"/>
    <xsl:choose>
      <!-- tab -->
      <xsl:when test="contains($s,'	')">
        <xsl:call-template name="encode-string">
          <xsl:with-param name="s" select="concat(substring-before($s,'	'),'\t',substring-after($s,'	'))"/>
        </xsl:call-template>
      </xsl:when>
      <!-- line feed -->
      <xsl:when test="contains($s,'
')">
        <xsl:call-template name="encode-string">
          <xsl:with-param name="s" select="concat(substring-before($s,'
'),'\n',substring-after($s,'
'))"/>
        </xsl:call-template>
      </xsl:when>
      <!-- carriage return -->
      <xsl:when test="contains($s,'
')">
        <xsl:call-template name="encode-string">
          <xsl:with-param name="s" select="concat(substring-before($s,'
'),'\r',substring-after($s,'
'))"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise><xsl:value-of select="$s"/></xsl:otherwise>
    </xsl:choose>
  </xsl:template>

  <!-- number (no support for javascript mantissa) -->
  <xsl:template match="text()[not(string(number())='NaN' or
                      (starts-with(.,'0' ) and . != '0' and
not(starts-with(.,'0.' ))) or
                      (starts-with(.,'-0' ) and . != '-0' and
not(starts-with(.,'-0.' )))
                      )]">
    <xsl:value-of select="."/>
  </xsl:template>

  <!-- boolean, case-insensitive -->
  <xsl:template match="text()[translate(.,'TRUE','true')='true']">true</xsl:template>
  <xsl:template match="text()[translate(.,'FALSE','false')='false']">false</xsl:template>

  <!-- object -->
  <xsl:template match="*" name="base">
    <xsl:if test="not(preceding-sibling::*)">{</xsl:if>
    <xsl:call-template name="escape-string">
      <xsl:with-param name="s" select="name()"/>
    </xsl:call-template>
    <xsl:text>:</xsl:text>
    <!-- check type of node -->
    <xsl:choose>
      <!-- null nodes -->
      <xsl:when test="count(child::node())=0">null</xsl:when>
      <!-- other nodes -->
      <xsl:otherwise>
        <xsl:apply-templates select="child::node()"/>
      </xsl:otherwise>
    </xsl:choose>
    <!-- end of type check -->
    <xsl:if test="following-sibling::*">,</xsl:if>
    <xsl:if test="not(following-sibling::*)">}</xsl:if>
  </xsl:template>

  <!-- array -->
  <xsl:template match="*[count(../*[name(../*)=name(.)])=count(../*) and count(../*)>1]">
    <xsl:if test="not(preceding-sibling::*)">[</xsl:if>
    <xsl:choose>
      <xsl:when test="not(child::node())">
        <xsl:text>null</xsl:text>
      </xsl:when>
      <xsl:otherwise>
        <xsl:apply-templates select="child::node()"/>
      </xsl:otherwise>
    </xsl:choose>
    <xsl:if test="following-sibling::*">,</xsl:if>
    <xsl:if test="not(following-sibling::*)">]</xsl:if>
  </xsl:template>
  
  <!-- convert root element to an anonymous container -->
  <xsl:template match="/">
    <xsl:apply-templates select="node()"/>
  </xsl:template>
    
</xsl:stylesheet>^';

end get_xml_to_json_stylesheet;


function ref_cursor_to_json (p_ref_cursor in sys_refcursor,
                             p_max_rows in number := null,
                             p_skip_rows in number := null) return json_list
as
  l_ctx         dbms_xmlgen.ctxhandle;
  l_num_rows    pls_integer;
  l_xml         xmltype;
  l_json        xmltype;
  l_returnvalue clob;
begin

  /*

  Purpose:    generate JSON from REF Cursor

  Remarks:    

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     30.01.2010  Created
  JKR     01.05.2010  Edited to fit in PL/JSON
  
  */

  l_ctx := dbms_xmlgen.newcontext (p_ref_cursor);
  
  dbms_xmlgen.setnullhandling (l_ctx, dbms_xmlgen.empty_tag);
  
  -- for pagination

  if p_max_rows is not null then
    dbms_xmlgen.setmaxrows (l_ctx, p_max_rows);
  end if;
  
  if p_skip_rows is not null then
    dbms_xmlgen.setskiprows (l_ctx, p_skip_rows);
  end if;
  
  -- get the XML content
  l_xml := dbms_xmlgen.getxmltype (l_ctx, dbms_xmlgen.none);
  
  l_num_rows := dbms_xmlgen.getnumrowsprocessed (l_ctx);
  
  dbms_xmlgen.closecontext (l_ctx);
  
  close p_ref_cursor;

  if l_num_rows > 0 then
    -- perform the XSL transformation
    l_json := l_xml.transform (xmltype(get_xml_to_json_stylesheet));
    l_returnvalue := l_json.getclobval();
  else
    l_returnvalue := g_json_null_object;
  end if;

  l_returnvalue := dbms_xmlgen.convert (l_returnvalue, dbms_xmlgen.entity_decode);
  
  if(l_num_rows = 0) then
    return json_list();
  else 
    if(l_num_rows = 1) then
      declare ret json_list := json_list();
      begin
        ret.append(
          json(
            json(l_returnvalue).get('ROWSET')
          ).get('ROW')
        );
        return ret;
      end;
    else 
      return json_list(json(l_returnvalue).get('ROWSET'));
    end if;
  end if;
  
exception
  when scanner_exception then
    dbms_output.put('Scanner problem with the following input: ');
    dbms_output.put_line(l_returnvalue);
    raise;
  when parser_exception then
    dbms_output.put('Parser problem with the following input: ');
    dbms_output.put_line(l_returnvalue);
    raise;
  when others then raise;  
end ref_cursor_to_json;

function sql_to_json (p_sql in varchar2,
                      p_max_rows in number := null,
                      p_skip_rows in number := null) return json_list
as
  v_cur sys_refcursor;
begin
  open v_cur for p_sql;
  return ref_cursor_to_json(v_cur, p_max_rows, p_skip_rows);

end sql_to_json;


end json_util_pkg;

3. 测试 

DECLARE
  V_REFER     SYS_REFCURSOR;
  V_JSON_LIST JSON_LIST;
BEGIN
  OPEN V_REFER FOR
    SELECT * FROM EMP;
  V_JSON_LIST := JSON_UTIL_PKG.REF_CURSOR_TO_JSON(V_REFER);
  V_JSON_LIST.PRINT;
END;
[{
  "EMPNO" : 7369,
  "ENAME" : "SMITH",
  "JOB" : "CLERK",
  "MGR" : 7902,
  "HIREDATE" : "17-DEC-80",
  "SAL" : 800,
  "COMM" : null,
  "DEPTNO" : 20
}, {
  "EMPNO" : 7499,
  "ENAME" : "ALLEN",
  "JOB" : "SALESMAN",
  "MGR" : 7698,
  "HIREDATE" : "20-FEB-81",
  "SAL" : 1600,
  "COMM" : 300,
  "DEPTNO" : 30
}, {
  "EMPNO" : 7521,
  "ENAME" : "WARD",
  "JOB" : "SALESMAN",
  "MGR" : 7698,
  "HIREDATE" : "22-FEB-81",
  "SAL" : 1250,
  "COMM" : 500,
  "DEPTNO" : 30
}, {
  "EMPNO" : 7566,
  "ENAME" : "JONES",
  "JOB" : "MANAGER",
  "MGR" : 7839,
  "HIREDATE" : "02-APR-81",
  "SAL" : 2975,
  "COMM" : null,
  "DEPTNO" : 20
}, {
  "EMPNO" : 7654,
  "ENAME" : "MARTIN",
  "JOB" : "SALESMAN",
  "MGR" : 7698,
  "HIREDATE" : "28-SEP-81",
  "SAL" : 1250,
  "COMM" : 1400,
  "DEPTNO" : 30
}, {
  "EMPNO" : 7698,
  "ENAME" : "BLAKE",
  "JOB" : "MANAGER",
  "MGR" : 7839,
  "HIREDATE" : "01-MAY-81",
  "SAL" : 2850,
  "COMM" : null,
  "DEPTNO" : 30
}, {
  "EMPNO" : 7782,
  "ENAME" : "CLARK",
  "JOB" : "MANAGER",
  "MGR" : 7839,
  "HIREDATE" : "09-JUN-81",
  "SAL" : 2450,
  "COMM" : null,
  "DEPTNO" : 10
}, {
  "EMPNO" : 7788,
  "ENAME" : "SCOTT",
  "JOB" : "ANALYST",
  "MGR" : 7566,
  "HIREDATE" : "19-APR-87",
  "SAL" : 3000,
  "COMM" : null,
  "DEPTNO" : 20
}, {
  "EMPNO" : 7839,
  "ENAME" : "KING",
  "JOB" : "PRESIDENT",
  "MGR" : null,
  "HIREDATE" : "17-NOV-81",
  "SAL" : 5000,
  "COMM" : null,
  "DEPTNO" : 10
}, {
  "EMPNO" : 7844,
  "ENAME" : "TURNER",
  "JOB" : "SALESMAN",
  "MGR" : 7698,
  "HIREDATE" : "08-SEP-81",
  "SAL" : 1500,
  "COMM" : 0,
  "DEPTNO" : 30
}, {
  "EMPNO" : 7876,
  "ENAME" : "ADAMS",
  "JOB" : "CLERK",
  "MGR" : 7788,
  "HIREDATE" : "23-MAY-87",
  "SAL" : 1100,
  "COMM" : null,
  "DEPTNO" : 20
}, {
  "EMPNO" : 7900,
  "ENAME" : "JAMES",
  "JOB" : "CLERK",
  "MGR" : 7698,
  "HIREDATE" : "03-DEC-81",
  "SAL" : 950,
  "COMM" : null,
  "DEPTNO" : 30
}, {
  "EMPNO" : 7902,
  "ENAME" : "FORD",
  "JOB" : "ANALYST",
  "MGR" : 7566,
  "HIREDATE" : "03-DEC-81",
  "SAL" : 3000,
  "COMM" : null,
  "DEPTNO" : 20
}, {
  "EMPNO" : 7934,
  "ENAME" : "MILLER",
  "JOB" : "CLERK",
  "MGR" : 7782,
  "HIREDATE" : "23-JAN-82",
  "SAL" : 1300,
  "COMM" : null,
  "DEPTNO" : 10
}]



rudy_gao
关注
专栏目录
（第75天）AutoUpgrade 升级：11GR2 到 19C
LuciferLiu_DBA
03-24 405
Oracle 从 12CR2 开始就开始大力推广 AutoUpgrade 升级工具，但是实际生产中使用并不广泛，但是这个工具已经发展的非常成熟了。本文介绍一下如何使用 AutoUpgrade 工具进行快速升级，操作非常简单。
Python中的数据读入、写出以及交换
jason_cuijiahui的博客
07-06 7578
b本章会涉及不同类型的数据存储，它们基于不同的目的进行优化：普通文件、结构化文件和数据库。普通文件输入/输出数据持久化最简单的类型是普通文件，有时也叫平面文件（flat file）。它仅仅是在一个文件名下的字节流，把数据从一个文件读入内存，然后从内存写入文件。打开文件# fileobj是open()返回的文件对象 # filename是该文件的字符串名 # mode是指明文件类型和操作的字符串 fi
MySql 使用游标把查询出转化为json字符串
书香水墨
07-05 364
表 (key_value) 结构 key_id key_name key_value 1 username 123 1 age 18 定义函数，转化查询结果为JSON格式 # 重新定义结束符号 DELIMITER $$ DROP FUNCTION IF EXISTS getJson $$ create function getJson (key_id varchar(10)) returns varchar(1000) begin # 定义参数 declare str .
oracle游标转json字符串函数,将pymongo光标转换为json
weixin_42299257的博客
04-03 194
>我知道这是一个相当普遍的问题。我正在写一个小的Flask应用程序，我正在尝试向视图提供一些查询。我已经连接到我的本地MongoDB设置，并成功查询 – 但是我不能生成一个json对象。我看到的最常见的解决方案是从pymongo导入json_util，即import jsonfrom pymongo import json_utilresults = connection.get_colle...
oracle游标转json字符串函数,将pymongo游标转换为json
weixin_31706903的博客
04-03 325
我知道这是一个相当普遍的问题 . 我试图将一些查询反馈给视图 . 我've connected to my local MongoDB setup, and made a successful query - but I can' t用它生成一个json对象 .我见过的最常见的解决方案是从pymongo导入json_util，即import jsonfrom pymongo import json...
oracle游标转json,如何在android中将游标对象转换为XML或JSON
weixin_39613433的博客
04-11 134
是否有任何库可用于将Cursor对象转换为直接XML或JSON.例如,我们有一个表有两列id,name.当我从DB获取游标对象时.使用循环游标我想将其转换为1xyz提前致谢.解决方法:用这个：JSONArray array = new JSONArray();if (cursor != null && cursor.getCount() > 0) {while (cursor...
REF Cursor to JSON
whz110的专栏
09-09 259
REF Cursors are cool. They allow you to encapsulate SQL queries behind a PL/SQL package API. For example, you can create a function called GET_EMPLOYEES that returns a SYS_REFCURSOR containing the emp...
Elasticsearch6.4专题之17:SQL Access
天空之蓝的博客
11-10 603
SQL Access 文章目录SQL Access概述简介为什么选择Elasticsearch SQL本机集成没有外部零件轻巧高效入门Conventions and Terminology（约定和术语）Mapping concepts across SQL and Elasticsearch（SQL和ES的概念映射）SecuritySQL REST APISQL Translate APISQL ...
pl sql面试题_PL SQL面试问答
从零开始的教程世界
07-12 4904
pl sql面试题If you have worked on Oracle database and going for an interview, you should go through PL SQL interview questions and answers. 如果您曾经在Oracle数据库上工作过并且要进行面试，则应该阅读PL SQL面试问题和答案。 PLSQL stands fo...
【译】数据库基础：用 Go 从零开始写一个 SQL 数据库 —— 第一部分
suhanyujie的专栏
03-17 213
Database basics: writing a SQL database from scratch in Go 译文 原文链接：http://notes.eatonphil.com/database-basics.html 原文作者：https://github.com/eatonphil 译文来自：https://github.com/suhanyujie/article-trans...
【ORACLE】记录pljson_util_pkg.sql_to_json中的数字格式转换问题
DarkAthena的博客
09-30 1222
JSON(JavaScript Object Notation) 是一种轻量级的数据交换格式。它基于JavaScript（Standard ECMA-262 3rd Edition - December 1999）的一个子集。 JSON采用完全独立于语言的文本格式，但是也使用了类似于C语言家族的习惯（包括C, C++, C#, Java, JavaScript, Perl, Python等）。这些特性使JSON成为理想的数据交换语言。易于人阅读和编写，同时也易于机器解析和生成。 目前绝大多数的api接口都采
Java JsonParseException异常的解决
热门推荐
我的博客
10-29 4万+
在开发工作过程中，有遇到以下异常的其实是参数异常，也就是json的拼装有问题。 在接口对接的时候，对方传过来的json解析报错： org.codehaus.jackson.JsonParseException: Unexpected character ('c' (code 99)): was expecting double-quote to start field name at [S...
json串反转义（消除反斜杠）
admin_Object的博客
03-25 6908
json字符串的值中包含反斜杠 {"image":"xxx.jpg","sku":"A301","name":"IPhone11\"5G","status":"有货"} 其实它在字符串里面是这样显示的 {\"image\":\"xxx.jpg\",\"sku\":\"A301\",\"name\":\"IPhone11\\\"5G\",\"status\":\"有货\"} 执行 JSON.parseObject("{"image":"xxx.jpg","sku":"A301","nam
mongo的cursor类型转json
cornorghost的博客
06-13 2265
之前做mongodb的查询并以json数据格式返回，会遇到_id关键字无法转换，然后上网看了很多资料有一些方法，比如用库或者手动去掉啥的，今天说一个简便的方法，就是查询的时候，不返回_id的值就好了，比如 db.collection.find({'地区':'福建省','科类':'文科'},{'_id':0}) 这里指定了_id为0，则不会返回，然后再 json.dumps(list(cursor...
成功解决org.yaml.snakeyaml.scanner.ScannerException: mapping values are not allowed
yjt520557的博客
01-31 3万+
出现这个问题报错的原因是由于yml文件的格式要求很严格，你需要在每个冒号后边添加上空格 如下即可
org.json.JSONException
hunterpanda的博客
06-26 706
JSONObject reqBody = new JSONObject(); reqBody.put("fbs",fbs); 错误信息： org.json.JSONException: JSON does not allow non-finite numbers. 错误原因为fsb的值为NaN   判断一个值是否为NaN：Boolean flag = Double.isNaN(fb...
SpringBoot-错误:Caused by: org.yaml.snakeyaml.scanner.ScannerException
ZPHTTT的博客
08-09 443
SpringBoot-错误:Caused by: org.yaml.snakeyaml.scanner.ScannerException
Android中org.json.JSONObject按键取值报JSONException解决方法
海阔天空
01-14 3530
应用场景在各类接口转换时，使用到JSONObject转换bean或取某个key值时。报异常原因当key在JSONObject中不存在时，无法取到相应的值，此时JSONObject会抛出JSONException。看源码： /** * Returns the value mapped by {@code name}. * * @throws JSONExceptio
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值