一、前言
目前项目使用的数据库是Oracle 12c,选择该版本的的主要原因之一是支持json。某日,某变态客户说要转向云平台,而云平台却没有Oracle只有MySql及其它开源产品。在把建库脚本(包括表、视图、索引、触发器、存储过程等等)由oracle版转为mysql版的过程中,最烦人的一个难关是如何实现json_table()函数的替代。
Oracle版脚本在多处使用了json_value()函数,少数几处使用了json_table()。转为MySql版时,json_value()可以替换为json_extract(),但json_table()……众所周知,MySql的函数不能返回表类型的变量,因此根本没有对应函数,也没提供对应的思路。曾经通过谷歌搜索到一段很复杂的示例sql代码,可当时的确看不明白,也就谈不上改造,而现在连谷歌也上不去,只能另寻出路。
二、Oracle脚本
去除不必要的内容,与json_table()相关的Oracle版脚本如下:
--Table
create tablePERSON_INFO
(
ID NUMBER(15) not null,
ADDR_INFO VARCHAR2(1000) CONSTRAINT ADDR_INFO_JSON CHECK (ADDR_INFO ISJSON),
PRIMARY KEY( ID )
);
--View
CREATE OR REPLACE VIEWPERSON_ADDR_VIEW
AS
SELECT
PI.ID ID,
TEMP_TAB.ADDRESS_CODE,
TEMP_TAB.ADDRESS_DETAIL,
TEMP_TAB.ADDRESS_TYPE,
TEMP_TAB.ADDRESS_ZIP_CODE
FROM PERSON_INFO PI,
JSON_TABLE(PI.ADDR_INFO, '$[*]' COLUMNS (ADDRESS_CODE VARCHAR2 PATH '$.AddressCode', ADDRESS_DETAIL VARCHAR2 PATH '$.AddressDetail',
ADDRESS_TYPE VARCHAR2 PATH '$.AddressType', ADDRESS_ZIP_CODE VARCHAR2 PATH '$.AddressZipCode')) TEMP_TAB;
--Function
CREATE OR REPLACE
FUNCTION GET_ADDR_NAME_BY_TYPE (P_ADDR_INFO IN VARCHAR2, P_ADDR_TYPE IN NUMBER)
RETURN VA