Oracle知識綜合整理

1、SQL語言分類種類縮寫全稱常用操作數(shù)據(jù)定義語言DDLData deefine languuagecreate, alteer, drrop數(shù)據(jù)操縱語言DMLData Maanipullationn Langguageselect, inseert, ddeletee, upddate事務(wù)控制語言TCLTransacction Contrrol Laanguaggecommit, savaapointt, rolllbackk數(shù)據(jù)控制語言DCLData Coontroll Langguagegrant, revokkeSQL常用數(shù)據(jù)據(jù)類型分類關(guān)鍵字表示范圍備注字符char12000字字節(jié)固定長度

2、varcharr214000字字節(jié)可自增長長度long2GB可自增長長度數(shù)值number定義方式number(P,S)P:長度 S:精度日期date日期和時間部分分，精確到整整個的秒timestaamp存儲日期、時間間和時區(qū)信息息，秒值精確確到小數(shù)點(diǎn)后后6位RAWraw12000字字節(jié)存儲二進(jìn)制數(shù)據(jù)據(jù)long raaw2GB存儲二進(jìn)制數(shù)據(jù)據(jù)LOBclob4GB能夠存儲大量字字符數(shù)據(jù)blob4GB可以存儲較大的的二進(jìn)制對象象，如圖形、視頻剪輯和和聲音文件bfile4GB用于將二進(jìn)制數(shù)數(shù)據(jù)存儲在數(shù)數(shù)據(jù)庫外部的的操作系統(tǒng)文文件中Oracle中中的偽列Oracle 中偽列就像像一個表列，但但是它并沒有

3、有存儲在表中中偽列可以從表中中查詢，但不不能插入、更更新和刪除它它們的值常用的偽列有 ROWIDD 和ROWNUUMROWIDROWID是表表中行的存儲儲地址，該地地址可以唯一一地標(biāo)識數(shù)據(jù)據(jù)庫中的一行行，可以使用用ROWIDD偽列快速地地定位表中的的一行。ROWNUMROWNUM是是查詢返回的的結(jié)果集中行行的序號，可可以使用它來來限制查詢返返回的行數(shù)。命名規(guī)則Oracle中中的各種數(shù)據(jù)據(jù)對象，包括括表名稱，視視圖等等名稱稱的命名都需需要遵循Orracle的的命名規(guī)則。Oraclle的命名規(guī)規(guī)則分為標(biāo)準(zhǔn)準(zhǔn)命名方式和和非標(biāo)準(zhǔn)命名名方式。標(biāo)準(zhǔn)命名方式以字符打頭300個字符以內(nèi)內(nèi)只能包含AA-Z,a-

4、z,0-99,_,$和和#。不能和同一一個用戶下的的其他對象重重名，不能是orracle服服務(wù)器的保留留字。非標(biāo)準(zhǔn)命名方式式你可以使用你想想使用的任何何字符，包括括中文，orracle中中的保留字，空空格等等，但但是需要將對對象名用雙引引號引起來。例如： ccreatee tablle “tablee” (tesst1 vaarcharr2(10);創(chuàng)建表空間CREATE TABLEESPACEE tabllespaccenameeDATAFILLE fiilenamme SSIZE iintegeer K|MAUTOEXXTEND OFF|ON;注：大寫字母為為關(guān)鍵字，小小寫部分為用用戶自定

5、義部部分；中中的內(nèi)容為可可選擇部分。例：creatte tabblespaace myy_tabllespacce dataffile c:myyspacee sizee 100KK autoeextendd on;Oracle用用戶操作創(chuàng)建用戶CREATE USER usernnameIDENTIFFIED BBY passsworddDEFAULLT TABBLESPAACE taablesppaceTEMPORRARY TTABLESSPACE tableespacee;例：creatte useer scoott iddentiffied bby tigger;密碼修改ALTER UU

6、SER uusernaame IDDENTIFFIED BBY newwpasswword鎖定用戶ALTER UUSER uusernaame ACCCOUNTT LOCKK;撤銷鎖定ALTER UUSER uusernaame ACCCOUNTT UNLOOCK;刪除用戶DROP USSER ussernamme;權(quán)限管理Oracle權(quán)權(quán)限有兩種類類型，系統(tǒng)權(quán)權(quán)限和對象權(quán)權(quán)限。系統(tǒng)權(quán)限：系統(tǒng)統(tǒng)規(guī)定用戶使使用數(shù)據(jù)庫的的權(quán)限。（系統(tǒng)權(quán)限是是對用戶而言言）。實(shí)體權(quán)限：某種種權(quán)限用戶對對其它用戶的的表或視圖的的存取權(quán)限。（是針對表表或視圖而言言的）。系統(tǒng)權(quán)限管理系統(tǒng)權(quán)限分類DBA：擁有全全部特權(quán)，是

7、是系統(tǒng)最高權(quán)權(quán)限，只有DDBA才可以以創(chuàng)建數(shù)據(jù)庫庫結(jié)構(gòu)。RESOURCCE：擁有Ressourcee權(quán)限的用戶戶只可以創(chuàng)建建實(shí)體，不可可以創(chuàng)建數(shù)據(jù)據(jù)庫結(jié)構(gòu)。CONNECTT：擁有Connnect權(quán)權(quán)限的用戶只只可以登錄OOraclee，不可以創(chuàng)創(chuàng)建實(shí)體，不不可以創(chuàng)建數(shù)數(shù)據(jù)庫結(jié)構(gòu)。對于普通用戶：授予connnect, resoource權(quán)權(quán)限。對于DBA管理理用戶：授予予conneect,resouurce, dba權(quán)限限。系統(tǒng)權(quán)限授權(quán)系統(tǒng)權(quán)限只能由由DBA用戶戶授出：syys, syystem(最開始只能能是這兩個用用戶)授權(quán)命令：GRRANT cconnecct, reesourcce, d

8、bba TOuserrname;普通用戶通過授授權(quán)可以具有有與systtem相同的的用戶權(quán)限，但但永遠(yuǎn)不能達(dá)達(dá)到與syss用戶相同的的權(quán)限，syystem用用戶的權(quán)限也也可以被回收收。例：grantt connnect, resouurce tto scoott;系統(tǒng)權(quán)限傳遞增加WITH ADMINN OPTIION選項(xiàng)，則則得到的權(quán)限限可以傳遞。例：grantt connnect, resorrce too userr01 withh admiin opttion;系統(tǒng)權(quán)限回收REVOKE conneect, rresourrce FRROM ussernamme;實(shí)體權(quán)限管理實(shí)體權(quán)限分類s

9、elect, updaate, iinsertt, altter, iindex, deleete, aall等實(shí)體權(quán)限授權(quán)GRANT sselectt, upddate, inserrt ONtabllenameeTOuserrname;例：grantt all on emmp to scottt;實(shí)體權(quán)限傳遞例：user001:graant seelect, updaate onn prodduct tto useer02 wwith ggrant optioon;實(shí)體權(quán)限回收REVOKE selecct, uppdate ONtabllenameeFROMussernamme;角色管理角

10、色是一組權(quán)限限的集合，將將角色賦給一一個用戶，這這個用戶就擁擁有了這個角角色中的所有有權(quán)限。系統(tǒng)預(yù)定義角色色預(yù)定義角色是在在數(shù)據(jù)庫安裝裝后，系統(tǒng)自自動創(chuàng)建的一一些常用的角角色。角色所包含的權(quán)權(quán)限可以用以以下語句查詢詢：select * froom rolle_syss_privvs wheere roole=角色名;CONNECTT, RESSOURCEE, DBAA這些預(yù)定義角色色主要是為了了向后兼容。其主要是用用于數(shù)據(jù)庫管管理。oraacle建議議用戶自己設(shè)設(shè)計(jì)數(shù)據(jù)庫管管理和安全的的權(quán)限規(guī)劃，而而不要簡單的的使用這些預(yù)預(yù)定角色。DELETE_CATALLOG_ROOLE,EXECUUTE_

11、CAATALOGG_ROLEE,SELECCT_CATTALOG_ROLE這些角色主要用用于訪問數(shù)據(jù)據(jù)字典視圖和和包SNMPAGEENT用于oraclle entterpriise maanagerr和Inteelligeent AggentRECOVERRY_CATTALOG_OWNERR用于創(chuàng)建擁有恢恢復(fù)庫的用戶戶。關(guān)于恢復(fù)復(fù)庫的信息，參參考o(jì)raccle文檔Oraclle9i UUser-MManageed Bacckup aand Reecoverry GuiideHS_ADMIIN_ROLLEA DBA uusing Oraclles hheteroogeneoous seervic

12、ees feaature needss thiss rolee to aaccesss apprropriaate taables in thhe datta dicctionaary.管理角色建一個角色create role role11;授權(quán)給角色grant ccreatee any tablee,creaate prroceduure too rolee1;授予角色給用戶戶grant rrole1 to usser1;查看角色所包含含的權(quán)限select * froom rolle_syss_privvs;scott的權(quán)權(quán)限如圖：-查看scoott用戶具具有什么系統(tǒng)統(tǒng)權(quán)限select *

13、froom rolle_syss_privvs;-查看scoott用戶自自己擁有什么么角色select * froom useer_rolle_priivs;-查看scoott用戶自自己具有什么么的權(quán)限select * froom SESSSION_ROLESS;-查scottt用戶的創(chuàng)創(chuàng)建時間、用用戶狀態(tài)、使使用的默認(rèn)表表空間、臨時時表空間等信信息select * froom useer_useers;-查看scoott用戶中中，都哪些用用戶把對象授授予給scoott用戶select * froom useer_tabb_privvs;-查看scoott用戶中中擁有的reesourcce角色

14、都具具有什么權(quán)限限select * froom rolle_syss_privvs wheere roole=RRESOURRCE;-scottt用戶自己擁擁有多少表select * froom useer_tabbles;-查看scoott用戶已已經(jīng)使用多大大的空間，允允許使用的最最大空間是多多少select tableespacee_namee,bytees,maxx_bytees froom useer_ts_quotaas;-查看哪些表表什么權(quán)限賦賦予了其他用用戶select * froom useer_tabb_privvs_madde-把自己的表表賦予給其他他用戶grant sse

15、lectt on eemp too mzl;-把表的某一一列操作權(quán)限限賦予給其他他用戶grant uupdatee(job) on eemp too mzl;創(chuàng)建帶有口令以以角色(在生生效帶有口令令的角色時必必須提供口令令)create role role11 idenntifieed by passwword1;修改角色：是否否需要口令alter rrole rrole1 not iidentiified;alter rrole rrole1 identtifiedd by ppasswoord1;設(shè)置當(dāng)前用戶要要生效的角色色set rolle rolle1;/使rolee1生效set ro

16、lle rolle,rolle2;/使rolee1,rolle2生效set rolle rolle1 iddentiffied bby passswordd1;/使使用帶有口令令的rolee1生效set rolle alll;/使用用該用戶的所所有角色生效效set rolle nonne;/設(shè)設(shè)置所有角色色失效set rolle alll exceept roole1;/除rolle1外的該該用戶的所有有其它角色生生效select * froom SESSSION_ROLESS;/查看看當(dāng)前用戶的的生效的角色色修改指定用戶，設(shè)設(shè)置其默認(rèn)角角色alter uuser uuser1 defauul

17、t roole roole1;alter uuser uuser1 defauult roole alll exccept rrole1;刪除角色drop roole roole1;Oracle用用戶密碼過期期解決方法原因：由于orracle111g中默認(rèn)認(rèn)在defaault概要要文件中設(shè)置置了“PASSWWORD_LLIFE_TTIME=1180天”所導(dǎo)致。解決方案：1、查看用戶的的proiffle是哪個個，一般是ddefaullt：sqlSELLECT uusernaame,PRROFILEE FROMM dba_userss;2、查看指定概概要文件（如如defauult）的密密碼有效期設(shè)

18、設(shè)置：sqlSELLECT * FROMM dba_profiiles ss WHERRE s.pprofille=DEEFAULTT ANDD resoource_name=PASSSWORD_LIFE_TIME;3、將密碼有效效期由默認(rèn)的的180天修修改成“無限制”：sqlALTTER PRROFILEE DEFAAULT LLIMIT PASSWWORD_LLIFE_TTIME UUNLIMIITED;修改之后不需要要重啟動數(shù)據(jù)據(jù)庫，會立即即生效。數(shù)據(jù)定義語言DDDL數(shù)據(jù)定義語言用用于改變數(shù)據(jù)據(jù)庫結(jié)構(gòu)，包包括創(chuàng)建、更更改和刪除數(shù)數(shù)據(jù)庫對象。用于操縱表結(jié)構(gòu)構(gòu)的數(shù)據(jù)定義義語言命令有有：CRE

19、ATE TABLEEALTER TTABLETRUNCATTE TABBLEDROP TAABLE利用現(xiàn)有表創(chuàng)建建新表語法： CREEATE TTABLE AS SELECCT collumn_nnames FROM ;例如：CREAATE TAABLE nnewiteemfileeAS SEELECT * FROOM iteemfilee;創(chuàng)建新表語法：CREAATE TAABLE ( Colnaame daatatyppe deefaultt XXX PRIIMARY KEY,Colnamee1 dattatypee deffault XXX NOT NULL | NULLLcolname

20、eN dattatypee deffault XXX);例如：creaate taable sscott.mytabble(uidd numbber(5) primmary kkey,name vvarchaar2(100);修改表結(jié)構(gòu)編輯表的字段修改一個列的數(shù)數(shù)據(jù)類型(一一般限于修改改長度，修改改為一個不同同類型時有諸諸多限制)。語法：ALTEER TABBLE 表名名 MODIIFY(列名名,數(shù)據(jù)類型型);例如：alteer tabble skkate_ttest mmodifyy (autthor nnumberr(10,00) );增加一個列語法：ALTEER TABBLE 表名名 A

21、DD(列名,數(shù)據(jù)據(jù)類型);例如：ALTEER TABBLE skkate_ttest AADD(auuthor NUMBEER(38,0) noot nulll);給列改名語法：ALTEER TABBLE 表名名 DROPP COLUUMN 列名名;例如：alteer tabble skkate_ttest ddrop ccolumnn authhor;將一個表改名語法：ALTEER TABBLE 當(dāng)前前表名 REENAME TO 新表表名;例如：alteer tabble skkate_ttest rrenamee to ttest_ssakte;添加約束語法：ALTEER TABBLE 表

22、名名 ADD CONSSTRAINNTconsstrainnt_namme 約束束條件(列名名) ；例如：alteer tabble scc add consttraintt sc_ffk_couurse fforeiggn keyy(cid) refeerencees couurse(ccid);/添加外鍵鍵alter ttable studeent addd connstraiint ckk_studdent_sssex ccheck(ssex in(男男,女);/添加cheeck約束刪除約束語法：ALTEER TABBLE 表名名 CONSSTRAINNTconsstrainnt_nam

23、me；例如：alteer tabble sttudentt dropp consstrainnt ck_studeent_saage;截斷表使用TRUNCCATE命令令可以刪除表表中所有數(shù)據(jù)據(jù)，且不能回回滾。語法：TRUNNCATE TABLEE tabllenamee;刪除表語法：DROPP TABLLE tabblenamme;數(shù)據(jù)操縱語言DDML數(shù)據(jù)操縱語言用用于檢索、插插入和修改數(shù)數(shù)據(jù)數(shù)據(jù)操縱語言是是最常見的SSQL命令數(shù)據(jù)操縱語言命命令包括：SSELECTT、INSERRT、UPDATTE、DELETTE增insertt語法：INSEERT INNTO taable_nname (列

24、1, 列2,) VVALUESS (值1, 值2,);例如：INSEERTINTTOs_deeptVALLUES (11,FFrinacce,2);INSERTIINTOs_deptss_emp(id,last_name,firstt_namee,salaary,sttart_ddate)VALUES (11,Frinaace,Jon,15600,03-4月月-92);INSERTIINTO hhistorrySELEECTid,last_name,salarryFROMMs_empp WHHEREsttart_ddate UUPDATEE ordeer_massterSEET dell_da

25、tee =30-8月月-05WHERE oordernno SSAVEPOOINT mmark1;SQL DDELETEE FROMM ordeer_masster WWHERE orderrno = o0022;SQL SSAVEPOOINT mmark2;SQL RROLLBAACK TOO SAVEEPOINTT markk1;SQL CCOMMITT;數(shù)據(jù)控制語言DDCL數(shù)據(jù)控制語言為為用戶提供權(quán)權(quán)限控制命令令 用于權(quán)限控制的的命令有：GRANT - 授予權(quán)限限REVOKE - 撤銷已授授予的權(quán)限例如：SQLGRANTT SELEECT ONN venddor_maasterTTO a

26、cccountss WITHH GRANNT OPTTION;SQLREVVOKE SSELECTT, UPDDATE OON ordder_maasterFFROM MMARTINN;PL/SQL基本語法：DECLARRE 聲聲明部分declaraationssBEGIN 執(zhí)行部分分executaable sstatemmentsEXCEPTTION 異常捕捕獲部分 handlerrsEND;聲明變量和常量量的語法：1.使用賦值語語句 :=2.使用 SEELECT INTO 語句例如：declaree var_nname vvarchaar(1000);-變變量的定義 var_jjob va

27、archarr(100); var_iint innt; var_cconst consttant vvarchaar(1000) :=;-);-常量的的定義 var_ddate eemp.hiiredatte%typpe;-引引用變量類型型 var_rrowtyppe empp%rowttype;提供表示示表中一行的的記錄類型 var_bbooleaan booolean;-布爾類類型,值:TTRUE,FFALSE,nullbeginselect * intto varr_rowttype ffrom eemp whhere eename=upperr(var_name);end;控制結(jié)構(gòu)P

28、L/SQL 支持的流程程控制結(jié)構(gòu)：條件控制IF 語句CASE 語句句循環(huán)控制LOOP 循環(huán)環(huán)WHILE 循循環(huán)FOR 循環(huán)順序控制GOTO 語句句NULL 語句句條件控制IF語句IF 語句根據(jù)據(jù)條件執(zhí)行一一系列語句，有有三種形式：IF-THHEN、IFF-THENN-ELSEE 和 IFF-THENN-ELSIIF例如：declaree varr_sal emp1.sal%ttype;begin sellect ssal innto vaar_sall fromm emp11 wherre emppno=73369; if var_ssal=22000 tthen updatte empp1

29、sett sal=sal*11.05 wwhere empnoo=73699; elssif vaar_sall=10000 andd var_sal22000 tthen updatte empp1 sett sal=sal*11.1 whhere eempno=7369; elsse updatte empp1 sett sal=sal*11.2 whhere eempno=7369; endd if;end;CASE語句CASE 語句句用于根據(jù)單單個變量或表表達(dá)式與多個個值進(jìn)行比較較。執(zhí)行 CASEE 語句前，先先計(jì)算選擇器器的值。例如：begin casse &ggrade wheen

30、 A thenn dbmss_outpput.puut_linne(優(yōu)異異); wheen B thenn dbmss_outpput.puut_linne(優(yōu)秀秀); wheen C thenn dbmss_outpput.puut_linne(良好好); wheen D thenn dbmss_outpput.puut_linne(一般般); wheen E thenn dbmss_outpput.puut_linne(較差差); elsse dbmms_outtput.pput_liine(無無成績); endd casee;end;循環(huán)控制LOOP循環(huán)語法：whille loopp.

31、eend looop;exit 終止止當(dāng)前循環(huán) exit whhen 條件滿滿足則終止當(dāng)當(dāng)前循環(huán)例如：begin LLOOP dbmss_outpput.puut_linne(哈哈哈); EEND LOOOP;end;WHILE循環(huán)環(huán)例如：declaree vvar_coount nnumberr(10):=0;begin wwhile var_ccountvallue2;rreversse表示vaalue2-valuue1例如：declaree vvar_i numbeer(10):=1; vvar_j numbeer(10):=1;begin ffor vaar_i iin 1.9 loo

32、op foor varr_j inn 1.99 loopp DBMMS_OUTTPUT.pput(vaar_j|*|var_ii|=|varr_i*vaar_j| ); exiit wheen varr_j=vaar_i; ennd looop; DBBMS_OUUTPUT.put_lline( ); eend looop;end;順序控制GOTO 語句句NULL語句goto - 無條件地轉(zhuǎn)轉(zhuǎn)到標(biāo)簽指定定的語句null -什什么也不做的的空語句例如：declaree vvar_a numbeer(2):=1;begin iif varr_a5 then gotto labbel1; eelse

33、 gotto labbel2; eend iff; DDBMS_OOUTPUTT.put_line(labeel1); nnull;end;動態(tài)SQL動態(tài) SQL 是指在PLL/SQL程程序執(zhí)行時生生成的 SQQL 語句。語法：execcute iimmediiate ddynamiic_sqll_striing INTO defiine_vaariablle_lisst USINGG bindd_arguument_list;例如：decllarevar_sqll varcchar2(200);var_emppno nuumber(4);var_empp emp%rowtyype;begin

34、 var_eempno:=&員工編編號; varr_sql := sselectt *froom empp1 wheere emmpno=:arg1 and ssal:aarg2; exeecute immeddiate var_ssql innto vaar_empp usinng varr_empnno,1000; DBMMS_OUTTPUT.pput_liine(vaar_empp.job);end;異常處理異常有兩種類型型：預(yù)定義異常 - 當(dāng) PLL/SQL 程序違反 Oraclle 規(guī)則或或超越系統(tǒng)限限制時隱式引引發(fā)用戶定義異常 - 用戶可可以在 PLL/SQL 塊的聲明部部分定義異

35、常常，自定義的的異常通過 RAISEE 語句顯式式引發(fā)預(yù)定義異常例如如：declareevar_id varchhar2(55);begin sellect eempno into var_iid froom empp1; sellect eempno into var_iid froom empp1 wheere emmpno=11111; DBMMS_OUTTPUT.pput_liine(我我被正常執(zhí)行行啦!);exceptiion wheen tooo_manyy_rowss thenn DBMMS_OUTTPUT.pput_liine(返返回太多行); wheen NO_DATA_FO

36、UNDD thenn DBMMS_OUTTPUT.pput_liine(查查詢未找到數(shù)數(shù)據(jù));end;用戶定義異常常例如：DECLAREE invallidCATTEGORYY EXCEEPTIONN; categgory VVARCHAAR2(100);BEGIN categgory := &CCategoory; IF caategorry NOTT IN (附件,頂蓋,備件) THENN RAIISE innvaliddCATEGGORY; ELSE DBMMS_OUTTPUT.PPUT_LIINE(您您輸入的類別別是| categgory); END IIF;EXCEPTIION WH

37、EN invallidCATTEGORYY THENN DBMMS_OUTTPUT.PPUT_LIINE(無無法識別該類類別);END;游標(biāo)游標(biāo)的類型有：1.隱式游游標(biāo) 2.顯顯式游標(biāo) 33.REF游游標(biāo)(REFF 游標(biāo)用于于處理運(yùn)行時時才能確定的的動態(tài) SQQL 查詢的的結(jié)果)隱式游標(biāo)在PL/SQLL中使用DMLL語句時自動動創(chuàng)建隱式游游標(biāo)，隱式游標(biāo)自自動聲明、打打開和關(guān)閉，其其名為 SQQL。通過檢查隱隱式游標(biāo)的屬屬性可以獲得得最近執(zhí)行的的DML 語句句的信息。隱式游標(biāo)屬性：%found - SQLL 語句影響響了一行或多多行時為 TTRUE%notfouund - SQL 語語句沒有影響

38、響任何行時為為 TRUEE%rowcouunt - SQL 語語句影響的行行數(shù)例如：declareevar_emppno emmp.emppno%tyype;BEGIN selecct emppno innto vaar_emppno frrom emmp wheere emmpno=77900;IF SQLL%FOUNND THEEN DBMMS_OUTTPUT.PPUT_LIINE(已已查找到|sql%rowcoount|條數(shù)據(jù)!);END IFF;EXCEPTIIONWHEN NNO_DATTA_FOUUND THHEN DBMSS_OUTPPUT.PUUT_LINNE(職員員未找到);

39、end;顯示游標(biāo)當(dāng)查詢返回結(jié)果果超過一行時時，就需要一個個顯式游標(biāo)。此時用戶不不能使用seelect into語語句。PL/SQL管理理隱式游標(biāo)，當(dāng)當(dāng)查詢開始時時隱式游標(biāo)打打開，查詢結(jié)結(jié)束時隱式游游標(biāo)自動關(guān)閉閉。顯式游標(biāo)標(biāo)在PL/SSQL塊的聲聲明部分聲明明，在執(zhí)行部部分或異常處處理部分打開開，取數(shù)據(jù)，關(guān)關(guān)閉。聲明游標(biāo)語法： CURRSOR ccursorr_namee IS sselectt_stattementt;打開游標(biāo)使用游標(biāo)中的值值之前應(yīng)該首首先打開游標(biāo)標(biāo)，打開游標(biāo)標(biāo)初始化查詢詢處理。打開開游標(biāo)的語法法是： OPEEN currsor_nname (curssor_naame是在聲聲

40、明部分定義義的游標(biāo)名。)關(guān)閉游標(biāo) 語法： CLLOSE ccursorr_namee從游標(biāo)提取數(shù)據(jù)據(jù) 從游標(biāo)標(biāo)得到一行數(shù)數(shù)據(jù)使用FEETCH命令令。每一次提提取數(shù)據(jù)后，游游標(biāo)都指向結(jié)結(jié)果集的下一一行。語法如如下： FEETCH ccursorr_namee INTOO variiable,variiable,.例如：declaree varr_enamme empp.enamme%typpe; currsor eemp_cuur is -聲明明游標(biāo) sselectt enamme froom empp wherre jobb=uppeer(&jjob);begin opeen empp_cur

41、; -打開游游標(biāo) looop fettch emmp_curr intoo var_enamee; -提提取行 exiit wheen empp_cur%notfoound; DBMMS_OUTTPUT.pput_liine(vaar_enaame); endd loopp; cloose emmp_curr; -關(guān)閉游游標(biāo)end; 帶參數(shù)的游標(biāo)與存儲過程和函函數(shù)相似，可可以將參數(shù)傳傳遞給游標(biāo)并并在查詢中使使用。這對于于處理在某種種條件下打開開游標(biāo)的情況況非常有用。它的語法如如下：CURSORcursoor_namme(paarametter,pparameeter,.)ISsselectt_

42、stattementt;定義參數(shù)的語法法如下：Parametter_naameIINdaata_tyype:=|DEFFAULTvaluue 與存儲過程不同同的是，游標(biāo)標(biāo)只能接受傳傳遞的值，而而不能返回值值。參數(shù)只定定義數(shù)據(jù)類型型，沒有大小小。另外可以給參數(shù)數(shù)設(shè)定一個缺缺省值，當(dāng)沒沒有參數(shù)值傳傳遞給游標(biāo)時時，就使用缺缺省值。游標(biāo)標(biāo)中定義的參參數(shù)只是一個個占位符，在在別處引用該該參數(shù)不一定定可靠。在打開游標(biāo)時給給參數(shù)賦值，語語法如下：OPENcuursor_namevaluee,vallue;參數(shù)值可以是文文字或變量。例如：DECLAREEvar_jjob emp.job%ttype;var_e

43、empno emp.eempno%type;var_eename empp.enamme%typpe;CURSOOR empp_cur(paramm_job emp.jjob%tyype) IIS SELEECT emmpno, enamee FROMM emp WHERRE jobb=paraam_jobb;BEGINvar_jjob:=uupper(&jobb);OPEN emp_ccur(vaar_jobb);LOOPFETCCH empp_cur INTO var_eempno,var_eename;EXITT WHENN emp_cur%NNOTFOUUND;DBMSS_OUTPPU

44、T.PUUT_LINNE(varr_empnno|, |vvar_enname); ENDD LOOPP;CLOSEE emp_cur;END;游標(biāo)中的更新和和刪除在PL/SQLL中依然可以以使用UPDDATE和DDELETEE語句更新或或刪除數(shù)據(jù)行行。顯式游標(biāo)標(biāo)只有在需要要獲得多行數(shù)數(shù)據(jù)的情況下下使用。PLL/SQL提提供了僅僅使使用游標(biāo)就可可以執(zhí)行刪除除或更新記錄錄的方法。語法：CURSOR IIS FOR UPDATTE OF ;UPDATE SET WHERE CCURRENNT OF DELETE FROM WHERE CCURRENNT OF 例如：declaree vvar_sa

45、al empp1.sall%typee; ccursorr emp_cur iis seleect saal froom empp1 wheere saal20000 forr updaate off sal;begin oopen eemp_cuur; lloop ffetch emp_ccur innto vaar_sall; eexit wwhen eemp_cuur%nottfoundd; uupdatee emp11 set sal=vvar_saal*1.11 wherre currrent of emmp_curr; eend looop; cclose emp_ccur;end

46、;循環(huán)游標(biāo)循環(huán)游標(biāo)用于簡簡化游標(biāo)處理理代碼，當(dāng)用用戶需要從游游標(biāo)中提取所所有記錄時使使用。語法：FOR IN LOOPEND LOOOP;例如：declaree currsor emp_ccur iss selecct enaame frrom emmp;begin forr emp_rec iin empp_cur looop DBMSS_OUTPPUT.PUUT_LINNE(empp_rec.enamee); endd loopp;end;REF游標(biāo)REF 游標(biāo)和和游標(biāo)變量用用于處理運(yùn)行行時動態(tài)執(zhí)行行的 SQLL 查詢。創(chuàng)建游標(biāo)變量需需要兩個步驟驟：1.聲明 REEF 游標(biāo)類類型2.聲明

47、REEF 游標(biāo)類類型的變量用于聲明 REEF 游標(biāo)類類型的語法為為：TYPE IS REF CCURSORRRETURRN ;打開游標(biāo)變量的的語法如下： OPEN cursoor_namme FORR seleect_sttatemeent;聲明強(qiáng)類型的 REF 游游標(biāo)(有返回回值)：TYPE myy_cur IS REEF CURRSOR RETURRN empp%ROWTTYPE;var_curr mmy_curr; 聲明弱類型的 REF 游游標(biāo)：TYPE myy_cur IS REEF CURRSOR;var_curr my_cur;例如：declaree tyype myy_cur i

48、s reef currsor;聲明游標(biāo)類型型 vaar_empp emp%rowtyype; emmp_curr my_ccur;申明游標(biāo)變變量begin oppen emmp_curr for selecct *frrom emmp;-打打開動態(tài)SQQL語句 looop feetch eemp_cuur intto varr_emp; exxit whhen emmp_curr%notffound; DBBMS_OUUTPUT.PUT_LLINE(vvar_emmp.enaame); ennd looop; cllose eemp_cuur;end;子程序命名的 PL/SQL 塊塊，編譯并存

49、存儲在數(shù)據(jù)庫庫中。子程序的各個部部分：聲明部分可執(zhí)行部分異常處理部分(可選)子程序的分類：過程 執(zhí)行行某些操作函數(shù) 執(zhí)行行操作并返回回值過程過程是用于完成成特定任務(wù)的的子程序。創(chuàng)建過程的語法法：-創(chuàng)建過程，可可指定運(yùn)行過過程需傳遞的的參數(shù)CREATE OR RREPLACCE PRROCEDUURE ()IS|AS BEGIN-包括在過程程中要執(zhí)行的的語句 -處理異常(可選)EXCEPTTION END;例如：create or reeplacee proccedureeget_emppno(teemp_noo emp.empnoo%typee) isvar_empp emp%rowtyype

50、;beginselect * intto varr_emp from emp wwhere empnoo=tempp_no; dbmss_outpput.puut_linne(varr_emp.enamee);exceptiion WHENN NO_DDATA_FFOUND THEN dbmss_outpput.puut_linne(無此此人信息!); end;過程參數(shù)的三種種模式：IN用于接受調(diào)用程程序的值默認(rèn)的參數(shù)模式式OUT用于向調(diào)用程序序返回值 IN OUT用于接受調(diào)用程程序的值，并并向調(diào)用程序序返回更新的的值例如：create or reeplacee proccedureeswap

51、(teemp1 iin outt numbber,teemp2 iin outt numbber)istemp nuumber;begin teemp:=ttemp1; teemp1:=temp22; teemp2:=temp;end;測試declaree a numbeer(2):=1; b numbeer(2):=2;begin dbbms_ouutput.put_lline(a=|a); dbbms_ouutput.put_lline(b=|b); swwap(a,b); dbbms_ouutput.put_lline(a=|a); dbbms_ouutput.put_lline(b=|

52、b); end;函數(shù)函數(shù)是可以返回回值的命名的的 PL/SSQL 子程程序。創(chuàng)建函數(shù)的語法法：CREATE OR RREPLACCE FUUNCTIOON (paaram1,paramm2)RETURN IS|AS locaal decclarattionsBEGIN Execuutablee Stattementts; RETURRN ressult;EXCEPTIION Excepption handllers;END;定義函數(shù)的限制制：函數(shù)只能接受 IN 參數(shù)數(shù)，而不能接接受 IN OUT 或或 OUT 參數(shù)形參不能是 PPL/SQLL 類型函數(shù)的返回類型型也必須是數(shù)數(shù)據(jù)庫類型訪問函數(shù)的

53、兩種種方式：使用 PL/SSQL 塊使用 SQL 語句例如：create or reeplacee funcctionmy_funreturn varchhar2 iisbeginreturn 哈哈;end;測試試begin dbbms_ouutput.put_lline(mmy_funn); end;程序包程序包是對相關(guān)關(guān)過程、函數(shù)數(shù)、變量、游游標(biāo)和異常等等對象的封裝裝。程序包由規(guī)范和和主體兩部分分組成規(guī)范：聲明程序序包中公共對對象。包括類類型、變量、常量、異常常、游標(biāo)規(guī)范范和子程序規(guī)規(guī)范等。主體：聲明程序序包私有對象象和實(shí)現(xiàn)在包包規(guī)范中聲明明的子程序和和游標(biāo)。語法：程序包規(guī)范：CREATE

54、 OR RREPLACCEPACCKAGE IS|ASSPublicc itemm decllaratiionsSubproogram speciificattionEND paackagee_namee;程序包主體：CREATE OR RREPLACCE PAACKAGEE BODYY IS|AASPrivatte iteem decclarattionsSubproogram bodieesBEGINInitiallizatiionEND paackagee_namee;例如：-定義程序序包規(guī)范create or reeplacee packkage mmy_pacckage isproc

55、eduure prrint;functioon priint_sttr(strr varcchar2) retuurn vaarcharr2;end my_packaage;-定義程序包包主體create or reeplacee packkage bbody mmy_pacckage is proceedure printt is beginn ddbms_ooutputt.put_line(哈哈); end pprint; functtion printt_str(str vvarchaar2) rreturnn varcchar2 is var_sstr vaarcharr2(1000)

56、; beginn vvar_sttr:=sttr; rreturnn strr; end pprint_str;end my_packaage;-測試begin my_paackagee.prinnt; dbms_outpuut.putt_linee(my_ppackagge.priint_sttr(o)/);end;程序包中的游標(biāo)標(biāo)游標(biāo)的定義分為為游標(biāo)規(guī)范和和游標(biāo)主體兩兩部分。在包規(guī)范中聲明明游標(biāo)規(guī)范時時必須使用 RETURRN 子句指指定游標(biāo)的返返回類型。RETURN子子句指定的數(shù)數(shù)據(jù)類型可以以是：用 %ROWTTYPE 屬屬性引用表定定義的記錄類類型程序員定義的記記錄類型例如：-程序包規(guī)

57、范范CREATE OR REEPLACEE PACKKAGE ccur_paack ISS CURSOOR ordd_cur(vcodee VARCCHAR2) RETTURN oorder_masteer%ROWWTYPE; PROCEEDURE ord_ppro(vccode VVARCHAAR2);END curr_packk;-程序包主體體CREATE OR REEPLACEE PACKKAGE BBODY ccur_paack ASS CURSORR ord_cur(vvcode VARCHHAR2) RETURNN ordeer_masster%RROWTYPPE IS SELEC

58、TT * FRROM orrder_mmasterr WHERRE VENNCODE=vcodee; PROCEDDURE oord_prro(vcoode VAARCHARR2) ISS or_rrec orrder_mmasterr%ROWTTYPE; BEGIN OPEN ord_ccur(vccode); LOOPFETCH oord_cuur INTTO or_rec;EXIT WHHEN orrd_curr%NOTFFOUND; DBMMS_OUTTPUT.PPUT_LIIne(返回的值為為 | or_reec.ordderno);END LOOOP;END ordd_pro;EN

59、D curr_packk;觸發(fā)器觸發(fā)器是當(dāng)特定定事件出現(xiàn)時時自動執(zhí)行的的存儲過程。特定事件可以是是執(zhí)行更新的的DML語句句和DDL語語句觸發(fā)器不能被顯顯式調(diào)用觸發(fā)器的功能：自動生成數(shù)據(jù)自定義復(fù)雜的安安全權(quán)限提供審計(jì)和日志志記錄啟用復(fù)雜的業(yè)務(wù)務(wù)邏輯觸發(fā)器由三部分分組成：觸發(fā)器語句（事事件）定義激活觸發(fā)器器的 DMLL 事件和 DDL 事事件觸發(fā)器限制執(zhí)行觸發(fā)器的條條件，該條件件必須為真才才能激活觸發(fā)發(fā)器觸發(fā)器操作（主主體）包含一些 SQQL 語句和和代碼，它們們在發(fā)出了觸觸發(fā)器語句且且觸發(fā)限制的的值為真時運(yùn)運(yùn)行語法create OR RREPLACCE trriggerr after | bef

60、oore | insteead offinsertt oor uppdate of ccolumnn_listt or ddeleteeon refereencingg oldd as old / neww AS newfor eaach roowwhen (condiition)例如create or reeplacee triggger ttrig_ssal afterr updaate off sal on emmp1 for eeach rrow when(new.ssalolld.sall)declaree sall_difff numbber;begin sall_difff:=: