【Oracle】第三单元 单行函数

dual为虚拟表

dual表是Oracle的一张虚拟表，这张表永远只有一条记录

select 10*9 from dual;

大小写转换函数

lower 全部转换成小写字母

select lower('SQL Course') from dual;

结果：sql course

upper全部转换成大写字母

select upper('SQL Course') from dual;

结果：SQL COURSE

inttcap全部转换成大写字母

select initcap(‘SQL course’) from dual;

结果：Sql Course

Oracle数据库中的数据是大小写敏感的

select employee_id,last_name,department_id from employees where last_name = 'higgins';

上面这样找不到数据 原因数据有大小写不一样

select employee_id,last_name,department_id from employees where lower(last_name) = 'higgins';

把表中所要查找的数据全部改成小写进行查找

字符串操作函数

函数	结果	说明
concat(‘Hello’,‘World’)	HelloWorld	把俩个单词拼接起来
substr(‘HelloWorld’,1,5)	Hello	从值得第1个字母开始截取到第5个
length(‘HelloWorld’)	10	字符串的长度
instr(‘HelloWorld’,‘W’)	6	W在字符串中处于第几位
lpad(salary,8,’#’)	###24000	向左用#补齐salary的值 直到8位
rpad(salary,8,’#’)	24000###	向右用#补齐salary的值 直到8位
trim(‘H’ from ‘HelloWorld’)	elloWorld	在字符串中去掉H
trim(’ HelloWorld ')	HelloWorld	什么都不加会去掉俩边的空格
trim(‘Hello World’)	Hello World	什么都不加不会去掉中间的空格

示例

select employee_id,
       concat(first_name, last_name) name,
       job_id,
       length(last_name),
       instr(last_name, 'a') "Contains 'a'?"
  from employees
 where substr(job_id, 4) = 'REP';

数字操作函数

函数	结果	说明
round(45.926, 2)	45.93	四舍五入保留俩位小数
trunc(45.926,2)	45.92	保留俩位小数后的直接舍去
mod(1600,300)	100	取余

示例：

select round(45.923, 2), round(45.923, 0), round(45.923, -1) from dual;

注：-1指的是小数点前面一位进行四舍五入，故第三个为50

select trunc(45.923, 2), trunc(45.923), trunc(45.923, -2) from dual;

注：-2指的是小数点前面俩位进行四舍五入，故第三个为0

select last_name,salary,mod(salary,5000) from employees where job_id=‘SA_REP’;

日期操作函数

函数	结果	说明
MONTHS_BETWEEN(‘01-SEP-95’,‘11-JAN-94’)	19.6774	比较俩个日期相差的月份
add_months(‘11-JAN-94’,6)	1994-07-11	日期加上相应的月份
NEXT_DAY (‘01-SEP-95’,‘FRIDAY’)	1995-09-08	日期的下一个星期五
select NEXT_DAY (‘01-SEP-95’,1) from dual;	1995-09-03	下一周的第一天 注：周日为第一天
last_day(‘01-FEB-95’)	1995-02-28	日期的最后一天
ROUND(‘25-JUL-95’,‘MONTH’)	1995-08-01	日 四舍五入
ROUND(‘25-JUL-95’ ,‘YEAR’)	1996-01-01	月份四舍五入
NEXT_DAY (TO_DATE(‘1995-09-01’,‘YYYY-MM-DD’),1)	1995/9/3	下一周第一天 转换成字符串否则会报错

可能会出错，原因是timestamp的日期格式 不同，解决办法如下：

alter session set nls_date_language='AMERICAN';

alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS.FF';

extract()函数：日期截取

select extract(day from sysdate) from dual

date类型中截取 year,month,day(date日期的格式为yyyy-mm-dd)

select extract(day from dt2 - dt1) day,
       extract(hour from dt2 - dt1) hour,
       extract(minute from dt2 - dt1) minute,
       extract(second from dt2 - dt1) second
  from (select to_timestamp('2011-02-04 15:07:00', 'yyyy-mm-dd hh24:mi:ss') dt1,
               to_timestamp('2011-05-17 19:08:46', 'yyyy-mm-dd hh24:mi:ss') dt2
          from dual)

select extract(year from systimestamp) year,
       extract(month from systimestamp) month,
       extract(day from systimestamp) day,
       extract(minute from systimestamp) minute,
       extract(second from systimestamp) second,
       extract(timezone_hour from systimestamp) th,
       extract(timezone_minute from systimestamp) tm,
       extract(timezone_region from systimestamp) tr,
       extract(timezone_abbr from systimestamp) ta
  from dual

TO_CHAR()函数：日期到字符串的转换

日期格式化元素	说明
YYYY	4位数字表示的年份
YEAR	英文描述的年份
MM	2位数字表示的月份
MONTH	英文描述的月份
MON	三个字母的英文描述月份简称
DD	2位数字表示的日期
DAY	英文描述的星期几
DY	三个字母的英文描述的星期几简称
HH24:MI:SS AM	时分秒的格式化
DDspth	英文描述的月中第几天
fm	格式化关键字，可选

示例：

select to_char(sysdate,'YYYY') FROM DUAL;

select to_char(sysdate,'DAY') FROM DUAL;

日期运算

SELECT last_name,
       SYSDATE - hire_date,
       (SYSDATE - hire_date) / 7 AS weeks,
       SYSDATE + 1 AS tomorrow,
       hire_date + 8 / 24
  FROM employees
 WHERE department_id = 90;

入职的天数

入职的多少周 除以7

明天的日期

计算入职的1/3天是是什么时间

Oracle 数据类型的 隐私转换规则

对于赋值操作可以：

从	到
VARCHAR2 or CHAR	NUMBER
VARCHAR2 or CHAR	DATE
NUMBER	VARCHAR2
DATE	VARCHAR2

对于表达式比较操作仅可以：

从	到
VARCHAR2 or CHAR	NUMBER
VARCHAR2 or CHAR	DATE

TO_CHAR() 函数：数字到字符串的转换

数字格式化元素	说明
9	表示一个数字
0	强制显示0
$	放一个美元占位符
L	使用浮点本地币种符号
.	显示一个小数点占位符
,	显示一个千分位占位符

示例：

SELECT salary, TO_CHAR(salary, 'L99,999.00') SALARY FROM employees WHERE last_name = 'Ernst';

结果$6000

alter session set NLS_CURRENCY = '￥';

注：语言环境更改成人民币

SELECT salary, TO_CHAR(salary, 'L99,999.00') SALARY FROM employees WHERE last_name = 'Ernst';

结果￥6,000

TO_NUMBER()函数：字符串到数字的转换

TO_NUMBER应用	正确与否
select to_number(‘4456’)from dual	√
select to_number(’$4,456’)from dual	×
select to_number(’$4,456’,’$9,999’)from dual	√
select to_number(’$4,456,455.000’,’$9,999,999’)from dual	×
select to_number(’$4,456,455.000’,’$9,999,999.999’)from dual	√

TO_DATE()函数：字符串到日期的转换

TO_DATE应用	正确与否
select to_date(‘22-FEB-11’)from dual	√
select to_date(‘2011/2/22’)from dual	×
select to_date(‘2011/2/22’,‘YYYY-MM-DD’)from dual	√
select to_date(‘2/22/2011’,‘MM-DD-YYYY’)from dual	√
select to_date(‘2011/FEB/22’,‘YYYY-MON-DD’)from dual	√

其他常用单行函数

函数	说明
NVL (expr1, expr2)	如果expr1为空，这返回expr2
NVL2 (expr1, expr2, expr3)	如果expr1为空，这返回expr3（第2个结果）否则返回expr2
NULLIF (expr1, expr2)	如果expr1和expr2相等，则返回空
COALESCE (expr1, expr2, …, exprn)	如果expr1不为空，则返回expr1,结束；否则计算expr2,直到找到一个不为NULL的值 或者如果全部为NULL，也只能返回NULL了

示例：

SELECT last_name,
       salary,
       NVL(commission_pct, 0),
       (salary * 12) + (salary * 12 * NVL(commission_pct, 0)) AN_SAL
  FROM employees;

SELECT last_name,
       salary,
       commission_pct,
       NVL2(commission_pct, 'SAL+COMM', 'SAL') income
  FROM employees
 WHERE department_id IN (50, 80);

SELECT first_name,
       LENGTH(first_name) "expr1",
       last_name,
       LENGTH(last_name) "expr2",
       NULLIF(LENGTH(first_name), LENGTH(last_name)) result
  FROM employees;

条件表达式

CASE语句

SELECT last_name,
       job_id,
       salary,
       CASE job_id
         WHEN 'IT_PROG' THEN
          1.10 * salary
         WHEN 'ST_CLERK' THEN
          1.15 * salary
         WHEN 'SA_REP' THEN
          1.20 * salary
         ELSE
          salary
       END "REVISED_SALARY"
  FROM employees;

DECODE函数

SELECT last_name,
       job_id,
       salary,
       decode(job_id,
              'IT_PROG',
              1.10 * salary,
              'ST_CLERK',
              1.15 * salary,
              'SA_REP',
              1.20 * salary,
              salary) revised_salary
  FROM employees;