Oracleg培訓(xùn)學(xué)習(xí)PPT課件

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

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

3、的、和集成的信息管理方法。每個Server由一個 Oracle DB和一個 Oracle Server實例組成。每個 Oracle數(shù)據(jù)庫對應(yīng)唯一的一個實例名SID，Oracle數(shù)據(jù)庫服務(wù)器啟動后，一般至少有以下幾個用戶：Internal，它不是一個真實的用戶名，而是具有SYSDBA優(yōu)先級的Sys用戶的別名，它由DBA用戶使用來完成數(shù)據(jù)庫的管理任務(wù)，包括啟動和關(guān)閉數(shù)據(jù)庫；Sys，它是一個 DBA用戶名，具有最大的數(shù)據(jù)庫操作權(quán)限；System，它也是一個 DBA用戶名，權(quán)限僅次于 Sys用戶。Oracle客戶端即為數(shù)據(jù)庫用戶操作端，由應(yīng)用、工具、SQL* NET組成，用戶操作數(shù)據(jù)庫時，必須連接到一

4、服務(wù)器，該數(shù)據(jù)庫稱為本地數(shù)據(jù)庫（Local DB）。在網(wǎng)絡(luò)環(huán)境下其它服務(wù)器上的 DB稱為遠(yuǎn)程數(shù)據(jù)庫（Remote DB）。用戶要存取遠(yuǎn)程 DB上的數(shù)據(jù)時，必須建立數(shù)據(jù)庫鏈。Oracle數(shù)據(jù)庫的存儲結(jié)構(gòu)：它由至少一個表空間和數(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)系設(shè)計。數(shù)據(jù)塊（Block）：是數(shù)據(jù)庫進(jìn)行UO操作的最小單位，它與操作系統(tǒng)的塊不是一個概念。oracle

5、數(shù)據(jù)庫不是以操作系統(tǒng)的塊為單位來請求數(shù)據(jù)，而是以多個Oracle數(shù)據(jù)庫塊為單位。段（Segment）：是表空間中一個指定類型的邏輯存儲結(jié)構(gòu)，它由一個或多個范圍組成，段將占用并增長存儲空間。其中包括：數(shù)據(jù)段：用來存放表數(shù)據(jù)；索引段：用來存放表索引；臨時段：用來存放中間結(jié)果；回滾段：用于出現(xiàn)異常時，恢復(fù)事務(wù)。范圍（Extent）：是數(shù)據(jù)庫存儲空間分配的邏輯單位，一個范圍由許多連續(xù)的數(shù)據(jù)塊組成，范圍是由段依此分配的，分配的第一個范圍稱為初始范圍，以后分配的范圍稱為增量范圍。第3頁/共31頁Oracle術(shù)語 2.1 數(shù)據(jù)庫名 數(shù)據(jù)庫名就是一個數(shù)據(jù)庫的標(biāo)識，就像人的身份證號一樣。他用參數(shù)DB_NAME表

6、示，如果一臺機器上裝了多全數(shù)據(jù)庫，那么每一個數(shù)據(jù)庫都有一個數(shù)據(jù)庫名。在數(shù)據(jù)庫安裝或創(chuàng)建完成之后，參數(shù)DB_NAME被寫入?yún)?shù)文件之中。格式如下： DB_NAME=myorcl . 在創(chuàng)建數(shù)據(jù)庫時就應(yīng)考慮好數(shù)據(jù)庫名，并且在創(chuàng)建完數(shù)據(jù)庫之后，數(shù)據(jù)庫名不宜修改，即使要修改也會很麻煩。因為，數(shù)據(jù)庫名還被寫入控制文件中，控制文件是以二進(jìn)制型式存儲的，用戶無法修改控制文件的內(nèi)容。假設(shè)用戶修改了參數(shù)文件中的數(shù)據(jù)庫名，即修改DB_NAME的值。但是在Oracle啟動時，由于參數(shù)文件中的DB_NAME與控制文件中的數(shù)據(jù)庫名不一致，導(dǎo)致數(shù)據(jù)庫啟動失敗，將返回ORA-01103錯誤。第4頁/共31頁Oracle術(shù)語

7、 數(shù)據(jù)庫實例名與Oracle_SID 一個數(shù)據(jù)庫實例（Instance） 是由SGA，后臺進(jìn)程以及數(shù)據(jù)文件組成，每個數(shù)據(jù)庫有自己的SGA和獨立的Oracle進(jìn)程集。 ORACLE_SID是操作系統(tǒng)的環(huán)境變量。 兩者都是oracle實例，但兩者是有區(qū)別的。instance_name是oracle數(shù)據(jù)庫參數(shù)，而ORACLD_SID用于與操作系統(tǒng)交互，也就是說，從操作系統(tǒng)的角度訪問實例名，必須通過ORACLE_SID。數(shù)據(jù)庫實例名除了與操作系統(tǒng)交互外，還用于網(wǎng)絡(luò)連接的oracle服務(wù)器標(biāo)識。當(dāng)你配置oracle主機連接串的時候，就需要指定0實例名。在winnt平臺， ORACLE_SID還需存在于注

8、冊表中。 在數(shù)據(jù)庫安裝之后，ORACLE_SID被用于定義數(shù)據(jù)庫參數(shù)文件的名稱。如：$ORACLE_BASE/admin/DB_NAME/pfile/init$ORACLE_SID.ora。第5頁/共31頁Oracle術(shù)語 2.3 表空間 一個Oracle數(shù)據(jù)庫由一個或多個邏輯存儲單元表空間構(gòu)成，表空間存儲著數(shù)據(jù)庫中所有的數(shù)據(jù)； Oracle數(shù)據(jù)庫中的每個表空間由一個或多個文件組成，這些文件就是數(shù)據(jù)文件，它們是Oracle所在操作系統(tǒng)上的物理結(jié)構(gòu)。Oracle數(shù)據(jù)庫中的所有數(shù)據(jù)物理的存儲在數(shù)據(jù)文件中。 除了普通表空間外，Oracle還有幾種特殊的表空間。系統(tǒng)表空間（SYSTEM表空間）、UND

9、O表空間（回滾表空間）、臨時表空間(TEMP表空間)。 系統(tǒng)表空間中除了保存數(shù)據(jù)字典外還保存所有的存儲過程、函數(shù)、包、觸發(fā)器和對象。 UNDO表空間用于保存被DML語句影響的記錄的原始狀態(tài)，以便在事務(wù)失敗的時候可以進(jìn)行回滾。 USER表空間 TOOLS表空間 當(dāng)大的排序操作發(fā)生時，排序無法在內(nèi)存中完成，這時會使用臨時表空間。 表空間可以設(shè)置為ONLINE和OFFLINE，OFFLINE的表空間無法進(jìn)行訪問。通過對單獨表空間的ONLINE和OFFLINE，可以減少對系統(tǒng)的影響。 表空間可以設(shè)置為READ ONLY和READ WRITE兩種狀態(tài)，只讀的表空間不可以進(jìn)行鎖操作，因此甚至可以放到只讀設(shè)

10、備上如CDROM。第6頁/共31頁Oracle術(shù)語 同義詞 同義詞用于屏蔽基表的身份，降低sql語句的復(fù)雜性，用戶不必知道同義詞所代表的表是哪一個表，從而用戶不需要指明表的所有者的名稱。 同義詞有私有和公有之分。私有同義詞是普通用戶所創(chuàng)建的同義詞，只有創(chuàng)建該同義詞的用戶才能使用此同義詞，而公用同義詞是指數(shù)據(jù)庫管理員創(chuàng)建的，公用同義詞允許所有用戶使用。第7頁/共31頁Oracle術(shù)語 序列 建序列號主要用來自動產(chǎn)生數(shù)據(jù)，如學(xué)生號自動加1，如果有很多的學(xué)生，比如10000個，那么使用序列較手工實現(xiàn)會提高效率。 創(chuàng)建語法： create sequence name increment by x /x

11、為增長間隔 start with x /x為初始值 maxvalue x /x為最大值 minvalue x /x為最小值 cycle /循環(huán)使用，到達(dá)最大值或者最小值時，重新建立對象 cache x /制定緩存序列值的個數(shù) 創(chuàng)建create sequence s increment by 10 start with 1 nomaxvalue ; 刪除drop sequence s; 使用insert into a values(s.nextval,xxx);第8頁/共31頁Oracle基本操作創(chuàng)建表空間 如何創(chuàng)建表空間創(chuàng)建表空間可以使用SQL語句，也可以通過DBA Studio。使用SYST

12、EM帳戶 drop tablespace test including contents; /刪除表空間create tablespace test logging / datafile d:test.dbf size 100M reuse autoextend on next 10M maxsize unlimited ;create temporary tablespace temptest tempfile d:temptest.dbf size 10M reuse;第9頁/共31頁Oracle基本操作連接ORACLE 如何配置ORACLE客戶端 如果Oracle服務(wù)器在另外一臺主機，你

13、要連接到數(shù)據(jù)庫服務(wù)器。那么需要做的事情就是：在自己的機器安裝ORACLE客戶端。配置ORACLE連接：演示選擇”Network Administration”-”Net8 Configuration Assistant”進(jìn)行配置第10頁/共31頁Oracle基本操作 常用的一些語句 1.select * from tab ; 查看當(dāng)前表和視圖 2.select name from v$database; 查看數(shù)據(jù)庫 3.select * from nls_database_parameters; 查看數(shù)據(jù)庫參數(shù)設(shè)置 4.select * from user_role_privs; 查看當(dāng)前用戶

14、的角色 5.select username,default_tablespace,temporary_tablespace from user_users; 查看用戶名，默認(rèn)/臨時表空間 6. select view_name from user_views; 查看視圖 7.select table_name from user_tables; 查看表（和第1的區(qū)別） 8.select * from user_constraints; 查看表的約束情況 9. select * from user_catalog 查看表，視圖，同義詞和當(dāng)前所有序列的簡明目錄第11頁/共31頁Oracle中Joi

15、n用法 內(nèi)連接 左連接 右連接 完全連接 迪卡爾1.內(nèi)連接：選出兩個表中所有符合連接條件的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以及任何在R或S中出現(xiàn)NULL的集合第12頁/共31頁Oracle中Join用法 inner join(等值連接 只返回兩個表中聯(lián)結(jié)字段相等的行1.select * from Table1 a,Table2 b where a.id

16、=b.id2. Select * from table 1 A INNER JOIN table2 B ON A.ID=B.ID第13頁/共31頁Oracle中Join用法 left join(左聯(lián)接) 返回包括左表中的所有記錄和右表中聯(lián)結(jié)字段相等的記錄 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頁Oracle中Join用法 right join(右聯(lián)接) 返回包括右表中的所有記錄和左表中聯(lián)結(jié)字段相等的記錄

17、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頁Oracle中Join用法 完全連接 返回包括右表中的所有記錄和左表中的所有記錄 select * from table1 a, table2 b where a.id=b.id(+) union select * from Table1 a, table2 b where a.id(+)=b.id; SELECT * FROM table1 A FULL OUT

18、ER JOIN table2 B ON A.ID=B.ID第16頁/共31頁Oracle中Join用法 迪卡爾 返回兩個表相等字段的乘積select * from table1, table2;第17頁/共31頁Oracle 異常處理1、 異常的優(yōu)點 如果沒有異常，在程序中，應(yīng)當(dāng)檢查每個命令的成功還是失敗。2、異常的分類 有兩種類型的異常，一種為內(nèi)部異常，一種為用戶自定義異常。3、異常的拋出 由三種方式拋出異常 1 通過PL/SQL運行時引擎 2. 使用RAISE語句 (RAISE exception_name; )3. 調(diào)用RAISE_APPLICATION_ERROR存儲過程.定義DECL

19、ARE inventory_too_low EXCEPTION;第18頁/共31頁Oracle 異常處理 4、異常的處理 PL/SQL程序塊的異常部分包含了程序處理錯誤的代碼，當(dāng)異常被拋出時，一個異常陷阱就自動發(fā)生，程序控制離開執(zhí)行部分轉(zhuǎn)入異常部分,一旦程序進(jìn)入異常部分就不能再回到同一塊的執(zhí)行部分。下面是異常部分的一般語法： EXCEPTION WHEN exception_name THEN Code for handing exception_name WHEN another_exception THEN Code for handing another_exception WHEN o

20、thers THEN code for handing any other exception.第19頁/共31頁Oracle 異常處理 例子：declare exec_sql_err exception; 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

21、_output.put_line(var_msg );End;第20頁/共31頁Oracle Update用法 關(guān)于ORACLE的UPDATE更新多表的問題一種是： 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頁Oracle Update用法 二種是： 將table1,table2相關(guān)聯(lián)字段建立主鍵Primary

22、 key 或Union key Update (Select Table1.field1,table1.field2,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將需要建立主鍵或唯一索引才行第22頁/共31頁Oracle Update用法 三種是：Oracle9i引入了MERGE命令,你能夠在一個

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

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

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

26、.EMPNO); (高效,執(zhí)行時間10.6秒) SELECT FROM EMP E WHERE 25 50000AND JOB = MANAGER; 第25頁/共31頁Oracle SQL優(yōu)化3、SELECT子句中避免使用 * 當(dāng)你想在SELECT子句中列出所有的COLUMN時,使用動態(tài)SQL列引用 * 是一個方便的方法.不幸的是,這是一個非常低效的方法. 實際上,ORACLE在解析的過程中, 會將* 依次轉(zhuǎn)換成所有的列名, 這個工作是通過查詢數(shù)據(jù)字典完成的, 這意味著將耗費更多的時間. 4、減少訪問數(shù)據(jù)庫的次數(shù)當(dāng)執(zhí)行每條SQL語句時, ORACLE在內(nèi)部執(zhí)行了許多工作: 解析SQL語句, 估算索引的利用率, 綁定變量 , 讀數(shù)據(jù)塊等等. 由此可見, 減少訪問數(shù)據(jù)庫的次數(shù) , 就能實際上減少ORACLE的工作量. 例如, 以下有三種方法可以檢索出雇員號等于0342或0291的職員. 第26頁/共31頁Oracle SQL優(yōu)化方法1 (最低效) SELECT ENAME , SAL FROM EMP WHERE EMPNO = 342; SELECT ENAME , SAL FROM EMP WHERE EMPNO = 291; 方法2