[php]
Lession3
Single Row Functions
* Functions are a very powerful feature of SQL and can be used to
Perform calculations on data.
Modify individual data items.
Manipulate output for groups of rows.
Alter date formats for display.
Convert column datatypes.
* An argument may be one of the following:
A user-supplied constant
A variable value
A column name
An expression
* Features of Single Row Functions
They may expect one or more user arguments.
You can nest them.
You can use them in SELECT, WHERE, and ORDER BY clauses.－－ 为什么不写：group by?
* Lower(),Upper(): Converts alpha character
INITCAP(): Converts alpha character values to uppercase for the first letter of each word, all other letters in lowercase.
CONCAT(): 同 ||
SUBSTR(column|expression,m[,n]): m 不能缺省，n缺省为到end, n=0返回null
NVL(column|expression1,column|expression2)：exp1和exp2必须同类型，或者可以隐含转换
ROUND(column|expression,n)：n＝0或n不指定，则round到整数位；n为负数，则从小数点起向左边round；Round the date at midnight.
TRUNC(column|expression,n)：n＝0或n不指定，则round到整数位；n为负数，则从小数点起向左边n位trunc为0
NEXT_DAY(date,‘char’)：Finds the date of the next specified day of the week (‘char’) following date. char may be a number representing a
day or a character string. (char在英文字符集下，用三位表示即可，如：mon, char是和字符集相关的？在中文字符集下具体的char格式如何寻找？)
* Oracle stores dates in an internal numberic format. - Century,year,month,day,hours,minutes,seconds.
* Default date display is DD-MON-YY.-- 于CharacterSet设置有关，nls_data_format, nls_date_language
* Valid Oracle dates are between January 1, 4712 B.C. and December 31, 4712 A.D.
--Oracle8i中不知是否更改了?
* The DUAL table is owned by the user SYS and may be accessed by all users. It contains one column, DUMMY, and one row with the value “X”
* Add hours to a date by dividing the number of hours by 24.－按天的方式增减
* 日期值可以直接按字符形式比较，比如：WHERE start_date LIKE ’%1991’; 当然于nls_date_format有关
* to_char(date): The names of days and months in the output are automatically padded with blanks. To remove padded blanks or to suppress leading zeros, use the fill mode fm element. The resultant column width is 80 characters by default. (注意：8i中已经改变了不是80列)
* TO_CHAR() format:
MONTH
Name of month padded with blanks to length of 9 characters.
RM
Roman numeral month.
DAY
Name of day padded with blanks to length of 9 characters.
DY
Name of day; 3-letter abbreviation.
Years in dates YYYY or SYYYY Year;
S prefixes BC date with -.
SCC or CC
Century;
S prefixes BC date with -.
Q
Quarter of year.
SSSSS
Seconds past midnight (0-86399).
/ . ,
Punctuation is reproduced in the result.
“ of the ”Quoted string is reproduced in the result.
TH
Ordinal number (for example, DDTH for 4TH).
SP
Spelled-out number (for example, DDSP for FOUR).
SPTH or THSP
Spelled-out ordinal numbers (for example, DDSPTH for FOURTH).
L
uses the floating local currency symbol
set the NLS_CURRENCY parameter
MI
Minus signs to right (negative values).
999999MI 1234-
PR
Parenthesize negative numbers.
999999PR
EEEE
Scientific notation (format must specify four Es).
99.999EEEE 1.234E+03
V
Multiply by 10 n times (n= no of 9s after V).
9999V99 123400
B
Display zero values as blank, not 0.
B9999.99 1234.00
FM/fm
Fill Mode 删除前导0和右边的空格
FX
Format Exact 精确匹配
－参见ORACLE文档
* RR available in Oracle7, not Oracle Version 6.
* NLS parameters may be added to init.ora file to set default date formats and language names and abbreviations. And you can use the dcl command: "Alter Session" to change it in a session and can change it in register table in Windows system.
* The Oracle Server displays a string of pound signs (#) in place of a whole number whose digits exceed the number of digits provided in the format model.
* Single row functions can be nested to any depth. Nested functions are evaluated from the innermost level to the outermost level. 可以嵌套任意层次，执行次序从内到外；
？在中文字符集下具体的char格式如何寻找？
-- --
[/php]
|
