oracle笔记

chr(39)表示单引号；

chr(34)表示双引号；

时间格式yyyymmddhh24miss

创建存储过程

create or replace procedure pro_updatetdwjkssccs(v_czlx varchar2) is
  init number;
begin

  update 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 dual

SELECT 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

扩展表空间

1.查询出fiel_name
select * from dba_data_files where tablespace_name='USERS'

2.查出使用情况
SELECT tbs 表空间名,
       sum(totalM) 总共大小M,
       sum(usedM) 已使用空间M,
       sum(remainedM) 剩余空间M,
       sum(usedM) / sum(totalM) * 100 已使用百分比,
       sum(remainedM) / sum(totalM) * 100 剩余百分比
  FROM (SELECT b.file_id ID,
               b.tablespace_name tbs,
               b.file_name name,
               b.bytes / 1024 / 1024 totalM,
               (b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 usedM,
               sum(nvl(a.bytes, 0) / 1024 / 1024) remainedM,
               sum(nvl(a.bytes, 0) / (b.bytes) * 100),
               (100 - (sum(nvl(a.bytes, 0)) / (b.bytes) * 100))
          FROM dba_free_space a, dba_data_files b
         WHERE a.file_id = b.file_id
         GROUP BY b.tablespace_name, b.file_name, b.file_id, b.bytes
         ORDER BY b.tablespace_name)
 GROUP BY tbs

 
3.更改表空间大小
alter database datafile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF' resize 7900M;

 

附：
自动增长  alterdatabase datafile 'D:\ORACLE\PRODUCT\ORADATA\TEST\USERS01.DBF' autoextend onnext 50m maxsize 500m;

【解决办法-原因二】

因为表空间中的数据文件已经足够大（达到32G），所以，这时仅仅增加表空间大小是不行的。

这个时候，我们可以增加该表空间的数据文件，这样表空间的大小即变为64G了。

ALTER TABLESPACE aaa

ADD DATAFILE 'E:\APP\ORACLE11GR2\ORADATA\ORCL\aaa_DATA02.DBF'

SIZE 32767M;