教学讲稿oracle官方les04.pptVIP

Using Conversion Functions and Conditional Expressions Objectives After completing this lesson, you should be able to do the following: Describe various types of conversion functions that are available in SQL Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions Apply conditional expressions in a SELECT statement Lesson Agenda Implicit and explicit data type conversion TO_CHAR, TO_DATE, TO_NUMBER functions Nesting functions General functions: NVL NVL2 NULLIF COALESCE Conditional expressions: CASE DECODE Conversion Functions Implicit Data Type Conversion In expressions, the Oracle server can automatically convert the following: Implicit Data Type Conversion For expression evaluation, the Oracle server can automatically convert the following: Explicit Data Type Conversion Explicit Data Type Conversion Explicit Data Type Conversion Lesson Agenda Implicit and explicit data type conversion TO_CHAR, TO_DATE, TO_NUMBER functions Nesting functions General functions: NVL NVL2 NULLIF COALESCE Conditional expressions: CASE DECODE Using the TO_CHAR Function with Dates The format model: 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 zeros Is separated from the date value by a comma Elements of the Date Format Model Elements of the Date Format Model Time elements format the time portion of the date:  Add character strings by enclosing them with double quotation marks:  Number suffixes spell out numbers: Using the TO_CHAR Function with Dates Using the TO_CHAR Function with Numbers These are some of the format elements that you can use with the TO_CHAR function to display a number value as a character: Using the TO_CHAR Function with Numbers Using the TO_NUMBER and TO_DATE Functions Convert a character string to a number format using the TO_NUMBER function: Convert a character string to a date format using the TO_DATE function: These functions ha