ORACL及SQL不常用函数

（1）偏移函数：lag()下偏移：lead(t.id, 1, null) over(partition by cphm order by t.id)。

                    lead() 上偏移：lead(t.id, 1, null) over(partition by cphm order by t.id)。

                     备注：1表示偏移行数，null表示找不到偏移对象就为空。

                     参考文章：https://blog.youkuaiyun.com/weixin_41287692/article/details/80577828。

(2) 寻找所有列信息：select * from all_col_comments           

（3）start with函数:https://www.cnblogs.com/zyzdisciple/p/7873584.html，用来查找树状关系的数据。

（4） select/*+parallel 4*/：提升select的运行速度，对insert起不到作用。

（5）select count(*)  from  (select  distinct * from tableA)运行速度高于select count(distinct *) from tableA

(6)运行一个存在out的存储过程方法：

declare
V_PRODUCTTYPE varchar2(100) :='所有险种';
V_DATETIME varchar2(100) :='2019/06/04';
result number := 0;
begin
DH_SP_DATE_EXC(V_PRODUCTTYPE,V_DATETIME,result);
end;

（7）删除正在运行的存储过程

select *
from v$db_object_cache
where locks > 0 and pins > 0 and type='PROCEDURE';

select /*+ rule*/ * from v$access where owner= 'DH_SP_H_MERGE_CUSTOMER_ECIF_9'

SELECT /*+ rule*/ t.* FROM V$ACCESS t WHERE t.object='DH_SP_H_MERGE_CUSTOMER_ECIF_9';

SELECT SID,SERIAL# FROM V$SESSION WHERE SID='1930';
alter system kill session '1930,50763';

（8）标准查询表空间情况
SELECT a.tablespace_name "表空间名",
       total / (1024 * 1024 * 1024) "表空间大小(G)",
       free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
       (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
       round((total - free) / total, 4) * 100 "使用率 %"
  FROM (SELECT tablespace_name, SUM(bytes) free
          FROM dba_free_space
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) total
          FROM dba_data_files
         GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name;
 
（9）查看锁表
  select l.session_id      sid,
        s.serial#,
        l.locked_mode     锁模式,
        l.ORACLE_USERNAME 登陆用户,
        l.os_user_name    登陆机器用户名,
        s.machine         机器名,
        s.terminal        终端用户名,
        o.object_name     被锁对象名,
        s.logon_time      登陆数据库时间
   from v$locked_object l, all_objects o, v$session s
  where l.OBJECT_ID = o.OBJECT_ID
    and l.SESSION_ID = s.SID
  order by sid, s.SERIAL#;

 

四、死锁的解决方法
     一般情况下，只要将产生死锁的语句提交就可以了，但是在实际的执行过程中。用户可
 能不知道产生死锁的语句是哪一句。可以将程序关闭并重新启动就可以了。
 　经常在Oracle的使用过程中碰到这个问题，所以也总结了一点解决方法。
  
1）查找死锁的进程： 
sqlplus "/as sysdba" (sys/change_on_install)
 SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
 l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS 
 FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;
 
2）kill掉这个死锁的进程： 
　　alter system kill session 'sid,serial#'; （其中sid=l.session_id） 

3）如果还不能解决： 
select pro.spid from v$session ses,v$process pro where ses.sid=XX and ses.paddr=pro.addr;
　　其中sid用死锁的sid替换: exit
 ps -ef|grep spid
　　其中spid是这个进程的进程号，kill掉这个oracle进程
kill -9 pid

(10)更新/插入：清洗某个时间段的表，因此需要将结果表相关的时间段数据删除，具体操作如下：

DELETE DH_C_YW_PRPCINSURED A
   WHERE EXISTS (SELECT 1
            FROM ODS.YW_PRPCINSURED B
           WHERE A.POLICYNO = B.POLICYNO
             AND A.SERIALNO = B.SERIALNO
             AND B.LASTMODIFYDATE >= V_STARTDATE
             AND B.LASTMODIFYDATE <= V_ENDDATE);

---在这里，A表是结果表，也就是清洗表。B是来源表。这段SQL的意思实际上是将A表中规定时间内与B表信息相符的数据删除。这里之所以这样做，是因为B中POLICYNO，SERIALNO是唯一值。所以，任何在被清洗时间段内的数据都会被删除，这样以来，可以执行更新/插入的效果。

--但是，其实还有一种我比较习惯的写法：

DELETE DH_C_YW_PRPCINSURED A

     where a.POLICYNO in (select b.POLICYNO from  ODS.YW_PRPCINSURED B)

     and A.SERIALNO IN ( select b.SERIALNO from  ODS.YW_PRPCINSURED B)

     AND B.LASTMODIFYDATE >= V_STARTDATE
      AND B.LASTMODIFYDATE <= V_ENDDATE；

    但是其实可以发现，条件较多的时候第一种更好。

 

(11)从某一天开始执行存储过程，共执行N天的方法：

declare
b varchar2(50);
SBGNDATE varchar2(100);
SENDDATE varchar2(100);
RETCODE varchar2(100):='0';
begin
  for i in 0..161 
  loop
  b:='2019/4/4';
  SBGNDATE:=to_char(to_date(b,'yyyy/mm/dd')+i,'yyyy/mm/dd');
  SENDDATE:=to_char(to_date(b,'yyyy/mm/dd')+i,'yyyy/mm/dd');
  -- Call the procedure
  DH_SP_T_REPORT_DATA_EXTRATION(SBGNDATE,
                     SENDDATE,
                     RETCODE);
   COMMIT;
   end loop;                                     
end;

 

（12）在调度存过中，循环执行某一存储过程的方法：

    DELETE FROM T_REPORT_ALL
    WHERE STATICDATE<=V_STARTDATE
    AND STATICDATE>=V_STARTDATE-30;
    COMMIT;
    FOR D IN 0 .. 30
    LOOP
    RBGNDATE := TO_CHAR(V_STARTDATE  - D, 'YYYY/MM/DD HH24:MI:SS');
    RENDDATE := TO_CHAR(V_STARTDATE  - D, 'YYYY/MM/DD HH24:MI:SS');
    V_ERRORMSG := 'DH_SP_T_REPORT_ALL';
    DH_SP_T_REPORT_ALL(RBGNDATE, RENDDATE, RETCODE);
    COMMIT;
    END LOOP;