PLSQL常用方法汇总

在SQLPLUS下，实现中-英字符集转换Oracle学习网mm:ICT2oC!n)^
alter session set nls_language='AMERICAN';
1r IL e G,{.oDalter session set nls_language='SIMPLIFIED CHINESE'; Oracle学习网 LE MB&kr0`
主要知识点：Oracle学习网 cJ(] bx
J/r*h
一、有关表的操作
W3|[$[QE0Z1)建表
i!E@ Mg"m$R.Wcreate table test as select * from dept; --从已知表复制数据和结构Oracle学习网pgR4K wO,tG/H
create table test as select * from dept where 1=2; --从已知表复制结构但不包括数据Oracle学习网 Y\ O(v*i {Q
2)插入数据：Oracle学习网}W;OH#u)IC7M~
insert into test select * from dept;Oracle学习网 c-H&?yA&N om+p
二、运算符Oracle学习网2B#JmU m4Y Q8bA
算术运算符：+ - * / 可以在select 语句中使用Oracle学习网9x(m,Ul X ~7d
连接运算符：|| select deptno|| dname from dept;
5[W!{a EG-a4p CC比较运算符：> >= = != < <= like between is null in
F5BB,A"v i3wm(dW逻辑运算符：not and or
4f}w X f~集合运算符： intersect ，union， union all， minus Oracle学习网'k~i
DB^6bT0d?B}
要求：对应集合的列数和数据类型相同
!t$z V?S0e ]:~C查询中不能包含long 列Oracle学习网N3si9}'w
列的标签是第一个集合的标签
D)F3VbV#B使用order by时，必须使用位置序号，不能使用列名Oracle学习网&U/Y6LnMT'K.L r;o
例：集合运算符的使用：
bo ]8`z?&Ointersect ，union， union all， minus
7r(M[V {gselect * from emp intersect select * from emp where deptno=10 ;
Tv&A:M.B&w.{select * from emp minus select * from emp where deptno=10;Oracle学习网({,a KFd j0ewmI
select * from emp where deptno=10 union select * from emp where deptno in (10,20); --不包括重复行 Oracle学习网e]H?*}[
select * from emp where deptno=10 union all select * from emp where deptno in (10,20); --包括重复行
MP&m4U&Jc
}#k3x.|GWUBN三，常用 ORACLE 函数
1`0t|N*wj0XQ.E%Bsysdate为系统日期 dual为虚表
^6ar2`G.]8o一）日期函数[重点掌握前四个日期函数]
RsrbQ0Z7_~@\Q1,add_months[返回日期加(减)指定月份后(前)的日期]
f"P3A/Pbg+aIV3yselect sysdate S1,add_months(sysdate,10) S2,Oracle学习网#p2t1S1z5k
add_months(sysdate,5) S3 from dual;
?)E8zXX9r#e#M2,last_day [返回该月最后一天的日期]
0A7N+M ` vN9aselect last_day(sysdate) from dual;
R6Op I6f?v^*e3,months_between[返回日期之间的月份数]
*w9i }R&eselect sysdate S1, months_between('1-4月-04',sysdate) S2,Oracle学习网!E x"vM/LEu
months_between('1-4月-04','1-2月-04') S3 from dual
i"C:x;d;x8TE Ag4,next_day(d,day): 返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日
c$@P)MmV?)M^@ |select sysdate S1,next_day(sysdate,1) S2,Oracle学习网{8c'TY]
next_day(sysdate,'星期日') S3 FROM DUAL
IU/m2mH'F ?Kkh"R5,round[舍入到最接近的日期](day:舍入到最接近的星期日)Oracle学习网Zw+^9GqCZ
select sysdate S1,Oracle学习网0K:Tz#@O)A0cc
round(sysdate) S2 ,Oracle学习网t,[$BWSmu:Y
round(sysdate,'year') YEAR,Oracle学习网7W ?V"h_
round(sysdate,'month') MONTH ,
5J.Xc.E:u G]8wz.tround(sysdate,'day') DAY from dualOracle学习网0V0xG6X(K6Mda/?)K
6,trunc[截断到最接近的日期] Oracle学习网 J-gp5VVAB
select sysdate S1,
LWKc#DY&xVsftrunc(sysdate) S2,Oracle学习网Cgd&f&~6T+gg
X
trunc(sysdate,'year') YEAR,
_%e*V3S*Y%\sqtrunc(sysdate,'month') MONTH ,
~2r zj&N)]Mtrunc(sysdate,'day') DAY from dualOracle学习网;_t8Eg*{?mHH*S
7,返回日期列表中最晚日期Oracle学习网1|Z8{Rz:IQ(L8g
select greatest('01-1月-04','04-1月-04','10-2月-04') from dual
1VZ9X:Wv
wKb6LX
^二）字符函数(可用于字面字符或数据库列)
#F!hVw2u1，字符串截取Oracle学习网,TC3A-Q6?2U.i"~
select substr('abcdef',1,3) from dual
7c7?~ n3ny5w2,查找子串位置Oracle学习网4ZS\)fZ,v&S G
select instr('abcfdgfdhd','fd') from dual
3^b s G^8y4hag3,字符串连接
*C8ZRy"_3\sdselect 'HELLO'||'hello world' from dual;
G#J0E4U/s0Q
e4, 1)去掉字符串中的空格Oracle学习网)J7[(jeO,cVf
select ltrim(' abc') s1,Oracle学习网W$O/gl2k@
rtrim('zhang ') s2,Oracle学习网ks jM+c
trim(' zhang ') s3 from dualOracle学习网` eCn)?6R.J^
2)去掉前导和后缀
j0VJ!\$jselect trim(leading 9 from 9998767999) s1,Oracle学习网:`Z)}3`(`(zd @
trim(trailing 9 from 9998767999) s2,
8R#a/NDQ5?
x-ptrim(9 from 9998767999) s3 from dual;
@E(GD6x{*Q5,返回字符串首字母的Ascii值Oracle学习网X?Q P1gY-gn7r
select ascii('a') from dualOracle学习网MPl3M&Gj6U
6,返回ascii值对应的字母
7At^Ik"d2aM+Y"Uselect chr(97) from dualOracle学习网A[liY m
7,计算字符串长度
3R3qQ*P3u\)_"yRselect length('abcdef') from dual
y%T
m)~d Q4}9` ~8,initcap（首字母变大写） ,lower(变小写),upper(变大写)
8B0s&w;Ke)f)Wselect lower('ABC') s1,
0Cl1rr-B/j`2}upper('def') s2,
,e.tT x^3G|/binitcap('efg') s3 from dual; Oracle学习网{ y@&J
B
w V3M2j#?
9,ReplaceOracle学习网l/|&T'a i.C/my
select replace('abc','b','xy') from dual;Oracle学习网)A1z9{5@.tKz1J
10,translateOracle学习网z"i G bQ;fvo+d
select translate('abc','b','xx') from dual; -- x是1位
@iH9Bw|11,lpad [左添充] rpad [右填充](用于控制输出格式)Oracle学习网/q^!iN}
`
select lpad('func',15,'=') s1, rpad('func',15,'-') s2 from dual;Oracle学习网i1S:J \+Wo
select lpad(dname,14,'=') from dept;
J r-eY2W I[G12, decode[实现if ..then 逻辑]
]:Vb#n:ha]}-Zselect deptno,decode(deptno,10,'1',20,'2',30,'3','其他') from dept;Oracle学习网'_#y;J|sf w }o!A
三）数字函数Oracle学习网~#~1w+HNy
1，取整函数（ceil 向上取整,floor 向下取整）
9USC |},{select ceil(66.6) N1,floor(66.6) N2 from dual;Oracle学习网Z wQ9G#{j h K+j$Jw
2, 取幂(power) 和 求平方根(sqrt)
(|x(A3kJ/X*an/yselect power(3,2) N1,sqrt(9) N2 from dual;Oracle学习网AMD0Zbg1sh A
3,求余Oracle学习网[ p&D L!q2EP
select mod(9,5) from dual;
&qd;mO8D%E_4,返回固定小数位数 (round:四舍五入，trunc:直接截断)
!Mg6X}9`select round(66.667,2) N1,trunc(66.667,2) N2 from dual;
.GPB7Z5zu5,返回值的符号(正数返回为1,负数为-1)
6q)a.x{*g A%nuselect sign(-32),sign(293) from dual;