03数据查询和事务控制SELECT进阶单行函数分析.ppt

函数可以使用date, character, 和number数据类型。 表达式的值和替换值类型必须匹配: NVL(comm,0) NVL(hiredate,01-JAN-97) NVL(job,No Job Yet) SELECT Ename, sal, NVL(comm, 0), (sal*12) + NVL(comm, 0) AN_SAL FROM emp; 1 2 2 1 SELECT ename, sal, comm, NVL2(comm, SAL+COMM, SAL) income FROM emp; 1 2 1 2 条件表达式：在SQL语句内，提供的IF-THEN-ELSE逻辑表达式。有两种方法: CASE表达式 DECODE函数 CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END SELECT ename, job, sal, CASE job WHEN ANALYST THEN 1.10*sal WHEN CLERK THEN 1.15*sal WHEN SALESMAN THEN 1.20*sal ELSE sal END REVISED_SALARY FROM emp; DECODE(col|expression, search1, result1 [, search2, result2,...,] [, default]) SELECT ename, job, sal, DECODE(job, ANALYST, 1.10*sal, CLERK, 1.15*sal, SALESMAN, 1.20*sal, sal) REVISED_SALARY FROM emp; SELECT ename, sal, DECODE (TRUNC(sal/2000, 0), 0, 0.00, 1, 0.09, 2, 0.20, 3, 0.30, 4, 0.40, 5, 0.42, 6, 0.44, 0.45) TAX_RATE FROM emp WHERE deptno = 30; F3(F2(F1(col,arg1),arg2),arg3) Step 1 = Result 1 Step 2 = Result 2 Step 3 = Result 3 SELECT Ename, NVL(TO_CHAR(mgr), No Manager) FROM emp WHERE mgr IS NULL; 单行函数能够在任何一个层次嵌套。 3 Arithmetic with Dates Since the database stores dates as numbers, you can perform calculations using arithmetic operators such as addition and subtraction. You can add and subtract number constants as well as dates. You can perform the following operations: Conversion Functions In addition to Oracle data types, columns of tables in an Oracle9