oracle教學(xué)尚硅谷宋紅康單行函數(shù)學(xué)習(xí)教案

1、會(huì)計(jì)學(xué)1oracle教學(xué)尚硅谷宋紅康單行教學(xué)尚硅谷宋紅康單行(dn xn)函函數(shù)數(shù)第一頁，共51頁。第1頁/共50頁第二頁，共51頁。輸入輸入 函數(shù)函數(shù)(hnsh)執(zhí)行執(zhí)行輸出輸出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頁。函數(shù)函數(shù)(hnsh)結(jié)果結(jié)果(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函數(shù)函數(shù)(hnsh)結(jié)果結(jié)果(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 是一個(gè)是一個(gè)偽表偽表，可以，可以(ky)(ky)用來測(cè)試函數(shù)和表達(dá)式用來測(cè)試函數(shù)和表達(dá)式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頁。兩個(gè)日期兩個(gè)日期(rq)相差的月數(shù)相差的月數(shù)MONTHS_BETWEENADD_MONTHSNEXT_DAYLAST_DAYROUNDTRUNC 向指定日期向指定日期(rq)中加上若干月數(shù)中加上若干月數(shù)指定日期的下一個(gè)星期指定日期的下一個(gè)星期 * 對(duì)應(yīng)的日期對(duì)應(yīng)的日期本月的最后一天本月的最后一天日期四舍五入日期四舍五入 日期截?cái)嗳掌诮財(cái)嗪瘮?shù)函數(shù)描述描述第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 小時(shí)小時(shí) mi 分鐘分鐘 ss 秒秒第21頁/共50頁第二十二頁，共51頁。第22頁/共50頁第二十三頁，共51頁。VARCHAR2VARCHAR2 or CHAR or CHAR源數(shù)據(jù)類型源數(shù)據(jù)類型目標(biāo)目標(biāo)(mbio)(mbio)數(shù)據(jù)類型數(shù)據(jù)類型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練習(xí)：返回hire_date 為 */*/*的員工信息，使用顯示日期表達(dá)第29頁/共50頁第三十頁，共51頁。TO_CHAR(number, format_model)9 90 0$ $L L. ., ,數(shù)字?jǐn)?shù)字(shz)(shz)零零美元符美元符本地貨幣符號(hào)本地貨幣符號(hào)小數(shù)點(diǎn)小數(shù)點(diǎn)千位符千位符第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頁。練習(xí)1：求公司員工的年薪（含commission_pct)練習(xí)2：輸出last_name,department_id，當(dāng)depart

13、ment_id為null時(shí)，顯示沒有(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練習(xí)(linx)：查詢員工的獎(jiǎng)金率，若為空，返回0.01,若不為空，返回實(shí)際獎(jiǎng)金率+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練習(xí)：查詢部門號(hào)為 10, 20, 30 的員工信息(xnx), 若部門號(hào)為 10, 則打印其工資的 1.1 倍, 20 號(hào)部門, 則打印其工資的 1.2 倍, 30 號(hào)部門打印其工資的 1.3 倍數(shù)第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 = 結(jié)果結(jié)果1 1步驟步驟2 =2 =結(jié)果結(jié)果2 2步驟步驟3 =3 =結(jié)果結(jié)果3 3第46頁/共50頁第四十七頁，共51頁。SELECT last_name, NVL(TO_CHAR(manager_id), No Manager)FROM employeesWHERE manager_id IS NULL;第47頁/共