oracle教学尚硅谷宋红康单行函数学习教案

1、会计学1oracle教学尚硅谷宋红康单行教学尚硅谷宋红康单行(dn xn)函函数数第一页，共51页。第1页/共50页第二页，共51页。输入输入 函数函数(hnsh)执行执行输出输出y = f(x1,x2)第2页/共50页第三页，共51页。第3页/共50页第四页，共51页。function_name (arg1, arg2,.)第4页/共50页第五页，共51页。第5页/共50页第六页，共51页。LOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPAD | RPADTRIMREPLACE第6页/共50页第七页，共51页。函数函数(hnsh)结果结果(ji gu)LO

2、WER(SQL Course)UPPER(SQL Course)INITCAP(SQL Course)sql courseSQL COURSESql Course第7页/共50页第八页，共51页。SELECT employee_id, last_name, department_idFROM employeesWHERE last_name = higgins;SELECT employee_id, last_name, department_idFROM employeesWHERE LOWER(last_name) = higgins;第8页/共50页第九页，共51页。CONCAT(Hel

3、lo, World)SUBSTR(HelloWorld,1,5)LENGTH(HelloWorld)INSTR(HelloWorld, W)LPAD(salary,10,*)RPAD(salary, 10, *)TRIM(H FROM HelloWorld)REPLACE(abcd,b,m)HelloWorldHello106*2400024000*elloWorldamcd函数函数(hnsh)结果结果(ji gu)第9页/共50页第十页，共51页。SELECT employee_id, CONCAT(first_name, last_name) NAME, job_id, LENGTH (l

4、ast_name), INSTR(last_name, a) Contains a?FROM employeesWHERE SUBSTR(job_id, 4) = REP;123123第10页/共50页第十一页，共51页。nMOD: 求余求余nMOD(1600, 300)100第11页/共50页第十二页，共51页。SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1)FROM DUAL;DUAL DUAL 是一个是一个伪表伪表，可以，可以(ky)(ky)用来测试函数和表达式用来测试函数和表达式123312第12页/共50页第十三页，共

5、51页。SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2)FROM DUAL;312123第13页/共50页第十四页，共51页。SELECT last_name, salary, MOD(salary, 5000)FROM employeesWHERE job_id = SA_REP;第14页/共50页第十五页，共51页。SELECT last_name, hire_dateFROM employeesWHERE last_name like G%第15页/共50页第十六页，共51页。第16页/共50页第十七页，共51页。第17页/共

6、50页第十八页，共51页。SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKSFROM employeesWHERE department_id = 90;第18页/共50页第十九页，共51页。两个日期两个日期(rq)相差的月数相差的月数MONTHS_BETWEENADD_MONTHSNEXT_DAYLAST_DAYROUNDTRUNC 向指定日期向指定日期(rq)中加上若干月数中加上若干月数指定日期的下一个星期指定日期的下一个星期 * 对应的日期对应的日期本月的最后一天本月的最后一天日期四舍五入日期四舍五入 日期截断日期截断函数函数描述描述第19页

7、/共50页第二十页，共51页。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.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-

8、95Assume SYSDATE = 25-JUL-95:yyyy 年年mm 月月dd 日日day 星期星期(xngq) hh 小时小时 mi 分钟分钟 ss 秒秒第21页/共50页第二十二页，共51页。第22页/共50页第二十三页，共51页。VARCHAR2VARCHAR2 or CHAR or CHAR源数据类型源数据类型目标目标(mbio)(mbio)数据类型数据类型VARCHAR2 or CHARVARCHAR2 or CHARNUMBERNUMBERDATEDATENUMBERNUMBERDATEDATEVARCHAR2VARCHAR2VARCHAR2VARCHAR2varchar2

9、numberdate第23页/共50页第二十四页，共51页。NUMBERCHARACTERTO_CHARTO_NUMBERDATETO_CHARTO_DATE第24页/共50页第二十五页，共51页。TO_CHAR(date, format_model)SELECT TO_CHAR(sysdate,yyyy-mm-dd hh:mi:ss) FROM dual;第25页/共50页第二十六页，共51页。YYYYYEARMMMONTHDYDAY2004TWO THOUSAND AND FOUR02MONMONDAYJULYMONJULDD02第26页/共50页第二十七页，共51页。HH24:MI:SS

10、 AMHH24:MI:SS AM15:45:32 15:45:32 PMPMDD of MONTHDD of MONTH12 12 of OCTOBERof OCTOBER第27页/共50页第二十八页，共51页。SELECT last_name, TO_CHAR(hire_date, DD Month YYYY) AS HIREDATEFROM employees;select employee_id,last_name,hire_datefrom employeeswhere to_char(hire_date,yyyy-mm-dd) = 1987-09-17第28页/共50页第二十九页，共

11、51页。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 为 */*/*的员工信息，使用显示日期表达第29页/共50页第三十页，共51页。TO_CHAR(number, format_model)9 90 0$ $L L. ., ,数字数字(shz)(shz)零零美元符美元符本地货币符号本地货币符号小数点小数点千位符千位符第30页/共50页第三十一页，共51页。SELECT TO_CH

12、AR(salary, $99,999.00) SALARYFROM employeesWHERE last_name = Ernst;第31页/共50页第三十二页，共51页。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页。第33页/共50页第三十四页，共51页。练习1：求公司员工的年薪（含commission_pct)练习2：输出last_name,department_id，当depart

13、ment_id为null时，显示没有(mi yu)部门。第34页/共50页第三十五页，共51页。SELECT last_name, salary, NVL(commission_pct, 0), (salary*12) + (salary*12*NVL(commission_pct, 0) AN_SALFROM employees;1212第35页/共50页第三十六页，共51页。SELECT last_name, salary, commission_pct, NVL2(commission_pct, SAL+COMM, SAL) incomeFROM employees WHERE depa

14、rtment_id IN (50, 80);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_n

15、ame), LENGTH(last_name) resultFROM employees;123123NULLIF (expr1, expr2) : 相等(xingdng)返回NULL，不等返回expr1 第37页/共50页第三十八页，共51页。第38页/共50页第三十九页，共51页。SELECT last_name, COALESCE(commission_pct, salary, 10) commFROM employeesORDER BY commission_pct;第39页/共50页第四十页，共51页。第40页/共50页第四十一页，共51页。CASE expr WHEN compar

16、ison_expr1 THEN return_expr1 WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_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_

17、PROG THEN 1.10*salary WHEN ST_CLERK THEN 1.15*salary WHEN SA_REP THEN 1.20*salary ELSE salary END REVISED_SALARYFROM employees;第42页/共50页第四十三页，共51页。DECODE(col|expression, search1, result1 , , search2, result2,., , default)第43页/共50页第四十四页，共51页。SELECT last_name, job_id, salary, DECODE(job_id, IT_PROG, 1

18、.10*salary, ST_CLERK, 1.15*salary, SA_REP, 1.20*salary, salary) REVISED_SALARYFROM employees;第44页/共50页第四十五页，共51页。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) TAX_RATEFROM employeesWHERE department_id = 80;第45页/共50页第四十六页，共51页。F3F3(F2(F2(F1(col,arg1)(F1(col,arg1),arg2),arg2), ,arg3)arg3)步骤步骤(bzhu)1 = (bzhu)1 = 结果结果1 1步骤步骤2 =2 =结果结果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;第47页/共