oracle函数详解-转

1、SQL 语句基础

下面给出SQL语句的基本介绍。

1.1、SQL语句所用符号

操作符	用途	例子
+ -	表示正数或负数，正数可省去 +	-1234.56
+	将两个数或表达式进行相加	A=c+b
-	将两个数或表达式进行相减	34-12
*	将两个数或表达式进行相乘	12*34
/	除以一个数或表达式	18*11
NULL	空值判断	Where name is null;
\|\|	字符串连接	‘101-’\|\|tel_num
=	等于测试	Select * from emp where name=’赵元杰’;
!= 或<>或^=	不等于测试	Select * from emp where name !=’赵元杰’;
<	小于测试	Select * from emp where sal < 5000;
>	大于测试	Select * from emp where sal > 5000;
<=	小于等于测试	Select * from emp where sal <= 5000;
>=	大于等于测试	Select * from emp where sal >= 5000;
Not in	测试某值是否在一个指定的结果集中	Select name,addr from expert where local not in(‘北京’,’上海’);
ANY	将一个值与一组值进行比较，返回满足条件的结果。必须跟!=,<,>,<=,>=	select ename,sal from emp where sal<= any(select sal from emp where deptno=10)
SOME	同ANY，必须跟!=,<,>,<=,>=
ALL	将一个值与一组值比较，返回满足条件的所有列值。必须跟!=,<,>,<=,>=	Select name,sal from emp w here sal<= all ( 500,800,1200);
Not between A and B	判断某个值是否界于两者之间。	Select name,sal from emp Where sal between 500 and 1200;
[not]exists	判断某个列是否存在于一组值中。	select dname,deptno from dept where exists (select * from emp where dept.deptno=emp.deptno)
A[not]like b	比较两个模式是否相似，当使用like 语句时Oracle不去访问索引。	Select * from emp Where ename like ‘TH%’;
Is [not] null	测试值是否为空。	Select ename,deptno from emp Where comm. Is null or comm.=0;
Not	对结果的否定。	Select * from emp Where sal not(sal<1000); 等价于select ename,sal from emp where sal>=1000;
AND	用于判断两个条件十分都满足。	Select * from emp where Ename=’SIMTH’ and sal>=1000;
OR	用于判断两个条件中是否有一个满足。	Select * from emp where Ename=’SIMTH’ or ename=’SCOTT’;
UNION	用于返回（组合）两个查询中所有唯一的行。	Select ename from emp union Select ename from emp;
UNION ALL	用于返回（组合）两个查询中所有所有的行。
INTERSECT	用于返回两个查询中相同的行。	Select ename from emp1 intersect select ename from emp2;
MINUS	用于返回两个查询中的不同的行。

1.2、简单select查询

当我们可以用SQL*PLUS登录到SQL>下后，我们可以用DESC 显示某表的结构，也可以用select 语句简单查询表中的一些列的内容。

例：要查询EMP表中员工的姓名、工资及出生日期，则：

SQL>select ename, sal, hiredate from emp;

2、伪列及伪表

Oracle系统为了实现完整的关系数据库功能，系统专门提供了一组称为伪列（Pseudocolumn）的数据库列，这些列不是在建立对象（如建表）时由我们完成的，而是在我们建立对象时由自动Oracle完成的。Oracle目前有以下的伪列：

CURRVAL and NEXTVAL 使用序列号的保留字

LEVEL 查询数据所对应的级

ROWID 记录的唯一标识

ROWNUM 限制查询结果集的数量

Oracle 还提供了一个DUAL 的伪表，该目的表主要是保证在使用SELECT 语句中语句的完整性而提供的，如：我们要查询当前的系统日期及时间，而系统的日期和时间并是放在一个指定的表里。所以在 from 语句后就没有表名给出。为了使用 from 后有个表名，我们就用DUAL代替。如：

例1：查询Oracle系统日期及时间：

SQL> select to_char( sysdate,'yyyy.mm.dd hh24:mi:ss') from DUAL;

TO_CHAR(SYSDATE,'YY

-------------------

2001.06.02 07:28:09

例2：计算一下 5000+5000*0.1 的结果是多少，则：

SQL> select 5000+5000*0.1 from DUAL;

5000+5000*0.1

-------------------

5500

3、SELECT语句

在关系数据库中，使用频率最高要算SELECT语句了。尽管SELECT 语句的使用非常简单，但确很有学问。下面简单介绍有关SELECT 语句的常用方法。

1.命令语法：

SELECT 的简单语法：

SELECT [DISTINCT | ALL] {* | column1[, column2]...}

FROM {table_1 | (subquery)} [alias]

[, {table_2 | (subquery)} [alias]]...

[WHERE condition]

[CONNECT BY condition [START WITH condition]

[GROUP BY expn] [HAVING expn]

[{ UNION [ALL] | INTERSECT | MINUS } SELECT . . . ]

[ ORDER BY [expn ] [ ASC | DESC]

[ FOR UPDATE [OF [user.]table | view] column ]

[NOWAIT]

详细语法结构需查阅最新ORACLE 原版《ORACLE8i SQL REFERENCE MANUAL》或《ORACLE9i SQL REFERENCE MANUAL》

4、SQL中的单记录函数

许多资料（包括Oracle 的资料）把Oracle的SQL语句中用到的函数分为单值函数和多值函数，单值函数又分为字符函数和数字函数。下面分别介绍它们。

4.1、单记录字符函数

函数	说明
ASCII	返回对应字符的十进制值
CHR	给出十进制返回字符
CONCAT	拼接两个字符串，与 \|\| 相同
INITCAT	将字符串的第一个字母变为大写
INSTR	找出某个字符串的位置
INSTRB	找出某个字符串的位置和字节数
LENGTH	以字符给出字符串的长度
LENGTHB	以字节给出字符串的长度
LOWER	将字符串转换成小写
LPAD	使用指定的字符在字符的左边填充
LTRIM	在左边裁剪掉指定的字符
RPAD	使用指定的字符在字符的右边填充
RTRIM	在右边裁剪掉指定的字符
REPLACE	执行字符串搜索和替换
SUBSTR	取字符串的子串
SUBSTRB	取字符串的子串（以字节）
SOUNDEX	返回一个同音字符串
TRANSLATE	执行字符串搜索和替换
TRIM	裁剪掉前面或后面的字符串
UPPER	将字符串变为大写
NVL	以一个值来替换空值

1、ASCII(<c1>)

<c1>是字符串。返回与指定的字符对应的十进制数。

SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;

A a ZERO SPACE

---------- ---------- ---------- ----------

65 97 48 32

SQL> select ascii('赵') zhao,length('赵') leng from dual;

ZHAO LENG

---------- ----------

54740 1

2、CHR(<I>[NCHAR])

给出整数，返回对应字符。如：

SQL> select chr(54740) zhao,chr(65) chr65 from dual;

ZH C

-- -

赵 A

3、CONCAT(<c1>,<c2>)

返回字符串c1与字符串c2合并后的值。例如：

SQL> select concat('010-','8801 8159')||'转23' 赵元杰电话 from dual;

赵元杰电话

-----------------

010-8801 8159转23

4、INITCAP(<c1>)

返回字符串c1 并第一个字母变为大写。例如：

SQL> select initcap('simth') upp from dual;

UPP

-----

Simth

5、INSTR(<c1>,<c2>[,<I>[,<j>] ] )

在一个字符串中搜索指定的字符，返回发现指定的字符的位置。

C1: 被搜索的字符串

C2: 希望搜索的字符串

I: 搜索的开始位置，缺省是1

J: 出现的位置，缺省是1。

SQL> SELECT INSTR ('Oracle Training', 'ra', 1, 2) "Instring" FROM DUAL;

Instring

----------

6、INSTRB(<c1>,<c2>[,<I>[,<j>] ] )

除了返回的字节外，与INSTR 相同，

7、LENGTH( <c> )

返回字符串 c 的长度。

SQL> l

1 select name,length(name),addr,length(addr),sal,length(to_char(sal))

2* from nchar_tst

SQL> /

NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL))

------ ------------ ---------------- ------------ ---------- ----------------

赵元杰 3 北京市海淀区 6 99999.99 8

8、LENGTHB( <c> )

以字节返回字符串的字节数。

SQL> select name,lengthb(name),length(name) from nchar_tst;

NAME LENGTHB(NAME) LENGTH(NAME)

------ ------------- ------------

赵元杰 6 3

9、LOWER ( <c> )

返回字符串并将所有字符变为小写。

SQL> select lower('AaBbCcDd') AaBbCcDd from dual;

AABBCCDD

--------

aabbccdd

10、UPPER( <c>)

与 LOWER 相反，将给出字符串变为大写。如：

SQL> select upper('AaBbCcDd') AaBbCcDd from dual;

AABBCCDD

--------

AABBCCDD

11、RPAD和LPAD(粘贴字符)

RPAD(string，Length[，'set'])

LPAD(string，Length[，'set'])

RPAD在列的右边粘贴字符；

LPAD在列的左边粘贴字符。

例1：

SQL>select RPAD(City，35，'.')，temperature from weather;

RPAD(City，35，'.') temperature

-------------------------- ----------------

CLEVELAND...... 85

LOS ANGELES.. 81

.........................

（即不够35个字符用'.'填满）

12、LTRIM(左截断)RTRIM（右截断）函数

LTRIM (string [,’set’])

Left TRIM (左截断)删去左边出现的任何set 字符。

RTRIM (string [,’set’])

Right TRIM (右截断)删去右边出现的任何set 字符。

例1：

SELECT RTRIM (‘Mother Theresa, The’, ‘The’) “Example of Right

Trimming” FROM DUAL;

Example of Right

----------------

Mother Theresa,

13、SUBSTR Substr(string，start[，Count])

取子字符串中函数

对字串（或字段），从start字符开始，连续取 count 个字符并返回结果，如果没有指count则一直取到尾。

select phone,substr(phone,1,3) || ‘0’ || substr(phone,4)

from telecommunication where master=’中国电信’;

14、SUBSTRB(string，start[，Count])

对字串（或字段），从start字节开始，连续取 count 个字节并返回结果，如果没有指count则一直取到尾。

15、REPLACE (‘string’ [,’string_in’,’string_out’])

String: 希望被替换的字符串或变量。

String_in: 被替换字符串。

String_out: 要替换字符串。

SQL> select replace('Informaix中国公司','Informaix','IBM Informix')

2 IBM数据库 from dual;

IBM数据库

--------------------

IBM Informix中国公司

16、SOUNDEX( <c> )

返回一个与给定的字符串读音相同的字符串（不管拼写是否一样）。

SELECT DPL_NAME FROM DENIED_PARTIES_LIST WHERE

SOUNDEX(DPL_NAME) = SOUNDEX(‘Saddam Hussain’) ;

DPL_NAME

----------------------------------------------

Al Husseni

Sadda Al Sada.

17、REPLACE (‘string’ [,’string_in’,’string_out’])

String:希望被替换的字符串或变量。

String_in: 被替换字符串。

String_out: 要替换字符串。

SELECT REPLACE (‘Oracle’, ‘Or’, ‘Mir’) “Example “ FROM DUAL;

Example

-------

Miracle

18、TRIM（ [<leading>] <trailing> FROM <trim_char> ）

TRIM可以使你对给定的字符串进行裁剪（前面，后面或前后）。

如果指定 LEADING, Oracle 从trim_char 中裁剪掉前面的字符；

如果指定TRAILING, Oracle 从trim_char 中裁剪掉尾面的字符；

如果指定两个都指定或一个都没有给出，Oracle从trim_char 中裁剪掉前面及尾面的字符；

如果不指定 trim_character, 缺省为空格符；

如果只指定trim_source, Oracle Oracle从trim_char 中裁剪掉前面及尾面的字符。

例子：将下面字符串中的前面和后面的‘0‘字符都去掉：

SELECT TRIM (0 FROM 0009872348900) "TRIM Example" FROM DUAL;

TRIM example

--------------------------------

98723489

4.2、单记录数字函数

函数	说明
Value1 + value2	加
Value1 - value2	减
Value1 * value2	乘
Value1 / value2	除
ABS(value)	绝对值
CEIL(value)	大于或等于value的最小整数
COS(value)	余弦
COSH(value)	反余弦
EXP(value)	e的value次幂
FLOOR(value)	小于或等于value的最大整数
LN(value)	value的自然对数
LOG(value)	value的以10为底的对数
MOD(value,divisor)	求模
NVL(value,substitute)	value为空时以substitute代替
POWER(value,exponent)	value的exponent次幂
ROUND(value,precision)	按precision 精度4舍5入
SIGN(value)	value为正返回1;为负返回-1;为0返回 0.
SIN(value)	余弦
SINH(value)	反余弦
SQRT(value)	value 的平方根
TAN(value)	正切
TANH(value)	反正切
TRUNC(value,按precision)	按照precision 截取value
VSIZE(value)	返回value在ORACLE的存储空间大小

1、ABS( <n> )

返回指定值的绝对值。如：

SQL> select abs(100),abs(-100) from dual;

ABS(100) ABS(-100)

---------- ----------

100 100

2、ACOS( <n> )

给出反余弦的值。如：

SQL> Select acos(-1) acos from dual;

ACOS

----------

3.14159265

3、ASIN( <n> )

给出反正弦的值。如：

SQL> select asin(-1) "arc sine" from dual;

arc sine

----------

-1.5707963

4、ATAN （ <N> ）

返回一个数字的反正切值。如：

SQL> select atan(-1) "arc tangent" from dual;

arc tangent

-----------

-.78539816

5、CEIL( <n> )

返回大于或等于给出数字的最小整数。如：

SQL> select ceil(3.14159) from dual;

CEIL(3.14159)

-------------

6、COS ( <n> )

返回一个数字余弦值。如：

SQL> select cos(-3.1415926) from dual;

COS(-3.1415926)

---------------

-1

7、COSH ( <n> )

返回一个数字双曲余弦值。如：

SQL> select cosh(20) cosh from dual;

COSH

----------

242582598

8、EXP ( <n> )

返回一个数字 e 的 n 次方的值。如：

SQL> select exp(2),exp(1) from dual;

EXP(2) EXP(1)

---------- ----------

7.3890561 2.71828183

9、FLOOR ( <n> )

对给定的数字取整数，如：

SQL> select floor(123.45),floor(45.56) from dual;

FLOOR(123.45) FLOOR(45.56)

------------- ------------

10、LN( <n> )

返回一个数字的对数值，n 是大于 0 的数字，如：

SQL> select ln(1),ln(2),ln(3) from dual;

LN(1) LN(2) LN(3)

---------- ---------- ----------

0 .693147181 1.09861229

11、LOG( <n1>, <n2> )

返回一个以n1为底的n2的对数，n1不是0或1的正数。如：

SQL> select log(2,1),log(2,2) from dual;

LOG(2,1) LOG(2,2)

---------- ----------

12、MOD( <n1>,<n2> )

SQL> Select mod(10,3), mod(10,2), mod(10,4) from dual;

MOD(10,3) MOD(10,2) MOD(10,4)

---------- ---------- ----------

1 0 2

13、POWER ( <n1>, <n2> )

返回 n1 的 n2 次方值，如：

SQL> select power(2,10),power(3,3) from dual;

POWER(2,10) POWER(3,3)

----------- ----------

14、ROUND(value,precision)

按照指定的精度进行舍入；

select round(55.5),round(-55.5),trunc(55.5),trunc(-55.5)

from dual;

round(55.5) round(-55.5) trunc(55.5) trunc(-55.5)

----------- ------------ ----------- -------------

56 -56 55 -55

15、SIGN(<n>)

取数字n 的符号，大于0 返回 1；小于0 返回-1；等于0 返回0。如：

例：

select sign(123), sign(-100),sign(0) from dual;

sign(123) sign(-100) sign(0)

---------- ---------- ----------

1 -1 0

16、SIN ( <n> )

返回一个数字的正弦值。如：

SQL> select sin(1.57079) from dual;

SIN(1.57079)

------------

17、SINH( <n> )

返回双曲余弦的值，如：

SQL> select sin(20),sinh(20) from dual;

SIN(20) SINH(20)

---------- ----------

.912945251 242582598

18、SQRT( <n> )

返回数字 n 的根，如：

SQL> select sqrt(64),sqrt(10) from dual;

SQRT(64) SQRT(10)

---------- ----------

8 3.16227766

19、TAN( <n> )

返回数字 n的正切值，如：

SQL> select tan(20),tan(10) from dual;

TAN(20) TAN(10)

---------- ----------

2.23716094 .648360827

20、TANH( <n> )

返回数字 n的双曲正切值，如：

SQL> select tanh(20),tan(20) from dual;

TANH(20) TAN(20)

---------- ----------

1 2.23716094

21、TRUNC(value,precision)

按照指定的截取一个数。如：

SQL> SELECT TRUNC (124.16666, -2) trunc1, trunc(124.16666,2) from dual;

TRUNC1 TRUNC(124.16666,2)

---------- ------------------

124.16

4.3、单记录日期函数

Oracle 用到的日期函是：

函数	描述
ADD_MONTH	在日期date上增加count个月
GREATEST(date1,date2,. . .)	从日期列表中选出最晚的日期
LAST_DAY( date )	返回日期date 所在月的最后一天
LEAST( date1, date2, . . .)	从日期列表中选出最早的日期
MONTHS_BETWEEN(date2,date1)	给出 Date2 - date1 的月数(可以是小数)
NEXT_DAY( date,’day’)	给出日期date之后下一天的日期，这里的day为星期，如： MONDAY,Tuesday等。
NEW_TIME(date,’this’,’other’)	给出在this 时区=Other时区的日期和时间
ROUND(date,’format’)	未指定format时，如果日期中的时间在中午之前，则将日期中的时间截断为12 A.M.(午夜，一天的开始),否则进到第二天。时间截断为12 A.M.(午夜，一天的开始),否则进到第二天。
TRUNC(date,’format’)	未指定format时，将日期截为12 A.M.( 午夜，一天的开始).

1、ADD_MONTHS( <d>,<I> )

增加月份和减去月份，如：

SQL> select to_char( add_months(to_date('199712','yyyymm'), 1),'yyyymm') add_month

2 from dual;

ADD_MO

------

199801

SQL> select to_char(add_months(to_date('199712','yyyymm'), -1 ),'yyyymm') add_mo

2 from dual;

ADD_MO

------

199711

2、LAST_DAY( date )

返回日期date 所在月的最后一天，如：

SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd')

2 from dual;

TO_CHAR(SY TO_CHAR((S

---------- ----------

2001.05.18 2001.05.19

3、MONTHS_BETWEEN(date2,date1)

给出 Date2 - date1 的月数(可以是小数)；

SQL> select months_between('19-12月-1999','19-3月-2000') mon_betw from dual;

MON_BETW

----------

-3

SQL> select months_between(to_date('2000.05.20','yyyy.mm.dd'),

2 to_date('2005.05.20','yyyy.mm.dd') ) mon_bet from dual;

MON_BET

----------

-60

4、NEW_TIME(date,’this’,’other’)

给出在this 时区=Other时区的日期和时间

This和other 是时区，它们可以是下面的值：

时区缩写	代表的时区
AST/ADT	大西洋标准/日期时间
BST/BDT	白令海标准/日期时间
CST/CDT	中部标准/日期时间
GMT	格林威治时间
HST/HDT	阿拉斯加-夏威夷标准/日期时间
MST/MDT	山区标准/日期时间
NST	新大陆标准时间
PST/PDT	太平洋标准/日期时间
YST/YDT	Yukon标准/日期时间

SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,

2 to_char(new_time(sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles

3 from dual;

BJ_TIME LOS_ANGLES

------------------- -------------------

2001.05.19 06:25:25 2001.05.19 13:25:25

5、NEXT_DAY( date,’day’)

给出日期date和星期x之后计算下一星期x的日期，这里的day为星期，如： MONDAY,Tuesday等。但在中文环境下，要写成’星期x’这样的格式，如：

例：比如今天是5月18日星期五，计算下一个星期五是几号：

SQL> select next_day('18-5月-2001','星期五') nxt_day from dual;

NXT_DAY

----------

25-5月 -01

6、SYSDATE

用来得到系统的当前日期，如：

SQL> select to_char(sysdate,'dd-mon-yyyy day') from dual;

TO_CHAR(SYSDATE,'DD

-------------------

18-5月 -2001 星期五

7、TRUNC(<date>,[,<fmt>] )

按照给出的 fmt 的要求将日期截断。如果 fmt=’mi’ 则表示截断掉秒保留至分。如：

SQL> select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh,

2 to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual;

HH HHMM

------------------- -------------------

2001.05.18 22:00:00 2001.05.18 22:27:00

45、单记录转换函数

函数	描述
CHARTOROWID	将字符转换到 rowid类型
CONVERT	转换一个字符节到另外一个字符节
HEXTORAW	转换十六进制到raw 类型
RAWTOHEX	转换raw 到十六进制
ROWIDTOCHAR	转换 ROWID到字符
TO_CHAR	转换日期格式到字符串
TO_DATE	按照指定的格式将字符串转换到日期型
TO_MULTIBYTE	把单字节字符转换到多字节
TO_NUMBER	将数字字串转换到数字
TO_SINGLE_BYTE	转换多字节到单字节

1、CHARTOROWID(<c>)

将字符数据类型转换为ROWID类型，如：

1* select rowid,rowidtochar(rowid),ename from scott.emp

SQL> /

ROWID ROWIDTOCHAR(ROWID) ENAME

----------------------------------- ---------------------------------------- ----------

AAAFXDAABAAAHVaAAA AAAFXDAABAAAHVaAAA SMITH

AAAFXDAABAAAHVaAAB AAAFXDAABAAAHVaAAB ALLEN

AAAFXDAABAAAHVaAAC AAAFXDAABAAAHVaAAC WARD

2、CONVERT( <c>,<dset>[,<sset>] )

将源字符串sset从一个语言字符集转换到另一个目的dset字符集。

SELECT CONVERT (‘strutz’, ‘ WE8HP’, ‘ F7DEC ‘) “Conversion”

FROM DUAL;

Conversion

---------------

Strutz.

3、HEXTORAW( <x> )

将一个十六进制构成的字符串转化为二进制。如：

Insert into printers( printer_nbr,manufacturer,model,init_string)

Values ( 12,’HP’,’Laserjet’,”HEXTORAW(‘1B45’));

RAWTOHEX( <x> )

将一个二进制构成的字符串转化为十六进制。如：

select rawtohex ( init_string) hext from printers

where model=LaserJet’ and manufacturer=’HP’;

hext

-----------

1B45

4、ROWIDTOCHAR( <x> )

将ROWID数据类型转换为字符类型，见 CHARTOROWID。

5、TO_CHAR(date,’format’)

根据format 重新格式日期date的格式。如：

SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY

-------------------

2001/05/18 23:05:36

日期格式比较多，详细内容请参考原版资料。下面给出常用的日期格式代码：

日期格式代码表

日期代码	格式说明	例子
AD 或 BC	AD=Anno Domini公元,BC=Before Christ公元前。不带点的公元或公元前	‘YYYY AD’=1999 AD
A.D. 或B.C.	带点的公元或公元前	‘YYYY A.D.’=1999 A.D.
AM或PM	AM= ante meridiem 上午，PM=post meridiem下午。不带点的上午或下午	‘HH12AM’=09AM
A.M.或P.M.	带点的上午或下午	‘HH12A.M.’=09A.M.
DY	星期几的缩写	Mon,Tue,...
DAY	星期几的全拼	Monday,Tuesday,...
D	一周的星期几，星期天=1，星期六=7	1,2,3,4,5,6,7
DD	一月的第几天，1à31	1，2，... 31
DDD	一年的第几天，1à366	1,2,3,...366
J	公元前的第几天（从公元前4712起 ?）	2451514,2451515,...
W	一个月的第几周，1à 5	1,2,3,4,5
WW,IW	一年的第几周，一年的ISO的第几周	1,2,3,4,... 52
MM	两为数的月	01,02,03,...12
MON	月份的缩写	Jan,Feb,Mar ,...Dec
MONTH	月份的全拼	January,February,...
RM	罗马数字的月份，I à XII	I,II,III,IV,...XII
YYYY,YYY,YY,Y	四位数的年，三位数的年	1999,999,99,9
YEAR	年的全拼	Nineteen Ninety-nine
SYYYY	如果是公元前(BC),年份前负号	-1250
RR	当前年份的后两位数字	01代表2001年
HH,HH12	12小时制，1à12	1,2,3,...12
HH24	24小时制，0à23	0,1,2,3,...23
MI	一小时中的第几分，0à59	0,1,2,3...59
SS	一分中的第几秒，0à59	0,1,2,3,...59
SSSSS	一天中的第几秒，0à86399	0,1,2,3,...86399
../-;:	标点符号表示法	文字显示
‘text’	引号表示法	文字显示

6、TO_DATE(string,’format’)

将一和字串转换为ORACLE的日期。如：

Insert into demo(demo_key,date_col) Values(1 , to_date(’04-Oct-1999’, ‘DD-Mon-yyyy’) );

7、TO_MULTI_BYTE(<c>)

将字符串中的单字节字符转换为多字节字符, 如：

8、TO_NUMBER(<c>)

将给出的字符转换为数字，如：

SELECT TO_NUMBER (‘1947’) “FISCAL_YEAR” FROM DUAL;

FISCAL_YEAR

-----------

1947

9、TO_MULTI_BYTE(<c>)及TO_SINGLE_BYTE

将单字节转换为多字节或从多字节转换为单字节。

4.6、其它的单记录函数

1、BFILENAME( <dir>, <file> )

指定一个外部二进制文件。如：

INSERT INTO file_tbl VALUES (BFILENAME (’lob_dir1’, ’image1.gif’));

2、CONVERT (‘x’,’desc_set’ [, ‘source_set’])

将x 字段或变量的源 source 转换为 desc，如：

select sid,serial#,username,

DECODE(command,

0,’None’,

2,’Insert’,

3,’Select’,

6,’Update’,

7,’Delete’,

8,’Drop’,

‘Other’) cmd

from v$session where type != ‘BACKGROUND’;

关于DECODE 在优化方面的内容在《Oracle8i/9i 高级数据库管理》中查阅。

3、DUMP( s,[,fmt [, start [, length ] ] ] )

DUMP 函数以fmt 指定的内部数字格式返回一个VARCHAR2类型的值。如：

SQL> col global_name for a30

SQL> col DUMP_STRING for a50

SQL> set lin 200

SQL> select global_name,dump(global_name,1017,8,5) dump_string

2 from global_name;

GLOBAL_NAME DUMP_STRING

------------------------------------- ----------------------------------------------------------------

ORA816.US.ORACLE.COM Typ=1 Len=20 CharacterSet=ZHS16GBK: U,S,.,O,R

4、EMPTY_BLOB() 和 EMPTY_CLOB() 函数

这两个函数都是用来对大数据类型字段进行初始化操作的函数，一般有：

BLOB数据类型 --- EMPTY_BLOB()

CLOB数据类型 --- EMPTY_CLOB()

NCLOB数据类型 --- EMPTY_CLOB()

Insert into proposal

( proposal_id, recipient_name,proposal_name,short_description,

proposal_text,budget , cover_letter )

values(2,’BRAD OHMONT’,’REBUILD FENCE’,NULL,

EMPTY_CLOB(),EMPTY_BLOB(),

BFILENAME(‘proposal_dir’,’P2.DOC’) );

5、GREATEST( <exp_list> )

返回一组表达式中的最大值，即比较字符的编码大小。如：

SQL> select greatest('AA','AB','AC') from dual;

SQL> select greatest('啊','安','天') from dual;

天

即 “天”的编码比“安”和“啊”都大。

6、LEAST ( <exp_list> )

返回一组表达式中的最小值，即比较字符的编码大小。如：

SQL> select least('啊','安','天') from dual;

啊

7、UID 函数

返回标识当前用户的唯一整数，如：

SQL> show user

USER 为"SYSTEM"

SQL> l

1* select username,user_id from dba_users where user_id=UID

SQL> /

USERNAME USER_ID

------------------------------ ----------

SYSTEM 5

8、USER 函数

返回当前用户的名字，如：

SQL> select user from dual;

USER

------------------------------

SYSTEM

9、USERENV( <opt> )

返回当前用户环境的信息，opt 选项可以是：

ENTRYID 返回当前用户会话的入口ID

SESSIONID 返回当前用户会话的ID

TERMINAL 返回当前系统会话的操作系统标识

OSDBA 如果当前用户有DBA权限，则返回 TRUE

LABLE 返回当前用户会话的标号

LANGUAGE 返回当前用户的语言和区域

CLIENT_INFO 为当前用户会话返回 client-info 域的值，这个值由

dbms_application_info,set_client_info 过程来设置。

LANG 以ISO 的三个字符表示当前用户会话所使用的语言。

VSIZE 返回表达式的字节大小。

10、ISDBA 函数

查看当前用户是否是 DBA ，当SYSDBA 角色有效是才返回 TRUE，如：

SQL> show user

USER is "SYSTEM"

SQL> select userenv('ISDBA') from dual;

USEREN

------

FALSE

SQL> connect sys/sys@ora816

Connected.

SQL> select userenv('ISDBA') from dual;

USEREN

------

FALSE

SQL> connect internal

Connected.

SQL> select userenv('ISDBA') from dual;

USEREN

------

TRUE

11、SESSIONID函数

返回审计会话标识，如：

select userenv(‘SESSIONID’) aud_id from dual;

aud_id

---------

47343

12、ENTRYID 函数

返回审计会话入口标识，当initsid.ora 文件中的 audit_trail=TRUE 时可以用，如：

select userenv(‘ENTRYID’) from dual;

USERENV(‘ENTRYID’)

-------------

835641

13、INSTANCE函数

返回当前INSTANCE 的标识，如：

SQL> select userenv('INSTANCE') from dual;

USERENV('INSTANCE')

-------------------

14、LANGUAGE函数

返回当前环境的语言，如：

SQL> select userenv('LANGUAGE') from dual;

USERENV('LANGUAGE')

----------------------------------------------------

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

15、LANG函数

返回当前环境的语言的缩写，如：

SQL> l

1* select userenv('LANG') from dual

SQL> /

USERENV('LANG')

----------------------------------------------------

ZHS

16、TERMINAL函数

返回用户的终端或机器的标识，如：

SQL> select userenv('TERMINAL') from dual;

USERENV('TERMINA

----------------

ZHAOYUANJIE

17、VSIZE( <x> )

返回 x 的大小（字节）数，如：

SQL> select vsize(user),user from dual;

VSIZE(USER) USER

----------- -----------------------------

6 SYSTEM

4.7、多记录组函数

1、AVG( [ { DISTINCT | ALL}] )

求平均值，ALL表示对所有求平均值，DISTINCT 只对不同的求平均值，相同只取一个。

SQL> l

1* select avg(sal) from emp

SQL> /

AVG(SAL)

----------

2073.21429

2、MAX( [ { DISTINCT | ALL}] )

求最大值，ALL表示对所有求最大值，DISTINCT 只对不同的求最大值，相同只取一个。

SQL> select max(sal) from emp;

MAX(SAL)

----------

5000

3、MIN( [ { DISTINCT | ALL}] )

求最小值，ALL表示对所有求最小值，DISTINCT 只对不同的求最小值，相同只取一个。

SQL> select min(sal) from emp;

MIN(SAL)

----------

800

4、STDDEV( [ { DISTINCT | ALL}] )

求标准差，ALL表示对所有求标准差，DISTINCT 只对不同的求标准差，相同只取一个。

SQL> select stddev(sal) from emp;

STDDEV(SAL)

-----------------

1182.50322

5、VARIANCE( [ { DISTINCT | ALL}] )

求协方差，ALL表示对所有求协方差，DISTINCT 只对不同的求协方差，相同只取一个。

SQL> select variance(sal) from emp;

VARIANCE(SAL)

------------------------

1398313.87

posted @ 2016-05-17 15:15 宁静.致远阅读(923) 评论(0) 编辑收藏举报

刷新页面返回顶部

宁静.致远

倚楼听风雨，淡看江湖路。

oracle函数详解-转

公告