5数据库内置函数.pptVIP

* Single-Row Functions Single-row functions can be nested to any level. Single-row functions can manipulate the following: Character data: LOWER, UPPER, INITCAP, CONCAT, SUBSTR, INSTR, LENGTH Number data: ROUND, TRUNC, MOD Date data: MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, TRUNC Date values can also use arithmetic operators. Conversion functions can convert character, date, and numeric values: TO_CHAR, TO_DATE, TO_NUMBER There are several functions that pertain to nulls, including NVL, NVL2, NULLIF, and COALESCE. IF-THEN-ELSE logic can be applied within a SQL statement by using the CASE expression or the DECODE function. SYSDATE and SYSIBM.SYSDUMMY1 SYSDATE is a date function that returns the current date and time. It is customary to select SYSDATE from a dummy table called SYSIBM.SYSDUMMY1. CASE 表达式 在需要使用 IF-THEN-ELSE 逻辑时: CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END CASE 表达式 下面是使用case表达式的一个例子： SELECT Order , Customer, Orderdate, Shipdate , Shipcity, CASE Shipcity WHEN 上海 THEN 直辖市 WHEN 南京 THEN 省内 WHEN 无锡 THEN 省内 WHEN 常州 THEN 省内 ELSE 省外 END Province from TBL_ORDERS; DECODE 函数 在需要使用 IF-THEN-ELSE 逻辑时: DECODE(col|expression, search1, result1 [, search2, result2,...,] [, default]) DECODE 函数 SELECT Order ,Customer, Orderdate, Shipdate ,Shipcity, DECODE( Shipcity , 上海 , 直辖市, 南京 , 省内, 无锡 , 省内, 常州 , 省内, 省外 ) Province FROM TBL_ORDERS DECODE 函数 SELECT Title, Cost,Retail, DECODE(TRUNC(Retail/Cost, 1) ,1.5,挣了一半, 1.6,挣了一半还多, 自己算算挣了多少) FROM TBL_BOOKS WHERE PUBID=4; 使用decode函数的一