ORACLE学习实例总结

ORACLE学习实例总结
笔名：北⽅天空<1>成功导⼊数据库1impuserdb/missrain@testfile=userdb.dmpfull=ylog=
userdb_imp.logimpdictdb/missrain@testfile=dictdb.dmpfull=ylog
=dictdb_imp.logimpappdb/missrain@testfile=appdb.dmpfull=ylog=
appdb_imp.logimptmmsdict/missrain@testfile=tmmsdict.dmpfull=y
log=tmmsdict_imp.logimptmmsapp/missrain@testfile=tmmsapp.dmpfu
ll=ylog=tmmsapp_imp.logimpuserdb/missrain@testfile=userdb.dmp
full=ylog=userdb_imp.log中imp:导⼊命令userdb：⽤户名missrain：密码test：服务命名u
serdb.dmp：数据库⽂件userdb_imp.log：数据库⽇志full=y：全部导⼊<2>成功创建表1createtab
letest(avarchar(128)default''wu'',bintdefault0)<3>成功创建存储过程1
CREATEORREPLACEprocedurepro1asbegininsertintotestvalues
(''afa'',3);end;<4>成功创建存储过程2CREATEORREPLACEprocedurepro1asb
egindeclareavarchar2(128);bint;beginselectintoa,bfrom
testwherea=''afa'';end;end;<5>成功创建存储过程3createorreplaceproce
durepro2asbeginexecuteimmediate''selectfromtest'';end;<6>
成功创建存储过程4createorreplaceprocedurepro2asbeginexecuteimmedi
ate''insertintotest(a,b)values(3,2)'';end;<7>成功创建触发器11、建⽴试验表c
reatetableemployees_copyasselectfromtt;2、建⽴⽇志表createta
bleemployees_log(whovarchar2(30),whendate);3、在employees_
copy表上建⽴语句触发器，在触发器中填充employees_log表。

Createorreplacetriggerb
iud_employee_copyBeforeinsertorupdateordeleteOnemployee
s_copyBeginInsertintoemployees_log(Who,when)Values(use
r,sysdate);End;4、测试updateemployees_copyseta=''fsd'';(tt表中
有a字段)<8>成功创建触发器21、建⽴试验表createtableemployees_copyasselect
fromtt;2、建⽴⽇志表createtableemployees_log(whovarchar2(30),
whendate);3、在employees_copy表上建⽴语句触发器，在触发器中填充employees_log表。

C
reateorreplacetriggerbiud_employee_copyAfterinsertorupda
teordeleteOnemployees_copyBeginInsertintoemployees_log
(Who,when)Values(user,sysdate);End;4、测试updateemploye
es_copyseta=''fsd'';(tt表中有a字段)<9>成功使⽤游标1declarename1varchar2(39
);cursormycurisselectafromtest;beginopenmycur;loopfe
tchmycurintoname1;exitwhenmycur%notfound;DBMS_OUTPU
T.PUT_LINE(name1);endloop;closemycur;end;<10>成功使⽤游标2decla
recursormycurisselectafromtest;name1varchar2(39);begin
openmycur;loopfetchmycurintoname1;exitwhenmycur%notf
ound;DBMS_OUTPUT.PUT_LINE(name1);endloop;closemycur;en
d;<11>常⽤sql语句1正在看的ORACLE教程是:oracle常⽤sql语句。

SQLPlussystem/manager
2、显⽰当前连接⽤户SQL>showuser
3、查看系统拥有哪些⽤户SQL>selectfromall_us
ers;4、新建⽤户并授权SQL>createuseraidentifiedbya;（默认建在SYSTEM表空间下
）SQL>grantconnect,resourcetoa;5、连接到新⽤户SQL>conna/a6、查询当前
⽤户下所有对象SQL>selectfromtab;7、建⽴第⼀个表SQL>createtablea(anu
mber);8、查询表结构SQL>desca9、插⼊新记录SQL>insertintoavalues(1);
10、查询记录SQL>selectfroma;11、更改记录SQL>updateaseta=2;12、删
除记录SQL>deletefroma;13、回滚SQL>roll;SQL>rollback;14、提交SQL
>commit;⽤户授权:GRANTALTERANYINDEXTO"user_id"GRANT"dba"
TO"user_id";ALTERUSER"user_id"DEFAULTROLEALL创建⽤户:CREAT
EUSER"user_id"PROFILE"DEFAULT"IDENTIFIEDBY"DEFAULTTABL ESPACE"USERS"TEMPORARYTABLESPACE"TEMP"ACCOUNTUNLOCK;GRAN
T"CONNECT"TO"user_id";⽤户密码设定:ALTERUSER"CMSDB"IDENTIFIE
DBY"pass_word"表空间创建:CREATETABLESPACE"table_space"LOGGING
DATAFILE''C:\ORACLE\ORADATA\dbs\table_space.ora''SIZE5M-------
-----------------------------------------------------------------
1、查看当前所有对象SQL>selectfromtab;
2、建⼀个和a表结构⼀样的空表SQL>create
tablebasselectfromawhere1=2;SQL>createtableb(b1,b2
,b3)asselecta1,a2,a3fromawhere1=2;3、察看数据库的⼤⼩，和空间使⽤情况SQL
>coltablespaceformata20SQL>selectb.file_id⽂件ID,b.tabl
espace_name表空间,b.file_name物理⽂件名,b.bytes总字节数,(
b.bytes-sum(nvl(a.bytes,0)))已使⽤,sum(nvl(a.bytes,0))剩
余,sum(nvl(a.bytes,0))/(b.bytes)100剩余百分⽐fromdba_free_space
a,dba_data_filesbwherea.file_id=b.file_idgroupbyb.tablesp
ace_name,b.file_name,b.file_id,b.bytesorderbyb.tablespace_nam
e/dba_free_space--表空间剩余空间状况dba_data_files--数据⽂件空间占⽤情况4、查
看现有回滚段及其状态SQL>colsegmentformata30SQL>SELECTSEGMENT_NAME
,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUSFROMDBA_ROLLBAC
K_SEGS;5、查看数据⽂件放置的路径SQL>colfile_nameformata50SQL>select
tablespace_name,file_id,bytes/1024/1024,file_namefromdba_data_
filesorderbyfile_id;6、显⽰当前连接⽤户SQL>showuser7、把SQLPlus当计算
器SQL>select10020fromdual;8、连接字符串SQL>select列1||列2fr
om表1;SQL>selectconcat(列1,列2)from表1;9、查询当前⽇期SQL>select
to_char(sysdate,''yyyy-mm-dd,hh24:mi:ss'')fromdual;10、⽤户间复制数据SQ
L>copyfromuser1touser2createtable2usingselectfromta
ble1;11、视图中不能使⽤orderby，但可⽤groupby代替来达到排序⽬的SQL>createviewa
asselectb1,b2frombgroupbyb1,b2;12、通过授权的⽅式来创建⽤户SQL>gra
ntconnect,resourcetotestidentifiedbytest;SQL>conntest/t
est13、查出当前⽤户所有表名。

selectuniquetnamefromcol;----------------
-------------------------------------------------------/向⼀个表格添
加字段/altertablealist_tableaddaddressvarchar2(100);/修改字段
属性字段为空/altertablealist_tablemodifyaddressvarchar2(80);
/修改字段名字/createtablealist_table_copyasselectID,NAME,PHON
E,EMAIL,QQasQQ2,/qq改为qq2/ADDRESSfromalist_table;dropt
ablealist_table;renamealist_table_copytoalist_table/修改表名
/空值处理有时要求列值不能为空createtabledept(deptnonumber(2)notnull,
dnamechar(14),locchar(13));在基表中增加⼀列altertabledeptadd(hea
dcntnumber(3));修改已有列属性altertabledeptmodifydnamechar(20);
注：只有当某列所有值都为空时，才能减⼩其列值宽度。

只有当某列所有值都为空时，才能改变其列值类型。

只有当某列所有值都为不空时，才能定义该列为notnull。

例：altertabledeptmodify(locchar(12));alter
tabledeptmodifylocchar(12);altertabledeptmodify(dnamech
ar(13),locchar(12));查找未断连接selectprocess,osuser,username,machi
ne,logon_time,sql_textfromv$sessiona,v$sqltextbwhe<12>成功定义
变量1declarepinumber(9);bvarchar2(32);beginexecuteimmediate''
selectfromtest'';end;<13>成功创建函数1CREATEORREPLACEFUNCTIONSTU
DENT_EXISTS(V_SNAMEVARCHAR2)RETURNVARCHAR2ISV_RESULTVARCHAR2
(100);BEGINV_RESULT:=V_SNAME;RETURN(V_RESULT);END;<14>成功使⽤li
ke1selectfromtestwherealike''as''<15>成功使⽤casewhen1select
caseawhen''a''then''⼩名''when''3''then''⼩刚''endasttfromtest
<16>成功使⽤casewhen2selectcaseawhen''a''then''D''when''3''th
en''D''endastt,bastsfromtest<17>成功使⽤leftjoin1select
fromtestleftjoinsson1=1--显⽰test表所有数据，显⽰ss表满⾜条件数据<18>成功使⽤rig
fromtestleftjoinsson1=1--显⽰test表所有数据，显⽰ss表满⾜条件数据<18>成功使⽤rig
htjoin1selectfromtestrightjoinsson1=1--显⽰test表满⾜条件数据，显⽰
ss表所有数据<19>成功使⽤innerjoin1selectfromtestinnerjoinsson1=1
--显⽰test表满⾜条件数据，显⽰ss表满⾜条件数据<20>成功使⽤联合查询1selectfromtest,ss—两表都
得有数据<21>成功创建序列1create?sequence?id?start?with?1?increment?by?1?min
value?1?nomaxvalue?cache?10;<22>成功删除序列1dropsequenceid<23>成功给⽤户赋
权限1grantselectonSEQ_USERStouserdb<24>成功创建标⽰列1先假设有这么⼀个表：creat
e?table?S_Depart??(?DepartIdINT?not?null,?DepartNameNV
ARCHAR2(40)not?null,?DepartOrderINT?default?0,
constraintPK_S_DEPARTprimarykey(DepartId));在oracle中sequence
就是所谓的序列号，每次取的时候它会⾃动增加，⼀般⽤在需要按序列号排序的地⽅。

1、CreateSequence你⾸先要有CRE ATESEQUENCE或者CREATEANYSEQUENCE权限，CREATESEQUENCEemp_sequence
INCREMENTBY1--每次加⼏个STARTWITH1--从1开始计数NOMAXvalue--不设置
最⼤值NOCYCLE--⼀直累加，不循环CACHE10;--设置缓存cache个序列，如果系统down掉了或者其它情况
将会导致序列不连续，也可以设置为---------NOCACHE针对S_Depart创建的sequence如下：create?se
quence?S_S_DEPARTminvalue?1maxvalue?999999999999999999999999999st
art?with?1increment?by?1nocache;⼀旦定义了emp_sequence，你就可以⽤CURRVAL，NE
XTVALCURRVAL=返回sequence的当前值NEXTVAL=增加sequence的值，然后返回sequence
值⽐如：emp_sequence.CURRVALemp_sequence.NEXTVAL可以使⽤sequence的地⽅：
-不包含⼦查询、snapshot、VIEW的SELECT语句-INSERT语句的⼦查询中-NSERT语句的value
S中-UPDATE的SET中可以看如下例⼦：insert?into?S_Depart(departId,Departn
ame,Departorder)values(S_S_Depart.Nextval,''12345'',1);SELECTempse
q.currvalFROMDUAL;<25>成功修改序列1ALTERSEQUENCEidINCREMENTBY10
MAXvalue10000CYCLE--µ?10000oó′óí·?aê?NOCACHE;<26>成功创建序列触发器
1createorreplacetriggertri_test_idbeforeinsertonS_Depart-
-S_Departê?±í??foreachrowdeclarenextidnumber;beginIF:new.
DepartIdISNULLor:new.DepartId=0THEN--DepartIdê?áD??,±í?D±êê
áDselectid.nextval--SEQ_IDyê2′′?¨µ?intonextidfromsys
.dual;:new.DepartId:=nextid;endif;endtri_test_id;<27>成功创建标⽰列2
1.创建表CREATETABLETEST(IDINTEGER,AVARCHAR2(128BYTE)
DEFAULT''⽆'',BVARCHAR2(128BYTE)
DEFAULT''⽆'')2.创建序列createsequencetest_idminvalue1ma
xvalue999999999999999999999999999startwith1incrementby1nocac
he;3.创建触发器createorreplacetriggertest_idbeforeinsertontest
--test为表名称foreachrowdeclarenextidnumber;beginIF:new.idIS
NULLor:new.id=0THEN-–id为列名称selecttest_id.nextval--test_id序
列名称intonextidfromsys.dual;:new.id:=nextid;endif;endtest_i
d;4.插⼊数据测试insertintotest(a,b)values(''a'',''b'')<28>成功设置主键1altert
abletest4addconstraintsconstraints_idPRIMARYKEY(id)<29>成
功创建标⽰列3createtabletest4(idintprimarykey,avarchar2(128)defa
ult''no'',bvarchar(128)default''no'')createsequencetest4_idminv
alue1maxvalue999999999999999999999999999startwith1incrementb
y1nocache;createorreplacetriggertest4_idbeforeinsertonte
st4--test?a±í??3?foreachrowdeclarenextidnumber;beginIF:new
.idISNULLor:new.id=0THEN--id?aáD??3?selecttest4_id.nextva
l--test_idDòáD??3?intonextidfromsys.dual;:new.id:=nextid;e
ndif;endtest4_id;<30>成功查询语句中使⽤||1selectfrompub_v_userinfo
wheredisabled=''0''and''R_B_''||depid||''_''||stationid=''R_B_35_029
''<31>成功oracle同时执⾏多条语句1begindeletefromtest4whereid=88;deletef
romtest3whereid=88;end;<32>成功导出数据库1setfdate=%DATE:~0,10%mkdir
%E:\jiatiejian\electric\数据库备份\2013040403%cd%E:\jiatiejian\elect
ric\数据库备份\2013040403%expappdb/missrain@yyhmisfile=appdb.dmplo
g=appdb_exp.logexpdictdb/missrain@yyhmisfile=dictdb.dmplog=di
ctdb_exp.logexptmmsapp/missrain@yyhmisfile=tmmsapp.dmplog=tmm
sapp_exp.logexptmmsdict/missrain@yyhmisfile=tmmsdict.dmplog=t
mmsdict_exp.logexpuserdb/missrain@yyhmisfile=userdb.dmplog=us
erdb_exp.log<33>成功备份数据库1setfdate=%DATE:~0,10%mkdir%E:\jiatiejia
n\electric\数据库备份\2013040403%cd%E:\jiatiejian\electric\数据库备份\2013
040403%expappdb/missrain@yyhmisfile=appdb.dmplog=appdb_exp.lo
gexpdictdb/missrain@yyhmisfile=dictdb.dmplog=dictdb_exp.logex
ptmmsapp/missrain@yyhmisfile=tmmsapp.dmplog=tmmsapp_exp.logex
ptmmsdict/missrain@yyhmisfile=tmmsdict.dmplog=tmmsdict_exp.lo
gexpuserdb/missrain@yyhmisfile=userdb.dmplog=userdb_exp.log<3
4>成功获得系统时间selectsysdatefromdd1<35>成功oracle修改字段名称1altertable
yxjgsqd_outstoragerenamecolumnremarktoremark1;<36>视图授权1ORAC
LE建视图授权的例⼦：A⽤⼾下有tableTabAA1⽤⼾建⽴view(V_TabA),view中是引⽤A
⽤⼾下的TabA.A2⽤⼾要selectA1⽤户下的这个view.采⽤直接赋予权限的⽅式，很可能会碰到错误O
RA-01720:grantoptiondoesnotexistfor......⼀般采取的⽅法是：1.
以基表⽤户A登⼊(如果多个基表，那么多次运⾏)或SYS登陆sql>connectA/Asql>grant
selectonTabAtoA1withgrantoption;允许A1授权给A22
.以view的拥有⽤户登⼊sql>connectA1/A1sql>grantselectonA
1.V_TabAtoA2;3.OK,以A2登⼊即可以访问view了sql>connectA
2/A2sql>selectfromA1.V_TabA;<37>成功创建视图1create
viewssasselectfromreqplanuseway<38>成功创建视图2createviewss(de
scription)asselectdescriptionfromreqplanuseway<39>成功导出数据库1set
fdate=%DATE:~0,10%mkdir%E:\jiatiejian\electric\数据库备份\20130408%c
d%E:\jiatiejian\electric\数据库备份\20130408%expappdb/missrain@yyhmi
sfile=appdb.dmplog=appdb_exp.logexpdictdb/missrain@yyhmisfil
e=dictdb.dmplog=dictdb_exp.logexptmmsapp/missrain@yyhmisfile=
tmmsapp.dmplog=tmmsapp_exp.logexptmmsdict/missrain@yyhmisfile
=tmmsdict.dmplog=tmmsdict_exp.logexpuserdb/missrain@yyhmisfil
e=userdb.dmplog=userdb_exp.logexpuserdb/missrain@yyhmisfile=l
cappdb.dmplog=lcappdb_exp.logexpuserdb/missrain@yyhmisfile=lc
dictdb.dmplog=lcdictdb_exp.log<40>成功oracle跨数据库查询1selectt.isflo
wfromtmmsapp.hr_yyhkq_setisflowtwheret.id=1前提是当前数据库对数据库tmm
sapp中表hr_yyhkq_setisflow有select权限设置⽅法：数据库右键下⾯“启⽤⾓⾊”<41>成功插⼊⽇期类型1t
o_date('''',''2013/4/12'')<42>成功关闭锁1selectobject_name,machine,s.sid,
s.serial#fromv$locked_objectl,dba_objectso,v$sessionswhere
l.object_id=o.object_idandl.session_id=s.sid;altersystemki
llsession''17,1275''--(其中24,111分别是上⾯查询出的sid,serial#)<43>成功同时执⾏多条语
句1begininsertintodd1values(''s'');insertintodd1values(''s
'');end;<44>成功拼接多个字段最⼤值1selectmax(year||month)fromxc_t_maintoex
cel<45>成功使⽤rownum1selectfromkaoqin_t_attendancewhererownum
<2orderbynameasc<46>成功使⽤类top1selectfromkaoqin_t_attend
ancewhererownum<2orderbynameasc<47>创建链接服务器1创建链接到ORACLE的链接
服务器：EXECsp_addlinkedserver@server=''QZYDJK_Link'',@srvpr
oduct=''Oracle'',@provider=''MSDAORA'',@datasrc=''CSC25''
远程链接到链接服务器：EXECsp_addlinkedsrvlogin@rmtsrvname=''QZYDJK_Li
nk'',@useself=''false'',@locallogin=NULL,@rmtuser=
''xxxx'',@rmtpassword=''1234''启⽤远程链接服务器上的远程调⽤过程RPC：EXECsp_ser
''xxxx'',@rmtpassword=''1234''启⽤远程链接服务器上的远程调⽤过程RPC：EXECsp_ser
veroption@server=''QZYDJK_Link'',@optname=''rpcout'',
@optvalue=true查询远程链接服务器上的数据：EXEC(''SELECTFROMtable'')
ATQZYDJK_Link断开远程链接服务器链接：EXECsp_droplinkedsrvlogin''QZYDJK_
Link'',NULL;删除远程链接服务器链接：EXECsp_dropserver[QZYDJK_Link]<48>成功使⽤n
vl函数1selectnvl(year,0)asnvlfrompn_t_monthplan<49>成功删除列1alter
tablepn_t_monthplan_masterdropcolumncheck1<50>成功字符转换成数字1sele
ctfromkaoqin_t_attendanceorderbyto_number(id)asc<51>成功⽤⽇期
作查询条件1selectfromfl_t_lh_basetempwherestatdatetime=to_date
(''2013/5/60:11:00'',''yyyy-mm-ddhh24:mi:ss'')<52>成功保存⽇期类型数据1update
fl_t_lh_basetempsetstatdatetime=to_date(''2013/5/60:11:00'',''y
yyy-mm-ddhh24:mi:ss'')wherestatdatetimeisnull<53>⼀个表更新另个表1me
rgeintotab1using(selectFROMtab2XWHEREX.ROWID=(SELECT
MAX(Y.ROWID)FROMtab2YWHEREX.ID=Y.ID))tab2on(tab1.id=tab
2.id)whenmatchedthenupdatesettab1.val=tab2.val<54>成功只查询年⽉⽇
selectto_char(begindate,''yyyy/MM/dd'')fromfl__fac_coal_balance_
fee_mainwhereto_char(begindate,''yyyy/MM/dd'')=''2013/05/19''<55>成功
groupby函数1selectto_char(statdatetime,''yyyy-MM-dd'')asstatdatet
imefromfl_t_lh_lime_tablewhereto_char(statdatetime,''yyyy-MM-d
d'')=''2013-05-15''groupbyto_char(statdatetime,''yyyy-MM-dd'')<56>成
功⽇期使⽤⼩于1selectto_char(statdatetime,''yyyy-MM-dd'')asstatdatetime
fromfl_t_lh_lime_tablewhereto_char(statdatetime,''yyyy-MM-dd'')
<''2013-05-15''orderbystatdatetimeasc<57>成功⼀表更新另表1mergeintof
l_t_lh_basetempusing(selectFROMfl_t_coalminesetXWHEREX.R
OWID=(SELECTMAX(Y.ROWID)FROMfl_t_coalminesetYWHEREX.coalm
ineid=Y.coalmineid))fl_t_coalmineseton(fl_t_lh_basetemp.UnitF
rom=fl_t_coalmineset.coalminename)whenmatchedthenupdatesetfl
_t_lh_basetemp.coalmine_code=fl_t_coalmineset.coalmineid<58>成功⼀
表更新另表2mergeintofl_t_lh_basetempusing(selectFROMfl_t_coal
minesetXWHEREX.ROWID=(SELECTMAX(Y.ROWID)FROMfl_t_coalmine
setYWHEREX.coalmineid=Y.coalmineid))fl_t_coalmineseton((fl
_t_lh_basetemp.UnitFrom=fl_t_coalmineset.coalminename)and(to_ch
ar(fl_t_lh_basetemp.STATDATETIME,''yyyy-MM-dd'')=''2013-05-06''))whe
nmatchedthenupdatesetfl_t_lh_basetemp.coalmine_code=fl_t_co
almineset.coalmineid<59>成功截取字符串1selectavg(hy_aar)ashy_aar,avg(
hy_st_ar)ashy_st_ar,substr(avg(hy_qnet_ar),1,4)ashy_qnet_arf
romfl_t_lh_coal_assaywhere(hy_timebetweento_date(''2013/5/20
:00:00'',''yyyy-MM-ddhh24:mi:ss'')andto_date(''2013/6/2122:39:47''
,''yyyy-MM-ddhh24:mi:ss''))and(cy_coalmine_name=''005'')and(cy_c
oal_type=''02'')<60>成功取⼩数点位数1selectavg(hy_aar)ashy_aar,avg(hy_st
_ar)ashy_st_ar,round(avg(hy_qnet_ar),2)ashy_qnet_arfromfl_t
_lh_coal_assaywhere(hy_timebetweento_date(''2013/5/20:00:00'',
''yyyy-MM-ddhh24:mi:ss'')andto_date(''2013/6/2122:39:47'',''yyyy-M
M-ddhh24:mi:ss''))and(cy_coalmine_name=''005'')and(cy_coal_type
=''02'')<61>成功字段连接1selectid||''x''frompub_t_sysmenu<62>成功查询字段长度1
selectlength(id)frompub_t_sysmenu<63>成功使⽤createtableas1creat
etablepub_t_sysmenu_backup1asselectfrompub_t_sysmenu<64>成
功使⽤类selectinto1createtablepub_t_sysmenu_backup1asselectfr
ompub_t_sysmenu<65>成功判断字段中是否存在某字符1instr(parentid,''_hide'')=0<66>成
功⽇期字段做查询条件1selectfromfl_t_lh_basetemp_tempwherestatdatetime
>to_date(''2013/5/621:51:00'',''yyyy-MM-ddhh24:mi:ss'')<67>成功⽇期字段
做查询条件2selectsum(coaling_amount)ascoaling_amountfromfl_t_lh_s
tove_coalwhere(to_char(coaling_date,''yyyy-MM-dd'')=''2013-05-29'')
<68>成功同时执⾏多条语句2begindeletefromfl_t_lh_basetemp;--地中衡deletefrom
fl_t_lh_huochebase;--轨道衡deletefromfl_t_lh_coal_assay;--化验表dele
tefromfl_t_lh_electric_coal;--电煤表deletefromfl_t_lh_lime_table
;--⽯灰⽯表deletefromfl_t_lh_stove_coal;--⼊炉煤表deletefromfl_t_lh_s
tove_coal_assay;--⼊炉煤化验表deletefromfl_t_lh_coal_factory_loss;--⼚
损表commit;end;<69>成功修改⽇期字段值1updatecar_t_use_applysetcheck_date=
to_date(''2013-06-0113:23:32'',''yyyy-MM-ddhh24:mi:ss'')wherechec
k_dateisnull<70>成功重命名字段名1altertabledevicecatalog2renamecolu
mntype2totype<71>overflowoccur错误原因1查询出来的字段长度太长<72>oracle⽇期函数1
ORACLE⽇期时间函数⼤全TO_DATE格式(以时间:2007-11-0213:45:25为例)Year:yy
twodigits两位年显⽰值:07yyythreedigits三位年
显⽰值:007yyyyfourdigits四位年显⽰值:2007Mon
th:mmnumber两位⽉显⽰值:11monabbreviated字
符集表⽰显⽰值:11⽉,若是英⽂版,显⽰novmonthspelledout字符集表⽰
显⽰值:11⽉,若是英⽂版,显⽰novemberDay:ddnumber当⽉第⼏天
显⽰值:02dddnumber当年第⼏天显⽰值:02dyabbrevi
ated当周第⼏天简写显⽰值:星期五,若是英⽂版,显⽰fridayspelledout当周第⼏天全写
显⽰值:星期五,若是英⽂版,显⽰fridayddspthspelledout,ordinaltwelfthH
our:hhtwodigits12⼩时进制显⽰值:01hh24twodigits24
⼩时进制显⽰值:13Minute:mitwodigits60进制
显⽰值:45Second:sstwodigits60进制显⽰值:25
其它Qdigit季度显⽰值:4WWdigit
当年第⼏周显⽰值:44Wdigit当⽉第⼏周
显⽰值:124⼩时格式下时间范围为：0:00:00-23:59:59....12⼩时格式下时间范围为：1:00:00
-12:59:59....1.⽇期和字符转换函数⽤法（to_date,to_char）selectto_char(
sysdate,''yyyy-mm-ddhh24:mi:ss'')asnowTimefromdual;//⽇期转化为字
符串selectto_char(sysdate,''yyyy'')asnowYearfromdual;//获取时间
的年selectto_char(sysdate,''mm'')asnowMonthfromdual;//获取时间
的⽉selectto_char(sysdate,''dd'')asnowDayfromdual;//获取时
间的⽇selectto_char(sysdate,''hh24'')asnowHourfromdual;//获取时
间的时selectto_char(sysdate,''mi'')asnowMinutefromdual;//获取
时间的分selectto_char(sysdate,''ss'')asnowSecondfromdual;//获
取时间的秒selectto_date(''2004-05-0713:23:44'',''yyyy-mm-ddhh24:mi:s
s'')fromdual//2.selectto_char(to_date(222,''J''),''Jsp'')fro
mdual显⽰TwoHundredTwenty-Two3.求某天是星期⼏selectto_char(to_date(
''2002-08-26'',''yyyy-mm-dd''),''day'')fromdual;星期⼀selectto_char(t
o_date(''2002-08-26'',''yyyy-mm-dd''),''day'',''NLS_DATE_LANGUAGE=Amer
ican'')fromdual;monday设置⽇期语⾔ALTERSESSIONSETNLS_DATE_LANGUA
GE=''AMERICAN'';也可以这样TO_DATE(''2002-08-26'',''YYYY-mm-dd'',''NLS_DA
TE_LANGUAGE=American'')4.两个⽇期间的天数selectfloor(sysdate-to_dat
e(''20020405'',''yyyymmdd''))fromdual;5.时间为null的⽤法selectid,acti
ve_datefromtable1UNIONselect1,TO_DATE(null)fromdual;注意要
⽤TO_DATE(null)6.⽉份差a_datebetweento_date(''20011201'',''yyyymmdd'')
andto_date(''20011231'',''yyyymmdd'')那么12⽉31号中午12点之后和12⽉1号的12点之前是不
包含在这个范围之内的。

所以，当时间需要精确的时候，觉得to_char还是必要的7.⽇期格式冲突问题输⼊的格式要看你安装的ORACLE字符集的类型,⽐如:US7ASCII,date格式的类型就是:''01-Jan-01''altersystem
setNLS_DATE_LANGUAGE=AmericanaltersessionsetNLS_DATE_LANG
UAGE=American或者在to_date中写selectto_char(to_date(''2002-08-26'',
''yyyy-mm-dd''),''day'',''NLS_DATE_LANGUAGE=American'')fromdual;注意
我这只是举了NLS_DATE_LANGUAGE，当然还有很多，可查看selectfromnls_session_par ametersselectfromV$NLS_PARAMETERS8.selectcount()from(s
electrownum-1rnumfromall_objectswhererownum<=to_date(''200
2-02-28'',''yyyy-mm-dd'')-to_date(''2002-02-01'',''yyyy-mm-dd'')+1)
whereto_char(to_date(''2002-02-01'',''yyyy-mm-dd'')+rnum-1,''D'')n
otin(''1'',''7'')查找2002-02-28⾄2002-02-01间除星期⼀和七的天数在前后分别调⽤DBMS
_UTILITY.GET_TIME,让后将结果相减(得到的是1/100秒,⽽不是毫秒).9.查找⽉份selectmont
hs_between(to_date(''01-31-1999'',''MM-DD-YYYY''),to_date(''12-31-1998
'',''MM-DD-YYYY''))"MONTHS"FROMDUAL;1selectmonths_between(to_d
ate(''02-01-1999'',''MM-DD-YYYY''),to_date(''12-31-1998'',''MM-DD-YYYY'') )"MONTHS"FROMDUAL;1.0322580645161310.Next_day的⽤法Next_day(
date,day)Monday-Sunday,forformatcodeDAYMon-Sun,forforma
tcodeDY1-7,forformatcodeD11selectto_char(sysdate,''hh:mi:
ss'')TIMEfromall_objects注意：第⼀条记录的TIME与最后⼀⾏是⼀样的可以建⽴⼀个函数来处理这个问题createorreplacefunctionsys_datereturndateisbeginreturn
sysdate;end;selectto_char(sys_date,''hh:mi:ss'')fromall_obje
cts;12.获得⼩时数extract()找出⽇期或间隔值的字段值SELECTEXTRACT(HOURFROMTIME STAMP''2001-02-162:38:40'')fromofferSQL>selectsysdate,to_ch
ar(sysdate,''hh'')fromdual;SYSDATETO_CHAR(SYSDATE,''HH'')------
-----------------------------------2003-10-1319:35:2107SQL>
selectsysdate,to_char(sysdate,''hh24'')fromdual;SYSDATETO_C
HAR(SYSDATE,''HH24'')-------------------------------------------
2003-10-1319:35:211913.年⽉⽇的处理selectolder_date,newer_date,y
ears,months,abs(trunc(newer_date-add_months(older_date,year
s12+months)))daysfrom(selecttrunc(months_between(newe
r_date,older_date)/12)YEARS,mod(trunc(months_between(newer_d
ate,older_date)),12)MONTHS,newer_date,older_datefrom(se
lecthiredateolder_date,add_months(hiredate,rownum)+rownumnewe
r_datefromemp))14.处理⽉份天数不定的办法selectto_char(add_months(last
_day(sysdate)+1,-2),''yyyymmdd''),last_day(sysdate)fromdual16.
找出今年的天数selectadd_months(trunc(sysdate,''year''),12)-trunc(sysd
ate,''year'')fromdual闰年的处理⽅法to_char(last_day(to_date(''02''
||:year,''mmyyyy'')),''dd'')如果是28就不是闰年17.yyyy与rrrr的区别''YYYY99T
O_C-----------yyyy990099rrrr991999yyyy010001rrrr012
00118.不同时区的处理selectto_char(NEW_TIME(sysdate,''GMT'',''EST''),''d
d/mm/yyyyhh:mi:ss''),sysdatefromdual;19.5秒钟⼀个间隔SelectTO_DATE
(FLOOR(TO_CHAR(sysdate,''SSSSS'')/300)300,''SSSSS''),TO_CHAR(sysd
ate,''SSSSS'')fromdual2002-11-19:55:0035786SSSSS表⽰5位秒数20.⼀年的第
⼏天selectTO_CHAR(SYSDATE,''DDD''),sysdatefromdual3102002-11-6
10:03:5121.计算⼩时,分,秒,毫秒selectDays,A,TRUNC(A24)Hours,TRUNC(
A2460-60TRUNC(A24))Minutes,TRUNC(A246060-60TRUNC(A
2460))Seconds,TRUNC(A246060100-100TRUNC(A246060))mS
econdsfrom(selecttrunc(sysdate)Days,sysdate-trunc(sysdate
)Afromdual)selectfromtabnameorderbydecode(mode,''FIFO''
,1,-1)to_char(rq,''yyyymmddhh24miss'');//floor((date2-date1)/3
65)作为年floor((date2-date1,365)/30)作为⽉d(mod(date2-date1,365)
,30)作为⽇.23.next_day函数返回下个星期的⽇期,day为1-7或星期⽇-星期六,1表⽰星期⽇nex
t_day(sysdate,6)是从当前开始下⼀个星期五。

后⾯的数字是从星期⽇开始算起。

1234567⽇⼀⼆
三四五六--------------------------------------------------------
-------select(sysdate-to_date(''2003-12-0312:55:45'',''yyyy-
mm-ddhh24:mi:ss''))246060fromddual⽇期返回的是天然后转换为ss24,roun
d[舍⼊到最接近的⽇期](day:舍⼊到最接近的星期⽇)selectsysdateS1,round(sysdate)S2
,round(sysdate,''year'')YEAR,round(sysdate,''month'')MONTH,rou
nd(sysdate,''day'')DAYfromdual25,trunc[截断到最接近的⽇期,单位为天],返回的是⽇期类
型selectsysdateS1,trunc(sysdate)S2,//返回当前⽇期,
⽆时分秒trunc(sysdate,''year'')YEAR,//返回当前年的1⽉1⽇,⽆时分秒trunc(s
ysdate,''month'')MONTH,//返回当前⽉的1⽇,⽆时分秒trunc(sysdate,''day'')
DAY//返回当前星期的星期天,⽆时分秒fromdual26,返回⽇期列表中最晚⽇期selectg
reatest(''01-1⽉-04'',''04-1⽉-04'',''10-2⽉-04'')fromdual27.计算时间差注:or
acle时间差是以天数为单位,所以换算成年⽉,⽇selectfloor(to_number(sysdate-to_date(
''2007-11-0215:55:03'',''yyyy-mm-ddhh24:mi:ss''))/365)asspanYears
fromdual//时间差-年selectceil(moths_between(sysdate-to_da
te(''2007-11-0215:55:03'',''yyyy-mm-ddhh24:mi:ss'')))asspanMonths
fromdual//时间差-⽉selectfloor(to_number(sysdate-to_date(
''2007-11-0215:55:03'',''yyyy-mm-ddhh24:mi:ss'')))asspanDaysfrom
dual//时间差-天selectfloor(to_number(sysdate-to_date(
''2007-11-0215:55:03'',''yyyy-mm-ddhh24:mi:ss''))24)asspanHours
fromdual//时间差-时selectfloor(to_number(sysdate-to_date(
''2007-11-0215:55:03'',''yyyy-mm-ddhh24:mi:ss''))2460)asspanMin
utesfromdual//时间差-分selectfloor(to_number(sysdate-to_date(
''2007-11-0215:55:03'',''yyyy-mm-ddhh24:mi:ss''))246060)asspan
Secondsfromdual//时间差-秒28.更新时间注:oracle时间加减是以天数为单位,设改变量为n,所以换算
成年⽉,⽇selectto_char(sysdate,''yyyy-mm-ddhh24:mi:ss''),to_char(sys
date+n365,''yyyy-mm-ddhh24:mi:ss'')asnewTimefromdual/
/改变时间-年selectto_char(sysdate,''yyyy-mm-ddhh24:mi:ss''),add_month
s(sysdate,n)asnewTimefromdual
//改变时间-⽉selectto_char(sysdate,''yyyy-mm-ddhh24:mi:ss''),to_char
(sysdate+n,''yyyy-mm-ddhh24:mi:ss'')asnewTimefromdual
//改变时间-⽇selectto_char(sysdate,''yyyy-mm-ddhh24:mi:ss''),to_ch
ar(sysdate+n/24,''yyyy-mm-ddhh24:mi:ss'')asnewTimefromdual
//改变时间-时selectto_char(sysdate,''yyyy-mm-ddhh24:mi:ss''),to_
char(sysdate+n/24/60,''yyyy-mm-ddhh24:mi:ss'')asnewTimefromdua
l//改变时间-分selectto_char(sysdate,''yyyy-mm-ddhh24:mi:ss''),t
o_char(sysdate+n/24/60/60,''yyyy-mm-ddhh24:mi:ss'')asnewTimefro
mdual//改变时间-秒29.查找⽉的第⼀天,最后⼀天SELECTTrunc(Trunc(SYSDATE,''MO
NTH'')-1,''MONTH'')First_Day_Last_Month,Trunc(SYSDATE,''MONTH'')
-1/86400Last_Day_Last_Month,Trunc(SYSDATE,''MONTH'')First_D
ay_Cur_Month,LAST_DAY(Trunc(SYSDATE,''MONTH''))+1-1/86400L
ast_Day_Cur_MonthFROMdual;三.字符函数(可⽤于字⾯字符或数据库列)1，字符串截取select substr(''abcdef'',1,3)fromdual2,查找⼦串位置selectinstr(''abcfdgfdhd
'',''fd'')fromdual3,字符串连接select''HELLO''||''helloworld''fromdual
;4,1)去掉字符串中的空格selectltrim(''abc'')s1,rtrim(''zhang'')s2,tri
m(''zhang'')s3fromdual2)去掉前导和后缀selecttrim(leading9from99
98767999)s1,trim(trailing9from9998767999)s2,trim(9from99
98767999)s3fromdual;5,返回字符串⾸字母的Ascii值selectascii(''a'')from
dual6,返回ascii值对应的字母selectchr(97)fromdual7,计算字符串长度selectl
ength(''abcdef'')fromdual8,initcap（⾸字母变⼤写）,lower(变⼩写),upper(变⼤写
)selectlower(''ABC'')s1,upper(''def'')s2,initcap(''efg'')s3fr。