Single-Row Functions

[url]http://superman-chenzs.itpub.net/post/29327/274808[/url]

- figer: function_name [(arg1,arg2,...)]
- Manipulate data items
- Accept arguments and return one value
- Act on each row returned
- Return one result per row
- May modify the data type
- Can be nested
- Accept arguments which can be a column or an expression
-----------------------------------------------------------------------------------------------------------------
Single-Row Functions' Type:
1.Character
2.Number
3.Date
4.Conversion
5.General


-----------------------------------------------------------------------------------------------------------------
Character Functions:
1.Case-manipulation functions
- LOWER : get the sql course's lower shap letters
- UPPER : get the sql course's upper shap letters
- INITCAP : get the sql course's first letter's upper shap and the others letter is lower,init caps Lock
eg:
FUNCTION RESULT
------------------------------
LOWER('CHENZS') chenzs
UPPER('chenzs') CHENZS
INITCAP('CHENZS') Chenzs
INITCAP('chenzs') Chenzs
INITCAP('cHENZS') Chenzs
INITCAP('Chenzs') Chenzs

2.Character-manipulation functions
- CONCAT : CONCAT('string1','string2'),连接了个字符串，相当于符合||
- SUBSTR : SUBSTR('string',A,B)得到某一个字符串的子字符串,A为正表示从string的左边第A个字符从左往右取B个字符；A为负数，表示从string的右边第|A|个字母从左往右取B个字符；B必须为正，若为负，返回空白。注意是字符非字节
- LENGTH : LENGTH('string'),get the length of the string
- INSTR : INSTR('string','c'),get the first char 'c''s location in the string
- LPAD|RPAD : LPAD('string',len,'C'),RPAD('string',len,'C'),在左/右边填充相应的字符(串)'C'，使字符串string达到相应的长度len
- TRIM：
- REPLACE
eg:

FUNCTION RESULT
------------------------------------------
CONCAT('Hello','World') HelloWorld
SUBSTR('HelloWorld',1,5) Hello
SUBSTR('HelloWorld',-5,3) Wor
SUBSTR('HelloWorld',-3,-2)
SUBSTR('HelloWorld',3,-2)
SUBSTR('超人，我爱你',2,3) 人，我
LENGTH('HELLOWORLD') 10
INSTR('HELLOWORLD','W') 6
INSTR('CHENZSCHENZS,'C') 1
LPAD(salary,10,'*') *****24000
RPAD(salary,10,'*') 24000*****
LPAD(salary,10,'*!') *!*!*24000
RPAD(salary,10,'*!') 24000*!*!*
TRIM('H' FROM 'HelloWorld') elloWorld
TRIM('e' FROM 'HelloWorld') HelloWorld
TRIM('e' FROM 'eHelloWorld') HelloWorld
TRIM('e' FROM 'HelloWorlde') HelloWorld
TRIM('e' FROM 'eHelloWorlde') HelloWorld


-----------------------------------------------------------------------------------------------------------------
Number Functions:
ROUND : Rounds value to specified decimal,ROUND(numA,numB)
TRUNC : Truncate value to specified decimal,TRUNC(numA,numB)
MOD : Returns remainder of division,MOD(numA,numB)
eg:
FUNCTION RESULT
------------------------------
ROUND(45.926,2) 45.93
ROUND(45.921,2) 45.92
ROUND(45.926,-1) 50
ROUND(45.921,-2) 0
TRUNC(45.926,2) 45.92
TRUNC(45.921,2) 45.92
TRUNC(45.926,-1) 10
TRUNC(45.921,-2) 0
MOD(1600,300) 100
MOD(10.54,0.5) 0.04


-----------------------------------------------------------------------------------------------------------------
Date Functions:
- Oracle database stors dates in an internal numeric format:century,year,month,day,hours,minutes,seconds
- The default date display format is DD-MON-yy
- SYSDATE is a function that returns:Date,Time

Arithmetic with Dates:
- Add or subtract a number to or from a date for a resultant date value
- Subtract two dates to find the number of days between those dates
- Add hours to a date by dividing the number of hours by 24

Function Description
----------------------------------------------------------
MONTHS_BETWEEN Number of months between two dates
ADD_MONTHS Add calendar months to date
NEXT_DAY Next day of the date specified
LAST_DAY Last day of the month
ROUND Round date
TRUNC Truncate date

eg:
MOUTHS_BETWEEN('01-SEP-95','11-JAN-94') -> 19.6774194
ADD_MONTHS('11-JAN-94',6) -> '11-JUL-94'
NEXT_DAY('01-SEP-95','FRIDAY') -> '08-SEP-95'
LAST_DAY('01-FEB-95') -> '28-FEB-95'
Assume SYSDATE = '25-JUL-95':
ROUND(SYSDATE,'MONTH') -> 01-AUG-95
ROUND(SYSDATE,'YEAR') -> 01-JAN-96
TRUNC(SYSDATE,'MONTH') -> 01-JUL-95
TRUNC(SYSDATE,'YEAR') -> 01-JAN-95


-----------------------------------------------------------------------------------------------------------------
Conversion Funtions:
- Data type conversion including: Implicit data type conversion & Explicit data type conversion

Implicit Data Type Conversion:
--------------------------------
FROM TO
--------------------------------
VARCHAR2 OR CHAR NUMBER
VARCHAR2 OR CHAR DATE
NUMBER VRACHAR2
DATE VARCHAR2

Explicit Data Type Conversion:
NUMBER ---TO_CHAR()---> CHARACTER <--TO_CHAR()--- DATE
NUMBER <--TO_NUMBER()-- CHARACTER ---TO_DATE()--> DATE

1.Using the TO_CHAR Function with Dates:
- TO_CHAR(date,'format_model')
- The format model:
* Must be enclosed in single quotation marks and is case sensitive
* Can include any valid date format element
* Has an fm element to remove padded blanks or suppress leading zeros
* Is separated from the date value by a comma
- Elements of the Date Format Model
* YYYY : Full year in numbers
* YEAR : Year spelled out
* YY : Two-digit value for year in the current century.eg:(this year is 2006),so 19-->2019,56-->2056
* RR : Two-digit value for year in the current century.eg:(this year is 2006),so 19-->2019,56-->1956(devived on the 50 year of the century)
* MM : Two-digit value for month
* MONTH: Full name of the month
* MON : Three-letter abbreviation of the month
* DY : Three-letter abbreviation of the day of the week
* DAY : Full name of the day of the week
* DD : Numeric day of the month
* Oracle stores time in 24-hour format--HH:MI:SS
* By default, the time in a date field is 00:00:00 A.M. (midnight) if no time portion is entered.
eg:
Time elements format the time portion of the date:
HH24:MI:SS AM --> 15:45:32 PM
* Add character strings by enclosing them in double quotation marks in the formate
eg:
DD "of" MONTH --> 12 of OCTOBER
Number suffixes spell out numbers
eg:
ddapth --> fourteenth
* the RR year format:
-----------------------------------------------------------------------------------|
| If the specified two-digit year is |
|---------------------|------------------------------|
| 0--49 | 50--99 |
----------------------------------------------------|------------------------------|
| |The return date is in|The return date in the century|
the year of the | RR |the current century |before the current one |
|-------|---------------------|------------------------------|
date's format | |The return date is in|The return date is in the |
| YY |the current century |current century |
------------------------------------------------------------------------------------

2.Using the TO_CHAR Function with Numbers:
- TO_CHAR(number,'format_model')
- the format model:
* 9 : Represents a number
* 0 : Forces a zero to be displayed
* $ : Places a floating dollar sign
* L : Uses the floating local currency symbol
* . : Prints a decimal point
* , : Prints a thousand indicator

3.Using the TO_NUMBER & TO_DATE Functions:
- Convert a character string to a number format using the TO_NUMBER function:TO_NUMBER(char1[,'format_model']);
- Convert a character string to a date format using the TO_DATE function:TO_DATE[,'format_model']);
This functions have an fx modifier. This modifier specifies the exact matching for the character argument and date format model of a TO_DATE function.


-----------------------------------------------------------------------------------------------------------------
Nesting Functions:
- Single-row functions can be nested to any level
- Nested functions are evaluated from deepest level to the least deep level


-----------------------------------------------------------------------------------------------------------------
General Functions:
- these functions work with any data type and pertain to using nulls
1.NVL(expr1,expr2)
- if expr1 is null show expr2,else show expr1
- Data types that can be used are date,character and number.
- Data types must match.
2.NVL2(expr1,expr2,expr3) :
- if expr1 is null show expr2,else show expr3
3.NULLIF(expr1,expr2) :
- if expr1 equals expr2 show null,else show expr1
4.COALESCE(expr1,expr2,...,exprn) :
- return the first null value between exprns.
- If the first expression is not null,it returns that expression;otherwise,it does a COALESCE of the remaining expressions.
- The advantage of the COALESCE function over the NVL function is that the COALESCE function can take multiple alternate values.


-----------------------------------------------------------------------------------------------------------------
Conditional Expressions:
- Provide the use of IF=THEN-ELSE logic within a SQL statement.
- Use two methods:
- CASE expression
- DECODE function

1.The CASE Expression
Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement.
Syntax:
--------------------------------------------------
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
--------------------------------------------------

2.The DECODE Function
Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement:
Syntax:
-------------------------------------------
DECODE (col|expression,search1,result1
[,search2,result2,...]
[,default value])