• Describes various conversion functions provided by SQL • TO_CHAR, TO_NUMBER, and TO_DATE conversion functions • Apply conditional expressions to SELECT statements
- Implicit and explicit data type conversion
- TO_CHAR, TO_DATE, and TO_NUMBER Functions
The following functions are applicable to any data type and use null values: • NVL (expr1, expr2) • NVL2 (expr1, expr2, expr3) • NULLIF (expr1, expr2) • COALESCE (expr1, expr2 ,..., exprn)
• Enables you to use the IF-THEN-ELSE logic in SQL statements. • Use the following two methods:-CASE expression-DECODE function in SQL statements, used to execute conditional processing (IF-THEN-ELSE logic) the two methods are CASE expressions and DECODE functions. Note: The CASE expression conforms to ansi SQL. The DECODE function is a specific Oracle syntax.
It has the same effect as the IF-THEN-ELSE statement and can simplify the condition query: CASE expr when then return_expr1 [when then return_expr2WHEN THEN return_exprnELSE else_expr] end case expression allows you to use the IF-THEN-ELSE logic in SQL statements without calling any procedures. In a simple CASE expression, Oracle Server searches for the first WHEN... THEN whose expr is equal to comparison_expr, and returns return_expr. If no WHEN... THEN pair meets this condition and an ELSE clause exists, the Oracle Server returns else_expr. Otherwise, OracleServer returns a null value. You cannot specify the literal value ULL for all return_expr and else_expr. The expressions expr and comparison_expr must have the same data type, which can be CHAR, VARCHAR2, NCHAR, or NVARCHAR2. All returned values (return_expr) must have the same data type.
It has the same effect as the IF-THEN-ELSE statement and can simplify the condition query:Hr @ TEST0924> SELECT last_name, job_id, salary,2 CASE job_id WHEN 'It _ prog' THEN 1.10 * salary3 WHEN 'st _ cler' THEN 1.15 * salary4 WHEN 'sa _ REP 'THEN 1.20 * salary5 ELSE salary END "REVISED_SALARY"6 FROM employees; LAST_NAME JOB_ID SALARY REVISED_SALARY-----------------------------------------------------------OConnell SH_CLERK 2600 2600Grant SH_CLERK 2600 2600Whalen AD_ASST 4400 4400Hartstein MK_MAN 13000 13000The SQL statement in the example is used to decode the value of JOB_ID. If JOB_ID is IT_PROG, the salary is increased by 10%. If JOB_ID is ST_CLERK, the salary is increased by 15%. If JOB_ID is SA_REP, the salary is increased by 20%. No salary increases for all other roles. You can use the DECODE function to write the same statement. The following code is an example of a search CASE expression. In the CASE expression, search from left to right until the listed conditions are found, and then the corresponding return expression is returned. If a condition cannot be found but an ELSE clause exists, the return expression in the else clause is returned. Otherwise, NULL is returned.Hr @ TEST0924> SELECT last_name, salary,2 (case when salary <5000 THEN 'low'3 WHEN salary <10000 THEN 'medium'4 WHEN salary <20000 THEN 'good'5 ELSE 'excellent'6 END) qualified_salary7 FROM employees; LAST_NAME SALARY QUALIFIED--------------------------------------------OConnell 2600 LowFay 6000 MediumMavris6500 MediumHiggins 12008 GoodKing 24000 Excellent
Similar to the CASE expression or IF-THEN-ELSE statement, DECODE (col | expression, search1, result1 [, search2, result2,...,] [, default]) the DECODE function is used to DECODE the expression in a way similar to the IF-THEN-ELSE logic used in various languages. The DECODE function decodes an expression after comparing it with each search value. If the expression is the same as the search value, the result is returned. If the default value is omitted and the search value does not match any result value, a null value is returned. For more details, please continue to read the highlights on the next page:
