基礎(chǔ)知識(shí)培訓(xùn)文檔教程

1、Oracle基礎(chǔ)知識(shí)培訓(xùn)內(nèi)容目錄第一部分 培訓(xùn)安排11.培訓(xùn)目標(biāo)11.1.熟練掌握SQL基礎(chǔ)知識(shí)11.2.熟練掌握ORACLE基礎(chǔ)知識(shí)12.培訓(xùn)計(jì)劃2第二部分 培訓(xùn)內(nèi)容33.Oracle及toad的安裝33.1.數(shù)據(jù)表空間和索引表空間33.2.基本SQL的使用43.3.DB_LINK及索引的簡介53.4.oracle數(shù)據(jù)庫的視圖63.5.oracle數(shù)據(jù)庫中序列問題63.6.給用戶賦權(quán)及收回權(quán)限73.7.單表查詢73.8.oracle常用函數(shù)103.9.oracle數(shù)據(jù)庫多表查詢133.10.數(shù)據(jù)導(dǎo)入、導(dǎo)出183.11.SQL語句優(yōu)化183.12.觸發(fā)器193.13.存儲(chǔ)過程、包及函數(shù)19第一

2、部分培訓(xùn)安排1. 培訓(xùn)目標(biāo)1.1. 熟練掌握SQL基礎(chǔ)知識(shí)Ø SQL的定義：SQL結(jié)構(gòu)化查詢語言（structure query language），SQL的基礎(chǔ)是關(guān)系數(shù)據(jù)模型；Ø 關(guān)系數(shù)據(jù)模型：以二維表的表示實(shí)體，以外鍵表示實(shí)體關(guān)系Ø SQL語法：² DDL（Data define language）數(shù)據(jù)定義語言，創(chuàng)建或修改數(shù)據(jù)對象（TABLE, INDEX）; (CRATE, ALTER, DROP) ² DML( Data Manipulation Language)數(shù)據(jù)操作語言（TABLE） (DELETE,UPDATE)²

3、DCL數(shù)據(jù)控制語言，用于定義數(shù)據(jù)庫用戶的權(quán)限。（GRANT、REVOKE）² DQL(SELECT)² 數(shù)據(jù)類型字符、數(shù)值、日期、二進(jìn)制1.2. 熟練掌握ORACLE基礎(chǔ)知識(shí)Ø ORACLE 安裝：服務(wù)（實(shí)例）、監(jiān)聽（listener）、端口、TNS（transparent network substrate透明網(wǎng)絡(luò)底層）Ø ORACLE對象：² 表（table）、序列（sequence）、索引（index）、視圖（view）、同義詞（synonymous）、約束（constraints），觸發(fā)器（trigger）；² 函數(shù)（func

4、tion）、過程（procedures）、包（package）² 用戶（user）、表空間（tablespace）、數(shù)據(jù)文件（datafile）；² 數(shù)據(jù)連接（db_link）任務(wù)（job）目錄（directory）Ø 工具使用：toad、數(shù)據(jù)備份Ø PL/SQL2. 培訓(xùn)計(jì)劃目標(biāo)內(nèi)容描述時(shí)間備注第一階段環(huán)境配置，系統(tǒng)備份和恢復(fù)，熟悉toad的基本使用安裝系統(tǒng) Oracle、VS2019、Toad、Office、Openvpn、SVN、iis1天TOAD快速入門.PDF第二階段ORACLE基礎(chǔ)知識(shí)（1）創(chuàng)建數(shù)據(jù)表空間CMIS_DATA、索引表空間CMIS

5、_IDX,并新建用戶CMIS（2）基本SQL使用，create、select、delete、drop等，數(shù)據(jù)表、主鍵、外鍵、索引，SQL執(zhí)行1天（1） 創(chuàng)建DB_LINK訪問68:1521數(shù)據(jù)庫（2） 視圖（3） 序列（4） 賦權(quán)1天單表查詢1天練習(xí)題函數(shù)使用（日期、數(shù)字、字符、分析、多重分組等）1天函數(shù)資料多表查詢：（1）笛卡爾積查詢（2）等值查詢 （3）不等值查詢（4）存在性判斷 （5）嵌套查詢1天練習(xí)題多表查詢：（6）連接SQL JOIN（7）層次遞歸查詢1天練習(xí)題多表查詢：（8）條件查詢 （9）WITH語句重用子查詢1天練習(xí)題（1） 數(shù)據(jù)導(dǎo)入、導(dǎo)出（2） 數(shù)據(jù)優(yōu)

6、化1天存儲(chǔ)過程、包、函數(shù)、觸發(fā)器1天第二部分培訓(xùn)內(nèi)容3. Oracle及toad的安裝Ø 詳見oracle的安裝.doc、toad環(huán)境設(shè)置.doc、toad快速入門.pdf3.1. 數(shù)據(jù)表空間和索引表空間一、在本地創(chuàng)建一數(shù)據(jù)表空間CMIS_DATA和索引表空間CMIS_IDX（1）表空間tablespaces：一個(gè)ORACLE數(shù)據(jù)庫能夠有一個(gè)或多個(gè)表空間,而一個(gè)表空間則對應(yīng)著一個(gè)或多個(gè)物理的數(shù)據(jù)庫文件。表空間是ORACLE數(shù)據(jù)庫恢復(fù)的最小單位,容納著許多數(shù)據(jù)庫實(shí)體,如表、視圖、索引、聚簇、回退段和臨時(shí)段等。(房間、箱子、物品)；塊的倍數(shù)例：create tablespace cmis

7、_data -表空間名稱logging datafile 'Q:oracleproduct10.2.0oradataTestxyrj_data.dbf' -表空間包含的數(shù)據(jù)文件及位置size 20g -大?。╮esize）autoextend on -自動(dòng)擴(kuò)展next 10g maxsize 500g-自動(dòng)擴(kuò)展是按10擴(kuò)展，最大的SIZE是500Gextent management local;-本地管理模式（表空間分為2種管理模式，字典及本地）（2）數(shù)據(jù)庫默認(rèn)表空間SYSTEM:存放數(shù)據(jù)字典，包括表、視圖，存儲(chǔ)過程的定義等SYSAUX：STSTEM表空間的輔助空間，減少SYS

8、TEM的負(fù)荷TEMP：存放SQL語句處理的表和索引的信息EXAMPLE：存放樣例的數(shù)據(jù)UNDOTBS1：存放撤銷數(shù)據(jù)的表空間USER：存放“應(yīng)用系統(tǒng)”所使用的數(shù)據(jù)庫對象（3）表空間的修改語句ALTER TABLESPACE TABNAME_OLD RENAME TO TABNAME_NEW;ALTER TABLESPACE TABNAME READ ONLY/WRITE;只讀不能進(jìn)行DML操作ALTER TABLESPACE TABNAME DROP TABLESPACE TABLENAME INCLUDING CONTENTS AND DATAFILES3.2. 基本SQL的使用（1） 創(chuàng)建

9、表student、teacher、course、sc；表結(jié)構(gòu)詳見sql練習(xí)題.sql（2） 練習(xí)select、insert、delete、update、drop等的操作DML（數(shù)據(jù)操作語言）：用于檢索或者修改數(shù)據(jù)。DML包括:SELECT：用于檢索數(shù)據(jù)； INSERT：用于增加數(shù)據(jù)到數(shù)據(jù)庫； UPDATE：用于從數(shù)據(jù)庫中修改現(xiàn)存的數(shù)據(jù)DELETE：用于從數(shù)據(jù)庫中刪除數(shù)據(jù)。DDL（數(shù)據(jù)定義語言）：用于定義數(shù)據(jù)的結(jié)構(gòu)，比如創(chuàng)建、修改或者刪除數(shù)據(jù)庫對象。DDL包括：DDL語句可以用于創(chuàng)建用戶和重建數(shù)據(jù)庫對象。 CREATE TABLE：創(chuàng)建表 ALTER TABLE DROP TABLE：刪除表 C

10、REATE INDEXDROP INDEXDCL（數(shù)據(jù)控制語言）：用于定義數(shù)據(jù)庫用戶的權(quán)限。DCL包括： ALTER PASSWORD GRANT REVOKE 注：truncate、dorp、delete之間的區(qū)別（3） 數(shù)據(jù)完整性和約束性a. 非空約束：必須為某個(gè)列提供值Create table books( Bookno number(4) not null, Bookname varchar2(20)b. 主鍵約束：用于唯一標(biāo)識(shí)表中的每一行記錄，在一個(gè)表中，最多只能有一個(gè)主鍵約束。Create table books(Bookno number(4) not null, Booknam

11、e varchar2(20),Constraint BOOK_PK primary key(bookno)c. 唯一性約束：強(qiáng)調(diào)所在的列不允許有相同的值，比主鍵約束弱，即它所在的列允許空值（主鍵約束不允許）Create table members( Memno number(4) not null, Memname varchar2(20) not null,Phone varchar2(20),QQ varchar2(20) constraint qq_uk uniqued. 外鍵約束3.3. DB_LINK及索引的簡介1、數(shù)據(jù)庫連接串主要用于建立對遠(yuǎn)程數(shù)據(jù)庫的訪問方法，可以直接讀取遠(yuǎn)程Or

12、acle的數(shù)據(jù)，或者直接修改。數(shù)據(jù)庫連接串可以是公用連接PUBLIC或者私有連接PRIVATE。例：Create public/private database link test -db_link名字，通過此名字來調(diào)用遠(yuǎn)程數(shù)據(jù)庫的內(nèi)容Connect to username -連接遠(yuǎn)程數(shù)據(jù)庫合法oracle用戶名Identified by password -該用戶連接到ORACLE時(shí)合法的密碼Using connect_string -該oracle數(shù)據(jù)庫所在的主機(jī)上的tnstames.ora文件里邊定義的數(shù)據(jù)庫連接串(SID)（1） 創(chuàng)建一db_link訪問cmis/:1521/orcl的

13、數(shù)據(jù)庫（2） 在指定表空間中按數(shù)據(jù)字典新建表: cm_channel cm_district cm_level cm_channel_category cm_category cm_domain_type 2、索引：數(shù)據(jù)庫索引是把用戶感興趣的列植連同其行標(biāo)示符（ROWID）存儲(chǔ)在一起。ROWID包含了存儲(chǔ)列值的表行在磁盤上的物理位置。索引只是一種快速訪問數(shù)據(jù)的途徑，只影響速度。create index index_name on table_name(column_list) tablespace tablespace_name;index_name指所創(chuàng)建的索引的名稱table_name表示

14、為之創(chuàng)建索引的表名；column_list 是在其上創(chuàng)建索引的列名列表，可以基+于多列創(chuàng)建索引tablespace_name為索引指定表空間；索引優(yōu)點(diǎn)：1.提高查詢速度 2.保證數(shù)據(jù)唯一性索引缺點(diǎn)：1.占用表空間 2索引會(huì)經(jīng)常失效(大量數(shù)據(jù)表的增、刪、改會(huì)影響)索引類型：（1）非唯一索引：沒有在列值上規(guī)定此限制；oracle自動(dòng)為表的主鍵列創(chuàng)建唯一索引；（2）唯一索引(unique)：唯一索引可以確保在定義索引的列中，表的人員兩行的值都不相同。（3）反向鍵索引：通常建立在一些值連續(xù)增長的列上，如列中的值是由序列產(chǎn)生的情況；可以在create index語句中指定關(guān)鍵字reverse創(chuàng)建反向，鍵

15、索引語法如下create index myrev_index on student(stuname) reverse;注意：可以使用關(guān)鍵字noreverse 將反向鍵索引重建為標(biāo)準(zhǔn)索引，但不能將標(biāo)準(zhǔn)索引重建為反向鍵索引；alter index murev_index rebuild noreverse;（4）位圖索引：優(yōu)勢在于它適用于低基數(shù)列，即不同值的數(shù)目比表的行數(shù)少的列；如某個(gè)列的值重復(fù)了超過100次，可以考慮在該列上創(chuàng)建位圖索引；如一個(gè)表有100萬條數(shù)據(jù)，其中一列小于1000個(gè)不同的值，則可以考慮在該列上創(chuàng)建位圖索引；語法：create bitmap index mybit_index

16、on student(stuname); -這里的stuname就是一個(gè)低基數(shù)列（5）群集索引:指在表中的多個(gè)列上創(chuàng)建的索引，組合索引中的列的順序是任意的，不必是表中相鄰的列；如果select語句中的where子句引用了組合索引中的所有列或大多數(shù)列，則組合引用可以提高數(shù)據(jù)檢索的速度，創(chuàng)建索引時(shí)，應(yīng)注意定義中使用的列的順序。通常最頻繁訪問的列應(yīng)放在列表的最前面。 （6）基于函數(shù)的索引：基于一列或多列上的函數(shù)或表達(dá)式創(chuàng)建索引，當(dāng)where子句中包含函數(shù)或表達(dá)式以計(jì)算查詢時(shí)基于函數(shù)的索引十分有效；創(chuàng)建索引的函數(shù)可以是算術(shù)表達(dá)式，也可以是PL/SQL函數(shù)；eg:create index myvn_in

17、d on student (upper(stuname); -upper是內(nèi)置函數(shù)，適用于檢索大寫姓名的條目,它將字母轉(zhuǎn)為大寫了；再如：select * from student where upper(stuname) = 'Huang Bing'在上表中創(chuàng)建以下索引：cm_channel_pk (group_id) cm_channel_idx (district_id) cm_district_idx1 (id) cm_category_pk (id) cm_category_idx1 (CATEGORY_TYPE_ID)cm_domain_type_idx1 (CODE

18、)注：如果在建表時(shí)對字段建立了主鍵，就等同于建立了隱式索引。3.4. oracle數(shù)據(jù)庫的視圖視圖：視圖是基于一個(gè)表或多個(gè)表或視圖的邏輯表，本身不包含數(shù)據(jù)，通過它可以對表里面的數(shù)據(jù)進(jìn)行查詢和修改。視圖基于的表稱為基表。視圖是存儲(chǔ)在數(shù)據(jù)字典里的一條select語句。 通過創(chuàng)建視圖可以提取數(shù)據(jù)的邏輯上的集合或組合。例：創(chuàng)建一視圖用于訪問cm_channel表中city_id='w'的數(shù)據(jù)CREATE OR REPLACE VIEW cm_channel_vw -OR REPLACE 不管視圖是否存在，都創(chuàng)建；FORCE 不管基表是否存在，都創(chuàng)建該視圖 NOFORCE:只有基表存在，

19、才創(chuàng)建該視圖 (id,name,group_id,distinct_id,level_id,phone_no) -視圖的列定義別名 AS SELECT id,name,group_id,distinct_id,level_id,phone_no FROM cm_channel A WHERE city_id = 'w'3.5. oracle數(shù)據(jù)庫中序列問題序列是oracle提供的用于生成一系列唯一數(shù)字的數(shù)據(jù)庫對象。會(huì)自動(dòng)生成順序遞增的序列號(hào)，以實(shí)現(xiàn)自動(dòng)提供唯一的主鍵值。及視圖一樣，不占用實(shí)際的存儲(chǔ)空間。例：Create sequence <seq_name> -序列

20、名 start with n -序列開始的位置 increment by n -序列的增量，正數(shù)生成一個(gè)遞增的序列，負(fù)數(shù)生成一個(gè)遞減的序列 minvalue n/nomainvalue -序列生成最小值 maxvalue n /nomaxvalue -序列生成的最大值 cache n/nocicle -是否產(chǎn)生序列號(hào)預(yù)分配，并存儲(chǔ)在內(nèi)存中 cycle /nocycle -當(dāng)序列達(dá)到最大或最小值時(shí)，是否復(fù)位并繼續(xù)下去 order /noorder; -生成的序列值是否按照順序產(chǎn)生序列的2個(gè)偽列，NEXTVAL:返回序列生成的下一個(gè)序列號(hào)，首次使用序列時(shí)，必須使用NEXTVALCURRVAL：返回

21、序列的當(dāng)前序列號(hào)注意：讓某表中的某一為空的字段值自動(dòng)增加，需先建序列，再調(diào)用，如：create sequence cmis_dy.seq_order_NO start with 1maxvalue 99999minvalue 1nocyclecache 100;insert into CMIS_DY.ORDER_AMT (select cmis_dy.seq_order_NO.nextval,name,UNITPRICE,QUANTITY, BILL_CYCLE_IDfrom CMIS_DY.ORDER_AMTwhere bill_cycle_id = 201907序列不能及函數(shù)一起用，如：s

22、elect cmis_dy.seq_order_NO.nextval,name,UNITPRICE,QUANTITY, BILL_CYCLE_ID,count(name) from CMIS_DY.ORDER_AMTgroup by name,UNITPRICE,QUANTITY, BILL_CYCLE_ID此時(shí)會(huì)報(bào)錯(cuò)3.6. 給用戶賦權(quán)及收回權(quán)限grant all on CMIS_YB.DM_PD_USERPRC_INFO_201905 to CMIS_ADMIN_YB；revoke select on CMIS_YB.DM_PD_USERPRC_INFO_201905 to CMIS_AD

23、MIN_YB；3.7. 單表查詢(1)檢索所有字段數(shù)據(jù)：*(2)查詢部分字段(3)查詢某些字段不同記錄，相同的記錄只顯示一次：distinct例如：selectcount(distinct tno)from CMIS_DY.COURSE;(4)單條件查詢:=,<>,>,<,>=,<=,in/exists,not in/not exists,like,not like,is null,is not null,between and,not between and,NULLS FIRST,NULLS LAST注：exists和in的區(qū)別select * from

24、 t1 where exists(select 1 from t2 where t1.a=t2.a);t1數(shù)據(jù)量小而t2數(shù)據(jù)量大時(shí)，執(zhí)行效率高select * from t1 where t1.a in (select t2.a from t2);t1數(shù)據(jù)量非常大而t2數(shù)據(jù)量小時(shí)，執(zhí)行效率高(5)多條件查詢：and,or,not(6)decode語法，case when詳見十四條件查詢DECODE(value,if1,then1,if2,then2,if3,then3,.,else)表示如果value 等于if1時(shí)，DECODE函數(shù)的結(jié)果返回then1,.,如果不等于任何一個(gè)if值，則返回el

25、se。例：SELECT A.GROUP_ID, TO_NUMBER(A.ID_NO) ID_NO, DECODE(A.BRAND_ID,'qq','全球通普通繳費(fèi)金額', 'dn','動(dòng)感地帶普通繳費(fèi)金額', '神州行普通繳費(fèi)金額' ) KPI_NAME, ROUND(SUM(A.PAY_FEE),2) PAY_FEE FROM $(kpi('DBYW_DSHF110') GROUP BY A.GROUP_ID,TO_NUMBER(A.ID_NO),DECODE(A.BRAND_ID,'qq

26、','全球通普通繳費(fèi)金額','dn','動(dòng)感地帶普通繳費(fèi)金額','神州行普通繳費(fèi)金額');(7)排序：order by desc,asc例如：select sno,avg(score) ppfromscgroupby snohavingavg(score)>'70'orderby pp;(8)分組查詢：group by having group by 檢索結(jié)果進(jìn)行分組顯示，having從使用group by 子句分組后的查詢結(jié)果中篩選數(shù)據(jù)行。having相當(dāng)where，及where的唯一區(qū)別是當(dāng)查詢語

27、句中有聚合函數(shù)的時(shí)候，就不能用where了只能用having。例:顯示每個(gè)地區(qū)的總?cè)丝跀?shù)和總面積.僅顯示那些面積超過的地區(qū)SELECT region, SUM(population), SUM(area)FROM bbcGROUP BY regionHAVING SUM(area)>1000000注：a,在select列表中所有未包含在組函數(shù)中的列都應(yīng)該包含在group by子句中；包含在group by子句中的列不必包含在select列表中b,如果在條件中不含有分組函數(shù)時(shí)，where和having可以通用。例：select sno,avg(score)fromscwhere sno=&

28、#39;s001'groupby sno;或：select sno,avg(score)fromscgroupby snohaving sno='s001'c,having先分組，后過濾;where先過濾，后分組d,group byrollup(a,b)group byrollup(a,b)=group by a,b + group by a + group by null例如：select sno,cno,sum(score)fromscgroupbyrollup(sno,cno);(9)字段運(yùn)用查詢：字段1(+,-,*,/)字段2, | 連接字符(10)別名的使用：

29、表別名和字段別名 (11)mger into的用法將源數(shù)據(jù)(來源于實(shí)際的表,視圖,子查詢)更新或插入到指定的表中(必須實(shí)際存在),依賴于on條件,好處是避免了多個(gè)insert 和update操作。Merge是一個(gè)目標(biāo)性明確的操作符,不允許在一個(gè)merge 語句中對相同的行insert 或update 操作。這個(gè)語法僅需要一次全表掃描就完成了全部工作，執(zhí)行效率要高于INSERTUPDATE。簡單來說，就是：“有則更新，無則插入”+Merge語法MERGE INTO schema table T1USING schema table | view | subquery TON ( conditio

30、n )WHEN MATCHED THEN merge_update_clauseWHEN NOT MATCHED THEN merge_insert_clause;第一行命名目標(biāo)表并給別名tableT1第二行 using子句提供merge操作的數(shù)據(jù)源，命名T第三行 on子句指定合并的條件第四行 when matched then 子句判定條件符合則對表T1做什么改變（或刪除）第五行 when not matched then 子句判斷條件不符合則插入的操作例：當(dāng)B.MODE_CODE = A.ORIG_MODE_CODE，把b表的PROD_PRCID、PROD_ID更新進(jìn)HXLCUSTOMER

31、表，當(dāng)ORIG_MODE_CODE為空時(shí)，保留HXLCUSTOMER表 MERGE INTO HXLCUSTOMER a USING ( SELECT * FROM CMIS_DY.HXL_RCM_CUSTOMER ) b ON (B.MODE_CODE = A.ORIG_MODE_CODE) WHEN MATCHED THEN UPDATE SET a.ORIG_PROD_PRCID=b.PROD_PRCID, a.ORIG_PROD_ID=b.PROD_ID;（12）rownum、rowidrownum和rowid都是偽列，但是兩者的根本是不同的，rownum是根據(jù)sql查詢出的結(jié)果給每行

32、分配一個(gè)邏輯編號(hào)。但是rowid是物理結(jié)構(gòu)上的，在每條記錄insert到數(shù)據(jù)庫中時(shí)，都會(huì)有一個(gè)唯一的物理記錄。Rownum中只能使用<=，不能使用>=,如：查詢學(xué)生表第4至第6行數(shù)據(jù)：SELECT * FROM( SELECT ROWNUM RN,A.* FROM STUDENT A WHERE ROWNUM<=6) WHERE RN>=4;或者：SELECT * FROM( SELECT rownum rn,a.* from student a)t Where t.rn between 4 and 6;（13）ROW_NUMBER()的用法（刪除表中的重復(fù)記錄并只保留

33、一條相同記錄）這里通過這個(gè)函數(shù)將重復(fù)的記錄重新排列并添加編號(hào)。初始表DELETE FROM TABLEC WHERE ROWID IN (SELECT ROWID FROM (SELECT TABLEC.ROWID, NUMB, NAME, SAL, ROW_NUMBER ()OVER (PARTITION BY NUMB, NAME, SAL ORDER BY NUMB, NAME, SAL) AS NUM FROM TABLEC) WHERE NUM > 1);結(jié)果表DELETE t_test1 t WHERE t.ROWID NOT IN ( SELECT Max (s.ROWID

34、) FROM t_test1 s WHERE t.id = s.id GROUP BY s.id); 同樣這里也可以用union 及distinct 來達(dá)到刪除的目的，但是要建一張結(jié)構(gòu)及上表相同的表結(jié)構(gòu)，再插入數(shù)據(jù)，顯然這樣比較麻煩。（14）分組函數(shù),會(huì)自動(dòng)忽略空值：MAX、MIN、COUNT、AVG、WM_CONCAT(行轉(zhuǎn)列)例如：select Tno,Wm_concat(cname)from CMIS_DY.COURSEgroupby Tno;（15）分組函數(shù)嵌套例如：selectmax(avg(score)fromscgroupby sno;練習(xí)：簡單的SQL練習(xí)，詳見sql練習(xí)題.s

35、ql文檔子查詢：1、不可以在主查詢后的GROUP BY后面使用子查詢 2、一般不在子查詢中，使用排序。 3、若子查詢只返回一條記錄，就是單行子查詢；返回2條記錄以上，就是多行子查詢。在select語句后的子查詢必須是單行子查詢 4、可以使用子查詢的位置：WHERE、SELECT、HAVING、FROM3.8. oracle常用函數(shù)3.8.1. 數(shù)值型函數(shù)Ø MOD(n1,n2) 返回n1除n2的余數(shù)，如果n2=0則返回n1的值。若其中任何一個(gè)參數(shù)為null,則結(jié)果為null例如：SELECTMOD(24,5)FROM DUAL;Ø ROUND(n1,n2) 返回四舍五入小數(shù)

36、點(diǎn)右邊n2位后n1的值，n2缺省值為0，如果n2為負(fù)數(shù)就舍入到小數(shù)點(diǎn)左邊相應(yīng)的位上；例如：SELECTROUND(23.56),ROUND(23.56,1),ROUND(23.56,-1)FROM DUAL;Ø TRUNC(n1,n2 返回截尾到n2位小數(shù)的n1的值，n2缺省設(shè)置為0，當(dāng)n2為缺省設(shè)置時(shí)會(huì)將n1截尾為整數(shù)，如果n2為負(fù)值，就截尾在小數(shù)點(diǎn)左邊相應(yīng)的位上。例如：SELECTTRUNC(23.56),TRUNC(23.56,1),TRUNC(23.56,-1)FROM DUAL;Ø CEIL(x)返回大于等于x的最小整數(shù)，F(xiàn)LOOR(x)返回小于等于x的最大整數(shù)。

37、例如：selectceil(24.35),floor(24.35)from dual;Ø 其他函數(shù)：COS、COSH、SIN、ASIN、POWER、LOG、SIN、SINH、SQRT、TAN、TANH、ACOS、ASIN、ATAN、ATAN2。3.8.2. 字符型函數(shù)Ø LOWER(c)：轉(zhuǎn)換成小寫, UPPER(c)：轉(zhuǎn)換成大寫，INITCAP（c）：首字母大寫例如：selectupper('abd'),lower('DSC'),initcap('dsf')from dual;Ø TRIM(LEADING|TRAI

38、LING|BOTH c2 FROM c1) 例如：Selecttrim('a'from'abcde')from dual;Selecttrim(' sbhd ')from dual;Ø LTRIM(c1,c2)、RTRIM(c1,c2)及上同，不過方向相反例如：selectltrim('ababaa','a')from dual;selectrtrim('ababaa','a')from dual;Ø REPLACE(c1,c2,c3) 將c1字符串中的c2替換為

39、c3，如果c3為空，則從c1中刪除所有c2。例如：selectreplace('abcde','a','A')testfrom dual;Ø SUBSTR(c1,n1,n2) 截取指定長度的字符串。Ø INSTR(c1,c2,n1,n2) 返回c2在c1中位置c1:原字符串, c2:要尋找的字符串, n1:查詢起始位置，正值表示從左到右，負(fù)值表示從右到左 (大小表示位置,比如3表示左面第3處開始,-3表示右面第3處開始)。,n2:第幾個(gè)匹配項(xiàng)。例如：selectinstr(full_name,'_',1,2)a

40、1,instr(full_name,'_',1,3)a2,substr(full_name,instr(full_name,'_',1,2)+1,instr(full_name,'_',1,3)-instr(full_name,'_',1,2)-1),a.* from CMIS.CM_DISTRICTawhere city_id ='s'LENGTH(c) 返回指定字符串的長度。例如：selectlength('abc'),length('abc '),length('成都T

41、O_CHAR')from dual;Ø CONCAT(char1,char2)連接函數(shù),及|操作符的作用一樣例如：select concat(acd,bgc) from dual; Select acd|bgc from dual;3.8.3. 日期函數(shù)Ø ADD_MONTHS() 返回指定日期月份+n之后的值，n可以為任何整數(shù)。例如：SELECTADD_MONTHS(SYSDATE,3),ADD_MONTHS(SYSDATE,-3)FROM DUAL;Ø CURRENT_DATE 返回當(dāng)前session所在時(shí)區(qū)的默認(rèn)時(shí)間，會(huì)話的當(dāng)前日期和時(shí)間Ø

42、 SYSDATE 功能及上相同，返回當(dāng)前session所在時(shí)區(qū)的默認(rèn)時(shí)間。服務(wù)器（主機(jī)）的當(dāng)前日期和時(shí)間Ø LAST_DAY(d) 返回指定時(shí)間所在月的最后一天例如：SELECTLAST_DAY(SYSDATE)FROM DUAL;Ø NEXT_DAY(d,n) 返回指定日期后第一個(gè)n的日期，n為一周中的某一天。但是，需要注意的是n如果為字符的話，它的星期形式需要及當(dāng)前session默認(rèn)時(shí)區(qū)中的星期形式相同。例如：selectnext_day(sysdate,'星期一')FROM DUAL;Ø MONTHS_BETWEEN(d1,d2) 返回d1及

43、d2間的月份差，視d1,d2的值大小，結(jié)果可正可負(fù)，當(dāng)然也有可能為0.注意該函數(shù)接收一個(gè)日期函數(shù)，返回一個(gè)數(shù)字例如：SELECTMONTHS_BETWEEN('20-5月-15','10-1月-15')FROM DUAL;Ø ROUND(d,fmt) 前面講數(shù)值型函數(shù)的時(shí)候介紹過ROUND，此處及上功能基本相似，不過此處操作的是日期。如果不指定fmt參數(shù)，則默認(rèn)返回距離指定日期最近的日期。例如：SELECTROUND(SYSDATE,'HH24')FROM DUAL;Ø EXTRACT(DATE FROM DATETIME)

44、返回一個(gè)date類型中截取 year,month,day。例如：SELECTEXTRACT(YEARFROMSYSDATE)FROM DUAL;SELECTEXTRACT(hourFROMtimestamp'2019-10-5 14:25:38')FROM DUAL;3.8.4. 轉(zhuǎn)換函數(shù)Ø TO_CHAR()本函數(shù)又可以分三小類，分別是² 轉(zhuǎn)換字符->字符TO_CHAR(c);² 轉(zhuǎn)換時(shí)間（日期轉(zhuǎn)換成字符函數(shù)）->字符TO_CHAR(date,fmtparams)：將指定的時(shí)間按照指定格式轉(zhuǎn)換為varchar2類型；TO_CHAR(d

45、ate,fmtparams)參數(shù)說明：Date:將要轉(zhuǎn)換的日期Fmt:轉(zhuǎn)換的格式Params:日期的語言例如：SELECTTO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss')FROM DUAL;SELECT PAY_TIME,TO_CHAR(PAY_TIME,'fmdd month yyyy')as PAYTIME FROM CMIS_DY.DM_BAL_PAYMENT_201904;轉(zhuǎn)換數(shù)值->字符TO_CHAR(n,fmt)：將指定數(shù)值n按照指定格式fmt轉(zhuǎn)換為varchar2類型并返回；例如：SELECTTO_CHAR(-

46、100,'L99G999D99MI')FROM DUAL;SELECT PAY_FEE,TO_CHAR(PAY_FEE,'$999,999.00')PAYFEE FROM CMIS_DY.DM_BAL_PAYMENT_201904;注：9：顯示數(shù)字并忽略前面的00：顯示數(shù)字，位數(shù)不足，用0補(bǔ)齊.或D：顯示小數(shù)點(diǎn) ,或G：顯示千位符$：美元符號(hào)S：加正負(fù)號(hào)，（前后都可以）Ø TO_DATE(c,fmt,nls) 將char,nchar,varchar2,nvarchar2轉(zhuǎn)換為日期類型，如果fmt參數(shù)不為空，則按照fmt中指定格式進(jìn)行轉(zhuǎn)換。注意這里的fm

47、t參數(shù)。如果ftm為'J'則表示按照公元制(Julian day)轉(zhuǎn)換，c則必須為大于0并小于的正整數(shù)。Fx精確匹配例如：SELECTTO_DATE(,'J')FROM DUAL;SELECTTO_DATE('2019-8-23 23:25:00','yyyy-mm-dd hh24:mi:ss')FROM DUAL;注：to_date()按照系統(tǒng)默認(rèn)格式顯示日期Ø TO_NUMBER(c,fmt,nls) 將char,nchar,varchar2,nvarchar2型字串按照fmt中指定格式轉(zhuǎn)換為數(shù)值類型并返回。例如：S

48、ELECTTO_NUMBER('-100.00','9G999D99')FROM DUAL;3.8.5. 其它輔助函數(shù)Ø DECODE(exp,s1,r1,s2,r2.s,r,def) 可以把它理解成一個(gè)增強(qiáng)型的if else，只不過它并不通過多行語句，而是在一個(gè)函數(shù)內(nèi)實(shí)現(xiàn)if else的功能。Ø GREATEST(n1,n2,.n) 返回序列中的最大值Ø LEAST(n1,n2.n) 返回序列中的最小值例如：SELECTGREATEST(2,5,12,3,16,8,9)AFROM DUAL;SELECTLEAST (2,5,12,

49、3,16,8,9)AFROM DUAL;Ø NULLIF(c1,c2) Nullif也是個(gè)很有意思的函數(shù)。邏輯等價(jià)于：CASE WHEN c1 = c2 THEN NULL ELSE c1 ENDØ NVL(c1,c2) 邏輯等價(jià)于IF c1 is null THEN c2 ELSE c1 END。c1,c2可以是任何類型。如果兩者類型不同，則oracle會(huì)自動(dòng)將c2轉(zhuǎn)換為c1的類型。返回值的類型必須和c1類型一樣。Ø NVL2(c1,c2,c3) 大家可能都用到nvl，但你用過nvl2嗎？如果c1非空則返回c2,如果c1為空則返回c3例如：SELECTnulli

50、f(888,888)AFROM DUAL;SELECTnvl(98,888)AFROM DUAL;SELECTnvl2(909,888,987)AFROM DUAL;Ø COALESCE(expr1,expr2, exprn)返回列表中非NULL的第一個(gè)表達(dá)式，若前面列表中的表達(dá)式都為空，則返回null,所有表達(dá)式的類型必須一致。3.8.6. 多重分組分析函數(shù)（1） 語法:sum(col1) over(partition by col2 order by col3 )例：create table tb1(dept_id number(5),ename varchar(10),sal

51、varchar(100);insert into tb1(dept_id,ename,sal)values('1000','A','2500');insert into tb1(dept_id,ename,sal)values('1000','B','3500');insert into tb1(dept_id,ename,sal)values('1000','C','1500');insert into tb1(dept_id,ename,sal)v

52、alues('1000','D','2000');insert into tb1(dept_id,ename,sal)values('2000','G','3500');-以dept_id 分區(qū)，并以sal排序，作為tatal列顯示出來，并計(jì)算出相同dept_id的sal，以total列顯示。select DEPT_ID,ENAME,SAL,sum(SAL) over(partition by dept_id order by SAL) AS TOTAL from tb1; -以ename排序，統(tǒng)計(jì)

53、sal列求和并以total顯示出來select DEPT_ID,ENAME,SAL,sum(SAL) over(order by ENAME) AS TOTAL from tb1;-有partition by無order by: 實(shí)現(xiàn)分組內(nèi)所有數(shù)據(jù)的匯總SELECT dept_id, ename, sal, SUM (sal) OVER (PARTITION BY dept_id) AS total FROM tb1;（2） 語法：rank() over (partition by 分組字段 order by 排序字段順序),排序的結(jié)果是不連續(xù)的，如果有4個(gè)人，其中有3個(gè)是并列第1名，那么最后

54、的排序結(jié)果結(jié)果如：1 1 1 4例：-統(tǒng)計(jì)前面ename列的sal列的和并顯示在toptotal列中1、SELECT SNO,CNO,SCORE,RANK()OVER(ORDERBY SCORE DESC) ORDERBYSCOREFROM CMIS_DY.SC;2、select dept_id,ename,sal,toptotal,rank() over (order by toptotal) gfrom(SELECT dept_id, ename, sal, SUM (sal) OVER (ORDER BY ename,sal) toptotal FROM tb1ORDER BY enam

55、e);（3）、語法dense_rank()over(order by 列名排序)，排序的結(jié)果是連續(xù)的，如果有4個(gè)人，其中有3個(gè)是并列第1名，那么最后的排序結(jié)果如：1 1 1 2SELECT SNO,CNO,SCORE,dense_rank()OVER(ORDERBY SCORE DESC) ORDERBYSCOREFROM CMIS_DY.SC;-語法：rank() over (order by 排序字段順序)- rank() over (partition by 分組字段 order by 排序字段順序)-runk() over(partition by 分組字段 order by 排序字段順序 nulls last) 如果排序字段為null，可能造成在排序時(shí)將null字段排在最前面，影響排序的正確性。所以建議將dense_rank()over(order by 列名排序)改為dense_rank()over(order by 列名排序 nulls last)函數(shù)詳見oracle函數(shù)大全.chm3.9. oracle數(shù)據(jù)庫多表查詢3.9.1. 笛卡爾積查詢即將一個(gè)表的每一行及另外一個(gè)表的每一行組合在一起。會(huì)生成大量通常無用的行。一般情況通過鏈接條件避免笛