转换函数与条件表达式

1、Using Conversion Functions and Conditional Expressions转换函数与条件表Copyright 2010, UplObjectives课程目标After completing this lesson, you should be able to do the following本课中你应该掌握:Describe the various types of conversion functions that are available in SQL了解有用的转换函数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应用条件表4 - 2Lesson Agenda课程进度Implicit and explicit data type conversion隐式与显式数据转换TO_CHAR, TO_DATE, TO_NUMBER functions Nesting functionsGeneral functions:NVL NVL2 NULLIFCOALESCEConditional expressions:CASEDEC

3、ODE4 - 3Conversion Functions转换函数Data type Conversion数据类型转换Implicit data typeExplicit data typeConversion隐式数据类型转换Conversion显示数据类型转换4 - 4Implicit Data Type Conversion隐式转换In expressions, the Oracle server can automatically convert the following:中oracle服务器能自动转换的在表4 - 5FromToVARCHAR2 or CHARNUMBERVARCHAR

4、2 or CHARDATEImplicit Data Type ConversionFor expression evaluation, the Oracle server can automatically convert the following:求值Oracle服务器能自动转换的对表4 - 6FromToNUMBERVARCHAR2 or CHARDATEVARCHAR2 or CHARExplicit Data Type Conversion显示转换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区分大小写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:序数4 - 14ddspthfourteenthDD of MONTH12 of OCTOBERHH24:MI:SS AM15:45:32 PM4 - 15Using the TO_CHAR Function with Dates to_char函数处理日期4 - 16SELECT last_name,FROMemployees;TO_CHAR(hire_date, fmDD Month YYYY)AS HIREDATEUsing the TO_CHAR Function with Numbers to_char函数处理数字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:转换数字为字符的元素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数字到字符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:转字符为数字用to_number函数Convert a character string to a date format using theTO_DATE function:转字符为日期用to_date函数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函数里可用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:嵌套函数NVL NVL2 NULLIFCOALESCEConditional expressions:CASEDECODE4 - 23Nesting Functions嵌套函数Single-r

15、ow functions can be nested to any level.单行函数可嵌套多层Nested functions are evaluated from the deepest level to the least deep level.嵌套函数的计算过程是从最内层向最外层运算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: 空值函数NVL NVL2 NULLIFCOALESCEConditional expressions:CASEDECODE4 - 27General Functions空值函数The following functions work with any data type and pertain to using nulls适合空值的函数如下:NVL (expr1,expr2)如果expr1为空则返回expr2否则返回expr1NVL2 (expr1,expr2,expr3)如果expr1不为空返回expr2，为空返回

18、expr3NULLIF (expr1,expr2)如果相同返回空否则返回expr1COALESCE (expr1,expr2,.,exprn)4 - 28NVL Function NVL函数Converts a null value to an actual value转空值为具体值:Data types that can be used are date, character, and number.适用于数字，字符，日期Data types must match:数据类型必须匹配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函数The advantage of the COALESCE func

21、tion over the NVL function is that the COALESCE function can take multiple alternate values.使用COALESCE函数而不使用NVL函数的优势在于，COALESCE函数可以使用多个替换值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函数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函数，容易地进行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小结In this lesson, you should have learned how to:本课你已经学到Alter date form

29、ats for display using functions更改日期显示形式Convert column data types using functions转换列数据Use NVL functions空值函数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