Oracle相關(guān)總結(jié)

Oracle相關(guān)總結(jié)

一、測試oracle安裝是否成功

安裝完Oracle如何驗(yàn)證安裝是否成功呢？下面介紹測試方法：

1.執(zhí)行操作系統(tǒng)級命令：tnspingorcl（假定全局?jǐn)?shù)據(jù)庫名為orcl）結(jié)果如下：

結(jié)果出現(xiàn)OK，說明偵聽器配置無誤，繼續(xù)第2步。如果沒有出現(xiàn)上圖結(jié)果，請檢查：

1）使用OracleNetConfigurationAssistant檢查網(wǎng)絡(luò)配置（netca命令）；2）使用DatabaseConfigurationAssistant檢查數(shù)據(jù)庫配置（dbca命令）。

2.執(zhí)行操作系統(tǒng)級的命令：sqlplussys/root@orclassysdba連接成功后輸入測試sql語句：select1+2+3sumfromdual;結(jié)果如下：

如果結(jié)果正確，表明數(shù)據(jù)庫例程安裝和啟動(dòng)正常�？梢蚤_始用剛安裝完的Oracle開始其他工作。

二、新建用戶、表空間并分配權(quán)限

Oracle安裝完后，其中有一個(gè)缺省的數(shù)據(jù)庫，除了這個(gè)缺省的數(shù)據(jù)庫外，我們還可以創(chuàng)建自己的數(shù)據(jù)庫。創(chuàng)建完數(shù)據(jù)庫后，并不能立即在數(shù)據(jù)庫中建表，必須先創(chuàng)建該數(shù)據(jù)庫的用戶，并且為該用戶指定表空間。

明確要在哪個(gè)數(shù)據(jù)庫下建立用戶、表空間，就先用dba權(quán)限的用戶（sys,system等）登錄該數(shù)據(jù)庫，再進(jìn)行以下操作。

1.查找所有數(shù)據(jù)庫文件存放的路徑,可根據(jù)查到的路徑定自己新建表空間數(shù)據(jù)文件存放的路徑。（當(dāng)然也可以自己定路徑，不必參照，可略過此步）select*fromdba_data_files;2.創(chuàng)建表空間

createtablespaceorcl_VoicedReading--表空間名Datafile--數(shù)據(jù)庫文件保存路徑

"C:\\ORACLE\\PRODUCT\\10.2.0\\ORADATA\\ORCL\\orcl_VoicedReading.dbf"size100M--大小autoextendon;create

userwyli-用戶名

identifiedbyroot--密碼

defaulttablespaceorcl_VoicedReading;--指定默認(rèn)表空間4.分配權(quán)限

--自動(dòng)增長

3.創(chuàng)建用戶并指定表空間

grantdbatowyli;--分配dba權(quán)限給用戶wyli

到此，為數(shù)據(jù)庫新建用戶完成，可以用新建的用戶以normal身份登錄，然后開始在數(shù)據(jù)庫上做建表等工作。

三、寫Oracle存儲過程要注意的問題

1.在oracle中，數(shù)據(jù)表別名不能加as，如：selecta.appnamefromappinfoa;--正確selecta.appnamefromappinfoasa;--錯(cuò)誤

2.在存儲過程中，select某一字段時(shí)，后面必須緊跟into，如果select整個(gè)記錄，利用游標(biāo)的話就另當(dāng)別論了。

selectaf.keynodeintoknfromAPPFOUNDATIONafwhereaf.appid=aidandaf.foundationid=fid;--有into，正確編譯

selectaf.keynodefromAPPFOUNDATIONafwhereaf.appid=aidandaf.foundationid=fid;

--沒有into，編譯報(bào)錯(cuò)，提示：Compilation

3.在利用select...into...語法時(shí)，必須先確保數(shù)據(jù)庫中有該條記錄，否則會(huì)報(bào)出"no

datafound"異常。

可以在該語法之前，先利用selectcount(*)from查看數(shù)據(jù)庫中是否存在該記錄，如果存在，再利用select...into...

4.在存儲過程中，別名不能和字段名稱相同，否則雖然編譯可以通過，但在運(yùn)行階段會(huì)報(bào)錯(cuò)selectkeynodeintoknfromAPPFOUNDATIONwhereappid=aidandfoundationid=fid;--正確運(yùn)行

selectaf.keynodeintoknfromAPPFOUNDATIONafwhereaf.appid=appidandaf.foundationid=foundationid;

5.在存儲過程中，關(guān)于出現(xiàn)null的問題假設(shè)有一個(gè)表A，定義如下：createtableA(

idvarchar2(50)primarykeynotnull,vcountnumber(8)notnull,

bidvarchar2(50)notnull--外鍵);

如果在存儲過程中，使用如下語句：

selectsum(vcount)intofcountfromAwherebid="xxxxxx";如果A表中不存在bid="xxxxxx"的記錄，則fcount=null(即使fcount定義時(shí)設(shè)置了默認(rèn)值，如：fcountnumber(8):=0依然無效，fcount還是會(huì)變成null)，這樣以后使用fcount時(shí)就可能有問題，所以在這里最好先判斷一下：iffcountisnullthenfcount:=0;endif;

--運(yùn)行階段報(bào)錯(cuò)

四、企業(yè)庫連接Oracle

企業(yè)庫(3.1)連接Oracle

命名空間Microsoft.Practices.EnterpriseLibrary.Data;中的DatabaseFactory類提供兩個(gè)方法來創(chuàng)建database對象。

1.用方法DatabaseFactory.CreateDatabase(string)

配置文件：

示例代碼：

usingMicrosoft.Practices.EnterpriseLibrary.Data;usingSystem.Data.Common;publicboolIsExistBackgroundMusic(stringbgMusicName){//創(chuàng)建database實(shí)例Databasedb=DatabaseFactory.CreateDatabase("Oracle");stringproduceName="proce_BackgroundMusic_Exist";DbCommandcommand=db.GetStoredProcCommand(produceName);//為command提供參數(shù)db.AddInParameter(command,"ParamBackgroundMusicName",DbType.String,bgMusicName);db.AddOutParameter(command,"outCount",DbType.Int32,4);//執(zhí)行存儲過程db.ExecuteScalar(command);intoutCount=(int)db.GetParameterValue(command,"outCount");if(outCount==0){returnfalse;}returntrue;}

2.用方法DatabaseFactory.CreateDatabase()

配置文件：

示例代碼：

usingMicrosoft.Practices.EnterpriseLibrary.Data;usingSystem.Data.Common;publicboolIsExistBackgroundMusic(stringbgMusicName){Databasedb=DatabaseFactory.CreateDatabase();stringproduceName="proce_BackgroundMusic_Exist";DbCommandcommand=db.GetStoredProcCommand(produceName);db.AddInParameter(command,"ParamBackgroundMusicName",bgMusicName);db.AddOutParameter(command,"outCount",DbType.Int32,4);db.ExecuteScalar(command);intoutCount=(int)db.GetParameterValue(command,"outCount");if(outCount==0){returnfalse;}returntrue;}

DbType.String,

擴(kuò)展閱讀：Oracle知識點(diǎn)總結(jié)

Oracle知識點(diǎn)總結(jié)

根據(jù)閻赫老師講義整理Zealjiang1、Oracle數(shù)據(jù)庫的安裝和配置⑴OracleInternet級數(shù)據(jù)庫SQLServer中小企業(yè)級數(shù)據(jù)庫Access桌面級數(shù)據(jù)庫⑵Oracle的安裝

注意：來源和目標(biāo)的目錄不允許有中文或空格⑶Oracle數(shù)據(jù)庫啟動(dòng)時(shí)必需開啟的后臺服務(wù)

①OracleOrahome90TNSListener使第三方的軟件或語言訪問②OracleServiceETCOracle的實(shí)例CRUD增刪改查注意：②中的ETC是你起的實(shí)例的名字⑷Oracle的開發(fā)工具

①DOS界面的開發(fā)平臺->運(yùn)行->sqlplus②Oracle本身基于DOS的平臺->運(yùn)行->sqlplusw③OracleEnterpriseManagerConsole④PL/SQLDeveloper7.1.5⑸創(chuàng)建一個(gè)表的完整流程①創(chuàng)建一個(gè)數(shù)數(shù)庫

例子：創(chuàng)建一個(gè)數(shù)據(jù)庫ETC,物理文件放在F:\\，初始化1m,自增長開啟

createtablespaceetcdatafile"f:\\etc.dbf"size1mautoextendon;

刪除表空間

droptablespace數(shù)據(jù)庫名稱;②為該數(shù)據(jù)庫創(chuàng)建一個(gè)帳號和密碼語法：createuser用戶名稱identifiedby密碼

defaulttablespace數(shù)據(jù)庫名稱

注意：1、在Oracle中賬號和數(shù)據(jù)庫one2one綁定2、Oracle的密碼不允許為純數(shù)字或空3、需要在system賬號完成動(dòng)作修改Oracle的賬號密碼語法：alteruser用戶identifiedby新密碼③用grant權(quán)限to用戶為該帳戶授權(quán)語法：grant權(quán)限名稱to用戶;撤銷權(quán)限

語法：revoke權(quán)限名稱from用戶名;④在該帳號下創(chuàng)建表⑹Oracle中的事務(wù)處理Transacation事務(wù)

特點(diǎn)：整體提交(commit)整體回滾(rollback)事務(wù)的四個(gè)特性

①原子性不可分割

②持久性->當(dāng)數(shù)據(jù)整整寫入到數(shù)據(jù)庫物理文件中后，該數(shù)據(jù)被持久化

③隔離性->事務(wù)之間相互獨(dú)立互不干擾④一致性->數(shù)據(jù)安全⑺Oracle中的保留點(diǎn)(還原點(diǎn))關(guān)鍵字：savepoint使用方法：savepoint名稱;如進(jìn)行保留點(diǎn)回滾rollbacktomark2⑻Oracle備份數(shù)據(jù)表數(shù)據(jù)

①createtable備份表名稱as查詢語句;②Oracle備份表結(jié)構(gòu)

createtableemp_bakasselect*fromempwhere1=2;2、Oracle的函數(shù)

⑴function（系統(tǒng)預(yù)定義函數(shù)）

函數(shù)和存儲過程相比，在于函數(shù)必須有返回值，而存儲過程只有輸出參數(shù)。語法：createorreplacefunction函數(shù)名稱return返回類型as

PL/SQL語句塊

例子：輸入一個(gè)員工工號，返回該員工所在部門平均工資

createorreplacefunctionfun_demo1(enoemp.empno%type)returnemp.sal%typeas

avgsalemp.sal%type;begin

selectavg(sal)intoavgsalfromempwhereempno=eno;returnavgsal;end;

調(diào)用：selectfun_demo1(7788)fromdual;

publicintadd(intnum1,intnum2){intres=0;res=num1+num2;returnres;}

⑵單行函數(shù)(單值函數(shù))：函數(shù)有且只有返回1個(gè)值|-字符函數(shù)substrinstr①字符串連接函數(shù)：concatconcat(字符串1,字符串2)Oracle中字符連接操作符號"||"

②字符串對齊函數(shù)：lpad->leftpadding左填充/rpad語法：lpad(字符串,預(yù)留位置,填充字符)

SQL:selectlpad(ename,10,"")fromemp;③字符串截取函數(shù)：substr->substring

語法：substr(字符串,截取的起始位置,截取個(gè)數(shù))substr(字符串,截取的起始位置)SQL:>selectsubstr(ename,1,3)fromeemp;④字符查找函數(shù)：instr->innerstring

語法：instr(字符串,待找的字符,查找的起始位置,出現(xiàn)次數(shù))SQL:>selectename,instr(ename,"T",1,1)fromemp;注意：當(dāng)返回值為0時(shí)，字符不存在⑤函數(shù)：initcap->initialcaptor語法：initcap(字符串)

SQL:>selectinitcap(ename)fromemp;⑥函數(shù)：length

語法：length(字符串)

SQL:>select*fromempwherelength(ename)=5;⑦函數(shù)：lower/upper語法：lower(字符串)SQL:>selectlower(ename)fromemp;

|-日期函數(shù)sysdateadd_months①函數(shù)：sysdate

SQL:>selectsysdatefromdual;dual-無實(shí)際意義，函數(shù)測試或其他測試使用

②月份差：months_between

語法：months_between(日期1,日期2)SQL:>select*fromempwheremonths_between(sysdate,hiredate)>=144③函數(shù)：last_day

語法：last_day(日期數(shù)據(jù))SQL:>select*fromempwherelast_day(hiredate)-2=hiredate;④函數(shù)：add_months

語法：add_months(時(shí)間,添加月份)

SQL：>selectadd_months(sysdate,3)fromdual;|-轉(zhuǎn)換函數(shù)to_charto_date①轉(zhuǎn)換函數(shù)to_char

語法：to_char(時(shí)間數(shù)據(jù),制定格式)

SQL:>selectto_char(sysdate,"yyyy-mm-ddhh24:mi:ssddddday")fromdual;yyyy-mm-dd年月日hh24:mi:ss時(shí)分秒(24)d一周第幾天ddd一年的第幾天day星期幾

②to_date:將文本類型數(shù)據(jù)按照制定格式轉(zhuǎn)換成時(shí)間類型數(shù)據(jù)語法：to_date("具體的時(shí)間字符數(shù)據(jù)","時(shí)間字符串格式")to_date("201*-01-19","yyyy-mm-dd")注意：Oracle中的date字段類型及其特殊|-數(shù)學(xué)函數(shù)absceilfloorabs(n)絕對值

ceil(n)向上取值ceilling天花板ceil(1.1)->2floor(n)向下取值floor地板floor(2.9)->2mod(m,n)求模取余mod(5,2)->1power(m,n)m的n次方power(2,8)->256

round(m,n)四舍五入m待處理數(shù)據(jù)n精度round(3.1415,2)->3.14

trunc(m)整數(shù)截取trunc(3.1415)=3sign(m)符號sqrt(m)平方根|-混合函數(shù)usernvl①user:返回當(dāng)前登錄賬號SQL>selectuserfromdual;②nvl:替空函數(shù)

語法：nvl(字符串,替換后的數(shù)據(jù))

注意：替換后的數(shù)據(jù)必須與該字段類型保持一致SQL>selectename,nvl(comm,0)fromemp;

⑶分析函數(shù)：數(shù)據(jù)分析和挖掘功能DataMiningrankdense_rank作用：主要用于排名使用函數(shù)：rank

①語法：rank()over(orderby字段名稱)

SQL:>selectrank()over(orderbysaldesc),ename,salfromemp;若排名相同下一個(gè)將跳轉(zhuǎn)

SQL:>selectdense_rank()over(orderbysaldesc),ename,salfromemp;連續(xù)

②語法：rank(參數(shù)1，參數(shù)2)withingroup(orderby字段1，字段2)

SQL:>selectename,rank(2850,"BLAKE")withingroup(orderbysal,ename)fromemp;

⑷分組函數(shù)：類似于SQLServer中的聚合函數(shù)sumavgcountgroupby分組關(guān)鍵字having條件3、Oracle數(shù)據(jù)庫的查詢⑴制定區(qū)間查詢

Oracle中偽列：Oracle為每張數(shù)據(jù)表(包括用戶自定義創(chuàng)建的數(shù)據(jù)表)都自動(dòng)創(chuàng)建兩個(gè)位列分別是：rowid、rownum通過命令：SQL>desc表名;--查看表結(jié)構(gòu)rowid:由18個(gè)字符組成唯一標(biāo)識每一行rownum:行號

⑵介紹問題：使用Oracle分層查詢以數(shù)據(jù)結(jié)構(gòu)顯示emp表中的人員關(guān)系語法：selectlpad(ename,level*5,"")fromempconnectbypriorempno=mgrstartwithmgrisnull;

⑶并集：查詢部門10的辦事員和部門20的經(jīng)理關(guān)鍵字：unionall

select*fromempwheredeptno=10andjob="CLERK"unionall

select*fromempwheredeptno=20andjob="MANAGER";

注意：unionall不會(huì)消除查詢結(jié)果中的重復(fù)數(shù)據(jù),union會(huì)消除查詢結(jié)果中的重復(fù)記錄

⑷交集：查詢部門10和部門20都有的工作類型關(guān)鍵字：intersect

selectjobfromempwheredeptno=10intersect

selectjobfromempwheredeptno=20;

⑸差集：查詢部門30中有，而部門10中沒有的工作類型關(guān)鍵字：minusselectjobfromempwheredeptno=30minus

selectjobfromempwheredeptno=10;

4、Oracle數(shù)據(jù)庫的數(shù)據(jù)對象

Oracle的數(shù)據(jù)庫對象：用戶、表、約束、序列、視圖、同義詞和索引定義：但凡使用"create"開頭創(chuàng)建的對象稱之為數(shù)據(jù)庫對象。⑴鎖定/解除用戶

語法：alteruser用戶名稱accountlock;注意：該指令一般在system賬號下輸入解除用戶鎖定

語法：alteruser用戶名稱accountunlock;SQL>alteruserscottaccountunlock;

注意：Oracle11g中scott賬號是默認(rèn)鎖定的，需要進(jìn)行解鎖處理。⑵用戶授權(quán)/撤銷

grant權(quán)限名稱to用戶名稱;撤銷用戶權(quán)限

revoke權(quán)限名稱from用戶名;⑶表

數(shù)據(jù)字段的類型①數(shù)字類型：number語法：number(長度,精度)number(5,2)數(shù)字長度為3，精度為2范圍：-999.99~999.99number(5)數(shù)字長度為5，精度為0范圍：-99999~99999number精度默認(rèn)為0范圍：-32767~32768②字符類型：varchar2可變長度char字符類型語法：varchar2(長度)③日期類型：date

④long數(shù)據(jù)類型：該字段最大存儲空間為2GB，該字段不允許添加索引

LOB數(shù)據(jù)類型：該字段最大存儲空間為4GB，該字段不允許添加索引CLOB：大字符類型->文章(小說，cnki)

BLOB：大二進(jìn)制類型->圖片、音頻、視頻.....多媒體文件FLOB：文件定位器->內(nèi)存指針⑷創(chuàng)建數(shù)據(jù)表的語法結(jié)構(gòu)：createtable表名(

字段名稱1類型[約束],字段名稱1類型[約束],

字段名稱n類型[約束])

①使用desc表名查看表結(jié)構(gòu)②給表添加字段address和telephone語法：altertable表名add(字段名稱類型);③刪除表字段address

語法：altertable表名dropcolumn字段名稱;④修改表中已有字段的類型

語法：altertable表名modify(字段名稱新類型);⑤修改表名稱

語法：rename原表名to新表名;約束

完整性約束=準(zhǔn)確性+一致性⑸約束的四大分類：

①實(shí)體完整性約束(行約束)->盡量減少數(shù)據(jù)表中數(shù)據(jù)的冗余(重復(fù)的數(shù)據(jù))

技術(shù)實(shí)施：主鍵約束、唯一約束

語法：altertable表名addprimarykey(字段名稱);altettable表名addunique(字段名稱);添加非空約束

語法：altertable表名modify(字段名稱notnull);

②域完整性約束(列約束)->達(dá)到數(shù)據(jù)的準(zhǔn)確性，控制數(shù)據(jù)的大小或范圍或格式

技術(shù)實(shí)施：check檢查約束

altertable表名addconstraint約束名稱check(條件);③引用完整性約束(表間約束)->達(dá)到數(shù)據(jù)的一致性技術(shù)實(shí)施：外鍵約束添加外鍵約束

altertable外鍵表addconstraint約束名稱foreignkey(外鍵字段)references主鍵表(主鍵字段);刪除外鍵約束

語法：altertable表名dropconstraint約束名稱;刪除匿名約束

語法：altertable表名modify(字段名稱null);

④自定義完整性約束->以上三種數(shù)據(jù)庫內(nèi)置約束不能滿足開發(fā)人員的需求是，需要開發(fā)人員自定一些約束條件技術(shù)實(shí)施：觸發(fā)器⑹使用sql腳本批量插入數(shù)據(jù)使用命令SQL>@路徑+文件名稱SQL>@f:/a.sql;⑺序列

定義：Oracle中使用sequence來實(shí)現(xiàn)字段的自增長功能，和SQLServer中的identity屬性類型語法：createsequence序列名稱startwith起始數(shù)字incrementby增長量;

用法：序列對象通過兩個(gè)重要的屬性進(jìn)行訪問取值.nextval->nextvalue->下一個(gè)值例子：selectseq_1.nextvalfromdual;.currval->currentvalue->當(dāng)前值例子：selectseq_1.currvalfromdual;如何實(shí)現(xiàn)其自增長功能

通過語法結(jié)構(gòu)將序列對象與對應(yīng)的數(shù)據(jù)表進(jìn)行綁定，實(shí)現(xiàn)其自增長功能。

例子：createtableusers(useridnumber(2),usernamevarchar2(10));

createsequenceseq_usersstartwith1incrementby1;insertintousersvalues(seq_users.nextval,"alvin");注意：Oracle建議一個(gè)序列對象盡與一張數(shù)據(jù)表進(jìn)行綁定技巧：createsequence序列名稱;默認(rèn)從1每次增長1⑻視圖

作用：1、簡化復(fù)雜的SQL語句2、提高數(shù)據(jù)的訪問安全性語法：createorreplaceview視圖名稱as

復(fù)雜的SQL查詢語句

視圖是一張?zhí)摂M的數(shù)據(jù)表，在Table對象中不存在，只存在于內(nèi)存中注意：若視圖由1張基表組成，修改視圖將會(huì)修改基表數(shù)據(jù)，若視圖由多張表組成，則不會(huì)修改基表數(shù)據(jù)⑼同義詞

作用：Oracle中的同義詞提供各種數(shù)據(jù)庫對象(表)的別名，目的在于提高數(shù)據(jù)表訪問的安全性，尤其多用戶并發(fā)訪問時(shí)。語法：createsynonym表的別名for表名;SQL>createsynonymbak1foremp;擴(kuò)展：公有同義詞public

語法：createpublicsynonym別名for表名;⑽索引

作用：提高SQL查詢語句按照制定字段查詢的效率語法：createindex索引的名稱on數(shù)據(jù)表(字段)

例子：為hiredate字段添加索引，提高按日期查詢的SQL語句的效率

createindexidx_hireonemp(hiredate)原理：空間換效率SQL語句索引優(yōu)化規(guī)則

①不帶where條件的SQL語句一定不能使用索引②在where條件中有索引的字段不能使用函數(shù)例子：假設(shè)我們查詢在1987年參加工作的所有員工

SQL>select*fromempwhereto_char(hiredate,"yyyy")="1987";SQL>select*fromempwherehiredate=to_date("1987","yyyy");③在where條件中有索引的字段不能參與運(yùn)算例子：查詢在10000天以前參加工作的員工信息SQL>select*fromempwheresysdate-hiredate>10000;SQL>select*fromempwherehiredateselecte.*,d.dnamefromempe,deptdwheree.deptno=d.deptnoandd.dname="ACCOUNTING";⑾Oracle中常用的數(shù)據(jù)字典

Oracle是以中以表管表的模式，同時(shí)數(shù)據(jù)字典是有表或視圖組成。數(shù)據(jù)字典的分類：

USER_xxx：表示當(dāng)前用戶所擁有的數(shù)據(jù)庫對象ALL_xxxx：表示當(dāng)前用戶與權(quán)力查看的數(shù)據(jù)庫對象

DBA_xxxx：表示數(shù)據(jù)中所擁有的全部對象，只有在超級管理員級別下才可查看。

xxxx：代表數(shù)據(jù)庫對象的復(fù)數(shù)形式，例如：tablesindexes.......

5、Oracle數(shù)據(jù)庫的多表查詢

⑴等連接：selectemp.ename,dept.dnamefromemp,deptwhereemp.deptno=dept.deptno;使用內(nèi)連接模式編寫：⑵內(nèi)連接：innerjoin......on......

selectemp.ename,dept.dnamefromempinnerjoindeptonemp.deptno=dept.deptno;

分析：部門為主顯示字段，dept為主表姓名為輔助顯示字段，emp為輔助表

⑶外連接：selectd.dname,e.enamefromempe,deptdwheree.deptno(+)=d.deptno;

6、Oracle數(shù)據(jù)庫的游標(biāo)游標(biāo)cursor

游標(biāo)的分類：隱式游標(biāo)：Oracle自動(dòng)應(yīng)以一個(gè)隱式游標(biāo)名稱為SQL,該游標(biāo)不被程序員控制，自動(dòng)開啟、操作及結(jié)束。顯式游標(biāo)：可供程序員自己創(chuàng)建及操作|---靜態(tài)游標(biāo)|---動(dòng)態(tài)游標(biāo)⑴靜態(tài)游標(biāo)loop循環(huán)游標(biāo)/for循環(huán)游標(biāo)

====loop循環(huán)游標(biāo)=====步驟1：創(chuàng)建一個(gè)游標(biāo)

cursor游標(biāo)名稱isSQL查詢語句;

步驟2：開啟游標(biāo)

open游標(biāo)名稱;->執(zhí)行定義的SQL查詢語句并將結(jié)果集合存放到游標(biāo)中

步驟3：使用loop循環(huán)遍歷游標(biāo)中的數(shù)據(jù)并進(jìn)行相應(yīng)處理loop

fetch游標(biāo)名稱into變量;exitwhen游標(biāo)名稱%notfound;.....endloop;步驟4：關(guān)閉游標(biāo)close游標(biāo)名稱;

例子：顯示部門編號為10的員工姓名declare

--步驟1：創(chuàng)建一個(gè)游標(biāo)

cursormycurisselectenamefromempwheredeptno=10;enemp.ename%type;begin

--步驟2：開啟游標(biāo)openmycur;

--步驟3：使用loop循環(huán)遍歷游標(biāo)中的數(shù)據(jù)并進(jìn)行相應(yīng)處理loop

fetchmycurintoen;exitwhenmycur%notfound;dbms_output.put_line(en);endloop;

--步驟4：關(guān)閉游標(biāo)closemycur;end;

=======for循環(huán)游標(biāo)=======declare

--步驟1：創(chuàng)建一個(gè)游標(biāo)

cursormycurisselect*fromempwheredeptno=10;erowemp%rowtype;begin

--步驟3：使用for循環(huán)遍歷游標(biāo)中的數(shù)據(jù)并進(jìn)行相應(yīng)處理forerowinmycurloopdbms_output.put_line(erow.ename);endloop;end;

⑵動(dòng)態(tài)游標(biāo)(擴(kuò)展)

作用：使用游標(biāo)變量高度重用(只能使用loop循環(huán))語法結(jié)構(gòu)：

步驟1：聲明一個(gè)動(dòng)態(tài)游標(biāo)類型type類型名稱isrefcursor;

步驟2：使用聲明號的類型創(chuàng)建一個(gè)游標(biāo)變量游標(biāo)名稱類型名稱;

步驟3：打開游標(biāo)并且綁定SQL語句open游標(biāo)名稱forSQL查詢語句

步驟4：使用循環(huán)遍歷游標(biāo)中的數(shù)據(jù)并進(jìn)行處理loop

fetch游標(biāo)名稱into變量;exitwhen游標(biāo)名稱%notfound.....數(shù)據(jù)處理......endloop;步驟5：重復(fù)3~步驟6：關(guān)閉游標(biāo)close游標(biāo)名稱;

例子：顯示部門30的員工姓名及dept表中全部部門名稱declare

--聲明一個(gè)游標(biāo)類型typecurisrefcursor;--聲明游標(biāo)變量mycurcur;--聲明變量erowemp%rowtype;drowdept%rowtype;begin--打開游標(biāo)

openmycurforselect*fromempwheredeptno=30;loop

fetchmycurintoerow;exitwhenmycur%notfound;

dbms_output.put_line(erow.ename);endloop;

dbms_output.put_line("============");--打開游標(biāo)

openmycurforselect*fromdept;loop

fetchmycurintodrow;exitwhenmycur%notfound;

dbms_output.put_line(drow.dname);endloop;--關(guān)閉游標(biāo)closemycur;end;

⑶游標(biāo)所有屬性游標(biāo)名稱%屬性名稱屬性名稱含義

notfound當(dāng)游標(biāo)中沒有遍歷的數(shù)據(jù)時(shí)返回1found當(dāng)游標(biāo)中存在有遍歷的數(shù)據(jù)時(shí)返回1isopen當(dāng)游標(biāo)開啟狀態(tài)時(shí)返回1rowcount返回游標(biāo)影響行數(shù)

7、Oracle數(shù)據(jù)的PL/SQL⑴PL/SQL塊

語法格式：declare聲明

......變量聲明區(qū)begin程序開始......執(zhí)行語句區(qū)exception異常處理部分.....

end;程序結(jié)束;①變量的聲明

標(biāo)量標(biāo)識符，不區(qū)分大小寫，先聲明再使用變量聲明的語法結(jié)構(gòu)：標(biāo)量名稱類型;例子：declare

enonumber(4);edatedate;beginend;②★屬性引用類型|-字段引用類型例子：declareenoemp.ename%type;|-行引用類型例子：declareerowemp%rowtype;③變量的賦值四種方式：◆聲明時(shí)賦值賦值運(yùn)算符“:=”

例子：聲明一個(gè)變量賦值為2declare

num1number(2):=2;.....begin

.....end;◆執(zhí)行區(qū)賦值

例子：聲明一個(gè)變量賦值為2declare

num1number(2);.....beginnum1:=2;.....end;

◆對話框賦值->程序與用戶之間進(jìn)行數(shù)據(jù)交互例子：聲明一個(gè)變量賦值為2declare

num1number(2);.....begin

num1:=&請輸入數(shù)字;.....end;

注意點(diǎn)：當(dāng)輸入字符串類型數(shù)據(jù)的使用書寫格式為：name:="&姓名";當(dāng)輸入數(shù)字類型數(shù)據(jù)的使用書寫格式為：age:=&年齡;◆select....into....賦值模式

例子：將emp表中simth的工資復(fù)制到變量esal中declare

esalemp.sal%type;begin

selectsalintoesalfromempwhereename="SMITH";end;

④Oracle的PL/SQL中屏幕輸出語句

語法：dbms_output.put_line("xxxxxxx");將信息輸出并換行dbms_output.put("xxxxxxx");輸出不換行

注意：Oracle默認(rèn)關(guān)閉屏幕輸出功能，需要開發(fā)人員輸入指令將其功能開啟

SQL>setserveroutputon;⑤PL/SQL中的異常處理語法：exception

when異常名稱then....處理方式.....異常：others->任何異常都可捕獲⑵執(zhí)行語句部分

①循環(huán)的使用：forwhileloop

for語法結(jié)構(gòu)：for循環(huán)變量in起始數(shù)字..結(jié)束數(shù)字loop

....循環(huán)體....endloop;

例子1：顯示數(shù)字10~1(reverse)declareinumber;begin

foriinreverse1..10loop

dbms_output.put_line(i);endloop;end;

例子1：顯示乘法口訣declareinumber;jnumber;begin

--外層循環(huán)控制行數(shù)foriin1..9loop

--內(nèi)層循環(huán)控制個(gè)數(shù)forjin1..iloop

dbms_output.put(j||"*"||i||"="||i*j||"");endloop;--換行

dbms_output.put_line("");endloop;end;

②while循環(huán)

語法：while進(jìn)入條件loop

....循環(huán)體.....endloop;③loop循環(huán)語法：loopexitwhen推出條件;...循環(huán)體....endloop;⑶條件判斷

語法：if條件thenelsif條件thenelseendif;

例子：模擬一個(gè)登錄結(jié)構(gòu)，用戶名為admin密碼為chinasoftdeclare

usernamevarchar2(10);passwordvarchar2(10);begin

--用戶輸入賬號及密碼username:="&賬號";password:="&密碼";--邏輯判斷

ifusername="admin"andpassword="chinasoft"thendbms_output.put_line("歡迎登錄："||username);else

dbms_output.put_line("賬號或密碼錯(cuò)誤！");endif;end;

⑷處理自定義異常

例子：若用戶輸入的數(shù)字不再1~10之內(nèi)則拋出異常

步驟1：創(chuàng)建一個(gè)異常對象在變量聲明區(qū)輸入一下代碼

expexception;--聲明一個(gè)異常對象，名稱為exp步驟2：編寫條件判斷結(jié)構(gòu)拋出異常在語句執(zhí)行區(qū)編寫一下代碼if.....then

raise異常對象名稱;--拋出異常endif;步驟3：異常捕獲在語句執(zhí)行區(qū)編寫exception

when異常對象名稱then......處理方式........declare

nnumber;--用來接收用戶輸入的數(shù)字expexception;--創(chuàng)建一個(gè)異常對象beginn:=&數(shù)字;--判斷

ifn10thenraiseexp;--拋出異常endif;

dbms_output.put_line(n);exceptionwhenexpthen

dbms_output.put_line("數(shù)字范圍不再1~10之內(nèi)");end;⑸

8、Oracle數(shù)據(jù)庫中的存儲過程、函數(shù)、數(shù)據(jù)包⑴Oracle中的存儲過程

Oracle中的PL/SQL語句塊，沒有名字，成為匿名的PL/SQL塊，代碼重用性低。需要為PL/SQL塊起名字，起名后的PL/SQL塊統(tǒng)稱為子程序。子程序的分類：存儲過程、自定義函數(shù)①存儲過程

語法：createorreplaceprocedure存儲過程名稱as

PL/SQL塊②如何調(diào)用存儲過程

兩種方式：1、指令方式->exec存儲過程名稱;2、使用PL/SQL塊調(diào)用begin

存儲過程名稱;end;③帶參數(shù)的存儲過程兩大類：a)帶輸入?yún)��?shù)b)帶輸出參數(shù)

a)帶輸入?yún)��?shù)的存儲過程

語法：createorreplaceprocedure存儲過程名稱(參數(shù)名稱1in類型,....,參數(shù)名稱n類型)as

PL/SQL塊b)帶輸出參數(shù)的存儲過程

語法：createorreplaceprocedure存儲過程名稱(參數(shù)名稱1out類型,....,參數(shù)名稱nout類型)as

PL/SQL塊例子：顯示部門10的平均工資(使用輸出參數(shù))

createorreplaceprocedurepro_demo3(avgsaloutemp.sal%type)asbegin

selectavg(sal)intoavgsalfromempwheredeptno=10;end;

調(diào)用：只能使用PL/SQL塊declare

getsalemp.sal%type;begin

pro_demo3(getsal);

dbms_output.put_line(getsal);end;

/1、輸入指定員工的員工編號，輸出其所在部門的其他姓名(存儲過程、帶參數(shù)的游標(biāo)、輸入?yún)��?shù))

思路：*1、使用SQL語句查詢7788員工所在部門的其他員工信息select*fromempwheredeptno=(selectdeptnofromempwhereempno=7788)andempno7788;*2、修改成PL/SQL塊declare

cursormycur(enoemp.empno%type)isselect*fromempwheredeptno=(selectdeptnofromempwhereempno=eno)andempnoeno;erowemp%rowtype;tmpemp.empno%type;begintmp:=&工號;

forerowinmycur(tmp)loop

dbms_output.put_line(erow.ename);endloop;end;

*3、修改成為存儲過程

createorreplaceprocedurepro_demo4(tmpemp.empno%type)as

cursormycur(enoemp.empno%type)isselect*fromempwheredeptno=(selectdeptnofromempwhereempno=eno)andempnoeno;erowemp%rowtype;begin

forerowinmycur(tmp)loop

dbms_output.put_line(erow.ename);endloop;end;*4、調(diào)用

execpro_demo4(7788);

/2、輸出制定員工所在部門的平均工資(存儲過程、輸入?yún)��?shù)、輸出參數(shù))思路：*1、編寫SQL語句

selectavg(sal)fromempwheredeptno=(selectdeptnofromempwhereempno=7788)*2、PL/SQL語句declare

enoemp.empno%type;esalemp.sal%type;begineno:=&編號;

selectavg(sal)intoesalfromempwheredeptno=(selectdeptnofromempwhereempno=eno);dbms_output.put_line(esal);end;*3、修改為存儲過程

createorreplaceprocedurepro_demo5(enoinemp.empno%type,esaloutemp.sal%type)asbeginselectavg(sal)intoesalfromempwheredeptno=(selectdeptnofromempwhereempno=eno);end;*4、調(diào)用declare

tmpemp.sal%type;begin

pro_demo5(7788,tmp);dbms_output.put_line(tmp);end;⑵程序包

程序包是一個(gè)函數(shù)或存儲過程的集合。程序包的結(jié)構(gòu)：第一部分：程序包的聲明createorreplacepackage包名稱as

存儲過程的聲明;自定義函數(shù)的聲明;end;

第二部分：程序包主體

createorreplacepackagebody包名稱as存儲過程的實(shí)現(xiàn);自定義函數(shù)的實(shí)現(xiàn);

例子：完成對EMP表進(jìn)行增刪改的操作part1:

createorreplacepackageemp_pack

procedureadd_emp(enoemp.empno%type,enmemp.ename%type,dnoemp.deptno%type);

procedureupdate_emp(enoemp.empno%type,esalemp.sal%type);proceduredelete_emp(enoemp.empno%type);end;part2:

createorreplacepackagebodyemp_packas--添加

procedureadd_emp(enoemp.empno%type,enmemp.ename%type,dnoemp.deptno%type)asbegin

insertintoemp(empno,ename,sal)values(eno,enm,dno);dbms_output.put_line("addisok");end;--修改procedureupdate_emp(enoemp.empno%type,esalemp.sal%type)asbegin

updateempsetsal=esalwhereempno=eno;dbms_output.put_line("updateisok");end;--刪除

proceduredelete_emp(enoemp.empno%type)asbegin

deletefromempwhereempno=eno;dbms_output.put_line("deleteisok");end;end;

如何調(diào)用exec程序包名稱.過程名稱(參數(shù));select程序包名稱.函數(shù)名稱(參數(shù))fromdual;⑶觸發(fā)器

關(guān)鍵字：trigger->扳機(jī)、目標(biāo)當(dāng)需要級聯(lián)操作的時(shí)候可能用到觸發(fā)器語法：createorreplacetrigger觸發(fā)器名稱

before|afterinsertorupdateordelete[of字段名稱]on表begin

PL/SQL語語句

案例：當(dāng)對Emp進(jìn)行CUD操作時(shí)候后經(jīng)行出發(fā)createorreplacetriggertri_empafterinsertorupdateordeleteonempbegin

ifinsertingthen

dbms_output.put_line("addisok");elsifupdatingthen

dbms_output.put_line("updateisok");else

dbms_output.put_line("deleteisok");endif;end;

觸發(fā)器的分類：分為：表級觸發(fā)

行及觸發(fā)foreachrow

例子：更新Emp表中部門為10的員工工資表級觸發(fā)

createorreplacetriggertri_demo1afterupdateonempbegin

ifupdatingthen

dbms_output.put_line("更新isok");endif;end;行級觸發(fā)

createorreplacetriggertri_demo1afterupdateonempforeachrowbegin

ifupdatingthen

dbms_output.put_line(:old.empno||"更新isok");endif;end;

在觸發(fā)器中:old代表原數(shù)據(jù)對象(一行對象),:new代表新的數(shù)據(jù)對象

案例：刪除Emp中指定數(shù)據(jù)的時(shí)候，將刪除的數(shù)據(jù)自動(dòng)備份到另外一張表中。

步驟1：創(chuàng)建一張備份表createtableemp_bak2asselect*fromempwhere1=2;步驟2：編寫觸發(fā)器自動(dòng)完成數(shù)據(jù)備份功能createorreplacetriggertri_demo2beforedeleteonempforeachrowbegin

insertintoemp_bak2

values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);

dbms_output.put_line("數(shù)據(jù)備份成功");end;

9、Oracle數(shù)據(jù)庫中的備份與還原10、Oracle數(shù)據(jù)庫優(yōu)化

友情提示：本文中關(guān)于《Oracle相關(guān)總結(jié)》給出的范例僅供您參考拓展思維使用，Oracle相關(guān)總結(jié)：該篇文章建議您自主創(chuàng)作。

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

《Oracle相關(guān)總結(jié)》由互聯(lián)網(wǎng)用戶整理提供,轉(zhuǎn)載分享請保留原作者信息,謝謝!
鏈接地址：http://www.hmlawpc.com/gongwen/671832.html

• 上一篇：大二學(xué)生個(gè)人總結(jié)
• 下一篇：大二的學(xué)習(xí)生活總結(jié)