oracle學(xué)習(xí)心得

一、定位

oracle分兩大塊，一塊是開發(fā)，一塊是管理。開發(fā)主要是寫寫存儲過程、觸發(fā)器什么的，還有就是用Oracle的Develop工具做form。有點類似于程序員，需要有較強的邏輯思維和創(chuàng)造能力，個人覺得會比較辛苦，是青春飯J；管理則需要對oracle數(shù)據(jù)庫的原理有深刻的認(rèn)識，有全局操縱的能力和緊密的思維，責(zé)任較大，因為一個小的失誤就會down掉整個數(shù)據(jù)庫，相對前者來說，后者更看重經(jīng)驗。

因為數(shù)據(jù)庫管理的責(zé)任重大，很少公司愿意請一個剛剛接觸oracle的人去管理數(shù)據(jù)庫。對于剛剛畢業(yè)的年輕人來說，可以先選擇做開發(fā)，有一定經(jīng)驗后轉(zhuǎn)型，去做數(shù)據(jù)庫的管理。當(dāng)然，這個還是要看人個的實際情況來定。

二、學(xué)習(xí)方法

我的方法很簡單，就是：看書、思考、寫筆記、做實驗、再思考、再寫筆記

看完理論的東西，自己靜下心來想想，多問自己幾個為什么，然后把所學(xué)和所想的知識點做個筆記；在想不通或有疑問的時候，就做做實驗，想想怎么會這樣，同樣的，把實驗的結(jié)果記下來。思考和做實驗是為了深入的了解這個知識點。而做筆記的過程，也是理清自己思路的過程。

學(xué)習(xí)的過程是使一個問題由模糊到清晰，再由清晰到模糊的過程。而每次的改變都代表著你又學(xué)到了一個新的知識點。

學(xué)習(xí)的過程也是從點到線，從線到網(wǎng)，從網(wǎng)到面的過程。當(dāng)點變成線的時候，你會有總豁然開朗的感覺。當(dāng)網(wǎng)到面的時候，你就是高手了

很多網(wǎng)友，特別是初學(xué)的人，一碰到問題就拿到論壇上來問，在問前，你有沒有查過書，自己有沒有研究過，有沒有搜索一下論壇？這就叫思維惰性，沒有鉆研的學(xué)習(xí)態(tài)度，不管學(xué)什么東西，都不會成功的。

三、oracle的體系

oracle的體系很龐大，要學(xué)習(xí)它，首先要了解oracle的框架。在這里，簡要的講一下oracle的架構(gòu)，讓初學(xué)者對oracle有一個整體的認(rèn)識。

1、物理結(jié)構(gòu)（由控制文件、數(shù)據(jù)文件、重做日志文件、參數(shù)文件、歸檔文件、密碼文件組成）

控制文件：包含維護(hù)和驗證數(shù)據(jù)庫完整性的必要信息、例如，控制文件用于識別數(shù)據(jù)文件和重做日志文件，一個數(shù)據(jù)庫至少需要一個控制文件

數(shù)據(jù)文件：存儲數(shù)據(jù)的文件

重做日志文件：含對數(shù)據(jù)庫所做的更改記錄，這樣萬一出現(xiàn)故障可以啟用數(shù)據(jù)恢復(fù)。一個數(shù)據(jù)庫至少需要兩個重做日志文件

參數(shù)文件：定義Oracle例程的特性，例如它包含調(diào)整SGA中一些內(nèi)存結(jié)構(gòu)大小的參數(shù)

歸檔文件：是重做日志文件的脫機副本，這些副本可能對于從介質(zhì)失敗中進(jìn)行恢復(fù)很必要。密碼文件：認(rèn)證哪些用戶有權(quán)限啟動和關(guān)閉Oracle例程

2、邏輯結(jié)構(gòu)（表空間、段、區(qū)、塊）

表空間：是數(shù)據(jù)庫中的基本邏輯結(jié)構(gòu)，一系列數(shù)據(jù)文件的集合。段：是對象在數(shù)據(jù)庫中占用的空間

區(qū)：是為數(shù)據(jù)一次性預(yù)留的一個較大的存儲空間

塊：ORACLE最基本的存儲單位，在建立數(shù)據(jù)庫的時候指定

3、內(nèi)存分配（SGA和PGA）

SGA：是用于存儲數(shù)據(jù)庫信息的內(nèi)存區(qū)，該信息為數(shù)據(jù)庫進(jìn)程所共享。它包含Oracle服務(wù)器的數(shù)據(jù)和控制信息,它是在Oracle服務(wù)器所駐留的計算機的實際內(nèi)存中得以分配，如果實際內(nèi)存不夠再往虛擬內(nèi)存中寫。

PGA：包含單個服務(wù)器進(jìn)程或單個后臺進(jìn)程的數(shù)據(jù)和控制信息，與幾個進(jìn)程共享的SGA正相反PGA是只被一個進(jìn)程使用的區(qū)域，PGA在創(chuàng)建進(jìn)程時分配在終止進(jìn)程時回收

4、后臺進(jìn)程（數(shù)據(jù)寫進(jìn)程、日志寫進(jìn)程、系統(tǒng)監(jiān)控、進(jìn)程監(jiān)控、檢查點進(jìn)程、歸檔進(jìn)程、服務(wù)進(jìn)程、用戶進(jìn)程）

數(shù)據(jù)寫進(jìn)程：負(fù)責(zé)將更改的數(shù)據(jù)從數(shù)據(jù)庫緩沖區(qū)高速緩存寫入數(shù)據(jù)文件日志寫進(jìn)程：將重做日志緩沖區(qū)中的更改寫入在線重做日志文件

系統(tǒng)監(jiān)控：檢查數(shù)據(jù)庫的一致性如有必要還會在數(shù)據(jù)庫打開時啟動數(shù)據(jù)庫的恢復(fù)進(jìn)程監(jiān)控：負(fù)責(zé)在一個Oracle進(jìn)程失敗時清理資源

檢查點進(jìn)程：負(fù)責(zé)在每當(dāng)緩沖區(qū)高速緩存中的更改永久地記錄在數(shù)據(jù)庫中時,更新控制文件和數(shù)據(jù)文件中的數(shù)據(jù)庫狀態(tài)信息。

歸檔進(jìn)程：在每次日志切換時把已滿的日志組進(jìn)行備份或歸檔服務(wù)進(jìn)程：用戶進(jìn)程服務(wù)。

用戶進(jìn)程：在客戶端，負(fù)責(zé)將用戶的SQL語句傳遞給服務(wù)進(jìn)程，并從服務(wù)器段拿回查詢數(shù)據(jù)。

5、oracle例程：Oracle例程由SGA內(nèi)存結(jié)構(gòu)和用于管理數(shù)據(jù)庫的后臺進(jìn)程組成。例程一次只能打開和使用一個數(shù)據(jù)庫。

6、SCN(SystemChangeNumber)：系統(tǒng)改變號，一個由系統(tǒng)內(nèi)部維護(hù)的序列號。當(dāng)系統(tǒng)需要更新的時候自動增加，他是系統(tǒng)中維持?jǐn)?shù)據(jù)的一致性和順序恢復(fù)的重要標(biāo)志。

四、深入學(xué)習(xí)管理：可以考OCP證書，對oracle先有一個系統(tǒng)的學(xué)習(xí)，然后看OracleConcepts、oracleonlinedocument,對oracle的原理會有更深入的了解，同時可以開始進(jìn)行一些專題的研究如：RMAN、RAS、STATSPACT、DATAGUARD、TUNING、BACKUP&RECOVER等等。

開發(fā)：對于想做Oracle開發(fā)的，在了解完Oracle基本的體系結(jié)構(gòu)之后，可以重點關(guān)注PL/SQL及Oracle的開發(fā)工具這一部分。PL/SQL主要是包括怎么寫SQL語句，怎么使用Oracle本身的函數(shù)，怎么寫存儲過程、存儲函數(shù)、觸發(fā)器等。Oracle的開發(fā)工具主要就是Oracle自己的DeveloperSuite（OracleFormsDeveloperandReportsDeveloper這些），學(xué)會如何熟練使用這些工具。

資料引用:

擴展閱讀：Oracle學(xué)習(xí)心得

成功源自勤奮

ORACLE學(xué)習(xí)筆錄

ORACLE教學(xué)視頻

1.開始---運行---CMD---SQLPLUS/NOLOG注釋：進(jìn)入不用輸入用戶、密碼的模式

2.SQL->CONN/ASSYSDBA;或SQL->CONNSYS/TIGERASSYSDBA;注釋：以SYS身份登錄

3.SQL->SHOWUSER;注釋：顯示當(dāng)前用戶

4.SQL->ALTERUSERSYSACCOUNTUNLOCK;注釋：使SYS用戶處于解鎖狀態(tài)

5.SQL->ALTERUSERSCOTTIDENTIFIEDBYTIGER;注釋：修改SCOTT的密碼為：TIGER

SQL*PLUS登錄的快捷方式：發(fā)送SQLPLUS到桌面快捷方式。在其右鍵屬性目標(biāo)里加入“CONN/ASSYSDBA”或其他語句.如下圖：

===============================================================================登陸時出現(xiàn)ORA--12560協(xié)議適配器錯誤的解決方法。

右鍵單擊我的電腦，屬性--高級--環(huán)境變量---系統(tǒng)變量--新建，變量名=ORACLE_SID變量值=XXXX,XXXX就是你的DATABASESID

這里軟件安裝時默認(rèn)創(chuàng)建了一個實例（即數(shù)據(jù)庫）ORCL，當(dāng)然默認(rèn)安裝時，實例名稱你可以隨意起。當(dāng)然這里的變量值隨著你的實例名稱的改變而改變。例如

成功源自勤奮

===============================================================================關(guān)閉實例（數(shù)據(jù)庫）的解決方法及啟動方法

前提是：必須以DBA的身份登錄數(shù)據(jù)庫方可關(guān)掉數(shù)據(jù)庫

SQL->SHUTDOWN注釋：把當(dāng)前數(shù)據(jù)庫關(guān)掉了，再連接時會顯示“已連接到空閑例程”如圖

解決方法：步驟（一）：關(guān)掉當(dāng)前使用的SQLPLUS

步驟（二）：把服務(wù)里面ORACLESERVERORCL重新啟動即可步驟（三）：重新啟動SQLPLUS就可以連接到實例上去了

===============================================================================ORACLE登錄密碼問題

===============================================================================1.SYS授權(quán)

成功源自勤奮

SQL->CONN/ASSYSDBA;注釋：授權(quán)人須具有DBA的權(quán)限，因此以SYS登錄以SYS身份創(chuàng)建一個名稱為XIAOMING的用戶，操作如下

在不給XIAOMING賦予登錄的權(quán)限的情況下，XIAOMING是不能登錄數(shù)據(jù)庫的，如下圖：

以SYS身份把連接數(shù)據(jù)庫的權(quán)限賦予XIAOMING

這時候再用XIAOMING來登錄數(shù)據(jù)庫，看看效果如何，圖如下：

附加說明：

SQL->GRANTCONNECTTOXIAOMING;等價于SQL->GRANTCREATESESSIONTOXIAOMING;注釋：SYS用戶授予小明連接數(shù)據(jù)庫的權(quán)限

SQL->GRANTRESOURCETOXIAOMING;等價于SQL->GRANTCREATETABLETOXIAOMING;注釋：SYS用戶授予小明來創(chuàng)建表的權(quán)限

當(dāng)然SYS有授權(quán)的權(quán)力，當(dāng)然也有回收權(quán)限的權(quán)力SQL->CONN/ASSYSDBA;

SQL->REVOKECONNECTFROMXIAOMING;注釋：SYS回收XIAOMING登錄的權(quán)限SQL->CONNXIAOMING/TIGER;

SQL->CREATETABLETEXT(USERIDVARCHAR2(25),USERNAMEVARCHAR2(38));

想要查看表TEXT的結(jié)構(gòu)SQL->DESCTEXT;

2.用戶SCOTT把他的EMP表的查詢權(quán)限賦予XIAOMINGSQL->GRANTSELECTONEMPTOXIAOMING;注釋：SCOTT用戶賦予XIAOMING查詢他EMP表

的權(quán)限

SQL->GRANTUODATEONEMPTOXIAOMING;注釋：SCOTT用戶賦予XIAOMING更改他EMP

表的權(quán)限

SQL->GRANTALLONEMPTOXIAOMING;注釋：SCOTT用戶賦予XIAOMING對他EMP表進(jìn)行

所有操作的權(quán)限包括（查詢，修改，刪除）

SQL->CONNXIAOMING/TIGER;

SQL->SELECT*FROMSCOTT.EMP;注釋：此時XIAOMING可以查詢SCOTT的EMP表3.用戶SCOTT收回賦予用戶XIAOMING的查詢權(quán)限

成功源自勤奮

4.系統(tǒng)用戶SYS回收SCOTT賦予XIAOMING對SCOTT的EMP表的查詢權(quán)限

5.權(quán)限的維護(hù)

希望XIAOMING用戶可以去查詢SCOTT的EMP表、還希望XIAOMING可以把這個權(quán)限繼續(xù)給別人。

如果是對象權(quán)限，對象權(quán)限傳遞就加入withgrantoption

這時候XIAOMING可以把對SCOTT.EMP表的查詢權(quán)限往下傳給XIAOHONG。

SQL->CONN/ASSYSDBA;注釋：首先要以SYS的身份登錄，才能創(chuàng)建XIAOHONG用戶

SQL->CREATEUSERXIAOHONGIDENTIFIEDBYTIGER;注釋：此時成功創(chuàng)建了XIAOHONG用戶SQL->GRANTCONNECTTOXIAOHONG;注釋：SYS授予XIAOHONG登錄數(shù)據(jù)庫的權(quán)限SQL->CONNXIAOMING/TIGER;注釋：此時登錄XIAOMING用戶

SQL->GRANTSELECTONSCOTT.EMPTOXIAOHONG;注釋：這時XIAOMING可以把SCOTT的

EMP表的查詢權(quán)限授予XIAOHONG

如果是系統(tǒng)權(quán)限時，系統(tǒng)權(quán)限傳遞就加入WITHADMINOPTIONSys授予XIAOMING權(quán)限時：

SQL->GRANTCONNECTTOXIAOMINGWITHADMINOPTION;其后面的步驟和對象權(quán)限類似。？如果SCOTT把XIAOMING對EMP表的查詢權(quán)限回收，那么XIAOMING會怎樣?查詢權(quán)限傳遞路線：SCOTT>XIAOMING>XIAOHONG

把XIAOMING的權(quán)限回收，那么XIAOHONG對Scott.emp表的權(quán)限也撤銷了。(級聯(lián)回收)注意：如果這里的SCOTT系統(tǒng)用戶替換成SYS的話，

此時把XIAOMING的系統(tǒng)權(quán)限回收，那么XIAOHONG的系統(tǒng)權(quán)限沒有被回收（對于系統(tǒng)權(quán)限來說，就不是級聯(lián)回收了）

===============================================================================6．使用PROFILE管理用戶口令

PROFILE是口令限制，資源限制的命令集合，當(dāng)建立數(shù)據(jù)庫時，ORACLE會自動建立名稱為DEFAULT的PROFILE。當(dāng)建立用戶沒有指定PROFILE選項，那ORACLE就會將DEFAULT分配給用戶。(1)賬戶鎖定

概述：指定該賬戶（用戶）登陸時最多可以輸入密碼的次數(shù)，也可以指定用戶鎖定的時間（天）一般用DBA的身份去執(zhí)行該命令。

例子：指定SCOTT這個用戶最多只能嘗試3次登陸，鎖定時間為2天，讓我們看怎么實現(xiàn)。SQL->CREATEPROFILELOCK_ACCOUNTLIMITFAILED_LOGIN_ATTEMPTS3

PASSWORD_LOCK_TIME2;注意：紅色字體為固定句式，LOCK_ACCOUNT是文件名

SQL->ALTERUSERSCOTTPROFILELOCK_ACCOUNT;注意：給SCOTT登錄的限制(2)給賬戶解鎖

SQL->ALTERUSERSCOTTACCOUNTUNLOCK;(3)終止命令

為了讓用戶定期修改密碼可以使用終止口令的指令來完成，同樣這個命令也需要DBA身份來操作。

例子：給前面創(chuàng)建的用戶XIAOMING創(chuàng)建一個PROFILE文件，要求該用戶每隔10天要修改

成功源自勤奮

自家的登陸密碼，寬限期為2天�？纯丛趺醋觥�

SQL->CREATEPROFILEMYPROFILELIMITPASSWORD_LIFE_TIME10PASSWORD_GRACE_TIME2;SQL-l>ALTERUSERXIAOMINGPROFILEMYPROFILE;

(4)口令歷史

概述：如果希望用戶在修改密碼時，不能使用以前使用過的密碼，可使用口令歷史，這樣Oracle就會將口令修改的信息存放到數(shù)據(jù)字典中，這樣當(dāng)用戶修改密碼時，ORACLE就會對新舊密碼進(jìn)行比較，當(dāng)發(fā)現(xiàn)新舊密碼一樣時，就提示用戶重新輸入密碼。例子：

1)建立PROFILE

SQL->CREATEPROFILEPASSWORD_HISTORYLIMITPASSWORD_LIFE_TIME10PASSWORD_GRACE_TIME2PASSWORD_REUSE_TIME10

注意：PASSWORD_REUSE_TIME//指定口令可重用時間(即10天后就可以重用)2）分配給某個用戶。

SQL->ALTERUSERXIAOMINGPROFILEPASSWORD_HISTORY;

3）刪除PROFILE

概述：當(dāng)不需要某個PROFILE文件時，可以刪除該文件。SQL->DROPPROFILEPASSWORD_HISTORY;【CASCADE】

注意：如果該文件已經(jīng)分發(fā)給某個用戶了，要刪除的話，得在后面添加CASCADE

==============================================================================

Oracle卸載

1.2.3.4.5.

運行ORACLE的UNIVERSALINSTALLER

運行REGEDIT進(jìn)入注冊表，刪除時一定要小心別刪錯了進(jìn)入HKEY_LOCAL_MACHINE\\SOFTWARE\\ORACLE

HKEY_LOCAL_MACHINE\\SYSTEM\\CURRENTCONTROLSET\\SERVICES下以O(shè)RACLE開頭的值。重啟計算機刪除硬盤上的ORACLE目錄，如果該目錄不讓刪除，那么把這個目錄改成一個別的名字，然后重啟機器，再刪除它。刪除C:\\PROGRAMFILE下的ORACLE目錄

===============================================================================連接命令：

（1）CONN[ECT]

當(dāng)用特權(quán)用戶身份連接時，必須帶上ASSYSDBA或是ASSYSOPER（2）DISC[ONNECT]

說明：該命令用來斷開與當(dāng)前數(shù)據(jù)庫的連接（3）PASSW[ORD]

說明：該命令用于修改用戶的密碼，如果要想修改其他用戶的密碼，需要用

SYS/SYSTEM登陸。

（4）SHOWUSER

說明：顯示當(dāng)前用戶名（5）EXIT

說明：該命令會斷開與數(shù)據(jù)庫的連接，同時退出SQLPLUS文件操作命令

（1）START和@

說明;運行SQL腳本

案例：SQL->@D:\\A.SQL或者SQL->STARTD:\\A.SQL

（2）EDIT

說明：該命令可以編輯指定的SQL腳本案例：SQL->EDITD:\\A.SQL

（3）SPOOL

說明：該命令可以將SQLPLUS屏幕上的內(nèi)容輸出到指定文件中去案例：SQL->SPOOLD:\\B.SQL并輸入SQL->SPOOLOFF

成功源自勤奮

交互式命令（1）&

說明:可以代替變量，而該變量在執(zhí)行時，需要用戶輸入。案例：

SQL->SELECT*FROMEMPWHEREJOB=’&JOB’

顯示和設(shè)置環(huán)境變量

概述：可以用來控制輸出的格式，SETSHOW如果希望永久的保存相關(guān)的設(shè)置，可以去修改GLOGIN.SQL腳本。（1）LINESIZE

說明:設(shè)置顯示行的寬度，默認(rèn)是80個字符SQL->SHOWLINESIZE;SQL->SETLINESIZE90;

操作如下圖：

（2）PADESIZE

說明:設(shè)置每頁顯示的行數(shù)目，默認(rèn)是14用法和LINESIZE一樣

===============================================================================PL/SQL中把表中日期（DATE）的格式修改。(原先里面的是DD-MM-YY)ALTERSESSIONSETNLS_DATE_FORMAT=’YYYY-MM-DD’;

注意：這樣修改只能對于當(dāng)前操作有效，并不能永久修改

===============================================================================刪除數(shù)據(jù):

DELETEFROMSTUDENT;

刪除所有記錄，表結(jié)構(gòu)還在，寫日志，可以恢復(fù)的，速度慢DROPTABLESTUDENT;//刪除表的結(jié)構(gòu)和數(shù)據(jù)

DELETEFROMSTUDENTWHEREXH=’A001’//刪除一條記錄TRUNCATETABLESTUDENT

刪除表中所有記錄，表結(jié)構(gòu)還在，不寫日志，無法找回刪除的記錄，速度快。

===============================================================================當(dāng)刪掉一個表時，想把它恢復(fù)。則需要回滾。語句如：

SQL->SAVEPOINTAA;注意：再刪除表之前進(jìn)行該操作，設(shè)置一個名為AA的保存點

SQL->ROLLBACKTOAA;注意：當(dāng)刪除表后的操作，就可以恢復(fù)到設(shè)置保存點的時候的數(shù)據(jù)===============================================================================查看表的結(jié)構(gòu)SQL->DESC表名;查詢所有列

SELECT*FROMDEPT;//這個會對查詢結(jié)果影響很大，速度慢查詢指定[列]

成功源自勤奮

SELECTENAME,SAL,JOB,DEPTNOFROMEMP;//這種查詢方式比較快查看查詢所用的時間{

SETTIMINGON;//執(zhí)行完之前得操作，當(dāng)執(zhí)行結(jié)束到會顯示查詢所需要的時間SETTIMINGOFF;}

===============================================================================超級復(fù)制（給表中插入它原本的東西）

INSERTINTOUSERS(字段，字段)SELECT字段，字段FROMUSER;查詢表中前面幾行

SELECT*FROMUSERSWHEREROWNUM‘1-1月-1982’;SELECT*FROMEMPWHERESALBETWEEN800AND1000;如何使用LIKE

%表示任意0到多個字符，_（下劃線）代表任意的單個字符

SELECT*FROMSTUDENTWHEREXMLIKE‘%G’;或WHEREXMLIKE‘X%’;

SELECT*FROMSTUDENTWHEREXMLIKE‘__O%’;注意：查詢第三個字母是O的姓名；在WHERE中使用IN

SELECT*FROMSTUDENTWHEREXMIN(‘XIAOMING’,’XIAOHONG’);ISNULL使用;

SELECT*FROMSTUDDENTWHEREXMISNULL;注意：切記不是XM=NULLORDERBYDESC或者ORDERBYASC

SELECT*FROMEMPORDERBYEMPTNO[ASC],SALDESC;//按照部門號升序而雇員的工資降序

排列。

使用到的別名排序

SELECTSAL“年薪”FROMEMPORDERBY“年薪”ASC；別名需要用雙引號圈中或者SELECTSALAS“年薪”FROMEMPORDERBY“年薪”ASC;

成功源自勤奮

GROUPBY和HAVING子句

？顯示每個部門的平均工資與最高工資

SELECTAVG(SAL),MAX(SAL),DEPTNOFROMEMPGROUPBYDEPTNO;//查詢字段里面一定要

有DEPTNO，因為是按照他分組的。

？顯示每個部門的每種崗位的平均工資與最高工資。

SELECTAVG(SAL),MAX(SAL),DEPTNO,JOBFROMEMPGROUPBYDEPTNO,JOB;

？顯示平均工資低于201*的部門號和它的平均工資。

SELECTAVG(SAL),MAX(SAL),DEPTNOFROMEMPGROUPBYDEPTNOHAVINGAVG(SAL)ALL(SELECTSALFROMEMPWHEREDEPTNO=30);等價于

SELECTENAME,SAL,DEPTNOFROMEMPSAL>(SELECTMAX(SAL)FROMEMPWHEREDEPTNO=30);注意：提倡使用第二種方法，查詢速度快ANY的用法

成功源自勤奮

SELECTENAME,SAL,DEPTNOFROMEMPWHERESAL>ANY(SELECTSALFROMEMPWHEREDEPTNO=30);等價于

SELECTENAME,SAL,DEPTNOFROMEMPSAL>(SELECTMIN(SAL)FROMEMPWHERE

DEPTNO=30);注意：提倡使用第二種方法，查詢速度快？如何查詢與SMITH的部門和崗位完全相同的所有雇員

SELECT*FROMEMPWHERE(DEPTNO,JOB)=(SELECTDEPTNO,JOB)FROMEMPWHERE=‘SMITH’);注意：前后兩個字段要對應(yīng)

？如何顯示高于自己部門平均工資的員工的信息。

SELECTA.*FROMEMPA,(SELECTDEPTNO,AVG(SAL)ASPPFROMEMPGROUPBYDEPTNO)BWHEREA.DEPTNO=B.DEPTNOANDA.SAL>B.PP;

===============================================================================在FROM子句中使用子查詢

這里需要說明的當(dāng)在FROM子句中使用子查詢時，該子查詢會被作為一個視圖來對待，因此叫做內(nèi)嵌視圖，當(dāng)在FROM子句中使用子查詢時，必須給子查詢指定別名。ORACLE分頁一共有三種方式1．ROWNUM分頁

SQL->SELECT*FROMDEPT

2.顯示ROWNUM[Oracle分配的]

比如要查詢前三條記錄的話：

SELECTA.*,ROWNUMNFROM(SELECT*FROMDEPT)AWHEREROWNUM

成功源自勤奮

SELECT*FROM(SELECTA.*,ROWNUMNFROM(SELECT*FROMDEPT)AWHEREROWNUM=2;//但他這種方法速度相對于其他數(shù)據(jù)庫來說比較快3.幾個查詢變化

a.如果指定查詢列，只需修改最里面的子查詢。

如：SELECT*FROM(SELECTA.*,ROWNUMNFROM(SELECTDEPTNO,LOCFROMDEPT)AWHEREROWNUM=2;

或者

SELECT*FROM(SELECTDEPTNO,LOC,ROWNUMNFROMDEPTWHEREROWNUM=2;

b.排序,只需修改最里面的子查詢

SELECT*FROM(SELECTA.*,ROWNUMNFROM(SELECTDEPTNO,LOCFROMDEPTORDERBYDEPTNO)AWHEREROWNUM=2;

===============================================================================

*用查詢結(jié)果來創(chuàng)建新表*(注意)和SQLSERVER的區(qū)別

CREATETABLEMYTABLE(ID,NAME,SAL,JOB,DEPTNO)ASSELECTEMPNO,ENAME,SAL,JOB,DEPTNOFROMEMP;注意：紅色字段得一一對應(yīng)這個區(qū)別于sql201*

SQL201*->SELECTEMPNOASID,ENAME,SAL,JOB,DEPTNOINTOMYTABLEFROMEMP;

===============================================================================合并查詢

有時在實際應(yīng)用中，為了合并多個SELECT語句的結(jié)果，可以使用集合操作符號UNION,UNIONALL,INTERSECT,MINUS(1)UNION

該操作符用于取得兩個結(jié)果集的并集，當(dāng)使用該操作符時，會自動去掉結(jié)果中重復(fù)行SELECTENAME,SAL,JOBFROMEMPWHERESAL>2500UNIONSELECTENAME,SAL,JOBFROMEMPWHEREJOB=‘MANAGER’;

(2))UNIONALL

該操作符與UNION相似，但是它不會取消重復(fù)行，而且不會排序。

成功源自勤奮

SELECTENAME,SAL,JOBFROMEMPWHERESAL>2500UNIONALLSELECTENAME,SAL,JOBFROMEMPWHEREJOB=‘MANAGER’;

(3)INTERSECT

使用該操作符用于取得兩個結(jié)果集的交集。

SELECTENAME,SAL,JOBFROMEMPWHERESAL>2500INTERSECTSELECTENAME,SAL,JOBFROMEMPWHEREJOB=‘MANAGER’;

(4)MINUS

使用該操作符用于取得兩個結(jié)果集的差集，它只會顯示存在第一個集合中，而不存在第二集合中的數(shù)據(jù)。

SELECTENAME,SAL,JOBFROMEMPWHERESAL>2500MINUSSELECTENAME,SAL,JOBFROMEMPWHEREJOB=‘MANAGER’;

===============================================================================

創(chuàng)建數(shù)據(jù)庫有兩種方法

1)通過ORACLE提供的向?qū)Чぞ?/必須掌握

開始--程序--ORACLE-ORADBA10G_HOME1--CONFIGURATIONANDMIGRATIONTOOLS--DATABASECONFIGURATIONASSISTANT

2)我們可以用手工步驟直接創(chuàng)建

===============================================================================ORACLE中操作數(shù)據(jù)使用TO_DATE函數(shù)

ALTERSESSIONSETNLS_DATE_FORMAT=’YYYY-MM-DD’;//改變當(dāng)前用戶日期格式方法1：

INSERTINTOEMPVALUES(8976,‘HAH’,‘CLERK’,6754,‘18-12月-201*’,9876.00,0,20);方法2：

INSERTINTOEMPVALUES(8976,‘HAH’,‘CLERK’,6754,TO_DATE(‘201*-12-18’,‘YYYY-MM-DD’)9876.00,0,20);

===============================================================================

成功源自勤奮

利用子查詢來插入大量數(shù)據(jù)（方法和前面超級復(fù)制一樣）這個區(qū)分于SQLSERVER201*Oracle：

INSERTINTOUSER2(字段1，字段2)SELECT字段3，字段4FROMUSERS【W(wǎng)HERE條件】注意：ORACLE這里USER2要在這之前建好

Sql201*:SELECT字段1，字段2INTOUSER2FROMUSERS【W(wǎng)HERE條件】

注意：SQLSERVER這里USER2不能在該操作之前存在?希望員工Scott的崗位，工資，補助與smith員工一樣

UPDATEEMPSET(JOB,SAL,COMN)=(SELECTJOB,SAL,COMNFROMEMPWHEREENAME=’SMITH’)WHEREENAME=’SCOTT’;

==============================================================================

事務(wù)

事務(wù)用于保證數(shù)據(jù)的一致性，它由一組相關(guān)的DML（即增加，刪除，修改）語句組成，該組的DML語句要么全部成功，要么全部失敗。

事務(wù)和鎖

當(dāng)執(zhí)行事務(wù)操作時（DML語句），ORACLE會在被作用的表上加鎖，防止其他用戶修改表的結(jié)構(gòu)。

提交事務(wù)

當(dāng)執(zhí)行COMMIT語句后可以提交事務(wù)，當(dāng)執(zhí)行了COMMIT語句后，會確認(rèn)事務(wù)的變化，結(jié)束事務(wù)，刪除保存點，釋放鎖，當(dāng)使用COMMIT語句結(jié)束事務(wù)之后，其他會話將可以查看到事務(wù)變化后的新數(shù)據(jù)。

回退事務(wù)

在介紹回退事務(wù)前，我們先介紹一下保存點（SAVEPOINT）的概念和作用。保存點是事務(wù)中的一點，用于取消部分事務(wù)、當(dāng)結(jié)束事務(wù)時，會自動刪除該事務(wù)所定義的所有保存點，當(dāng)執(zhí)行ROLLBACK時，通過指定保存點可以回退到指定的點。SQL->SAVEPOINTA1;SQL->DELETEFROMEMP;SQL->ROLLBACKTOA1;

SQL->COMMIT;//提交，確保之前所作的是正確的，提交之后，保存點也隨之消失了注意：如果在操作的過程中意外退出了，那么系統(tǒng)會默認(rèn)執(zhí)行COMMIT提交操作事務(wù)的幾個重要操作：1）設(shè)置保存點

SAVEPOINTA3)取消部分事務(wù)

ROLLBACKTOA4)取消全部事務(wù)

ROLLBACK

只讀事務(wù)

只讀事務(wù)是指只允許執(zhí)行查詢的操作，而不允許執(zhí)行任何其他DML操作的事務(wù)，使用只讀事務(wù)可以確保用戶只能取得某時間點的數(shù)據(jù)。假定機票代售點每天18點開始統(tǒng)計今天的銷售情況。這時可以使用只讀事務(wù)，在設(shè)置了只讀事務(wù)后，盡管其他會話可能會提交新的事務(wù)，但是只讀事務(wù)將不會取得最新數(shù)據(jù)的變化，從而可以保持取得特定時間點的數(shù)據(jù)信息。

成功源自勤奮

設(shè)置只讀事務(wù)

SETTRANSACTIONREADONLY

===============================================================================

SQL函數(shù)的使用

字符函數(shù)是ORACLE中最常用的函數(shù)，我們來看看有哪些字符函數(shù)。LOWER（CHAR）：將字符串轉(zhuǎn)化成小寫的格式UPPER（CHAR）：將字符串轉(zhuǎn)化成大寫的格式LENGTH（CHAR）:返回字符串的長度SUBSTR(CHAR,M,N):取字符串的子串

REPLACE（CHAR1,SEARCH_STRING,REPLACE_STRING）INSTR(CHAR1,CHAR2):取子串在字符串中的位置

這點區(qū)別于SQLSERVER中，SQLSERVER用PATINDEX(‘%,%’,字符串)//查找逗號出現(xiàn)的位置字符串轉(zhuǎn)化函數(shù)SELECTCAST("2768453"ASNUMBER)-3FROMDUAL;

數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)的輸入?yún)��?shù)和返回值的數(shù)據(jù)類型都是數(shù)字類型的，數(shù)學(xué)函數(shù)包含COS，COSH,EXP,LN,LOG,SIN,SINH,SQRT,TAN,TANH,ACOS,ASIN,ATAN,ROUND,我們講最常用的

ROUND(N,[M]：/該函數(shù)用于執(zhí)行四舍五入，如果省掉M，則四舍五入到整數(shù)；如果M是正

數(shù)，則四舍五入到小數(shù)點的M位后；如果M是負(fù)數(shù)，則四舍五入到小數(shù)點M位前。

TRUNC(N,[M])：該函數(shù)用于截取數(shù)字，如果省掉M，就截掉小數(shù)部分，如果M是正數(shù)就截

取到小數(shù)的M位后，如果M是負(fù)數(shù)，則截取到小數(shù)點的前M位。

MOD(M,N)：取模

FLOOR(N)：返回小于或等于N的最大整數(shù)CEIL(N)：返回大于或等于N的最小整數(shù)

對數(shù)字的處理，在財務(wù)系統(tǒng)或銀行系統(tǒng)中用的最多，不同的處理方法，對財務(wù)報表有不同的結(jié)果。

例子：ROUND(2456.34)=2456,ROUND(2456.56)=2457,

ROUND(2456.34,1)=2456.3,ROUND(5.67,1)=5.7

TRUNC(5.57,1)=5.5,TRUNC(56.67,-1)=50,MOD(10.3,4)=2.3其他數(shù)學(xué)函數(shù)：

ABS(N):返回數(shù)字N的絕對值A(chǔ)COS(N):返回數(shù)字的反余弦值A(chǔ)SIN(N):返回數(shù)字的反正弦值A(chǔ)TAN(N):返回數(shù)字的反正切值COS(N):返回數(shù)字的余弦值EEP(N):返回E的N次方

LOG(M,N)返回以M為底的對數(shù)值LN(N):以E為底的對數(shù)

POWER(M,N)）:返回m的n次方日期函數(shù)

用于處理DATE類型的數(shù)據(jù)SYSDATE:該函數(shù)返回系統(tǒng)時間

例子：SELECTSYSDATEFROMDUAL;

ADD_MONTHS(D,M)

?查找已經(jīng)入職8個多月的員工

答案：SELECT*FROMEMPWHERESYSDATE>ADD_MONTHS(HIREDATE,8);

成功源自勤奮

？對于每個員工，顯示其加入公司的天數(shù)

答案：SELECTSYSDATE-HIREDATE"入職天數(shù)"FROMEMP;LAST_DAY(D):返回指定日期所在月份的最后一天？找出各月倒數(shù)第三天受雇的所有員工

SELECTHIREDATE,ENAME,LAST_DAY(HIREDATE)FROMEMPWHEREHIREDATE=LAST_DAY(HIREDATE)-2;

轉(zhuǎn)換函數(shù)：

轉(zhuǎn)換函數(shù)用于將數(shù)據(jù)類型從一種轉(zhuǎn)為另外一種，在某些情況下，ORACLESERVER允許值的數(shù)據(jù)類型和實際的不一樣。這時ORACLESERVER會隱形地轉(zhuǎn)化數(shù)據(jù)類型。比如：CREATETABLET1(IDINT);

INSERTINTOT1VALUES(‘10’)//這樣ORACLE會自動的把‘10’-->10CREATTABLET1(IDvarchar2(100));

Insertintot1values(1);這樣ORACLE就會自動得把1-->‘1’TO_CHAR：？日期是否可以顯示：時/分/秒

SELECTENAME,TO_CHAR(HIREDATE,’YYYY-MM-DDHH24:MI:SS’)FROMEMP;

?薪水是否可以顯示指定的貨幣符號。

SELECTENAME,TO_CHAR(HIREDATE,’YYYY-MM-DDHH24:MI:SS’),TO_CHAR(SAL,’L99999.99’)FROMEMP;

SELECTENAME,TO_CHAR(HIREDATE,’YYYY-MM-DDHH24:MI:SS’),TO_CHAR(SAL,’L99,999.99’)FROMEMP;

成功源自勤奮

//其中L99,999.99中L表示本地貨幣號，如果安裝的是中文版，那就是人民幣。

99,999.99是根據(jù)這個題目的要求的，因為SAL是NUMBER(7,2)類型的，也可以隨意指定比如：99999.99或99,999.99或99,999.000注意：L和99999.99中不能加空格

9：代表是數(shù)字，并忽略前面0

0：顯示數(shù)字，如位數(shù)不足時，則用0補齊.：表示小數(shù)點，：表示逗號$：表示美元￥:表示人民幣C:表示國際貨幣

？顯示1980年入職的所有員工答案：

SELECTENAME,TO_CHAR(HIREDATE,‘YYYY’),TO_CHAR(SAL,‘L99,999.99’)FROMEMPWHERETO_CHAR(HIREDATE,’YYYY’)=1980;

？顯示12月份入職的所有員工

答案：

SELECTENAME,TO_CHAR(HIREDATE,‘MM’),TO_CHAR(SAL,‘L99,999.99’)FRMEMPWHERETO_CHAR(HIREDATE,’MM’)=12;

===============================================================================系統(tǒng)函數(shù)：SYS_CONTEXT

1)TERMINAL:當(dāng)前會話客戶所對應(yīng)的終端的標(biāo)識符。2)LANGUSGE：語言

3)DB_NAME:當(dāng)前數(shù)據(jù)庫名稱

4)NLS_DATE_FORMAT:當(dāng)前會話客戶所對應(yīng)的日期格式5)SESSION_USER:當(dāng)前會話客戶所對應(yīng)的數(shù)據(jù)庫用戶名6)HOST：返回數(shù)據(jù)庫所在主機的名稱

成功源自勤奮

7)CURRENT_SCHEMA：顯示當(dāng)前用戶的方案名

固定句式：SELECTSYS_CONTEXT(‘USERENV’,DB_NAME)FROMDUAL;注意：紅色部分是固定的

===============================================================================

數(shù)據(jù)庫管理員

管理數(shù)據(jù)庫的用戶主要是SYS和SYSTEM（SYS相當(dāng)于董事長，SYSTEM相當(dāng)于總經(jīng)理）(1)最重要的區(qū)別，存儲的數(shù)據(jù)的重要性不同

SYS：所有ORACLE的數(shù)據(jù)字典的基表和視圖都存放在SYS用戶中，這些基表和視圖對于ORACLE的運行來說是至關(guān)重要的，由數(shù)據(jù)庫自己維護(hù)，任何用戶都不能手動更改，SYS用戶擁有DBA（數(shù)據(jù)庫管理員）,SYSDBA（系統(tǒng)管理員/最高權(quán)限）,SYSOPER（系統(tǒng)操作員）角色或權(quán)限，是ORACLE權(quán)限中最高的用戶。SYSTEM：用于存放次一級的內(nèi)部數(shù)據(jù)，如：ORACLE的一些特性或工具的管理信息，SYSTEM用戶擁有DBA，SYSDBA角色或系統(tǒng)權(quán)限。(2)其次的區(qū)別，權(quán)限的不同。

SYS用戶必須以ASSYSDBA或ASSYSOPER形式登陸，不能以NORMAL方式登陸數(shù)據(jù)庫。SYSTEM如果正常登陸，它其實就是一個普通的DBA用戶，但是如果以ASSYSDBA登陸，其結(jié)果實際上它是作為SYS用戶登陸的，從登陸信息里我們可以看出來例如：SYS可以建數(shù)據(jù)庫，但SYSTEM不行

===============================================================================

管理初始化參數(shù)

初始化參數(shù)用于設(shè)置實例或是數(shù)據(jù)庫的特征，ORACLE11g提供了200多個初始化參數(shù)，并且每個初始化參數(shù)都要默認(rèn)值。

顯示初始化參數(shù)

（1）SHOWPARAMETER命令注意：DBA權(quán)限的才可以查詢?nèi)绾涡薷膮��?shù)

需要說明的如果你希望修改這些初始化的參數(shù)，可以到文件中去修改實例名

==============================================================================

數(shù)據(jù)庫表的邏輯備份與恢復(fù)

邏輯備份是指使用工具EXPORT將數(shù)據(jù)對象的結(jié)構(gòu)和數(shù)據(jù)導(dǎo)出到文件的過程，邏輯恢復(fù)是指當(dāng)數(shù)據(jù)庫對象被誤操作而損壞后使用工具IMPORT利用備份的文件把數(shù)據(jù)對象導(dǎo)入數(shù)據(jù)庫的過程，物理備份即可在數(shù)據(jù)庫OPEN的狀態(tài)下進(jìn)行也可以關(guān)閉數(shù)據(jù)庫后進(jìn)行，但是邏輯備份和恢復(fù)只能在OPEN狀態(tài)下進(jìn)行。

導(dǎo)出

導(dǎo)出具體分為;導(dǎo)出表，導(dǎo)出方案，導(dǎo)出數(shù)據(jù)三種方式導(dǎo)出使用EXP命令來完成，該命令常用的選項有：

USERID：用于指定執(zhí)行導(dǎo)出操作的用戶名，口令，連接字符串TABLE：用于指定執(zhí)行導(dǎo)出操作的表

成功源自勤奮

OWNER：用于指定執(zhí)行導(dǎo)出操作的方案FULL=Y：用于指定導(dǎo)出操作的數(shù)據(jù)庫

INCTYPE：用于指定執(zhí)行導(dǎo)出操作的增量類型

ROWS：用于指定執(zhí)行導(dǎo)出操作是否要導(dǎo)出表中的數(shù)據(jù)FILE：用于指定導(dǎo)出文件名導(dǎo)出表：

（1）導(dǎo)出自己的表

EXPUSERID=SCOTT/TIGER@ORCLTABLES=(EMP)FILE=D:\\E1.DMP（2）導(dǎo)出其他方案的表

如果用戶要導(dǎo)出其他方案的表，則需DBA的權(quán)限或是

EXP_FULL_DATABASE的權(quán)限，比如SYSTEM可以導(dǎo)出SCOTT的表

EXPUSERID=SYSTEM/TIGER@ORCLTABLES=(SCOTT.EMP)FILE=D:\\E1.DMP

注意：

在導(dǎo)入和導(dǎo)出的時候，要到ORACLE目錄下的BIN目錄下

操作如下圖：

或快速進(jìn)入

輸出結(jié)果為下圖：

導(dǎo)出多張表：

EXPUSERID=SCOTT/TIGER@ORCLTABLES=(EMP,DEPT)FILE=D:\\E1.DMP

成功源自勤奮

（3）導(dǎo)出表的結(jié)構(gòu)

EXPUSERID=SCOTT/TIGER@ORCLTABLES=(EMP)FILE=D:\\E1.DMPROWS=N

（4）使用直接導(dǎo)出方式

EXPUSERID=SCOTT/TIGER@ORCLTABLES=(EMPFILE=D:\\E1.DMPDIRECT=Y

這種方式比默認(rèn)的常規(guī)方式要快，當(dāng)數(shù)據(jù)量大時，可以考慮使用這樣的方法這時需要數(shù)據(jù)庫的字符集要和客戶端字符完全一致，否則會報錯。

導(dǎo)出方案

導(dǎo)出方案是指使用EXPORT工具導(dǎo)出一個方案或是多個方案中的所有對象（表，索引，約束）和數(shù)據(jù)，并存放到文件中。（1）導(dǎo)出自己的方案

EXPSCOTT/TIGER@ORCLOWNER=SCOTTFILE=D:\\SCOTT.DMP

導(dǎo)出數(shù)據(jù)庫

導(dǎo)出數(shù)據(jù)庫是指利用EXPORT導(dǎo)出所有數(shù)據(jù)庫中的對象及數(shù)據(jù)，要求該用戶具有DBA權(quán)限或是EXP_FULL_DATABASE權(quán)限

EXPUSERID=SYSTEM/TIGER@ORCLFULL=YINCTYPE=COMPLETEFILE=D:\\X.DMP

（2）導(dǎo)出其他方案

如果用戶要導(dǎo)出其他方案，則需要DBA的權(quán)限或是

EXP_FULL_DATABASE的權(quán)限，例如SYSTEM用戶就可以導(dǎo)出任何方案（但SYS另外）EXPSYSTEM/TIGER@ORCLOWNER=(SYSTEM,SCOTT)FILE=D:\\SYSTEM.DMP

===============================================================================

導(dǎo)入

導(dǎo)入就是使用工具import將文件中的對象和數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫中，但是導(dǎo)入要使用的文件必須是export所導(dǎo)出的文件，與導(dǎo)出相似，導(dǎo)入也分為導(dǎo)入表，導(dǎo)入方案，導(dǎo)入數(shù)據(jù)庫三種方式。

IMP常用的選項有

USERID：用于指定執(zhí)行導(dǎo)入操作的用戶名，口令，連接字符串TABLE：用于指定執(zhí)行導(dǎo)入操作的表

成功源自勤奮

FORMUSER：用于指定源用戶TOUSER：用于指定目標(biāo)用戶FILE：用于指定導(dǎo)入文件名

FULL=Y：用于指定執(zhí)行導(dǎo)入整個文件

INCTYPE：用于指定執(zhí)行導(dǎo)入操作的增量類型ROWS：指定是否要導(dǎo)入表行數(shù)據(jù)IGNORE:如果表存在，則只導(dǎo)入數(shù)據(jù)(1)導(dǎo)入自己的表

IMPUSERID=SCOTT/TIGER@ORCLTABLES=(EMP)FILE=D:\\E1.DMP

(2)導(dǎo)入其他用戶的表

要求該用戶具有DBA的權(quán)限，或是IMP_FULL_DATABASE的權(quán)限

IMPUSERID=SYSTEM/TIGER@ORCLTABLES=(EMP)FILE=D:\\E1.DMPTOUSER=SCOTT(3)導(dǎo)入表的結(jié)構(gòu)

IMPUSERID=SCOTT/TIGER@ORCLTABLES=(EMP)FILE=D:\\E1.DMPROWS=N

(5)如果對象（如此表）已經(jīng)存在可以只導(dǎo)入表的數(shù)據(jù)

IMPUSERID=SCOTT/TIGER@ORCLTABLES=(EMP)FILE=D:\\E1.DMPIGNORE=Y導(dǎo)入方案

導(dǎo)入方案是指使用IMPORT工具將文件中的對象和數(shù)據(jù)導(dǎo)入到一個或是多個方案中。如果要導(dǎo)入其他方案，要求該用戶具有DBA的權(quán)限，或是IMP_FULL_DATABASE的權(quán)限（1）導(dǎo)入自己的方案

IMPUSERID=SCOTT/TIGER@ORCLFILE=D:\\XXX.DMP

（2）導(dǎo)入其他方案

要求該用戶具有DBA的權(quán)限

IMPUSERID=SYSTEM/TIGER@ORCLFILE=D:\\XXX.DMPFORMUSER=SYSTEMTOUSER=SCOTT

導(dǎo)入數(shù)據(jù)庫

在默認(rèn)情況下，當(dāng)導(dǎo)入數(shù)據(jù)庫時，會導(dǎo)入所有對象結(jié)構(gòu)和數(shù)據(jù)，案例如下：

IMPUSERID=SYSTEM/TIGERFULL=YFILE=D:\\XXX.DMP//這里數(shù)據(jù)庫實例都不用寫了，默認(rèn)===============================================================================數(shù)據(jù)字典

數(shù)據(jù)字典是ORACLE數(shù)據(jù)庫中最重要的組成部分，他提供了數(shù)據(jù)庫的一些系統(tǒng)信息

數(shù)據(jù)字典記錄了數(shù)據(jù)庫的系統(tǒng)信息，他是只讀表和視圖的集合，數(shù)據(jù)字典的所有者為SYS用戶。

用戶只能在數(shù)據(jù)字典上執(zhí)行查詢操作（SELECT語句），而其維護(hù)和修改是由系統(tǒng)自動完成的。

數(shù)據(jù)字典包括字典基表和數(shù)據(jù)字典視圖，其中基表存儲數(shù)據(jù)庫的基本信息，普通不能直接訪問數(shù)據(jù)字典的基表，數(shù)據(jù)字典視圖是基于數(shù)據(jù)字典基表所建立的視圖，普通用戶可以通過查詢數(shù)據(jù)字典視圖取得系統(tǒng)信息，數(shù)據(jù)字典視圖主要包括USER_XXX,ALL_XXX,DBA_XXX三種類型。動態(tài)性能視圖記載了例程啟動后的相關(guān)信息例如：

USER_TABLES:

用于顯示當(dāng)前用戶所擁有的所有表，它只返回用戶所對應(yīng)方案的所有表比如:SELECTTABLE_NAMEFROMUSER_TABLES

成功源自勤奮

ALL_USERS:

用于顯示當(dāng)前用戶可以訪問的所有表。它不僅返回當(dāng)前用戶方案的所有表，還會返回當(dāng)前用戶可以訪問的其他方案的表；

比如：SELECTTABLE_NAMEFROMALL_TABLESDBA_TABLES:

它會顯示所有方案擁有的數(shù)據(jù)庫表，但是查詢這種數(shù)據(jù)庫字典視圖，要求用戶必須是dba角色或是SELECTANYTABLE系統(tǒng)權(quán)限。

例如：當(dāng)用SYSTEM用戶查詢數(shù)據(jù)字典視圖DBA_TABLES時，會返回SYSTEM,SYS,SCOTT…方案所對應(yīng)的數(shù)據(jù)庫表。

===============================================================================用戶名、權(quán)限、角色

在建立用戶時，ORACLE會把用戶的信息存放到數(shù)據(jù)字典中，當(dāng)給用戶授予權(quán)限或是角色時，ORACLE會將權(quán)限和角色的信息存放到數(shù)據(jù)字典。

通過查詢DBA-USERS可以顯示所有數(shù)據(jù)庫用戶的所有信息；

通過查詢數(shù)據(jù)字典視圖DBA_SYS_PRIVS,可以顯示用戶所具有的系統(tǒng)權(quán)限；通過查詢數(shù)據(jù)字典視圖DBA_TAB_PRIVS可以顯示用戶具有的對象權(quán)限；通過查詢數(shù)據(jù)字典DBA_COL_PRIVS可以顯示用戶具有的列權(quán)限；通過查詢數(shù)據(jù)庫字典視圖DBA_ROLE_PRIVS可以顯示用戶所具有的角色例如：要查看SCOTT具有的角色，可查詢DBA_ROLE_PRIVSSELECT*FROMDBA_ROLE_PRIVSWHEREGRANTEE=’SCOTT’

//查詢ORACLE中所有的系統(tǒng)權(quán)限，一般是DBA

SELECT*FROMSYSTEM_PRIVILEGE_MAPORDERBYNAME;//查詢ORACLE中所有的角色，一般是DBASELECT*FROMDBA_ROLES;

//查詢ORACLE中所有對象權(quán)限，一般是DBASELECTDISTINCTPRIVILEGEFROMDBA_TAB_PRIVS;//查詢數(shù)據(jù)庫的表空間

SELECXTTABLESPACE_NAMEFROMDBA_TABLESPACE;

?問題

1．如何查詢一個角色包括的權(quán)限

a.一個角色包含的系統(tǒng)權(quán)限

SELECT*FROMDBA_SYS_PRIVSWHEREGRANTEE=’DBA’//CONNECT應(yīng)大寫另外也可以這樣查看

SELECT*FROMROLE_SYS_PRIVSWHEREROLE=’DBA’b.一個角色包含的對象權(quán)限

SELECT*FROMDBA_TAB_PRIVSWHEREGRANTEE=’CONNECT’

2.如何查看某個用戶，具有什么樣的角色。

SELECT*FROMDBA_ROLE_PRIVSWHEREGRANTEE=’用戶名’

顯示當(dāng)前用戶可以訪問的所有數(shù)據(jù)字典視圖

SELECT*FROMDIETWHERECOMMENTSLIKE‘%GRANT%’

成功源自勤奮

===============================================================================

管理表空間和數(shù)據(jù)文件

表空間是數(shù)據(jù)庫的邏輯組成部分，從物理上講，數(shù)據(jù)庫數(shù)據(jù)存放在數(shù)據(jù)文件中；從邏輯上講，數(shù)據(jù)庫則是存放在表空間中，表空間由一個或是多個數(shù)據(jù)文件組成。ORACLE中邏輯結(jié)構(gòu)包括表空間、段、區(qū)、和塊。

說明一下數(shù)據(jù)庫由表空間構(gòu)成，而區(qū)又是由ORACLE塊構(gòu)成的這樣的一種結(jié)構(gòu)，可以提高數(shù)據(jù)庫的效率。

表空間用于從邏輯上組織數(shù)據(jù)庫的數(shù)據(jù)，數(shù)據(jù)庫邏輯上是由一個或是多個表空間組成。通過表空間可以達(dá)到以下作用：

（1）控制數(shù)據(jù)庫占用的磁盤空間

（2）DBA可以將不同數(shù)據(jù)類型部署到不同的位置，這樣有利于提高I/O性能，同時利

于備份和恢復(fù)等管理操作

建立表空間

建立表空間是使用CREATETABLESPACE命令完成的。需要注意的是，一般情況下，建立表空間是特權(quán)用戶或是DBA來執(zhí)行的。如果用其他用戶來創(chuàng)建表空間，則用戶必須要具有CREATETABLESPAC的系統(tǒng)權(quán)限。建立數(shù)據(jù)表空間

在建立數(shù)據(jù)庫后，為了便于管理表，最好建立自己的表空間Createtablespacedata01datafile‘d:\\test\\data01.dbf’Size20muniformsize128k

注意：20m表示表空間的大小，最大不能超過500m128k表示區(qū)得大小

說明：執(zhí)行完上述命令后，會建立名稱為data01的表空間，并為該表空間建立名稱為data01.dbf的數(shù)據(jù)文件，區(qū)的大小為128k

使用數(shù)據(jù)表空間//由sys創(chuàng)建，登陸scott用戶就可以看到了

Createtablemypart(deptnonumber(4),dnamevarchar2(14),locvarchar2(13))tablespacedata01;改變表空間的狀態(tài)

當(dāng)建立表空間時，表空間處于聯(lián)機的（online）狀態(tài)，此時該表空間是可以訪問的，并且該表空間是可以讀寫的，即可以查詢該表空間的數(shù)據(jù)，而且還可以在表空間執(zhí)行各種語句，但是在進(jìn)行系統(tǒng)維護(hù)和數(shù)據(jù)維護(hù)時，可能需要改變表空間的狀態(tài)，一般情況下，由特權(quán)用戶或是dba來操作。

（1）使表空間脫機

Altertablespace表空間名offline;

（2）使表空間聯(lián)機

Altertablespace表空間名online;

（3）只讀表空間

當(dāng)建立表空間時，表空間可以讀寫，如果不希望在該表空間上執(zhí)行update，delete，insert操作，那么可以將表空間修改為只讀Altertablespace表空間名readonly

（4）知道表空間名，顯示該表空間包括的所有表

select*fromall_tableswheretablespace_name="data01";

（5）知道表明查看該表空間屬于哪個表空間（所查的用戶應(yīng)該存在表EMP）

selecttablespace_name,table_namefromuser_tableswheretable_name="EMP";

（6）改變表空間的狀態(tài)使表空間可以讀寫

Altertablespace表空間名readwrite;

（7）刪除表空間

一般情況下，由特權(quán)用戶或是dba來操作，如果是其他用戶操作，那么要求用戶具有droptablespace系統(tǒng)權(quán)限

Droptablespace‘表空間’includingcontentsanddatafiles;

成功源自勤奮

說明：includingcontents表示刪除表空間時，刪除該空間的所有數(shù)據(jù)庫對象，而datafiles表示將數(shù)據(jù)庫文件也刪除。

擴展表空間

表空間是由數(shù)據(jù)文件組成的，表空間的大小實際就是數(shù)據(jù)文件添加的大小，那么我們可以想象，假定employee存放到data01表空間上，初始大小就是2M，當(dāng)數(shù)據(jù)滿2M空間后，如果再向employee表插入數(shù)據(jù)，這樣就會顯示空組不足的錯誤。案例說明：

1．建立一個表空間data01

2．在該表空間上建立一個普通表mydept，其結(jié)構(gòu)和dept一樣

3．向該表空間中加入數(shù)據(jù)insertintomydeptselect*fromdept;4．當(dāng)一定時候就會出現(xiàn)無法擴展的問題，怎么辦？

5．就擴展該表空間，為其增加更多的存儲空間，有三種方法：

解決方法：

（1）增加數(shù)據(jù)文件

Sql->altertablespacedata01adddatafile‘d:\\data01.dbf’size20m

（2）增加數(shù)據(jù)文件的大小

Sql->altertablespacedata01datafile‘d:\\data01.dbf’resize20m這里需要注意的是數(shù)據(jù)文件的大小不要超過500m

（3）設(shè)置文件的自動增長

Sql->alterdatabasedatafile‘d:\\data01.dbf’autoextendonnext10mmaxsize500m;

移動數(shù)據(jù)文件

有時，如果你的數(shù)據(jù)文件所在的磁盤損壞時，該數(shù)據(jù)文件不能再使用，為了能夠重新使用，需要將這些文件的副本移動到其他的磁盤，然后恢復(fù)。下面以移動數(shù)據(jù)文件data01.dbf為例來說明1)確定數(shù)據(jù)文件所在的表空間

Selecttablespace_namefromdba_data_fileswherefile_name=’d:\\data01.dbf’2)使表空間脫機

確保數(shù)據(jù)文件的一致性，將表空間轉(zhuǎn)變?yōu)閛ffline狀態(tài)Altertablespacedata01offline;3)使用命令移動數(shù)據(jù)文件到指定的目標(biāo)位置

Sql->hostmoved:\\data01.dbfc:\\data01.dbf4)執(zhí)行altertablespace命令

在物理上移動了數(shù)據(jù)后，還必須執(zhí)行altertablespace命令對數(shù)據(jù)文件進(jìn)行邏輯修改；

Sql->altertablespacedata01renamedatafile‘d:\\data01.dbf’to‘c:\\data01.dbf’5)使表空間聯(lián)機

Altertablespacedata01online;

===============================================================================

約束

約束用于確保數(shù)據(jù)庫數(shù)據(jù)滿足特定的商業(yè)規(guī)則，在Oracle中，約束包括：notnull、uniqu、

成功源自勤奮

primarykey，foreignkey，和check五種例如:

CREATETABLEGOODS--商品信息表(

GOODIDCHAR(8)PRIMARYKEY,GOODNAMEVARCHAR2(30),

UNITPRICENUMBER(10,2)CHECK(UNITPRICE>0),CATEGORYVARCHAR2(8),PROVIDERVARCHAR2(50));

CREATETABLECUSTOMER--顧客信息表(

CUSTOMERIDCHAR(8)PRIMARYKEY,NAMEVARCHAR2(50)NOTNULL,ADDRESSVARCHAR2(50),

EMAILVARCHAR2(50)UNIQUE,

SEXCHAR(2)DEFAULT‘男’CHECK(SEXIN(‘男’,’女’)),CARDIDVARCHAR2(18));

CREATETABLEPURCHASE--購買信息表(

CUSTOMERIDCHAR(8)REFERENCESCUSTOMER(CUSTOMERID)，//外鍵指向另一個表的某個字段GOODIDCHAR(8)REFERENCESGOODS(GOODID),NUMNUMBER(5)CHECK(NUMBETWEEN1AND30));

如果建表時忘記了建立必要的約束，則可以在建表后使用altertable命令為表增加約束，但是要注意的是：增加NOTNULL約束時，需要使用modify選項，而增加其他四種約束使用add系選項。

例如：在以上表中增加商品名也不能為空

ALTERTABLEGOODSMODIFYGOODNAMENOTNULL;例如：增加身份證也不重復(fù)

ALTERTABLECUSTOMERADDCONSTRAINTHAHAUNIQUE(CARDID);

例如：添加客戶的住址只能是‘海淀’，‘朝陽’，‘東城’，‘西城’，‘通州’

ALTERTABLECUSTOMERADDCONSTRAINT名稱CHECK(ADDRESSIN("海淀"，"朝陽"，"東城"，"西城"，"通州"));刪除約束

ALTERTABLE表名DROPCONSTRAINT約束名稱；在刪除主鍵的時候，可能有錯誤，比如ALTERTABLE表名DROPPRIMARYKEY;這是因為如果兩張表存在主從關(guān)系，那么在刪除主表的主鍵約束時，必須帶上CASCADE選項如：ALTERTABLE表名DROPPRIMARYKEYCASCADE;顯示約束的信息1.顯示約束信息

通過要查詢的數(shù)據(jù)字典視圖USER_CONSTRAINTS,可以顯示當(dāng)前用戶所有的約束的信息。

成功源自勤奮

SELECTCONSTRAINT_NAME,CONSTRAINT_TYPE,STATUSFROMUSER_CONSTRAINTSWHERETABLE_NAME=’表名’；

2.顯示約束列

通過查詢數(shù)據(jù)字典視圖USER_CONS_COLUMNS,可以顯示約束所對應(yīng)的表信息列

SELECTCOLUMN_NAME,POSITIONFROMUSER_CONS_COLUMNSWHERECONSTRAINT_NAME=’約束名’表級定義

表級定義是指定義了所有列之后，再定義約束，這里需要注意：NOTNULL約束只能在列級定義上定義

以在EMPLOYEE表時定義主鍵約束和外鍵約束為例：

CREATETABLEEMPLOYEE(

EMP_IDNUMBER(8),NAMEVARCHAR2(18),DEPT_IDNUMBER(2),

CONSTRAINTPK_EMPLOYEEPRIMARYKEY(EMP_ID),CONSTRAINTFK_DEPARTMENTFOREIGNKEY(EMP_ID),REFERENCESDEPARTMENT(DEPT_ID));列級定義

列級定義是在定義列的同時定義約束如在DEPARTMENT表定義主鍵約束CREATETABLEDEPARTMENT(

DEPT_IDNUMBER(2)CONSTRAINTPK_DEPARTMENTPRIMARYKEY,NAMEVARCHAR2(12),LOCVARCHAR2(12));

管理索引

索引是用來加速數(shù)據(jù)存取的數(shù)據(jù)對象，合理地使用索引可以降低I/O次數(shù)，從而提高數(shù)據(jù)訪問性能，索引有很多種我們主要介紹常用的幾種：單列索引

單列索引是基于單個列所建立的索引，比如：CREATEINDEX索引名ON表名（列名）復(fù)合索引

復(fù)合索引是基于兩列或是多列的索引，在同一張表上可以有多個索引，但是要求列的組合必須不同，比如：

CREATEINDEXEMP_INDEXON表名（ENAME，JOB）；//先按ENAME查，再按JOB查CREATEINDEXEMP_INDEXON表名（JOB，ENAME）；//這兩個是有區(qū)別的SQL語句優(yōu)化

這就提示我們應(yīng)該盡可能地把那種很容易就把一些數(shù)據(jù)范圍減小的條件寫在后面：如：SELECT*FROMCUSTOMERWHERECATEGORY=’’ANDENAME=’’;

Sql語句掃描是從后往前掃描，如：ENAME一下子把范圍減小了，應(yīng)該寫GATEGORY后面，這樣篩選的速度快。索引的缺點

1.建立索引，系統(tǒng)要占用大約為表的1.2倍的硬盤和內(nèi)存來保存索引。

成功源自勤奮

2.更新數(shù)據(jù)的時候，系統(tǒng)必須要有額外的時間來同時對索引進(jìn)行更新，以維持?jǐn)?shù)據(jù)和索引

的一致性。

實踐表明，不恰當(dāng)?shù)乃饕�不但于事無補，反而降低系統(tǒng)性能。因為大量的索引在進(jìn)行插入、修改和刪除操作時比沒有索引花費的時間更多的系統(tǒng)時間。比如：在如下字段建立索引應(yīng)該不恰當(dāng)：1、很少或從不引用的字段；

2、邏輯型的字段，如男或女（是或否）等，綜上所述，提高查詢查詢效率是以消耗一定的

系統(tǒng)資源為代價的，索引不能盲目地建立，這是考驗一個DBA是否優(yōu)秀的很重要的指標(biāo)。顯示表的所有索引

在同一張表上可以有多個索引，通過查詢數(shù)據(jù)字典視圖dba_indexes和user_indexes,可以顯示索引信息，其中dba_indexs用于顯示數(shù)據(jù)庫所有的索引信息，而user_insexs用于顯示當(dāng)前用戶的索引信息；

SELECTINDEX_NAME,INDEX_TYPEFROMUSER_INDEXESWHERETABLE_NAME=’表名’；顯示索引列

通過查詢數(shù)據(jù)字段視圖user_ind_columns,可以顯示索引對應(yīng)的列的信息

SELECTTABLE_NAME,COLUMN_NAMEFROMUSER_IND_COLUMNSWHEREINDEX_NAME=’索引名’===============================================================================

管理權(quán)限

//查詢Oracle中所有的系統(tǒng)權(quán)限，一般是dba

SELECT*FROMSYSTEM_PRIVILEGE_MAPORDERBYNAME;//查詢Oracle中所有的角色，一般是dbaSELECT*FROMDBA_ROLES;

//查詢Oracle中所有對象權(quán)限，一般是dba

SELECTDISTINCTPRIVILEGEFROMDBA_TAB_PRIVS;//查詢數(shù)據(jù)庫的表空間

SELECTTABLESPACE_NAMEFROMDBA_TABLESPACES;//查詢某個用戶具有怎樣的角色

SELECT*FROMDBA_ROLE_PRIVSWHEREGRANTEE=’用戶名’；//查看某個角色包括哪些系統(tǒng)權(quán)限。

SELECT*FROMDBA_SYS_PRIVSWHEREGRANTEE=’DBA’;或者是:

SELECT*FROMROLE_SYS_PRIVSWHEREROLE=’DBA’;

權(quán)限

權(quán)限是指執(zhí)行特定類型sql命令或是訪問其他方案對象的權(quán)利。包括系統(tǒng)權(quán)限和對象權(quán)限兩種。

系統(tǒng)權(quán)限：是指執(zhí)行特定類型sql命令的權(quán)利。它用于控制用戶可以執(zhí)行的一個或一組數(shù)據(jù)庫操作。比如當(dāng)所有用戶具有createtable權(quán)限時，可以在其方案中建表，當(dāng)用戶具有createanytable權(quán)限時，可以在任何方案中建表Oracle提供了100多種系統(tǒng)權(quán)限。常用的有：

Createsession連接數(shù)據(jù)庫Createview建視圖

Createprocedure建過程、包Createcluster建簇Createtable建表

Createtrigger建觸發(fā)器顯示系統(tǒng)權(quán)限

Oracle提供了100多種權(quán)限，而且Oracle得版本越高，提供的系統(tǒng)權(quán)限就越多，我們可以查詢數(shù)據(jù)字典視圖system，privilege，map,可以顯示所有系統(tǒng)的權(quán)限SELECT*FROMSYSTEM_PRIVILEGE_MAPORDERBYNAME;

成功源自勤奮

GRANTCREATESESSION,CREATETABLETOXIAOMINGWITHADMINOPTION;系統(tǒng)權(quán)限不能級聯(lián)回收例如：

SQL->CONNSYS/TIGERASSYSDBA;//登陸系統(tǒng)用戶

SQL->CREATEUSERXIAOMINGIDENTIFIEDBYTIGER;//以系統(tǒng)用戶身份創(chuàng)建小明用戶SQL->CREATEUSERXIAOHONGIDENTIFIEDBYTIGER;//以系統(tǒng)用戶身份創(chuàng)建小紅用戶SQL->GRANTCONNECTTOXIAOMINGWITHADMINOPTION;//授予小明連接數(shù)據(jù)庫這個系統(tǒng)權(quán)

限，并授予他把權(quán)限傳下去

SQL->CONNXIAOMING/TIGER;//這時小明就可以連接到數(shù)據(jù)庫了

SQL->GRANTCONNECTTOXIAOHONG;//接著小明把連接數(shù)據(jù)庫的權(quán)限傳給小紅SQL->CONNSYS/TIGERASSYSDBA;//登陸系統(tǒng)用戶

SQL->REVOKECONNECTFROMXIAOMING;//SYS把小明連接數(shù)據(jù)庫的權(quán)限

SQL->CONNXIAOHONG/TIGER;//思考~這時小紅的權(quán)限有沒有被回收呢？答案：沒有被回收

授予對象權(quán)限

注意：WITHGRANTOPTION選項不能被授予角色。

1.MONKEY用戶要操作SCOTT.EMP表，則必須授予相應(yīng)的對象權(quán)限

（1）希望MONKEY可以查詢SCOTT.EMP的表數(shù)據(jù)，怎樣操作GRANTSELECTONEMPTOMONKEY

（2）希望MONKEY可以修改SCOTT.EMP的表數(shù)據(jù)，怎樣操作

GRANTUPDATEONEMPTOMONKEY

（3）希望MONKEY可以刪除SCOTT.EMP的表數(shù)據(jù)，怎樣操作

GRANTDELETEONEMPTOMONKEY

(4)有沒有更加簡單的方法，一次把所有權(quán)限賦給MONKEY?GRANTALLONEMPTOMONKEY2.能否對MONKEY訪問權(quán)限更加精細(xì)控制。（授予列權(quán)限）

（1）希望MONKEY只可以修改SCOTT.EMP的表的SAL字段，怎樣操作？

GRANTUPDATEONEMP(SAL)TOMONKEY

（2）希望MONKEY只可以查詢SCOTT.EMP的表的ENAME,SAL數(shù)據(jù)，怎樣操作？（3）GRANTSELECTONEMP(ENAME,SAL)TOMONKEY3.授予ALTER權(quán)限

如果BLACK用戶要修改SCOTT.EMP表的結(jié)構(gòu)，則必須授予ALTER對象權(quán)限SQL->CONNSCOTT/TIGER

SQL->GRANTALTERONEMPTOBLACK;當(dāng)然也可以用SYSTEM，SYS來完成這件事4.授予EXECUTE權(quán)限

如果用戶想要執(zhí)行其他方案的包/過程/函數(shù)。則須有EXECUTE權(quán)限。比如為了讓KEN可以執(zhí)行包DBNS_TRANSACTION,可以授EXECUTE權(quán)限SQL->CONNSYSTEM/TIGER;

SQL->GRANTEXECUTEONDBNS_TRANSACTIONTOKEN;5.授予INDEX權(quán)限

如果想在別的方案的表上建立索引，則必須具有INDEX對象權(quán)限，如為了讓BLACK可以在SCOTT.EMP上建立索引，就給其INDEX對象權(quán)限SQL->CONNSCOTT/TIGER;

SQL->GRANTINDEXONSCOTT.EMPTOBLACK;6.使用WITHGRANTOPTION選項

該選項用于轉(zhuǎn)授對象權(quán)限，但是該選項只能被授予用戶，而不能授予角色SQL->CONNSCOTT/TIGER;

SQL->GRANTSELECTONEMPTOBLACKWITHGRANTOPTIONSQL->CONNBLACK/TIGER

SQL->GRANTSELECTONSCOTT.EMPTOJONES

成功源自勤奮

回收對象權(quán)限

收回對象的權(quán)限可以由對象的所有者來完成，也可以用DBA用戶（SYS,SYSTEM）來完成這里要說明的是：收回對象權(quán)限后，用戶就不能執(zhí)行相應(yīng)的SQL命令，但是要注意的是對象的權(quán)限是否會被級聯(lián)收回呢？如：

SCOTT------------>BLACK------------->JONES

SELECTONEMPSELECTONEMPSELECTONEMPSQL->CONNSCOTT/TIGER@ORCL

SQL->REVOKESELECTONEMPFROMBLACK思考：JONES能否查詢SCOTT.EMP表數(shù)據(jù)

答案：這個區(qū)別系統(tǒng)權(quán)限，這個會被級聯(lián)回收的，也就是JONES不能查詢SCOTT.EMP的數(shù)據(jù)===============================================================================角色

角色就是相關(guān)權(quán)限的命令的集合，使用角色的主要目的就是簡化權(quán)限的管理（為了好管理，ORACLE事先把一系列的權(quán)限集中在一起打包賦予某些角色，在吧角色交給某個用戶）。假定有用戶A,B,C為了讓他們都擁有權(quán)限（1）連接數(shù)據(jù)庫

（2）在SCOTT.EMP表上SELECT,INSERT,UPDATE如果采用直接授權(quán)操作，則需要進(jìn)行12次授權(quán)現(xiàn)在我們采用角色來簡化該操作

首先將CREATESESSION,SELECTONSCOTT.EMP,INSERTONSCOTT.EMP,UPDATEONSCOTT.EMP授予角色，然后該角色授予A,B,C用戶，這樣就可以三次授權(quán)搞定了。角色分為預(yù)定義角色和自定義角色兩類：預(yù)定義角色

預(yù)定義角色是指ORACLE所提供的角色，每種角色都用于執(zhí)行一些特定的管理任務(wù)，下面我們介紹常用的預(yù)定義角色CONNECT,RESCOURCE,DBA(一)CONNECT角色

CONNECT角色具有一般應(yīng)用開發(fā)人員需要的大部分權(quán)限，當(dāng)建立了一個用戶后，多數(shù)情況下，只要給用戶授予CONNECT和RESOURCE角色就夠了，那么CONNECT角色具有哪些系統(tǒng)權(quán)限呢？

ALTERSESSION//修改會話CREATECLUSTER//創(chuàng)建簇

CREATEDATABASELINK//創(chuàng)建數(shù)據(jù)庫鏈接CREATESESSION//鏈接數(shù)據(jù)庫CREATETABLE//創(chuàng)建表CREATEVIEW//創(chuàng)建視圖CREATESEQUENCE//創(chuàng)建序列(二)RESOURCE角色

RESOURCE角色具有應(yīng)用開發(fā)人員所需要的其他權(quán)限，比如建立存儲過程，觸發(fā)器等，這里需要注意的是RESOURCE角色隱含了UNLIMITEDTABLESPACE系統(tǒng)權(quán)限。RESOURCE角色包含了以下系統(tǒng)權(quán)限：CREATECLUSTERCREATEINDEXTYPECREATETABLECREATESEQUENCECREATETYPE

CREATEPROCEDURECREATETRIGGER(三)DBA角色

DBA角色具有所有的系統(tǒng)權(quán)限，及WITHADMINOPTION選項，默認(rèn)的DBA用戶為SYS和SYSTEM他們可以將任何系統(tǒng)權(quán)限授予其他用戶，但是要注意的是DBA角色不具備

成功源自勤奮

SYSDBA和SYSOPER的特權(quán)（啟動和關(guān)閉數(shù)據(jù)庫）

自定義角色

根據(jù)自己的需要來定義，一般是DBA來建立，如果用的別的用戶來建立，則需要具有CREATEROLE的權(quán)限，在建立角色時可以指定驗證方式（不驗證，數(shù)據(jù)庫驗證等）（一）建立角色（不驗證）

如果角色時公用的角色，可以采用不驗證的方式建立角色CREATEROLE角色名NOTIDENTIFIED;

（二）建立角色（數(shù)據(jù)庫驗證）

采用這樣的方式時，角色名、口令名存放在數(shù)據(jù)庫中，當(dāng)激活該角色時，必須提供口令，在建立這種角色時，需要為其提供口令CREATEROLE角色名IDENTIFIEDBY密碼；角色授權(quán)

當(dāng)建立角色時，角色沒有任何權(quán)限，為了使得角色完成特定任務(wù)，必須為其授予相

應(yīng)的系統(tǒng)權(quán)限和對象權(quán)限。

（一）給角色授權(quán)

舉個例子：

SQL->CONN/ASSYSDBA;

SQL->CREATEROLEMTROLENOTIDENTIFIED;

SQL->GRANTCREATESESSIONTOMYROLEWITHADMINOPTION;

SQL->CONNSCOTT/TIGER;

SQL->GRANTSELECTONSCOTT.EMPTOMYROLE;

SQL->GRANTINSERT,UPDATE,DELETEONSCOTT.EMPTOMYROLE

通過上面的步驟，就給角色授權(quán)了。接著把角色分配給用戶。(具有DBA權(quán)限才可以)

SQL->CONN/ASSYSDBA;

SQL->CREATEUSERXIAOMINGIDENTIFIEDBYTIGER;

SQL->GRANTMYROLETOXIAOMINNG;//此時XIAOMING就擁有MYROLE中的權(quán)限具體操作如下圖：

成功源自勤奮

（二）刪除角色

使用DROPROLE,一般是DBA來執(zhí)行，如果其他用戶要求該用戶具有DROPANYROLE權(quán)限

SQL->CONN/ASSYSDBA;SQL->DROPROLE角色名；

這時刪除角色，XIAOMING也就不具有MYROLE中的權(quán)限（級聯(lián)）。

（三）顯示角色的信息

1顯示所有角色○

SQL->SELECT*FROMDBA_ROLES;//DBA才可以查詢2顯示角色具有的系統(tǒng)權(quán)限○

SQL->SELECTPRIVILEGE,ADMIN_OPTIONFROMROLE_SYS_PRIVSWHEREROLE=’角色名’;

3顯示角色具有的對象權(quán)限○

通過查詢數(shù)據(jù)字典視圖DBA_TAB_PRIVS可以查看角色具有的對象權(quán)限或是列的權(quán)限。

4顯示用戶具有的角色，及默認(rèn)角色○

當(dāng)以用戶的身份連接到數(shù)據(jù)庫時，ORACLE會自動地激活默認(rèn)的角色，通過查詢數(shù)據(jù)字典視圖DBA_ROLE_PRIVS可以顯示某個用戶具有的所有角色及當(dāng)前默認(rèn)的角色

SQL->SELECTGRANTED_ROLE,DEFAULT_ROLEFROMDBA_ROLE_PRIVSWHEREGRANTEE=’用戶名’；

成功源自勤奮

（四）精細(xì)訪問控制

是指用戶可以使用函數(shù)、策略實現(xiàn)更加細(xì)微的安全訪問控制，如果使用精細(xì)訪問控制。則當(dāng)在客戶端發(fā)出SQL語句（SELECT,INSERT,UPDATE,DELETE）時，ORACLE會自動在SQL語句后追加謂詞（WHERE子句），并執(zhí)行新的SQL語句，通過這樣的控制，可以使得不同的數(shù)據(jù)庫用戶在訪問相同表時，返回不同的數(shù)據(jù)信息，如：

用戶：SCOTTBLAKEJONES策略：EMP.ACCESS數(shù)據(jù)庫表EMP

上面所示：通過策略EMP.ACCESS,用戶SCOTT,BLAKE,JONES在執(zhí)行相同的SQL語句時，可以返回不同的結(jié)果。例如，當(dāng)執(zhí)行SELECTENAMEFROMEMP;會根據(jù)實際情況返回不同的結(jié)果。

===============================================================================PL/SQL編程

PL/SQL（PROCEDURALLANGUAGE/SQL）是ORACLE在標(biāo)準(zhǔn)SQL語言上的擴展，PL/SQL不僅允許嵌入SQL語言，還可以定義變量和常量，允許使用條件語句和循環(huán)語句，允許使用例外處理各種錯誤，這樣使得它的功能更加強大。（缺點：PL/SQL移植性不好）SQLPLUS開發(fā)工具

SQLPLUS是ORACLE公司提供的一個工具.舉一個簡單的案例：

編寫一個存儲過程，該過程可以向某表中添加記錄解答：1.創(chuàng)建一個簡單的表

SQL->CREATETABLEMYTEXT(NAMEVARCHAR2(30),PASSWSVARCHAR2(30));2.創(chuàng)建過程

SQL->CREATEORREPLACEPROCEDURESP_PRO1IS

注釋：或如果存在SP_PRO1，就可以REPLACEPROCEDURESQL->BEGIN

SQL->INSERTINTOMYTEXTVALUES(‘WANGP’,’TIGER’);SQL->END;

SQL->/注釋：表示讓ORACLE去創(chuàng)建這樣的一個存儲過程

如何查看錯誤信息？答案：showerror如何調(diào)用該過程？○1EXEC過程名（參數(shù)值1，參數(shù)值2...）;

2CALL過程名（參數(shù)值1，參數(shù)值2...）;○

PL/SQLDEVELOPER開發(fā)工具

PL/SQLDEVELOPER是用于開發(fā)PL/SQL塊的集成開發(fā)環(huán)境（IDE），他是一個獨立的產(chǎn)品，而不是ORACLE的一個附帶品。舉一個簡單案例：

成功源自勤奮

編寫一個存儲過程，該過程可以刪除某表記錄

把里面內(nèi)容復(fù)制到對話框中

PL/SQL編程的基礎(chǔ)單位是塊（編程），通過塊的概念可以編寫過程（存儲過程）、函數(shù)、觸發(fā)器、包。編寫規(guī)范：1注釋：○

單行注釋

SELECT*FROMEMPWHEREEMPNO=7788取得員工信息多行注釋

/*......*/來劃分2標(biāo)識符號的命名規(guī)范○

1)當(dāng)定義變量時，建立用V_作為前綴比如;V_SAL2)當(dāng)定義常量時，建議用C_作為前綴C_RATE

3)當(dāng)定義游標(biāo)時，建議用_CURSOR作為后綴EMP_CURSOR4)當(dāng)定義例外時，建議用E_作為前綴E_ERROR塊的介紹

塊（BLOCK）是PL/SQL的基本程序單元，編寫PL/SQL程序?qū)嶋H上就是編寫PL/SQL塊。要完成相對簡單的應(yīng)用功能，可能只需要編寫一個PL/SQL塊，但是如果要想實現(xiàn)復(fù)雜的功能，可能需要在一個PL/SQL塊中嵌套其他的PL/SQL

PL/SQL塊由三個部分構(gòu)成：定義部分、執(zhí)行部分、例外處理部分。如下所示：

DECLARE

/*定義部分------定義常量、變量、游標(biāo)、例外、復(fù)雜數(shù)據(jù)類型*/BEGIN

/*執(zhí)行部分------要執(zhí)行的PL/SQL語句和SQL語句*/EXCEPTION

/*例外處理部分-----處理運行的各種錯誤*/END;

實例1-只包括執(zhí)行部分的PL/SQL塊SETSERVEROUTPUTON打開輸出選項BEGIN

DBMS_OUTPUT.PUT_LINE(‘LINE’);END;

？相關(guān)說明：

DBMS_OUTPUT是ORACLE所提供的包（類似JAVA的開發(fā)包），該包包含一些過程，PUT_LINE就是DBMS_OUTPUT包的一個過程。

成功源自勤奮

實例2-包含定義部分和執(zhí)行部分的PL/SQL塊

DECLARE

V_ENAMEVARCHAR2(5);--定義字符串變量BEGIN

SELECTENAMEINTOV_ENAMEFROMEMPWHEREEMPNO=&NO;DBMS_OUTPUT.PUT_LINE("雇員名："||V_ENAME);END;/

相關(guān)說明：

&表示要接受從控制臺輸入的變量注意：每條語句后面得加分號（；）

DECLARE

V_ENAMEVARCHAR2(5);--定義字符串變量V_SALNUMBER(7,2);--顯示用戶的工資BEGIN

SELECTENAME，SALINTOV_ENAME,V_SALFROMEMPWHEREEMPNO=&no1;DBMS_OUTPUT.PUT_LINE("雇員名："||V_ENAME||"薪水為："||V_SAL);END;/

成功源自勤奮

實例3-包含定義部分、執(zhí)行部分和例外部分

為了避免PL/SQL程序的運行錯誤，提高PL/SQL的健壯性，應(yīng)該對可能的錯誤進(jìn)行處理，這個很有必要：

1比如在實例2中，如果輸入了不存在的雇員號，應(yīng)當(dāng)做例外處理。○

2有時出現(xiàn)異常，希望用另外的邏輯處理。○

相關(guān)說明：ORACLE事先預(yù)定義了一些例外，NO_DATA_FOUND就是找不到數(shù)據(jù)的例外。

DECLARE

V_ENAMEVARCHAR2(5);--定義字符串變量V_SALNUMBER(7,2);--顯示用戶的工資BEGIN

SELECTENAME，SALINTOV_ENAME,V_SALFROMEMPWHEREEMPNO=&no1;DBMS_OUTPUT.PUT_LINE("雇員名："||V_ENAME||"薪水為："||V_SAL);--異常處理EXCEPTION

WHENNO_DATA_FOUNDTHEN

DBMS_OUTPUT.PUT_LINE("您輸入得編號有誤！");END;/

過程：

過程用于執(zhí)行特定的操作，當(dāng)建立過程時，既可以指定輸入?yún)��?shù)（IN），也可以指定輸出參數(shù)（OUT），通過在過程中使用輸入?yún)��?shù)，可以將數(shù)據(jù)傳遞到執(zhí)行部分；通過使用輸出參數(shù)，

成功源自勤奮

可以將執(zhí)行部分的數(shù)據(jù)傳遞到應(yīng)用環(huán)境，在SQLPLUS中可以使用CREATEPROCEDURE命令來建立過程。

實例如下：

1請考慮編寫一個過程,可以輸入雇員名，薪工資，可修改雇員的工資○

2如何調(diào)用過程有兩種方法：EXEC,CALL○

實例4-

CREATEPROCEDURESP_PRO3(SPNAMEVARCHAR2,NEWSALNUMBER)IS

--DECLARE

BEGIN

--執(zhí)行部分，根據(jù)用戶名去修改工資

UPDATEEMPSETSAL=NEWSALWHEREENAME=SPNAME;END;

//執(zhí)行過程用EXEC或CALL

?如何使用過程返回值函數(shù)

函數(shù)用于返回特定的數(shù)據(jù)，當(dāng)建立函數(shù)時，在函數(shù)頭部必須包含RETURN子句，而在函數(shù)體內(nèi)必須包含RETURN語句返回的數(shù)據(jù)，我們可以使用CREATEFUNCTION來建立函數(shù)，實際案例：

--函數(shù)案例

--輸入雇員的姓名返回該雇員的年薪

CREATEFUNCTIONSP_FUN2(SPNAMEVARCHAR2)RETURNNUMBERISYEARSALNUMBER(7,2);--函數(shù)只能返回一個值

BEGIN--執(zhí)行部分

SELECTSAL*12+NVL(COMM,0)*12INTOYEARSALFROMEMPWHEREENAME=SPNAME;RETURNYEARSAL;END;

成功源自勤奮

在PL/SQL中調(diào)用函數(shù)（左）-----------------在SQLPLUS中調(diào)用函數(shù)（右）

---------------------------------------

//注意：在PL/SQL中INTO后面的冒號（：）后面不能

有空格，但是SQLPLUS可以允許加空格。

包

包用于邏輯上組合過程和函數(shù)，它由包規(guī)范和包體兩部分組成。1我們可以使用CREATEPACKAGE命令來創(chuàng)建包：○

實例：

--創(chuàng)建一個包：SP_PACKAGE--聲明了該包有一個過程--聲明了該包有一個函數(shù)

CREATEPACKAGESP_PACKAGEIS

PROCEDUREUPDATE_SAL(NAMEVARCHAR2,NEWSALNUMBER);FUNCTIONSP_FUN(NAMEVARCHAR2)RETURNNUMBER;END;

包的規(guī)范只包含了過程和函數(shù)的說明，但是沒有過程和函數(shù)的實現(xiàn)代碼。包體用于實現(xiàn)包規(guī)范中的過程和函數(shù)，

2建立包體可以使用CREATEPACKAGEBODY命令○

--創(chuàng)建包體

CREATEORREPLACEPACKAGEBODYSP_PACKAGEIS

PROCEDUREUPDATE_SAL(NAMEVARCHAR2,NEWSALNUMBER)ISBEGIN

UPDATEEMPSETSAL=NEWSALWHEREENAME=NAME;END;

FUNCTIONSP_FUN(NAMEVARCHAR2)RETURNNUMBERISANNUAL_SALARYNUMBER;BEGIN

成功源自勤奮

SELECTSAL*12+NVL(COMM,0)INTOANNUAL_SALARYFROMEMPWHEREENAME=NAME;RETURNANNUAL_SALARY;END;END;

注意：包里面定義幾個函數(shù),幾個過程，包體里面也要想對應(yīng)地去聲明函數(shù)例如：現(xiàn)在包里創(chuàng)建了一個過程（UPDATE_SAL）和一個函數(shù)（SP_FUN）當(dāng)包體中少聲明了函數(shù)（SP_FUN），就會報錯。具體操作如下圖：

如果該包刪掉會出現(xiàn)怎樣的錯誤呢？

如何調(diào)用包的過程或是函數(shù)當(dāng)帶調(diào)用包的過程或是函數(shù)時，在過程和函數(shù)前需要帶有包名，如果需要訪問其他方案的包，還需要在包名前加方案名。

如：SQL->CALLSP_PACKAGE.UPDATE.SAL(‘SCOTT’,1500);特別說明：

成功源自勤奮

包是PL/SQL中非常重要的部分，我們在使用過程分頁時，將會再次體驗它的威力。

===============================================================================問題有待解決

視頻上講call和exec都可以用來調(diào)用，但是經(jīng)過仔細(xì)操作，看下面截圖：

調(diào)用包的過程（左）-----------------------------------直接調(diào)用過程（右）

注釋：這里對于過程來說，CALL和EXEC都是可行的，但是對于函數(shù)來說，請看截圖調(diào)用包的函數(shù)（左）-----------------------------------直接調(diào)用函數(shù)（右）

注釋：可以看出來，函數(shù)只用CALL來調(diào)用是可行的，一旦用EXEC的話便會報錯。（這點我也不懂ing

）

===============================================================================

成功源自勤奮

觸發(fā)器

觸發(fā)器是指隱含的執(zhí)行的存儲過程，當(dāng)定義觸發(fā)器時，必須要指定觸發(fā)的事件和觸發(fā)的操作，常用的觸發(fā)事件包括INSERT、UPDATE、DELETE語句，而觸發(fā)操作實際就是一個PL/SQL塊，可以使用CREATETRIGGER來建立觸發(fā)器。特別說明：

觸發(fā)器是非常有用的，可維護(hù)數(shù)據(jù)庫的安全和一致性。定義并使用變量

在編寫PL/SQL程序時，可以定義變量和常量；在PL/SQL程序中包括：1標(biāo)量類型（SCALAR）○

定義標(biāo)量的案例

1定義一個變長字符串○

V_ENAMEVARCHAR2(10)

2定義一個小數(shù)，范圍-9999.99~9999.99○

V_SALNUMBER(6,2);

3定義一個小數(shù)并給一個初始值為5.4：=是PL/SQL的賦值號○

V_SAL2NUMBER(6,2):=5.4

4定義一個日期類型的數(shù)據(jù)○

V_HIREDATEDATE;

5定義一個布爾變量，不能為空，初始值為FALSE○

V_VALIDBOOLEANNOTNULLDEFAULTFALSE;標(biāo)量的使用

在定義好變量后，就可以使用這些變量。這里需要說明的是PL/SQL塊為變量賦值不

同于其他的編程語言，需要再等號前加冒號（：=）

例子：輸入員工工號，顯示雇員姓名、工資、個人所得稅（稅率為0.03）

DECLARE

C_TAX_RATENUMBER(3,2):=0.03;

--用戶名

V_ENAMEVARCHAR2(5);V_SALNUMBER(7,2);V_TAX_SALNUMBER(7,2);BEGIN--執(zhí)行

SELECTENAME,SALINTOV_ENAME,V_SALFROMEMPWHEREEMPNO=&NO;--計算所得稅

V_TAX_SAL:=V_SAL*C_TAX_RATE;--輸出

DBMS_OUTPUT.PUT_LINE("姓名是："||V_ENAME||"他的工資"||V_SAL||"所得稅"||V_TAX_SAL);END;

對上面的PL/SQL塊有一個問題：

就是如果員工的姓名超過了5字符的話，就會有錯誤，為了降低PL/SQL程序的維護(hù)工作

成功源自勤奮

量，可以使用%TYPE屬性定義變量，這樣它會按照數(shù)據(jù)庫列來確定你定義的變量的類型和長度。

標(biāo)識符名表名.列名%TYPE

DECLARE

C_TAX_RATENUMBER(3,2):=0.03;

--用戶名

V_ENAMEEMP.ENAME%TYPE;--V_ENAME的類型和EMP表中的ENAM字段的類型一致V_SALNUMBER(7,2);--V_SALEMP.SAL%TYPEV_TAX_SALNUMBER(7,2);BEGIN--執(zhí)行

SELECTENAME,SALINTOV_ENAME,V_SALFROMEMPWHEREEMPNO=&NO;--計算所得稅

V_TAX_SAL:=V_SAL*C_TAX_RATE;--輸出

DBMS_OUTPUT.PUT_LINE("姓名是："||V_ENAME||"他的工資"||V_SAL||"所得稅"||V_TAX_SAL);END;

提倡：最好把所有定義的類型都該成和表字段類型一致2復(fù)合類型（COMPOSITE）○

用于存放多個值的變量，主要包括這幾種：

1PL/SQL記錄○

類似于高級語言的結(jié)構(gòu)體，需要注意的是，當(dāng)引用PL/SQL記錄成員時，必須要加

記錄變量作為前綴（記錄變量，記錄成員）如下：2PL/SQL表○

3嵌套表○

4VARRAY○

--PL/SQL記錄實例

DECLARE

--定義一個PL/SQL記錄類型EMP_RECORD_TYPE,類型包含三個數(shù)據(jù)：NAME、SALARY、TITLE

TYPEEMP_RECORD_TYPEISRECORD(NAMEEMP.ENAME%TYPE,SALARYEMP.SAL%TYPE,TITLEEMP.JOB%TYPE);

--定義了一個變量SP_RECORD,這個變量的類型EMP_RECORD_TYPE

SP_RECORDEMP_RECORD_TYPE;BEGIN

SELECTENAME,SAL,JOBINTOSP_RECORDFROMEMPWHEREEMPNO=7788;

DBMS_OUTPUT.PUT_LINE("員工名："||SP_RECORD.NAME||"工資是"||SP_RECORD.SALARY||"工作崗位是："||SP_RECORD.TITLE);END;

復(fù)合類型-PL/SQL表

相當(dāng)于高級語言中的數(shù)組，但是需要注意的是在高級語言中數(shù)組的下標(biāo)不能為負(fù)數(shù)，而PL/SQL是可以為負(fù)數(shù)的，并且表元素的下標(biāo)沒有限制，實例如下：

--PL/SQL表實例

DECLARE

--定義了一個PL/SQL表類型：SP_TABLE_TYPE該類型是用于存放EMP.ENAME%TYPE

成功源自勤奮

--INDEXBYBINARY_INTEGER;表示下標(biāo)是整數(shù)

TYPESP_TABLE_TYPEISTABLEOFEMP.ENAME%TYPEINDEXBYBINARY_INTEGER;

--定義了一個SP_TABLE變量，這個變量的類型是SP_TABLE_TYPE:

SP_TABLESP_TABLE_TYPE;BEGIN

SELECTENAMEINTOSP_TABLE(0)FROMEMPWHEREEMPNO=7788;DBMS_OUTPUT.PUT_LINE("員工名："||SP_TABLE(0));END;

注意：如果把WHERE去掉會怎樣呢？

解答：因為SP_TABLE(0)是指定返回一條數(shù)據(jù)，可是把WHERE去掉，就返回多條數(shù)據(jù)。3參照類型（REFERENCE）○

參照變量是指用于存放數(shù)值指針的變量，通過使用參照變量，可以使得應(yīng)用程序共享相同對象，從而降低占用的空間，在編寫PL/SQL程序時，可以使用游標(biāo)變量（REFCURSOR）和對象類型變量（REFOBJ_TYPE）兩種參照變量類型。參照變量-REFCURSOR游標(biāo)變量

使用游標(biāo)時，當(dāng)定義游標(biāo)時不需要指定相應(yīng)地SELECT語句，但是當(dāng)使用游標(biāo)時（OPEN時）需要指定SELECT語句，這樣一個游標(biāo)就與一個SELECT語句結(jié)合了。實例如下：

1請使用PL/SQL編寫一個塊，○可以輸入部門號，并顯示該部門所有員工姓名和他的工資。

DECLARE

--定義游標(biāo)

TYPESP_EMP_CURSORISREFCURSOR;

--定義一個游標(biāo)變量

TEST_CURSORSP_EMP_CURSOR;

--定義接收姓名,和工資的變量

V_ENAMEEMP.ENAME%TYPE;V_SALEMP.SAL%TYPE;BEGIN--執(zhí)行

--把TEST_CURSOR和一個SELECT結(jié)合

OPENTEST_CURSORFORSELECTENAME,SALFROMEMPWHEREDEPTNO=&NO;--把這個游標(biāo)指向一個結(jié)果集--循環(huán)取出LOOP

--FETCH就是取出的意思

FETCHTEST_CURSORINTOV_ENAME,V_SAL;--判斷是否TEST_CURSOR是否為空，然后退出循環(huán)EXITWHENTEST_CURSOR%NOTFOUND;

DBMS_OUTPUT.PUT_LINE("名字："||V_ENAME||"工資："||V_SAL);ENDLOOP;END;

成功源自勤奮

2在○1基礎(chǔ)上，如果某個員工的工資低于201*元，就增加100元�！�

DECLARE

--定義游標(biāo)

TYPESP_EMP_CURSORISREFCURSOR;

--定義一個游標(biāo)變量

TEST_CURSORSP_EMP_CURSOR;

--定義接收姓名,和工資的變量

V_ENAMEEMP.ENAME%TYPE;V_SALEMP.SAL%TYPE;BEGIN--執(zhí)行

--把TEST_CURSOR和一個SELECT結(jié)合

OPENTEST_CURSORFORSELECTENAME,SALFROMEMPWHEREDEPTNO=&NO;--把這個游標(biāo)指向一個結(jié)果集--循環(huán)取出LOOP

FETCHTEST_CURSORINTOV_ENAME,V_SAL;--判斷是否TEST_CURSOR是否為空EXITWHENTEST_CURSOR%NOTFOUND;IFV_SAL

成功源自勤奮

PL/SQL中的控制結(jié)構(gòu)

小練習(xí)

編寫一個過程，可以輸入一個雇員名，如果該雇員的工資低于201*，就給該雇員工資增加100CREATEORREPLACEPROCEDURESP_PRO3(SPNAMEVARCHAR2)IS

--定義

V_SALEMP.SAL%TYPE;BEGIN

SELECTSALINTOV_SALFROMEMPWHEREENAME=SPNAME;IFV_SAL

成功源自勤奮

多重條件分支IF---THEN---ELSIF---ELSE

?編寫一個過程，可以輸入一個雇員名，如果該雇員的職位是PRESIDENT就給他的工資增加1000，如果該雇員的職位是MANAGER就給他的工資增加500，其他職位的雇員工資增加200CREATEORREPLACEPROCEDURESP_PRO1(SPNAMEVARCHAR2)ISV_JOBEMP.JOB%TYPE;BEGIN

SELECTJOBINTOV_JOBFROMEMPWHEREENAME=SPNAME;IFV_JOB="PRESIDENT"THEN

UPDATEEMPSETSAL=SAL+1000WHEREENAME=SPNAME;ELSIFV_JOB="MANAGER"THEN

UPDATEEMPSETSAL=SAL+500WHEREENAME=SPNAME;ELSE

UPDATEEMPSETSAL=SAL+200WHEREENAME=SPNAME;ENDIF;END;

**注意：這里是ELSIF，而不是ELSEIF

循環(huán)語句LOOP是PL/SQL中最簡單的循環(huán)語句，這種循環(huán)語句以LOOP開頭，以ENDLOOP結(jié)尾，這種循環(huán)至少會被執(zhí)行一次。案例：現(xiàn)有一張表USERS，表結(jié)構(gòu)如下用戶ID用戶名請編寫一個過程，可輸入用戶名，并循環(huán)添加10個用戶到USERS表中、用戶編號從1開始增加。CREATETABLEUSERS(IDNUMBER(2),NAMEVARCHAR2(67));CREATEORREPLACEPROCEDURESP_PRO2(SPNAMEVARCHAR2)ISV_IDNUMBER(2):=1;BEGINLOOPINSERTINTOUSERSVALUES(V_ID,SPNAME);V_ID:=V_ID+1;EXITWHENV_ID>10;ENDLOOP;END;循環(huán)語句WHILE對于WHILE來說，只要有條件為TRUE時，才會執(zhí)行循環(huán)語句，WHILE循環(huán)以WHILE..LOOP開始，以ENDLOOP結(jié)束請編寫一個過程，可輸入用戶名，并循環(huán)添加10個用戶到USERS表中、用戶編號從11開始增加。案例：現(xiàn)有一張表USERS，表結(jié)構(gòu)同上表；

CREATEORREPLACEPROCEDURESP_PRO2(SPNAMEVARCHAR2)ISV_IDNUMBER:=11;BEGIN

WHILEV_ID○2NULL

成功源自勤奮

NULL語句不會執(zhí)行任何操作，并且會直接將控制傳遞到下一條語句，使用NULL語句的主要好處是可以提高PL/SQL的可讀性。案例：DECLARE

V_SALEMP.SAL%TYPE;V_ENAMEEMP.ENAME%TYPE;BEGIN

SELECTENAME,SALINTOV_ENAME,V_SALFROMEMPWHEREEMPNO=&NO;IFV_SAL

成功源自勤奮

--創(chuàng)建一個包，在該包中，定義類型TEST_CURSOR,是個游標(biāo)

CREATEORREPLACEPACKAGETESTPACKAGEASTYPETEST_CURSORISREFCURSOR;ENDTESTPACKAGE;

2創(chuàng)建一個過程，并調(diào)用包○

CREATEORREPLACEPROCEDURESP_PRO9(SPNOINNUMBER,P_CURSOROUTTESTPACKAGE.TEST_CURSOR)ISBEGIN

OPENP_CURSORFORSELECT*FROMEMPWHEREDEPTNO=SPNO;END;注意：//該程序是在JAVA中調(diào)用

編寫分頁過程

SELECT*FROM(SELECTA.*,ROWNUMNFROM(SELECT*FROMEMP)AWHEREROWNUM=6;

請編寫一個存儲過程，要求可以輸入表名、每頁顯示記錄數(shù)，當(dāng)前頁，返回總記錄數(shù)，總頁數(shù)和返回的結(jié)果集。

--創(chuàng)建一個包，在該包中，定義類型TEST_CURSOR,是個游標(biāo)

CREATEORREPLACEPACKAGETESTPACKAGEASTYPETEST_CURSORISREFCURSOR;ENDTESTPACKAGE;

--開始編寫分頁的過程

CREATEORREPLACEPROCEDURESP_FENYE(TABLENAMEINVARCHAR2,PAGESIZESINNUMBER,PAGENOWINNUMBER,

MYROWSOUTNUMBER,--返回總記錄數(shù)MYPAGECOUNTOUTNUMBER,--返回總頁數(shù)P_CURSOROUTTESTPACKAGE.TEST_CURSOR)IS

--定義部分

--定義SQL語句字符串

V_SQLVARCHAR2(1000);

--定義兩個整數(shù)

V_BEGINNUMBER:=(PAGENOW-1)*PAGESIZES+1;V_ENDNUMBER:=PAGENOW*PAGESIZES;BEGIN

V_SQL:="SELECT*FROM(SELECTA.*,ROWNUMNFROM(SELECT*FROM"||TABLENAME||")AWHEREROWNUM="||V_BEGIN;--把游標(biāo)和SQL語句關(guān)聯(lián)起來】OPENP_CURSORFORV_SQL;--要計算MYROWS和MYPAGECOUNT--組織一個SQL語句

V_SQL:="SELECTCOUNT(*)FROM"||TABLENAME;--執(zhí)行SQL，并把返回的值，賦給MYROWSEXECUTEIMMEDIATEV_SQLINTOMYROWS;--計算MYPAGECOUNT

IFMOD(MYROWS,PAGESIZES)=0THENMYPAGECOUNT:=MYROWS/PAGESIZES;ELSE

MYPAGECOUNT:=MYROWS/PAGESIZES+1;ENDIF;--關(guān)閉游標(biāo)--CLOSEP_CURSOR;

END;注意：該過程時在JAVA中執(zhí)行

成功源自勤奮

===============================================================================Java代碼：

Publicstaticvoidmain(string[]rags){Try{

class.forName("Oracle.jdbc.driver.OracleDriver");Connectionct=DriverManager.GetConnnection

("jdbc:oracle:thin:@192.168.186:1158:MYORACLE","SCOTT","TIGER");CallableStatementcs=ct.prepareCall("callSP_FENTE(?,?,?,?,?,?)")//給？賦值

cs.setString(1,"EMP");cs.setString(2,5);cs.setInt(3,1);//注冊總記錄數(shù)

cs.registerOutparameter(4,oracle.jdbc.oracleTypes.INTEGER);//注冊總列數(shù)

cs.registerOutparameter(5,oracle.jdbc.oracleTypes.INTEGER);//注冊返回的結(jié)果集

cs.registerOutparameter(6,oracle,jdbc.oracleTypes.CURSOR);cs.execute();

//取出總記錄數(shù)/這里要注意，getInt(4)中4，是由該參數(shù)的位置決定的IntMYROWNUM=cs.getInt(4);intMYPAGECOUNT=cs.getInt(5);

Resultsetrs=(ResultSet)cs.getobject(6);//顯示一下，看看是否對不對

System.out.println("MYROWNUM="+MYROWNUM);System.out.println("總頁數(shù)："+MYPAGECOUNT);

while(rs.next()){Sys.out.println("編號："+rs.getint(1)+"名字："+getstring(2))};}catch(exceptione){e.printStackTrace();}}

============================================================================================

PL/SQL的例外處理

案例：編寫一個過程，可接收雇員的編號，并顯示該雇員的姓名。問題是：如果輸入的雇員編號不存在，怎樣去處理呢？

DECLARE

V_NAMEEMP.ENAME%TYPE;BEGIN

SELECTENAMEINTOV_NAMEFROMEMPWHEREEMPNO=&NO;DBMS_OUTPUT.PUT_LINE("姓名："||V_NAME);EXCEPTION

WHENNO_DATA_FOUNDTHEN

DBMS_OUTPUT.PUT_LINE("編號沒有！");END;

處理預(yù)定義例外

預(yù)定義例外時由PL/SQL所提供的系統(tǒng)例外，當(dāng)PL/SQL應(yīng)用程序違反了Oracle規(guī)定的限制時，則會隱含地觸發(fā)一個內(nèi)部例外。

成功源自勤奮

PL/SQL為開發(fā)人員提供了二十多個預(yù)定義例外，下面來介紹常用的例外。（一）預(yù)定義例外CASE_NOT_FOUND

在開發(fā)PL/SQL塊中編寫CASE語句時，如果WHERE子句中沒有包含必須的條件分支，就會觸發(fā)CASE_NOT_FOUND的例外

CREATEORREPLACEPROCEDURESP_PRO9(SPNONUMBER)ISV_SALEMP.SAL%TYPE;BEGIN

SELECTSALINTOV_SALFROMEMPWHEREEMPNO=SPNO;CASE

WHENV_SAL

成功源自勤奮

（三）預(yù)定義例外DUP_VAL_ON_INDEX

在唯一索引所對應(yīng)的列上插入重復(fù)的值時，會隱含地觸發(fā)例外

--DUP_VAL_ON_INDEX

BEGIN

INSERTINTODEPTVALUES(10,"公關(guān)部","北京"）;EXCEPTIONWHEN

DUP_VAL_ON_INDEXTHEN

DBMS_OUTPUT.PUT_LINE("在DEPTNO列上不能出現(xiàn)重復(fù)值");

END;

（四）預(yù)定義例外INVALID_CURSOR

當(dāng)視圖在不合法的游標(biāo)上執(zhí)行操作時，會觸發(fā)該例外

例如：視圖從沒有打開的游標(biāo)提取數(shù)據(jù)，或是關(guān)閉沒有打開的游標(biāo)，則會觸發(fā)該例外

DECLARE

CURSOREMP_CURSORISSELECTENAME,SALFROMEMP;EMP_RECORDEMP_CURSOR%ROWTYPE;BEGIN

--OPENEMP_CURSOR;打開游標(biāo)FETCHEMP_CURSORINTOEMP_RECORD;DBMS_OUTPUT.PUT_LINE(EMP_RECORD.ENAME);CLOSEEMP_CURSOR;EXCEPTION

WHENINVALID_CURSORTHEN

DBMS_OUTPUT.PUT_LINE("請檢測游標(biāo)是否打開");

END;

成功源自勤奮

（五）預(yù)定義例外INVALID_NUMBER

當(dāng)輸入的數(shù)據(jù)有誤時，會觸發(fā)該例外比如：數(shù)字100寫成1oo就會觸發(fā)該例外

BEGIN

UPDATEEMPSETSAL=SAL+"1oo";EXCEPTION

WHENINVALID_NUMBERTHEN

DBMS_OUTPUT.PUT_LINE("輸入的數(shù)字不正確");

END;

（六）預(yù)定義例外NO_DATA_FOUND

下面是一個PL/SQL塊，當(dāng)執(zhí)行SELECTINTO沒有返回行，就會觸發(fā)該例外

DECLARE

V_SALEMP.SAL%TYPE;BEGIN

SELECTSALINTOV_SALFROMEMP--WHEREENAME="&NAME";WHEREENAME="WANGWP";EXCEPTION

WHENNO_DATA_FOUNDTHEN

DBMS_OUTPUT.PUT_LINE("不存在該員工");

END;

（七）預(yù)定義例外TOO_MANY_ROWS

當(dāng)執(zhí)行SELECTINTO語句時，如果返回超過了一行，則會觸發(fā)該例外。

DECLARE

V_ENAMEEMP.ENAME%TYPE;BEGIN

SELECTENAMEINTOV_ENAMEFROMEMP;EXCEPTION

友情提示：本文中關(guān)于《oracle學(xué)習(xí)心得》給出的范例僅供您參考拓展思維使用，oracle學(xué)習(xí)心得：該篇文章建議您自主創(chuàng)作。

來源：網(wǎng)絡(luò)整理 免責(zé)聲明：本文僅限學(xué)習(xí)分享，如產(chǎn)生版權(quán)問題，請聯(lián)系我們及時刪除。

《oracle學(xué)習(xí)心得》由互聯(lián)網(wǎng)用戶整理提供,轉(zhuǎn)載分享請保留原作者信息,謝謝!
鏈接地址：http://www.seogis.com/gongwen/712053.html

• 上一篇：幼兒園園長培訓(xùn)心得體會
• 下一篇：Oracle的初學(xué)者入門心得