chr(39)表示单引号；
chr(34)表示双引号；
时间格式yyyymmddhh24miss
创建存储过程
create or replace procedure pro_updatetdwjkssccs(v_czlx varchar2) is
init number;
beginupdate t_dw_jks a set sccs=0 where userid='123456' and fsfcs='F';
commit;
end pro_updatetdwjkssccs;
创建job
declare job number;
begin
sys.dbms_job.submit(job => job,
what => 'begin pro_updatetdwjkssccs(''0''); end;',
next_date => to_date('10-11-2018', 'dd-mm-yyyy'),
interval => 'TRUNC(sysdate+1)+(0*60)/(24*60)');
commit;
end;
/
begin
sys.dbms_job.submit(job => :job,
what => 'begin pro_t_pay_app_user(''0''); end;',
next_date => to_date('23-09-2019', 'dd-mm-yyyy'),
interval => 'TRUNC(sysdate)+((to_char(sysdate,''hh24'')+1)*60)/(24*60)');
commit;
end;
/
删除运行的job
select * from Dba_Scheduler_Running_Jobs
select SID,SERIAL# from V$Session WHERE SID='691'
alter system kill session '691,3244';
sql字段去重
select wm_concat(distinct regexp_substr('02 湘J2H867,02 湘J2H867,02 湘J2H866','[^,]+',1,level,'i')) from dual
connect by level <= length('02 湘J2H867,02 湘J2H867,02 湘J2H866') -length(regexp_replace('02 湘J2H867,02 湘J2H867,02 湘J2H866',',',''))+1;
id
select sys_guid() from dual
//创建触发器
create or replace trigger update_ysk_JK_JKMX
after update OF FXSWM,FXSMC,FCARDS on t_vdw_xx_xs
for each row
declare
-- local variables here
begin
update t_vdw_xx_ysk set FXSWM=:new.FXSWM,FXSMC=:new.FXSMC,FCARDS=:new.FCARDS where FLSH=:old.FLSH;
update t_vdw_xx_jk set FXSWM=:new.FXSWM,FXSMC=:new.FXSMC where FLSH=:old.FLSH;
update t_vdw_xx_jkmx set FXSWM=:new.FXSWM,FXSMC=:new.FXSMC where FLSH=:old.FLSH;
end update_ysk_JK_JKMX;
//创建视图
CREATE OR REPLACE VIEW T_DW_FM_SFXX AS
select '3' wfjf,
a.lsh jdsbh,
a.xm dsr,
a.sfzh zjhm,
(sum(xmje) - sum(ysznj)) jkje,
sum(ysznj) znj,
a.clsj kprq,
d.fpjh jksbh,
a.ywdwbh dwmc,
a.USERID jbr,
'' pjtp,
'2' jkbj,
'接口写入' bz,
(select glbm from sys_user where czybm = a.userid) scbm
from pams.t_jj_sfxx a,
pams.jc_cllx b,
pams.jc_lyxx c,
(select fposlsh,
fid,
fpclsh,
flsh,
fsfcs,
wm_concat(distinct fpjh) fpjh,
fkprq
from (SELECT fpclsh,
flsh,
fpjh,
fposlsh,
fsfcs,
REGEXP_SUBSTR(CP.fid, '[^,]+', 1, L) AS fid,
fkprq
FROM pams.T_DW_JKS CP,
(SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 100)
WHERE L(+) <=
LENGTH(CP.fid) - LENGTH(REPLACE(CP.fid, ',')) + 1)
group by fpclsh, flsh, fposlsh, fid, fsfcs,fkprq) d
where a.hpzl = b.flbbm(+)
and a.sjly = c.fbm
and a.JKID = c.ywlx
and a.clbz = 'T'
and length(d.fposlsh) > 4
and a.fid = d.fid
and a.fsfcs = 'F'
and a.sjly = '02'
and d.fkprq =to_char(sysdate,'yyyy-mm-dd')
group by a.jfsj,
a.fid,
a.hpzl,
b.fclmc,
a.hphm,
a.sfzh,
a.sjly,
c.fmc,
a.lsh,
a.xm,
a.ywdwbh,
a.clbz,
a.clsj,
a.userid,
d.fpclsh,
d.flsh,
d.fpjh,
d.fposlsh
order by a.jfsj, d.fpclsh desc
NOT EXISTS语句需注意建立与前表关联id的索引
按字符串截取
select REGEXP_SUBSTR（'1234567890|03048-非税收入专用收据(工商专用50元)|83-湘财专字[2019]', '[^|]+'） from dual
select REGEXP_SUBSTR（'1234567890|03048-非税收入专用收据(工商专用50元)|83-湘财专字[2019]', '[^|]+', 1, 2） from dual
select REGEXP_SUBSTR(REGEXP_SUBSTR（'1234567890|03048-非税收入专用收据(工商专用50元)|83-湘财专字[2019]', '[^|]+', 1, 2）,'[^-]+') from dual
select REGEXP_SUBSTR(REGEXP_SUBSTR（'1234567890|03048-非税收入专用收据(工商专用50元)|83-湘财专字[2019]', '[^|]+', 1, 2）,'[^-]+', 1, 2) from dual
select REGEXP_SUBSTR（'1234567890|03048-非税收入专用收据(工商专用50元)|83-湘财专字[2019]', '[^|]+', 1, 3） from dual
select REGEXP_SUBSTR(REGEXP_SUBSTR（'1234567890|03048-非税收入专用收据(工商专用50元)|83-湘财专字[2019]', '[^|]+', 1, 3）,'[^-]+') from dual
select REGEXP_SUBSTR(REGEXP_SUBSTR（'1234567890|03048-非税收入专用收据(工商专用50元)|83-湘财专字[2019]', '[^|]+', 1, 3）,'[^-]+', 1, 2) from dualSELECT REGEXP_SUBSTR('1234567890|03048-非税收入专用收据(工商专用50元)|83-湘财专字[2019]', '[^|]+', 1, LEVEL)
FROM DUAL
CONNECT BY REGEXP_SUBSTR('1234567890|03048-非税收入专用收据(工商专用50元)|83-湘财专字[2019]', '[^|]+', 1, LEVEL) IS NOT NULL;
dblink创建
-- Create database link
create database link TESTDBLINK
connect to test20200609 identified by test20200609
using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=199.0.0.176)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora11g)))';
循环
declare
v_cnt number;
v_paramcnt number;
BEGIN
for v_cnt in 1..100
loop
dbms_output.put_line('name');
end loop;
END ;
if判断
declare
v_cnt number;
v_paramcnt number;
BEGIN
select 1 into v_cnt from dual;
select 1 into v_paramcnt from dual;
if v_cnt=0 and v_paramcnt!=0
then
dbms_output.put_line('name');
else
ROLLBACK;
end if
;END ;
单独授权表
grant select,insert,update,delete,all on sys_table to testdba_new
截取
格式1： substr(string string, int a, int b);
格式2：substr(string string, int a) ;
解释：格式1
1、string 需要截取的字符串
2、a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取）
3、b 要截取的字符串的长度
格式2
1、string 需要截取的字符串
2、a 可以理解为从第a个字符开始截取后面所有的字符串。select
substr('2019-09-09 15:00:03', 0, instr('2019-09-09 15:00:03', ' ')-1) as code,
substr('2019-09-09 15:00:03', instr('2019-09-09 15:00:03', ' ')+1) as name
from dual