Oracle常用函数
日期处理
1、sysdate
获取当前日期
SQL> select sysdate from dual;
SYSDATE
--------------
24-11月-152、systimestamp函数
获取当前日期、时间，精确到毫秒级
SQL> select systimestamp from dual;
SYSTIMESTAMP
----------------------------------------
03-12月-15 03.28.15.834000 下午 +08:003、add_months(date,n1)
增加或者减少n1个月
SQL> select add_months('17-1月-80',2) from dual;
ADD_MONTHS('17
--------------
17-3月 -80
SQL> select add_months('17-1月-80',-2) from dual;
ADD_MONTHS('17
--------------
17-11月-794、to_date(date,dateType)
将一个日期按照对应的日期格式转化为oracle标准日期格式
SQL> select to_date('201501','yyyymm') from dual;
TO_DATE('20150
--------------
01-1月 -15
select to_date('2015-1-19','yyyy-mm-dd') from dual;
select to_date('2015/1/19','yyyy/mm/dd') from dual;5、last_day
返回日期的最后一天
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDA
--------------
30-11月-156、next_day(date,’day’)
返回下一个星期n（day）的日期
SQL> select next_day(sysdate,'星期二') from dual;
NEXT_DAY(SYSDA
--------------
01-12月-157、to_char(date,dateType)
SQL> select to_char(sysdate,'yyyy-mm-dd hh:mm:ss') date from dual;
DATE
-------------------
2015-11-24 05:11:268、extract()
1、日期截取函数，可以截取日期中的年、月、日、时、分、秒
2、获取两个日期之间的间隔
//截取年、月、日
SQL> select extract(year from to_date('2015-11-22','yyyy-mm-dd')) year,
extract(month from to_date('2015-11-22','yyyy-mm-dd')) month,
extract(day from to_date('2015-11-22','yyyy-mm-dd')) day from dual
YEAR MONTH DAY
---------- ---------- ----------
2015 11 22
//截取年并比较
SQL> select * from emp where extract(year from hiredate) = '1987';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
//获取两个日期之间的间隔
SQL> select extract(day from dt2-dt1) day
2 ,extract(hour from dt2-dt1) hour
3 ,extract(minute from dt2-dt1) minute
4 ,extract(second from dt2-dt1) second
5 from (
6 select to_timestamp('2015-11-4 2:07:00','yyyy-mm-dd hh24:mi:ss') dt1,
7 to_timestamp('2018-8-8 19:08:46','yyyy-mm-dd hh24:mi:ss') dt2
8 from dual);
DAY HOUR MINUTE SECOND
---------- ---------- ---------- ----------
1008 17 1 46
//当前日期分割
SQL> select extract(year from systimestamp) year
2 ,extract(month from systimestamp) month
3 ,extract(day from systimestamp) day
4 ,extract(minute from systimestamp) minute
5 ,extract(second from systimestamp) second
6 ,extract(timezone_hour from systimestamp) th
7 ,extract(timezone_minute from systimestamp) tm
8 ,extract(timezone_region from systimestamp) tr
9 ,extract(timezone_abbr from systimestamp) ta
10 from dual;
YEAR MONTH DAY MINUTE SECOND TH TM TR
---------- ---------- ---------- ---------- ---------- ---------- ---------- --------
2015 11 27 50 44.77 8 0 UNKNOWN9、instr()
//获取指定字符串自原字符串中出现的位置
//instr(source,targer,[起始位置])
SQL> select instr('hello','he') from dual;
INSTR('HELLO','HE')
-------------------
1
示例
//邮箱@符号验证
//起始位置6的作用是用来判断@之前的字长度是否满足6。
SQL> select instr('QQ764073542@gmail.com','@',6) from dual;
INSTR('QQ764073542@GMAIL.COM','@',6)
------------------------------------
12
SQL> select instr('QQ@gmail.com','@',6) from dual;
INSTR('QQ@GMAIL.COM','@',6)
---------------------------
0字符串处理
1、chr
给出整数,返回对应的字符;
select chr(12345) as A,chr(66) as B from dual;
A B
-- -
09 B2、concat
连接两个字符串：concat函数或者 || 连接符
select concat('000-','88888888')||'转88' as 电话 from dual;
电话
----------------
010-88888888转883、initcap
返回字符串并将字符串的第一个字母变为大写
select initcap('demo') from dual;
INIT
----
Demo4、length
返回字符串的长度
SQL> select length('demo')length from dual;
LENGTH
------
4
5、lower
返回字符串,并将所有的字符小写
SQL> select lower('ABCEDFG') lower from dual;
LOWER
-------
abcedfg6、upper
返回字符串,并将所有的字符大写
SQL> select upper('abcedfg') upper from dual;
UPPER
-------
ABCEDFG7、substr(string,start,count)
取子字符串,从start开始,取count个 #从1开始计数
SQL> select substr('abcdefg',2,2) from dual;
SU
--
bc
select substr('abcdefg',0,2) from dual;
select substr('abcdefg',1,2) from dual;
这两句执行结果相同，都为
SU
--
ab8、replace(‘string’,’s1’,’s2’)
string 希望被替换的字符或变量
s1 被替换的字符串
s2 要替换的字符串
SQL> select replace('abcdefghijk','abc','zzz') replace from dual;
REPLACE
-----------
zzzdefghijk
9、to_number
将字符串转化为数字
SQL> select to_number('45')as num from dual;
NUM
----------
45数学函数
1、abs
取绝对值
SQL> select abs(10),abs(-5) from dual;
ABS(10) ABS(-5)
---------- ----------
10 52、ceil
返回大于或等于给出数字的最小整数,即 [上取整]
SQL> select ceil(5.9),ceil(6.1) from dual;
CEIL(5.9) CEIL(6.1)
---------- ----------
6 7
3、exp
求 e^n次方根
SQL> select exp(1),exp(10) from dual;
EXP(1) EXP(10)
---------- ----------
2.71828183 22026.46584、ln
求一个数的对数值
SQL> select ln(1),ln(10) from dual;
LN(1) LN(10)
---------- ----------
0 2.302585095、log(n1,n2)
返回以n1为底n2的对数
SQL> select log(2,4)from dual;
LOG(2,4)
----------
26、mod(n1,n2)
返回 n1%n2 的结果，即n1/n2的余数
SQL> select log(2,4)from dual;
LOG(2,4)
----------
27、power(n1,n2)
返回n1的n2次方根
SQL> select power(2,10) from dual;
POWER(2,10)
-----------
10248、round
四舍五入
SQL> select round(9.9)from dual;
ROUND(9.9)
----------
109、trunc
①trunc(n1)
不进位的四舍五入 [下取整]
SQL> select trunc(9.9)from dual;
TRUNC(9.9)
----------
9②trunc(n1,n2)
按照n2的指定值来截取一个数
SQL> select trunc(999.9999,3) from dual; #保留小数点后三位
TRUNC(999.9999,3)
-----------------
999.999
SQL> select trunc(999.9999,-2) from dual;#整数部分截取掉前两位
TRUNC(999.9999,-2)
------------------
900常用查询函数
1、avg、sum、max、min、count
SQL> select avg(sal),sum(sal),max(sal),min(sal),count(sal) from emp;
AVG(SAL) SUM(SAL) MAX(SAL) MIN(SAL) COUNT(SAL)
---------- ---------- ---------- ---------- ----------
2073.21429 29025 5000 800 142、group by
分组查询
SQL> select deptno,count(*) from emp group by deptno;
DEPTNO COUNT(*)
---------- ----------
30 6
20 5
10 33、having
对分组进行筛选
SQL> select deptno,count(*) from emp group by deptno having count(*)>=5;
DEPTNO COUNT(*)
---------- ----------
30 6
20 54、order by
排序 [升序：asc][降序：desc]
select * from emp order by sal asc;
SAL
----------
800
950
1100
1250
1250
1300
1500
1600
2450
2850
2975
3000
3000
50005、minus函数
//取差集
例如：
a={2,4,6,8}
b={2,5,7,8}
c={1,3,5,7}
a minus b = {4,6}
b minus a = {5,7}
a minus c = {2,4,6,8}
它运用在两个SQL语句上，它先找出第一条SQL语句所产生的结果，然后看这些结果有没有在第二个SQL语句的结果中。如果有的话，那这一条记录就被去除，而不会在最后的结果中出现。如果第二个SQL语句所产生的结果并没有存在于第一个SQL语句所产生的结果内，那这条记录就被抛弃。
结果集：
//第一个结果集 a
SQL> select * from emp where sal < 1300;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
//第二个结果集 b
SQL> select * from emp where job='CLERK';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
//第三个结果集 c
SQL> select * from emp where sal > 1500;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
已选择7行。查询：
//a minus b
SQL> select * from emp where sal < 1300
2 minus
3 select * from emp where job='CLERK';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
//b minus a
SQL> select * from emp where job='CLERK'
2 minus
3 select * from emp where sal < 1300;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-1月 -82 1300 10
//a minus c = 原集合
SQL> select * from emp where job='CLERK'
2 minus
3 select * from emp where sal > 1500;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
6、 raise_application_error(number, msg);
//抛出异常,阻止程序继续执行实验
限制对dept表修改（包括INSERT,DELETE,UPDATE）的时间范围，即不允许在非工作时间修改dept表。
思路：
（1）非工作时间：周一到周五的8：30到18：00以外的时间、星期六、星期日。
（2）怎么求非工作时间，sysdate函数可以求当前时间，to_char()函数可以求当前时间是几点几分，可以求今天是星期几。
（3）if 当前时间 in(….) or (TO_CHAR(sysdate, ‘HH24:MI’) NOT BETWEEN ‘08:30’ AND ‘18:00’)
create or replace trigger modify_dept
before insert or update or delete on dept for each row
begin
if to_char(sysdate,'day') in ('星期六','星期日') or to_char(sysdate,'hh24:mm') not between '08:30' and '18:00' then
RAISE_APPLICATION_ERROR(-20001, '非上班时间，禁止修改.');
end if;
end;