Oracle单行函数分析.ppt

The COALESCE Function In the example shown, if the COMMISSION_PCT value is not null, it is shown. If the COMMISSION_PCT value is null, then the SALARY is shown. If the COMMISSION_PCT and SALARY values are null, then the value 10 is shown. Conditional Expressions Two methods used to implement conditional processing (IF-THEN-ELSE logic) within a SQL statement are the CASE expression and the DECODE function. Note: The CASE expression is new in the Oracle9i Server release. The CASE expression complies with ANSI SQL; DECODE is specific to Oracle syntax. The CASE Expression CASE expressions let you use IF-THEN-ELSE logic in SQL statements without having to invoke procedures. In a simple CASE expression, Oracle searches for the first WHEN ... THEN pair for which expr is equal to comparison_expr and returns return_expr. If none of the WHEN ... THEN pairs meet this condition, and an ELSE clause exists, then Oracle returns else_expr. Otherwise, Oracle returns null. You cannot specify the literal NULL for all the return_exprs and the else_expr. All of the expressions ( expr, comparison_expr, and return_expr) must be of the same data type, which can be CHAR, VARCHAR2, NCHAR, or NVARCHAR2. Instructor Note There is also a searched CASE expression. Oracle searches from left to right until it finds an occurrence of a condition that is true, and then returns return_expr. If no condition is found to be true, and an ELSE clause exists, Oracle returns else_expr. Otherwise Oracle returns null. For more information, see Oracle9i SQL Reference, “Expressions.” Using the CASE Expression In the preceding SQL statement, the value of JOB_ID is decoded. If JOB_ID is IT_PROG, the salary increase is 10%; if JOB_ID is ST_CLERK, the salary increase is 15%; if JOB_ID is SA_REP, the salary increase is 20%. For all other job roles, there is no increase in salary. The same statement can be written with the DECODE function. The DECODE Function The DECODE function decodes an expression in a way