oracle開發常用到的函式

Purpose

DECODE compares expr to each search value one by one. If expr is equal to a search, then Oracle Database returns the corresponding result. If no match is found, then Oracle returns default. If default is omitted, then Oracle returns null.

The arguments can be any of the numeric types (NUMBER, BINARY_FLOAT, or BINARY_DOUBLE) or character types.

• If expr and search are character data, then Oracle compares them using nonpadded comparison semantics. expr, search, and result can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The string returned is ofVARCHAR2 datatype and is in the same character set as the first result parameter.

• If the first search-result pair are numeric, then Oracle compares all search-result expressions and the first expr to determine the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.

The search, result, and default values can be derived from expressions. Oracle Database uses short-circuit evaluation. That is, the database evaluates each search value only before comparing it to expr, rather than evaluating allsearch values before comparing any of them with expr. Consequently, Oracle never evaluates a search if a previous search is equal to expr.

Oracle automatically converts expr and each search value to the datatype of the first search value before comparing. Oracle automatically converts the return value to the same datatype as the first result. If the first result has the datatype CHAR or if the first result is null, then Oracle converts the return value to the datatype VARCHAR2.

In a DECODE function, Oracle considers two nulls to be equivalent. If expr is null, then Oracle returns the result of the first search that is also null.

The maximum number of components in the DECODE function, including expr, searches, results, and default, is 255.

Examples

This example decodes the value warehouse_id. If warehouse_id is 1, then the function returns 'Southlake'; if warehouse_id is 2, then it returns 'San Francisco'; and so forth. If warehouse_id is not 1, 2, 3, or 4, then the function returns 'Non domestic'.

SELECT product_id,
       DECODE (warehouse_id, 1, 'Southlake', 
                             2, 'San Francisco', 
                             3, 'New Jersey', 
                             4, 'Seattle',
                                'Non domestic') 
       "Location of inventory" FROM inventories
       WHERE product_id < 1775;

2.oracle本月、上月、去年同月第一天，最後一天

	select trunc(sysdate, 'month') 本月第一天,



	       trunc(last_day(sysdate)) 本月最後一天,



	       trunc(add_months(sysdate, -1), 'month') 上月第一天,



	       trunc(last_day(add_months(sysdate, -1))) 上月最後一天,



	       trunc(add_months(sysdate, -12), 'month') 去年本月第一天,



	       trunc(last_day(add_months(sysdate, -12))) 去年本月最後一天



	  from dual

3.行專列函式wm_concat

SELECT 
UUGR_UURR.USERID,to_char(wm_concat(ROLENAME))
FROM
(SELECT UUGR.USERID,UURR.ROLEID FROM UC_USERINFO_GROUP_REF UUGR,UC_GROUP_ROLE_REF UURR WHERE UUGR.GROUPID=UURR.GROUPID) UUGR_UURR,UC_ROLE UR 
WHERE UUGR_UURR.ROLEID=UR.ID GROUP BY UUGR_UURR.USERID

在用到wm_concat之後如果是字串型別則要進行一次to_char轉換，否則會返回字串