oracle apex post接口

日常记录

使用到了apex_json方式接收

、、、1
首先，接口通过body传递过来，成功接收到， 数据格式为 JSON_OBJECT_T
l_json := JSON_OBJECT_T.parse(:body);  
这里我用参数接收到 
然后 里面是包含了 "data"
我用 继续接收到这个
l_data            := l_json.get_object('data');、、、2
但是有个问题，如果是使用系统原始的 get_array('persons')  之类的，始终是报错 
我只能将代码转成clob格式存放到系统的参数中
V_JSON_DATA := l_data.To_Clob;、、、3apex_json.parse(V_JSON_DATA);
apex_json.parse函数来解析JSON字符串使用 apex_json.parse(V_JSON_DATA); 将 CLOB 类型的 JSON 数据解析为 APEX 的 JSON 对象。
然后，尝试使用 apex_json.get_varchar2 函数来访问解析后的 JSON 数据。
apex_json.get_varchar2 函数是线程安全的，这意味着同一时间的多个请求不会互相影响。
每个 APEX 应用程序实例（即每个用户会话）都有自己的内存空间，
因此一个会话中的 apex_json.get_varchar2 调用不会影响另一个会话中的调用。

代码

DECLAREl_json     JSON_OBJECT_T;l_data     JSON_OBJECT_T;V_JSON   JSON_OBJECT_T;V_DATA   JSON_OBJECT_T;V_JSON_DATA CLOB;V_VISITORS  JSON_ARRAY_T;V_VISITOR   JSON_OBJECT_T;PAR_DATA_NO NUMBER;PAR_EXISTS  NUMBER;RETURNOBJ JSON_OBJECT_T; -- 返回V_SQLCODE VARCHAR2(4000); -- 错误代码V_SQLERRM VARCHAR2(4000); -- 错误内容BEGINl_json := JSON_OBJECT_T.parse(:body);-- 获取嵌套的data对象l_data            := l_json.get_object('data');V_JSON_DATA := l_data.To_Clob;apex_json.parse(V_JSON_DATA);-- 当前流程标题是否存在SELECT COUNT(*)INTO PAR_EXISTSFROM TZ_FREE_TICKETSWHERE PROCESS_TITLE = apex_json.get_varchar2('process_title')AND DEL_FLAG = 1;IF PAR_EXISTS > 0 THEN-- 获取原有批次号  SELECT DATA_NOINTO PAR_DATA_NOFROM TZ_FREE_TICKETSWHERE PROCESS_TITLE = apex_json.get_varchar2('process_title')AND DEL_FLAG = 1;-- 原有数据删除，重新插入UPDATE TZ_FREE_TICKETSSET DEL_FLAG = 0, UPDATE_TIME = SYSDATEWHERE PROCESS_TITLE = apex_json.get_varchar2('process_title')AND DEL_FLAG = 1; -- 根据流程单据名称更新ELSESELECT TZ_FREE_TICKETS_DATA_NO_SEQ.NEXTVAL INTO PAR_DATA_NO FROM DUAL; -- 获取当前批次号      END IF;-- 插入主表数据INSERT INTO TZ_FREE_TICKETS(DATA_NO,OPEN_INVOICE, -- 是否出票 是/否APPLY_TIME, -- 申请日期PROCESS_TITLE, -- 流程单据名称PROCESS_NUMBER, -- OA流程编号APPLY_DEPT, -- 申请部门APPLY_BY, -- 申请人FREE_TICKET_TYPE, -- 免票类型ENTER_GARDEN_CAUSE, -- 入园事由ENTER_GARDEN_DATE, -- 入园日期 TICKET_NAME, -- 票种名称TICKET_PRICE, -- 门票单价APPLY_NUM, -- 申请入园人数FREE_TICKET_PRICE, -- 免票金额RECIPIENT_UNIT, -- 领用人/单位RECIPIENT_PHONE, --领用人联系电话REMARKS, -- 备注BUSID -- 景区 莽山221)VALUES(PAR_DATA_NO,apex_json.get_varchar2('open_invoice'),apex_json.get_varchar2('apply_time'),apex_json.get_varchar2('process_title'),apex_json.get_varchar2('process_number'),apex_json.get_varchar2('apply_dept'),apex_json.get_varchar2('apply_by'),apex_json.get_varchar2('free_ticket_type'),apex_json.get_varchar2('enter_garden_cause'),apex_json.get_varchar2('enter_garden_date'),apex_json.get_varchar2('ticket_name'),apex_json.get_number('ticket_price'),apex_json.get_number('apply_num'),apex_json.get_number('free_ticket_price'),apex_json.get_varchar2('recipient_unit'),apex_json.get_varchar2('recipient_phone'),apex_json.get_varchar2('remarks'),apex_json.get_varchar2('busid'));-- 插入游客信息表数据FOR I IN 1 .. apex_json.get_count('visitors') LOOPINSERT INTO TZ_FREE_TICKETS_VISITOR(DATA_NO,VISITOR_NAME, -- 游客姓名VISITOR_ID_NUMBER, -- 游客身份证号ENTRY_STATUS, -- 入园状态，是 否TICKET_ORDER_NO, -- 关联票务订单号BUSID)VALUES(PAR_DATA_NO,apex_json.get_varchar2('visitors[%d].visitor_name', i),apex_json.get_varchar2('visitors[%d].visitor_id_number', i),apex_json.get_varchar2('visitors[%d].entry_status', i),apex_json.get_varchar2('visitors[%d].ticket_order_number', i),apex_json.get_varchar2('busid'));END LOOP;COMMIT;-- 设置返回信息RETURNOBJ := NEW JSON_OBJECT_T;RETURNOBJ.PUT('msg', '成功');RETURNOBJ.PUT('code', 200);SYS.HTP.PRINT(RETURNOBJ.TO_STRING());EXCEPTIONWHEN OTHERS THENV_SQLCODE := SQLCODE;V_SQLERRM := SUBSTR(SQLERRM, 1, 2000);RETURNOBJ := NEW JSON_OBJECT_T;RETURNOBJ.PUT('msg', '失败' || V_SQLERRM);RETURNOBJ.PUT('code', 500);SYS.HTP.PRINT(RETURNOBJ.TO_STRING());
END;

参数

{"data": {"open_invoice": "是","apply_time": "2024-05-20","process_title": "测试流程单","process_number": "PR202405200001","apply_dept": "人力资源部","apply_by": "张三","free_ticket_type": "VIP","enter_garden_cause": "商务合作","enter_garden_date": "2024-05-22","ticket_name": "普通门票","ticket_price": 120.00,"apply_num": 4,"free_ticket_price": 480.00,"recipient_unit": "合作伙伴公司","recipient_phone": "13800000000","remarks": "请尽快处理","busid": "221","visitors": [{"visitor_name": "张三","visitor_id_number": "123456789012345678","entry_status": "是","ticket_order_number": "T11111111"},{"visitor_name": "李四","visitor_id_number": "123456789012345679","entry_status": "是","ticket_order_number": "T11111111"},{"visitor_name": "王五","visitor_id_number": "123456789012345670","entry_status": "是","ticket_order_number": "T11111111"},{"visitor_name": "赵六","visitor_id_number": "123456789012345671","entry_status": "是","ticket_order_number": "T11111111"}]}
}