Oracle SQL 基础语句和基本函数
2015-03-26 11:13 义超 阅读(3164) 评论(0) 编辑 收藏 举报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;
伪列及伪表
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
1.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》
1.4、SQL中的单记录函数
许多资料（包括Oracle 的资料）把Oracle的SQL语句中用到的函数分为单值函数和多值函数，单值函数又分为字符函数和数字函数。下面分别介绍它们。
4.1、单记录字符函数
函 数 说 明
ASCII 返回对应字符的十进制值
CHR 给出十进制返回字符
CONCAT 拼接两个字符串，与 || 相同
INITCAT 将字符串的第一个字母变为大写
INSTR 找出某个字符串的位置 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
----------
9
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)
-------------
4
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)
------------- ------------
45
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)
---------- ----------
1
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)
----------- ----------
27
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)
------------
1
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
4.4、单记录转换函数
函 数 描 述
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.5、其它的单记录函数
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;
GR
--
AC
SQL> select greatest('啊','安','天') from dual;
GR
--
天
即 “天”的编码比“安”和“啊”都大。
6、LEAST ( <exp_list> )
返回一组表达式中的最小值，即比较字符的编码大小。如：
SQL> select least('啊','安','天') from dual;
LE
--
啊
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')
-------------------
1
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.6、多记录组函数
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)
------------------------
2、SQL 函数效果
2.1、oracle数据库sql字符串分隔split
select dbms_lob.substr(regexp_substr('123,456,7890', '[^,]+', 1, 1)) arry1,
dbms_lob.substr(regexp_substr('123,456,7890', '[^,]+', 1, 2)) arry2,
dbms_lob.substr(regexp_substr('123,456,7890', '[^,]+', 1, 3)) arry3
from dual
运行结果：
ARRY1 ARRY2 ARRY3
1 123 456 7890
