ORACLE基礎(chǔ)學(xué)習(xí)

企業(yè)信息化部

2015-01-15ORACLE基礎(chǔ)學(xué)習(xí)課程內(nèi)容數(shù)據(jù)庫基本概念介紹SQL程序語言SQL語言課程內(nèi)容數(shù)據(jù)庫基本概念介紹SQL程序語言SQL語言數(shù)據(jù)庫基本概念-（1）Oracle服務(wù)器：Oracle服務(wù)器中包含多種文件結(jié)構(gòu)、進(jìn)程結(jié)構(gòu)和內(nèi)存結(jié)構(gòu)；但是，處理SQL語句時，并非所有這些結(jié)構(gòu)都會用到。某些結(jié)構(gòu)用于提高數(shù)據(jù)庫的性能，確保該數(shù)據(jù)庫在遇到軟件或硬件錯誤時可以恢復(fù)，或者執(zhí)行維護(hù)該數(shù)據(jù)庫所需的其它任務(wù)。Oracle服務(wù)器包括一個Oracle例程和一個Oracle數(shù)據(jù)庫。Oracle例程：Oracle例程是后臺進(jìn)程和內(nèi)存結(jié)構(gòu)的組合。只有啟動例程后，才能訪問數(shù)據(jù)庫中的數(shù)據(jù)。每次啟動例程時，會分配系統(tǒng)全局區(qū)(SGA)并啟動Oracle后臺進(jìn)程。后臺進(jìn)程代表調(diào)用進(jìn)程執(zhí)行各種功能。它們把為每個用戶運行的多個Oracle程序所處理的功能統(tǒng)一起來。后臺進(jìn)程執(zhí)行輸入/輸出(I/O)，并監(jiān)視其它Oracle進(jìn)程來提高并行性，從而使性能和可靠性更加優(yōu)越。數(shù)據(jù)庫基本概念-（1）Oracle服務(wù)器：Oracle服數(shù)據(jù)庫基本概念-（2）Oracle數(shù)據(jù)庫：Oracle數(shù)據(jù)庫包含操作系統(tǒng)文件（也稱為數(shù)據(jù)庫文件），這些文件為數(shù)據(jù)庫信息提供了實際的物理存儲。數(shù)據(jù)庫文件用于確保數(shù)據(jù)一致性并能在例程失敗時得以恢復(fù)。表空間：是實際存儲數(shù)據(jù)的地方。數(shù)據(jù)庫存儲分配就是表空間的定義過程，因此表空間的設(shè)計對數(shù)據(jù)庫物理設(shè)計極為重要，其參數(shù)設(shè)定好壞對數(shù)據(jù)庫的性能及維護(hù)影響巨大。數(shù)據(jù)庫基本概念-（2）Oracle數(shù)據(jù)庫：Oracle數(shù)數(shù)據(jù)庫基本概念-（3）表：表是由確定的列數(shù)和可變的行數(shù)組成的邏輯結(jié)構(gòu)。是存放數(shù)據(jù)的基本單元。數(shù)據(jù)庫基本概念-（3）表：表是由確定的列數(shù)和可變的行數(shù)組成的數(shù)據(jù)庫基本概念-（4）視圖：視圖提供了在一個表或多個表中查看數(shù)據(jù)的替代方法，它是表上的一個動態(tài)窗口。視圖并沒有存儲數(shù)據(jù)，它只是將一個查詢結(jié)果集表示成一個虛擬表。索引：索引是為了加快查詢的速度而在表上創(chuàng)建的對象。數(shù)據(jù)庫基本概念-（4）視圖：視圖提供了在一個表或多個表中查看課程內(nèi)容數(shù)據(jù)庫基本概念介紹SQL程序語言SQL語言課程內(nèi)容數(shù)據(jù)庫基本概念介紹SQL程序語言SQL語言SQL數(shù)據(jù)類型數(shù)據(jù)類型描述VARCHAR2(SIZE)可變長字符串類型。SIZE默認(rèn)單位為BYTE，最大長度為4000，最小為1NUMBER(PRECISION,SCALE)可變長度的數(shù)字類型，PRECISION是數(shù)字可用的最大位數(shù)。如有不指定PRECISION,SCALE，可以提供PRECISION和SCALE為38位的數(shù)字DATE日期類型，從公元前4712年1月1日到公元9999年12月31日ROWID用一個十六進(jìn)制的數(shù)來標(biāo)明當(dāng)前行在表內(nèi)的唯一地址BLOB存儲非結(jié)構(gòu)化二進(jìn)制文件。常用于圖片，視頻的存儲CLOB存儲單字節(jié)或者多字節(jié)字符數(shù)據(jù)。常用語文本存儲SQL數(shù)據(jù)類型數(shù)據(jù)類型描述VARCHAR2(SIZE)可變長SQL語言SQL語言SQL語言－創(chuàng)建表使用CREATETABLE語句創(chuàng)建表，需指定表名、列名和數(shù)據(jù)類型。可通過tablespace指定表空間。SQL語言－創(chuàng)建表使用CREATETABLE語句創(chuàng)建表，需SQL語言－創(chuàng)建表CREATE建表示例：比較常用的建表方法改為“ulocal_tbs”SQL語言－創(chuàng)建表CREATE建表示例：比較常用的建表方法改SQL語言－創(chuàng)建視圖使用CREATEVIEW語句創(chuàng)建視圖，以限制對表的數(shù)據(jù)訪問或代替常用的SQL查詢語句視圖查詢方式SQL語言－創(chuàng)建視圖使用CREATEVIEW語句創(chuàng)建視圖，SQL語言－修改表SQL語言－修改表SQL語言－修改表SQL語言－修改表SQL語言－刪除表在ORACLE10版本以后，如果DROP不帶PURGE選項，則會把表先放在回收站DROP

TABLEDEPTPURGE;SELECT*FROMUSER_RECYCLEBIN;

PURGE

TABLEDEPT;SQL語言－刪除表在ORACLE10版本以后，如果DROP不SQL語言－RENAME要更改表、視圖、序列或同義詞的名稱，可以執(zhí)行RENAME語句RENAME的另一種寫法。SQL語言－RENAME要更改表、視圖、序列或同義詞的名稱，SQL語言－注釋可以使用COMMENT語句向表或列添加備注SQL語言－注釋可以使用COMMENT語句向表或列添加備注SQL語言－約束常用的約束有四種：非空約束，主鍵約束，外鍵約束和唯一性約束。SQL語言－約束常用的約束有四種：非空約束，主鍵約束，外鍵約SQL語言－約束非空約束：為表的列添加非空約束后，再為該表添加數(shù)據(jù)時就不可以插入空置了。主鍵約束：主鍵約束在一個表中，只能定義一個，定義為主鍵約束的可以是列或者列的組合，其中不能包含重復(fù)值，并且不能包含NULL值。外鍵約束：是讓兩個表通過外鍵建立關(guān)系。在使用外鍵約束時，被引用的列應(yīng)該具有主鍵約束或者具有唯一性約束。唯一性約束：用來保證表中的某一列或者表中的某幾列組合起來不重復(fù)的一種手段。SQL語言－約束非空約束：為表的列添加非空約束后，再為該表添SQL語言－約束除了在表創(chuàng)建時，定義約束關(guān)系，也可以在表創(chuàng)建后建立或取消約束關(guān)系，語句如下：SQL語言－約束除了在表創(chuàng)建時，定義約束關(guān)系，也可以在表創(chuàng)建SQL語言-插入數(shù)據(jù)使用INSERT語句為表添加數(shù)據(jù)SQL語言-插入數(shù)據(jù)使用INSERT語句為表添加數(shù)據(jù)SQL語言-插入數(shù)據(jù)SQL語言-插入數(shù)據(jù)更新數(shù)據(jù)更新數(shù)據(jù)更新數(shù)據(jù)更新數(shù)據(jù)刪除數(shù)據(jù)刪除數(shù)據(jù)查詢語句Subselect語法：Select-cause：查詢語句Subselect語法：Select-cause：查詢語句-選擇列查詢語句-選擇列查詢語句-選擇行查詢語句-選擇行查詢語句-選擇行查詢語句-選擇行查詢語句-選擇行可以使用AND和OR來指定任意多個查詢條件。查詢語句-選擇行可以使用AND和OR來指定任意多個查詢條件。查詢語句-謂詞查詢語句-謂詞查詢語句-謂詞查詢語句-謂詞查詢語句-謂詞查詢語句-謂詞查詢語句-排序查詢語句-排序查詢語句-去除重復(fù)行查詢語句-去除重復(fù)行查詢語句-運算次序查詢語句的運算次序如下，一個子句的輸出是下一個子句的輸入。只有運算次序在后面的子句才能引用運算次序在前面的子句。查詢語句-運算次序查詢語句的運算次序如下，一個子句的輸出是下查詢語句-給表達(dá)式命名查詢語句-給表達(dá)式命名查詢語句-子查詢查詢語句-子查詢查詢語句-分組函數(shù)分組函數(shù)對列中的一組值進(jìn)行運算以得到單個結(jié)果值。函數(shù)名稱功能AVG返回某一組中的值除以該組中值得個數(shù)的和COUNT返回一組行或值的個數(shù)MAX返回一組值中的最大值MIN返回一組值中的最小值SUM返回一組值的和查詢語句-分組函數(shù)分組函數(shù)對列中的一組值進(jìn)行運算以得到單個結(jié)查詢語句-數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)函數(shù)名稱功能ABS(N)返回數(shù)字N的絕對值CEIL(N)返回大于等于數(shù)字N的最小整數(shù)FLOOR(N)返回小于等于數(shù)字N的最大整數(shù)MOD(M,N)返回兩個數(shù)字相除后的余數(shù)。如果N為0，則返回MROUND(N[,M])執(zhí)行四舍五入運算SIGN(N)N大于0，返回1，N等于0，返回0，N小于0，返回-1TRUNC(N[,M])對數(shù)字進(jìn)行截取操作查詢語句-數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)函數(shù)名稱功能ABS(N)返回數(shù)字N查詢語句-字符函數(shù)字符函數(shù)函數(shù)名稱功能CONCAT(S1,S2)連接兩個字符串INSTR(S1,S2[,n[,m]])取得子串S2在字符串S1中的位置，N為起始搜索位置，M為子串出現(xiàn)次數(shù)，N,M默認(rèn)為1LENGTH(S)返回字符串的長度LOWER返回字符串,并將所有的字符小寫UPPER返回字符串,并將所有的字符大寫SUBSTR(S,N[,M])取子字符串,從N開始,取M個REPLACE(S,S1,S2)將字符串S中的子串S1用S2替換查詢語句-字符函數(shù)字符函數(shù)函數(shù)名稱功能CONCAT(S1,S查詢語句-日期函數(shù)日期函數(shù)函數(shù)名稱功能ADD_MONTHS(D,N)返回指定日期時間D之前或者之后的N個月所對應(yīng)的時間LAST_DAY(D)返回指定日期所在月份的最后一天MONTHS_BETWEEN(D1,D2)返回日期D1和D2之間相差的月數(shù)NEXT_DAY(D,'day')返回指定日期D后的第一個工作日（由day指定）所對應(yīng)的日期SYSDATE用來得到系統(tǒng)的當(dāng)前日期TRUNC(D[,FMT])按指定格式格式化日期查詢語句-日期函數(shù)日期函數(shù)函數(shù)名稱功能ADD_MONTHS查詢語句-轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù)函數(shù)名稱功能TO_CHAR(D[,FMT])按指定格式將日期轉(zhuǎn)變?yōu)樽址甌O_CHAR(N[,FMT])按指定格式將數(shù)字轉(zhuǎn)變?yōu)樽址甌O_DATE(S[,FMT])按指定格式將符合特定日期格式的字符串轉(zhuǎn)變?yōu)镈ATE類型的值TO_NUMBER(S[,FMT])按指定格式將符合特定數(shù)字格式的字符串轉(zhuǎn)變?yōu)閿?shù)字值查詢語句-轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù)函數(shù)名稱功能TO_CHAR(D[,查詢語句字符串使用單引號,如:’Clerk’ORDERBY指定多列時,先按第一列排序,對于第一列相同的行,再按第二列排序.不同列可以指定不同的排序,如:SELECTDISTINCTDEPT,JOBFROMSTAFFWHEREDEPT<30ORDERBYDEPTASC,JOBDESC查詢語句字符串使用單引號,如:’Clerk’查詢語句多個搜索條件:SELECTDEPT,NAME,JOBFROMSTAFFWHEREJOB='Clerk'AND(DEPT=20ORDEPT=21)查詢語句多個搜索條件:查詢語句-分組將WHERE子句與GROUPBY子句一起使用查詢語句-分組將WHERE子句與GROUPBY子句一起使用查詢語句-分組在GROUPBY子句之后使用HAVING子句查詢語句-分組在GROUPBY子句之后使用HAVING子句查詢語句-標(biāo)量全查詢標(biāo)量全查詢返回一行，且該行只有一個值。查詢語句-標(biāo)量全查詢標(biāo)量全查詢返回一行，且該行只有一個值。查詢語句-條件表達(dá)式查詢語句-條件表達(dá)式查詢語句-條件表達(dá)式查詢語句-條件表達(dá)式查詢語句-相關(guān)子查詢查詢語句-相關(guān)子查詢查詢語句-集合運算集合運算的查詢語句必須要有相同的數(shù)據(jù)類型和列數(shù)。查詢語句-集合運算集合運算的查詢語句必須要有相同的數(shù)據(jù)類型和查詢語句-集合運算查詢語句-集合運算查詢語句-集合運算INTERSECT相當(dāng)于取兩個集合的交集MINUS相當(dāng)于取兩個集合的差集，就是“去同留異”查詢語句-集合運算INTERSECT相當(dāng)于取兩個集合的交集M查詢語句-連接查詢語句-連接查詢語句-連接查詢語句-連接查詢語句-連接查詢語句-連接查詢語句-連接查詢語句-連接查詢語句-連接查詢語句-連接查詢語句-層次查詢當(dāng)表具有層次結(jié)構(gòu)數(shù)據(jù)時，通過使用層次查詢可以更直觀的顯示數(shù)據(jù)結(jié)果，并顯示其數(shù)據(jù)之間的層次關(guān)系STARTWITH：用于指定層次查詢的根行CONNECTBY：用于指定父行和子行之間的關(guān)系，在其后的表達(dá)式中，必須使用PRIOR引用父行。查詢語句-層次查詢當(dāng)表具有層次結(jié)構(gòu)數(shù)據(jù)時，通過使用層次查詢可課程內(nèi)容數(shù)據(jù)庫基本概念介紹SQL程序語言SQL語言課程內(nèi)容數(shù)據(jù)庫基本概念介紹SQL程序語言SQL語言PL/SQL支持的流程控制結(jié)構(gòu)：條件控制IF語句CASE語句循環(huán)控制LOOP循環(huán)WHILE循環(huán)FOR循環(huán)順序控制GOTO語句NULL語句PL/SQL支持的流程控制結(jié)構(gòu)PL/SQL支持的流程控制結(jié)構(gòu)：PL/SQL支持的流程控制流程控制結(jié)構(gòu)-條件控制IF語句根據(jù)條件執(zhí)行一系列語句，有三種形式：IF-THEN、IF-THEN-ELSE和IF-THEN-ELSIF流程控制結(jié)構(gòu)-條件控制IF語句根據(jù)條件執(zhí)行一系列語句，有三CASE語句用于根據(jù)單個變量或表達(dá)式與多個值進(jìn)行比較執(zhí)行CASE語句前，先計算選擇器的值流程控制結(jié)構(gòu)-條件控制CASE語句用于根據(jù)單個變量或表達(dá)式與多個值進(jìn)行比較流程控流程控制結(jié)構(gòu)-條件控制LOOP-無條件循環(huán)WHILE-根據(jù)條件循環(huán)FOR-循環(huán)固定的次數(shù)循環(huán)退出控制命令：EXIT流程控制結(jié)構(gòu)-條件控制LOOP-無條件循環(huán)流程控制結(jié)構(gòu)-條件控制GOTO語句-無條件地轉(zhuǎn)到標(biāo)簽指定的語句

因為GOTO語句會增加程序的復(fù)雜性，并且使得程序的可讀性變差，一般不建議使用。NULL語句-什么也不做的空語句

通常用在異常語句中，對于發(fā)生的異常生么都不做的時候可以加一句NULL。流程控制結(jié)構(gòu)-條件控制GOTO語句-無條件地轉(zhuǎn)到標(biāo)簽動態(tài)SQL動態(tài)SQL是指在PL/SQL程序執(zhí)行時生成的SQL語句編譯程序?qū)討B(tài)SQL不做處理，而是在程序運行時動態(tài)構(gòu)造語句、對語句進(jìn)行語法分析并執(zhí)行DDL語句命令和會話控制語句不能在PL/SQL中直接使用，但是可以通過動態(tài)SQL來執(zhí)行執(zhí)行動態(tài)SQL的語法：EXECUTEIMMEDIATEdynamic_sql_string[INTOdefine_variable_list][USINGbind_argument_list];動態(tài)SQL動態(tài)SQL是指在PL/SQL程序執(zhí)行時生成的異常在運行程序時出現(xiàn)的錯誤叫做異常發(fā)生異常后，語句將停止執(zhí)行，控制權(quán)轉(zhuǎn)移到PL/SQL塊的異常處理部分異常有兩種類型：預(yù)定義異常-當(dāng)PL/SQL程序違反Oracle規(guī)則或超越系統(tǒng)限制時隱式引發(fā)用戶定義異常-用戶可以在PL/SQL塊的聲明部分定義異常，自定義的異常通過RAISE語句顯式引發(fā)異常在運行程序時出現(xiàn)的錯誤叫做異常異常ORACLE常用的異常異常ORACLE常用的異常異常在異常發(fā)生時，可以使用ORACLE內(nèi)置的兩個變量：SQLCODE獲取異常錯誤號，SQLERRM獲取異常錯誤消息。在程序中總會有未預(yù)料到的異常，故要在異常處理部分最后加上WHENOTHERS增強程序的健壯性。異常在異常發(fā)生時，可以使用ORACLE內(nèi)置的兩個變量：SQLOracle服務(wù)器執(zhí)行PL/SQL程序內(nèi)存單元保存到游標(biāo)中一次處理一行檢索行提取行游標(biāo)簡介Oracle服務(wù)器執(zhí)行內(nèi)存單元保存到游標(biāo)中一次處理一行檢逐行處理查詢結(jié)果，以編程的方式訪問數(shù)據(jù)游標(biāo)的類型：隱式游標(biāo)REF游標(biāo)顯式游標(biāo)在PL/SQL程序中執(zhí)行DMLSQL語句時自動創(chuàng)建隱式游標(biāo)。顯式游標(biāo)用于處理返回多行的查詢。REF游標(biāo)用于處理運行時才能確定的動態(tài)SQL查詢的結(jié)果游標(biāo)類型游標(biāo)簡介逐行處理查詢結(jié)果，以編程的方式訪問數(shù)據(jù)隱式游標(biāo)REF游標(biāo)顯游標(biāo)簡介在PL/SQL中使用DML語句時自動創(chuàng)建隱式游標(biāo)隱式游標(biāo)自動聲明、打開和關(guān)閉，其名為SQL通過檢查隱式游標(biāo)的屬性可以獲得最近執(zhí)行的DML語句的信息隱式游標(biāo)的屬性有： %FOUND–SQL語句影響了一行或多行時為TRUE %NOTFOUND–SQL語句沒有影響任何行時為TRUE %ROWCOUNT–SQL語句影響的行數(shù) %ISOPEN-游標(biāo)是否打開，始終為FALSE游標(biāo)簡介在PL/SQL中使用DML語句時自動創(chuàng)建隱式游標(biāo)隱式游標(biāo)SQL>SETSERVEROUTPUTONSQL>BEGIN UPDATEprodSETprice=270 WHEREpid=‘0005'; IFSQL%FOUNDTHEN DBMS_OUTPUT.PUT_LINE(‘?dāng)?shù)據(jù)已修改'); ENDIF;END; /游標(biāo)簡介隱式游標(biāo)SQL>SETSERVEROUTPUTON游游標(biāo)簡介隱式游標(biāo)循環(huán)游標(biāo)用于簡化游標(biāo)處理代碼當(dāng)用戶需要從游標(biāo)中提取所有記錄時使用循環(huán)游標(biāo)的語法如下：

FOR<record_index>IN<cursor_name>LOOP <executablestatements>ENDLOOP;游標(biāo)簡介隱式游標(biāo)循環(huán)游標(biāo)用于簡化游標(biāo)處理代碼FOR<re游標(biāo)簡介顯式游標(biāo)在PL/SQL塊的聲明部分定義查詢，該查詢可以返回多行顯式游標(biāo)的操作過程：數(shù)據(jù)庫打開游標(biāo)30George344Roger245James1Stud_mrksStud_namestud_no提取行變量關(guān)閉游標(biāo)顯式游標(biāo)游標(biāo)簡介顯式游標(biāo)在PL/SQL塊的聲明部分定義查詢，該查游標(biāo)簡介帶參數(shù)的顯式游標(biāo)聲明顯式游標(biāo)時可以帶參數(shù)以提高靈活性聲明帶參數(shù)的顯式游標(biāo)的語法如下：

CURSOR<cursor_name>(<param_name><param_type>)ISselect_statement;游標(biāo)簡介帶參數(shù)的顯式游標(biāo)聲明顯式游標(biāo)時可以帶參數(shù)以提高靈活性游標(biāo)簡介使用顯式游標(biāo)更新行2-1允許使用游標(biāo)刪除或更新活動集中的行聲明游標(biāo)時必須使用SELECT…FORUPDATE語句

CURSOR<cursor_name>IS SELECTstatementFORUPDATE; UPDATE<table_name> SET<set_clause> WHERECURRENTOF<cursor_name>游標(biāo)簡介使用顯式游標(biāo)更新行2-1允許使用游標(biāo)刪除或更新活動程序子程序命名的PL/SQL塊，編譯并存儲在數(shù)據(jù)庫中。子程序的各個部分：聲明部分可執(zhí)行部分異常處理部分(可選)子程序的分類：過程－執(zhí)行某些操作函數(shù)－執(zhí)行操作并返回值程序子程序命名的PL/SQL塊，編譯并存儲在數(shù)據(jù)庫中。過程創(chuàng)建過程的語法：CREATE[ORREPLACE]PROCEDURE<procedurename>[(<parameterlist>)]IS|AS<localvariabledeclaration>BEGIN<executablestatements>[EXCEPTION<exceptionhandlers>]END;程序過程創(chuàng)建過程的語法：程序程序過程過程參數(shù)的三種模式：IN用于接受調(diào)用程序的值默認(rèn)的參數(shù)模式OUT用于向調(diào)用程序返回值INOUT用于接受調(diào)用程序的值，并向調(diào)用程序返回更新的值程序過程過程參數(shù)的三種模式：程序函數(shù)函數(shù)是可以返回值的命名的PL/SQL子程序。創(chuàng)建函數(shù)的語法：

CREATE[ORREPLACE]FUNCTION<functionname>[(param1,param2)]RETURN<datatype>IS|AS[localdeclarations]BEGINExecutableStatements;RETURNresult;EXCEPTIONExceptionhandlers;END;程序函數(shù)函數(shù)是可以返回值的命名的PL/SQL子程序。過程和函數(shù)的比較過程函數(shù)作為PL/SQL語句執(zhí)行作為表達(dá)式的一部分調(diào)用在規(guī)格說明中不包含RETURN子句必須在規(guī)格說明中包含RETURN子句不返回任何值必須返回單個值可以包含RETURN語句，但是與函數(shù)不同，它不能用于返回值必須包含至少一條RETURN

語句程序過程和函數(shù)的比較過程函數(shù)作為PL/SQL語句執(zhí)行作程序程序包程序包是對相關(guān)過程、函數(shù)、變量、游標(biāo)和異常等對象的封裝程序包由規(guī)范和主體兩部分組成聲明程序包中公共對象。包括類型、變量、常量、異常、游標(biāo)規(guī)范和子程序規(guī)范等聲明程序包私有對象和實現(xiàn)在包規(guī)范中聲明的子程序和游標(biāo)程序包規(guī)范主體程序程序包程序包是對相關(guān)過程、函數(shù)、變量、游標(biāo)和異常等對象的程序創(chuàng)建程序包程序包規(guī)范CREATE[ORREPLACE]PACKAGEpackage_nameIS|AS[Publicitemdeclarations][Subprogramspecification]END[package_name];程序包主體CREATE[ORREPLACE]PACKAGEBODYpackage_nameIS|AS[Privateitemdeclarations][Subprogrambodies][BEGINInitialization]END[package_name];程序創(chuàng)建程序包程序包規(guī)范程序包主體系統(tǒng)視圖V$SESSIONV$VERSIONALL_JOBSALL_ALL_TABLESALL_OJBECTSALL_COL_COMMENTSALL_IND_COLUMNSALL_SOURCEDBA_SEGMENTSDBA_LOBSRECYCLEBIN系統(tǒng)視圖V$SESSION推薦的電子書輕松學(xué)編程：輕松學(xué)Oracle數(shù)據(jù)庫精通Oracle.10g.Pl.SQL編程oracle存儲過程學(xué)習(xí)經(jīng)典[語法+實例+調(diào)用]Oracle.PL.SQL程序設(shè)計_第五版_上冊O(shè)racle.PL.SQL程序設(shè)計_第五版_下冊O(shè)racle.SQL高級編程推薦的電子書輕松學(xué)編程：輕松學(xué)Oracle數(shù)據(jù)庫謝謝！謝謝！ORACLE基礎(chǔ)學(xué)習(xí)

企業(yè)信息化部

2015-01-15ORACLE基礎(chǔ)學(xué)習(xí)課程內(nèi)容數(shù)據(jù)庫基本概念介紹SQL程序語言SQL語言課程內(nèi)容數(shù)據(jù)庫基本概念介紹SQL程序語言SQL語言數(shù)據(jù)庫基本概念-（1）Oracle服務(wù)器：Oracle服務(wù)器中包含多種文件結(jié)構(gòu)、進(jìn)程結(jié)構(gòu)和內(nèi)存結(jié)構(gòu)；但是，處理SQL語句時，并非所有這些結(jié)構(gòu)都會用到。某些結(jié)構(gòu)用于提高數(shù)據(jù)庫的性能，確保該數(shù)據(jù)庫在遇到軟件或硬件錯誤時可以恢復(fù)，或者執(zhí)行維護(hù)該數(shù)據(jù)庫所需的其它任務(wù)。Oracle服務(wù)器包括一個Oracle例程和一個Oracle數(shù)據(jù)庫。Oracle例程：Oracle例程是后臺進(jìn)程和內(nèi)存結(jié)構(gòu)的組合。只有啟動例程后，才能訪問數(shù)據(jù)庫中的數(shù)據(jù)。每次啟動例程時，會分配系統(tǒng)全局區(qū)(SGA)并啟動Oracle后臺進(jìn)程。后臺進(jìn)程代表調(diào)用進(jìn)程執(zhí)行各種功能。它們把為每個用戶運行的多個Oracle程序所處理的功能統(tǒng)一起來。后臺進(jìn)程執(zhí)行輸入/輸出(I/O)，并監(jiān)視其它Oracle進(jìn)程來提高并行性，從而使性能和可靠性更加優(yōu)越。數(shù)據(jù)庫基本概念-（1）Oracle服務(wù)器：Oracle服數(shù)據(jù)庫基本概念-（2）Oracle數(shù)據(jù)庫：Oracle數(shù)據(jù)庫包含操作系統(tǒng)文件（也稱為數(shù)據(jù)庫文件），這些文件為數(shù)據(jù)庫信息提供了實際的物理存儲。數(shù)據(jù)庫文件用于確保數(shù)據(jù)一致性并能在例程失敗時得以恢復(fù)。表空間：是實際存儲數(shù)據(jù)的地方。數(shù)據(jù)庫存儲分配就是表空間的定義過程，因此表空間的設(shè)計對數(shù)據(jù)庫物理設(shè)計極為重要，其參數(shù)設(shè)定好壞對數(shù)據(jù)庫的性能及維護(hù)影響巨大。數(shù)據(jù)庫基本概念-（2）Oracle數(shù)據(jù)庫：Oracle數(shù)數(shù)據(jù)庫基本概念-（3）表：表是由確定的列數(shù)和可變的行數(shù)組成的邏輯結(jié)構(gòu)。是存放數(shù)據(jù)的基本單元。數(shù)據(jù)庫基本概念-（3）表：表是由確定的列數(shù)和可變的行數(shù)組成的數(shù)據(jù)庫基本概念-（4）視圖：視圖提供了在一個表或多個表中查看數(shù)據(jù)的替代方法，它是表上的一個動態(tài)窗口。視圖并沒有存儲數(shù)據(jù)，它只是將一個查詢結(jié)果集表示成一個虛擬表。索引：索引是為了加快查詢的速度而在表上創(chuàng)建的對象。數(shù)據(jù)庫基本概念-（4）視圖：視圖提供了在一個表或多個表中查看課程內(nèi)容數(shù)據(jù)庫基本概念介紹SQL程序語言SQL語言課程內(nèi)容數(shù)據(jù)庫基本概念介紹SQL程序語言SQL語言SQL數(shù)據(jù)類型數(shù)據(jù)類型描述VARCHAR2(SIZE)可變長字符串類型。SIZE默認(rèn)單位為BYTE，最大長度為4000，最小為1NUMBER(PRECISION,SCALE)可變長度的數(shù)字類型，PRECISION是數(shù)字可用的最大位數(shù)。如有不指定PRECISION,SCALE，可以提供PRECISION和SCALE為38位的數(shù)字DATE日期類型，從公元前4712年1月1日到公元9999年12月31日ROWID用一個十六進(jìn)制的數(shù)來標(biāo)明當(dāng)前行在表內(nèi)的唯一地址BLOB存儲非結(jié)構(gòu)化二進(jìn)制文件。常用于圖片，視頻的存儲CLOB存儲單字節(jié)或者多字節(jié)字符數(shù)據(jù)。常用語文本存儲SQL數(shù)據(jù)類型數(shù)據(jù)類型描述VARCHAR2(SIZE)可變長SQL語言SQL語言SQL語言－創(chuàng)建表使用CREATETABLE語句創(chuàng)建表，需指定表名、列名和數(shù)據(jù)類型?？赏ㄟ^tablespace指定表空間。SQL語言－創(chuàng)建表使用CREATETABLE語句創(chuàng)建表，需SQL語言－創(chuàng)建表CREATE建表示例：比較常用的建表方法改為“ulocal_tbs”SQL語言－創(chuàng)建表CREATE建表示例：比較常用的建表方法改SQL語言－創(chuàng)建視圖使用CREATEVIEW語句創(chuàng)建視圖，以限制對表的數(shù)據(jù)訪問或代替常用的SQL查詢語句視圖查詢方式SQL語言－創(chuàng)建視圖使用CREATEVIEW語句創(chuàng)建視圖，SQL語言－修改表SQL語言－修改表SQL語言－修改表SQL語言－修改表SQL語言－刪除表在ORACLE10版本以后，如果DROP不帶PURGE選項，則會把表先放在回收站DROP

TABLEDEPTPURGE;SELECT*FROMUSER_RECYCLEBIN;

PURGE

TABLEDEPT;SQL語言－刪除表在ORACLE10版本以后，如果DROP不SQL語言－RENAME要更改表、視圖、序列或同義詞的名稱，可以執(zhí)行RENAME語句RENAME的另一種寫法。SQL語言－RENAME要更改表、視圖、序列或同義詞的名稱，SQL語言－注釋可以使用COMMENT語句向表或列添加備注SQL語言－注釋可以使用COMMENT語句向表或列添加備注SQL語言－約束常用的約束有四種：非空約束，主鍵約束，外鍵約束和唯一性約束。SQL語言－約束常用的約束有四種：非空約束，主鍵約束，外鍵約SQL語言－約束非空約束：為表的列添加非空約束后，再為該表添加數(shù)據(jù)時就不可以插入空置了。主鍵約束：主鍵約束在一個表中，只能定義一個，定義為主鍵約束的可以是列或者列的組合，其中不能包含重復(fù)值，并且不能包含NULL值。外鍵約束：是讓兩個表通過外鍵建立關(guān)系。在使用外鍵約束時，被引用的列應(yīng)該具有主鍵約束或者具有唯一性約束。唯一性約束：用來保證表中的某一列或者表中的某幾列組合起來不重復(fù)的一種手段。SQL語言－約束非空約束：為表的列添加非空約束后，再為該表添SQL語言－約束除了在表創(chuàng)建時，定義約束關(guān)系，也可以在表創(chuàng)建后建立或取消約束關(guān)系，語句如下：SQL語言－約束除了在表創(chuàng)建時，定義約束關(guān)系，也可以在表創(chuàng)建SQL語言-插入數(shù)據(jù)使用INSERT語句為表添加數(shù)據(jù)SQL語言-插入數(shù)據(jù)使用INSERT語句為表添加數(shù)據(jù)SQL語言-插入數(shù)據(jù)SQL語言-插入數(shù)據(jù)更新數(shù)據(jù)更新數(shù)據(jù)更新數(shù)據(jù)更新數(shù)據(jù)刪除數(shù)據(jù)刪除數(shù)據(jù)查詢語句Subselect語法：Select-cause：查詢語句Subselect語法：Select-cause：查詢語句-選擇列查詢語句-選擇列查詢語句-選擇行查詢語句-選擇行查詢語句-選擇行查詢語句-選擇行查詢語句-選擇行可以使用AND和OR來指定任意多個查詢條件。查詢語句-選擇行可以使用AND和OR來指定任意多個查詢條件。查詢語句-謂詞查詢語句-謂詞查詢語句-謂詞查詢語句-謂詞查詢語句-謂詞查詢語句-謂詞查詢語句-排序查詢語句-排序查詢語句-去除重復(fù)行查詢語句-去除重復(fù)行查詢語句-運算次序查詢語句的運算次序如下，一個子句的輸出是下一個子句的輸入。只有運算次序在后面的子句才能引用運算次序在前面的子句。查詢語句-運算次序查詢語句的運算次序如下，一個子句的輸出是下查詢語句-給表達(dá)式命名查詢語句-給表達(dá)式命名查詢語句-子查詢查詢語句-子查詢查詢語句-分組函數(shù)分組函數(shù)對列中的一組值進(jìn)行運算以得到單個結(jié)果值。函數(shù)名稱功能AVG返回某一組中的值除以該組中值得個數(shù)的和COUNT返回一組行或值的個數(shù)MAX返回一組值中的最大值MIN返回一組值中的最小值SUM返回一組值的和查詢語句-分組函數(shù)分組函數(shù)對列中的一組值進(jìn)行運算以得到單個結(jié)查詢語句-數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)函數(shù)名稱功能ABS(N)返回數(shù)字N的絕對值CEIL(N)返回大于等于數(shù)字N的最小整數(shù)FLOOR(N)返回小于等于數(shù)字N的最大整數(shù)MOD(M,N)返回兩個數(shù)字相除后的余數(shù)。如果N為0，則返回MROUND(N[,M])執(zhí)行四舍五入運算SIGN(N)N大于0，返回1，N等于0，返回0，N小于0，返回-1TRUNC(N[,M])對數(shù)字進(jìn)行截取操作查詢語句-數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)函數(shù)名稱功能ABS(N)返回數(shù)字N查詢語句-字符函數(shù)字符函數(shù)函數(shù)名稱功能CONCAT(S1,S2)連接兩個字符串INSTR(S1,S2[,n[,m]])取得子串S2在字符串S1中的位置，N為起始搜索位置，M為子串出現(xiàn)次數(shù)，N,M默認(rèn)為1LENGTH(S)返回字符串的長度LOWER返回字符串,并將所有的字符小寫UPPER返回字符串,并將所有的字符大寫SUBSTR(S,N[,M])取子字符串,從N開始,取M個REPLACE(S,S1,S2)將字符串S中的子串S1用S2替換查詢語句-字符函數(shù)字符函數(shù)函數(shù)名稱功能CONCAT(S1,S查詢語句-日期函數(shù)日期函數(shù)函數(shù)名稱功能ADD_MONTHS(D,N)返回指定日期時間D之前或者之后的N個月所對應(yīng)的時間LAST_DAY(D)返回指定日期所在月份的最后一天MONTHS_BETWEEN(D1,D2)返回日期D1和D2之間相差的月數(shù)NEXT_DAY(D,'day')返回指定日期D后的第一個工作日（由day指定）所對應(yīng)的日期SYSDATE用來得到系統(tǒng)的當(dāng)前日期TRUNC(D[,FMT])按指定格式格式化日期查詢語句-日期函數(shù)日期函數(shù)函數(shù)名稱功能ADD_MONTHS查詢語句-轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù)函數(shù)名稱功能TO_CHAR(D[,FMT])按指定格式將日期轉(zhuǎn)變?yōu)樽址甌O_CHAR(N[,FMT])按指定格式將數(shù)字轉(zhuǎn)變?yōu)樽址甌O_DATE(S[,FMT])按指定格式將符合特定日期格式的字符串轉(zhuǎn)變?yōu)镈ATE類型的值TO_NUMBER(S[,FMT])按指定格式將符合特定數(shù)字格式的字符串轉(zhuǎn)變?yōu)閿?shù)字值查詢語句-轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù)函數(shù)名稱功能TO_CHAR(D[,查詢語句字符串使用單引號,如:’Clerk’ORDERBY指定多列時,先按第一列排序,對于第一列相同的行,再按第二列排序.不同列可以指定不同的排序,如:SELECTDISTINCTDEPT,JOBFROMSTAFFWHEREDEPT<30ORDERBYDEPTASC,JOBDESC查詢語句字符串使用單引號,如:’Clerk’查詢語句多個搜索條件:SELECTDEPT,NAME,JOBFROMSTAFFWHEREJOB='Clerk'AND(DEPT=20ORDEPT=21)查詢語句多個搜索條件:查詢語句-分組將WHERE子句與GROUPBY子句一起使用查詢語句-分組將WHERE子句與GROUPBY子句一起使用查詢語句-分組在GROUPBY子句之后使用HAVING子句查詢語句-分組在GROUPBY子句之后使用HAVING子句查詢語句-標(biāo)量全查詢標(biāo)量全查詢返回一行，且該行只有一個值。查詢語句-標(biāo)量全查詢標(biāo)量全查詢返回一行，且該行只有一個值。查詢語句-條件表達(dá)式查詢語句-條件表達(dá)式查詢語句-條件表達(dá)式查詢語句-條件表達(dá)式查詢語句-相關(guān)子查詢查詢語句-相關(guān)子查詢查詢語句-集合運算集合運算的查詢語句必須要有相同的數(shù)據(jù)類型和列數(shù)。查詢語句-集合運算集合運算的查詢語句必須要有相同的數(shù)據(jù)類型和查詢語句-集合運算查詢語句-集合運算查詢語句-集合運算INTERSECT相當(dāng)于取兩個集合的交集MINUS相當(dāng)于取兩個集合的差集，就是“去同留異”查詢語句-集合運算INTERSECT相當(dāng)于取兩個集合的交集M查詢語句-連接查詢語句-連接查詢語句-連接查詢語句-連接查詢語句-連接查詢語句-連接查詢語句-連接查詢語句-連接查詢語句-連接查詢語句-連接查詢語句-層次查詢當(dāng)表具有層次結(jié)構(gòu)數(shù)據(jù)時，通過使用層次查詢可以更直觀的顯示數(shù)據(jù)結(jié)果，并顯示其數(shù)據(jù)之間的層次關(guān)系STARTWITH：用于指定層次查詢的根行CONNECTBY：用于指定父行和子行之間的關(guān)系，在其后的表達(dá)式中，必須使用PRIOR引用父行。查詢語句-層次查詢當(dāng)表具有層次結(jié)構(gòu)數(shù)據(jù)時，通過使用層次查詢可課程內(nèi)容數(shù)據(jù)庫基本概念介紹SQL程序語言SQL語言課程內(nèi)容數(shù)據(jù)庫基本概念介紹SQL程序語言SQL語言PL/SQL支持的流程控制結(jié)構(gòu)：條件控制IF語句CASE語句循環(huán)控制LOOP循環(huán)WHILE循環(huán)FOR循環(huán)順序控制GOTO語句NULL語句PL/SQL支持的流程控制結(jié)構(gòu)PL/SQL支持的流程控制結(jié)構(gòu)：PL/SQL支持的流程控制流程控制結(jié)構(gòu)-條件控制IF語句根據(jù)條件執(zhí)行一系列語句，有三種形式：IF-THEN、IF-THEN-ELSE和IF-THEN-ELSIF流程控制結(jié)構(gòu)-條件控制IF語句根據(jù)條件執(zhí)行一系列語句，有三CASE語句用于根據(jù)單個變量或表達(dá)式與多個值進(jìn)行比較執(zhí)行CASE語句前，先計算選擇器的值流程控制結(jié)構(gòu)-條件控制CASE語句用于根據(jù)單個變量或表達(dá)式與多個值進(jìn)行比較流程控流程控制結(jié)構(gòu)-條件控制LOOP-無條件循環(huán)WHILE-根據(jù)條件循環(huán)FOR-循環(huán)固定的次數(shù)循環(huán)退出控制命令：EXIT流程控制結(jié)構(gòu)-條件控制LOOP-無條件循環(huán)流程控制結(jié)構(gòu)-條件控制GOTO語句-無條件地轉(zhuǎn)到標(biāo)簽指定的語句

因為GOTO語句會增加程序的復(fù)雜性，并且使得程序的可讀性變差，一般不建議使用。NULL語句-什么也不做的空語句

通常用在異常語句中，對于發(fā)生的異常生么都不做的時候可以加一句NULL。流程控制結(jié)構(gòu)-條件控制GOTO語句-無條件地轉(zhuǎn)到標(biāo)簽動態(tài)SQL動態(tài)SQL是指在PL/SQL程序執(zhí)行時生成的SQL語句編譯程序?qū)討B(tài)SQL不做處理，而是在程序運行時動態(tài)構(gòu)造語句、對語句進(jìn)行語法分析并執(zhí)行DDL語句命令和會話控制語句不能在PL/SQL中直接使用，但是可以通過動態(tài)SQL來執(zhí)行執(zhí)行動態(tài)SQL的語法：EXECUTEIMMEDIATEdynamic_sql_string[INTOdefine_variable_list][USINGbind_argument_list];動態(tài)SQL動態(tài)SQL是指在PL/SQL程序執(zhí)行時生成的異常在運行程序時出現(xiàn)的錯誤叫做異常發(fā)生異常后，語句將停止執(zhí)行，控制權(quán)轉(zhuǎn)移到PL/SQL塊的異常處理部分異常有兩種類型：預(yù)定義異常-當(dāng)PL/SQL程序違反Oracle規(guī)則或超越系統(tǒng)限制時隱式引發(fā)用戶定義異常-用戶可以在PL/SQL塊的聲明部分定義異常，自定義的異常通過RAISE語句顯式引發(fā)異常在運行程序時出現(xiàn)的錯誤叫做異常異常ORACLE常用的異常異常ORACLE常用的異常異常在異常發(fā)生時，可以使用ORACLE內(nèi)置的兩個變量：SQLCODE獲取異常錯誤號，SQLERRM獲取異常錯誤消息。在程序中總會有未預(yù)料到的異常，故要在異常處理部分最后加上WHENOTHERS增強程序的健壯性。異常在異常發(fā)生時，可以使用ORACLE內(nèi)置的兩個變量：SQLOracle服務(wù)器執(zhí)行PL/SQL程序內(nèi)存單元保存到游標(biāo)中一次處理一行檢索行提取行游標(biāo)簡介Oracle服務(wù)器執(zhí)行內(nèi)存單元保存到游標(biāo)中一次處理一行檢逐行處理查詢結(jié)果，以編程的方式訪問數(shù)據(jù)游標(biāo)的類型：隱式游標(biāo)REF游標(biāo)顯式游標(biāo)在PL/SQL程序中執(zhí)行DMLSQL語句時自動創(chuàng)建隱式游標(biāo)。顯式游標(biāo)用于處理返回多行的查詢。REF游標(biāo)用于處理運行時才能確定的動態(tài)SQL查詢的結(jié)果游標(biāo)類型游標(biāo)簡介逐行處理查詢結(jié)果，以編程的方式訪問數(shù)據(jù)隱式游標(biāo)REF游標(biāo)顯游標(biāo)簡介在PL/SQL中使用DML語句時自動創(chuàng)建隱式游標(biāo)隱式游標(biāo)自動聲明、打開和關(guān)閉，其名為SQL通過檢查隱式游標(biāo)的屬性可以獲得最近執(zhí)行的DML語句的信息隱式游標(biāo)的屬性有： %FOUND–SQL語句影響了一行或多行時為TRUE %NOTFOUND–SQL語句沒有影響任何行時為TRUE %ROWCOUNT–SQL語句影響的行數(shù) %ISOPEN-游標(biāo)是否打開，始終為FALSE游標(biāo)簡介在PL/SQL中使用DML語句時自動創(chuàng)建隱式游標(biāo)隱式游標(biāo)SQL>SETSERVEROUTPUTONSQL>BEGIN UPDATEprodSETprice=270 WHEREpid=‘0005'; IFSQL%FOUNDTHEN DBMS_OUTPUT.PUT_LINE(‘?dāng)?shù)據(jù)已修改'); ENDIF;END; /游標(biāo)簡介隱式游標(biāo)SQL>SETSERVEROUTPUTON游游標(biāo)簡介隱式游標(biāo)循環(huán)游標(biāo)用于簡化游標(biāo)處理代碼當(dāng)用戶需要從游標(biāo)中提取所有記錄時使用循環(huán)游標(biāo)的語法如下：

FOR<record_index>IN<cursor_name>LOOP <executablestatements>ENDLOOP;游標(biāo)簡介隱式游標(biāo)循環(huán)游標(biāo)用于簡化游標(biāo)處理代碼FOR<re游標(biāo)簡介顯式游標(biāo)在PL/SQL塊的聲明部分定義查詢，該查詢可以返回多行顯式游標(biāo)的操作過程：數(shù)據(jù)庫打開游標(biāo)30George344Roger245James1Stud_mrksStud_namestud_no提取行變量關(guān)閉游標(biāo)顯式游標(biāo)游標(biāo)簡介顯式游標(biāo)在PL/SQL塊的聲明部分定義查詢，該查游標(biāo)簡介帶參數(shù)的顯式游標(biāo)聲明顯式游標(biāo)時可以帶參數(shù)以提高靈活性聲明帶參數(shù)的顯式游標(biāo)的語法如下：

CURSOR<cursor_name>(<param_name><param_type>)ISselect_statement;游標(biāo)簡介帶參數(shù)的顯式游標(biāo)聲明顯式游標(biāo)時可以帶參數(shù)以提高靈活性游標(biāo)簡介使用顯式游標(biāo)更新行2-1允許使用