《oracle教学尚硅谷宋红康单行函数学习教案》由会员分享，可在线阅读，更多相关《oracle教学尚硅谷宋红康单行函数学习教案（51页珍藏版）》请在金锄头文库上搜索。
1、会计学1oracle教学尚硅谷宋红康单行教学尚硅谷宋红康单行(dn xn)函函数数第一页，共51页。目目 标标通过本章学习，您将可以:SQL中不同类型的函数在SELECT语句中使用(shyng)字符，数字，日期和转换函数使用(shyng)条件表达式第1页/共50页第二页，共51页。SQL 函数函数(hnsh) 函函函函 数数数数输入输入参数参数参数参数1 1参数参数参数参数2 2参数参数参数参数n n 函数函数(hnsh)执行执行输出输出 结结结结 果果果果y = f(x1,x2)第2页/共50页第三页，共51页。两种两种SQLSQL函数函数(hnsh)(hnsh) 函函函函 数数数数 单行单
2、行单行单行(dn xn)(dn xn)函数函数函数函数 多行函数多行函数多行函数多行函数(hnsh)(hnsh)第3页/共50页第四页，共51页。单行单行(dn xn)(dn xn)函数函数单行函数单行函数: :操作数据对象操作数据对象接受参数返回一个结果接受参数返回一个结果只对一行进行变换只对一行进行变换每行返回一个结果每行返回一个结果可以可以(ky)(ky)转换数据类型转换数据类型可以可以(ky)(ky)嵌套嵌套参数可以参数可以(ky)(ky)是一列或一个值是一列或一个值function_name (arg1, arg2,.)第4页/共50页第五页，共51页。单行单行(dn xn)(dn
3、xn)函数函数 转转转转 换换换换 字字字字 符符符符 数数数数 值值值值 日日日日 期期期期 通通通通 用用用用 单行单行单行单行(dn xn)(dn xn)函数函数函数函数第5页/共50页第六页，共51页。字符字符(z f)(z f)函数函数 字符字符字符字符(z f)(z f)函数函数函数函数LOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPAD | RPADTRIMREPLACE 大小写控制大小写控制大小写控制大小写控制(kngzh)(kngzh)函数函数函数函数 字符控制函数字符控制函数字符控制函数字符控制函数第6页/共50页第七页，共51页。函数函
4、数(hnsh)结果结果(ji gu)大小写控制大小写控制(kngzh)(kngzh)函数函数这类函数改变字符的大小写。这类函数改变字符的大小写。LOWER(SQL Course)UPPER(SQL Course)INITCAP(SQL Course)sql courseSQL COURSESql Course第7页/共50页第八页，共51页。大小写控制大小写控制(kngzh)(kngzh)函数函数显示员工显示员工(yungng)Higgins(yungng)Higgins的信息的信息: :SELECT employee_id, last_name, department_idFROM empl
5、oyeesWHERE last_name = higgins;no rows selectedno rows selectedSELECT employee_id, last_name, department_idFROM employeesWHERE LOWER(last_name) = higgins;第8页/共50页第九页，共51页。CONCAT(Hello, World)SUBSTR(HelloWorld,1,5)LENGTH(HelloWorld)INSTR(HelloWorld, W)LPAD(salary,10,*)RPAD(salary, 10, *)TRIM(H FROM H
6、elloWorld)REPLACE(abcd,b,m)HelloWorldHello106*2400024000*elloWorldamcd函数函数(hnsh)结果结果(ji gu)字符字符(z f)(z f)控制函数控制函数这类函数控制字符这类函数控制字符: :第9页/共50页第十页，共51页。SELECT employee_id, CONCAT(first_name, last_name) NAME, job_id, LENGTH (last_name), INSTR(last_name, a) Contains a?FROM employeesWHERE SUBSTR(job_id, 4
7、) = REP;字符控制字符控制(kngzh)(kngzh)函函数数123123第10页/共50页第十一页，共51页。数字数字(shz)(shz)函数函数n nROUND: 四舍五入四舍五入(s sh w r)n nROUND(45.926, 2)45.93n nTRUNC: 截断截断n nTRUNC(45.926, 2)45.92n nMOD: 求余求余n nMOD(1600, 300) 100第11页/共50页第十二页，共51页。SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1)FROM DUAL;ROUND ROUND 函数
8、函数(hnsh)(hnsh)DUAL DUAL 是一个是一个伪表伪表，可以，可以(ky)(ky)用来测试函数和表达式用来测试函数和表达式123312第12页/共50页第十三页，共51页。SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2)FROM DUAL;TRUNC TRUNC 函数函数(hnsh)(hnsh)312123第13页/共50页第十四页，共51页。SELECT last_name, salary, MOD(salary, 5000)FROM employeesWHERE job_id = SA_REP;MOD MOD 函数
9、函数(hnsh)(hnsh)第14页/共50页第十五页，共51页。日日 期期n nOracle 中的日期型数据实际含有两个(lin )值: 日期和时间。SELECT last_name, hire_dateFROM employeesWHERE last_name like G%;第15页/共50页第十六页，共51页。日日 期期函数函数SYSDATE SYSDATE 返回返回(fnhu):(fnhu):日期日期时间时间第16页/共50页第十七页，共51页。日期的数学日期的数学(shxu)(shxu)运运算算n n在日期上加上或减去一个数字结果仍为日期。在日期上加上或减去一个数字结果仍为日期。n
10、 n两个日期相减返回日期之间相差的天数两个日期相减返回日期之间相差的天数(tinsh)(tinsh)。n n日期不允许做加法运算，无意义日期不允许做加法运算，无意义n n可以用数字除可以用数字除2424来向日期中加上或减去天数来向日期中加上或减去天数(tinsh)(tinsh)。第17页/共50页第十八页，共51页。日期日期(rq)(rq)的数学运算的数学运算SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKSFROM employeesWHERE department_id = 90;第18页/共50页第十九页，共51页。日期日期(rq)(rq)函
11、数函数两个日期两个日期(rq)相差的月数相差的月数MONTHS_BETWEENADD_MONTHSNEXT_DAYLAST_DAYROUNDTRUNC 向指定日期向指定日期(rq)中加上若干月数中加上若干月数指定日期的下一个星期指定日期的下一个星期 * 对应的日期对应的日期本月的最后一天本月的最后一天日期四舍五入日期四舍五入 日期截断日期截断函数函数描述描述第19页/共50页第二十页，共51页。MONTHS_BETWEEN (01-SEP-95,11-JAN-94)日期日期(rq)(rq)函数函数ADD_MONTHS (11-JAN-94,6)NEXT_DAY (01-SEP-95,FRIDA
12、Y) LAST_DAY(01-FEB-95) 19.677419411-JUL-9408-SEP-9528-FEB-95第20页/共50页第二十一页，共51页。ROUND(SYSDATE,MONTH) 01-AUG-95ROUND(SYSDATE ,YEAR) 01-JAN-96TRUNC(SYSDATE ,MONTH) 01-JUL-95 TRUNC(SYSDATE ,YEAR) 01-JAN-95日期日期(rq)(rq)函数函数Assume SYSDATE = 25-JUL-95:yyyy 年年mm 月月dd 日日day 星期星期(xngq) hh 小时小时 mi 分钟分钟 ss 秒秒第2
13、1页/共50页第二十二页，共51页。转换转换(zhunhun)(zhunhun)函数函数 隐隐隐隐 性性性性 显显显显 性性性性 数据数据数据数据(shj)(shj)类型转换类型转换类型转换类型转换第22页/共50页第二十三页，共51页。隐式数据隐式数据(shj)(shj)类型类型转换转换Oracle Oracle 自动完成自动完成(wn chng)(wn chng)下列转换：下列转换：VARCHAR2VARCHAR2 or CHAR or CHAR源数据类型源数据类型目标目标(mbio)(mbio)数据类型数据类型VARCHAR2 or CHARVARCHAR2 or CHARNUMBERN
14、UMBERDATEDATENUMBERNUMBERDATEDATEVARCHAR2VARCHAR2VARCHAR2VARCHAR2varchar2numberdate第23页/共50页第二十四页，共51页。显式数据显式数据(shj)(shj)类型类型转换转换NUMBERCHARACTERTO_CHARTO_NUMBERDATETO_CHARTO_DATE第24页/共50页第二十五页，共51页。TO_CHAR函数函数(hnsh)对日期的转对日期的转换换格式格式: :必须包含在单引号中而且必须包含在单引号中而且(r qi)(r qi)大小写敏感。大小写敏感。可以包含任意的有效的日期格式。可以包含任
15、意的有效的日期格式。日期之间用逗号隔开。日期之间用逗号隔开。TO_CHAR(date, format_model)SELECT TO_CHAR(sysdate,yyyy-mm-dd hh:mi:ss) FROM dual;第25页/共50页第二十六页，共51页。YYYY日期格式日期格式(g shi)(g shi)的元的元素素YEARMMMONTHDYDAY2004TWO THOUSAND AND FOUR02MONMONDAYJULYMONJULDD02第26页/共50页第二十七页，共51页。日期格式日期格式(g shi)(g shi)的元的元素素n n时间格式时间格式n n使用使用(shyn
16、g)(shyng)双引号向日期中添加字符双引号向日期中添加字符HH24:MI:SS AMHH24:MI:SS AM15:45:32 15:45:32 PMPMDD of MONTHDD of MONTH12 12 of OCTOBERof OCTOBER第27页/共50页第二十八页，共51页。TO_CHAR 函数对日期函数对日期(rq)的转换的转换SELECT last_name, TO_CHAR(hire_date, DD Month YYYY) AS HIREDATEFROM employees;select employee_id,last_name,hire_datefrom empl
17、oyeeswhere to_char(hire_date,yyyy-mm-dd) = 1987-09-17第28页/共50页第二十九页，共51页。TO_DATE 函数对字函数对字符符(z f)的转换的转换 n n使用使用TO_DATETO_DATE函数将字符函数将字符(zf)(zf)转换成数字转换成数字: :TO_DATE(char, format_model)使用(shyng) TO_DATE :TO_DATE(2012年年10月月29日日 08:10:21,yyyy “年年”mm”月月”dd“日日”hh:mi:ss)From dual练习：返回hire_date 为 */*/*的员工信息，
18、使用显示日期表达第29页/共50页第三十页，共51页。TO_CHAR函数对数字函数对数字(shz)的转换的转换下面是在下面是在TO_CHAR TO_CHAR 函数中经常使用的几种函数中经常使用的几种(j zhn)(j zhn)格式格式: :TO_CHAR(number, format_model)9 90 0$ $L L. ., ,数字数字(shz)(shz)零零美元符美元符本地货币符号本地货币符号小数点小数点千位符千位符第30页/共50页第三十一页，共51页。SELECT TO_CHAR(salary, $99,999.00) SALARYFROM employeesWHERE last_n
19、ame = Ernst;TO_CHAR函数对数字函数对数字(shz)的转换的转换第31页/共50页第三十二页，共51页。TO_NUMBER 函数函数(hnsh)对字符的对字符的转换转换 n n使用使用 TO_NUMBER TO_NUMBER 函数函数(hnsh)(hnsh)将字符转换将字符转换成日期成日期: :TO_NUMBER( char, format_model)使用(shyng) TO_NUMBER :TO_NUMBER( ￥1,234,567,890.00,L999,999,999,999.99)from dual第32页/共50页第三十三页，共51页。通用通用(tngyng)(tn
20、gyng)函数函数这些函数适用于任何(rnh)数据类型，同时也适用于空值：NVL(expr1,expr2)NVL2(expr1,expr2,expr3)NULLIF(expr1,expr2)COALESCE(expr1,expr2,.,exprn)第33页/共50页第三十四页，共51页。NVL 函数函数(hnsh)将空值转换成一个已知的值：将空值转换成一个已知的值：将空值转换成一个已知的值：将空值转换成一个已知的值：可以可以可以可以(ky)(ky)使用的数据类型有日期、字符、数使用的数据类型有日期、字符、数使用的数据类型有日期、字符、数使用的数据类型有日期、字符、数字。字。字。字。函数的一般形
21、式函数的一般形式函数的一般形式函数的一般形式: :NVL(commission_pct,0)NVL(commission_pct,0)NVL(hire_date,01-JAN-97)NVL(hire_date,01-JAN-97)NVL(job_id,No Job Yet)NVL(job_id,No Job Yet)练习1：求公司员工的年薪（含commission_pct)练习2：输出last_name,department_id，当department_id为null时，显示没有(mi yu)部门。第34页/共50页第三十五页，共51页。SELECT last_name, salary, N
22、VL(commission_pct, 0), (salary*12) + (salary*12*NVL(commission_pct, 0) AN_SALFROM employees;使用使用(shyng)NVL(shyng)NVL函数函数1212第35页/共50页第三十六页，共51页。SELECT last_name, salary, commission_pct, NVL2(commission_pct, SAL+COMM, SAL) incomeFROM employees WHERE department_id IN (50, 80);使用使用(shyng)NVL2(shyng)NVL
23、2函数函数1212NVL2 (expr1, expr2, expr3) : expr1不为不为(b wi)NULL，返回，返回expr2；为；为NULL，返回，返回expr3。exp1 != null ? exp2 : exp3练习(linx)：查询员工的奖金率，若为空，返回 0.01,若不为空，返回实际奖金率 +0.015第36页/共50页第三十七页，共51页。SELECT first_name, LENGTH(first_name) expr1, last_name, LENGTH(last_name) expr2, NULLIF(LENGTH(first_name), LENGTH(la
24、st_name) resultFROM employees;使用使用(shyng)NULLIF(shyng)NULLIF函数函数123123NULLIF (expr1, expr2) : 相等(xingdng) 返回NULL，不等返回expr1 第37页/共50页第三十八页，共51页。使用使用(shyng)(shyng)COALESCECOALESCE函数函数n nCOALESCE COALESCE 与与 NVL NVL 相比的优点在于相比的优点在于 COALESCE COALESCE 可以可以同时处理交替的多个同时处理交替的多个(du )(du )值。值。n n如果第一个表达式为空如果第一个
25、表达式为空, ,则返回下一个表达式，对其则返回下一个表达式，对其他的参数进行他的参数进行COALESCE COALESCE 。第38页/共50页第三十九页，共51页。SELECT last_name, COALESCE(commission_pct, salary, 10) commFROM employeesORDER BY commission_pct;使用使用(shyng)(shyng)COALESCECOALESCE函数函数第39页/共50页第四十页，共51页。条件条件(tiojin)(tiojin)表达式表达式n n在在SQLSQL语句中使用语句中使用(shyng)IF-THEN-E
26、LSE(shyng)IF-THEN-ELSE逻辑逻辑n n使用使用(shyng)(shyng)两种方法两种方法: :n nCASECASE表达式表达式n nDECODEDECODE函数函数第40页/共50页第四十一页，共51页。CASE 表达式表达式在需要使用在需要使用(shyng)IF-THEN-ELSE(shyng)IF-THEN-ELSE逻辑时逻辑时: :CASE expr WHEN comparison_expr1 THEN return_expr1 WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN r
27、eturn_exprn ELSE else_exprEND练习：查询部门号为 10, 20, 30 的员工信息(xnx), 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数第41页/共50页第四十二页，共51页。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
28、 REVISED_SALARYFROM employees;CASE 表达式表达式下面是使用下面是使用casecase表达式的一个表达式的一个(y )(y )例子：例子：第42页/共50页第四十三页，共51页。DECODE 函数函数(hnsh)在需要在需要(xyo)(xyo)使用使用IF-THEN-ELSEIF-THEN-ELSE逻辑时逻辑时: :DECODE(col|expression, search1, result1 , , search2, result2,., , default)第43页/共50页第四十四页，共51页。DECODE 函数函数(hnsh)SELECT last_na
29、me, job_id, salary, DECODE(job_id, IT_PROG, 1.10*salary, ST_CLERK, 1.15*salary, SA_REP, 1.20*salary, salary) REVISED_SALARYFROM employees;第44页/共50页第四十五页，共51页。DECODE 函数函数(hnsh)SELECT last_name, salary, DECODE (TRUNC(salary/2000, 0), 0, 0.00, 1, 0.09, 2, 0.20, 3, 0.30, 4, 0.40, 5, 0.42, 6, 0.44, 0.45)
30、 TAX_RATEFROM employeesWHERE department_id = 80;使用使用decodedecode函数函数(hnsh)(hnsh)的一个例子：的一个例子：第45页/共50页第四十六页，共51页。嵌套函数嵌套函数(hnsh)(hnsh)n n单行函数可以单行函数可以(ky)(ky)嵌套。嵌套。n n嵌套函数的执行顺序是由内到外。嵌套函数的执行顺序是由内到外。F3F3(F2(F2(F1(col,arg1)(F1(col,arg1),arg2),arg2), ,arg3)arg3)步骤步骤(bzhu)1 = (bzhu)1 = 结果结果1 1步骤步骤2 =2 =结果结果
31、2 2步骤步骤3 =3 =结果结果3 3第46页/共50页第四十七页，共51页。SELECT last_name, NVL(TO_CHAR(manager_id), No Manager)FROM employeesWHERE manager_id IS NULL;嵌套函数嵌套函数(hnsh)(hnsh)第47页/共50页第四十八页，共51页。总总 结结通过本章学习，您应该学会通过本章学习，您应该学会: : 使用函数对数据进行计算使用函数对数据进行计算使用函数修改数据使用函数修改数据使用函数控制一组数据的输出格式使用函数控制一组数据的输出格式使用函数改变使用函数改变(gibin)(gibin)
32、日期的显示格式日期的显示格式使用函数改变使用函数改变(gibin)(gibin)数据类型数据类型使用使用 NVL NVL 函数函数使用使用IF-THEN-ELSE IF-THEN-ELSE 逻辑逻辑第48页/共50页第四十九页，共51页。第49页/共50页第五十页，共51页。内容(nirng)总结会计学。INITCAP(SQL Course)。ROUND(45.926, 2)45.93。TRUNC(45.926, 2)45.92。15:45:32 PM。使用(shyng) TO_DATE 函数将字符转换成数字:。TO_DATE(2012年10月29日 08:10:21,yyyy“年”mm”月”dd“日”hh:mi:ss)。使用(shyng) TO_NUMBER 函数将字符转换成日期:。使用(shyng)函数改变数据类型第五十一页，共51页。
