分享
 
 
 

Oracle 常用函数

王朝oracle·作者佚名  2006-01-31
窄屏简体版  字體: |||超大  

oracle 常用功能函数汇总********************

*******************************************************************************

* SQL Group Functions (num can be a column or expression) *

(null values are ignored, default between distinct and all is all) *

*******************************************************************************

AVG([distinct or all] num) -- average value

COUNT(distinct or all] num) -- number of values

MAX([distinct or all] num) -- maximum value

MAX([distinct or all] num) -- minimum value

STDDEV([distinct or all] num) -- standard deviation

SUM([distinct or all] num) -- sum of values

VARIANCE([distinct or all] num) -- variance of values

*******************************************************************************

* Miscellaneaous Functions : *

*******************************************************************************

DECODE(expr, srch1, return1 [,srch2, return2...], default]

-- if no search matches the expression then the default is returned,

-- otherwise, the first search that matches will cause

-- the corresponding return value to be returned

DUMP(column_name [,fmt [,start_pos [, length]]])

-- returns an internal oracle format, used for getting info about a column

-- format options : 8 = octal, 10 = decimel, 16 = hex, 17 = characters

-- return type codes : 1 = varchar2, 2 = number, 8 = long, 12 = date,

-- 23 = raw, 24 = long raw, 69 = rowid, 96 = char, 106 = mlslabel

GREATEST(expr [,expr2 [, expr3...]]

-- returns the largest value of all expressions

LEAST(expr [,expr2 [, expr3...]]

-- returns the smallest value of all expressions

NVL(expr1 ,expr2

-- if expr1 is not null, it is returned, otherwise expr2 is returned

SQLCODE

-- returns sql error code of last error. Can not be used directly in query,

-- value must be set to local variable first

SQLERRM

-- returns sql error message of last error. Can not be used directly in query,

-- value must be set to local variable first

UID

-- returns the user id of the user you are logged on as

-- useful in selecting information from low level sys tables

USER

-- returns the user name of the user you are logged on as

USERENV('option')

-- returns information about the user you are logged on as

-- options : ENTRYID, SESSIONID, TERMINAL, LANGUAGE, LABEL, OSDBA

-- (all options not available in all Oracle versions)

VSIZE(expr)

-- returns the number of bytes used by the expression

-- useful in selecting information about table space requirements

*******************************************************************************

* SQL Date Functions (dt represents oracle date and time) *

* (functions return an oracle date unless otherwise specified) *

*******************************************************************************

ADD_MONTHS(dt, num) -- adds num months to dt (num can be negative)

LAST_DAY(dt) -- last day of month in month containing dt

MONTHS_BETWEEN(dt1, dt2) -- returns fractional value of months between dt1, dt2

NEW_TIME(dt, tz1, tz2) -- dt = date in time zone 1, returns date in time zone 2

NEXT_DAY(dt, str) -- date of first (str) after dt (str = 'Monday', etc..)

SYSDATE -- present system date

ROUND(dt [,fmt] -- rounds dt as specified by format fmt

TRUNC(dt [,fmt] -- truncates dt as specified by format fmt

*******************************************************************************

* Number Functions : *

*******************************************************************************

ABS(num) -- absolute value of num

CEIL(num) -- smallest integer > or = num

COS(num) -- cosine(num), num in radians

COSH(num) -- hyperbolic cosine(num)

EXP(num) -- e raised to the num power

FLOOR(num) -- largest integer < or = num

LN(num) -- natural logarithm of num

LOG(num2, num1) -- logarithm base num2 of num1

MOD(num2, num1) -- remainder of num2 / num1

POWER(num2, num1) -- num2 raised to the num1 power

ROUND(num1 [,num2] -- num1 rounded to num2 decimel places (default 0)

SIGN(num) -- sign of num * 1, 0 if num = 0

SIN(num) -- sin(num), num in radians

SINH(num) -- hyperbolic sine(num)

SQRT(num) -- square root of num

TAN(num) -- tangent(num), num in radians

TANH(num) -- hyperbolic tangent(num)

TRUNC(num1 [,num2] -- truncate num1 to num2 decimel places (default 0)

*******************************************************************************

* String Functions, String Result : *

*******************************************************************************

(num) -- ASCII character for num

CHR(num) -- ASCII character for num

CONCAT(str1, str2) -- str1 concatenated with str2 (same as str1||str2)

INITCAP(str) -- capitalize first letter of each word in str

LOWER(str) -- str with all letters in lowercase

LPAD(str1, num [,str2]) -- left pad str1 to length num with str2 (default spaces)

LTRIM(str [,set]) -- remove set from left side of str (default spaces)

NLS_INITCAP(str [,nls_val]) -- same as initcap for different languages

NLS_LOWER(str [,nls_val]) -- same as lower for different languages

REPLACE(str1, str2 [,str3]) -- replaces str2 with str3 in str1

-- deletes str2 from str1 if str3 is omitted

RPAD(str1, num [,str2]) -- right pad str1 to length num with str2 (default spaces)

RTRIM(str [,set]) -- remove set from right side of str (default spaces)

SOUNDEX(str) -- phonetic representation of str

SUBSTR(str, num2 [,num1]) -- substring of str, starting with num2,

-- num1 characters (to end of str if num1 is omitted)

SUBSTRB(str, num2 [,num1]) -- same as substr but num1, num2 expressed in bytes

TRANSLATE(str, set1, set2) -- replaces set1 in str with set2

-- if set2 is longer than set1, it will be truncated

UPPER(str) -- str with all letters in uppercase

*******************************************************************************

* String Functions, Numeric Result : *

*******************************************************************************

ASCII(str) -- ASCII value of str

INSTR(str1, str2 [,num1 [,num2]]) -- position of num2th occurrence of

-- str2 in str1, starting at num1

-- (num1, num2 default to 1)

INSTRB(str1, str2 [,num1 [num2]]) -- same as instr, byte values for num1, num2

LENGTH(str) -- number of characters in str

LENGTHB(str) -- number of bytes in str

NLSSORT(str [,nls_val]) -- nls_val byte value of str

*******************************************************************************

* SQL Conversion Functions *

*******************************************************************************

CHARTOROWID(str) -- converts str to ROWID

CONVERT(str, chr_set2 [,chr_set1]) -- converts str to chr_set2

-- chr_set1 default is the datbase character set

HEXTORAW(str) -- converts hex string value to internal raw values

RAWTOHEX(raw_val) -- converts raw hex value to hex string value

ROWIDTOCHAR(rowid) -- converts rowid to 18 character string format

TO_CHAR(expr [,fmt]) -- converts expr(date or number) to format specified by fmt

TO_DATE(str [,fmt]) -- converts string to date

TO_MULTI_BYTE(str) -- converts single byte string to multi byte string

TO_NUMBER(str [,fmt]) -- converts str to a number formatted by fmt

TO_SINGLE_BYTE(str) -- converts multi byte string to single byte string

*******************************************************************************

* SQL Date Formats *

*******************************************************************************

BC, B.C. BC indicator

AD, A.D. AD indicator

CC, SCC Century Code (SCC includes space or - sign)

YYYY, SYYYY 4 digit year (SYYYY includes space or - sign)

IYYY 4 digit ISO year

Y,YYY 4 digit year with comma

YYY, YY, or Y last 3, 2, or 1 digit of year

YEAR, SYEAR year spelled out (SYEAR includes space or - sign)

RR last 2 digits of year in prior or next century

Q quarter or year, 1 to 4

MM month - from 01 to 12

MONTH month spelled out

MON month 3 letter abbreviation

RM roman numeral for month

WW week of year, 1 to 53

IW ISO week of year, 1 to 52 or 1 to 53

W week of month, 1 to 5 (week 1 begins 1st day of the month)

D day of week, 1 to 7

DD day of month, 1 to 31

DDD day of year, 1 to 366

DAY day of week spelled out, nine characters right padded

DY day abbreviation

J # of days since Jan 1, 4712 BC

HH, HH12 hour of day, 1 to 12

HH24 hour of day, 0 to 23

MI minute of hour, 0 to 59

SS second of minute, 0 to 59

SSSSS seconds past midnight, 0 to 86399

AM, A.M. am indicator

PM, P.M. pm indicator

any puctuation punctuation between format items, as in 'DD/MM/YY'

any text text between format items

TH converts 1 to '1st', 2 to '2nd', and so on

SP converts 1 to 'one', 2 to 'two', and so on

SPTH converts 1 to 'FIRST', 2 to 'SECOND', and so on

FX fill exact : uses exact pattern matching

FM fill mode : toggles suppression of blanks in output

*******************************************************************************

来自 http://www.oracle.com.cn/viewthread.php?tid=14850

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有