Oracle/PL/SQL奇技淫巧之Json转表

这篇具有很好参考价值的文章主要介绍了Oracle/PL/SQL奇技淫巧之Json转表。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

在Oracle中,有些时候我们需要在一个json文档中查数据
这个时候我们可以通过JSON_TABLE函数来把 json文档 提取成一张可以执行正常查询操作的表

先看JSON_TABLE函数的基础用法:

JSON_TABLE(json_data, '$.json_path' COLUMNS (column_definitions))

其中:
json_data:要从中提取数据的 JSON文档 或 JSON列
$.json_path:JSON路径表达式,该表达式指定要提取的数据的位置
COLUMNS子句:定义要从JSON数据中提取的列,每个列定义都应该包括列名、数据类型和JSON路径表达式,以指定数据在JSON文档中的位置。

例:

SELECT *
FROM JSON_TABLE('{"name": "John", "age": 30, "city": "New York"}',
                '$' COLUMNS (name VARCHAR2(50) PATH '$.name_',
                         	 age NUMBER PATH '$.age_',
                         	 city VARCHAR2(50) PATH '$.city_'));

这里的json_data='{"name": "John", "age": 30, "city": "New York"}'
$的含义是从JSON文档的根路径提取数据
COLUMNS子句表示将从JSON文档中提取name_age_city_这三列数据分别放入nameagecity列中
注意()中的$表示的路径是基于COLUMNS 前面指定的那个路径,这里是$.$
函数的结果将是一个包含三列的表:nameagecity,其中包含从JSON文档中提取的相应值。

如果JSON文档是一个JSON数组呢?
例如有这样一个json_list文档:

[
  {
    "count": "2",
    "items": [
      {
        "key": "keyOne",
        "value": "valueOne"
      },
      {
        "key": "keyTwo",
        "value": "valueTwo"
      }
    ]
  }
]

虽然里面只有一个JSON对象,但是这个JSON文档是一个JSON数组,用[]包起来的
看看怎么解析它:

 json_table(json_list, 
 			'$[*]' columns (colum_1 VARCHAR2(20) PATH '$.count',
                     		colum_2 VARCHAR2(1000) FORMAT JSON PATH '$.count'));

$[*]的含义是从JSON数组[]里面提取数据,*表示所有元素
COLUMNS子句表示将从JSON文档中提取countcount这两列数据分别放入colum_1colum_2列中
注意columns ()中的$表示的路径是基于COLUMNS 前面指定的那个路径,在这里就是$[*].$
函数的结果将是一个包含两列的表:colum_1colum_2

路径格式:

  1. 取所有元素:$[*],表示取所有元素;
  2. 取指定单个元素:如'$[0]',表示取第一个元素;
  3. 取指定多个元素:如$[0, 2, 4],表示取第一、三、五个元素;
  4. 取范围连续元素:如$[0 TO 2],表示取第一到第三个元素;

如果不指定元素,如$[],则会报错

例:存储过程中把接收到的Clob参数转成Json表

现在后台传递一个Json格式的数据给数据库:

[  
  {  
    "fulfillmentId": "12345",  
    "itemCode": "ABC123",  
    "dataId": "data123",  
    "itemQty": 5,  
    "deliveryId": "deliveryXYZ",  
    "itemid": "item789"  
  },  
  {  
    "fulfillmentId": "54321",  
    "itemCode": "XYZ789",  
    "dataId": "data456",  
    "itemQty": 3,  
    "deliveryId": "deliveryQRS",  
    "itemid": "item098"  
  },  
  // 更多对象...  
]

存储过程要接收这个数据(这里用了Clob类型来接收),并且把他转成一张表,然后遍历取得里面的数据:文章来源地址https://www.toymoban.com/news/detail-654426.html

	PROCEDURE	pro_save_data(v_data_list IN CLOB, v_status OUT NUMBER)
	IS
		v_delivery_id VARCHAR2(20);
		# 定义一个游标,接收一个CLOB类型的参数,参数名随意,这里是v_list
		CURSOR	v_cur(v_list IN CLOB) IS
			SELECT t.*
			FROM JSON_TABLE(v_data_list, '$[*]' COLUMNS(fulfillment_id VARCHAR2(20) PATH '$.fulfillmentId',
														item_code VARCHAR2(20) PATH '$.itemCode',
														data_id VARCHAR2(20) PATH '$.dataId',
														item_qty NUMBER PATH '$.itemQty',
														delivery_id VARCHAR2(20) PATH '$.deliveryId',
														item_id VARCHAR2(255) PATH '$.itemid'
											           )
						   ) t; 
	BEGIN
		# 用For循环遍历游标,把v_data_list传给游标
		FOR v_rec IN v_cur(v_data_list) LOOP
			INSERT INTO table_one
			VALUES( v_rec.data_id , 
			        v_rec.fulfillmentId, 
			        v_rec.item_code, 
			        v_rec.item_qty, 
			        v_rec.delivery_id , 
			        v_rec.item_id, 
			        NULL);
		END LOOP;

		SELECT json_value(v_data_list, '$[0].deliveryId')
		INTO v_delivery_id
		FROM dual;
		
		COMMIT;

		v_status := 0;

	EXCEPTION
		WHEN OTHERS THEN
			ROLLBACK;
			v_status := 1; -- Fail to save 

	END pro_save_data;

到了这里,关于Oracle/PL/SQL奇技淫巧之Json转表的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 奇技淫巧第8期

    学无止境。 下面是对去年11月至今年5月的零散知识点总结。 春节期间好好放松了一两个月,来校后又懒散的度过了一两个月,直到论文评审意见下来,才开启冲刺模式狂干了一两个月。总的来说,这半年来摸的时间比较多。好,不废话了,开始。 https://zhuanlan.zhihu.com/p/4595

    2024年02月04日
    浏览(29)
  • matlab奇技淫巧——绘制三维地图

      在数据处理工作中,常常会用到地图的绘制,最常用的自然是绘制平面的区域/全球地图,通过 即可绘制,效果如下,其中经度为 − 180 ∼ 180 -180sim180 − 180 ∼ 180 ,负为西经,正为东经,纬度为 − 90 ∼ 90 -90sim90 − 90 ∼ 90 ,北纬为正,南纬为负。   不过本博文题目

    2024年02月07日
    浏览(53)
  • 记录--`ElementUI` 中的奇技淫巧

    在 ElementUI 的世界中,不仅有基础的组件和功能,还有一些让你眼前一亮、 * 得不能再 * 的高级技巧和窍门。本文将揭示这些技巧,让你在前端开发的舞台上独领风骚。无论你是一个勇敢的创新者还是一个喜欢调皮捣蛋的开发者,这些技巧都将让你的 ElementUI 应用更加酷炫和有

    2024年02月08日
    浏览(32)
  • 奇技淫巧:Lambda表达式

    最近学习到的奇技淫巧: Lambda表达式 ,将函数包括递归函数改为Lambda表达式写法,可节省大量时间,在大量调用下可能节省近一半时间。 该语法过于复杂,见https://en.cppreference.com/w/cpp/language/lambda,本文仅写在算法竞赛下的应用。 该语法在OIWiki中有所提及,但是十分抽象,

    2024年02月12日
    浏览(27)
  • Intellij IDEA有什么奇技淫巧?

    IDEA全称 IntelliJIDEA,是java语言开发的集成环境,IntelliJ在业界被公认为最好的java开发工具之一,尤其在 智能代码助手、代码自动提示、重构、J2EE支持、Ant、JUnit、CVS整合、代码审查、创新的GUI设计 等方面的功能可以说是超常的。 idea下载地址:jetbrains.com/idea 下面来说几个I

    2024年02月15日
    浏览(37)
  • 一看就懂的OpenGL ES教程——仿抖音滤镜的各种奇技淫巧(一)_opengl es添加视频

    上一篇文章一看就懂的OpenGL ES教程——渲染宫崎骏动漫重拾童年 已经详细阐述了如何用OpenGL es将原始的YUV数据组成的视频渲染到屏幕上,想必有很多童鞋在阅读了它之后依然觉得回味无穷,学习的胃口也越来越大了,因为你们知道仅仅渲染视频是不够的,我们要的是,能够在

    2024年04月25日
    浏览(42)
  • 一看就懂的OpenGL ES教程——仿抖音滤镜的各种奇技淫巧(一)_opengl es添加视频(1)

    自我介绍一下,小编13年上海交大毕业,曾经在小公司待过,也去过华为、OPPO等大厂,18年进入阿里一直到现在。 深知大多数HarmonyOS鸿蒙开发工程师,想要提升技能,往往是自己摸索成长或者是报班学习,但对于培训机构动则几千的学费,着实压力不小。自己不成体系的自学

    2024年04月16日
    浏览(42)
  • ORACLE数据库 —— PL/SQL知识点2

    ORACLE数据库  PL/SQL语句基础知识点  适合有SQL基础的人群。  禁止转载! 内置函数 字符串函数     lower(列名|字符串)函数用于返回字符串的小写形式。         eg.SELECT ename,sal FROM emp WHERE ename=lower(\\\'ename\\\');     upper(列名|字符串)函数用于返回字符串的大写形式。       

    2024年02月08日
    浏览(50)
  • Oracle/PL/SQL数据库基础操作(持续更新)

            PL/SQL不是一个独立的编程语言;它是Oracle编程环境中的工具。 SQL* Plus是一个互动的工具,它可以在命令提示符下键入SQL和PL/SQL语句。这些命令发送到数据库进行处理。语句处理之后将结果发回,并在屏幕上显示出来。 分类 命令 DDL create:创建;drop:删除;alter:

    2024年02月09日
    浏览(59)
  • 用PL/SQL Developer连接远程Oracle数据库

    oracle数据库安装教程参考 注意: 第六步中的Oracle基目录中的用户名为中文的话,需要去掉中文部分。然后管理口令一定保存好。 2.打开oracle安装目录(默认在C盘,我的在D盘),找到tnsnames.ora文件 这是oracle客户端所需要的一个文件,通过该文件可以配置数据库的连接地址,

    2024年02月13日
    浏览(42)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包