Oracle 单行函数

开发工具与关键技术：PLSQL Developer、Oracle、SQL*plus
作者：邓恩明
撰写时间：2019年04月02日
Oracle 单行函数
备注(以下SQL语句)：employees 为员工信息表，sysdate为系统日期时间

Lower() 将括号内的内容转换成小写；Upper() 与 Lower() 作用相反，将括号内的内容转换成大写；Initcap() 将括号内的首字母转换成大写; 例如下面转换employees中的last_name列：
SQL 语句：select lower(last_name) as 小写，upper(last_name) as 大写，initcap(last_name) as 首字母大写 from employees ；

Concat ( ‘A’ , ’B’ ) 将括号内的A与B合并在一起；Substr( ‘A’ , b , n ) 将A字段从第b位开始截取后面的n位字符；Length() 计算括号里面内容的字符长度；Instr( ‘A’ ，‘a’)计算在A字段中a字母在顺数第几位; Lpad( A , n , ‘x’) 在A字段中截取n位数，如果不足n位就用x从左边进行填充；Rpad(A , n , ‘x’)与lpad() 作用基本相同，但它是从右边进行填充；Trim( ‘B’ from ‘A’ )从A字段的首尾字符中除去B字符(B只能是一个字符)；Replace( ‘A’，‘B’ ，‘C’)将在A字段中的B字段转换成C字段（B字段和C字段长度要一致）；例如下面转换employees中的某些列：
SQL语句：select Concat(first_name,last_name)as 合并,
Substr(last_name,2,3)as 截取第2位后的3位字符，
length(last_name)as 长度, Instr(last_name,‘a’)as 字母a在第几位,
Lpad(salary,10,’’)as 左填充,Rpad(salary,10,’’)as 右填充,
Trim(‘K’ from last_name)as 首尾除去K， Replace(last_name,‘a’,‘i’)as 替换 from employees
Round( ‘A’ , 2 ) 将A字符串四舍五入并保留两位小数；Trunc(‘A’ , n) 将A字符串中小数点后面的第n位之后开始截断，若n<0则去掉小数，倒数第n位截断补0，Trunc截断都不进行四舍五入，；Mod( ‘A’ , ’B’ ) A字符串整除B字符串，取余；例如下面用伪表——dual演示：
SQL 语句：select Round(5.5555,2)as 四舍五入,
Trunc(555.5555,2)as 正数截断,
Trunc(555.5555,-2)as 负数截断,
Mod(2400,700)as 求余 from dual
Months_Between(‘A’, ‘B’) A日期与B日期相差的月数(前面日期减去后面日期：A-B)；Add_Months(‘A’ , n) 在A日期中加上n个月；Next_day(‘A’ , ‘星期几’ ) A日期的下一个星期几是什么日期；last_day( ‘A’ ) A日期所在的月份的最后一天的日期；Round( ‘A’ , ‘month’ 或 ‘year’)将A日期的月份或年份四舍五入；Trunc( ‘A’ , ‘month’ 或 ‘year’) 将A日期以本月份截断或以本年份截断；
SQL 语句：
select hire_date,Months_Between(sysdate,hire_date)as 日期相差的月数,
Add_Months(hire_date,6)as 日期加上6个月,
Next_day(hire_date,‘星期一’)as 日期的下一个星期一,
last_day(hire_date)as 日期当月最后一天,
Round(hire_date,‘month’)as 月份四舍五入,
Round(hire_date,‘year’)as 年份四舍五入,
Trunc(hire_date,‘month’)as 以本月份截断,
Trunc(hire_date,‘year’)as 以本年份截断 from employees
to_char()、to_date()、to_numbe()：
SQL语句：
select hire_date,to_char(hire_date,‘dd-mon-yyyy’)as 日期格式转成字符串
,to_char(salary,‘L99,999.00’)as 转换为本地货币格式L
,to_char(hire_date,‘fmYYYY"年"MM"月"DD"日" DY’)as 加入字符和星期几DY
,to_date(‘2019-4-4’,‘yyyy-mm-dd’)as 字符串转成日期格式
,to_number(‘￥658,369’,‘L999,999.99’)as 对字符的转换 from employees
NVL( ‘A’ ，’b’ ) 若输出A的内容为空，则输出为b (b必须为已知值)；NVL2(‘A’ ，’b’ ，‘c’)若输出A的内容不为空，输出为b，若A为空，输出为c (b和c必须为已知值)；Nullif( ‘A’ ，‘B’ ) 若A与B 的值相等，输出为null，若不等，输出为A的值；Coalesce( ‘A’ ，‘B’ ，……)当前值为空，就返回下个值，下个值为空，返回下下个值，以此类推，Coalesce可以同时处理交替的多个值；
SQL 语句：
select NVL(commission_pct,‘0’)as 本身为空输出0，
NVL2(commission_pct,‘true’,‘false’)as 不为空true为空false
,Nullif(length(first_name), length(last_name))as 两者相等返回空不等返回前者
,Coalesce(commission_pct, salary, 10)as 当前值为空输出下个值 from employees
Case表达式
例：查询工种为 AD_VP, IT_PROG, ST_MAN 的员工信息, 若工种为 AD_VP, 则打印其工资的 1.1 倍, 工种为IT_PROG, 则打印其工资的 1.2 倍, 工种为 ST_MAN，则打印其工资的 1.3 倍数：
SQL语句：（case when then else end）
select job_id,salary,case when job_id = ‘AD_VP’ then 1.1salary
when job_id = ‘IT_PROG’ then 1.2salary
when job_id = ‘ST_MAN’ then 1.3salary
else salary end as endsalary
from employees
where job_id in (‘AD_VP’,‘IT_PROG’,‘ST_MAN’)*

相关推荐