PL/SQL常用函数

名称                                   类型       说明

 

NUMBER                          数字型    能存放整数值和实数值，并且可以定义精度和取值范围

BINARY_INTEGER          数字型    可存储带符号整数，为整数计算优化性能

DEC                                  数字型    NUMBER的子类型，小数

DOUBLE PRECISION     数字型    NUMBER的子类型，高精度实数

INTEGER                         数字型    NUMBER的子类型，整数

INT                                    数字型    NUMBER的子类型，整数

NUMERIC                        数字型    NUMBER的子类型，与NUMBER等价

REAL                                数字型    NUMBER的子类型，与NUMBER等价

SMALLINT                       数字型    NUMBER的子类型，取值范围比INTEGER小

VARCHAR2                       字符型  存放可变长字符串，有最大长度

CHAR                                字符型  定长字符串

LONG                                字符型  变长字符串，最大长度可达32,767

DATE                                日期型  以数据库相同的格式存放日期值

BOOLEAN                        布尔型  TRUE OR FALSE

ROWID                             ROWID 存放数据库的行号

 

 

常用函数
　　 1)处理字符的函数
　　 ||
　　 CONCAT ---并置运算符。
　　 格式∶CONCAT(STRING1, STRING2)
　　 例：’ABC’|| ’DE’=’ABCDE’
　　 CONCAT(‘ABC’,’DE’) =’ABCDE’
　　
　　 ASCII---返回字符的ASCII码。
　　 例：ASCII(‘A’) = 65
　　
　　 CHR---返回指定ASCII码的字符。
　　 例：CHR(65) = ‘A’
　　
　　 INSTR---搜索子串位置
　　格式∶INSTR(STRING , SET[ , 开始位置[ , 出现次数]])
　　 例∶ INSTR (‘this is a test’ , ‘i’ , 1,2)=6
　　
　　 INITCAP---将字符串每个单词首字母均变为大写
　　例: INITCAP(‘this is a test’)=’ This Is A Test’’
　　
　　 LENGTH----计算串长
　　 格式∶ LENGTH(string)
　　
　　 RPAD,LPAD---右填充、左填充。默认为填充空格。
　　 格式: RPAD(字符串 , 字符个数 , 填充字符)
　　例: RPAD(‘ABC’ , 6 , ’H’)=’ABCHHH’
　　
　　 LTRIM,RTRIM-----左右截断。默认为删除空格。
　　格式∶ LTRIM(STRING[,’SET’])
　　例∶ LTRIM(‘***tes*t***’ , ’*’)=’ tes*t***’
　　
　　 LOWER----将字符串转换为小写
　　 格式∶LOWER(string)
　　
　　UPPER---将字符串转换为大写
　　 格式∶UPPER(string)
　　
　　SUBSTR----提取子串。START为正数时从左开始 、为负数时从右开始
　　格式∶ SUBSTR(STRING , START [ , COUNT])
　　例∶ SUBSTR(‘WORDSTAR’ , 2 , 3)=’ ORD’
　　
　　 REPLACE---搜索指定字符串并替换
　　 格式∶REPLACE(string , substring , replace_string)
　　 例∶ REPLACE(‘this is a test’ , ‘this’ , ‘that an’)=’that an is a test’
　　
　　TRIM---删除字符串前缀或尾随字符
　　格式∶TRIM( [LEADING | TRAILING ｜BOTH] [ trimchar FROM ] string)
　　 LEADING---删除前缀字符
　　 TRAILING---删除后缀字符
　　 BOTH---前后缀字符均删除(默认方式)
　　 Trimchar---指定删除的字符
　　
　　注：INSTR,LENGTH,SUBSTR加Ｂ时针对字节．
　　2)处理数字的函数
　　LEAST---返回参数列表中的最小值。返回参数类型以第一参数为准
　　格式∶LEAST(value,value,value,value,value,….)value为数字或字符串
　　例∶ LEAST(1,2,5,-10,9)= -10
　　
　　SIGN---返回参数的符号位,负数--- -1,0----0,正数---1
　　格式∶SIGN(value)
　　
　　 CELL---返回大于等于特定值的最小整数
　　 格式∶CELL(value)
　　 例∶ CELL(-10,9)= -10
　　3）处理日期
　　 SYSDATE---系统时间。精确至秒
　　
　　 ADD_MONTH—加减月份。numvalue为负数时减去相应月份
　　 格式: ADD_MONTH (data1,numvalue)
　　
　　MONTHS_BETWEEN---返回两日期之间的月数,当data1
　　 格式: MONTHS_BETWEEN(data1,data2)
　　
　　 LAST_DAY---返回指定日期的最后一天。
　　 格式∶LAST_DAY(date)
　　
　　 TRUNC---将日期按照format格式截短，缺省为DD(不是四舍五入)
　　　　格式：TRUNC（date, format）
　　例∶
　　 TRUNC (to_date(‘2002-11-07’,’yyyy-mm-dd’),’MM’)= 2002-11-01
　　
　　　 4）分组函数
　　　　　　　返回基于多个行的单一结果．
　　　　　　　常用函数：
　　 AVG---求平均值
　　 COUNT---返回查询的行数
　　 MAX---返回查询列的最大值
　　 MIN---返回查询列的最小值
　　 SUM---返回查询列的总和
　　 MAX, MIN常与GROUP BY配套使用
　　5）转换函数
　　 TO_DATE---转换字符串为日期型
　　格式∶ TO_DATE(STRING[,’FORMAT’])
　　
　　TO_CHAR---转换日期型或数值型为字符串。最重要的函数之一．其FORMAT格式多种多样
　　格式∶TO_CHAR(DATE [,’FORMAT’])
　　FORMAT---具体格式参考ORACLE8i DBA 宝典P835数字格式元素
　　P836 日期格式元素
　　
　　 TO_NUMBER---转换字符串为数字
　　 格式∶TO_NUMBER(string [ , format])
　　FORMAT---具体格式参考ORACLE8i DBA 宝典P835数字格式元素
　　
　　
　　6)其他
　　 DECODE---IF语句的另一形式。将输入数值与参数列表比较，返回对应值。应用于将表的行转换成列以及IF语句无法应用的场合。当与SIGN联合使用时功能扩展,可以判断大于小于的情况.
　　格式: DECODE(input_value , value1 , result1 , value2 , result2 , ….defult_result)
　　例∶ DECODE(VALUE,1,100,2,300,500)
　　当VALUE=1时返回100
　　当VALUE=2时返回300
　　否则返回500
　　
　　DECODE(SIGN(VALUES-100), -1,-10,1,10,0)
　　当VALUE<100时返回-10
　　当VALUE>100时返回10
　　当VALUE=100时返回0
　　
　　 SELECT SUM(DECODE(EST_INT_KEY,77771,1,0)) A,
　　SUM(DECODE(EST_INT_KEY,77772,1,0)) B,
　　SUM(DECODE(EST_INT_KEY,77773,1,0)) C
　　FROM PMS_BLK
　　
　　 NVL---空值置换
　　 格式: NVL(value,替换值)
　　例: NVL(value,’nullvalue’)当value为NULL值时返回nullvalue否则返回value的值
　　
　　VSIZE---以字节为单位返回数据类型尺寸
　　格式∶VSIZE(数据类型)
　　例∶ VSIZE(SYSDATE)=8
　　
　　USER---得到当前用户名
　　
　　ROWNUM--- oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数。不能以任何基表的名称作为前缀。可以用在限制返回记录条数的地方不受ORDER BY的影响.
　　例∶ select rownum,month,sell from sale where rownum=1
　　 或select rownum,month,sell from sale where rownum<2(返回第一条记录)
　　 返回rownum在4—10之间的记录
　　select rownum,month,sell from sale where rownum<10
　　 minus
　　select rownum,month,sell from sale where rownum<5;
　　
　　 以下均错误:
　　select rownum,month,sell from sale where rownum=2(1以上)
　　select rownum,month,sell from sale where rownum>2
　　
　　ROWID---数据行在物理磁盘上的物理地址，Oracle通过ROWID来定位数据的具体位置，这是存取表中数据的最快的方法
　　
　　RETURN---用于函数和过程中.遇见RETURN程序立刻中断,返回,不再执行剩余部分.
　　SQLCODE---返回ORACLE错误号。
　　SQLERRM---返回ORACLE错误信息。

 

在SQLPLUS下，实现中-英字符集转换
alter session set nls_language='AMERICAN';
alter session set nls_language='SIMPLIFIED CHINESE';
主要知识点：
一、有关表的操作
1)建表
create table test as select * from dept; --从已知表复制数据和结构
create table test as select * from dept where 1=2; --从已知表复制结构但不包括数据
2)插入数据：
insert into test select * from dept;

二、运算符
算术运算符：+ - * / 可以在select 语句中使用
连接运算符：|| select deptno|| dname from dept;
比较运算符：> >= = != < <= like between is null in
逻辑运算符：not and or
集合运算符： intersect ，union， union all， minus
要求：对应集合的列数和数据类型相同
查询中不能包含long 列
列的标签是第一个集合的标签
使用order by时，必须使用位置序号，不能使用列名
例：集合运算符的使用：
intersect ，union， union all， minus
select * from emp intersect select * from emp where deptno=10 ;
select * from emp minus select * from emp where deptno=10;
select * from emp where deptno=10 union select * from emp where deptno in (10,20); --不包括重复行
select * from emp where deptno=10 union all select * from emp where deptno in (10,20); --包括重复行

三，常用 ORACLE 函数
sysdate为系统日期 dual为虚表
一）日期函数[重点掌握前四个日期函数]
1,add_months[返回日期加(减)指定月份后(前)的日期]
select sysdate S1,add_months(sysdate,10) S2,
add_months(sysdate,5) S3 from dual;
2,last_day [返回该月最后一天的日期]
select last_day(sysdate) from dual;
3,months_between[返回日期之间的月份数]
select sysdate S1, months_between('1-4月-04',sysdate) S2,
months_between('1-4月-04','1-2月-04') S3 from dual
4,next_day(d,day): 返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日
select sysdate S1,next_day(sysdate,1) S2,
next_day(sysdate,'星期日') S3 FROM DUAL
5,round[舍入到最接近的日期](day:舍入到最接近的星期日)
select sysdate S1,
round(sysdate) S2 ,
round(sysdate,'year') YEAR,
round(sysdate,'month') MONTH ,
round(sysdate,'day') DAY from dual
6,trunc[截断到最接近的日期]
select sysdate S1,
trunc(sysdate) S2,
trunc(sysdate,'year') YEAR,
trunc(sysdate,'month') MONTH ,
trunc(sysdate,'day') DAY from dual
7,返回日期列表中最晚日期
select greatest('01-1月-04','04-1月-04','10-2月-04') from dual

二）字符函数(可用于字面字符或数据库列)
1，字符串截取
select substr('abcdef',1,3) from dual
2,查找子串位置
select instr('abcfdgfdhd','fd') from dual
3,字符串连接
select 'HELLO'||'hello world' from dual;
4, 1)去掉字符串中的空格
select ltrim(' abc') s1,
rtrim('zhang ') s2,
trim(' zhang ') s3 from dual
2)去掉前导和后缀
select trim(leading 9 from 9998767999) s1,
trim(trailing 9 from 9998767999) s2,
trim(9 from 9998767999) s3 from dual;
5,返回字符串首字母的Ascii值
select ascii('a') from dual
6,返回ascii值对应的字母
select chr(97) from dual
7,计算字符串长度
select length('abcdef') from dual
8,initcap（首字母变大写） ,lower(变小写),upper(变大写)
select lower('ABC') s1,
upper('def') s2,
initcap('efg') s3 from dual;
9,Replace
select replace('abc','b','xy') from dual;
10,translate
select translate('abc','b','xx') from dual; -- x是1位
11,lpad [左添充] rpad [右填充](用于控制输出格式)
select lpad('func',15,'=') s1, rpad('func',15,'-') s2 from dual;
select lpad(dname,14,'=') from dept;
12, decode[实现if ..then 逻辑]
select deptno,decode(deptno,10,'1',20,'2',30,'3','其他') from dept;

三）数字函数
1，取整函数（ceil 向上取整,floor 向下取整）
select ceil(66.6) N1,floor(66.6) N2 from dual;
2, 取幂(power) 和 求平方根(sqrt)
select power(3,2) N1,sqrt(9) N2 from dual;
3,求余
select mod(9,5) from dual;
4,返回固定小数位数 (round:四舍五入，trunc:直接截断)
select round(66.667,2) N1,trunc(66.667,2) N2 from dual;
5,返回值的符号(正数返回为1,负数为-1)
select sign(-32),sign(293) from dual;

 

1.ASCII
返回与指定的字符对应的十进制数;
SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;
A         A      ZERO     SPACE
--------- --------- --------- ---------
65        97        48        32

---

2.CHR
给出整数,返回对应的字符;
SQL> select chr(54740) zhao,chr(65) chr65 from dual;

ZH C
-- -
赵 A

---

3.CONCAT
连接两个字符串;
SQL> select concat('010-','88888888')||'转23'  吴龙武电话 from dual;

吴龙武电话
----------------
010-88888888转23

---

4.INITCAP
返回字符串并将字符串的第一个字母变为大写;
SQL> select initcap('smith') upp from dual;

UPP
-----
Smith

SQL> select initcap('吴abdc') from dual;

INITCA
------
吴Abdc

小注释:CAP就是帽子的意思,initcap意思是"初始化帽子". :)
对括号里的字符串,先略之前的汉字,然后对第一个字母变成大写,其他字母变成小写

---

5.INSTR(C1,C2,I,J)
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1    被搜索的字符串
C2    希望搜索的字符串
I     搜索的开始位置,默认为1
J     出现的位置,默认为1
SQL> select instr('oracle traning','ra',1,2) instring from dual;

INSTRING
---------
9

---

6.LENGTH
返回字符串的长度;
SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from .nchar_tst;

NAME   LENGTH(NAME) ADDR             LENGTH(ADDR)       SAL LENGTH(TO_CHAR(SAL))
------ ------------ ---------------- ------------ --------- --------------------
吴龙武            3 北京市海锭区                6   9999.99                    7

---

7.LOWER
返回字符串,并将所有的字符小写
SQL> select lower('AaBbCcDd')AaBbCcDd from dual;

AABBCCDD
--------
aabbccdd

---

8.UPPER
返回字符串,并将所有的字符大写
SQL> select upper('AaBbCcDd') upper from dual;

UPPER
--------
AABBCCDD

---

9.RPAD和LPAD(粘贴字符)
RPAD  在列的右边粘贴字符
LPAD  在列的左边粘贴字符
SQL> select lpad(rpad('gao',10,'*'),17,'*')from dual;

LPAD(RPAD('GAO',1
-----------------
*******gao*******
不够字符则用*来填满

PAD的意思是:加上衬垫

---

10.LTRIM和RTRIM
LTRIM  删除左边出现的字符串
RTRIM  删除右边出现的字符串
SQL> select ltrim(rtrim('   gao qian jing   ',' '),' ') from dual;

LTRIM(RTRIM('
-------------
gao qian jing

---

11.SUBSTR(string,start,count)
取子字符串,从start开始,取count个
SQL> select substr('13088888888',3,8) from dual;

SUBSTR('
--------
08888888

---

12.REPLACE('string','s1','s2')
string   希望被替换的字符或变量 
s1       被替换的字符串
s2       要替换的字符串
SQL> select replace('he love you','he','i') from dual;

REPLACE('HELOVEYOU','HE','I')
------------------------------
i love you

---

13.SOUNDEX
返回一个与给定的字符串读音相同的字符串
SQL> create table table1(xm varchar(8));
SQL> insert into table1 values('weather');
SQL> insert into table1 values('wether');
SQL> insert into table1 values('gao');

SQL> select xm from table1 where soundex(xm)=soundex('weather');

XM
--------
weather
wether

---

14.TRIM('s' from 'string')
LEADING   剪掉前面的字符
TRAILING  剪掉后面的字符
如果不指定,默认为空格符

---

15.ABS
返回指定值的绝对值
SQL> select abs(100),abs(-100) from dual;

ABS(100) ABS(-100)
--------- ---------
100       100

---

16.ACOS
给出反余弦的值
SQL> select acos(-1) from dual;

ACOS(-1)
---------
3.1415927

---

17.ASIN
给出反正弦的值
SQL> select asin(0.5) from dual;

ASIN(0.5)
---------
.52359878

---

18.ATAN
返回一个数字的反正切值
SQL> select atan(1) from dual;

ATAN(1)
---------
.78539816

---

19.CEIL
返回大于或等于给出数字的最小整数
SQL> select ceil(3.1415927) from dual;

CEIL(3.1415927)
---------------
      4

---

20.COS
返回一个给定数字的余弦
SQL> select cos(-3.1415927) from dual;

COS(-3.1415927)
---------------
     -1

---

21.COSH
返回一个数字反余弦值
SQL> select cosh(20) from dual;

COSH(20)
---------
242582598

---

22.EXP
返回一个数字e的n次方根
SQL> select exp(2),exp(1) from dual; 
EXP(2)    EXP(1)
--------- ---------
7.3890561 2.7182818

---

23.FLOOR
对给定的数字取整数
SQL> select floor(2345.67) from dual;

FLOOR(2345.67)
--------------
  2345

---

24.LN
返回一个数字的对数值
SQL> select ln(1),ln(2),ln(2.7182818) from dual;

LN(1)     LN(2) LN(2.7182818)
--------- --------- -------------
0 .69314718     .99999999

---

25.LOG(n1,n2)
返回一个以n1为底n2的对数 
SQL> select log(2,1),log(2,4) from dual;

LOG(2,1)  LOG(2,4)
--------- ---------
0         2

---

26.MOD(n1,n2)
返回一个n1除以n2的余数
SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;

MOD(10,3)  MOD(3,3)  MOD(2,3)
--------- --------- ---------
1         0         2

---

27.POWER
返回n1的n2次方根
SQL> select power(2,10),power(3,3) from dual;

POWER(2,10) POWER(3,3)
----------- ----------
1024         27

---

28.ROUND和TRUNC
按照指定的精度进行舍入
SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;

ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
----------- ------------ ----------- ------------
 56          -55          55          -55

---

29.SIGN
取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
SQL> select sign(123),sign(-100),sign(0) from dual;

SIGN(123) SIGN(-100)   SIGN(0)
--------- ---------- ---------
1         -1         0

---

30.SIN
返回一个数字的正弦值
SQL> select sin(1.57079) from dual;

SIN(1.57079)
------------
   1

---

31.SIGH
返回双曲正弦的值
SQL> select sin(20),sinh(20) from dual; 

SIN(20)  SINH(20)
--------- ---------
.91294525 242582598

---

32.SQRT
返回数字n的平方根
SQL> select sqrt(64),sqrt(10) from dual;

SQRT(64)  SQRT(10)
--------- ---------
8 3.1622777

---

33.TAN
返回数字的正切值
SQL> select tan(20),tan(10) from dual;

TAN(20)   TAN(10)
--------- ---------
2.2371609 .64836083

---

34.TANH
返回数字n的双曲正切值
SQL> select tanh(20),tan(20) from dual;

TANH(20)   TAN(20)
--------- ---------
1 2.2371609

---

35.TRUNC
按照指定的精度截取一个数
SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;

TRUNC1 TRUNC(124.16666,2)
--------- ------------------
100             124.16

---

36.ADD_MONTHS
增加或减去月份
SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;

TO_CHA
------
200002
SQL> select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual;

TO_CHA
------
199910

---

37.LAST_DAY
返回日期的最后一天
SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual;

TO_CHAR(SY TO_CHAR((S
---------- ----------
2004.05.09 2004.05.10
SQL> select last_day(sysdate) from dual;

LAST_DAY(S
----------
31-5月 -04

---

38.MONTHS_BETWEEN(date2,date1)
给出date2-date1的月份
SQL> select months_between('19-12月-1999','19-3月-1999') mon_between from dual;

MON_BETWEEN
-----------
  9
SQL> select months_between(to_date('2000.05.20', 'yyyy.mm.dd'),to_date('2005.05.20', 'yyyy.mm.dd')) mon_betw  from dual;

MON_BETW
---------
-60

---

39.NEW_TIME(date,'this','that')
给出在this时区=other时区的日期和时间
SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time
2  (sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;

BJ_TIME             LOS_ANGLES
------------------- -------------------
2004.05.09 11:05:32 2004.05.09 18:05:32

---

40.NEXT_DAY(date,'day')
给出日期date和星期x之后计算下一个星期的日期
SQL> select next_day('18-5月-2001','星期五') next_day from dual;

NEXT_DAY
----------
25-5月 -01

---

41.SYSDATE
用来得到系统的当前日期
SQL> select to_char(sysdate,'dd-mm-yyyy day') from dual;

TO_CHAR(SYSDATE,'
-----------------
09-05-2004 星期日
trunc(date,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
------------------- -------------------
2004.05.09 11:00:00 2004.05.09 11:17:00

---

42.CHARTOROWID
将字符数据类型转换为ROWID类型
SQL> select chartorowid('AAAGDxAABAAAH9EAAA') from dual;

CHARTOROWID('AAAGD
------------------
AAAGDxAABAAAH9EAAA

---

43.CONVERT(c,dset,sset)
将源字符串 sset从一个语言字符集转换到另一个目的dset字符集
SQL> select convert('strutz','we8hp','f7dec') "conversion" from dual;

conver
------
strutz

---

44.HEXTORAW
将一个十六进制构成的字符串转换为二进制

---

45.RAWTOHEXT
将一个二进制构成的字符串转换为十六进制

---

46.ROWIDTOCHAR
将ROWID数据类型转换为字符类型

SQL> select rowid,rowidtochar(rowid),ename from scott.emp
  2  order by rowid ;

ROWID              ROWIDTOCHAR(ROWID) ENAME
------------------ ------------------ ----------
AAAGDxAABAAAH9EAAA AAAGDxAABAAAH9EAAA SMITH
AAAGDxAABAAAH9EAAB AAAGDxAABAAAH9EAAB ALLEN
AAAGDxAABAAAH9EAAC AAAGDxAABAAAH9EAAC WARD
AAAGDxAABAAAH9EAAD AAAGDxAABAAAH9EAAD JONES
AAAGDxAABAAAH9EAAE AAAGDxAABAAAH9EAAE MARTIN

---

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

TO_CHAR(SYSDATE,'YY
-------------------
2004/05/09 21:14:41

---

48.TO_DATE(string,'format')
将字符串转化为ORACLE中的一个日期

---

49.TO_MULTI_BYTE
将字符串中的单字节字符转化为多字节字符
SQL> select to_multi_byte('吴') from dual;

TO
--
吴

---

50.TO_NUMBER
将给出的字符转换为数字
SQL> select to_number('1999') year from dual;

     YEAR
---------
     1999

---

51.BFILENAME(dir,file)
指定一个外部二进制文件
SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif'));

---

52.decode('x','desc','source')
将x字段或变量的源source转换为desc
SQL> select sid,serial#,username,decode(command,
  2  0,'none',
  3  2,'insert',
  4  3,
  5  'select',
  6  6,'update',
  7  7,'delete',
  8  8,'drop',
  9  'other') cmd  from v$session where type!='background';

      SID   SERIAL# USERNAME                       CMD
--------- --------- ------------------------------ ------
        1         1                                none
        2         1                                none
        3         1                                none
        4         1                                none
        5         1                                none
        6         1                                none
        7      1275                                none
        8      1275                                none
        9        20 GAO                            select
       10        40 GAO                            none

---

53.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 from global_name;

GLOBAL_NAME                    DUMP_STRING
------------------------------ --------------------------------------------------
ORACLE.WORLD                   Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D

---

54.EMPTY_BLOB()和EMPTY_CLOB()
这两个函数都是用来对大数据类型字段进行初始化操作的函数

---

55.GREATEST
返回一组表达式中的最大值,即比较字符的编码大小.
SQL> select greatest('AA','AB','AC') from dual;

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

GR
--
天

---

56.LEAST
返回一组表达式中的最小值 
SQL> select least('啊','安','天') from dual;

LE
--
啊

---

57.UID
返回标识当前用户的唯一整数

SQL> show user
USER 为"WULW"
SQL> select username,user_id from dba_users where user_id=uid;

USERNAME                          USER_ID
------------------------------ ----------
WULW                                   41

---

58.USER
返回当前用户的名字
SQL> select user from  dual;

USER
------------------------------
WULW

---

59.USEREVN
返回当前用户环境的信息,opt可以是:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
ISDBA  查看当前用户是否是DBA如果是则返回true
SQL> select userenv('isdba') from dual;

USEREN
------
FALSE
SQL> select userenv('isdba') from dual;

USEREN
------
TRUE
SESSION
返回会话标志
SQL> select userenv('sessionid') from dual;

USERENV('SESSIONID')
--------------------
                 152
ENTRYID
返回会话入口标志
SQL> select userenv('entryid') from dual;

USERENV('ENTRYID')
------------------
                 0
INSTANCE
返回当前INSTANCE的标志
SQL> select userenv('instance') from dual;

USERENV('INSTANCE')
-------------------
                  1
LANGUAGE
返回当前环境变量
SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
LANG
返回当前环境的语言的缩写
SQL> select userenv('lang') from dual;

USERENV('LANG')
----------------------------------------------------
ZHS
TERMINAL
返回用户的终端或机器的标志
SQL> select userenv('terminal') from dual;

USERENV('TERMINA
----------------
GAO
VSIZE(X)
返回X的大小(字节)数
SQL> select vsize(user),user from dual;

VSIZE(USER) USER
----------- ------------------------------
          6 SYSTEM

---

60.AVG(DISTINCT|ALL)
all表示对所有的值求平均值,distinct只对不同的值求平均值
SQLWKS> create table table3(xm varchar(8),sal number(7,2));
语句已处理。
SQLWKS>  insert into table3 values('gao',1111.11);
SQLWKS>  insert into table3 values('gao',1111.11);
SQLWKS>  insert into table3 values('zhu',5555.55);
SQLWKS> commit;

SQL> select avg(distinct sal) from gao.table3;

AVG(DISTINCTSAL)
----------------
         3333.33

SQL> select avg(all sal) from gao.table3;

AVG(ALLSAL)
-----------
    2592.59

---

61.MAX(DISTINCT|ALL)
求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次
SQL> select max(distinct sal) from scott.emp; 

MAX(DISTINCTSAL)
----------------
            5000

---

62.MIN(DISTINCT|ALL)
求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次
SQL> select min(all sal) from gao.table3; 
MIN(ALLSAL)
-----------
    1111.11

---

63.STDDEV(distinct|all)
求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差
SQL> select stddev(sal) from scott.emp;

STDDEV(SAL)
-----------
  1182.5032

SQL> select stddev(distinct sal) from scott.emp;

STDDEV(DISTINCTSAL)
-------------------
           1229.951

---

64.VARIANCE(DISTINCT|ALL)
求协方差 

SQL> select variance(sal) from scott.emp;

VARIANCE(SAL)
-------------
    1398313.9

---

65.GROUP BY
主要用来对一组数进行统计
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno;

   DEPTNO  COUNT(*)  SUM(SAL)
--------- --------- ---------
       10         3      8750
       20         5     10875
       30         6      9400

---

66.HAVING
对分组统计再加限制条件
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having count(1)>=5;

   DEPTNO  COUNT(*)  SUM(SAL)
--------- --------- ---------
       20         5     10875
       30         6      9400
SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by deptno ;

   DEPTNO  COUNT(*)  SUM(SAL)
--------- --------- ---------
       20         5     10875
       30         6      9400

---

67.ORDER BY
用于对查询到的结果进行排序输出
SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc;

   DEPTNO ENAME            SAL
--------- ---------- ---------
       10 KING            5000
       10 CLARK           2450
       10 MILLER          1300
       20 SCOTT           3000
       20 FORD            3000
       20 JONES           2975
       20 ADAMS           1100
       20 SMITH            800
       30 BLAKE           2850
       30 ALLEN           1600
       30 TURNER          1500
       30 WARD            1250
       30 MARTIN          1250
       30 JAMES            950

Rename [OldTableName] to [NewTableName]   //重命名表
Alter Table [Table] Add [Column] Decimal(14,0)  //增加列
Alter Table [Table] Set UnUsed Column [Column]  //刪除列
Alter Table [Table] Drop UnUsed Columns
Alter Table [Table] Modify([Column] Decimal(14,0)) //修改列
Create Sequence BUFFER_FORMULA_Seq NoMaxValue Nocycle //创建序列
=================以为为常用函数用法=================
SELECT Least([Num1],[Num2],[Num3],[Num4]) FROM [Table] //取最小值者
SELECT Greatest([Num1],[Num2],[Num3]) FROM [Table] //取最大值者
SELECT AscII([Chr1]) FROM [Table]   //CHAR返回ASC码
SELECT Chr([Num1]) FROM [Table]    //ASC返回CHAR值
SELECT Concat([Str1],[Str2]) FROM [Table]  //连接两个字符串
SELECT InitCap([Str1]) FROM [Table]   //首字母大写
SELECT InStr([Str1],[Str2],[Num1],[Num2]) FROM [Table] //返回Str1中从Num1开始查找第Num2个Str2的位置
SELECT Length([Str1]) FROM [Table]   //返回字串长度
SELECT Lower([Str1]) FROM [Table]   //小写转换
SELECT Upper('sdfsds') FROM [Table]   //大写转换
SELECT LPad([Str1],[Num1],[Str2]) FROM [Table]  //在Str1左边用Str2补足Num1位
SELECT RPad([Str1],[Num1],[Str2]) FROM [Table]  //在Str1右边用Str2补足Num1位
SELECT LTrim([Str1]) FROM [Table]   //去除Str1中左边的空格
SELECT RTrim([Str1]) FROM [Table]   //去除Str1中右边的空格
SELECT Replace([Str1],[Str2],[Str3]) FROM [Table] //将Str1中的所有Str2都替换成Str3
SELECT SubStr([Str1],[Num1],[Num2]) FROM [Table] //返回Str1中从Num1开始的Num2个字符
SELECT Sign([Num1]) FROM [Table]   //根据Num1值与0的关系分别返回0,1,-1
SELECT DeCode([V],[C1],[R1],[C2],[R2],[E1]) FROM [Table]//同C语言中的Case用法，详解省略
SELECT VSize([Str1]) FROM [Table]   //返回Str1的字节数
=================通用汇总函数用法=================
SELECT C2,C3,Sum(C3) FROM [Table] Group By RollUp(C2,C3)
SELECT C2,C3,Sum(C3) FROM [Table] Group By Cube(C2,C3) Order By C2,C3
=================常用时间操作函数=================
SELECT To_Date([Date1],'DD-Mon-YYYY')-To_Date([Date2],'DD-Mon-YYYY') FROM [Table]//日期间的天数差
SELECT Add_Months([Date1],-5) FROM [Table]  //在Date1上加上-5个月
SELECT To_Date([Date1],'DD-Mon-YYYY')+30 FROM [Table] //在转换成的日期上加上30天
SELECT Last_Day([Date1]) FROM [Table]   //Date1所在月份的最后一天
SELECT Next_Day([Date1],'星期一') FROM [Table]  //Date1的下周一
SELECT Months_Between([Date1],[Date2]) FROM [Table] //Date1与Date2之间的月份差
常用的時间格式：
DY-------DAY OF WEEK ABBREVIATED----------MON,TUE,FRI......
DAY------DAY OF WEEK SPELLED OUT----------MONDAY,TUESDAY,FRIDAY......
D--------DAY OF WEEK(1--7)----------------1,2,3,4,5,6,7
DD-------DAY OF MONTH(1-31)---------------1,2,3,.....31
DDD------DAY OF YEAR(1--366)--------------1,2,3,.....366
W--------WEEK OF THE MONTH----------------1,2,3,4,5
WW-------WEEK OF THE YEAR-----------------1,2,3,.....53
MM-------TWO-DIGIT MONTH------------------01,02,03,....12
MON------MONTH NAME ABBREVIATED-----------JAN,FEB,MAR....
MONTH----MONTH NAME SPELLED OUT-----------JANUARY,FEBRUARY.....
YYYY--------------------------------------YEAR
SS----------------------------------------SECOND
HH,HH12,HH24------------------------------HOUR
MI----------------------------------------MINUTES
=================相关的几个JOB操作=================
添加Job-----------dbms_Job.submit(Jobno,'test;',sysdate,'sysdate+1/1440');
删除Job-----------dbms_Job.remove(Jobno);
修改要执行的操作--Job:dbms_Job.what(Jobno,what);
修改下次执行时间--dbms_Job.next_date(Job,next_date);
修改间隔时--------dbms_Job.interval(Job,interval);
停止Job-----------dbms.broken(Job,broken,nextdate);
启动Job-----------dbms_Job.run(Jobno);

 

 

 

函数	描述
LOWER(char)	将字符串表达式char中的所有大写字母转换为小写字母
UPPER(char)	将字符串表达式char中的所有小写字母转换为大写字母
INITCAP(char)	首字母转换成大写
SUBSTR(char,start,length)	返回字符串表达式char中从第start开始的length个字符
LENGTH(char)	返回字符串表达式char的长度
LTRIM(char)	去掉字符串表达式char后面的空格
ASCII(char)	取char的ASCII值
CHAR(number)	取number的ASCII值
REPLACE(char,str1,str2)	将字符串中所有str1换成str2
INSTR(char1,char2,start,times)	在char1字符串中搜索char2字符串，start为执行搜索操作的起始位置，times为搜索次数

 

常用日期函数

函数	描述
SYSDATE	返回系统当前日期和时间
NEXT_DAY(day,char)	返回day指定的日期之后并满足char指定条件的第一个日期，char所指条件只能为星期几
LAST_DAY(day)	返回day日期所指定月份中最后一天所对应的日期
ADD_MONTH(day,n)	返回day日期在n个月后(n为正数)或前(n为负数)的日期
MONTH_BETWEEN(day1,day2)	返回day1日期和day2日期之间相差得月份
ROUND(day[,fmt])	按照fmt指定格式对日期数据day做舍入处理，默认舍入到日
TRUNC(day,[,fmt])	按照fmt指定格式对日期数据day做舍入处理，默认截断到日

 

数据类型转换函数

函数	描述
TO_CHAR	将一个数字或日期转换成字符串
TO_NUMBER	将字符型数据转换成数字型数据
TO_DATE	将字符型数据转换为日期型数据
CONVERT	将一个字符串从一个字符集转换为另一种字符集
CHARTORowid	将一个字符串转换为Rowid数据类型
RowIDTOCHAR	将一个Rowid数据类型转换为字符串
NEXTTORAW	将一个十六进制字符串转换为RAW数据类型
RAWTOHEX	将一个RAW类型的二进制数据类型转换为一个十六进制表达的字符串
TO_MULTI_BYTE	将一个单字节字符串转换为多字节字符串
TO_SINGLE_BYTE	将一个多字节字符串转换为单字节字符串

 

 

 

集合函数

函数	描述
AVG	计算一列值的平均值
COUNT	统计一列中值的个数
MAX	求一列值中的最大值
MIN	求一列值中的最小值
SUM	计算一列值的总和
STDDEV	计算一列值的标准差
VARIANCE	计算一列值的方差