Oracleg培訓(xùn)學(xué)習(xí)實(shí)用教案

1、培訓(xùn)(pixn)內(nèi)容 Oracle概述 Oracle術(shù)語 Oracle基本操作 Oracle中Join用法 Oracle 異常處理(chl) Oracle Update操作 Oracle SQL優(yōu)化第1頁/共31頁第一頁，共32頁。Oracle概述(i sh)1.1 什么是ORACLE ORACLE，即甲骨文股份有限公司，成立于1977年，總部位于美國加州 Redwood shore，是全球最大的信息管理軟件及服務(wù)供應(yīng)商，也是最早進(jìn)入中國的跨國軟件巨頭。甲骨文公司主要的產(chǎn)品目前分為以下幾大類：* 服務(wù)器（服務(wù)器）及工具（主要競爭對手：國際商用機(jī)器、微軟）* 數(shù)據(jù)庫服務(wù)器：最新版本11G* 應(yīng)用

2、服務(wù)器： Oracle Application Server* 開發(fā)工具：Oracle JDeveloper，Oracle Designer，Oracle Developer，等* 企業(yè)(qy)資源計(jì)劃(ERP)軟件。已有10年以上的歷史。2005年，并購了開發(fā)企業(yè)(qy)軟件的仁科軟件公司（PeopleSoft）以增強(qiáng)在這方面的競爭力。* 客戶關(guān)系管理(CRM)軟件。自1998年開始研發(fā)這種軟件。2005年，并購了開發(fā)客戶關(guān)系管理軟件的希柏軟件公司（Siebel）。第2頁/共31頁第二頁，共32頁。Oracle概述(i sh)1.2 Oracle數(shù)據(jù)庫Oracle數(shù)據(jù)庫的邏輯體系包括服務(wù)器和

3、客戶端：Oracle Server是一個(gè)對象一關(guān)系數(shù)據(jù)庫管理系統(tǒng)。它提供開放的、全面的、和集成的信息管理方法。每個(gè)Server由一個(gè) Oracle DB和一個(gè) Oracle Server實(shí)例組成。每個(gè) Oracle數(shù)據(jù)庫對應(yīng)唯一的一個(gè)實(shí)例名SID，Oracle數(shù)據(jù)庫服務(wù)器啟動后，一般至少有以下幾個(gè)用戶：Internal，它不是一個(gè)真實(shí)的用戶名，而是具有(jyu)SYSDBA優(yōu)先級的Sys用戶的別名，它由DBA用戶使用來完成數(shù)據(jù)庫的管理任務(wù)，包括啟動和關(guān)閉數(shù)據(jù)庫；Sys，它是一個(gè) DBA用戶名，具有(jyu)最大的數(shù)據(jù)庫操作權(quán)限；System，它也是一個(gè) DBA用戶名，權(quán)限僅次于 Sys用戶。O

4、racle客戶端即為數(shù)據(jù)庫用戶操作端，由應(yīng)用、工具、SQL* NET組成，用戶操作數(shù)據(jù)庫時(shí)，必須連接到一服務(wù)器，該數(shù)據(jù)庫稱為本地?cái)?shù)據(jù)庫（Local DB）。在網(wǎng)絡(luò)環(huán)境下其它服務(wù)器上的 DB稱為遠(yuǎn)程數(shù)據(jù)庫（Remote DB）。用戶要存取遠(yuǎn)程 DB上的數(shù)據(jù)時(shí)，必須建立數(shù)據(jù)庫鏈。Oracle數(shù)據(jù)庫的存儲結(jié)構(gòu)：它由至少一個(gè)表空間和數(shù)據(jù)庫模式對象組成。這里，模式是對象的集合，而模式對象是直接引用數(shù)據(jù)庫數(shù)據(jù)的邏輯結(jié)構(gòu)。模式對象包括這樣一些結(jié)構(gòu)：表、視圖、序列、存儲過程、同一詞、索引、簇和數(shù)據(jù)庫鏈等。邏輯存儲結(jié)構(gòu)包括表空間、段和范圍，用于描述怎樣使用數(shù)據(jù)庫的物理空間。而其中的模式對象和關(guān)系形成了數(shù)據(jù)庫的關(guān)

5、系設(shè)計(jì)。數(shù)據(jù)塊（Block）：是數(shù)據(jù)庫進(jìn)行UO操作的最小單位，它與操作系統(tǒng)的塊不是一個(gè)概念。oracle數(shù)據(jù)庫不是以操作系統(tǒng)的塊為單位來請求數(shù)據(jù)，而是以多個(gè)Oracle數(shù)據(jù)庫塊為單位。段（Segment）：是表空間中一個(gè)指定類型的邏輯存儲結(jié)構(gòu)，它由一個(gè)或多個(gè)范圍組成，段將占用并增長存儲空間。其中包括：數(shù)據(jù)段：用來存放表數(shù)據(jù)；索引段：用來存放表索引；臨時(shí)段：用來存放中間結(jié)果；回滾段：用于出現(xiàn)異常時(shí)，恢復(fù)事務(wù)。范圍（Extent）：是數(shù)據(jù)庫存儲空間分配的邏輯單位，一個(gè)范圍由許多連續(xù)的數(shù)據(jù)塊組成，范圍是由段依此分配的，分配的第一個(gè)范圍稱為初始范圍，以后分配的范圍稱為增量范圍。第3頁/共31頁第三頁，

6、共32頁。Oracle術(shù)語(shy) 2.1 數(shù)據(jù)庫名 數(shù)據(jù)庫名就是一個(gè)數(shù)據(jù)庫的標(biāo)識，就像人的身份證號一樣。他用參數(shù)DB_NAME表示，如果一臺機(jī)器上裝了多全數(shù)據(jù)庫，那么每一個(gè)數(shù)據(jù)庫都有一個(gè)數(shù)據(jù)庫名。在數(shù)據(jù)庫安裝或創(chuàng)建完成之后，參數(shù)DB_NAME被寫入?yún)?shù)文件之中。格式如下： DB_NAME=myorcl . 在創(chuàng)建數(shù)據(jù)庫時(shí)就應(yīng)考慮好數(shù)據(jù)庫名，并且在創(chuàng)建完數(shù)據(jù)庫之后，數(shù)據(jù)庫名不宜修改，即使要修改也會很麻煩。因?yàn)?yn wi)，數(shù)據(jù)庫名還被寫入控制文件中，控制文件是以二進(jìn)制型式存儲的，用戶無法修改控制文件的內(nèi)容。假設(shè)用戶修改了參數(shù)文件中的數(shù)據(jù)庫名，即修改DB_NAME的值。但是在Oracle啟動

7、時(shí)，由于參數(shù)文件中的DB_NAME與控制文件中的數(shù)據(jù)庫名不一致，導(dǎo)致數(shù)據(jù)庫啟動失敗，將返回ORA-01103錯(cuò)誤。第4頁/共31頁第四頁，共32頁。Oracle術(shù)語(shy) 數(shù)據(jù)庫實(shí)例名與Oracle_SID 一個(gè)數(shù)據(jù)庫實(shí)例（Instance） 是由SGA，后臺進(jìn)程以及數(shù)據(jù)文件組成，每個(gè)數(shù)據(jù)庫有自己的SGA和獨(dú)立的Oracle進(jìn)程集。 ORACLE_SID是操作系統(tǒng)的環(huán)境變量。 兩者都是oracle實(shí)例，但兩者是有區(qū)別的。instance_name是oracle數(shù)據(jù)庫參數(shù)，而ORACLD_SID用于與操作系統(tǒng)交互，也就是說，從操作系統(tǒng)的角度訪問實(shí)例名，必須通過ORACLE_SID。數(shù)據(jù)庫實(shí)例

8、名除了與操作系統(tǒng)交互外，還用于網(wǎng)絡(luò)連接的oracle服務(wù)器標(biāo)識。當(dāng)你配置oracle主機(jī)連接串的時(shí)候，就需要指定0實(shí)例名。在winnt平臺， ORACLE_SID還需存在于注冊表中。 在數(shù)據(jù)庫安裝之后，ORACLE_SID被用于定義(dngy)數(shù)據(jù)庫參數(shù)文件的名稱。如：$ORACLE_BASE/admin/DB_NAME/pfile/init$ORACLE_SID.ora。第5頁/共31頁第五頁，共32頁。Oracle術(shù)語(shy) 2.3 表空間 一個(gè)Oracle數(shù)據(jù)庫由一個(gè)或多個(gè)邏輯存儲單元表空間構(gòu)成，表空間存儲著數(shù)據(jù)庫中所有的數(shù)據(jù)； Oracle數(shù)據(jù)庫中的每個(gè)表空間由一個(gè)或多個(gè)文件組成，

9、這些文件就是(jish)數(shù)據(jù)文件，它們是Oracle所在操作系統(tǒng)上的物理結(jié)構(gòu)。Oracle數(shù)據(jù)庫中的所有數(shù)據(jù)物理的存儲在數(shù)據(jù)文件中。 除了普通表空間外，Oracle還有幾種特殊的表空間。系統(tǒng)表空間（SYSTEM表空間）、UNDO表空間（回滾表空間）、臨時(shí)表空間(TEMP表空間)。 系統(tǒng)表空間中除了保存數(shù)據(jù)字典外還保存所有的存儲過程、函數(shù)、包、觸發(fā)器和對象。 UNDO表空間用于保存被DML語句影響的記錄的原始狀態(tài)，以便在事務(wù)失敗的時(shí)候可以進(jìn)行回滾。 USER表空間 TOOLS表空間 當(dāng)大的排序操作發(fā)生時(shí)，排序無法在內(nèi)存中完成，這時(shí)會使用臨時(shí)表空間。 表空間可以設(shè)置為ONLINE和OFFLINE，

10、OFFLINE的表空間無法進(jìn)行訪問。通過對單獨(dú)表空間的ONLINE和OFFLINE，可以減少對系統(tǒng)的影響。 表空間可以設(shè)置為READ ONLY和READ WRITE兩種狀態(tài)，只讀的表空間不可以進(jìn)行鎖操作，因此甚至可以放到只讀設(shè)備上如CDROM。第6頁/共31頁第六頁，共32頁。Oracle術(shù)語(shy) 同義詞 同義詞用于屏蔽基表的身份，降低sql語句的復(fù)雜性，用戶不必知道同義詞所代表的表是哪一個(gè)表，從而用戶不需要指明(zhmng)表的所有者的名稱。 同義詞有私有和公有之分。私有同義詞是普通用戶所創(chuàng)建的同義詞，只有創(chuàng)建該同義詞的用戶才能使用此同義詞，而公用同義詞是指數(shù)據(jù)庫管理員創(chuàng)建的，公用同義

11、詞允許所有用戶使用。第7頁/共31頁第七頁，共32頁。Oracle術(shù)語(shy) 序列 建序列號主要用來自動產(chǎn)生數(shù)據(jù)，如學(xué)生號自動加1，如果有很多的學(xué)生，比如10000個(gè)，那么使用序列較手工實(shí)現(xiàn)會提高效率。 創(chuàng)建語法： create sequence name increment by x /x為增長間隔 start with x /x為初始值 maxvalue x /x為最大值 minvalue x /x為最小值 cycle /循環(huán)使用，到達(dá)最大值或者最小值時(shí)，重新建立對象 cache x /制定(zhdng)緩存序列值的個(gè)數(shù) 創(chuàng)建create sequence s increment by

12、 10 start with 1 nomaxvalue ; 刪除drop sequence s; 使用insert into a values(s.nextval,xxx);第8頁/共31頁第八頁，共32頁。Oracle基本操作創(chuàng)建(chungjin)表空間 如何創(chuàng)建(chungjin)表空間創(chuàng)建(chungjin)表空間可以使用SQL語句，也可以通過DBA Studio。使用SYSTEM帳戶 drop tablespace test including contents; /刪除表空間create tablespace test logging / datafile d:test.dbf s

13、ize 100M reuse autoextend on next 10M maxsize unlimited ;create temporary tablespace temptest tempfile d:temptest.dbf size 10M reuse;第9頁/共31頁第九頁，共32頁。Oracle基本操作連接ORACLE 如何配置(pizh)ORACLE客戶端 如果Oracle服務(wù)器在另外一臺主機(jī)，你要連接到數(shù)據(jù)庫服務(wù)器。那么需要做的事情就是：在自己的機(jī)器安裝ORACLE客戶端。配置(pizh)ORACLE連接：演示選擇”Network Administration”-”Net8

14、 Configuration Assistant”進(jìn)行配置(pizh)第10頁/共31頁第十頁，共32頁。Oracle基本操作 常用的一些語句 1.select * from tab ; 查看(chkn)當(dāng)前表和視圖 2.select name from v$database; 查看(chkn)數(shù)據(jù)庫 3.select * from nls_database_parameters; 查看(chkn)數(shù)據(jù)庫參數(shù)設(shè)置 4.select * from user_role_privs; 查看(chkn)當(dāng)前用戶的角色 5.select username,default_tablespace,tempo

15、rary_tablespace from user_users; 查看(chkn)用戶名，默認(rèn)/臨時(shí)表空間 6. select view_name from user_views; 查看(chkn)視圖 7.select table_name from user_tables; 查看(chkn)表（和第1的區(qū)別） 8.select * from user_constraints; 查看(chkn)表的約束情況 9. select * from user_catalog 查看(chkn)表，視圖，同義詞和當(dāng)前所有序列的簡明目錄第11頁/共31頁第十一頁，共32頁。Oracle中Join用法(yn

16、 f) 內(nèi)連接 左連接 右連接 完全連接 迪卡爾 1.內(nèi)連接：選出兩個(gè)表中所有符合連接條件的row構(gòu)成的集合。 2.外連接，分為LEFT、RIGHT、FULL三種 如有R與S做連接，那么 LEFT是所有滿足條件的row以及所有在R屬性對應(yīng)值在S中為NULL的集合。 RIGHT是所有滿足條件的row以及所有在S屬性對應(yīng)值在R中為NULL的集合 FULL是所有滿足條件的row以及任何(rnh)在R或S中出現(xiàn)NULL的集合第12頁/共31頁第十二頁，共32頁。Oracle中Join用法(yn f) inner join(等值連接 只返回兩個(gè)表中聯(lián)結(jié)(linji)字段相等的行 1.select * f

17、rom Table1 a,Table2 b where a.id=b.id 2. Select * from table 1 A INNER JOIN table2 B ON A.ID=B.ID第13頁/共31頁第十三頁，共32頁。Oracle中Join用法(yn f) left join(左聯(lián)接) 返回包括左表中的所有記錄和右表中聯(lián)結(jié)(linji)字段相等的記錄 select * from Table1 a, table2 b where a.id=b.id(+); SELECT * FROM table1 A LEFT JOIN table2 B ON A.ID=B.ID第14頁/共31頁

18、第十四頁，共32頁。Oracle中Join用法(yn f) right join(右聯(lián)接) 返回包括右表中的所有(suyu)記錄和左表中聯(lián)結(jié)字段相等的記錄 select * from table1 a, table2 b where a.id(+)=b.id; SELECT * FROM table1 A RIGHT JOIN table2 B ON A.ID=B.ID第15頁/共31頁第十五頁，共32頁。Oracle中Join用法(yn f) 完全連接 返回(fnhu)包括右表中的所有記錄和左表中的所有記錄 select * from table1 a, table2 b where a.i

19、d=b.id(+) union select * from Table1 a, table2 b where a.id(+)=b.id; SELECT * FROM table1 A FULL OUTER JOIN table2 B ON A.ID=B.ID第16頁/共31頁第十六頁，共32頁。Oracle中Join用法(yn f) 迪卡爾 返回兩個(gè)(lin )表相等字段的乘積 select * from table1, table2;第17頁/共31頁第十七頁，共32頁。Oracle 異常(ychng)處理1、 異常的優(yōu)點(diǎn) 如果沒有異常，在程序中，應(yīng)當(dāng)檢查每個(gè)命令(mng lng)的成功還是

20、失敗。2、異常的分類 有兩種類型的異常，一種為內(nèi)部異常，一種為用戶自定義異常。3、異常的拋出 由三種方式拋出異常 1 通過PL/SQL運(yùn)行時(shí)引擎 2. 使用RAISE語句 (RAISE exception_name; )3. 調(diào)用RAISE_APPLICATION_ERROR存儲過程.定義DECLARE inventory_too_low EXCEPTION;第18頁/共31頁第十八頁，共32頁。Oracle 異常(ychng)處理 4、異常的處理 PL/SQL程序塊的異常部分包含了程序處理錯(cuò)誤的代碼，當(dāng)異常被拋出時(shí)，一個(gè)異常陷阱就自動(zdng)發(fā)生，程序控制離開執(zhí)行部分轉(zhuǎn)入異常部分,一旦程

21、序進(jìn)入異常部分就不能再回到同一塊的執(zhí)行部分。下面是異常部分的一般語法： EXCEPTION WHEN exception_name THEN Code for handing exception_name WHEN another_exception THEN Code for handing another_exception WHEN others THEN code for handing any other exception.第19頁/共31頁第十九頁，共32頁。Oracle 異常(ychng)處理 例子(l zi)： declare exec_sql_err exception;

22、var_msg varchar(255); Begin update table1 set name=test where id=1; if sql%notfound then raise exec_sql_err; end if; Exception when others then when exec_sql_err then var_msg :=sqlcode|sqlerrm; dbms_output.put_line(var_msg ); End;第20頁/共31頁第二十頁，共32頁。Oracle Update用法(yn f) 關(guān)于ORACLE的UPDATE更新(gngxn)多表的問題

23、 一種是： update table1 set (field1,field2.) = (Select Field1,field2. from table2 where table1.field1=table2.field1) where table1.field1 in (select field1 from table2)第21頁/共31頁第二十一頁，共32頁。Oracle Update用法(yn f) 二種是： 將table1,table2相關(guān)聯(lián)字段建立(jinl)主鍵Primary key 或Union key Update (Select Table1.field1,table1.fi

24、eld2,table2.field1,table2.field2 from table1,table2 where Table1.Field1=table2.field1) Set table1.table2=table2.table2, table1.fieldn=table2.tablen, . table1的Field1和table2的field1將需要建立(jinl)主鍵或唯一索引才行第22頁/共31頁第二十二頁，共32頁。Oracle Update用法(yn f) 三種是： Oracle9i引入了MERGE命令,你能夠在一個(gè)SQL語句中對一個(gè)表同時(shí)執(zhí)行inserts和updates操

25、作. MERGE命令從一個(gè)或多個(gè)數(shù)據(jù)源中選擇行來updating或inserting到一個(gè)或多個(gè)表. Oracle 10g中MERGE有如下一些改進(jìn)： 1、UPDATE或INSERT子句是可選的 2、UPDATE和INSERT子句可以加WHERE子句 3、ON條件中使用常量過濾謂詞(wi c)來insert所有的行到目標(biāo)表中,不需要連接源表和目標(biāo)表 merge into table1 using table2 on (table1.field1=table2.field1 and .) when matched then Update set table1.field2=table2.fiel

26、d2, table1.fieldn=table2.fieldn, . when not matched then doing other thing 其余方法可使用游標(biāo);第23頁/共31頁第二十三頁，共32頁。Oracle SQL優(yōu)化1、選擇最有效率的表名順序例如: 表 TAB1 16,384 條記錄 表 TAB2 1 條記錄 選擇TAB2作為基礎(chǔ)表 (最好的方法) select count(*) from tab1,tab2 執(zhí)行時(shí)間0.96秒 選擇TAB2作為基礎(chǔ)表 (不佳的方法) select count(*) from tab2,tab1 執(zhí)行時(shí)間26.09秒如果有3個(gè)以上的表連接查詢

27、, 那就需要(xyo)選擇交叉表(intersection table)作為基礎(chǔ)表, 交叉表是指那個(gè)被其他表所引用的表第24頁/共31頁第二十四頁，共32頁。Oracle SQL優(yōu)化2、WHERE子句中的連接順序 ORACLE采用自下而上的順序解析WHERE子句,根據(jù)這個(gè)原理,表之間的連接必須寫在其他WHERE條件之前, 那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾. 例如: (低效,執(zhí)行(zhxng)時(shí)間156.3秒) SELECT FROM EMP E WHERE SAL 50000 AND JOB = MANAGER AND 25 (SELECT COUNT(*) FRO

28、M EMP WHERE MGR=E.EMPNO); (高效,執(zhí)行(zhxng)時(shí)間10.6秒) SELECT FROM EMP E WHERE 25 50000AND JOB = MANAGER; 第25頁/共31頁第二十五頁，共32頁。Oracle SQL優(yōu)化3、SELECT子句中避免使用 * 當(dāng)你想在SELECT子句中列出所有的COLUMN時(shí),使用動態(tài)SQL列引用 * 是一個(gè)方便的方法.不幸的是,這是一個(gè)非常低效的方法. 實(shí)際上,ORACLE在解析的過程中, 會將* 依次轉(zhuǎn)換成所有的列名, 這個(gè)工作是通過查詢數(shù)據(jù)字典完成的, 這意味著將耗費(fèi)(hofi)更多的時(shí)間. 4、減少訪問數(shù)據(jù)庫的次數(shù)

29、當(dāng)執(zhí)行每條SQL語句時(shí), ORACLE在內(nèi)部執(zhí)行了許多工作: 解析SQL語句, 估算索引的利用率, 綁定變量 , 讀數(shù)據(jù)塊等等. 由此可見, 減少訪問數(shù)據(jù)庫的次數(shù) , 就能實(shí)際上減少ORACLE的工作量. 例如, 以下有三種方法可以檢索出雇員號等于0342或0291的職員. 第26頁/共31頁第二十六頁，共32頁。Oracle SQL優(yōu)化方法1 (最低效) SELECT ENAME , SAL FROM EMP WHERE EMPNO = 342; SELECT ENAME , SAL FROM EMP WHERE EMPNO = 291; 方法2 (高效) SELECT A.ENAME , A.SAL, B.ENAME , B.SAL FROM EMP A,EMP B WHERE A.EMPNO = 342 AND B.EMPNO = 291;5.盡量多使用COMMIT 只要(zhyo)有可能,在程序中盡量多使用COMMIT, 這樣程序的性能得到提高,