王牌对王牌第一季综艺,黄视频在线观看网站,世界一级毛片,成人黄色免费看

薈聚奇文、博采眾長、見賢思齊
當(dāng)前位置:公文素材庫 > 報(bào)告體會 > 心得體會 > oracle學(xué)習(xí)心得

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

網(wǎng)站:公文素材庫 | 時間:2019-05-29 15:09:26 | 移動端:oracle學(xué)習(xí)心得

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

一、定位

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

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

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

我的方法很簡單,就是:看書、思考、寫筆記、做實(shí)驗(yàn)、再思考、再寫筆記

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

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

學(xué)習(xí)的過程也是從點(diǎn)到線,從線到網(wǎng),從網(wǎng)到面的過程。當(dāng)點(diǎn)變成線的時候,你會有總豁然開朗的感覺。當(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ù)和驗(yàn)證數(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ù)

歸檔文件:是重做日志文件的脫機(jī)副本,這些副本可能對于從介質(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ù)器所駐留的計(jì)算機(jī)的實(shí)際內(nèi)存中得以分配,如果實(shí)際內(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)控、檢查點(diǎ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)程失敗時清理資源

檢查點(diǎ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)之后,可以重點(diǎn)關(guān)注PL/SQL及Oracle的開發(fā)工具這一部分。PL/SQL主要是包括怎么寫SQL語句,怎么使用Oracle本身的函數(shù),怎么寫存儲過程、存儲函數(shù)、觸發(fā)器等。Oracle的開發(fā)工具主要就是Oracle自己的DeveloperSuite(OracleFormsDeveloperandReportsDeveloper這些),學(xué)會如何熟練使用這些工具。

資料引用:

擴(kuò)展閱讀:Oracle學(xué)習(xí)心得

成功源自勤奮

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

ORACLE教學(xué)視頻

1.開始---運(yùn)行---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í)例(即數(shù)據(jù)庫)ORCL,當(dāng)然默認(rèn)安裝時,實(shí)例名稱你可以隨意起。當(dāng)然這里的變量值隨著你的實(shí)例名稱的改變而改變。例如

成功源自勤奮

===============================================================================關(guān)閉實(shí)例(數(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就可以連接到實(shí)例上去了

===============================================================================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選項(xiàng),那ORACLE就會將DEFAULT分配給用戶。(1)賬戶鎖定

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

例子:指定SCOTT這個用戶最多只能嘗試3次登陸,鎖定時間為2天,讓我們看怎么實(shí)現(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.

運(yùn)行ORACLE的UNIVERSALINSTALLER

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

HKEY_LOCAL_MACHINE\\SYSTEM\\CURRENTCONTROLSET\\SERVICES下以O(shè)RACLE開頭的值。重啟計(jì)算機(jī)刪除硬盤上的ORACLE目錄,如果該目錄不讓刪除,那么把這個目錄改成一個別的名字,然后重啟機(jī)器,再刪除它。刪除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和@

說明;運(yùn)行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的保存點(diǎn)

SQL->ROLLBACKTOAA;注意:當(dāng)刪除表后的操作,就可以恢復(fù)到設(shè)置保存點(diǎn)的時候的數(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,因?yàn)槭前凑账纸M的。

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

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;

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

有時在實(shí)際應(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的崗位,工資,補(bǔ)助與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ù),刪除保存點(diǎn),釋放鎖,當(dāng)使用COMMIT語句結(jié)束事務(wù)之后,其他會話將可以查看到事務(wù)變化后的新數(shù)據(jù)。

回退事務(wù)

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

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

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

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

ROLLBACK

只讀事務(wù)

只讀事務(wù)是指只允許執(zhí)行查詢的操作,而不允許執(zhí)行任何其他DML操作的事務(wù),使用只讀事務(wù)可以確保用戶只能取得某時間點(diǎn)的數(shù)據(jù)。假定機(jī)票代售點(diǎn)每天18點(diǎn)開始統(tǒng)計(jì)今天的銷售情況。這時可以使用只讀事務(wù),在設(shè)置了只讀事務(wù)后,盡管其他會話可能會提交新的事務(wù),但是只讀事務(wù)將不會取得最新數(shù)據(jù)的變化,從而可以保持取得特定時間點(diǎn)的數(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):取子串在字符串中的位置

這點(diǎn)區(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ù)點(diǎn)的M位后;如果M是負(fù)數(shù),則四舍五入到小數(shù)點(diǎn)M位前。

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

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

MOD(M,N):取模

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

對數(shù)字的處理,在財(cái)務(wù)系統(tǒng)或銀行系統(tǒng)中用的最多,不同的處理方法,對財(cái)務(wù)報(bào)表有不同的結(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):返回?cái)?shù)字N的絕對值A(chǔ)COS(N):返回?cái)?shù)字的反余弦值A(chǔ)SIN(N):返回?cái)?shù)字的反正弦值A(chǔ)TAN(N):返回?cái)?shù)字的反正切值COS(N):返回?cái)?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ù)類型和實(shí)際的不一樣。這時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ù)這個題目的要求的,因?yàn)镾AL是NUMBER(7,2)類型的,也可以隨意指定比如:99999.99或99,999.99或99,999.000注意:L和99999.99中不能加空格

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

0:顯示數(shù)字,如位數(shù)不足時,則用0補(bǔ)齊.:表示小數(shù)點(diǎn),:表示逗號$:表示美元¥:表示人民幣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:返回?cái)?shù)據(jù)庫所在主機(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的運(yùn)行來說是至關(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如果正常登陸,它其實(shí)就是一個普通的DBA用戶,但是如果以ASSYSDBA登陸,其結(jié)果實(shí)際上它是作為SYS用戶登陸的,從登陸信息里我們可以看出來例如:SYS可以建數(shù)據(jù)庫,但SYSTEM不行

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

管理初始化參數(shù)

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

顯示初始化參數(shù)

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

需要說明的如果你希望修改這些初始化的參數(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命令來完成,該命令常用的選項(xiàng)有:

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ù)庫的字符集要和客戶端字符完全一致,否則會報(bào)錯。

導(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常用的選項(xiàng)有

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ù)庫實(shí)例都不用寫了,默認(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)機(jī)的(online)狀態(tài),此時該表空間是可以訪問的,并且該表空間是可以讀寫的,即可以查詢該表空間的數(shù)據(jù),而且還可以在表空間執(zhí)行各種語句,但是在進(jìn)行系統(tǒng)維護(hù)和數(shù)據(jù)維護(hù)時,可能需要改變表空間的狀態(tài),一般情況下,由特權(quán)用戶或是dba來操作。

(1)使表空間脫機(jī)

Altertablespace表空間名offline;

(2)使表空間聯(lián)機(jī)

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ù)庫文件也刪除。

擴(kuò)展表空間

表空間是由數(shù)據(jù)文件組成的,表空間的大小實(shí)際就是數(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)無法擴(kuò)展的問題,怎么辦?

5.就擴(kuò)展該表空間,為其增加更多的存儲空間,有三種方法:

解決方法:

(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)使表空間脫機(jī)

確保數(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)機(jī)

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選項(xiàng),而增加其他四種約束使用add系選項(xiàng)。

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

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

ALTERTABLECUSTOMERADDCONSTRAINTHAHAUNIQUE(CARDID);

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

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

ALTERTABLE表名DROPCONSTRAINT約束名稱;在刪除主鍵的時候,可能有錯誤,比如ALTERTABLE表名DROPPRIMARYKEY;這是因?yàn)槿绻麅蓮埍泶嬖谥鲝年P(guān)系,那么在刪除主表的主鍵約束時,必須帶上CASCADE選項(xiàng)如: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后面,這樣篩選的速度快。索引的缺點(diǎn)

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

成功源自勤奮

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

的一致性。

實(shí)踐表明,不恰當(dāng)?shù)乃饕坏谑聼o補(bǔ),反而降低系統(tǒng)性能。因?yàn)榇罅康乃饕谶M(jìn)行插入、修改和刪除操作時比沒有索引花費(fèi)的時間更多的系統(tǒng)時間。比如:在如下字段建立索引應(yīng)該不恰當(dāng):1、很少或從不引用的字段;

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

系統(tǒng)資源為代價的,索引不能盲目地建立,這是考驗(yàn)一個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選項(xiàng)不能被授予角色。

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選項(xiàng)

該選項(xiàng)用于轉(zhuǎn)授對象權(quán)限,但是該選項(xiàng)只能被授予用戶,而不能授予角色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選項(xiàng),默認(rèn)的DBA用戶為SYS和SYSTEM他們可以將任何系統(tǒng)權(quán)限授予其他用戶,但是要注意的是DBA角色不具備

成功源自勤奮

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

自定義角色

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

如果角色時公用的角色,可以采用不驗(yàn)證的方式建立角色CREATEROLE角色名NOTIDENTIFIED;

(二)建立角色(數(shù)據(jù)庫驗(yàn)證)

采用這樣的方式時,角色名、口令名存放在數(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ù)、策略實(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ù)實(shí)際情況返回不同的結(jié)果。

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

PL/SQL(PROCEDURALLANGUAGE/SQL)是ORACLE在標(biāo)準(zhǔn)SQL語言上的擴(kuò)展,PL/SQL不僅允許嵌入SQL語言,還可以定義變量和常量,允許使用條件語句和循環(huán)語句,允許使用例外處理各種錯誤,這樣使得它的功能更加強(qiáng)大。(缺點(diǎ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),他是一個獨(dú)立的產(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塊,但是如果要想實(shí)現(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

/*例外處理部分-----處理運(yùn)行的各種錯誤*/END;

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

DBMS_OUTPUT.PUT_LINE(‘LINE’);END;

?相關(guān)說明:

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

成功源自勤奮

實(shí)例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;/

成功源自勤奮

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

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

1比如在實(shí)例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命令來建立過程。

實(shí)例如下:

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

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

實(shí)例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í)際案例:

--函數(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)建包:○

實(shí)例:

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

CREATEPACKAGESP_PACKAGEIS

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

包的規(guī)范只包含了過程和函數(shù)的說明,但是沒有過程和函數(shù)的實(shí)現(xiàn)代碼。包體用于實(shí)現(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),就會報(bào)錯。具體操作如下圖:

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

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

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

成功源自勤奮

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

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

視頻上講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的話便會報(bào)錯。(這點(diǎn)我也不懂ing

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

成功源自勤奮

觸發(fā)器

觸發(fā)器是指隱含的執(zhí)行的存儲過程,當(dāng)定義觸發(fā)器時,必須要指定觸發(fā)的事件和觸發(fā)的操作,常用的觸發(fā)事件包括INSERT、UPDATE、DELETE語句,而觸發(fā)操作實(shí)際就是一個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;--計(jì)算所得稅

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;--計(jì)算所得稅

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記錄實(shí)例

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)沒有限制,實(shí)例如下:

--PL/SQL表實(shí)例

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去掉會怎樣呢?

解答:因?yàn)镾P_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é)合了。實(shí)例如下:

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;--要計(jì)算MYROWS和MYPAGECOUNT--組織一個SQL語句

V_SQL:="SELECTCOUNT(*)FROM"||TABLENAME;--執(zhí)行SQL,并把返回的值,賦給MYROWSEXECUTEIMMEDIATEV_SQLINTOMYROWS;--計(jì)算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.taixiivf.com/gongwen/712053.html