Oracle常用语句

2017.12.20

1.输出当月的所有天数

SELECT TRUNC(SYSDATE, 'MM') + ROWNUM - 1
  FROM DUAL
CONNECT BY ROWNUM <= TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE), 'DD'))

输出:
2017/12/1
......
2017/12/31

2.在一条语句里先分组后排序

语法: row_number() over(partition by column1 order by column2 asc) 先按照column1分组，再对分组后的数据进行以column2升序排列

select psnId,actionType,row_number() over(partition by psnId order by actionType asc) rn from person 

3.用sqlplus远程连接oracle命令

语法: sqlplus usr/pwd@//host:port/sid
实例: $ sqlplus userName/password@//192.168.1.66:1521/orcl

如果已经配置远程实例,则可以使用sqlplus userName/password@远程实例名直接连接即可

4.使用WITH子句重用子查询

从Oracle9i开始，通过WITH子句可以给子查询指定一个名称，并且使得在一条语句中可以完成所有任务，从而避免使用临时表。如显示部门工资总和高于雇员工资总和三分之一的部门名称及工资总和。

WITH summary AS (SELECT dname,SUM(sal) AS dept_total FROM emp,dept WHERE emp.deptno=dept.deptno GROUP BY dname)   
SELECT dname, dept_total FROM summary WHERE dept_total>(   
SELECT SUM(dept_total) * 1/3 FROM summary);  

5.获取年份第一天/最后一天和月份第一天和最后一天

（1）--获取上个月的第一天
SELECT TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, -1), 'mm'), 'yyyy-mm-dd')FROM DUAL；

SYSDATE为系统当前时间：可以更改为字符格式，然后to_date如：

SELECT TO_CHAR(TRUNC(ADD_MONTHS(to_date('2017-09-09','yyyy-mm-dd'), -1), 'mm'), 'yyyy-mm-dd')FROM DUAL​；输出结果为：2017-08-01

（2）--获取上个月的最后一天
(SELECT LAST_DAY(ADD_MONTHS(to_date('2017-09-23','yyyy-mm-dd'), -1)) FROM DUAL)；输出结果为：2017-08-31

（3）--获取本月第一天
​(SELECT LAST_DAY(ADD_MONTHS(to_date('2017-09-23','yyyy-mm-dd'), -1))+1 FROM DUAL)；输出结果为：2017-09-01（即在上个月的最后一天的基础上加1天即可）

（4）--获取本月最后一天
select last_day(trunc(to_date('2017-09-09','yyyy-mm-dd'))) from dual​；输出结果为：2017-09-30

（5）--获取本年度第一天
select trunc(to_date('2017-05-09','yyyy-mm-dd'),'yyyy') FROM DUAL;​输出结果为：2017-01-01​

（6）--获取本年度最后一天
select last_day(add_months(trunc(to_date('2017-08-08','yyyy-mm-dd'),'yyyy'),11)) from dual；输出结果为：2017-12-31

6.Oracle自定义函数

create or replace function get_empname(v_id in number) 
return varchar2 as v_name varchar2(50);

　　begin
　　select name into v_name from employee where id = v_id;
　　return v_name;

　　exception
　　when no_data_found then raise_application_error(-20001, '你输入的ID无效!');

end get_empname;

if ExpireTime is null then
　　if m_flag = 'String' then
　　return '正常';
end if;

7.Oracle查询前面加序号: 在外层用嵌套

select rownum,a.col1,a.clo2,a.counts from 
(select col1,col2,count(*) counts  from table1 group by col1,col2) a;

8.instr函数

语法如下： 
　　instr( string1, string2 [, start_position [, nth_appearance ] ] ) 
　　参数分析： 
　　string1 
　　源字符串，要在此字符串中查找。 
　　string2 
　　要在string1中查找的字符串. 
　　start_position

注意： 

　　如果String2在String1中没有找到，instr函数返回0. 

　　示例： 

　　SELECT instr('syranmo','s') FROM dual; -- 返回 1 
　　SELECT instr('syranmo','ra') FROM dual; -- 返回 3 
　　SELECT instr('syran mo','a',1,2) FROM dual; -- 返回 0 

9.oracle递归语句

create table YourTable (Job int, Repeat int);
insert into YourTable
    select 1,2 from dual
    union all select 2,0 from dual
    union all select 3,1 from dual
    union all select 4,3 from dual;

with    cte(Job, Repeat, i) as 
        (
        select  Job
        ,       Repeat
        ,       0
        from    YourTable
        union all
        select  Job
        ,       Repeat
        ,       i + 1
        from    cte
        where   cte.i < cte.Repeat
        )
select  *
from    cte
order by
        Job
,       i

更新列表：

参考文章：

Oracle常用语句

相关阅读