ORACLE第四章单行函数

单行函数 本章要点 字符函数 数字函数 日期函数 转换函数 通用函数 SQL函数 函数 函数 FunctionFunction 执行作用 SQL函数的两种类型 函数 单行函数 多行函数 单行函数 单行函数 操纵数据项 接受多个参数并返回一个值 作用于每一个返回行 每行返回一个结果 可以修改数据类型 可以嵌套 接受多个参数 参数可以是一个列或者一个表达式 function name arg1 arg2 单行函数 转换 字符 数字 日期 通用 单行函数 字符函数 字符函数 LOWERUPPERINITCAP CONCATSUBSTRLENGTHINSTRLPAD RPADTRIMREPLACE 大小写处理函数 字符处理函数 Function Result 大小写处理函数 这些函数转换字符串的大小写 LOWER SQLCourse UPPER SQLCourse INITCAP SQLCourse sqlcourseSQLCOURSESqlCourse 使用大小写处理函数 显示雇员Higgins的雇员号 姓名和部门号 SELECTemployee id last name department idFROMemployeesWHERElast name higgins norowsselected SELECTemployee id last name department idFROMemployeesWHERELOWER last name higgins CONCAT Hello World SUBSTR HelloWorld 1 5 LENGTH HelloWorld INSTR HelloWorld W LPAD salary 10 RPAD salary 10 TRIM H FROM HelloWorld HelloWorldHello106 2400024000 elloWorld Function Result 字符处理函数 字符串处理函数 SELECTemployee id CONCAT first name last name NAME job id LENGTH last name INSTR last name a Contains a FROMemployeesWHERESUBSTR job id 4 REP 使用字符处理函数 1 2 3 1 2 3 数字函数 ROUND 四舍五入指定小数的值ROUND 45 926 2 45 93TRUNC 截断指定小数的值TRUNC 45 926 2 45 92MOD 返回除法的余数MOD 1600 300 100 SELECTROUND 45 923 2 ROUND 45 923 0 ROUND 45 923 1 FROMDUAL 使用ROUND函数 DUAL是一个虚拟表 你可以用它来查看函数和计算的结果 1 2 3 3 1 2 SELECTTRUNC 45 923 2 TRUNC 45 923 TRUNC 45 923 2 FROMDUAL 使用TRUNC函数 3 1 2 1 2 3 SELECTlast name salary MOD salary 5000 FROMemployeesWHEREjob id SA REP 使用MOD函数 计算所有是销售代表的雇员的工资被5000除后的余数 日期函数 常用的日期运算如下 日期类型列或表达式可以加减数字 功能是在该日期上加减对应的天数 如 08 JAN 08 15结果是 23 JAN 08 日期类型列或表达式之间可以进行减操作 功能是计算两个日期之间间隔了多少天 如 23 JAN 08 08 JAN 08 结果15天 如果需要加减相应小时或分钟 可以使用n 24来实现 月的3字母缩写JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC 用日期做算术运算 SELECTlast name SYSDATE hire date 7ASWEEKSFROMemployeesWHEREdepartment id 90 常用日期函数 SYSDATE 返回系统日期 MONTHS BETWEEN 返回两个日期间隔的月数 ADD MONTHS 在指定日期基础上加上相应的月数 NEXT DAY 返回某一日期的下一个指定日期 LAST DAY 返回指定日期当月最后一天的日期 ROUND date fmt 对日期进行指定格式的四舍五入操作 按照YEAR MONTH DAY等进行四舍五入 TRUNC date fmt 对日期进行指定格式的截断操作 按照YEAR MONTH DAY等进行截断 EXTRACT 返回从日期类型中取出指定年 月 日 MONTHS BETWEEN 01 SEP 95 11 JAN 94 使用日期函数 ADD MONTHS 11 JAN 94 6 NEXT DAY 01 SEP 95 FRIDAY LAST DAY 01 FEB 95 19 6774194 11 JUL 94 08 SEP 95 28 FEB 95 ROUND SYSDATE MONTH 01 AUG 95 ROUND SYSDATE YEAR 01 JAN 96 TRUNC SYSDATE MONTH 01 JUL 95 TRUNC SYSDATE YEAR 01 JAN 95 使用日期函数 AssumeSYSDATE 25 JUL 95 日期函数 MONTHS BETWEEN函数演示 公司员工服务的月数SELECTlast name salary MONTHS BETWEEN SYSDATE hire date monthsFROMemployeesORDERBYmonths ADD MONTHS函数演示 99年公司员工转 SELECTlast name salary hire date ADD MONTHS hire date 3 new dateFROMemployeesWHEREhire date 01 1月 1999 NEXT DAY函数演示 下周一的日期 SELECTNEXT DAY 02 2月 06 星期一 NEXT DAYFROMDUAL LAST DAY函数演示 06年2月2日所在月 SELECTLAST DAY 02 2月 2006 LASTDAY FROMDUAL ROUND函数演示 98年入职员工入职日期按月四舍五入 SELECTemployee id hire date ROUND hire date MONTH FROMemployeesWHERESUBSTR hire date 2 2 98 TRUNC函数演示 98年入职员工入职日期按月截断 SELECTemployee id hire date TRUNC hire date MONTH FROMemployeesWHERESUBSTR hire date 2 2 98 EXTRACT函数 EXTRACT函数语法EXTRACT YEAR MONTH DAY FROM 日期类型表达式 部门编号是90的部门中所有员工入职月份 SELECTlast name hire date EXTRACT MONTHFROMHIRE DATE MONTHFROMemployeesWHEREdepartment id 90 数据类型显性转换 通常是在字符类型 日期类型 数字类型之间进行显性转换 主要有3个显性函数 TO CHAR TO NUMBER TO DATE TO CHAR函数 TO CHAR date number fmt 把日期类型 数字类型的表达式或列转换为变长类型字符类型 fmt 指的是需要显示的格式 需要写在单引号中 并且是大小写敏感 可包含任何有效的日期格式 fm表示去除生成结果的前导零或空格 常用日期格式 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 THIRTIETH 字符串 如在格式中显示字符串 需要两端加双引号 TO CHAR SYSDATE DD of MONTH 18of10月 日期到字符型转换 TO CHAR函数进行日期到字符型复杂格式转换演示 altersessionsetnls language simplifiedchinese SELECTemployee id last name TO CHAR hire date Day the Ddspth of YYYYHH24 MI SS hire dateFROMemployeesWHEREdepartment id 90 数字到字符型转换 具体格式如下 9 一位数字 0 一位数字或前导零 显示为美元符号 L 显示按照区域设置的本地货币符号 小数点 千位分割符 TO CHAR函数进行数字到字符型格式转换 SELECTlast name TO CHAR salary 99 999 00 salaryFROMemployeesWHERElast name King TO NUMBER和TO DATE函数 TO NUMBER char fmt 把字符类型列或表达式转换为数字类型 使用格式和TO CHAR中转换成字符类型中的格式相同 在转换时让Oracle知道字符串中每部分的功能 TO DATE char fmt 把字符类型列或表达式转换为日期类型 格式和TO CHAR中转换成字符类型中的格式相同 通用函数 这些函数可用于任意数据类型 并且适用于空值 NVL expr1 expr2 NVL2 expr1 expr2 expr3 NULLIF expr1 expr2 COALESCE expr1 expr2 exprn SELECTlast name salary NVL commission pct 0 salary 12 salary 12 NVL commission pct 0 AN SALFROMemployees NVL函数 1 2 1 2 SELECTlast name salary commission pct NVL2 commission pct SAL COMM SAL incomeFROMemployeesWHEREdepartment idIN 50 80 使用NVL2函数 1 2 1 2 SELECTfirst name LENGTH first name expr1 last name LENGTH last name expr2 NULLIF LENGTH first name LENGTH last name resultFROMemployees 使用NULLIF函数 1 2 3 1 2 3 SELECTlast name COALESCE commission pct salary 10 commFROMemployeesORDERBYcommission pct 使用COALESCE函数 条件表达式 在SQL语句中提供IF THEN ELSE逻辑的使用 两种用法 CASE表达式 DECODE函数 CASE表达式 使得IF THEN ELSE条件判断容易实现 CASEexprWHENcomparison expr1THENreturn expr1 WHENcomparison expr2THENreturn expr2WHENcomparison exprnTHENreturn exprnELSEelse expr END SELECTlast name job id salary CASEjob idWHEN IT PROG THEN1 10 salaryWHEN ST CLERK THEN1 15 salaryWHEN SA REP THEN1 20 salaryELSEsalaryEND REVISED SALARY FROMemployees 使用CASE表达式 使得IF THEN ELSE条件判断容易实现 DECODE函数 使得CASE或者IF THEN ELSE条件判断容易实现 DECODE col expression search1 result1 search2 result2 default 使用DECODE函数 SELECTlast 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