此文章包含MySQL、Oracle和Sql Server這三種數據庫的創建json格式入參并解析的主子表存儲

文章目錄

• 此文章包含MySQL、Oracle和Sql Server這三種數據庫的創建json格式入參并解析的主子表存儲
• • 一、MySQL
  • • MySQL5.7存儲過程是自帶解析json的方法的。
  • 二、Oracle
  • • Oracle解析json之前需先導入需要用到一個 PLJSON 的開源項目
  • 三、Sql Server
  • • SqlServer解析json之前需先引入函數 目前支持2008版本 其他版本暫無測試

一、MySQL

MySQL5.7存儲過程是自帶解析json的方法的。

下面為MySQL的測試存儲過程的建表語句。

// 主表 CREATE TABLE `demo` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) COLLATE utf8_bin DEFAULT NULL,`number` int(255) DEFAULT NULL,PRIMARY KEY (`id`) ) //子表 CREATE TABLE `demo_item` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) COLLATE utf8_bin DEFAULT NULL,`number` varchar(255) COLLATE utf8_bin DEFAULT NULL,`demo_number` varchar(255) COLLATE utf8_bin DEFAULT NULL,PRIMARY KEY (`id`) )

下面為MySQL的測試存儲過程的存儲過程建立語句。

CREATE DEFINER=`root`@`localhost` PROCEDURE `demo`(json LONGTEXT CHARACTER SET utf8) BEGINDECLARE i INT; #定義循環SET i = 0; INSERT INTO mysql.`demo` (`name`,`number`) VALUES ( JSON_UNQUOTE(JSON_EXTRACT(json, CONCAT('$.name'))),#獲取主表name JSON_UNQUOTE(JSON_EXTRACT(json, CONCAT('$.number'))));#獲取主表numberWHILE i< JSON_LENGTH(json, "$.data") DO #循環獲取json對象數組長度INSERT INTO mysql.`demo_item` (`name`,`number`,`demo_number`) VALUES ( JSON_UNQUOTE(JSON_EXTRACT(json, CONCAT('$.data[',i,'].name_item'))),#獲取子表name JSON_UNQUOTE(JSON_EXTRACT(json, CONCAT('$.data[',i,'].number'))),#獲取子表number JSON_UNQUOTE(JSON_EXTRACT(json, CONCAT('$.number'))));#關聯主表numberSELECT JSON_UNQUOTE(JSON_EXTRACT(json, CONCAT('$.data[',i,'].name_item')));#查詢打印信息 SELECT JSON_EXTRACT(json, CONCAT('$.data[',i,'].number'));#查詢打印信息 SET i = i+1; END WHILE;END

關鍵字講解

語法：
JSON_EXTRACT(json_doc, path[, path] …)

用法提示：

• 如果json字符串不是數組，則直接使用 $.字段名
• 如果json字符串是數組[Array]，則直接使用 $[對應的索引ID]

JSON_UNQUOTE 是去除json字符串的引號，將值轉成string類型(這里不加json_unquote的話值將會保存到數據庫格式為：'xxx’帶引號格式的)

MySQL具體json操作方法詳情借鑒::https://blog.csdn.net/weixin_29920889/article/details/113430682

下面為MySQL的測試存儲過程的存儲過程入參json。
Navicat Premium執行json入參時不可包含換行，所以這里貼了一個未格式化的json

//入參json {"name": "主表","number": 1,"data": [{"name_item": "子表1","number": 2},{"name_item": "子表2","number": 3}]} //格式化后的入參json {"name": "主表","number": 1,"data": [{"name_item": "子表1","number": 2},{"name_item": "子表2","number": 3}] }

運行后保存數據庫里的結果值

二、Oracle

Oracle解析json之前需先導入需要用到一個 PLJSON 的開源項目

下載下來安裝
https://github.com/pljson/pljson

具體安裝參考：
https://blog.csdn.net/cctvcqupt/article/details/62368230

下面為Oracle的測試存儲過程的建表語句。

// 主表 CREATE TABLE "LTEST"."DEMO_ITEM" ( "ID" NUMBER, "NAME" VARCHAR2(255), "NUM" NUMBER, "DEMO_NUM" NUMBER, PRIMARY KEY ("ID")USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "LTEST" ENABLE) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "LTEST" //子表 CREATE TABLE "LTEST"."DEMO_ITEM" ( "ID" NUMBER, "NAME" VARCHAR2(255), "NUM" NUMBER, "DEMO_NUM" NUMBER, PRIMARY KEY ("ID")USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "LTEST" ENABLE) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "LTEST"

下面為Oracle的測試存儲過程的存儲過程建立語句。

CREATE OR REPLACE PROCEDURE "demo_x"( json6 IN VARCHAR2 ) AS id NUMBER(20); name VARCHAR2(20); num NUMBER(20); item_id NUMBER(20); item_name VARCHAR2(20); item_num NUMBER(20); json1 json; jsonlist json_list; itemjson json; BEGIN json1 := json (json6); dbms_output.put_line (json6); id := json_ext.get_number(json1,'id'); name :=json_ext.get_string (json1,'name'); num :=json_ext.get_number (json1,'num');INSERT INTO DEMO(ID,NAME,NUM) VALUES (id,name,num);--主表保存jsonlist := json_ext.get_json_list (json1,'data'); IF jsonlist IS NOT NULL THEN FOR i IN 1 .. jsonlist.count LOOPitemjson := json(jsonlist.get(i));item_id :=json_ext.get_number (itemjson,'item_id'); item_name :=json_ext.get_string (itemjson,'item_name'); item_num :=json_ext.get_number (itemjson,'item_num');INSERT INTO DEMO_ITEM(ID,NAME,NUM,DEMO_NUM) VALUES(item_id,item_name,item_num,num);END LOOP; END IF;END;

關鍵字講解
Oracle需要定義類似一種變量來接收值,賦值方法 xx ：= xx
控制臺打印 dbms_output.put_line (變量);

json_ext.get_numbe
json_ext.get_string
獲取對應類型的值，詳情自行百度(個人編寫的時候簡單搜了一下，暫沒找可以參考的)

下面為Oracle的測試存儲過程的存儲過程入參json。
Navicat Premium執行json入參時不可包含換行，所以這里貼了一個未格式化的json

注：Oracle創建表的時候可以設置ID自增(json為ID沒有自增的時候創建的,如有報錯刪除json.id)

//入參json {"id": 1,"name": "主表","num":1,"data": [{"item_id":1,"item_name": "子表1","item_num": 1},{"item_id": 2,"item_name": "子表2","item_num": 1}]} //格式化后的入參json {"id": 1,"name": "主表","num": 1,"data": [{"item_id": 1,"item_name": "子表1","item_num": 1},{"item_id": 2,"item_name": "子表2","item_num": 1}] }

運行后保存數據庫里的結果值

三、Sql Server

SqlServer解析json之前需先引入函數 目前支持2008版本 其他版本暫無測試

//這里函數直接引入,不需具體查看 CREATE FUNCTION [dbo].[fn_parseJSON] ( @JSON NVARCHAR(MAX) ) RETURNS @hierarchy TABLE(element_id INT IDENTITY(1, 1)NOT NULL , /* internal surrogate primary key gives the order of parsing and the list order */sequenceNo [int] NULL , /* the place in the sequence for the element */parent_ID INT ,/* if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */Object_ID INT ,/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */NAME NVARCHAR(2000) ,/* the name of the object */StringValue NVARCHAR(MAX) NOT NULL ,/*the string representation of the value of the element. */ValueType VARCHAR(10) NOT NULL /* the declared type of the value represented as a string in StringValue*/) AS BEGINDECLARE @FirstObject INT , --the index of the first open bracket found in the JSON string@OpenDelimiter INT ,--the index of the next open bracket found in the JSON string@NextOpenDelimiter INT ,--the index of subsequent open bracket found in the JSON string@NextCloseDelimiter INT ,--the index of subsequent close bracket found in the JSON string@Type NVARCHAR(10) ,--whether it denotes an object or an array@NextCloseDelimiterChar CHAR(1) ,--either a '}' or a ']'@Contents NVARCHAR(MAX) , --the unparsed contents of the bracketed expression@Start INT , --index of the start of the token that you are parsing@end INT ,--index of the end of the token that you are parsing@param INT ,--the parameter at the end of the next Object/Array token@EndOfName INT ,--the index of the start of the parameter at end of Object/Array token@token NVARCHAR(max) ,--either a string or object@value NVARCHAR(MAX) , -- the value as a string@SequenceNo INT , -- the sequence number within a list@name NVARCHAR(200) , --the name as a string@parent_ID INT ,--the next parent ID to allocate@lenJSON INT ,--the current length of the JSON String@characters NCHAR(36) ,--used to convert hex to decimal@result BIGINT ,--the value of the hex symbol being parsed@index SMALLINT ,--used for parsing the hex value@Escape INT --the index of the next escape characterDECLARE @Strings TABLE /* in this temporary table we keep all strings, even the names of the elements, since they are 'escaped' in a different way, and may contain, unescaped, brackets denoting objects or lists. These are replaced in the JSON string by tokens representing the string */(String_ID INT IDENTITY(1, 1) ,StringValue NVARCHAR(MAX))SELECT--initialise the characters to convert hex to ascii@characters = '0123456789abcdefghijklmnopqrstuvwxyz' ,@SequenceNo = 0 , --set the sequence no. to something sensible./* firstly we process all strings. This is done because [{} and ] aren't escaped in strings, which complicates an iterative parse. */@parent_ID = 0 ;WHILE 1 = 1 --forever until there is nothing more to doBEGINSELECT @start = PATINDEX('%[^a-zA-Z]["]%',@json COLLATE SQL_Latin1_General_CP850_Bin) ;--next delimited stringIF @start = 0 BREAK --no more so drop through the WHILE loopIF SUBSTRING(@json, @start + 1, 1) = '"' BEGIN --Delimited NameSET @start = @Start + 1 ;SET @end = PATINDEX('%[^\]["]%',RIGHT(@json,LEN(@json + '|') - @start) COLLATE SQL_Latin1_General_CP850_Bin) ;ENDIF @end = 0 --no end delimiter to last stringBREAK --no moreSELECT @token = SUBSTRING(@json, @start + 1, @end - 1)--now put in the escaped control charactersSELECT @token = REPLACE(@token, FROMString, TOString)FROM ( SELECT '\"' AS FromString ,'"' AS ToStringUNION ALLSELECT '\\' ,'\'UNION ALLSELECT '\/' ,'/'UNION ALLSELECT '\b' ,CHAR(08)UNION ALLSELECT '\f' ,CHAR(12)UNION ALLSELECT '\n' ,CHAR(10)UNION ALLSELECT '\r' ,CHAR(13)UNION ALLSELECT '\t' ,CHAR(09)) substitutionsSELECT @result = 0 ,@escape = 1--Begin to take out any hex escape codesWHILE @escape > 0 BEGINSELECT @index = 0 ,--find the next hex escape sequence@escape = PATINDEX('%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%',@token COLLATE SQL_Latin1_General_CP850_Bin)IF @escape > 0 --if there is oneBEGINWHILE @index < 4 --there are always four digits to a \x sequence BEGIN SELECT --determine its value@result = @result + POWER(16,@index)* ( CHARINDEX(SUBSTRING(@token,@escape + 2 + 3- @index, 1),@characters) - 1 ) ,@index = @index + 1 ;END-- and replace the hex sequence by its unicode valueSELECT @token = STUFF(@token, @escape, 6,NCHAR(@result))ENDEND--now store the string away INSERT INTO @Strings( StringValue )SELECT @token-- and replace the string with a tokenSELECT @JSON = STUFF(@json, @start, @end + 1,'@string'+ CONVERT(NVARCHAR(5), @@identity))END-- all strings are now removed. Now we find the first leaf. WHILE 1 = 1 --forever until there is nothing more to doBEGINSELECT @parent_ID = @parent_ID + 1--find the first object or list by looking for the open bracketSELECT @FirstObject = PATINDEX('%[{[[]%',@json COLLATE SQL_Latin1_General_CP850_Bin)--object or arrayIF @FirstObject = 0 BREAKIF ( SUBSTRING(@json, @FirstObject, 1) = '{' ) SELECT @NextCloseDelimiterChar = '}' ,@type = 'object'ELSE SELECT @NextCloseDelimiterChar = ']' ,@type = 'array'SELECT @OpenDelimiter = @firstObjectWHILE 1 = 1 --find the innermost object or list...BEGINSELECT @lenJSON = LEN(@JSON + '|') - 1--find the matching close-delimiter proceeding after the open-delimiterSELECT @NextCloseDelimiter = CHARINDEX(@NextCloseDelimiterChar,@json,@OpenDelimiter+ 1)--is there an intervening open-delimiter of either typeSELECT @NextOpenDelimiter = PATINDEX('%[{[[]%',RIGHT(@json,@lenJSON- @OpenDelimiter)COLLATE SQL_Latin1_General_CP850_Bin)--objectIF @NextOpenDelimiter = 0 BREAKSELECT @NextOpenDelimiter = @NextOpenDelimiter+ @OpenDelimiterIF @NextCloseDelimiter < @NextOpenDelimiter BREAKIF SUBSTRING(@json, @NextOpenDelimiter, 1) = '{' SELECT @NextCloseDelimiterChar = '}' ,@type = 'object'ELSE SELECT @NextCloseDelimiterChar = ']' ,@type = 'array'SELECT @OpenDelimiter = @NextOpenDelimiterEND---and parse out the list or name/value pairsSELECT @contents = SUBSTRING(@json, @OpenDelimiter + 1,@NextCloseDelimiter- @OpenDelimiter - 1)SELECT @JSON = STUFF(@json, @OpenDelimiter,@NextCloseDelimiter - @OpenDelimiter + 1,'@' + @type+ CONVERT(NVARCHAR(5), @parent_ID))WHILE ( PATINDEX('%[A-Za-z0-9@+.e]%',@contents COLLATE SQL_Latin1_General_CP850_Bin) ) <> 0 BEGINIF @Type = 'Object' --it will be a 0-n list containing a string followed by a string, number,boolean, or nullBEGINSELECT @SequenceNo = 0 ,@end = CHARINDEX(':', ' ' + @contents)--if there is anything, it will be a string-based name.SELECT @start = PATINDEX('%[^A-Za-z@][@]%',' ' + @contents COLLATE SQL_Latin1_General_CP850_Bin)--AAAAAAAASELECT @token = SUBSTRING(' ' + @contents,@start + 1,@End - @Start - 1) ,@endofname = PATINDEX('%[0-9]%',@token COLLATE SQL_Latin1_General_CP850_Bin) ,@param = RIGHT(@token,LEN(@token)- @endofname + 1)SELECT @token = LEFT(@token, @endofname - 1) ,@Contents = RIGHT(' ' + @contents,LEN(' ' + @contents+ '|') - @end- 1)SELECT @name = stringvalueFROM @stringsWHERE string_id = @param --fetch the nameENDELSE SELECT @Name = NULL ,@SequenceNo = @SequenceNo + 1 SELECT @end = CHARINDEX(',', @contents)-- a string-token, object-token, list-token, number,boolean, or nullIF @end = 0 BEGINSELECT @end = PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%',@Contents + ' ' COLLATE SQL_Latin1_General_CP850_Bin)+ 1ENDSELECT @start = PATINDEX('%[^-A-Za-z0-9@+.e][-A-Za-z0-9@+.e]%',' ' + @contents COLLATE SQL_Latin1_General_CP850_Bin)--select @start,@end, LEN(@contents+'|'), @contents SELECT @Value = RTRIM(SUBSTRING(@contents, @start,@End - @Start)) ,@Contents = RIGHT(@contents + ' ',LEN(@contents + '|') - @end)IF SUBSTRING(@value, 1, 7) = '@object' INSERT INTO @hierarchy( NAME ,SequenceNo ,parent_ID ,StringValue ,Object_ID ,ValueType)SELECT @name ,@SequenceNo ,@parent_ID ,SUBSTRING(@value, 8, 5) ,SUBSTRING(@value, 8, 5) ,'object' ELSE IF SUBSTRING(@value, 1, 6) = '@array' INSERT INTO @hierarchy( NAME ,SequenceNo ,parent_ID ,StringValue ,Object_ID ,ValueType)SELECT @name ,@SequenceNo ,@parent_ID ,SUBSTRING(@value, 7, 5) ,SUBSTRING(@value, 7, 5) ,'array' ELSE IF SUBSTRING(@value, 1, 7) = '@string' INSERT INTO @hierarchy( NAME ,SequenceNo ,parent_ID ,StringValue ,ValueType)SELECT @name ,@SequenceNo ,@parent_ID ,stringvalue ,'string'FROM @stringsWHERE string_id = SUBSTRING(@value,8, 5)ELSE IF @value IN ( 'true', 'false' ) INSERT INTO @hierarchy( NAME ,SequenceNo ,parent_ID ,StringValue ,ValueType)SELECT @name ,@SequenceNo ,@parent_ID ,@value ,'boolean'ELSE IF @value = 'null' INSERT INTO @hierarchy( NAME ,SequenceNo ,parent_ID ,StringValue ,ValueType)SELECT @name ,@SequenceNo ,@parent_ID ,@value ,'null'ELSE IF PATINDEX('%[^0-9]%',@value COLLATE SQL_Latin1_General_CP850_Bin) > 0 INSERT INTO @hierarchy( NAME ,SequenceNo ,parent_ID ,StringValue ,ValueType)SELECT@name ,@SequenceNo ,@parent_ID ,@value ,'real'ELSE INSERT INTO @hierarchy( NAME ,SequenceNo ,parent_ID ,StringValue ,ValueType)SELECT@name ,@SequenceNo ,@parent_ID ,@value ,'int'IF @Contents = ' ' SELECT @SequenceNo = 0ENDENDINSERT INTO @hierarchy( NAME ,SequenceNo ,parent_ID ,StringValue ,Object_ID ,ValueType)SELECT '-' ,1 ,NULL ,'' ,@parent_id - 1 ,@type --RETURNENDGO

下面為Sql Server的測試存儲過程的建表語句。

// 主表 CREATE TABLE [dbo].[demo] ([id] int IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,[name] varchar(255) COLLATE Chinese_PRC_CI_AS NULL,[number] int NULL,CONSTRAINT [PK__demo__3213E83F5812160E] PRIMARY KEY CLUSTERED ([id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GOALTER TABLE [dbo].[demo] SET (LOCK_ESCALATION = TABLE) //子表 CREATE TABLE [dbo].[demo_item] ([id] int IDENTITY(1,1) NOT NULL,[name] varchar(255) COLLATE Chinese_PRC_CI_AS NULL,[number] varchar(255) COLLATE Chinese_PRC_CI_AS NULL,[demo_number] int NULL,CONSTRAINT [PK__demo_ite__3213E83F5BE2A6F2] PRIMARY KEY CLUSTERED ([id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GOALTER TABLE [dbo].[demo_item] SET (LOCK_ESCALATION = TABLE)

下面為Sql Server的測試存儲過程的存儲過程建立語句。

ALTER PROCEDURE [dbo].[demo_x]@Json VARCHAR(4000) ASDECLARE @name VARCHAR(50), --主表@number INT, --主表@name_item VARCHAR(50), --子表@number2 INT, --子表@demo_number INT, --子表@parent_ID INT,@Object_ID INT;SELECT @name = StringValue FROM fn_parseJSON(@Json) where name = 'name'; --獲取主表nameSELECT @number = StringValue FROM fn_parseJSON(@Json) where name = 'number';--獲取主表numberINSERT INTO demo(name,number) VALUES(@name,@number)SELECT * INTO #temp_fkd_data_json FROM fn_parseJSON(@Json); --保存主表SELECT @parent_ID = Object_IDFROM #temp_fkd_data_jsonWHERE ValueType = 'array';WHILE ( EXISTS ( SELECT 1FROM #temp_fkd_data_jsonWHERE parent_ID = @parent_ID ) )BEGINSELECT TOP 1@Object_ID = Object_IDFROM #temp_fkd_data_jsonWHERE parent_ID = @parent_ID;SELECT @name_item = StringValueFROM #temp_fkd_data_jsonWHERE parent_ID = @Object_IDAND NAME = 'name_item';print @name_item;SELECT @number2 = StringValueFROM #temp_fkd_data_jsonWHERE parent_ID = @Object_IDAND NAME = 'number';print @number2; INSERT INTO demo_item(name,number,demo_number) VALUES(@name_item,@number2,@number)--保存子表 DELETE #temp_fkd_data_json WHERE Object_ID = @Object_ID;ENDDROP TABLE #temp_fkd_data_json; END

關鍵字講解
擺爛了不想寫關鍵字段,想起了再說吧

//入參json {"name": "主表","number": 1,"data": [{"name_item": "子表1","number": 2},{"name_item": "子表2","number": 3}]} //格式化后的入參json {"name": "主表","number": 1,"data": [{"name_item": "子表1","number": 2},{"name_item": "子表2","number": 3}] }

運行后保存數據庫里的結果值

總結

以上是生活随笔為你收集整理的常用的三种数据库创建存储过程并解析json的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。