oracle json转换为达梦json
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.对象使用大括号{}，数组使用中括号[]
