此文章包含MySQL、Oracle和Sql Server这三种数据库的创建json格式入参并解析的主子表存储
文章目录
一、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)
BEGIN
DECLARE 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'))));#获取主表number
WHILE 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'))));#关联主表number
SELECT 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 2147483645
PCTINCREASE 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 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 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 2147483645
PCTINCREASE 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 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 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 LOOP
itemjson := 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
BEGIN
DECLARE @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 character
DECLARE @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 do
BEGIN
SELECT @start = PATINDEX('%[^a-zA-Z]["]%',
@json COLLATE SQL_Latin1_General_CP850_Bin) ;--next delimited string
IF @start = 0
BREAK --no more so drop through the WHILE loop
IF SUBSTRING(@json, @start + 1, 1) = '"'
BEGIN --Delimited Name
SET @start = @Start + 1 ;
SET @end = PATINDEX('%[^\]["]%',
RIGHT(@json,
LEN(@json + '|') - @start) COLLATE SQL_Latin1_General_CP850_Bin) ;
END
IF @end = 0 --no end delimiter to last string
BREAK --no more
SELECT @token = SUBSTRING(@json, @start + 1, @end - 1)
--now put in the escaped control characters
SELECT @token = REPLACE(@token, FROMString, TOString)
FROM ( SELECT '\"' AS FromString ,
'"' AS ToString
UNION ALL
SELECT '\\' ,
'\'
UNION ALL
SELECT '\/' ,
'/'
UNION ALL
SELECT '\b' ,
CHAR(08)
UNION ALL
SELECT '\f' ,
CHAR(12)
UNION ALL
SELECT '\n' ,
CHAR(10)
UNION ALL
SELECT '\r' ,
CHAR(13)
UNION ALL
SELECT '\t' ,
CHAR(09)
) substitutions
SELECT @result = 0 ,
@escape = 1
--Begin to take out any hex escape codes
WHILE @escape > 0
BEGIN
SELECT @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 one
BEGIN
WHILE @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 value
SELECT @token = STUFF(@token, @escape, 6,
NCHAR(@result))
END
END
--now store the string away
INSERT INTO @Strings
( StringValue )
SELECT @token
-- and replace the string with a token
SELECT @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 do
BEGIN
SELECT @parent_ID = @parent_ID + 1
--find the first object or list by looking for the open bracket
SELECT @FirstObject = PATINDEX('%[{[[]%',
@json COLLATE SQL_Latin1_General_CP850_Bin)--object or array
IF @FirstObject = 0
BREAK
IF ( SUBSTRING(@json, @FirstObject, 1) = '{' )
SELECT @NextCloseDelimiterChar = '}' ,
@type = 'object'
ELSE
SELECT @NextCloseDelimiterChar = ']' ,
@type = 'array'
SELECT @OpenDelimiter = @firstObject
WHILE 1 = 1 --find the innermost object or list...
BEGIN
SELECT @lenJSON = LEN(@JSON + '|') - 1
--find the matching close-delimiter proceeding after the open-delimiter
SELECT @NextCloseDelimiter = CHARINDEX(@NextCloseDelimiterChar,
@json,
@OpenDelimiter
+ 1)
--is there an intervening open-delimiter of either type
SELECT @NextOpenDelimiter = PATINDEX('%[{[[]%',
RIGHT(@json,
@lenJSON
- @OpenDelimiter)COLLATE SQL_Latin1_General_CP850_Bin)--object
IF @NextOpenDelimiter = 0
BREAK
SELECT @NextOpenDelimiter = @NextOpenDelimiter
+ @OpenDelimiter
IF @NextCloseDelimiter < @NextOpenDelimiter
BREAK
IF SUBSTRING(@json, @NextOpenDelimiter, 1) = '{'
SELECT @NextCloseDelimiterChar = '}' ,
@type = 'object'
ELSE
SELECT @NextCloseDelimiterChar = ']' ,
@type = 'array'
SELECT @OpenDelimiter = @NextOpenDelimiter
END
---and parse out the list or name/value pairs
SELECT @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
BEGIN
IF @Type = 'Object' --it will be a 0-n list containing a string followed by a string, number,boolean, or null
BEGIN
SELECT @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)--AAAAAAAA
SELECT @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 = stringvalue
FROM @strings
WHERE string_id = @param --fetch the name
END
ELSE
SELECT @Name = NULL ,
@SequenceNo = @SequenceNo + 1
SELECT @end = CHARINDEX(',', @contents)-- a string-token, object-token, list-token, number,boolean, or null
IF @end = 0
BEGIN
SELECT @end = PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%',
@Contents + ' ' COLLATE SQL_Latin1_General_CP850_Bin)
+ 1
END
SELECT @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 @strings
WHERE 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 = 0
END
END
INSERT INTO @hierarchy
( NAME ,
SequenceNo ,
parent_ID ,
StringValue ,
Object_ID ,
ValueType
)
SELECT '-' ,
1 ,
NULL ,
'' ,
@parent_id - 1 ,
@type
--
RETURN
END
GO
下面为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]
GO
ALTER 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]
GO
ALTER TABLE [dbo].[demo_item] SET (LOCK_ESCALATION = TABLE)
下面为Sql Server的测试存储过程的存储过程建立语句。
ALTER PROCEDURE [dbo].[demo_x]@Json VARCHAR(4000)
AS
DECLARE
@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'; --获取主表name
SELECT @number = StringValue FROM fn_parseJSON(@Json) where name = 'number';--获取主表number
INSERT INTO demo(name,number) VALUES(@name,@number)
SELECT * INTO #temp_fkd_data_json FROM fn_parseJSON(@Json); --保存主表
SELECT @parent_ID = Object_ID
FROM #temp_fkd_data_json
WHERE ValueType = 'array';
WHILE ( EXISTS ( SELECT 1
FROM #temp_fkd_data_json
WHERE parent_ID = @parent_ID ) )
BEGIN
SELECT TOP 1
@Object_ID = Object_ID
FROM #temp_fkd_data_json
WHERE parent_ID = @parent_ID;
SELECT @name_item = StringValue
FROM #temp_fkd_data_json
WHERE parent_ID = @Object_ID
AND NAME = 'name_item';
print @name_item;
SELECT @number2 = StringValue
FROM #temp_fkd_data_json
WHERE parent_ID = @Object_ID
AND 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;
END
DROP 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
}
]
}
运行后保存数据库里的结果值
1129
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
