Oracle数据库SQL总结

1Oracle时间段的查询

1.1 场景：根据用户输入的时间段过滤出相应记录。

1.2 解决办法：

第一种写法：

Sql代码 
SELECT * FROM T_XJXX_XJGL  
WHERE CREATEDATE >= TO_DATE(2011-6-13, yyyy-MM-dd)  
AND CREATEDATE <= TO_DATE(2011-6-17, yyyy-MM-dd); 

SELECT * FROM T_XJXX_XJGL
WHERE CREATEDATE >= TO_DATE(2011-6-13, yyyy-MM-dd)
AND CREATEDATE <= TO_DATE(2011-6-17, yyyy-MM-dd); 
第二种写法：

Sql代码 
SELECT * FROM T_XJXX_XJGL  
WHERE TO_CHAR(CREATEDATE, yyyy-MM-dd) >= 2011-6-13 
AND TO_CHAR(CREATEDATE, yyyy-MM-dd) <= 2011-6-16; 

SELECT * FROM T_XJXX_XJGL
WHERE TO_CHAR(CREATEDATE, yyyy-MM-dd) >= 2011-6-13
AND TO_CHAR(CREATEDATE, yyyy-MM-dd) <= 2011-6-16;

 第三种写法：

Sql代码 
SELECT * FROM T_XJXX_XJGL  
WHERE CREATEDATE > TO_DATE(2011-6-15, yyyy-MM-dd) - 1  
AND CREATEDATE <= TO_DATE(2011-6-16, yyyy-MM-dd) + 1; 

SELECT * FROM T_XJXX_XJGL
WHERE CREATEDATE > TO_DATE(2011-6-15, yyyy-MM-dd) - 1
AND CREATEDATE <= TO_DATE(2011-6-16, yyyy-MM-dd) + 1;

取出当前时间在开始时间和结束时间范围内的记录：

Sql代码 
SELECT * FROM T_XJXX_XJGL  
WHERE 1 = 1  
AND TO_CHAR(KSSJ, yyyy-mm-dd) <= TO_CHAR(SYSDATE, yyyy-mm-dd)  
AND TO_CHAR(JSSJ, yyyy-mm-dd) >= TO_CHAR(SYSDATE, yyyy-mm-dd) 

SELECT * FROM T_XJXX_XJGL
WHERE 1 = 1
AND TO_CHAR(KSSJ, yyyy-mm-dd) <= TO_CHAR(SYSDATE, yyyy-mm-dd)
AND TO_CHAR(JSSJ, yyyy-mm-dd) >= TO_CHAR(SYSDATE, yyyy-mm-dd)

2 Oracle创建触发器的例子

2.1场景：创建T_XJXX_XJGL中BH字段为自增长类型，start by 1 increment by 1

2.2解决方法：BH字段的类型设置为number，创建Sequence

Sql代码 
CREATE SEQUENCE  SEQ_XJXX_BH    
MINVALUE 1 MAXVALUE 999999   
INCREMENT BY 1 START WITH 11   
CACHE 10 NOORDER  NOCYCLE ; 

CREATE SEQUENCE  SEQ_XJXX_BH 
MINVALUE 1 MAXVALUE 999999
INCREMENT BY 1 START WITH 11
CACHE 10 NOORDER  NOCYCLE ; 创建触发器：

Sql代码 
CREATE OR REPLACE TRIGGER TR_ADDID  
BEFORE   INSERT   ON   T_XJXX_XJGL  
FOR   EACH   ROW  
BEGIN 
      IF (:NEW.BH IS NULL) THEN 
        SELECT SEQ_XJXX_BH.NEXTVAL INTO :NEW.BH FROM DUAL;  
      END IF;  
END; 

CREATE OR REPLACE TRIGGER TR_ADDID
BEFORE   INSERT   ON   T_XJXX_XJGL
FOR   EACH   ROW
BEGIN
      IF (:NEW.BH IS NULL) THEN
        SELECT SEQ_XJXX_BH.NEXTVAL INTO :NEW.BH FROM DUAL;
      END IF;
END;

3 Oracle中经常使用的函数

3.1场景：按照用户设定的定时器时间段，过滤出数据后Update下一执行时间。例如用户设定是邮件发送频率为每月15号 12:00:00发送，则计算出下一发送时间点为当前发送时间+1个月；设定频率为每周一 09:00:00发送，则计算出下一发送时间+一周

3.2解决方法：

1)、ADD_MONTHS(x,y)函数，平时我使用的场景也就是对月份进行加减时使用ADD_MONTHS函数，其中y若是负整数表示对月份的减操作；y是正整数表示对x月份添加y个月

Sql代码 
SELECT ADD_MONTHS(SYSDATE, -1) FROM DUAL 

SELECT ADD_MONTHS(SYSDATE, -1) FROM DUAL2)、NEXT_DAY(x,day)返回x日期下一个day的日期，新手需要注意的是这里的day指的是星期。1表示星期天、2表示星期一，以此类推。

Sql代码 
SELECT NEXT_DAY(SYSDATE, 2) FROM DUAL; 

SELECT NEXT_DAY(SYSDATE, 2) FROM DUAL; 返回从当前时间开始计算下一星期一的日期。

3)、如果是+1天或是-1天的操作可以直接对日期进行+-操作，like this

Sql代码 
SELECT SYSDATE-1 FROM dual  

SELECT SYSDATE-1 FROM dual oracle支持对日期进行运算，运算时是以天为单位进行的。

4)、LAST_DAY(x) 获取x月份中的最后一天

5)、MONTHS_BETWEEN(x,y);x>y返回正数,表示x和y之间相隔的月份数(实际项目中没使用过，暂时不做太多记录)。

字符串处理函数：

6)、nvl(x,value)，如果x is null 则返回value中的值，否则返回x；

7)、length(x)，返回x的字符长度；

8)、substr(x,start,length) 截取字符串，对字符串x进行截取，从start开始截取的长度为length；如果需要取字符串的后面几位可以这样写：

Sql代码 
SELECT SUBSTR(TEST, -2) FROM DUAL; 

 SELECT SUBSTR(TEST, -2) FROM DUAL; 返回最后两位字符 ST

4、Oracle中伪列的使用技巧

4.1场景：利用oracle中的伪列进行分页是一种简单方便的分页手段有些场景中我们也可以使用伪列来代替组函数，从而巧妙的实现过滤要求。

1 )  利用伪列取出工资最高的第6到第10名雇员的记录

Sql代码 
SELECT *  
  FROM (SELECT ROWNUM RN, TEMP.*  
          FROM (SELECT E.ENAME, E.SAL  
                  FROM EMP E  
                 WHERE ROWNUM <= 10  
                 ORDER BY E.SAL DESC) TEMP)  
 WHERE RN > 5 

 SELECT *
   FROM (SELECT ROWNUM RN, TEMP.*
           FROM (SELECT E.ENAME, E.SAL
                   FROM EMP E
                  WHERE ROWNUM <= 10
                  ORDER BY E.SAL DESC) TEMP)
  WHERE RN > 5
 
2 ) 利用伪列进行分页操作：

Sql代码 
SELECT B.*, RN  
  FROM (SELECT A.*, ROWNUM AS RN2  
          FROM (SELECT XXKC.WID,  
                       ...  
                       ROWNUM AS RN  
                  FROM T_PY_XXKC XXKC  
                  LEFT JOIN T_PY_KC KC ON KC.KCDM = XXKC.XXKCDM  
                 ORDER BY XXKC.XXKCDM) A) B  
 WHERE B.RN2 >  v_start   
 AND B.RN2 <=  (((v_start/10) + 1) * v_limit ) 

SELECT B.*, RN
  FROM (SELECT A.*, ROWNUM AS RN2
          FROM (SELECT XXKC.WID,
                       ...
                       ROWNUM AS RN