ORACLE日常操作手冊

1、ORACLE日常操作手冊目錄一、數(shù)據(jù)庫的啟動和關(guān)閉.41. 數(shù)據(jù)庫的正常啟動步驟.42. 數(shù)據(jù)庫的正常關(guān)閉步驟.43. 幾種關(guān)閉數(shù)據(jù)庫方法對比.44. 數(shù)據(jù)庫的啟動關(guān)閉過程.4二、創(chuàng)建數(shù)據(jù)庫用戶.51、 以DBA用戶登錄數(shù)據(jù)庫（如system,sys）.52、 用create user語法創(chuàng)建用戶.53、 賦表空間使用權(quán)限.54、 給用戶賦權(quán)限.55、 刪除用戶.5三、ORACL常用的數(shù)據(jù)類型.5四、基本的SQL語句的寫法.61、 rowid和rownum的區(qū)別.6 刪除表中重復記錄.6 使表處于可編輯狀態(tài).6 批量刪除記錄.7 分頁查詢.72、 delete和truncate 、drop的區(qū)

2、別.73、 多表關(guān)聯(lián)查詢.7不等連接實例.7 查詢員工的工資等級.7內(nèi)連接實例.8 查詢詳細信息記錄.8 關(guān)聯(lián)更新和刪除.8外連接實例.8 查詢沒有附件信息記錄.8自連接實例.8 查詢員工和主管之間的關(guān)系.8 遞歸查詢.84、 子查詢.8單行子查詢實例.9 查詢內(nèi)容大小大于平均大小的記錄.9 在having子句中使用子查詢.9 在from子句中使用子查詢（內(nèi)聯(lián)視圖）.9 可能碰到的兩個錯誤.9多行子查詢實例.9 在多行查詢中使用in操作符.9 在多行子查詢中使用any操作符.10 在多行子查詢中使用all操作符.10多例子查詢實例.10 檢索每種產(chǎn)品類型中價格最低的產(chǎn)品.10關(guān)聯(lián)子查詢實例.1

3、0 在關(guān)聯(lián)子查詢中exists10 在關(guān)聯(lián)子查詢中not exists10 Exists和not exists與in和not in的比較.10嵌套子查詢實例.11 多層嵌套子查詢.115、 使用集合操作符.12 Union all使用實例.12 Union使用實例.12 Intersect使用實例.13 Minus使用實例.136、 Decode函數(shù)和Case表達式的比較.13 Decode函數(shù)使用實例.13 Case表達式使用實例.147、 其它.15五、日期和時間的存儲與處理.151、 常用的幾個日期函數(shù)說明.152、 常用的日期計算實例.15 取得當前日期是本月的第幾周.15 取得當前日

4、期是一個星期中的第幾天,注意星期日是第一天.15 取當前日期是星期幾中文顯示:16 如果一個表在一個date類型的字段上面建立了索引，如何使用.16 得到當前的日期.16 得到當天凌晨0點0分0秒的日期.16 得到這天的最后一秒.16 得到小時的具體數(shù)值.16 得到明天凌晨0點0分0秒的日期.17 本月一日的日期.17 得到下月一日的日期.17 返回當前月的最后一天.17 得到一年的每一天.17 如何在給現(xiàn)有的日期加上2年.18 判斷某一日子所在年分是否為潤年.18 判斷兩年后是否為潤年.18 得到日期的季度.18六、SQL語句的優(yōu)化寫法.181、 oracle訪問Table的方式.182、

5、創(chuàng)建索引.19 創(chuàng)建普通索引實例.19 創(chuàng)建全文索引實例.19 創(chuàng)建主建.203、 SQL優(yōu)化實例及問題.20 使用like操作符的問題.20 選擇最有效率的表名順序(只在基于規(guī)則的優(yōu)化器中有效)20 WHERE子句中的連接順序.21 SELECT子句中避免使用*21 減少訪問數(shù)據(jù)庫的次數(shù).21 盡量多使用COMMIT.22 減少對表的查詢.22 通過內(nèi)部函數(shù)提高SQL效率.23 使用表的別名(Alias)24 用EXISTS替代IN和用NOT EXISTS替代NOT IN.24 用表連接替換EXISTS.24 用EXISTS替換DISTINCT.24 等式比較和范圍比較.25 不明確的索引等

6、級.25 強制索引失效.26 避免在索引列上使用計算.26 自動選擇索引.27 避免在索引列上使用NOT.27 避免在索引列上使用IS NULL和IS NOT NULL.28 總是使用索引的第一個列.28七、常見的數(shù)據(jù)庫管理和優(yōu)化配置.291、 數(shù)據(jù)庫的備份.29 導出/導入(Export/Import)29 rman備份實例.302、 數(shù)據(jù)庫的參數(shù)配置及性能調(diào)整.31 如何增加ORACLE連接數(shù).31 關(guān)于內(nèi)存參數(shù)的調(diào)整.31 32bit 和 64bit 的問題.32 Linux上shmmax參數(shù)的設(shè)置及含義.32 解決CPU高度消耗(100%)的數(shù)據(jù)庫問.333、 存儲管理.35 創(chuàng)建表空

7、間.36 管理表空間.36 管理數(shù)據(jù)文件.36 查看表空間的使用情況.37本文檔約定：1、 文中的數(shù)據(jù)庫主要用到了公司cms、pms庫結(jié)構(gòu)2、 所有SQL都實際的測試通過，放心使用。3、 如果有再需要了解的部分以后可以再做補充。一、 數(shù)據(jù)庫的啟動和關(guān)閉1. 數(shù)據(jù)庫的正常啟動步驟l 以DBA的身份登錄數(shù)據(jù)庫（要在oracle安裝用戶下執(zhí)行sqlplus）oracleDB1 $sqlplus “/as sysdba”l 執(zhí)行啟動數(shù)據(jù)庫命令SQLstartupORACLE instance started.Total System Global Area 285212672 bytesFixed S

8、ize 1218968 bytesVariable Size 88082024 bytesDatabase Buffers 188743680 bytesRedo Buffers 7168000 bytesDatabase mounted.Database opened.l 啟動和關(guān)閉監(jiān)聽oracleDB1 $lsnrctl startoracleDB1 $lsnrctl stop2. 數(shù)據(jù)庫的正常關(guān)閉步驟l 同樣以DBA的身份登錄數(shù)據(jù)庫oracleDB1 $sqlplus “/as sysdba”l 執(zhí)行數(shù)據(jù)庫關(guān)閉命令SQLshutdown immediate;Database closed

9、.Database dismounted.ORACLE instance shut down.3. 幾種關(guān)閉數(shù)據(jù)庫方法對比SHUTDOWN有四個參數(shù)：NORMAL、TRANSACTIONAL、IMMEDIATE、ABORT。缺省不帶任何參數(shù)時表示是NORMAL。SHUTDOWN NORMAL：不允許新的連接、等待會話結(jié)束、等待事務(wù)結(jié)束、做一個檢查點并關(guān)閉數(shù)據(jù)文件。啟動時不需要實例恢復，這種方法往往不能關(guān)閉數(shù)據(jù)庫或等待很長時間。SHUTDOWN TRANSACTIONAL：不允許新的連接、不等待會話結(jié)束、等待事務(wù)結(jié)束、做一個檢查點并關(guān)閉數(shù)據(jù)文件。啟動時不需要實例恢復。SHUTDOWN IMMED

10、IATE：不允許新的連接、不等待會話結(jié)束、不等待事務(wù)結(jié)束、做一個檢查點并關(guān)閉數(shù)據(jù)文件。沒有結(jié)束的事務(wù)是自動rollback的。啟動時不需要實例恢復。最常用的方法。SHUTDOWN ABORT：不允許新的連接、不等待會話結(jié)束、不等待事務(wù)結(jié)束、不做檢查點且沒有關(guān)閉數(shù)據(jù)文件。啟動時自動進行實例恢復。一般不推薦采用，只有在數(shù)據(jù)庫無法關(guān)閉時使用，可能造成數(shù)據(jù)庫的不一致。4. 數(shù)據(jù)庫的啟動關(guān)閉過程二、 創(chuàng)建數(shù)據(jù)庫用戶1、 以DBA用戶登錄數(shù)據(jù)庫（如system,sys）SQLconn system/oracleorcl注：如果在本地服務(wù)器登錄orcl可以去掉2、 用create user語法創(chuàng)建用戶CRE

11、ATE USER user_name IDENTIFIED BY user_passwordDefaultTablespace tbs_users;l user_name為數(shù)據(jù)庫用戶的用戶名l user_password為數(shù)據(jù)庫用戶的密碼l tbs_users為用戶使用的表空間，默認是users表空間。例如：CREATE USER cmsuser IDENTIFIED BY passwordDefaultTablespace users;3、 賦表空間使用權(quán)限alter user user_name quota unlimited on user_tablespace quota unlimi

12、ted on user_tablespace;4、 給用戶賦權(quán)限GRANT connect, resource TO cmsuser;l Connect用戶能登錄數(shù)據(jù)庫的權(quán)限l Resource用戶能創(chuàng)建一些數(shù)據(jù)庫對像的權(quán)限，表、視圖，存儲過程，一般是授予開發(fā)人員的5、 刪除用戶DropUser cmsuser Cascade;l 使用cascade參數(shù)可以刪除該用戶的全部objects三、 ORACL常用的數(shù)據(jù)類型l INTEGER存儲整數(shù)，整數(shù)不包括浮點數(shù);它是一個整數(shù)數(shù)字，如：1、10、15l NUMBER，是以十進制格式進行存儲的，它便于存儲，但是在計算上，系統(tǒng)會自動的將它轉(zhuǎn)換成為二進

13、制進行運算的。它的定義方式是NUMBER(P，S)，P是精度，最大38位，S是刻度范圍，可在-84.127間取值。例如：NUMBER(5，2)可以用來存儲表示-999.99.999.99間的數(shù)值。P、S可以在定義是省略，例如：NUMBER(5)、NUMBER等;l CHAR，描述定長的字符串，如果實際值不夠定義的長度，系統(tǒng)將以空格填充。它的聲明方式如下CHAR(L)，L為字符串長度，缺省為1，作為變量最大32767個字符，作為數(shù)據(jù)存儲在ORACLE8中最大為2000。l VARCHAR2(VARCHAR)，描述變長字符串。它的聲明方式如下VARCHAR2(L)，L為字符串長度，沒有缺省值，作為

14、變量最大32767個字節(jié)，作為數(shù)據(jù)存儲在ORACLE8中最大為4000。在多字節(jié)語言環(huán)境中，實際存儲的字符個數(shù)可能小于L值，例如：當語言環(huán)境為中文(SIMPLIFIED CHINESE_CHINA.ZHS16GBK)時，一個VARCHAR2(200)的數(shù)據(jù)列可以保存200個英文字符或者100個漢字字符。l NCHAR、NVARCHAR2，國家字符集，與環(huán)境變量NLS指定的語言集密切相關(guān)，使用方法和CHAR、VARCHAR2相同。不過最大參數(shù)為NCHAR(2000)、NVARCHAR2(2000)l DATE唯一的一種日期類型-，用來存儲時間信息，站用7個字節(jié)(從世紀到秒)l LOB（oracl

15、e8以前叫l(wèi)ong）變量主要是用來存儲大量數(shù)據(jù)的數(shù)據(jù)庫字段，最大可以存儲4G字節(jié)的內(nèi)容，CLOB：存儲單字節(jié)字符數(shù)據(jù)（如英文）NCLOB：用來存儲定寬多字節(jié)字符數(shù)據(jù)（如漢字），BLOB：用來存儲無結(jié)構(gòu)的二進制數(shù)據(jù)（word、pdf文檔）。四、 基本的SQL語句的寫法1、 rowiddelete和rownum的區(qū)別rowid是Oracle數(shù)據(jù)庫中的每一行都有一個唯一的行標識符，稱為rowid,它是一個18位數(shù)字，以64為基數(shù)，該徝包含了該行在oracle數(shù)據(jù)庫中的物理位置，查詢rowid如下：SQL Select rowid,id From infobase Where Rownum 5;ROW

16、ID ID AAAYKRAAEAAGGpcAAI 1000000AAAYKRAAEAAGGpcAAJ 1000001AAAYKRAAEAAGGpcAAK 1000002AAAYKRAAEAAGGpcAAL 1000003Rowid應(yīng)用實例： 刪除表中重復記錄DeleteFrom Infobase aWhere Rowid Select Rownum,id From infobase Where Rownum 5;ROWNUM ID 1 10000002 10000013 10000024 1000003rownum應(yīng)用實例： 批量刪除記錄如果要刪除的數(shù)據(jù)量很大，一次刪除可能需要占用系統(tǒng)大量的內(nèi)

17、存，給數(shù)據(jù)庫帶來很大的壓力，可以進行分步批量刪除并提交，避免這種情況createor replace procedure del_dataas -創(chuàng)建過程并執(zhí)行beginfor i in 1.1000loopdelete from cmsuser_base Where posterid=Servlet提交and rownum 100;commit;end loop;End del_data; 分頁查詢Select *From (Select * From InfobaseOrder By OriginaltimeDesc)Where Rownum = 10;2、 delete和truncate

18、、drop的區(qū)別TRUNCATE TABLE 在功能上與不帶WHERE子句的DELETE語句相同：二者均刪除表中的全部行。但 TRUNCATE TABLE比 DELETE速度快，且使用的系統(tǒng)和事務(wù)日志資源少。Drop 則是刪除整個表，與TRUNCATE操作類型相同，都是DDL操作（數(shù)據(jù)定義語言）DeleteFrom infobase Id = 1;Commit;-或Delete infobase Where Id = 1;Commit;TruncateTable infobase;DropTable infobase;3、 多表關(guān)聯(lián)查詢根據(jù)連接中使用操作符的不同，連接條件可以分為兩類：l 等連

19、接：在連接中使用等于操作符(=)l 不等連接：在連接中使用除等號之外的操作符，如：、between等除連接條件區(qū)分之外，連接本身也有3種不同的類型：l 內(nèi)連接：只有當連接中的列包含滿足連接條件的值時才會返回一行。這就是說，如果某一行的連接條件中的一列是空值，那么這行就不返回。l 外連接：即使連接條件中的一列包含空值也會返回一行。l 自連接：返回連接的同一個表中的行。不等連接實例 查詢員工的工資等級Select e.first_name,e.title,e.salary,sg.salary_grade_idFrom employees e,salary_grades sgWhere e.sala

20、ry Between sg.low_salary And sg.high_salary;-employees員工表，salary_grades工資等級表內(nèi)連接實例 查詢詳細信息記錄Select a.Id, b.Name, a.Title, a.ContentFrom Infobase aJoin Class b On a.Classid = ( | b.Id | );-或Select a.Id, b.Name, a.Title, a.ContentFrom Infobase a Class bWhere a.Classid = ( | b.Id |); 關(guān)聯(lián)更新和刪除Update Infoba

21、se aSet a.Title = (Select Title From Infobase_TempWhere Id = a.Id);Commit;Delete From Infobase aWhere Exists ( Select 1 From Class bWhere a.classid = b.id);Commit;外連接實例 查詢沒有附件信息記錄Select a.*From Infobase aLeft Join Attachment b On a.Id = b.InfoidWhere b.Infoid Is Null;-這是典型兩表相減查詢，也可用not in，但是種寫法效率會高些

22、-這是一個左外連接例子，右外連接跟左外連接一樣，只是表的位置不同自連接實例 查詢員工和主管之間的關(guān)系Select w.Last_Name | works for | m.Last_NameFrom Employees m, Employees wWhere w.Employee_Id = m.Manager_Id; 遞歸查詢下面的語法也可以看做成一個隱含的自連接查詢，它是一個字列和父列的遞歸查詢Select *From ClassStart With Parentid = 0001Connect By Prior Id = Parentid-id,parentid那么通過表示每一條記錄的par

23、ent是誰，就可以形成一個樹狀結(jié)構(gòu)-Parentid = 0001指定樹的根從哪個節(jié)點開始4、 子查詢子查詢有兩種基本類型：l 單行子查詢：不向外部的SQL返回結(jié)果，或者只返回一行。l 多行子查詢：向外部的SQL返回一行或多行。另外子查詢還有三種子類型：l 多列子查詢：向外部的SQL語句返回多列。l 關(guān)聯(lián)子查詢：引用外的SQL語句中的一錢或多列。l 嵌套子查詢：位于另外一個子查詢中。子查詢最多可以嵌套255層。單行子查詢實例 查詢內(nèi)容大小大于平均大小的記錄Select *From InfobaseWhere Contentsize (Select Avg(Contentsize) From I

24、nfobase); 在having子句中使用子查詢檢索那些平均價格低于同類產(chǎn)品平均價格最大值的產(chǎn)品的product_type_id和平均價格：Select Product_Type_Id,Avg(Price)From ProductsGroup By Product_Type_IdHaving Avg(Price) (Select Max(Avg(Price)From ProductsGroup By Product_Type_Id); 在from子句中使用子查詢（內(nèi)聯(lián)視圖）就外部查詢的from子句而言，子查詢的輸出僅僅是另外一個數(shù)據(jù)源。檢索Productid大于100的產(chǎn)品Select Pr

25、oductidFrom (Select Productid From ProductWhere Productid Select Productid, Productname2 From Product3 Where Productid =4 (Select Productid From Product Where Productname Like 恒泰%);Select Productid, ProductnameORA-01427:單行子查詢返回多于一個行(2)、子查詢不能包含order by子句，必須在外查詢中進行任何排序多行子查詢實例 在多行查詢中使用in操作符檢索信息表里符合clas

26、sid條件的記錄：Select *From InfobaseWhere Classid In(Select ( |Id |)From Class Where Name Like營業(yè)部%) 在多行子查詢中使用any操作符檢查是否有任何員工的工資低于salary_grades表中任何一級的最低工資：Select e.Employee_Id, e.Last_NameFrom Employees eWhere e.Salary All (Select sg.high_salaryFrom Salary_Grades Sg);多例子查詢實例 檢索每種產(chǎn)品類型中價格最低的產(chǎn)品Select *From Pr

27、oductsWhere (Product_Type_Id, Price) In(Select Product_Type_Id, Min(Price)From ProductsGroup By Product_Type_Id);-上面的寫法也如同下面的寫法，返回結(jié)果一樣Select *From Products aWhere Price = (Select Min(Price)From ProductsWhere Product_Type_Id = a.Product_Type_Id);-注意：這個例子是日常的開發(fā)很典型的例子，會經(jīng)常用到，一定要學會應(yīng)用關(guān)聯(lián)子查詢實例 在關(guān)聯(lián)子查詢中exists

28、檢索那些負責管理其它員工的員工記錄：Select Employee_Id, Last_NameFrom EmployeesOuter Where Exists(Select Employee_IdFrom Employees InnerInner Where Inner.Manager_Id = Outer.Employee_Id); 在關(guān)聯(lián)子查詢中not exists檢索從未購買過的產(chǎn)品Select Product_Id,NameFrom Products aWhere Not Exists (Select 1 From Purchases Where Product_Id = a.Prod

29、uct_Id)- 子句的1是個虛擬列，沒有意義，改成其它值也可以 Exists和not exists與in和not in的比較Exists與in不同，Exists只檢查行的存在性，而in則要檢查實際值的存在性。通常來講，Exists的性能要比in要高一些，因此應(yīng)該盡可能地使用Exists，而不用in。在編寫使用Not Exists和Not in的查詢時必須要謹慎。當一個值列表包含一個空值時，Not Exists就返回true,而Not in 則返回false?？紤]下面這個例子：本例使用了Not Exists，檢索那些在products表中沒有任何產(chǎn)品的產(chǎn)品類型：Select Product_T

30、ype_Id,NameFrom Product_Types aWhere Not Exists(Select 1From ProductsWhere Product_Type_Id = a.Product_Type_Id);PRODUCT_TYPE_ID NAME 5 Magazine注意上面這個例子返回了一行記錄。下面這個例子使用Not in重寫了上面這個例子，而此時沒有返回任何行：Select Product_Type_Id,NameFrom Product_Types aWhere Product_Type_Id Not In (Select Product_Type_IdFrom Pr

31、oducts);PRODUCT_TYPE_ID NAME 這所以沒有返回行，就是因為子查詢返回Product_Type_Id值的列表，其中包含一個空值。而產(chǎn)品#12的Product_Type_Id是空值。因此外部查詢中的Not in操作符返回false,因此沒有任何行。這個問題可以使用Nvl()函數(shù)將空值轉(zhuǎn)換成一個值解決。下面的例子中，Nvl()函數(shù)將空值的Product_Type_Id轉(zhuǎn)換成0：Select Product_Type_Id,NameFrom Product_Types aWhere Product_Type_Id Not In (Select nvl(Product_Type

32、_Id,0)From Products);PRODUCT_TYPE_ID NAME 5 Magazine這次返回了我們要得到的那行記錄。嵌套子查詢實例 多層嵌套子查詢在子查詢內(nèi)部可以嵌套其它子查詢，嵌套層次最多為255。在編寫時應(yīng)該盡量少使用嵌套子查詢技術(shù)，因為使用表連接時，查詢性能會更高。下面的例子包含了一個嵌套子查詢，子查詢包含了另外一個子查詢，而它自己又被包含在一個外部查詢中：Select Product_Type_Id,Avg(Price)From ProductsGroup By Product_Type_IdHaving Avg(Price) = 1)Group By Produc

33、t_Type_Id);這個查詢包含了3個查詢：一個嵌套子查詢、一個子查詢和一個外部查詢?？梢杂衫锏酵庾约褐鸩椒治?，得到運行結(jié)果。5、 使用集合操作符集合操作符可以將兩個或多個查詢返回的行組合起來，當使用集合操作符的時候，必須牢記下列的限制條件：所有查詢所返回的列數(shù)以及列的類型必須匹配，列名可以不同。集合操作符主要有：l Union all 返回各個查詢檢索出的所有行，包括重復的行。l Union 返回各個查詢檢索出的所有行，不包括重復的行。l Intersect 返回兩個查詢共有行。l Minus 返回第二個查詢檢索出的行從第一個查詢檢索出的行中減去之后剩余的記錄。 Union all使用實例

34、Union all 返回各個查詢檢索出的所有行，包括重復的行SelectId,classidFrom infobase Where Rownum 5Union AllSelect Id,classid From infobase_temp;ID CLASSID 1000000 (000100010002)1000001 (000200020003000100010002)1000002 (000100010003)1000005 (00010001000400030007)1000000 (000100010002)1000001 (000200020003000100010002)可以使用o

35、rder by子句根據(jù)兩個查詢中的列的位置對列進行排序。SelectId,classidFrom infobase Where Rownum 5Union AllSelect Id,classid From infobase_tempOrder By 1;ID CLASSID 1000000 (000100010002)1000000 (000100010002)1000001 (000200020003000100010002)1000001 (000200020003000100010002)1000002 (000100010003)1000005 (000100010004000300

36、07) Union使用實例返回各個查詢檢索出的所有行，不包括重復的行。因為Union查詢時要有排重操作，所以Union all要比Union操作效率要高一些。SelectId,classidFrom infobase Where Rownum 5UnionSelect Id,classid From infobase_temp;ID CLASSID 1000000 (000100010002)1000001 (000200020003000100010002)1000002 (000100010003)1000005 (00010001000400030007) Intersect使用實例In

37、tersect 返回兩個查詢共有行只檢索出那些infobase與infobase_temp共有的行SelectId,classidFrom infobase Where Rownum 5intersectSelect Id,classid From infobase_temp;ID CLASSID 1000000 (000100010002)1000001 (000200020003000100010002) Minus使用實例Minus 返回第二個查詢檢索出的行從第一個查詢檢索出的行中減去之后剩余的記錄.下例是從infobase返回的行中減去從infobase_temp中返回的行，然后返回剩

38、余的行：SelectId,classidFrom infobase Where Rownum Select decode(1,1,2,3) From dual;DECODE(1,1,2,3)2因為對1與1進行比較，由于兩者相等，所以返回2（否則返回3）Decode通常在寫SQL時與dual表結(jié)合給變量賦值。下面這個例子對more_products中的available列進行比較。如果available等于Y，返回字符串Product is available,否則返回字符串Product is not available:Select Prd_Id,Available,Decode(Avail

39、able,Y,Product is available,Product is not available)From More_Products;PRD_ID AVAILABLE DECODE(AVAILABLE,Y,PRODUCTI 1 Y Product is available2 Y Product is available3 N Product is not available4 N Product is not available5 Y Product is available可以向Decode()傳遞多個搜索和結(jié)果參數(shù)，如下例：Select Product_Id,Product_Ty

40、pe_Id,Decode(Product_Type_Id, 1,Book,2, Video,3,Dvd,CD)From Products;如果Product_Type_Id=1，返回Book如果Product_Type_Id=2，返回Video如果Product_Type_Id=3，返回Dvd如果Product_Type_Id等于其它值，返回CD Case表達式使用實例case允許if-then-else類型的邏輯處理，而不需要使用pl/sql。Case的工作方式與Decode()類似，通常我們在有較少的判斷時使用decode，因為條件多話會看著很混亂；所以盡量使用case，它與ANSI兼容。

41、有兩種類型的case表達式：l 簡單case表達式，使用表達式確定返回值。l 搜索case表達式，使用條件確定返回值。使用簡單表達式例子：Select Product_Id,Product_Type_Id,Case Product_Type_IdWhen 1Then BookWhen 2Then VideoWhen 3Then DvdElse CDEndFrom Products;PRODUCT_ID PRODUCT_TYPE_ID CASEPRODUCT_TYPE_IDWHEN1THENB 1 1 Book2 1 Book3 2 Video4 2 Video5 2 Video6 2 Vide

42、o7 3 Dvd8 3 Dvd9 4 CD使用搜索case表達式Select Product_Id,Product_Type_Id,CaseWhen Product_Type_Id = 1Then BookWhen Product_Type_Id = 2 Then VideoWhen Product_Type_Id = 3 Then DvdElse CDEndFrom Products;返回結(jié)果中上面是一樣的7、 其它五、 日期和時間的存儲與處理1、 常用的幾個日期函數(shù)說明l MONTHS_BETWEEN兩日期相差多少月l ADD_MONTHS 加月份到日期l NEXT_DAY 指定日期的下一

43、天l LAST_DAY 一個月中的最后一天l ROUND Round日期l TRUNC Truncate日期l TO_CHAR(x,format)函數(shù)用于將時間值轉(zhuǎn)換為字符串，該函數(shù)還可以提供一個可選的參數(shù)format來說明x的格式。如：MONTH DDD,YYYYl TO_DATE(x,format)將字符串x轉(zhuǎn)換成date類型。2、 常用的日期計算實例下面是幾個關(guān)于日期方面的SQL實例 取得當前日期是本月的第幾周SQL select to_char(sysdate,YYYYMMDD W HH24:MI:SS) from dual;TO_CHAR(SYSDATE,YYYYMMDDWHH242

44、0090202 1 18:00:43SQL select to_char(sysdate,W) from dual;TO_CHAR(SYSDATE,W)1 取得當前日期是一個星期中的第幾天,注意星期日是第一天SQL select sysdate,to_char(sysdate,D) from dual;SYSDATE TO_CHAR(SYSDATE,D) 2009-2-2 18 2select to_char(sysdate,yyyy) from dual; -年select to_char(sysdate,Q from dual; -季select to_char(sysdate,mm) f

45、rom dual; -月select to_char(sysdate,dd) from dual; -日ddd年中的第幾天WW年中的第幾個星期W該月中第幾個星期D周中的星期幾hh小時(12)hh24小時(24)Mi分ss秒 取當前日期是星期幾中文顯示:SQL select to_char(sysdate,day) from dual;TO_CHAR(SYSDATE,DAY)星期四 如果一個表在一個date類型的字段上面建立了索引，如何使用alter session set NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS 得到當前的日期select sysdate f

46、rom dual;SYSDATE2009-2-2 18 得到當天凌晨0點0分0秒的日期select trunc(sysdate) from dual;TRUNC(SYSDATE)2009-2-2 得到這天的最后一秒select trunc(sysdate) + 0.99999 from dual;TRUNC(SYSDATE)+0.999992009-2-2 23:59:59 得到小時的具體數(shù)值select trunc(sysdate) + 1/24 from dual;TRUNC(SYSDATE)+1/242009-2-2 1:00:00select trunc(sysdate) + 7/24

47、 from dual;TRUNC(SYSDATE)+7/242009-2-2 7:00:00 得到明天凌晨0點0分0秒的日期select trunc(sysdate+1) from dual;TRUNC(SYSDATE+1)2009-2-3 本月一日的日期select trunc(sysdate,mm) from dual;TRUNC(SYSDATE,MM)2009-2-1 得到下月一日的日期select trunc(add_months(sysdate,1),mm) from dual;TRUNC(ADD_MONTHS(SYSDATE,1),M2009-3-1 返回當前月的最后一天SQL s

48、elect last_day(sysdate) from dual;LAST_DAY(SYSDATE)2009-2-28 18:11:3SQL select last_day(trunc(sysdate) from dual;LAST_DAY(TRUNC(SYSDATE)2009-2-28SQL select trunc(last_day(sysdate) from dual;TRUNC(LAST_DAY(SYSDATE)2009-2-28SQL select trunc(add_months(sysdate,1),mm) - 1 from dual;TRUNC(ADD_MONTHS(SYSD

49、ATE,1),M2009-2-28 得到一年的每一天Select Trunc(Sysdate, yyyy) + Rn - 1 Date0From (Select Rownum Rn From All_Objects Where Rownum SELECT TO_CHAR(SYSDATE,DDD) FROM DUAL;TO_CHAR(SYSDATE,DDD)033 如何在給現(xiàn)有的日期加上2年SQL select add_months(sysdate,24) from dual;ADD_MONTHS(SYSDATE,24)2011-2-2 18:15:56 判斷某一日子所在年分是否為潤年SQL s

50、elect decode(to_char(last_day(trunc(sysdate,y)+31),dd),29,閏年,平年) from dual;DECODE(TO_CHAR(LAST_DAY(TRUNC(平年 判斷兩年后是否為潤年SQL select decode(to_char(last_day(trunc(add_months(sysdate,24),y)+31),dd),29,閏年,平年) from dual;DECODE(TO_CHAR(LAST_DAY(TRUNC(平年 得到日期的季度SQL select ceil(to_number(to_char(sysdate,mm)/3

51、) from dual;CEIL(TO_NUMBER(TO_CHAR(SYSDATE1SQL select to_char(sysdate, Q) from dual;TO_CHAR(SYSDATE,Q)1六、 SQL語句的優(yōu)化寫法1、 oracle訪問Table的方式ORACLE 采用兩種訪問表中記錄的方式:l 全表掃描全表掃描就是順序地訪問表中每條記錄. ORACLE采用一次讀入多個數(shù)據(jù)塊(database block)的方式優(yōu)化全表掃描.l 通過ROWID訪問表你可以采用基于ROWID的訪問方式情況,提高訪問表的效率, , ROWID包含了表中記錄的物理位置信息.ORACLE采用索引(I

52、NDEX)實現(xiàn)了數(shù)據(jù)和存放數(shù)據(jù)的物理位置(ROWID)之間的聯(lián)系.通常索引提供了快速訪問ROWID的方法,因此那些基于索引列的查詢就可以得到性能上的提高.2、 創(chuàng)建索引索引是表的一個概念部分,用來提高檢索數(shù)據(jù)的效率.通過索引查詢數(shù)據(jù)比全表掃描要快.當ORACLE找出執(zhí)行查詢和Update語句的最佳路徑時, ORACLE優(yōu)化器將使用索引.同樣在聯(lián)結(jié)多個表時使用索引也可以提高效率.另一個使用索引的好處是,它提供了主鍵(primary key)的唯一性驗證.除了那些LONG或LONG RAW、LOB數(shù)據(jù)類型,你可以索引幾乎所有的列.通常,在大型表中使用索引特別有效.當然,你也會發(fā)現(xiàn),在掃描小表時,使

53、用索引同樣能提高效率.雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價.索引需要空間來存儲,也需要定期維護,每當有記錄在表中增減或索引列被修改時,索引本身也會被修改.這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5次的磁盤I/O . 因為索引需要額外的存儲空間和處理,那些不必要的索引反而會使查詢反應(yīng)時間變慢。大多數(shù)情況下,優(yōu)化器通過WHERE子句訪問INDEX.定期的重構(gòu)索引是有必要的.ALTER INDEX REBUILD 創(chuàng)建普通索引實例CreateIndex infobase_titleOn infobase(title);-創(chuàng)建唯一

54、索引Createunique Index infobase_keyOn infobase (Id); 創(chuàng)建全文索引實例-創(chuàng)建全文索引CREATE INDEX infobase_content ON infobase(content)INDEXTYPE IS CTXSYS.CONTEXT;-在全文索引進行檢索SELECT * FROM infobase WHERE CONTAINS (content,first) 0;-創(chuàng)建同步全文索引過程create or replace procedure sync_contentisbeginexecute immediatealter index inf

55、obase_content rebuild online | parameters ( sync ) ;execute immediatealter index infobase_content rebuild online | parameters ( optimize full maxtime unlimited ) ;end sync_content;/-創(chuàng)建作業(yè)執(zhí)行同步過程variable n number;begindbms_job.submit(:n,sync_content;,sysdate,sysdate+1/48);commit;end;/ 創(chuàng)建主建AlterTable in

56、fobaseAdd Constraints infobase_key Primary Key(Id);-表上創(chuàng)建主建相當于在列上的建了一個唯一的索引3、 SQL優(yōu)化實例及問題 使用like操作符的問題在WHERE子句中,如果索引列所對應(yīng)的值的第一個字符由通配符(WILDCARD)開始,索引將不被采用.如：like %標題。這一點一定要注意。因為在我們開發(fā)的過程中經(jīng)常遇到這樣的問題，like %標題%會掃描全表，會給數(shù)據(jù)庫的性能帶來很大的壓力。要盡可能避免這種寫法，如果有必要可以用全文索引代替。如下面的例子：selectcount(*)from infobasewhere classid in

57、(0001000300030001),(0001000300030002)and (category like %600755% or category like%600976%);-可用全文索引代替select count(*) from infobasewhere classid in (0001000300030001),(0001000300030002)and (CONTAINS (category, 600755) 0or CONTAINS (category, 600976) 0 );但like 標題%這種寫法會使用索引 選擇最有效率的表名順序(只在基于規(guī)則的優(yōu)化器中有效)ORA

58、CLE的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子句中寫在最后的表(基礎(chǔ)表 driving table)將被最先處理. 在FROM子句中包含多個表的情況下,你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表.當ORACLE處理多個表時,會運用排序及合并的方式連接它們.首先,掃描第一個表(FROM子句中最后的那個表)并對記錄進行派序,然后掃描第二個表(FROM子句中最后第二個表),最后將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合并.例如:表 TAB1 16,384條記錄表 TAB2 1條記錄選擇TAB2作為基礎(chǔ)表 (最好的方法)select count(*) from tab

59、1,tab2執(zhí)行時間0.96秒選擇TAB2作為基礎(chǔ)表 (不佳的方法)select count(*) from tab2,tab1執(zhí)行時間26.09秒如果有3個以上的表連接查詢,那就需要選擇交叉表(intersection table)作為基礎(chǔ)表,交叉表是指那個被其他表所引用的表.例如:EMP表描述了LOCATION表和CATEGORY表的交集.SELECT *FROM LOCATION L ,CATEGORY C,EMP EWHERE E.EMP_NO BETWEEN 1000 AND 2000AND E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCN將比下列SQL更

60、有效率SELECT *FROM EMP E ,LOCATION L ,CATEGORY CWHERE E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCNAND E.EMP_NO BETWEEN 1000 AND 2000 WHERE子句中的連接順序ORACLE采用自下而上的順序解析WHERE子句,根據(jù)這個原理,表之間的連接必須寫在其他WHERE條件之前,那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾.例如:第二個SQL要比第一個SQL查詢效率高：SELECT *FROM EMP EWHERE SAL 50000AND JOB = MANAGERAND 2