首页 > 代码库 > Oracle 中的sql函数以及分页
Oracle 中的sql函数以及分页
SELECT LPAD(‘Page 1‘,15,‘*.‘) "LPAD example" FROM DUAL;
1.分页查询
（1）方法一：使用 between and 来实现分页
select * from ( select emp.*,rownum rn from emp ) where rn between 4 and 6
（2）方法二：使用 rownum 来实现分页
select * from ( select emp.*,rownum rn from emp where rownum<=6 ) where rn>=4
（3）方法三：使用rownum 三层来实现分页
select * from
(
select emp.*,rownum rn from
(
select * from emp
)emp
where rownum<=6
)
where rn >=4
得到的结果如下：
2.分析函数
（1）rank() :rank()函数返回一个唯一的值，当遇到相同的数据时，所有相同数据的排名是一样的，
同时会在最后一条相同记录和下一条不同记录之间空出排名。
select ename,deptno,sal, rank() over(partition by deptno order by sal desc) "RANK" --dense_rank() over(partition by deptno order by sal desc)"dense_rank" --row_number() over(partition by deptno order by sal desc)"row_number" from emp
结果：
（2）dense_rank() :dense_rank()函数返回一个唯一的值，当遇到相同数据时，所有相同的数据的排名都是一样的。
select ename,deptno,sal, --rank() over(partition by deptno order by sal desc) "RANK" dense_rank() over(partition by deptno order by sal desc)"dense_rank" --row_number() over(partition by deptno order by sal desc)"row_number" from emp
（3）row_number():row_number()函数返回一个唯一的值，当遇到相同数据时，排名按照记录集中记录的顺序依次递增。
select ename,deptno,sal, --rank() over(partition by deptno order by sal desc) "RANK" --dense_rank() over(partition by deptno order by sal desc)"dense_rank" row_number() over(partition by deptno order by sal desc)"row_number" from emp
（4）合并结果：
select ename,deptno,sal, rank() over(partition by deptno order by sal desc) "RANK", dense_rank() over(partition by deptno order by sal desc)"dense_rank", row_number() over(partition by deptno order by sal desc)"row_number" from emp
3.SQL函数
（1）字符函数
1.大小写控制函数
lower():全部小写
SELECT LOWER(‘MR. SCOTT MCMILLAN‘) "Lowercase" FROM DUAL;
upper()：全部大写
SELECT UPPER(‘abc‘) "Uppercase" FROM dual;
initcap()：首字母大写
SELECT initcap(‘MR. SCOTT MCMILLAN‘) "initcap" FROM DUAL;
2.字符控制函数
concat:拼接字符串
select concat(‘Happy‘,‘Boy‘) from dual;
substr:截取字符串
substr(‘要截取的字符串‘,起始位置) 说明：位置从1开始
select substr(‘HappyBoy‘,4) from dual;
substr(‘要截取的字符,起始位置,取几个字符)
select substr(‘HappyBoy‘,2,3) from dual;
length(‘字符串‘)：字符个数统计
lengthb(‘字符串‘)：字节个数统计
select length(‘快乐‘) 字符数，lengthb(‘快乐‘) as 字节数 from dual;
instr(‘大字符串‘,‘小字符串‘)返回小字符串在大字符串中出现的位置
SELECT INSTR(‘CORPORATE FLOOR‘,‘OR‘, 3, 2) "Instring" FROM DUAL;
lpad()和rpad()
SELECT LPAD(‘Page 1‘,15,‘*.‘) "LPAD example" FROM DUAL;
SELECT ename, RPAD(‘ ‘, sal/1000/1, ‘*‘) "Salary" FROM emp WHERE deptno=20 ORDER BY ename, "Salary";
trim()
select trim(‘a‘ from ‘ahappy‘) from dual;
（2）日期函数
1.日期函数
months_between()两个日期相差的月数
SELECT MONTHS_BETWEEN (TO_DATE(‘02-02-1995‘,‘MM-DD-YYYY‘),TO_DATE(‘01-01-1995‘,‘MM-DD-YYYY‘) ) "Months" FROM DUAL;
add_months():向指定日期中加上若干月数
SELECT TO_CHAR(ADD_MONTHS(hiredate, 1), ‘DD-MON-YYYY‘) "Next month" FROM emp WHERE ename = ‘JONES‘;
2.日期相减
两个日期相差的天数
select floor(sysdate-to_date(‘2015-12-12‘,‘yyyy-MM-dd‘)) from dual;
两个日期相差的月数
select months_between(sysdate,to_date(‘20151212‘,‘yyyyMMdd‘)) from dual;
（3）转换函数
1.隐式转换
select * from emp where hiredate=‘17-12月-80‘;
2.显式转换
to_char()对日期的转换
select to_char(sysdate,‘yyyy-MM-dd hh24:mi:ss‘) from dual;
to_char()对数字的转换
select to_char(sal,‘L9999.99‘) from emp;
（4）数字函数
1.round():四舍五入
select round(12.45,1) from dual;
2.trunc
SELECT TRUNC(TO_DATE(‘2015-12-12‘,‘yyyy-MM-dd‘), ‘YEAR‘) "New Year" FROM DUAL;
select trunc(15.71,1) "trunc" from dual;
（5）通用函数
1.nvl()滤空函数
SELECT SAL*12 工资，comm 奖金，sal*12+nvl(comm,0) from emp;
2.nvl2()滤空函数
SELECT SAL*12 工资，comm 奖金，sal*12+nvl2(comm,comm,0) from emp;
3.不用滤空函数
SELECT SAL*12 工资，comm 奖金，sal*12 from emp;
（6）decode函数
SELECT product_id, DECODE (warehouse_id,
1, ‘Southlake‘, 2, ‘San Francisco‘, 3, ‘New Jersey‘, 4, ‘Seattle‘, ‘Non domestic‘) "Location" FROM inventories WHERE product_id < 1775 ORDER BY product_id, "Location";
Oracle 中的sql函数以及分页