轉(zhuǎn)換函數(shù)與條件表達式

1、Using Conversion Functions and Conditional Expressions轉(zhuǎn)換函數(shù)與條件表Copyright 2010, UplObjectives課程目標(biāo)After completing this lesson, you should be able to do the following本課中你應(yīng)該掌握:Describe the various types of conversion functions that are available in SQL了解有用的轉(zhuǎn)換函數(shù)Use the TO_CHAR, TO_NUMBER, and TO_DATE con

2、version functions使用to_char,to_number,to_dateApply conditional expressions in a SELECT statement應(yīng)用條件表4 - 2Lesson Agenda課程進度Implicit and explicit data type conversion隱式與顯式數(shù)據(jù)轉(zhuǎn)換TO_CHAR, TO_DATE, TO_NUMBER functions Nesting functionsGeneral functions:NVL NVL2 NULLIFCOALESCEConditional expressions:CASEDEC

3、ODE4 - 3Conversion Functions轉(zhuǎn)換函數(shù)Data type Conversion數(shù)據(jù)類型轉(zhuǎn)換Implicit data typeExplicit data typeConversion隱式數(shù)據(jù)類型轉(zhuǎn)換Conversion顯示數(shù)據(jù)類型轉(zhuǎn)換4 - 4Implicit Data Type Conversion隱式轉(zhuǎn)換In expressions, the Oracle server can automatically convert the following:中oracle服務(wù)器能自動轉(zhuǎn)換的在表4 - 5FromToVARCHAR2 or CHARNUMBERVARCHAR

4、2 or CHARDATEImplicit Data Type ConversionFor expression evaluation, the Oracle server can automatically convert the following:求值Oracle服務(wù)器能自動轉(zhuǎn)換的對表4 - 6FromToNUMBERVARCHAR2 or CHARDATEVARCHAR2 or CHARExplicit Data Type Conversion顯示轉(zhuǎn)換TO_NUMBERTO_DATENUMBERCHARACTERDATETO_CHARTO_CHAR4 - 7Explicit Data

5、Type ConversionTO_NUMBERTO_DATENUMBERCHARACTERDATETO_CHARTO_CHAR4 - 8Explicit Data Type ConversionTO_NUMBERTO_DATENUMBERCHARACTERDATETO_CHARTO_CHAR4 - 9Lesson Agenda課程進度Implicit and explicit data type conversion TO_CHAR, TO_DATE, TO_NUMBER functions Nesting functionsGeneral functions:NVL NVL2 NULLIF

6、COALESCEConditional expressions:CASEDECODE4 - 10Using the TO_CHAR Function with Dates to_char處理日期The format m:Must be enclosed with single quotation marks必須用單引號引起來Is case-sensitive區(qū)分大小寫Can include any valid date format element可用有效日期格式元素Has an fm element to remove padded blanks or suppress leading ze

7、ros fm能去除填充的空格或前置的零Is separated from the date value by a comma用逗號與日期隔開4 - 11TO_CHAR(date, format_m)Elements of the Date Format M日期元素4 - 12ElementResultYYYYFull year in numbersYEARYear spelled out (in English)MMTwo-digit value for the monthMONTHFull name of the monthMONThree-letter abbreviation of th

8、e monthDYThree-letter abbreviation of the day of the weekDAYFull name of the day of the weekDDNumeric day of the month4 - 13Elements of the Date Format M日期元素Time elements format the time portion of the date時間元素:Add character strings by enclosing them with double quotation marks:加入的字符串引號引起來Number suf

9、fixes spell out numbers:序數(shù)4 - 14ddspthfourteenthDD of MONTH12 of OCTOBERHH24:MI:SS AM15:45:32 PM4 - 15Using the TO_CHAR Function with Dates to_char函數(shù)處理日期4 - 16SELECT last_name,FROMemployees;TO_CHAR(hire_date, fmDD Month YYYY)AS HIREDATEUsing the TO_CHAR Function with Numbers to_char函數(shù)處理數(shù)字These are s

10、ome of the format elements that you can use with the TO_CHAR function to display a number value as a character:轉(zhuǎn)換數(shù)字為字符的元素4 - 17ElementResult9Represents a number0Forces a zero to be displayed$Places a floating dollar signLUses the floating local currency symbol.Prints a decimal point,Prints a comma a

11、s a thousands indicatorTO_CHAR(number, format_m)4 - 18Using the TO_CHAR Function with Numbers數(shù)字到字符4 - 19SELECTFROMemployeesWHERE last_name = Ernst;TO_CHAR(salary, $99,999.00) SALARYUsing the TO_NUMBER and TO_DATE Functions to_number與to_dateConvert a character string to a number format using theTO_NU

12、MBER function:轉(zhuǎn)字符為數(shù)字用to_number函數(shù)Convert a character string to a date format using theTO_DATE function:轉(zhuǎn)字符為日期用to_date函數(shù)These functions have an fx modifier. This modifier specifies the exact match for the character argument anddate format mof a TO_DATE function.to_date函數(shù)里可用fx選項精確匹配之后的字符或日期是否忽略多個空格還是只能

13、有1個空格4 - 20TO_DATE(char, format_m)TO_NUMBER(char, format_m)4 - 21Using the TO_CHAR and TO_DATE Function with the RR Date FormatTo find employees hired before 1990, use the RR date format, which produces the same results whether the command is run in 1999 or now:4 - 22SELECT last_name, TO_CHAR(hire_d

14、ate, DD-Mon-YYYY) FROM employeesWHERE hire_date TO_DATE(01-Jan-90,DD-Mon-RR);Lesson AgendaImplicit and explicit data type conversionTO_CHAR, TO_DATE, TO_NUMBER functionsNesting functionsGeneral functions:嵌套函數(shù)NVL NVL2 NULLIFCOALESCEConditional expressions:CASEDECODE4 - 23Nesting Functions嵌套函數(shù)Single-r

15、ow functions can be nested to any level.單行函數(shù)可嵌套多層Nested functions are evaluated from the deepest level to the least deep level.嵌套函數(shù)的計算過程是從最內(nèi)層向最外層運算Step 1 = Result 1Step 2 = Result 2Step 3 = Result 34 - 24F3(F2(F1(col,arg1),arg2),arg3)Nesting Functions: Example 1例子4 - 25SELECT last_name,FROMemployees

16、WHERE department_id = 60;UPPER(CONCAT(SUBSTR (LAST_NAME, 1, 8), _US)Nesting Functions: Example 2例子4 - 26SELECTTO_CHAR(ROUND(salary/7), 2),99G999D99, NLS_NUMERIC_CHARACTERS = ,. )Formatted SalaryFROM employees;Lesson AgendaImplicit and explicit data type conversion TO_CHAR, TO_DATE, TO_NUMBER functio

17、ns Nesting functionsGeneral functions: 空值函數(shù)NVL NVL2 NULLIFCOALESCEConditional expressions:CASEDECODE4 - 27General Functions空值函數(shù)The following functions work with any data type and pertain to using nulls適合空值的函數(shù)如下:NVL (expr1,expr2)如果expr1為空則返回expr2否則返回expr1NVL2 (expr1,expr2,expr3)如果expr1不為空返回expr2，為空返回

18、expr3NULLIF (expr1,expr2)如果相同返回空否則返回expr1COALESCE (expr1,expr2,.,exprn)4 - 28NVL Function NVL函數(shù)Converts a null value to an actual value轉(zhuǎn)空值為具體值:Data types that can be used are date, character, and number.適用于數(shù)字，字符，日期Data types must match:數(shù)據(jù)類型必須匹配NVL(commission_pct,0)NVL(hire_date,01-JAN-97) NVL(job_id

19、,No Job Yet)4 - 29Using the NVL Function NVL例子12124 - 30SELECT last name, salary,FROM employees;NVL(commission pct, 0),(salary*12) + (salary*12*NVL(commission_pct, 0) AN_SALUsing the NVL2 Function NLV2例子214 - 31SELECT12FROMemployees WHERE department_id IN (50, 80);last name, salary,commission pct,NV

20、L2(commission_pct, SAL+COMM, SAL) incomeUsing the NULLIF Function NULLIF例子131234 - 32SELECT first_name,last_name,LENGTH(first_name) expr1,2LENGTH(last_name) expr2,NULLIF(LENGTH(first_name), LENGTH(last_name) resultFROMemployees;Using the COALESCE Function COALESCE函數(shù)The advantage of the COALESCE func

21、tion over the NVL function is that the COALESCE function can take multiple alternate values.使用COALESCE函數(shù)而不使用NVL函數(shù)的優(yōu)勢在于，COALESCE函數(shù)可以使用多個替換值If the first expression is not null, the COALESCE function returns that expression; otherwise, it does a COALESCE of the remaining expressions.如果第一個表 就返回第二個表為非空，就

22、返回該表；如果是空值，；如果前兩個都是空值，就返 回第三；依此類推，直至第n個表個表4 - 33Using the COALESCE Function COALESCE例子4 - 34SELECT last_name, employee_id,FROM employees;COALESCE(TO_CHAR(commission_pct),TO_CHAR(manager_id), No commission and no manager)4 - 35Lesson AgendaImplicit and explicit data type conversion TO_CHAR, TO_DATE,

23、TO_NUMBER functions Nesting functionsGeneral functions:NVL NVL2 NULLIFCOALESCEConditional expressions:條件表CASEDECODE4 - 36Conditional Expressions條件表Provide the use of the IF-THEN-ELSE logic within a SQL statement提供IF-THEN-ELSE邏輯Use two methods有2種方式:.CASE expression CASE表DECODE function DECODE函數(shù)4 - 37

24、CASE Expression CASE語法Facilitates conditional inquiries by doing the work of anIF-THEN-ELSE statement:使用如下的IF-THEN-ELSE語句，可以容易地進行條件4 - 38CASE expr WHEN comparison_expr1 THEN return_expr1 WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprnELSE else_exprENDUsing the CASE Ex

25、pression CASE例子Facilitates conditional inquiries by doing the work of anIF-THEN-ELSE statement:4 - 39SELECT last_name, job_id, salary,FROMemployees;CASE job_id WHEN IT_PROG THEN 1.10*salary WHEN ST_CLERK THEN 1.15*salary WHEN SA_REP THEN 1.20*salaryELSE salary END REVISED_SALARYDECODE Function DECOD

26、E語法Facilitates conditional inquiries by doing the work of a CASEexpression or an IF-THEN-ELSE statement:可以使用DECODE函數(shù)，容易地進行IF-THEN-ELSE條件4 - 40DECODE(col|expression, search1, result1, search2, result2,., , default)Using the DECODE Function DECODE例子4 - 41SELECT last name, job id, salary,FROMemployees;

27、DECODE(job_id, IT_PROG, 1.10*salary,ST_CLERK, 1.15*salary, SA_REP,1.20*salary,salary) REVISED_SALARYUsing the DECODE Function例子Display the applicable tax rate for each employee in department 80:4 - 42SELECT last_name, salary,FROMemployeesWHERE department_id = 80;DECODE (TRUNC(salary/2000, 0),0, 0.00

28、,1, 0.09,2, 0.20,3, 0.30,4, 0.40,5, 0.42,6, 0.44,0.45) TAX_RATEQuiz課堂測試The TO_NUMBER function converts either character strings or date values to a number in the format specified by the optionalformat m.1.2.TrueFalse4 - 43Summary小結(jié)In this lesson, you should have learned how to:本課你已經(jīng)學(xué)到Alter date form

29、ats for display using functions更改日期顯示形式Convert column data types using functions轉(zhuǎn)換列數(shù)據(jù)Use NVL functions空值函數(shù)Use IF-THEN-ELSE logic and other conditional expressions in a SELECT statementIF-THEN-ELSE條件4 - 44Practice 4: OverviewThis practice covers the following topics:Creating queries that use TO_CHAR,

30、 TO_DATE, and otherDATE functionsCreating queries that use conditional expressions such asDECODE and CASE4 - 451.Create a report that produces the following for each employee: earns monthly but wants . Label the column Dream Salaries2.Display each employees last name, hire date, and salary review da

31、te, which is the first Monday after six months of service. Label the column REVIEW. Format the dates to appear in the format similar to “Monday, the Thirty-First of July, 2000.”3. Display the last name, hire date, and day of the week on which the employee started.Label the column DAY. Order the resu

32、lts by the day of the week, starting with Monday4. Create a query that displays the employees last names and commission amounts. If an employee does not earn commission, show “No Commission.” Label the column COMM.4 - 465. Using the DECODE function, write a query that displays the grade of all emplo

33、yees based on the value of the JOB_ID column, using the following data:Job AD_PRES ST_MAN IT_PROG SA_REP ST_CLERKNone of the aboveGradeA B C D E 06. Rewrite the statement in the preceding exercise by using the CASE syntax.4 - 474 - 484 - 491.Create a report that produces the following for each emplo

34、yee: earns monthly but wants . Label the column Dream SalariesSQLSELECT last_name | earns | TO_CHAR(salary, fm$99,999.00)| monthly but wants | TO_CHAR(salary * 3, fm$99,999.00)| . Dream Salaries FROM employees;4 - 502.Display each employees last name, hire date, and salary review date, which is the first Monday after six months of service. Label the column REVIEW. Format the dates to appear in the format similar to “Monday, the Thirty-First of July, 2000.”SQLSELECT last_name, hire_date, TO_CHAR(NEXT_DAY(ADD_MONTHS(hire_date, 6),MONDAY),fmDay, the Ddspth of Mon