Oracle数据库SQL总结

1Oracle时间段的查询

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

1.2 解决办法：

第一种写法：

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

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

 创建触发器：

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个月

SELECT ADD_MONTHS(SYSDATE, '-1') FROM DUAL

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

SELECT NEXT_DAY(SYSDATE, 2) FROM DUAL;

 返回从当前时间开始计算下一星期一的日期。

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

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；如果需要取字符串的后面几位可以这样写：

 SELECT SUBSTR('TEST', -2) FROM DUAL;

 返回最后两位字符 ST

 

 

4、Oracle中伪列的使用技巧

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

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

 

 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 ) 利用伪列进行分页操作：

 

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 )

 这样，便可以利用前台传来的v_start(起始页)和v_limit(每页显示条数)和计算得出的totalCount(总页数)进行分页显示。此外RowNum还可以用来取某个结果集中的最大值，思路就是将结果集进行降序排序后取出rn = 1的记录就是最大值了，呵呵很简单吧!