oracle字符串提取记录

背景：需要限制用户操作次数，而用户操作次数只有统一的日志表有记录。 并且，因为在批量查询中也需做限制，所有需要一次查询多条数据，保证效率。后来采用视图做的

 

视图

instr 查找字符串，返回起始坐标， 坐标从1开始

substr切割字符串，substr(字符串，起始坐标，长度)

create or replace view v_log as
--用作批量核查时限制次数
select t2."PHONE_NUMBER",t2."TIME",t2."URL",
substr(t2.post_params, instr(t2.post_params, 'reportTime:')+length('reportTime:'), instr(t2.post_params, ',companyName')-instr(t2.post_params, 'reportTime:')-length('reportTime:')) as reportTime,
substr(t2.post_params, instr(t2.post_params, 'companyName:')+length('companyName:'), instr(t2.post_params, ',xyCode')-instr(t2.post_params, 'companyName:')-length('companyName:')) as companyName,
substr(t2.post_params, instr(t2.post_params, 'xyCode:')+length('xyCode:'), instr(t2.post_params, ',zb')-instr(t2.post_params, 'xyCode:')-length('xyCode:')) as xyCode,
substr(t2.post_params, instr(t2.post_params, ',zb:')+length(',zb:'), instr(t2.post_params, ',value')-instr(t2.post_params, ',zb:')-length(',zb:')) as zb,
substr(t2.post_params, instr(t2.post_params, ',type:')+length(',type:'), length(t2.post_params)-instr(t2.post_params, ',type:')-length(',type:')) as type
from user_log t2
where t2.url like '%/check/single';

 

查询结果