oracle常见操作
blob转字符串类型
dbms_lob.SUBSTR(b.YZB,2000,1) AS LONGITUDE
DECODE(b.zxsj,'',DECODE(b.zxyy,'','0','1'),'1') AS DEL_FLAG
字符串转时间:
TO_DATE('2022-05-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
将日期字段添加上秒
UPDATE FK_B_RYGJJC SET CFSJ = CFSJ || ':00' WHERE CXFS = 'DB'
SQL:
select u.name,u.jh,b.UPDATE_DATE from fk_b_emp a
left join REL_PR_EMP b on a.unid=b.emp_unid
left join SYS_USER u on u.id=b.UPDATE_by
left join SYS_OFFICE o on o.id =u.office_id
where a.id_sn='654123198706290056'
给中间表加一个状态字段,ZT,将拉取过的数据设置为1
SELECT
a.RYBH AS UNID,
a.JGBH AS PUNID,
a.ZYM AS NAME,
a.SJ AS PHONE,
a.XZZ AS ADDRESS,
a.ZW AS DUTY,
a.DJSJ AS CREATE_DATE,
a.XGSJ AS UPDATE_DATE
FROM
JG_FKZDMBDWXX b
INNER JOIN JG_CYRY a ON a.JGBH = b.JGBH
WHERE a.ZT IS NULL
批量查询
SELECT
t.*
FROM
(
SELECT
a.RYBH AS UNID,
a.JGBH AS PUNID,
a.ZYM AS NAME,
a.SJ AS PHONE,
a.XZZ AS ADDRESS,
a.ZW AS DUTY,
a.DJSJ AS CREATE_DATE,
a.XGSJ AS UPDATE_DATE,
ROWNUM AS NUM
FROM
JG_FKZDMBDWXX b
INNER JOIN JG_CYRY a ON a.JGBH = b.JGBH
WHERE
a.ZT IS NULL
) t
WHERE
t.NUM <= 500