我的学习之路——Oracle数据库之单行函数

1单行函数的一些特点

单行函数还有以下的一些特征:
–单行函数对单行操作
–每行返回一个结果
–有可能返回值与原参数数据类型不一致(转换函数)
–单行函数可以写在SELECT、WHERE、ORDER BY子句中
–有些函数没有参数,有些函数包括一个或多个参数
–函数可以嵌套

2单行函数的分类

字符函数

数字函数

日期函数

转换函数

通用函数

3 字符函数

大小写操作函数

LOWER(列名|表达式)其它同理

LOWER,UPPER,INITCAP(将首字母大写,其它字母小写)

字符处理函数

CONCAT:连接两个值,等同于||

CONCAT('Good','String‘)  结果 GoodString

SUBSTR:返回第一个参数中从n1字符开始长度为n2的子串,如果n1是负值,表从后向前数的abs(n1)位,如果n2省略,取n1之后的所有字符 格式:SUBSTR(column | expression,n1[,n2])

SUBSTR('String',1,3) 结果Str

LENGTH :取字符串的长度 LENGTH('String') 结果 6

INSTR:返回s1中,子串s2从n1开始,第n2次出现的位置。n1,n2默认值为1 格式:INSTR(s1,s2,[,n1],[n2])

INSTR('String','r') 结果 3

LPAD:返回s1被s2从左面填充到n1长度。 格式:LPAD(s1,n1,s2)

LPAD(sal,10,'*') 结果 ******5000
RPAD:返回s1被s2从右面填充到n1长度。 格式:RPAD(s1,n1,s2)

RPAD(sal,10,'*') 结果5000******
TRIM:去除字符串头部或尾部(头尾)的字符 格式:TRIM(leading | trailing | both trim_character From trim_source)

TRIM('S' FROM 'SSMITH') 结果MITH
REPLACE:把s1中的s2用s3替换。 格式:REPLACE(s1,s2,s3)

REPLACE('abc','b','d') 结果adc

查找公司员工编号,用户名(first_name与last_name连接成一个字符串),职位编号及last_name的长度,要求职位从第四位起匹配'ACCOUNT',同时last_name中至少包含一个’e’字母。
SELECT employee_id, CONCAT(first_name, last_name)
NAME, job_id, LENGTH (last_name) length
FROM employees
WHERE SUBSTR(job_id, 4) = 'ACCOUNT'
AND INSTR(last_name, 'e')>0;

4数字函数

ROUND(列名|表达式, n):将列或表达式所表示的数值四舍五入到小数点后的第n位。
TRUNC(列名|表达式,n):将列或表达式所表示的数值截取到小数点后的第n位。
MOD(m,n):取m除以n后得到的余数。

5日期函数

常用的日期运算如下:
–日期类型列或表达式可以加减数字,功能是在该日期上加减对应的天数。如:’10-AUG-06’+15结果是’25-AUG-06’。
–日期类型列或表达式之间可以进行减操作,功能是计算两个日期之间间隔了多少天。如:’10-AUG-06’-‘4-AUG-06’结果四舍五入后是6天。
–如果需要加减相应小时或分钟,可以使用n/24来实现

SYSDATE:返回系统日期
•MONTHS_BETWEEN:返回两个日期间隔的月数
•ADD_MONTHS:在指定日期基础上加上相应的月数
•NEXT_DAY:返回某一日期的下一个指定日期
•LAST_DAY:返回指定日期当月最后一天的日期
•ROUND(date[,'fmt'])将date按照fmt指定的格式进行四舍五入,fmt为可选项,如果没有指定fmt,则默认为DD’,将date四舍五入为最近的天。
格式码:世纪CC,年YY,月MM,日DD,小时HH24,分MI,秒SS
•TRUNC(date[,'fmt'])将date按照fmt指定的格式进行截取,fmt为可选项,如果没有指定fmt,则默认为‘DD’,将date截取为最近的天。
•EXTRACT:返回从日期类型中取出指定年、月、日

MONTHS_BETWEEN 函数演示——公司员工服务的月数。

SELECT last_name, salary, MONTHS_BETWEEN(SYSDATE,hire_date) months
FROM employees
ORDER BY months;
ADD_MONTHS 函数演示——99年公司员工转正日期。

SELECT last_name, salary, hire_date, ADD_MONTHS(hire_date,3) new_date

FROM employees

WHERE hire_date>'01-1月-1999';

NEXT_DAY 函数演示——下周一的日期。

SELECT NEXT_DAY('02-2月-06','星期一') NEXT_DAY FROM DUAL;


LAST_DAY 函数演示——06年2月2日所在月份最后一天。
SELECT LAST_DAY('02-2月-2006') "LAST DAY" FROM DUAL;

ROUND函数演示——98年入职员工入职日期按月四舍五入。
SELECT employee_id, hire_date, ROUND(hire_date, 'MONTH') FROM employees WHERE SUBSTR(hire_date,-2,2)='98';

TRUNC 函数演示——98年入职员工入职日期按月截断。
SELECT employee_id, hire_date, TRUNC(hire_date, 'MONTH') FROM employees WHERE SUBSTR(hire_date,-2,2)='98';

EXTRACT 函数语法
部门编号是90的部门中所有员工入职月份。
EXTRACT ([YEAR] [MONTH][DAY] FROM [日期类型表达式])
SELECT last_name, hire_date, EXTRACT (MONTH FROM HIRE_DATE) MONTH FROM employees WHERE department_id = 90;

6转换函数

数据类型转换分为隐式转化和显示转换

对数据进行隐式转换

对于INSERT和UPDATE操作,oracle会把插入值或者更新值隐式转换为字段的数据类型
–对于SELECT语句,oracle会把字段的数据类型隐式转换为变量的数据类型
–当比较一个字符型和数值型的值时,oracle会把字符型的值隐式转换为数值型
–当比较字符型和日期型的数据时,oracle会把字符型转换为日期型
–用连接操作符(||)时,oracle会把非字符类型的数据转换为字符类型
–如果字符类型的数据和非字符类型的数据(如number、date、rowid等)作算术运算,则oracle会将字符类型的数据转换为合适的数据类型,这些数据类型可能是number、date、rowid等

数据类型的显示转换,通常是在字符类型,日期类型,数字类型直接进行显示转换

主要有3个显性函数

-TO_CHAR

-TO_NUMBER

-TO_DATE

TO_CHAR(date|number [,‘fmt’])把日期类型/数字类型的表达式或列转换为字符类型。
–‘fmt’指的是需要显示的格式:
–需要写在单引号中,并且是大小写敏感
–可包含任何有效的日期格式

常用日期格式
–YYYY:4位数字表示年份;
–YY:2位数字表示年份,但是无世纪转换(与RR区别在后面章节介绍);
–RR:2位数字表示年份,有世纪转换(与YY区别在后面章节介绍);
–YEAR:年份的英文拼写;
–MM:两位数字表示月份;
–MONTH:月份英文拼写;

–DY:星期的英文前三位字母;
–DAY:星期的英文拼写;
–D:数字表示一星期的第几天,星期天是一周的第一天。
–DD:数字表示一个月中的第几天;
–DDD:数字表示一年中的第几天。

–AM 或PM:上下午表示;
–HH 或HH12或HH24:数字表示小时。HH12代表12小时计时,HH24代表24小时计时;
–MI:数字表示分钟;
–SS:数字表示秒;

一些特殊格式
–TH:显示数字表示的英文序数词,如:DDTH显示天数的序数词。
–SP:显示数字表示的拼写。
–SPTH:显示数字表示的序数词的拼写。
–“字符串”:如在格式中显示字符串,需要两端加双引号。
TO_CHAR(SYSDATE,’DDSPTH’)
TO_CHAR(SYSDATE,’DD “of” MONTH ‘)

select to_char(sysdate,'YYYY-MM-DD')
from dual;

TO_NUMBER(char[,’fmt’]) 把字符类型列或表达式转换为数字类型。
–使用格式和TO_CHAR中转换成字符类型中的格式相同


TO_DATE(char[,‘fmt’]) 把字符类型列或表达式转换为日期类型。
–格式和TO_CHAR中转换成字符类型中的格式相同。

具体格式如下:
–9:一位数字;
–0:一位数字或前导零;
–$:显示为美元符号;
–L:显示按照区域设置的本地货币符号;
–.:小数点;
–,:千位分割符;

TO_CHAR 函数进行数字到字符型格式转换
注意:进行数字类型到字符型转换,格式中的宽度一定要超过实际列宽度,否则会显示为###。
SELECT last_name, TO_CHAR(salary, '$99,999.00') salary FROM employees WHERE last_name = 'King';
SELECT last_name, TO_CHAR(salary, '$9,999.00') salary FROM employees WHERE last_name = 'King';

7RR功能


8通用函数

与空值(NULL)相关的一些函数,完成对空值(NULL)的一些操作。主要包括以下函数:
–NVL
–NVL2
–NULLIF
–COALESCE
条件处理函数:
–CASE表达式
–DECODE

NVL (表达式1, 表达式2)函数功能是空值转换,把空值转换为其他值,解决空值问题。表达式1是需要转换的列或表达式,表达式2是如果第一个参数为空时,需要转换的值。
–NVL(comm,0)
–NVL(hire_date,'01-JAN-06')
–NVL(job_id,'No Job Yet')
注意:数据格式可以是日期、字符、数字,但数据类型必须匹配。

NVL2(表达式1, 表达式2, 表达式3)函数是对第一个参数进行检查。如果第一个参数不为空,则输出第二个参数;如果第一个参数为空,则输出第三个参数。表达式1可以为任何数据类型

NULLIF (表达式1, 表达式2)函数主要是完成两个参数的比较。当两个参数不相等时,返回值是第一个参数值;当两个参数相等时,返回值是空值。

COALESCE (表达式1, 表达式2, ... 表达式n)函数是对NVL函数的扩展。COALESCE函数的功能是返回第一个不为空的参数,参数个数不受限制。

CASE语法
CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END

SELECT last_name, commission_pct,

 (CASE commission_pct

    WHEN 0.1 THEN '低'

    WHEN 0.2 THEN '中'

    WHEN 0.3 THEN '高'

     ELSE '无'

 END) Commission

FROM employees

 WHERE commission_pct IS NOT NULL

 ORDER BY last_name;

DECODE(字段|表达式, 条件1,结果1[,条件2,结果2…,][,缺省值])
SELECT last_name, commission_pct, decode(commission_pct, 0.1,'低', 0.2,'中', 0.3 , '高', '无') Commission FROM employees WHERE commission_pct IS NOT NULL ORDER BY last_name;

9嵌套函数

单行函数可以嵌套于任何层。
嵌套的函数是从最里层向最外层的顺序计算的。

SELECT employee_id,manager_id , NVL2(TO_CHAR(manager_id),to_char(manager_id),'No Manager') FROM employees


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值