PLSQL教材培訓(xùn)課程

1、PL/SQL培訓(xùn)教程1.0文檔作者創(chuàng)建日期確認日期控制編碼當(dāng)前版本目錄目錄2UNIT1SQL、PL/SQL概要1.單元培訓(xùn)目標(biāo)1.LESSON1SQL、SQL*Plus和PL/SQL基本本既念1.UNIT2數(shù)據(jù)查詢3單元培訓(xùn)目標(biāo)3.LESSON1簡單的數(shù)據(jù)查詢3LESSON2SQL*Plus的使用.5.LESSON3單行函數(shù)的使用6LESSON3多表數(shù)據(jù)查詢7.LESSON4組函數(shù)8LESSON5子查詢9UNIT3數(shù)據(jù)庫1.Q單元培訓(xùn)目標(biāo)10.LESSON1系統(tǒng)開發(fā)周期和數(shù)據(jù)關(guān)聯(lián)11.LESSON2創(chuàng)建表、序列、視圖和索引1.2.LESSON3Oracle數(shù)據(jù)字典15.LESSON4用戶訪問控

2、制15.UNIT4PL/SQL1.8.單元培訓(xùn)目標(biāo)18.LESSON1簡單PL/SQL1.8.LESSON2數(shù)據(jù)獲取和游標(biāo).24.LESSON3PL/SQL控制流25.LESSON4出錯處理25.附錄A:26.數(shù)據(jù)結(jié)構(gòu):26.描述：26.UNIT1SQL、PL/SQL概要單元培訓(xùn)目標(biāo)u了解SQL、PL/SQL的基本概念、功能u了解Oracle產(chǎn)品集和關(guān)聯(lián)u了解SQL、PL/SQL的基本命令LESSON1SQL、SQL*Plus和PL/SQL基本概念SQL、SQL*Plus和PL/SQL是用來檢索和維護存儲在Oracle數(shù)據(jù)庫上數(shù)據(jù)的工具或語言。uOracle是一個對象一關(guān)系型數(shù)據(jù)庫管理系統(tǒng)(O

3、RDBMS),它擴展了普通關(guān)系型數(shù)據(jù)庫管理方式，支持面向?qū)ο蟮母拍?，以表的形式存儲信息，維護數(shù)據(jù)的輸入、數(shù)據(jù)的存儲和數(shù)據(jù)查詢并處理及對數(shù)據(jù)的三種基本操作：采集、存儲、檢索。uSQL:結(jié)構(gòu)化查詢語言(StructuredQueryLanguage),用來與Oracle數(shù)據(jù)庫服務(wù)器進行交互。例：selectname,salaryfromt_empselectsysdatefromdualuPL/SQL:Oracle擴展的SQL語言，具有完整的流程控制定義。uSQL*Plus:Oracle常用的工具，用來識別SQL語言和編寫、執(zhí)行PL/SQL代碼,它與Oracle數(shù)據(jù)庫管理系統(tǒng)緊密結(jié)合。uSQL*P

4、lus環(huán)境登錄：(1) windows環(huán)境(2) 命令行環(huán)境如下格式：Sqlplususername/passworddatabaseusername數(shù)據(jù)庫用戶名password數(shù)據(jù)庫的密碼，此時是可見的database要登錄的數(shù)據(jù)庫名一旦成功登錄，系統(tǒng)提示如下：SQL>uSQL常用命令:分類命令數(shù)據(jù)檢索Select數(shù)據(jù)維護(DML)insert、update、delete數(shù)據(jù)定義(DDL)create>drop、alert、rename、truncate事務(wù)處理控制commit、rollback、savepoint數(shù)據(jù)制(DCL)Grant、revokeu數(shù)據(jù)、操彳系統(tǒng)、Orac

5、le數(shù)據(jù)庫管理器、SQL和PL/SQL及工具之間的關(guān)系u在Oracle的產(chǎn)品中有如下的關(guān)系：以數(shù)據(jù)為核心，OracleServer結(jié)合操作系統(tǒng)進行數(shù)據(jù)傳遞和通信，外圍有SQL*Plus等工具提供給用戶，通過SQL、PL/SQL與OracleServer交互。(如下圖：)u代碼、縮寫和命名標(biāo)準(zhǔn)：由于SQL與自然語的相似性，同時為提高代碼的可讀性，對于檢索語句、表列命名和程序代碼應(yīng)采用標(biāo)準(zhǔn)規(guī)則，表名和列名既要反映數(shù)據(jù)的實際意義，又要考慮簡潔性、完整性。Oracle允許使用最多30個字符命名表和列，為建立完整的、含義明確的、描述性的名稱提供了足夠的空間。對于程序代碼，更要講究規(guī)范性，如變量、函數(shù)名等

6、等，要含義明確，提高程序的可讀性。orQcracle8PL/SQL具一具一/、應(yīng)用產(chǎn)品用UNIT2數(shù)據(jù)查詢單元培訓(xùn)目標(biāo)u理解Oracle數(shù)據(jù)查詢的概念和作用；u掌握數(shù)據(jù)查詢的基本方法；u通過函數(shù)、子查詢的學(xué)習(xí)，能寫一些較復(fù)雜的數(shù)據(jù)查詢。LESSON1簡單的數(shù)據(jù)查詢?基本結(jié)構(gòu)：SELECTdistinct*,columnalias,FROMTABLESELECT:選擇要查詢的數(shù)據(jù)列FROM:數(shù)據(jù)列所在的表*:選擇所有的列COLUMN:選擇的列名ALIAS:給選定的列一個別名例：SELECT*FROMs_deptSELECTlast_name,first_name,addressFROMs_emp

7、算術(shù)表達式：加：+減：-乘：*除：/例：SELECTlast_name,salary*12,base_salary+salaryFROMtablename字符串處理字符串可以做最簡單的“加”法，即合并運算：|例：SELECTcolumn1|.'|column2|$FROMtablename注：columnl、column2必須位字符型數(shù)據(jù)列SELECTfirst_name|la'st_name|,|'title"Employees"FROMs_emp空處理NVL:在數(shù)據(jù)列的值為空時(并非為零)，以真實的值進行替代處理例：NVL(start_date,

8、sysdate)NVL(name,noname)NVL(base_salary,2000)注：任何未被聲明為NOTNULL或PRIMARYKEY的列都可包含空值，注意，空值就是空值，并不是值為零或空格，零是一個數(shù)值，而空格是一個字符。消除重復(fù)行：例：SELECTitem_idfromt_onhandSELECTDISTINCTitem_idFROMt_onhand選者多列非重復(fù)：例：SELECTDISTINCTwarehoused_id,item_idFROMt_onhand顯示表結(jié)構(gòu)：格式：DESCRIBEtablenameOracle常用數(shù)據(jù)類型：number(p,s):浮點類型，最大精度

9、p,小數(shù)位s位varchar2(s):可變長字符類型，最大長度s位date:日期型char(s):定長字符類型，長度為s例：SQL>descs_itemNameNullTypeitem_idNOTNULLnumberitem_nameNOTNULLvarchar2(20)base_pricenumber,descriptionvarchar2(100)class_idnumberLESSON2SQL*Plus的使用SQL*Plus的使用:I.文件命令例:a) SAVE filenameb) GET filenamec) START filenamed) filenamee) EDIT f

10、ilename件 afiedt.buff) SPOOL filenameg) EXITII.文本編輯命令把當(dāng)前SQL緩沖區(qū)的內(nèi)容存儲在文件filename中把文件filename中的內(nèi)容寫入當(dāng)前SQL緩沖區(qū)執(zhí)行存儲在filename中的內(nèi)容執(zhí)行存儲在filename中的內(nèi)容打開文本編輯器，把當(dāng)前SQL緩沖區(qū)的內(nèi)容寫入文把查詢的數(shù)據(jù)結(jié)果存儲在filename中退出 SQL*Plusa) APPEND textb) CHANGE/old/newc) CLEARbuffERd) DELe) INPUT textf) LIST ng) N textIII. 格式輸出a) 格式:COLUMN colum

11、n|aliasoption b) 參數(shù)選項：CLEAR、FORMATformat 、 JUSTTIFYalign 、NOPRINT TRUNCATED 、WEAPPENDHEADINGtext 、NULLtext 、PRINT、WORD_WRAPPEDCOLUMN last_name HEADING employee|name ' FORMAT A15COLUMN salary JUSTIFY LEFT FORMAT $99,999.00COLUMN start_date FORMAT A9 NULL Not hired '數(shù)據(jù)控制輸出SELECTexprFROMtableWH

12、EREcondition(s)ORDERBYexpr;邏輯比較=、>=、<=、>、<betweenand、in、like、isnulland、or、not例：SELECTlast_name,employee_idFROMt_empWHEREstart_date>to_date(2001-01-01,yyyy-mm-dd)Andlast_namelike%H%LESSON3單行函數(shù)的使用Function_name(column|expression,arg1,arg2,)?字符函數(shù)LOWER(col|expr)、UPPER(col|expr)、INITCAP(col

13、|expr)CONCAT(col|expr1,col|expr2)、SUBSTR(col|expr,m,n)LENGTH(col|expr)NVL(col|expr1,col|exp?數(shù)值函數(shù)ROUNDTRUNCMOD注意：round和trunc函數(shù)的不同使用，如下：例：round(23535.7476,2)=>23535.75round(23535.7476,0)=>23536round(23535.7476,-1)=>23530trunc(23535.7476,2)=>23535.74trunc(23535.7476)=>23535trunc(23535,-1

14、)=>23530日期函數(shù)MONTH_BETWEEN、ADD_MONTHS、NEXT_DAYLAST_DAY、ROUND、TRUNC日期函數(shù)有其特性：例：add_months(to_date(2001-05-01,yyyy-mm-dd'),2)=>2001-07-01add_months(to_date(2001-05-01,yyyy-mm-dd'),-2)=>2001-03-01兩個日期不可相加，但可做減法，結(jié)果為number型，日期型數(shù)據(jù)加/減數(shù)值型數(shù)據(jù)結(jié)果為日期型，日期型數(shù)據(jù)間不可做乘除法，其他運算借助于日期函數(shù)。?轉(zhuǎn)換函數(shù)TO_NUMBER(number

15、/date,fmt'')TO_NUMBER(char)TO_DATE(char,fmt')轉(zhuǎn)換函數(shù)即用來變換數(shù)據(jù)的類型，如把字符串按格式轉(zhuǎn)換成日期型、數(shù)值型，八數(shù)值型、日期型轉(zhuǎn)換成字符串等等。在to_date的函數(shù)中，注意fmt'的使用，區(qū)別"yyyy'和RRRR,RR,Vy'等。?單行函數(shù)使用的例子：例：SELECTlast_name|.|first_name,employee_id,manager_idFROMs_empWHEREtrunc(start_date)=to_date(2001-05-12',yyyy-mm-dd

16、)Andlength(last_name)<10LESSON3多表數(shù)據(jù)查詢多表數(shù)據(jù)查詢的首要條件是如何把多個數(shù)據(jù)表按一定的規(guī)則連接起來，從而獲取多個表的、組織好的信息。連接的方式有多種，可按不同的要求進行選擇。連接SELECTtable.column,table.columnFROMtable1,table2WHEREtable1.column1=table2.colmn2;表別名SELECTT1.column,T2.columnFROMtable1T1,table2T2WHERET1.column1=T2.colmn2;非等號連接即不使用等號來連接兩個或兩個以上的表例如：使用：>

17、=、<、between外連接對于兩個表，如記錄不能一一連接，而又不想漏掉數(shù)據(jù)，可使用外連接SELECTT1.column,T2.columnFROMtable1T1,table2T2WHERET1.column1=T2.colmn2(+);自連接對于某些情況，必須要有表自身的連接，這時可利用表別名的作用，即把表完全看作兩個不同的表來處理對于已連接好的多表數(shù)據(jù)查詢，如同一張“大數(shù)據(jù)表”，進行類似的數(shù)據(jù)查詢。例：SELECTt1.item_name,t2.quantity,t3.last_nameFROMt_itemt1,T_salest2,T_empt3WHEREt1.item_id=t2

18、.item_idAndt2.emp_id=t3.employee_idAndt2.approval_flag=Y'LESSON4組函數(shù)?格式SELECTcolumn,group_functionFROMtableWHEREconditionGROUPBYgroup_by_exprHAVINGgroup_conditionORDERBYcolumn* GROUPBY:對歹U進行分組* HAVING:條件限制組查詢?常用組函數(shù)AVG、COUNT、MAX、MINSTDDEV、SUM、VARIANCE組值函數(shù)如sum、avg、stddev等等，反映的是一組值的某種整體狀況、一些統(tǒng)計信息。同時，

19、要注意的是，組值函數(shù)忽略NULL值，在做計算時將其排除在外，如avg函數(shù)，如數(shù)據(jù)中含有較多的空值時，avg返回的統(tǒng)計信息偏差較大.而count函數(shù)出外,它是一種特殊情況，它可以處理NULL值，并總是返回一個數(shù)值，所求出的值決不會是NULLo例：SELECTT1.name,T2.item_name,sum(T3.quantity),min(T3.rec_date)FROMt_inventoryT1,T_itemT2,T_onhandT3WhereT1.warehouse_id=T3.warehouse_idAndT3.item_id=T3.item_idGROUPBYT1.name,T2.ite

20、m_nameHAVINGsum(T3.quantity)>100所有的組值函數(shù)都有一個distinct或all選項，缺省的選項為all,count函數(shù)就是一個說明起作用的例子：注意如下查詢的結(jié)果:SELECTcount(distinctitem_id),count(item_id),count(*)FROMt_onhandLESSON5子查詢格式SELECTselect_listFROMtableWHEREexproperator(SELECTselect_listFROMtable)注：I. Expr與子查詢的select_list有相應(yīng)的結(jié)構(gòu)和數(shù)據(jù)類型II. 子查詢無orderby語

21、句III. 子查詢必須在operator的右面下名通過一些例子來說明子查詢的應(yīng)用。例：SELECTt1.item_id,tl.quantity,t1.rec_dateFROMt_onhandt1WHERErec_date=(SELECTmin(x.rec_date)FROMt_onhandxWHEREx.item_id=t1.item_id)當(dāng)然，很多時候子查詢也可由組函數(shù)實現(xiàn)，比較一下下面查詢的結(jié)果與前者的情況：SELECTT1.item_id,Tl.quantity,T1.rec_dateFROMt_onhandT1,(SELECTitem_id,min(rec_date)rec_date

22、FROMt_onhand)T2WHERET1.item_id=T2.item_idAndT1.rec_date=T2.rec_date可用于operator的運算符很多，分為單行子查詢和多行子查詢，單行子查詢即是在自查詢的結(jié)果中，至多返回一行值，可用于單行子查詢的邏輯運算符有：=、=者，=多行子查詢相對于單行子查詢，即在子查詢中可返回多行數(shù)據(jù)結(jié)果，可用于多行子查詢的邏輯運算有：in、notin還有一種存在性相關(guān)子查詢，即exists語句，它與in子查詢相似，但有兩點不同:i. 不能匹排一列或多列ii. 只能用于相關(guān)子查詢例：SELECTT1.item_name,n.descriptionFRO

23、Mt_itemT1Whereexists(SELECT*fromt_salesWHEREitem_id=T1.item_id)UNIT3數(shù)據(jù)庫單元培訓(xùn)目標(biāo)u理解Oracle數(shù)據(jù)庫的概念和作用；U能創(chuàng)建常用的數(shù)據(jù)庫對象(如表、索引、視圖和序列)；學(xué)會使用數(shù)據(jù)庫字典，記住一些常用視圖LESSON1系統(tǒng)開發(fā)周期和數(shù)據(jù)關(guān)聯(lián)?系統(tǒng)開發(fā)周期通過使用系統(tǒng)開發(fā)周期策略，實現(xiàn)數(shù)據(jù)庫從概念到產(chǎn)品的轉(zhuǎn)變，系統(tǒng)開發(fā)周期包含多個開發(fā)過程和步驟。這種由上到下、系統(tǒng)的方法可把實際的業(yè)務(wù)需求變成可操作的數(shù)據(jù)庫概念。有如下步驟(流程)：I. 分析，策略：根據(jù)企業(yè)的實際業(yè)務(wù)和將要擴展的業(yè)務(wù)需求，分析所要提供的信息和企業(yè)及系統(tǒng)特性

24、，建立相應(yīng)系統(tǒng)模型；II. 設(shè)計：數(shù)據(jù)庫設(shè)計，把實體模型映射成數(shù)據(jù)庫表之間的關(guān)聯(lián)，外鍵關(guān)聯(lián)和約束；III. 開發(fā)和創(chuàng)建文檔：開發(fā)業(yè)務(wù)流程，建立用戶界面，操作流程，并創(chuàng)建相應(yīng)文檔(如幫助文檔、操作手冊等)IV. 產(chǎn)品化：集成各模塊功能，統(tǒng)一成一個完整產(chǎn)品，并做好各項測試準(zhǔn)備；V. 測試：功能測試、業(yè)務(wù)流程測試、出錯流程測試最后的安裝測試等。在這幾步步驟中，第二步尤為重要，直接影響開發(fā)的成敗和效率，在第二步中，應(yīng)用開發(fā)者重點要要考慮以下一些因數(shù)：效率：最初的設(shè)計是最終設(shè)計的一個雛形，要充分考慮業(yè)務(wù)的效率；系統(tǒng)集成：系統(tǒng)的多個模塊間不是孤立的，應(yīng)有緊密的結(jié)合，這種結(jié)合并非看起來或感覺上的，是從邏輯上

25、和業(yè)務(wù)流程上的角度來分析的；與其他系統(tǒng)的集成：企業(yè)與運作時，往往有多個系統(tǒng)的存在，但同時各個系統(tǒng)間是有結(jié)合的、聯(lián)系的，如數(shù)據(jù)上、業(yè)務(wù)上的，同時還要考慮未來業(yè)務(wù)需求的標(biāo)準(zhǔn)接口等等；數(shù)據(jù)關(guān)聯(lián)系統(tǒng)開發(fā)周期第二步的重點工作就是建立邏輯模型，邏輯模型是業(yè)務(wù)數(shù)據(jù)的非常規(guī)范化的流程圖。懂得數(shù)據(jù)分解的原因和方法是理解模型的基礎(chǔ)，而模型又是建立長期支持業(yè)務(wù)應(yīng)用程序的基礎(chǔ)。規(guī)范化的過程通常用“范式“一詞來討論，最常見就是第一、二、三范式，其中第三范式是最高的規(guī)范化層次。I. 第一范式(1NF)：具體做法就是將每一個表中具有相似類型的數(shù)據(jù)存入不同的表中，并給每個表確定一個主關(guān)鍵字(即是：PrimaryKey)唯一的

26、標(biāo)號或標(biāo)示符，消除重復(fù)數(shù)據(jù)；II. 第二范式(2NF)：即是表中數(shù)據(jù)列只依賴主關(guān)鍵字；III. 第三范式(3NF)：即是找出不只是依賴于主關(guān)鍵字的說有數(shù)據(jù)列。約束為實現(xiàn)數(shù)據(jù)結(jié)構(gòu)的規(guī)范化標(biāo)準(zhǔn)，即是各級范式，可以在定義表結(jié)構(gòu)時，定一各個列和表的約束，從而嚴(yán)格表中的數(shù)據(jù)，格式如下：數(shù)據(jù)列級約束：ColumnCONSTRAINTconstraint_nameconstraint_type數(shù)據(jù)表級約束：ColumnCONSTRAINTconstraint_nameconstraint_type約束主要要以下幾種：I. PrimaryKey(PK):由一列或多列構(gòu)成的唯一標(biāo)示各行的列；II. Foreig

27、nKey(FK):即由表中一列或列組合來映射同一表或別的表的PK或UK(uniquekey);III. NOTNULLIV. UNIQUEV. CHECK對于建立約束的例子，可在建立數(shù)據(jù)表中參考。LESSON2創(chuàng)建表、序歹U、視圖和索引?創(chuàng)建表格式CREATETABLEschema.table(columndatatypeDEFAULTexprcolumn_constraint,table_constraint);Schema所有者標(biāo)識Table列表名Column列名Datatype列的數(shù)據(jù)類型Column_constraint列約束Table_constraint表約束例：CREATETAB

28、LEs_dept(idNUMBER(7)CONSTRAINTs_dept_id_pkPRIMARYKEY,NameVARCHAR2(25)CONSTRAINTs_dept_name_nnNOTNULL,Region_idNUMBER(7)CONSTRAINTs_dept_region_id_fkREFERENCESS_region(id),12CONSTRAINTs_dept_name_region_id_ukUNIQUECompanyConfidential,-Torinternaluseonly(name,region_id);創(chuàng)建序列格式CREATESEQUENCEsequenceINC

29、REMENTBYnSTARTWITHnMAXVALUEn|NOMAXVALUEMINVALUEn|NOMINVALUECYCLE|NOCYCLECACHEn|NOCACHE說明:標(biāo)示說明Sequence建立的序列名INCREMENTBY序列數(shù)字間的間隔，缺省為1STARTWITHn第一個序列值，缺生為1MAXVALUEn序列的最大值NOMAXVALUE確定序列的最大值，為10的27次方MINVALUEn序列的最小值NOMINVALUE確定序列的最小值，為1CYCLE|NOCYCLE確定序列值達到最大值時，是否重復(fù)開始，缺省為NOCYCELCACHEn|NOCACHE確定數(shù)據(jù)庫管理器于值和存儲的

30、值的個數(shù)，缺省為NOCACHE序列常用來給一個表的PK列賦值序歹U有NEXTVAL和CURRVAL方法。例：CREATESEQUENCEt_item_sIncrementby1Startwih1Maxvalue999999NocacheNocycle;是運行如下命令，看看結(jié)果：selectt_item_s.nextvalfromdual;selectt_item_s.currvalfromdual;?創(chuàng)建視圖CREATEORREPLACEFORCE|NOFORCEVIEWview(alias,alias)ASsubsequeryWITHCHECKOPTIONconstraintWITHREAD

31、ONLY注：I. Subquery可以是非常復(fù)雜的select語句II. Subquery不能包含ORDERBY語句實際上，視圖是一個基于數(shù)據(jù)表或其他視圖的邏輯數(shù)據(jù)表，一個視圖本身并不包含數(shù)據(jù)，僅僅是一個可以用來查看數(shù)據(jù)或更新的窗口，一個視圖在數(shù)據(jù)字典中是以一個select語句存儲的。使用視圖有著許多優(yōu)點：i. 限制數(shù)據(jù)表的訪問：由于它是一條select語句，通過選擇的取舍，使用戶只能看到或只需看到所要檢索的數(shù)據(jù)列；ii. 允許用戶通過一個視圖檢索多個表的信息，而不要去寫復(fù)雜的SQL語句；?創(chuàng)建索引I. 自動創(chuàng)建在創(chuàng)建表時，若包含PrimaryKey或者其他唯一約束時，一個唯一索引就自動創(chuàng)II

32、. 手動創(chuàng)建用戶可以創(chuàng)建非唯一的索引來提高檢索速度CREATEINDEXindexONtable(column,column)；III. 何時建立索引：列經(jīng)常在where或連接中使用；列包含了一個大范圍的值；列包含了大量的空值；兩個或多個列常常在where或連接中一起被使用；表的數(shù)據(jù)量大，而常常檢索其中的2-4%;太多的索引并不總是能提高檢索速度。LESSON3Oracle數(shù)據(jù)字典Oracle數(shù)據(jù)字典是Oracle數(shù)據(jù)庫管理系統(tǒng)的重要組件之一，它包含一系列數(shù)據(jù)表和數(shù)據(jù)視圖，提供只讀屬性的數(shù)據(jù)庫信息。Oracle數(shù)據(jù)字典實在數(shù)據(jù)庫建立的時候建立的，又Oracle數(shù)據(jù)庫管理器進行更新和維護，提供O

33、racle數(shù)據(jù)庫用戶、各個用戶的權(quán)限、數(shù)據(jù)庫對象、數(shù)據(jù)表約束等等信息。用戶擁有的對象 用戶可訪問對象 所有數(shù)據(jù)對象 服務(wù)器性能對象顯示所有數(shù)據(jù)字典的數(shù)據(jù)表、視圖和同義詞Oracle數(shù)據(jù)字典的四大類視圖：- user- all- DBA- v$其他視圖：- DICTIONARY- TABLE_PRIVILEGES顯示數(shù)據(jù)對象的所有者、授權(quán)者和權(quán)限- INDUser_indexes的同義詞常用視圖DICTIONARY、TABLE_PRIVILEGES、INDALL_TABLES、USER_OBJECTS數(shù)據(jù)字典使用示例：例一：SELECTdistinctobject_name,object_typ

34、eFROMusr_objects例二：SELECT*FROMuser_constraintsWHEREtable_name=T_ITEMOracle 數(shù)據(jù)庫管理器提LESSON4用戶訪問控制：在一個多用戶數(shù)據(jù)庫環(huán)境，必須維護數(shù)據(jù)訪問和使用的權(quán)限，供以下安全機制控制：I. 控制數(shù)據(jù)庫的訪問；II. 提供數(shù)據(jù)庫對象的局部訪問；III. 通過數(shù)據(jù)字典驗證賦予和接收特權(quán)IV. 為數(shù)據(jù)庫對象創(chuàng)建同義詞。數(shù)據(jù)庫安全分為兩類，一是系統(tǒng)安全，二是數(shù)據(jù)安全。系統(tǒng)安全控制數(shù)據(jù)庫的訪問和使用這一層次，例如用戶和密碼，用戶能使用的數(shù)據(jù)空間，用戶允許使用的操作系統(tǒng)等等；數(shù)據(jù)庫安全控制數(shù)據(jù)庫對象的訪問和使用，以及能在這些

35、數(shù)據(jù)庫對象上能進行的操作。權(quán)限的概念即是執(zhí)行特定SQL語句的權(quán)利。數(shù)據(jù)庫的系統(tǒng)管理員具有最高的權(quán)限，普通用戶必須有系統(tǒng)管理員賦予權(quán)限來訪問數(shù)據(jù)庫和數(shù)據(jù)庫對象，當(dāng)然，用戶也可具有賦予其他用戶或角色的權(quán)利。系統(tǒng)權(quán)限：DBA具有最高級系統(tǒng)權(quán)限，典型的系統(tǒng)權(quán)限有：系統(tǒng)權(quán)限CREATEUSER操作授權(quán)賦予創(chuàng)建其他Oracle用戶權(quán)限D(zhuǎn)ROPUSER刪除任何用戶DROPANYTABLE刪除任何束中的表BACKUPANYTABLE備份任何束中的表創(chuàng)建用戶格式:CREATEUSERuserIDENTIFIEDBYpassword系統(tǒng)權(quán)限授權(quán)格式：角色：即是一組相似的權(quán)限，可以用來同一授權(quán)給一些用戶。這樣把相似

36、的權(quán)限統(tǒng)一起來，易于給用戶授權(quán)和維護系統(tǒng)權(quán)限，同時，一個用戶也可以被授予多個角色權(quán)限。用例如下：CREATEROLErole;GRANTcreatetable,createviewTOmanager;GRANTmanagerTOuserA,userB;GRANTprivilege,privilegeTOuser,user對象權(quán)限：注意以下幾點：1. DBA可授權(quán)給用戶，對特定的對象執(zhí)行某項操作；2. 對象權(quán)限因?qū)ο嗟牟煌愋投袇^(qū)別；3. 對象的所有者擁有對象的所有權(quán)限；4. 對象的所有者可以對對象授予特定的權(quán)限。格式如下：GRANTobject_priv(,object_priv)|ALL(

37、columns)ONobjectTOuser,user|rolePUBLICWITHGRANTOPTION例：GRANTupdate(last_name,first_name,address)ONt_empTOmanager在Oracle系統(tǒng)中，可通過如下數(shù)據(jù)字典查看用戶權(quán)限情況：數(shù)據(jù)字典表說明ROLE_SYS_RPIVS授予角色的系統(tǒng)權(quán)限ROLE_TAB_PRIVS授予據(jù)色的表權(quán)限USER_ROLE_PRIVS用戶可使用的角色USER_TAB_PRIVS_MADE用戶對象所授予的對象權(quán)限USER_TAB_PRIVS_RECD用戶所授予的對象權(quán)限USER_COL_PRIVS_MADE用戶對象列

38、所授予的對象權(quán)限USER_COL_PRIVS_RECD用戶在特定的列上所授予的權(quán)限權(quán)限的回收：通過使用revoke命令可以收回授予其他用戶的權(quán)限，同時授予用戶的withgrantoption也會收回。格式如下：REVOKEprivilege,privilege|ALLONobjectFORMuser,user|role|PUBLICCASCADECONSTRAINTS例：REVOKEselect,insertONS_EMPFORMmanager;創(chuàng)建同義詞：為了引用其他用戶的某個表，必須使用這個表的全稱，即是用戶名加表名，通過使用同義詞就可避免這個麻煩，可以引用其他用戶的表、視圖、序列、過程或

39、是其他別的對象。格式如下：CREATEPUBLICSYNONYMsynonymFORobject;說明:PUBLIC所有用戶都可訪問的同義詞Synonym創(chuàng)建的同義詞名Object同義詞的引用源例：CREATESYNONYMT_EMPFORscott.t_tmp;UNIT4PL/SQL單元培訓(xùn)目標(biāo)u掌握PL/SQL常用語法；u使用OracleProcedureBuilder；u能創(chuàng)建包、函數(shù)和過程解決一些復(fù)雜問題；u掌握出錯流程處理LESSON1簡單PL/SQLPL/SQL是Oracle擴展的、具有程序設(shè)計特色的程序語言,它可以把數(shù)據(jù)維護和數(shù)據(jù)檢索容入在程序過程當(dāng)中。?PL/SQL結(jié)構(gòu)DECL

40、ARE-可選項- 變量，常數(shù)，游標(biāo)，用戶定義的例外BEGIN-Mandatory- SQL語句- PL/SQL控制語句EXCEPTION可選項- 例外處理END;Declarativeoptional：包括所有變量、常量、游標(biāo)和用戶定的例外等ExecutableMandatory:包括各種SQL代碼和PL/SQL代碼等ExceptionHandlingoptional:對各種例外情況的處理通常，每一個PL/SQL單元包含一個或多個塊結(jié)構(gòu)，這些塊之間可以是完全獨立的,也可以是一個塊在另一個塊里面。即是，一個塊又能被分為若干個更小的塊。下面是一些典型的PL/SQL程序結(jié)構(gòu)：程序結(jié)構(gòu)說明使用位置匿名

41、塊沒有特定名稱的PL/SQL塊，存在于APPLICATION中所有PL/SQL環(huán)境數(shù)據(jù)庫procedure、function命名PL/SQL塊，能接受參數(shù)和重復(fù)調(diào)用OracledatabaseServerApplicationProcedure、命名PL/SQL塊，能接受參Developer2000的組件，function數(shù)和重復(fù)調(diào)用例如FORMSPackage命名PL/SQL模塊，包含相關(guān)的Procedure、function和變量OracledatabaseServerDatabasetrigger與數(shù)據(jù)庫表聯(lián)系在一起的PL/SQL塊，能自動觸發(fā)OracledatabaseServerAp

42、plicationtrigger與Application事件聯(lián)系在一起的PL/SQL塊，能自動觸發(fā)Developer2000的組件，例如FORMS?匿名塊，過程和函數(shù)I.匿名塊DECLAREBEGIN-語句EXCEPTIONEND;II.過程PROCEDUREnameisBEGIN-語句EXCEPTIONEND;III.函數(shù)FUNCTIONnameRETURNdatatypeISBEGIN-語句RETURNVALUEEXCEPTIONEND;PL/SQL環(huán)境,通過PL/SQL引擎進行傳PL/SQL是一項綁定在Oracle數(shù)據(jù)庫服務(wù)器上的技術(shù)輸和處理。當(dāng)一段PL/SQL塊從Pro*程序、user

43、-exit、SQL*Plus或是ServerManager中提交后，Oracle數(shù)據(jù)庫服務(wù)器上的PL/SQL引擎就開始處理他們。PL/SQL引擎首先把塊中的PL/SQL代碼分成獨立的若干塊，在把他們傳送到SQL語句執(zhí)行器。這也意味著盡量采用少的PL/SQL塊傳輸?shù)絆racle數(shù)據(jù)庫服務(wù)器，從而提高客戶-服務(wù)器網(wǎng)絡(luò)構(gòu)造工作的高效性。在許多Oracle工具中，包括Developer/2000,擁有他們自己的PL/SQL引擎，這是完全獨立Oracle數(shù)據(jù)庫服務(wù)器，且在之前的引擎。OracleProcedureBuilder的使用結(jié)構(gòu)化程序設(shè)計的最大有點在于能快速、簡單的創(chuàng)建和調(diào)試代碼，Procedu

44、reBuilder提供所有必要的功能，以利于成功開發(fā)和調(diào)試PL/SQL程序。組要構(gòu)件：組件說明對象瀏覽器管理PL/SQL結(jié)構(gòu)，執(zhí)行調(diào)試PL/SQL解釋執(zhí)行器調(diào)試PL/SQL代碼，執(zhí)行PL/SQL代碼程序編輯器創(chuàng)建和編輯PL/SQL代碼數(shù)據(jù)庫程序編輯器創(chuàng)建和編輯服務(wù)器端PL/SQL代碼數(shù)據(jù)庫觸發(fā)器編輯器創(chuàng)建和編輯數(shù)據(jù)庫觸發(fā)器對象瀏覽器：ProgramUnit:能獨立被PL/SQL編譯器識別和處理的PL/SQL結(jié)構(gòu);ProgramUnitSepcification:程序單元的名稱、參數(shù)和返回類型；ProgramUnitReferances:程序單元引用的過程、函數(shù)、匿名塊和表；ProgramUni

45、tReferancedBy:引用程序單元的過程、函數(shù)、匿名塊和表；Libraries:文件或數(shù)據(jù)庫中PL/SQL包、過程和函數(shù)的集合；AttachedLibraries:引用的數(shù)據(jù)庫或文件系統(tǒng)中的庫；Built-inPackages:在程序調(diào)試中可引用的PL/SQL結(jié)構(gòu)單元；DebugActions:在程序單元調(diào)試中，可進行的監(jiān)測、中斷的命令；Stack:子程序調(diào)用堆棧DatabaseObjects:服務(wù)器端存儲的過程、庫、表和視圖。PL/SQL解釋執(zhí)行器：通過使用PL/SQL解釋執(zhí)行器，可以定義、顯示、調(diào)試和運行PL/SQL程序，同時，在PL/SQL解釋執(zhí)行器中，可以直接運行過程，以分號結(jié)束

46、，如下：Procedure_name(argumentl,argument2,.);在PL/SQL解釋執(zhí)行器中，也可執(zhí)行SQLDDL語句和其他命令語句。程序編輯器：1. 創(chuàng)建一個新程序單元：i. 選中程序單元；ii. 點擊創(chuàng)建按鈕，彈出程序?qū)υ捒虼翱?；iii. 出入新建程序單元名稱，并選擇程序類型，點擊OK鍵；新建程序單元顯示在對象瀏覽器上；iv. 在打開的程序單元內(nèi)，編輯。2. 程序編譯點擊程序編輯窗口中的compile按鈕，編譯程序單元，編譯信息在程序編輯器下方顯示，包括各種出錯信息或編譯成功信息。調(diào)試數(shù)據(jù)庫程序：1.總體步驟：i. 把數(shù)據(jù)庫程序單元載入到對象瀏覽器中；ii. 選中要調(diào)試的

47、程序單元；iii. 用鼠標(biāo)將其拖到程序單元中；iv. 進行調(diào)試工作；v. 用鼠標(biāo)間調(diào)試好程序單元拖回到原數(shù)據(jù)庫單元中。調(diào)試方法：在PL/SQL解釋執(zhí)行器中調(diào)試程序，可以雙擊代碼行，設(shè)置斷點(可以設(shè)置多割斷點)；同時，還可以設(shè)置局部變量和參數(shù)，如下：(debug1)PL/SQL>debug.seti(I：3)程序中的參數(shù)和變量類型I. 參數(shù)：參數(shù)有如下三種類型INArgumentOUTArgumentINOUTArgument在參數(shù)格式中，IN可以省略，它也是缺省的形式；IN代表該參數(shù)用于向程序中傳遞值，OUT代表由程序單元返回給調(diào)用它的函數(shù)值，而對于INOUT型參數(shù)，代表既可向子程序傳遞

48、值，同時子程序也可返回值給主程序。II. 變量類型通用數(shù)據(jù)類型：數(shù)據(jù)類型說明BINARY_INTEGER基本數(shù)值整型，-2147483647-2147483647NUMBER(precision,scale)基本浮點數(shù)值型CHAR(maximun_length)固定長度的字符型，最大值為32760LONG可變常字符型，最大長度為32760LONGRAW二進制型，最大長度為32760VARCHAR2(maximum_length)可變長字符型，最大長度為32767DATE日期和時間類型BOOLEAN邏輯型(TRUE、FALSE或NULL)程序單元（過程、匿名塊和函數(shù)）中變量的聲明：Identif

49、ierCONSTANTdatatypeNOTNULL:=|DEFAULTexpr;注意：具有初始值的常量和變量被認為是NOTNULL的，一行最多只有一個常量或是變量的聲明%TYPE類型：這是一種動態(tài)數(shù)據(jù)類型，在PL/SQL中動態(tài)決定器數(shù)據(jù)類型。它有兩大優(yōu)點：1 .決定其數(shù)據(jù)類型的數(shù)據(jù)庫表列的類型未知；2 .決定其數(shù)據(jù)類型的數(shù)據(jù)庫表列的類型在運行時是可變的。為了在PL/SQL中保存某個列的數(shù)值，必須確保變量與列具有相同的數(shù)據(jù)類型和精度，否則，常會造成PL/SQL錯誤或是精度的丟失。而%TYPE類型是由某一個預(yù)定的變量或時數(shù)據(jù)庫表中列決定的，這樣就可以確保數(shù)據(jù)類型的一致。例：l_last_name

50、 l_salaryt_emp.last_name%TYPEt_emp.base_salary%TYPE復(fù)合數(shù)據(jù)類型:1. PL/SQLTABLES它有兩部分組成：I)BINARY_INTEGER型的PrimaryKey;II）數(shù)據(jù)的列范圍注意：i.PL/SQLTABLES數(shù)據(jù)類型并不等同于數(shù)據(jù)庫中的表ii.PL/SQL TABLES數(shù)據(jù)類型類似一個一維數(shù)組;iii.PL/SQL TABLES類型包含兩個組件;iv.PL/SQL TABLES類型可動態(tài)增長聲明如下:TYPEtype_nameISTABLEOFscalar_datatypeNOTNULLINDEXBYBINARY_INTEGER;

51、identifiertype_name;例:TYPEname_table_typeISTABLEOFVARCHAR2(25)INDEXBYBINARY_INTEGER;First_name_tablename_table_type;first_name_table(1):=FRANCK'2. PL/SQLRECORDS它包含一個或是一個以上的任何范圍、記錄、PL/SQLTABLES類型的組件。注意:PL/SQLRECORD數(shù)據(jù)類型并不等同于數(shù)據(jù)庫中的表中的一條v.PL/SQLRECORD數(shù)據(jù)類型類似一個多維數(shù)組vi.PL/SQLRECORD類型包含多個組件;vii.PL/SQLRECORD類型可動態(tài)增長;viii.PL/SQLRECORD于PL