分享
 
 
 

Oracle 常用功能函数汇总

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

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

* 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

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 str1str2)

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

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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
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- 王朝網路 版權所有