DECODE

Syntax

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 of VARCHAR2 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 all search 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;

参考链接:http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm#i1017437 

SIGN

Syntax


Purpose

SIGN returns the sign of n. This function takes as an argument any numeric datatype, or any nonnumeric datatype that can be implicitly converted to NUMBER, and returns NUMBER.

For value of NUMBER type, the sign is:
    -1 if n<0
    0 if n=0
    1 if n>0

For binary floating-point numbers (BINARY_FLOAT and BINARY_DOUBLE), this function returns the sign bit of the number. The sign bit is:

    -1 if n<0
    +1 if n>=0 or n=NaN

Examples

The following example indicates that the argument of the function (-15) is <0:
SELECT SIGN(-15) "Sign" FROM DUAL;
      Sign
----------
        -1

参考链接: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions145.htm#i90056

SUBSTR

Syntax

substr::=

Purpose

The SUBSTR functions return a portion of char, beginning at character position, substring_length characters long. SUBSTR calculates lengths using characters as defined by the input character set. SUBSTRB uses bytes instead of characters. SUBSTRC uses Unicode complete characters. SUBSTR2 uses UCS2 code points. SUBSTR4 uses UCS4 code points.

• If position is 0, then it is treated as 1.

• If position is positive, then Oracle Database counts from the beginning of char to find the first character.

• If position is negative, then Oracle counts backward from the end of char.

• If substring_length is omitted, then Oracle returns all characters to the end of char. If substring_length is less than 1, then Oracle returns null.

char can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The exceptions are SUBSTRC, SUBSTR2, and SUBSTR4, which do not allow char to be a CLOB or NCLOB. Both position and substring_length must be of data type NUMBER, or any data type that can be implicitly converted to NUMBER, and must resolve to an integer. The return value is the same data type as char. Floating-point numbers passed as arguments to SUBSTR are automatically converted to integers.

Examples

SQL> SELECT SUBSTR('ABCDEFG',3,4) "Substring"
     FROM DUAL;  2 

Substring
----------------------------------------------------------------
CDEF

SQL> SELECT SUBSTR('ABCDEFG',-5,4) "Substring"
     FROM DUAL;  2 

Substring
----------------------------------------------------------------
CDEF

SQL> SELECT SUBSTRB('ABCDEFG',5,4.2) "Substring with bytes"
     FROM DUAL;  2 

Substring with bytes
--------------------------------
EFG

SQL> SELECT SUBSTRB('ABCDEFG',5,1.2) "Substring with bytes" FROM DUAL;

Substring with bytes
--------------------------------
E

SQL>

参考链接：http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions181.htm#i87066  （注：官档中的关于substr函数的关于double-byte类型测试结果是错误的，上例中给出的结果正确）

TRUNC (number)

Syntax

trunc_number::=

Purpose

The TRUNC (number) function returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point.

This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If you omit n2, then the function returns the same datatype as the numeric datatype of the argument. If you include n2, then the function returns NUMBER.

Examples

The following examples truncate numbers:

SELECT TRUNC(15.79,1) "Truncate" FROM DUAL;

  Truncate
----------
      15.7

SELECT TRUNC(15.79,-1) "Truncate" FROM DUAL;

  Truncate
----------
        10

参考链接:http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions200.htm#i79729

TRUNC (date)

Syntax

trunc_date::=

Purpose

The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. The value returned is always of datatype DATE, even if you specify a different datetime datatype for date. If you omit fmt, then date is truncated to the nearest day. Please refer to "ROUND and TRUNC Date Functions" for the permitted format models to use in fmt.

Examples

The following example truncates a date:

SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR')
  "New Year" FROM DUAL;
 
New Year
---------
01-JAN-92