单 行 函 数
•操作数据对象
•接受参数返回一个结果
•只对一行进行变换
•每行返回一个结果
•可以转换数据类型
•可以嵌套
•参数可以是一列或一个值
大小写控制函数：这类函数改变字符的大小写
在查询表中的记录时，条件为字符串，而字符串是区分大小写的当不知道是大写还是小写的时候，只要是是同一个字母就可以时，那么可以使用大小写函数
查询结果是：（符合字母，不管是大写还是小写都可以查询的出来了）
字符控制函数：这类函数控制字符
select concat('Hello','World'),substr('HelloWorld',1,5), length('HelloWorld'),instr('HelloWorld','W') from dual
concat：表示连接两个字符
substr：第一个数表示开始数，SQL中开始是从1算起，java是从0算起，第二个参数个数，所以表示从第一个字符开始获取，获取5个字符
length：判断字符串的长度
instr：某个字符在字符串中的位置，从1算起，如果没有返回0
select employee_id,last_name,lpad(salary,10,'*') from employees
lpad：（右对齐）表示总共显示10位，前面为0的拿在第三个参数中指定的符号替换
select employee_id,last_name,rpad(salary,10,'*') from employees
rpad：(左对齐)
select trim('h' from 'hfjdkahfdh') from dual
select trim('H' from 'HfjdkahfdHHH') from dual
trim：表示取出两端所有指定的字符，区分大小写
select replace('abcd','b','m') from dual
select replace('abbcdb','b','m') from dual
replace：替换
数字函数：
ROUND: 四舍五入
select round(45.923,2),round(45.923,0),round(45.923,-1) from dual
第二个参数表示保留多少位小数，可以为负数，也不可以不写，不写默认是0
TRUNC: 截断
select trunc(45.923,2),trunc(45.923,0),trunc(45.923,-1) from dual
第二个参数表示保留 多少位数，多余的不要
MOD: 求余
select mod(1100,200) from dual
表示1100模于200
select last_name,salary,mod(salary,5000) from employees where job_id='SA_REP'
日 期：Oracle 中的日期型数据实际含有两个值: 日期和时间
函数SYSDATE 返回:日期和时间
日期的数学运算
在日期上加上或减去一个数字结果仍为日期。
两个日期相减返回日期之间相差的天数。
–日期不允许做加法运算，无意义
可以用数字除24来向日期中加上或减去天数。
select last_name,months_between(sysdate,hire_date) from employees
select add_months(sysdate,2),add_months(sysdate,-3),next_day(sysdate,'星期日') from dual
select last_day(sysdate) from dual
select round(sysdate,'month'),round(sysdate,'mm'),trunc(sysdate,'hh') from dual
隐式数据类型转换：Oracle 自动完成下列转换
显式数据类型转换
TO_CHAR函数对日期的转换
格式:
•必须包含在单引号中而且大小写敏感。
•可以包含任意的有效的日期格式。
•日期之间用逗号隔开。
select to_char(sysdate,'yyyy-mm-dd hh:mm:ss') from dual
select last_name,to_char(hire_date,'yyyy"年"mm"月"dd"日"') from employees where to_char(hire_date,'yyyy"年"mm"月"dd"日"')='1994年06月07日'
TO_CHAR函数对数字的转换
select to_char(1234567.54,'999,999,999.99') from dual
select to_char(1234567.54,'000,000,000.00') from dual
格式化的时候加上货币符号
select last_name,to_char(salary,'$000,000,000.00') from employees
select last_name,to_char(salary,'L000,000,000.00') from employees
TO_NUMBER 函数对字符的转换
通用函数:这些函数适用于任何数据类型，同时也适用于空值
•NVL (expr1, expr2)
•NVL2 (expr1, expr2, expr3)
•NULLIF (expr1, expr2)
•COALESCE (expr1, expr2, ..., exprn)
NVL 函数:将空值转换成一个已知的值.可以使用的数据类型有日期、字符、数字。
函数的一般形式:
–NVL(commission_pct,0)
–NVL(hire_date,'01-JAN-97')
–NVL(job_id,'No Job Yet')
求公司员工的年薪（含commission_pct)(年薪是12个月的工资乘以奖金率，因为有些员工没有奖金率，所以当乘以奖励率时，没有奖金的员工就变成0了 所以使用该函数)
select employee_id,last_name,salary*12*(1+nvl(commission_pct,0)) "annual_sal" from employees
输出last_name,department_id，当department_id为null时，显示‘没有部门’。（这里department_id是数值型的，而'显示部门是字符型'，所以要转换一个数值类型，变成统一类型，这里转换department_id）
select last_name,nvl(to_char(department_id,'999999'),'没有部门') from employees
使用NVL2 函数:NVL2 (expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3。
select last_name,salary,commission_pct, nvl2(commission_pct,'sal+comm','sal') "income" from employees where department_id in (50,80)
查询员工的奖金率，若为空，返回0.01,若不为空，返回实际奖金率+0.015
select last_name,salary,commission_pct, nvl2(commission_pct,commission_pct+0.15,0.01) "income" from employees where department_id in (50,80)
NULLIF 函数:NULLIF (expr1, expr2) : 相等返回NULL，不等返回expr1
select first_name,length(first_name) "expr1", last_name,length(last_name) "expr2", nullif(length(first_name),length(last_name)) result from employees
COALESCE 函数
COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。
如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE 。
select last_name,coalesce(commission_pct,salary,10) comm from employees order by commission_pct
条件表达式
在 SQL 语句中使用IF-THEN-ELSE 逻辑
使用两种方法:
–CASE 表达式
–DECODE 函数
CASE 表达式:
查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数
select employee_id,last_name,department_id,case department_id when 10 then salary*1.1 when 20 then salary*1.2 else salary*1.3 end new_sal from employees where department_id in(10,20,30)
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 employee_id,last_name,department_id,decode(department_id, 10,salary*1.1, 20,salary*1.2, salary) new_sal from employees where department_id in(10,20,30)
嵌套函数:
•单行函数可以嵌套。
•嵌套函数的执行顺序是由内到外。
打印出 "2009年10月14日 9:25:40" 格式的当前系统的日期和时间.（注意: 使用双引号向日期中添加字符）
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh:mi:ss') from dual
格式化数字: 1234567.89 为 1,234,567.89
select to_char(1234567,'999,999,999.99') from dual
字符串转为数字时
1). 若字符串中没有特殊字符, 可以进行隐式转换:
select '12345.432'+100 from dual
2). 若字符串中有特殊字符, 例如 '1,234,567.89', 则无法进行隐式转换, 需要使用 to_number() 来完成
select to_number('1,234,567.89','999,999,999.99')+100 from dual
对于把日期作为查询条件的查询, 一般都使用 to_date() 把一个字符串转为日期, 这样可以不必关注日期格式
select last_name, hire_date from employees where hire_date = to_date('1998-5-23', 'yyyy-mm-dd') -- where to_char(hire_date,'yyyy-mm-dd') = '1998-5-23'
转换函数: to_char(), to_number(), to_date()
查询每个月倒数第 2 天入职的员工的信息.
select last_name, hire_date from employees where hire_date = last_day(hire_date) - 1
计算公司员工的年薪
--错误写法: 因为空值计算的结果还是空值 select last_name, salary * 12 * (1 + commission_pct) year_sal from employees
--正确写法 select last_name, salary * 12 * (1 + nvl(commission_pct, 0)) year_sal from employees
查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数
--使用 case-when-then-else-end select last_name, department_id, salary, case department_id when 10 then salary * 1.1 when 20 then salary * 1.2 when 30 then salary * 1.3 end new_sal from employees where department_id in (10, 20, 30)
--使用 decode select last_name, department_id, salary, decode(department_id, 10, salary * 1.1, 20, salary * 1.2, 30, salary * 1.3 ) new_sal from employees where department_id in (10, 20, 30)
显示系统时间(注：日期+时间)
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual
--查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary） select employee_id,last_name,salary,salary*1.2 "new salary" from employees
--将员工的姓名按首字母排序，并写出姓名的长度（length） select last_name,length(last_name) from employees order by last_name asc
--查询各员工的姓名，并显示出各员工在公司工作的月份数（worked_month） select last_name,hire_date,round(months_between(sysdate,hire_date),1) "worked_month" from employees
--查询员工的姓名，以及在公司工作的月份数（worked_month），并按月份数降序排列 select last_name,hire_date,round(months_between(sysdate,hire_date),1) "worked_month" from employees order by "worked_month" desc
注意：需要加上双引号
select last_name || ' earsn ' || to_char(salary,'$999999') || ' monthly but wants ' || to_char(3*salary,'$999999') "Dream Salary" from employees
select last_name "Last_name",job_id "Job_id", decode(job_id, 'AD_PRES','A', 'ST_MAN','B', 'IT_PROG','C', 'SA_REP','D', 'ST_CLERK','E') "Grade" from employees
用case函数
select last_name "Last_name",job_id "Job_id",case job_id when 'AD_PRES'then 'A' when 'ST_MAN' then 'B' when 'IT_PROG' then 'C' when 'SA_REP' then 'D' when 'ST_CLERK' then'E' end "Grade" from employees
