oracle json数据性能,oracle json转换为达梦json

oracle json转换为达梦json

发布时间：2020-08-07 14:45:39

来源：ITPUB博客

阅读：98

作者：yanhengdoudou

栏目：数据库

oracle端：

CREATE VIEW "FJMP"."MP_ATTEND_APPLY_ALL_VIEW" ("ATTEND_ID","ATTEND_TYPE","ATTEND_NAME","PERSON_CODE","PERSON_NAME","APPLY_FALG","APPLY_DATE","APPRAISE_LEVEL","APPRAISE_DATE","APPRAISE_FLAG","STR1","STR2","STR3","STR4","APPLY_FALG_NAME")

AS

SELECT A.ATTEND_ID,A.ATTEND_TYPE,A.ATTEND_NAME,A.PERSON_CODE,PERSON_NAME,APPLY_FALG,APPLY_DATE,APPRAISE_LEVEL,TO_CHAR(APPRAISE_DATE,'YYYY-MM-DD') APPRAISE_DATE,APPRAISE_FLAG,

JSON_ARRAY(

JSON_OBJECT(

KEY 'title' VALUE '"申请人"' FORMAT JSON ,

KEY 'content' VALUE '"'||REPLACE(PERSON_NAME,'–','-')||'"' FORMAT JSON)

,

JSON_OBJECT(

KEY 'title' VALUE '"申请时间"' FORMAT JSON,

KEY 'content' VALUE '"'||TO_CHAR(A.APPLY_DATE,'YYYY-MM-DD')||'"' FORMAT JSON)

,

JSON_OBJECT(

KEY 'title' VALUE '"目的地"' FORMAT JSON,

KEY 'content' VALUE '"'||B.DESTIN_ADDR||'"' FORMAT JSON)

,

JSON_OBJECT(

KEY 'title' VALUE '"申请用途"' FORMAT JSON,

KEY 'content' VALUE '"'||B.CONTENT||'"' FORMAT JSON)

) STR1,'' STR2,'' STR3,'' STR4,

--'申请人：'||PERSON_NAME STR1,'申请时间：'||TO_CHAR(A.APPLY_DATE,'YYYY-MM-DD') STR2,'目的地：'||B.DESTIN_ADDR STR3,'文件摘要：'||SUBSTR(B.CONTENT,1,20)||'...' STR4,

DECODE(APPLY_FALG,'0','未审批','1','审批通过','2','审批不通过','3','撤销') APPLY_FALG_NAME

FROM MP_ATTEND A,MP_ATTEND_APPLY_CAR B

WHERE A.ATTEND_ID=B.ATTEND_ID

达梦端：

CREATE VIEW "FJMP"."MP_ATTEND_APPLY_ALL_VIEW" ("ATTEND_ID","ATTEND_TYPE","ATTEND_NAME","PERSON_CODE","PERSON_NAME","APPLY_FALG","APPLY_DATE","APPRAISE_LEVEL","APPRAISE_DATE","APPRAISE_FLAG","STR1","STR2","STR3","STR4","APPLY_FALG_NAME")

AS

SELECT A.ATTEND_ID,A.ATTEND_TYPE,A.ATTEND_NAME,A.PERSON_CODE,PERSON_NAME,APPLY_FALG,APPLY_DATE,APPRAISE_LEVEL,TO_CHAR(APPRAISE_DATE,'YYYY-MM-DD') APPRAISE_DATE,APPRAISE_FLAG,

'{[

{

"title" : "申请人",

"content" : "'||REPLACE(PERSON_NAME,'–','-')||'" }

,

{

"title" : "申请时间",

"content" : "'||TO_CHAR(A.APPLY_DATE,'YYYY-MM-DD')||'"}

,

{

"title" : "目的地",

"content" : "'||B.DESTIN_ADDR||'"}

,

{

"title" : "申请用途",

"content" : "'||B.CONTENT||'"}]

}'STR1,'' STR2,'' STR3,'' STR4,

--'申请人：'||PERSON_NAME STR1,'申请时间：'||TO_CHAR(A.APPLY_DATE,'YYYY-MM-DD') STR2,'目的地：'||B.DESTIN_ADDR STR3,'文件摘要：'||SUBSTR(B.CONTENT,1,20)||'...' STR4,

DECODE(APPLY_FALG,'0','未审批','1','审批通过','2','审批不通过','3','撤销') APPLY_FALG_NAME

FROM MP_ATTEND A,MP_ATTEND_APPLY_CAR B

WHERE A.ATTEND_ID=B.ATTEND_ID

注意事项：(达梦)

1.整个json需要单引号包起来；

2.处理函数格式："'||sysdate||'"

3.对象使用大括号{}，数组使用中括号[]