Oracle學(xué)習(xí)之總結(jié)

一、學(xué)會(huì)操作庫

首先在命令行：輸入sqlplus以sysassysdba登錄后操作

（在unix下先輸入suoracle切換到Oracle后，再輸入sqplus同上）1、怎樣創(chuàng)建一個(gè)庫？命令如下：//創(chuàng)建表空間CreatetablespaceNMJYdatafile"D:\\app\\Administrator\\product\\11.2.0\\dbhome_1/oradata/NMJY.dbf"size200Mreuseautoextendonnext1280Kmaxsizeunlimited;//創(chuàng)建用戶并設(shè)置默認(rèn)表空間

createuserNMJYidentifiedbynmjy201*defaulttablespaceNMJYquota10monusers;//授權(quán)

grantconnect,resource,dbatoNMJY;

grantsysdbatoNMJY;//此處授予最高權(quán)限commit;//提交操作2、怎樣刪除一個(gè)庫？命令如下：//刪表空間

droptablespacelvyongincludingcontents;//刪用戶

dropusernmjycascade;3、怎樣備份和還原庫？

創(chuàng)建目錄語句：CREATEDIRECTORYdumdiras‘d:dump’;下面以備份和還原unix下的nmjy庫為例：--切換到oracle（windows下不需切換）su-oracle

--備份（此處路徑名是自己創(chuàng)建的dumdir，也可以自己直接輸入指定路徑）

expdpnmjy/nmjy201*@orclschemas=nmjyDIRECTORY=dumdirdumpfile=nmjy11117.dmp;

【說明：命令（expdp）庫（nmjy）/密碼（nmjy201*）@服務(wù)器（orcl）shemas=用戶（nmjy）（DIRECTORY）=dumdirdumpfile=文件名（nmjy11117.dmp）】

--還原

Impdpnmjy/nmjy201*@orclDIRECTORY=dumdirDUMPFILE=NMJY11117.DMP【說明同上】

二、怎樣從sqlserver導(dǎo)入表到oracle

在此借助開發(fā)工具VisualStudio201*：

1、打開VisualStudio201*,在工具欄中找到“連接到數(shù)據(jù)庫”，點(diǎn)擊后添加一個(gè)Oracle庫的連接，

在服務(wù)資源管理器中找到此連接，，右擊點(diǎn)‘導(dǎo)入表…’

路徑彈出如下頁面：

點(diǎn)新建一個(gè)連接，之后：

然后更改連接，選擇SqlServer,如下：

確定后，根據(jù)向?qū)�向后操作，到如下頁�?/p>

選擇要導(dǎo)入的表，防止和Oracle庫的已存在的表名沖動(dòng)，統(tǒng)一在表名后加了個(gè)1，下一步檢查列類型，

把NCLOB類型轉(zhuǎn)為varchar2(4000)，節(jié)省不需要的空間，然后點(diǎn)完成即導(dǎo)入。之后再通過PLSQL工具到把帶1的表中數(shù)據(jù)導(dǎo)入到對應(yīng)表中

注：Oracle是嚴(yán)格區(qū)分大小寫的,凡牽扯到用戶ID之類的務(wù)必用Upper()轉(zhuǎn)換后導(dǎo)入。

說明：在Oracle庫之間導(dǎo)入數(shù)據(jù)的時(shí)候，也可用此辦法。

三、SqlServer和Oracle語句編碼比較

經(jīng)過這一段時(shí)間的Oracle版本程序和數(shù)據(jù)庫的修改，總結(jié)如下：

1、在后臺(tái)C#代碼中拼Sql語句執(zhí)行的時(shí)候，用begin…end，中間是需執(zhí)行語句且以分號結(jié)尾2、凡是關(guān)于用戶ID查詢的，請轉(zhuǎn)換成大寫后再執(zhí)行查詢

3、新增用戶或子用戶的功能，也要注意把用戶ID轉(zhuǎn)成大寫后再插入數(shù)據(jù)庫4、在SqlServer中的字符串連接是用+，Oracle中的字符串連接是用||

5、SqlServer中的字符串轉(zhuǎn)換函數(shù)可以用convert、cast，Oracle中是用to_char、cast,為了通用，建議平時(shí)養(yǎng)成用cast的習(xí)慣

6、讀取前10行，SqlServer中是用selecttop10fromtable，Oracle是用select*fromtablewhererownum

擴(kuò)展閱讀：有關(guān)Oracle學(xué)習(xí)總結(jié)

表xyzabc1mhj1mhjk1njk2uwe2uwert3qs4ads4adsaa

怎么刪掉第1、5、8條記錄？（字段a、b相同的幾條記錄只留一條，留下字段C較短的一條）

DELETEFROMLIANXI

WHERELENGTH(C)NOTIN(SELECTMIN(LENGTH(C))FROMLIANXIGROUPBYA,B)

oracle數(shù)據(jù)庫常用的命令集錦

今日開始研究oracle,搜索到了一個(gè)好東東，拿出來與大家一同分享。下面是摘抄的部分：[local]2[/local]

ORACLE相關(guān)語法及命令一、Oracle入門理論知識：

Oracle的物理組件有三個(gè)：

(1)數(shù)據(jù)文件數(shù)據(jù)文件是用于存儲(chǔ)數(shù)據(jù)庫數(shù)據(jù)的文件，如表、索引數(shù)據(jù)。每個(gè)Oracle數(shù)據(jù)庫有一個(gè)或多個(gè)物理數(shù)據(jù)文件，一個(gè)數(shù)據(jù)文件只能與一個(gè)數(shù)據(jù)庫關(guān)聯(lián)。(2)日志文件用于記錄對數(shù)據(jù)庫進(jìn)行的修改信息，日志文件主要用于在數(shù)據(jù)庫出現(xiàn)故障時(shí)實(shí)施數(shù)據(jù)庫恢復(fù)。

(3)控制文件控制文件是記錄數(shù)據(jù)庫物理結(jié)構(gòu)的二進(jìn)制文件，每個(gè)Oracle數(shù)據(jù)庫都含有一個(gè)控制文件。

Oracle的邏輯組件：

表空間(TableSpace)表空間是數(shù)據(jù)庫最大的邏輯單位，一個(gè)數(shù)據(jù)庫至少包含一個(gè)表空間，一個(gè)表空間包含一個(gè)或多個(gè)段等等。段(Segment)段存在于表空間中，分成4類，數(shù)據(jù)段、索引段、回退段、臨時(shí)段。區(qū)(Extent)區(qū)是磁盤空間分配最小單位，由連續(xù)的數(shù)據(jù)塊組成，一個(gè)或多個(gè)區(qū)構(gòu)成段，區(qū)只能存在于一個(gè)數(shù)據(jù)文件中。

數(shù)據(jù)塊(DataBlock)數(shù)據(jù)塊是數(shù)據(jù)庫中最小的數(shù)據(jù)組織單位與管理單位，Oracle數(shù)據(jù)庫中的數(shù)據(jù)存儲(chǔ)于數(shù)據(jù)塊中，取值范圍2K-64K之間。

模式(schema)模式是對用戶所創(chuàng)建的數(shù)據(jù)庫對象的總稱，又稱為用戶模式。概念：

內(nèi)存Oracle內(nèi)存結(jié)構(gòu)包含以下兩個(gè)內(nèi)存區(qū)。

1、系統(tǒng)全局區(qū)(SGA)實(shí)例啟動(dòng)時(shí)分配該內(nèi)存區(qū)，是Oracle實(shí)例的一個(gè)基本組件。又稱為共享全局區(qū)，它用來存儲(chǔ)數(shù)據(jù)庫信息，并由多個(gè)數(shù)據(jù)庫進(jìn)程共享�？煞譃楣蚕沓�、數(shù)據(jù)緩沖區(qū)及日志緩沖區(qū)。

(1)共享池是對SQL、PL\\SQL程序進(jìn)行語法分析、編譯、執(zhí)行的內(nèi)存區(qū)域。共享池由庫緩存和數(shù)據(jù)字典緩存組成。其中，庫緩存含有最近執(zhí)行的SQL、PL\\SQL語句的分析碼和執(zhí)行計(jì)劃；數(shù)據(jù)字典緩存含有從數(shù)據(jù)字典中得到的表、索引、列定義和權(quán)限等信息。(2)數(shù)據(jù)緩沖區(qū)數(shù)據(jù)緩沖區(qū)用于存儲(chǔ)從磁盤數(shù)據(jù)文件中讀入的數(shù)據(jù)，所有用戶共享。(3)日志緩沖區(qū)日志記錄數(shù)據(jù)庫的所有修改信息，主要用于恢復(fù)數(shù)據(jù)。

2、程序全局區(qū)(PGA)服務(wù)器進(jìn)程啟動(dòng)時(shí)分配該內(nèi)存區(qū)。PGA為非共享區(qū)，只能單個(gè)進(jìn)程使用，當(dāng)一個(gè)用戶會(huì)話結(jié)束后，PGA釋放。

用戶進(jìn)程(PGA)發(fā)送SQL語句到共享全局區(qū)(SGA)，先在共享池的庫緩存中查詢是否存在所需的數(shù)據(jù)塊，如果存在就在數(shù)據(jù)字典中讀取相應(yīng)的數(shù)據(jù)塊，如果不存在就由服務(wù)器進(jìn)程(DBWR)來IO數(shù)據(jù)庫

語法知識：

創(chuàng)建表空間的語法如下：

CREATETABLESPACEtablespacenameDATAFILE"d:\\filename.DBF"[SIZEint[KB|MB]][AUTOEXTEND[OFF|ON]];

tablespacename是需創(chuàng)建的表空間名稱。

DATAFILE指定組成表空間的一個(gè)或多個(gè)數(shù)據(jù)文件，當(dāng)有多個(gè)數(shù)據(jù)文件時(shí)使用逗號分隔。filename是表空間中數(shù)據(jù)文件的路徑和名稱。

SIZE指定文件的大小，用K指定千字節(jié)大小，用M指定兆字節(jié)大小。AUTOEXTEND子句用來啟用或禁用數(shù)據(jù)文件的自動(dòng)擴(kuò)展。Oracle默認(rèn)用戶：

用戶名：sys默認(rèn)密碼：chage_on_install用來管理擁有Oracle數(shù)據(jù)字典文件用戶名：system默認(rèn)密碼：manager用來管理擁有數(shù)據(jù)字典視圖對象用戶名：scott默認(rèn)密碼：tiger示例用戶，包括emp、dept等表連接Oracle:在控制臺(tái)下輸入sqlplus用戶名/密碼回車或sqlplusw回車相關(guān)命令：

disconn//退出當(dāng)前登錄conn用戶名/密碼//連接Oracle

alteruser用戶名identifiedby密碼//修改用戶口令dropuser用戶名cascade;//刪除用戶

alteruser用戶名accountlock;//給某個(gè)用戶加鎖alteruser用戶名accountunlock;//給某個(gè)用戶解鎖ed回車://打開緩沖區(qū)

/回車://執(zhí)行緩沖區(qū)中的語句

createuser用戶名identifiedby密碼[passwordexpire]

[defaulttablespace表空間名][temporarytablespace臨時(shí)表空間名];//創(chuàng)建用戶相關(guān)權(quán)限：

grantconnecttoscott;//connect角色將允許用戶創(chuàng)建數(shù)據(jù)庫并在數(shù)據(jù)庫中創(chuàng)建表或其他對象grantresourcetoscott;//resource角色將允許用戶使用數(shù)據(jù)庫中的空間grantcreatesequencetoscott;//createsequence權(quán)限將允許用戶創(chuàng)建序列，此權(quán)限包含在connect連接角色中

grantselectonemptoscott;//將emp表的查詢權(quán)限授予用戶scott

grantupdate(vencode,venname)on表名toscott;//將特定列的更新權(quán)限授予用戶scottgrant權(quán)限on表名to用戶名withgrantoption;//接受該權(quán)限的用戶可以將此權(quán)限授予其他用戶

revokeselect,updateon表名from用戶名;//收回相應(yīng)的權(quán)限二、SQL查詢和SQL函數(shù)SQL支持如下類別的命令：

數(shù)據(jù)定義語言：create(創(chuàng)建)、alter(更改)、drop(刪除)和truncate(截?cái)?命令。數(shù)據(jù)操縱語言：insert(插入)、select(選擇)、delete(刪除)和update(更新)命令。事務(wù)控制語言：commit(提交)、savepoint(保存點(diǎn))和rollback(回滾)命令。數(shù)據(jù)控制語言：grant(授予)和revoke(回收)命令。數(shù)據(jù)類型：

char:長度在1到201*個(gè)字節(jié)，聲明多少字節(jié)在內(nèi)存中就占用多少字節(jié)，輸入的值小于指定的長度時(shí)用空格填充。

varchar2:長度在1到4000個(gè)字節(jié)，輸入的值是多少字節(jié)，就占用多少字節(jié)。

long:長度在2GB，設(shè)置為此類型的列時(shí)，要注意：一個(gè)表中只有一列可以為long類型，long類型列不能定義為唯一約束或主鍵約束，不能建立索引，過程或存儲(chǔ)過程不能接受long類型的參數(shù)。

number(p,s):其中p為精度，表示數(shù)字的總位數(shù)，在1至38之間。s為范圍，表示小數(shù)點(diǎn)右邊數(shù)字的位數(shù)，在-84至127之間。

date:日期類型，sysdate為當(dāng)前系統(tǒng)時(shí)間。格式為08-9月-07。

timestamp:用于存儲(chǔ)日期的年、月、日以及時(shí)間的時(shí)、分和秒。其中秒精確到小數(shù)點(diǎn)后6位，

systimestamp返回當(dāng)前日期、時(shí)間。格式為08-9月-0704.08.30.000000下午。

raw:此數(shù)據(jù)類型用于存儲(chǔ)基于字節(jié)的數(shù)據(jù)，如二進(jìn)制數(shù)據(jù)或字節(jié)串，該類型最多能存儲(chǔ)201*個(gè)字節(jié)，可以建立索引。

longraw:此數(shù)據(jù)類型用于可變長度的二進(jìn)制數(shù)據(jù)，最多能存儲(chǔ)2GB。long數(shù)據(jù)類型的所有限制對longraw數(shù)據(jù)類型也同樣有效。

lob又稱為"大對象"數(shù)據(jù)類型，最多能存儲(chǔ)4GB的非結(jié)構(gòu)化信息。包括：

clob:clob代表CharacterLOB(字符LOB)，它能存儲(chǔ)大量字符數(shù)據(jù)。如XML文檔。blob:blob代表BinaryLOB(二進(jìn)制LOB)，它能存儲(chǔ)較大的二進(jìn)制對象，如圖形、視頻剪輯和聲音剪輯。

bfile:bfile代表BinaryFile(二進(jìn)制文件)，它能夠?qū)⒍�進(jìn)制文件存儲(chǔ)在數(shù)據(jù)庫外部的操作系統(tǒng)文件中。偽列：

rowid:selectrowid,ename,fromscott.empwhereempno="7900";

rownum:select*fromscott.empwhererownumaltertable表名dropcolumn列名;//刪除列

truncatetable表名;//中刪除記錄而不刪除結(jié)構(gòu)，不使用事務(wù)處理，因此無法回滾droptable表名;//刪除表及其全部數(shù)據(jù)

createtable新表名asselect*from表名where1=2;//用現(xiàn)有的表創(chuàng)建一個(gè)新表selectdeptno*2"NewNo",dname,locfromdept;//指定一個(gè)含有特殊字符(如空格)的列標(biāo)題

commit;//提交事務(wù)

savepoint標(biāo)記名;//標(biāo)記事務(wù)點(diǎn)rollback;//回滾整個(gè)事務(wù)處理

rollbackto[savepoint]標(biāo)記名;//回滾到事務(wù)中某個(gè)特定的保存點(diǎn)集合操作符：

union(聯(lián)合):此操作符返回兩個(gè)查詢選定的所有不重復(fù)的行。

語法selectordernofromorder_masterUNIONselectordernofromorder_detail;unionall(聯(lián)合所有):此操作符合并兩個(gè)查詢選定的所有行，包括重復(fù)的行。

語法：selectorderno,enamefromorder_masterUNIONALLselectorderno,pronamefromorder_detailorderby2;

注意：在兩個(gè)select語句中指定的列名不必相同，但數(shù)據(jù)類型必須匹配。也可以對聯(lián)合查詢的結(jié)果進(jìn)行排序，使用OrderBy子句時(shí)，它必須放在最后

一個(gè)select語句之后，而且必須指定列索引來排序，而不是指定列名，列索引是從1開始的整數(shù)。上述語法便是以proname的索引排序

intersect(交集):此操作符只返回兩個(gè)查詢都有的行。

語法：selectordernofromorder_masterINTERSECTselectordernofromorder_detail;

minus(減集):此操作符中返回由第一個(gè)查詢選定但是第二個(gè)查詢中沒有選定的行，也就是在第一個(gè)查詢結(jié)果中排除第二個(gè)查詢結(jié)果中出現(xiàn)的行。

語法：selectordernofromorder_masterMINUSselectordernofromorder_detail;查詢尚未交付的訂單

連接(||)操作符：

語法：select("供應(yīng)商"||venname||"的地址是"||venadd1||""||venadd2||""||venadd3)地址fromvendor_masterwherevencode="V002";將多個(gè)字符串合并為一個(gè)字符串。SQL函數(shù)：

1、日期函數(shù)：

add_months:此函數(shù)返回給指定的日期加上指定的月數(shù)后的日期值。語法為add_months(d,n),其中d是日期，n表示月數(shù)。

示例：selectadd_months(sysdate,2)fromdual;將當(dāng)前時(shí)間加上2個(gè)月后的日期值。months_between:此函數(shù)返回兩個(gè)日期之間的月數(shù)。語法為months_between(d1,d2),其中d1和d2是日期，如果d1大于d2，則結(jié)果為正數(shù)；否則為負(fù)數(shù)。

last_day:此函數(shù)返回指定日期當(dāng)月的最后一天的日期值，語法為last_day(d),其中d表示日期。

示例：selectlast_day(sysdate)fromdual;返回當(dāng)前日期的月的最后一天，如果是9月就返回30-09月-07

round:此函數(shù)返回日期值，將日期四舍五入為格式模型指定的單位。語法為round(d,[fmt])。其中d是日期，fmt是格式模型。fmt是一個(gè)可選項(xiàng)，日期默認(rèn)舍入為最靠近的那一天。如果指定格式為年"Year"，則舍入到年的開始，即1月1日;如果格式為月"Month"，則舍入到月的第一日;如果格式為周"Day"，則舍入到最靠近的星期日。示例：selectround(sysdate,"month")fromdual;返回最接近的一個(gè)月。

next_day:此函數(shù)返回指定的下一個(gè)星期幾的日期。語法為next_day(d,day)。其中d表示日期，而day指周內(nèi)任何一天。

示例：selectnext_day(sysdate,"星期日")fromdual;返回下一個(gè)星期日的日期，也可以用1表示，以此類推，星期一以2表示。

trunc:此函數(shù)將指定日期截?cái)酁橛筛袷侥Ｐ椭付ǖ膯挝蝗掌�，與Round函數(shù)不同的是它只舍不入，語法為trunc(d,[fmt])，與round格式相同。

示例：selecttrunc(sysdate,"year")fromdual;返回當(dāng)前年的第一天，也就是1月1日。示例：selecttrunc(sysdate,"day")fromdual;返回緊靠前面的星期日。如果為"201*年1月27日"就會(huì)返回"201*年1月23日"。

extract:此函數(shù)提取日期時(shí)間類型中的特定部分。語法為extract(fmtfromd)，其中d是日期時(shí)間表達(dá)式，fmt是要提取的部分的格式。格式的取值可以是year,month,day,hour,minute,second,注意此處的格式不使用單引號。示例：selectextract(yearfromsysdate)fromdual;返回當(dāng)前的年份。2、字符函數(shù)：

initcap(char):首字母大寫，示例：selectinitcap("hello")fromdual;輸出結(jié)果:Hello。lower(char):轉(zhuǎn)換為小寫，示例：selectlower("FUN")fromdual;輸出結(jié)果:fun。upper(char):轉(zhuǎn)換為大寫，示例：selectupper("sun")fromdual;輸出結(jié)果:SUN。

ltrim(char,set):左剪裁，示例：selectltrim("xyzadams","xyz")fromdual;輸出結(jié)果:adams。rtrim(char,set):右剪裁，示例：selectrtrim("xyzadams","ams")fromdual;輸出結(jié)果:xyzad。translate(char,from,to):按字符翻譯，示例：selecttranslate("jack","abcd","1234")fromdual;輸出結(jié)果:j13k。

replace(char,search_str,replace_str):字符串替換，示例：selectreplace("jackandjue","j","bl")fromdual;輸出結(jié)果:blackandblue。

instr(char,substr[,pos1,pos2]):查找子字串位置。

示例：selectinstr("vorldwide","d")fromdual;輸出結(jié)果:5。pos1為可選，表示從第幾個(gè)位置查找。pos2為可選，表示從第幾次出現(xiàn)的位置找。substr(char,pos,len):取子字符串，示例：selectsubstr("abcdefg",3,2)fromdual;輸出結(jié)果:cd。concat(char1,char2):連接字符串，示例：selectconcat("Hello","world")fromdual;輸出結(jié)果:Helloworld。

chr:此函數(shù)根據(jù)Ascii碼返回對應(yīng)的字符，示例：selectchr(45788),chr(53671),chr(50167),chr(65)fromdual;輸出結(jié)果:曹學(xué)明A。

ascii:此函數(shù)返回GBK編碼值，示例：selectascii("曹")cao,ascii("學(xué)")xue,ascii("明")Mingfromdual;輸出結(jié)果:457885367150167。

lpad和rpad：示例：selectlpad("function",15,"=")fromdual;輸出結(jié)果:=======function。而rpad則相反，字符串填充在右邊。

trim:此函數(shù)從字符串的開頭或結(jié)尾(或開頭和結(jié)尾)剪裁特定的字符，默認(rèn)剪裁空格。如果加上leading選項(xiàng)時(shí)與ltrim函數(shù)相似。指定trailing時(shí)和rtrim函數(shù)相似。示例：selecttrim(9from999992598899)fromdual;輸出結(jié)果:25988。

示例：selecttrim(leading9from999992598899)fromdual;輸出結(jié)果:2598899。示例：selecttrim(trailing9from999992598899)fromdual;輸出結(jié)果:9999925988。

length:此函數(shù)返回字符串的長度，示例：selectlength("frances")fromdual;輸出結(jié)果:7。decode:示例：selectdeptno,dname,decode(loc,"NEWYORK","紐約","BOSTON","波士頓")fromscott.dept;此示例將替換顯示loc列的結(jié)果,結(jié)果為"NEWYORK"的替換為"紐約"，"BOSTON"的替換為"波士頓"。

GREATEST/least:返回一組表達(dá)式中的最大值/最小值,即比較字符的編碼大小.示例：selectgreatest("AA","AB","AC")fromdual;輸出結(jié)果:AC。selectleast("AA","AB","AC")fromdual;輸出結(jié)果:AA。selectgreatest("啊","安","天")fromdual;輸出結(jié)果:天。selectleast("啊","安","天")fromdual;輸出結(jié)果:啊。3、數(shù)字函數(shù)：

abs(n):取絕對值，示例：selectabs(-15)fromdual;輸出結(jié)果:15。ceil(n):向上取整，示例：selectceil(44.778)fromdual;輸出結(jié)果:45。sign(n):取符號，示例：selectsign(-2)fromdual;輸出結(jié)果:-1。

floor(n):向下取整，示例：selectfloor(200.88)fromdual;輸出結(jié)果:200。power(m,n):m的n次冪，示例：selectpower(5,3)fromdual;輸出結(jié)果:125。mod(m,n):取余數(shù)，示例：selectmod(10,3)fromdual;輸出結(jié)果:1。

round(m,n):四舍五入，示例：selectround(100.256,2)fromdual;輸出結(jié)果:100.26。trunc(m,n):截?cái)�，示例：selecttrunc(100.256,2)fromdual;輸出結(jié)果:100.25。sqrt(n):平方根，示例：selectsqrt(4)fromdual;輸出結(jié)果:2。4、轉(zhuǎn)換函數(shù)：

to_char(d|n[,fmt]):其中d是日期，n是數(shù)字，fmt指定日期或數(shù)字的格式。

示例：selectto_char(sysdate,"yyyy"年"fmmm"月"fmdd"日"hh24:mi:ss")fromdual;輸出結(jié)果:201*年9月09日20:44:27。

selectto_char(sysdate,"yyyy/mm/ddhh24:mi:ss")fromdual;selectto_char(sysdate,"yyyy-mm-ddhh24:mi:ss")fromdual;

selectto_char(sysdate,"yyyy"年"mm"月"dd"日"hh24"時(shí)"mi"分"ss"秒"")fromdual;示例：selectto_char(sal,"$99999")fromemp;輸出結(jié)果:$1600。

to_date(char[,fmt]):此函數(shù)將char或varchar2數(shù)據(jù)類型轉(zhuǎn)換為日期數(shù)據(jù)類型。示例：selectto_date("201*-12-06","yyyy-mm-dd")fromdual;輸出結(jié)果:06-12月-05。selectto_date("201*/08/1020:08:08","yyyy/mm/ddhh24:mi:ss")fromdual;selectto_date("201*-08-1020:08:08","yyyy/mm/ddhh24:mi:ss")fromdual;

selectto_date("201*年08月10日20時(shí)08分08秒","yyyy"年"mm"月"dd"日"hh24"時(shí)"mi"分"ss"秒"")fromdual;

to_number(char):此函數(shù)將包含數(shù)字的字符串轉(zhuǎn)換為number數(shù)據(jù)類型，通常不用這么做，因?yàn)镺racle可以對數(shù)字字符串進(jìn)行隱式轉(zhuǎn)換。

示例：selectsqrt(to_number("100"))fromdual;輸出結(jié)果:10。5、其它函數(shù)：

nvl(expression1,expression2):如果expression1為NULL，則nvl返回expression2。

nvl2(expression1,expression2,expression3):如果expression1不是NULL，則nvl2返回expression2，如果expression1是NULL，則返回expression3。nullif(expr1,expr2):此函數(shù)比較兩個(gè)表達(dá)式，如果它們相等，則返回空值，否則返回expr1。nullif函數(shù)等價(jià)于以下的case表達(dá)式：

casewhenexpr1=expr2thennullelseexpr1end6、分組函數(shù)：

avg:此函數(shù)返回指定列值的平均值，示例：selectavg(sal)fromemp;輸出結(jié)果:2073.21429。min:此函數(shù)返回指定列值的最小值，示例：selectmin(sal)fromemp;輸出結(jié)果:800。max:此函數(shù)返回指定列值的最大值，示例：selectmax(sal)fromemp;輸出結(jié)果:5000。sum:此函數(shù)返回指定列值的總和，示例：selectsum(sal)fromemp;輸出結(jié)果:29025。count:此函數(shù)是為了計(jì)算行數(shù)，它可以接受3種不同的參數(shù)示例：selectcount(*)fromemp;輸出結(jié)果:14。示例：selectcount(列名)from表名;

示例：selectcount(distinct列名)from表名;

groupby:此子句用于將信息表劃分為組，按組進(jìn)行聚合運(yùn)算。select后面跟的列名只能是分組函數(shù)、groupby子句中出現(xiàn)的列或表達(dá)式。

示例：selectdeptno,max(sal)fromempgroupby(deptno);查出每個(gè)部門的最高工資。示例：selectdeptno,count(*),sum(sal)fromscott.empgroupbydeptno;//groupby主要用來對一組數(shù)進(jìn)行統(tǒng)計(jì)

having:此子句用來指定groupby子句的檢索條件。

示例：selectdeptno,count(*),sum(sal)fromscott.empgroupbydeptnohavingcount(*)>=5;//having對分組統(tǒng)計(jì)再加限制條件

7、分析函數(shù)：只能出現(xiàn)在select列表或orderby子句中。

row_number:下面對所有員工的工資進(jìn)行排名，即使工資相同，其排名也不能相同。示例：selectename,job,deptno,sal,row_number()over(orderbysaldesc)as排名fromscott.emp;

下面對所有員工的工資按部門進(jìn)行排名，即使工資相同，排名也不同。

示例：selectename,job,deptno,sal,row_number()over(partitionbydeptnoorderbysaldesc)as排名fromscott.emp;

rank:此函數(shù)計(jì)算一個(gè)值在一組值中的排位，排位是以1開頭的連續(xù)整數(shù)，如果兩行的序數(shù)為1，則沒有序數(shù)2，下行的序數(shù)為3。

下面根據(jù)員工的工資和傭金對員工在每個(gè)部門中進(jìn)行排位。相同的工資排位相同，并且排位不連續(xù)。

示例：selectename,sal,comm,deptno,rank()over(partitionbydeptnoorderbysaldesc,comm)排名fromscott.emp;

dense_rank:此函數(shù)計(jì)算一個(gè)行在一組有序行中的排位，排位是以1開頭的連續(xù)整數(shù)，具有相同值的排位相同，并且排位是連續(xù)的。

下面首先選擇所有在accounting或research部門中工作的員工的部門名稱、員工姓名和工資，

然后分別計(jì)算每個(gè)員工的工資在部門中的排位，相等的工次排位相同示例：selectd.dname,e.ename,e.sal,dense_rank()over(partitionbye.deptnoorderbye.saldesc)排名

fromempe,deptdwheree.deptno=d.deptno;

三、鎖和表分區(qū)

鎖定是數(shù)據(jù)庫用來控制共享資源并發(fā)訪問的機(jī)制。

Oracle提供以確保在多用戶環(huán)境下數(shù)據(jù)的完整性和一致性。鎖的兩種級別：(只有在提交或回滾后才能釋放鎖定)

(1)行級鎖：是一種排他鎖，防止其他事務(wù)修改此行，但是不會(huì)阻止讀取此行的操作。在使用Insert、Update、Delete和Select...ForUpdate等語句時(shí)，Oracle會(huì)自動(dòng)應(yīng)用行級鎖定。Select...ForUpdate語法為：

Select...ForUpdate[OFcolumn_list][WAITn|NOWAIT]

其中：OF子句用于指定即將更新的列，即鎖定行上的特定列。WAIT子句指定等待其他用戶釋放鎖的秒數(shù)，防止無限期的等待。NOWAIT為不等待。示例1：演示如何鎖定deptno值為10的所有行。

select*fromdeptwheredeptno=10forupdateofdname,loc;

(2)表級鎖：將保護(hù)表數(shù)據(jù)，在事務(wù)處理過程中，表級鎖會(huì)限制對整個(gè)表的訪問。表級鎖用來限制對表執(zhí)行添加、更新和刪除等修改操作。語法：

LOCKTABLEINMODE[NOWAIT];其中：table_name是要被鎖定的表的名稱。lock_mode是鎖定的模式。表級鎖的模式：

1、行共享(ROWSHARE,RS)：允許其他用戶訪問和鎖定該表，但是禁止排他鎖鎖定整個(gè)表。

2、行排他(ROWEXCLUSIVE,RX)：與行共享模式相同，同時(shí)禁止其他用戶在此表上使用共享鎖。使用Select...Forupdate語句會(huì)自動(dòng)應(yīng)用行排他。

3、共享(SHARE,S)：共享鎖將鎖定表，僅允許其他用戶查詢表中的行，但不允許插入、更新或刪除行。多個(gè)用戶可以同時(shí)在同一張表中放置共享鎖即允許資源共享。但是這樣極容易造成死鎖。

4、共享行排他(SHAREROWEXCLUSIVE,SRX)：執(zhí)行比共享表鎖更多的限制。防止其他事務(wù)在表上應(yīng)用共享鎖、共享行排他鎖以及排他鎖。

5、排他(EXCLUSIVE,X)：對表執(zhí)行最大限制。除了允許其他用戶查詢該表的記錄，排他鎖防止其他事務(wù)對表做任何更改或在表上應(yīng)用任何類型的鎖。示例2：演示如何以共享模式鎖定表。locktabledeptinsharemodenowait;表分區(qū)的優(yōu)點(diǎn)：

改善表的查詢性能;表更容易管理;便于備份和恢復(fù);提高數(shù)據(jù)安全性。注意：要分區(qū)的表不能具有Long和LongRaw數(shù)據(jù)類型的列。四種分區(qū)方法：

1、范圍分區(qū)：根據(jù)表的某個(gè)列或一組列的值范圍，決定將該數(shù)據(jù)存儲(chǔ)在哪個(gè)分區(qū)上。語法如下：

在CreateTable語句后增加

PARTITIONBYRANGE(column_name)(

PARTITIONpart1VALUELESSTHAN(range1)[TABLESPACEtbs1],PARTITIONpart2VALUELESSTHAN(range2)[TABLESPACEtbs2],....

PARTITIONpartNVALUELESSTHAN(MAXVALUE)[TABLESPACEtbsN]);

其中：column_name是以其為基礎(chǔ)創(chuàng)建范圍分區(qū)的列，特定行的該列值稱為分區(qū)鍵。part1...partN是分區(qū)的名稱。

range1...MAXVALUE是分區(qū)的邊界值。

tbs1...tbsN是分區(qū)所在的表空間，TABLESPACE子句是可選項(xiàng)。示例3：

createtablet_emp(

empnonumber(4),enamevarchar2(30),salnumber)

partitionbyrange(empno)(

partitione1valueslessthan(1000)tablespaceemp1,也可以than(to_date("201*-01-01","yyyy-mm-dd"))

partitione2valueslessthan(201*)tablespaceemp2,也可以than(to_date("201*-01-01","yyyy-mm-dd"))

partitione3valueslessthan(maxvalue)tablespaceemp3);

2、散列分區(qū)：語法有兩種如下

PARTITIONBYHASH(column_name)

PARTITIONSnumber_of_partitions[STOREIN(tablespace_list)];或

PARTITIONBYHASH(column_name)(

PARTITIONpart1[TABLESPACEtbs1],PARTITIONpart2[TABLESPACEtbs2],...

PARTITIONpartN[TABLESPACEtbsN]);

其中：column_name是以其為基礎(chǔ)創(chuàng)建散列分區(qū)的列。

number_of_partitions是散列分區(qū)的數(shù)目，使用這種方法系統(tǒng)會(huì)自動(dòng)生成分區(qū)的名稱。tablespace_list指定分區(qū)使用的表空間，如果分區(qū)數(shù)目比表空間的數(shù)目多，分區(qū)將會(huì)以循環(huán)的方式分配到表空間中。part1...partN是分區(qū)的名稱。

tbs1...tbsN是分區(qū)所在的表空間，TABLESPACE子句是可選項(xiàng)。示例4：自動(dòng)分配4個(gè)散列分區(qū)，

可以使用selectpartition_name,HIGH_VALUEfromuser_tab_partitionswheretable_name=upper("t_emp")查詢分區(qū)名createtablet_emp(

empnonumber(4),enamevarchar2(30),salnumber)

partitionbyhash(empno)partitions4;

3、復(fù)合分區(qū)：是范圍分區(qū)和散列分區(qū)的結(jié)合。在創(chuàng)建復(fù)合分區(qū)時(shí)，先根據(jù)范圍對數(shù)據(jù)進(jìn)行分區(qū)，然后在這些分區(qū)內(nèi)創(chuàng)建散列子分區(qū)。語法如下：

PARTITIONBYRANGE(column_name1)SUBPARTITIONBYHASH(column_name2)

SUBPARTITIONSnumber_of_partitions[STOREIN(tablespace_list)](

PARTITIONpart1VALUELESSTHAN(range1)[TABLESPACEtbs1],PARTITIONpart2VALUELESSTHAN(range2)[TABLESPACEtbs2],....

PARTITIONpartNVALUELESSTHAN(MAXVALUE)[TABLESPACEtbsN]);

其中：column_name1是以其為基礎(chǔ)創(chuàng)建范圍分區(qū)的列。column_name2是以其為基礎(chǔ)創(chuàng)建散列分區(qū)的列。number_of_partitions是要?jiǎng)?chuàng)建的子分區(qū)的數(shù)目。part1...partN是分區(qū)的名稱。

range1...MAXVALUE是范圍分區(qū)的邊界值

示例5：將雇員表先按照雇傭時(shí)間hiredate進(jìn)行了范圍分區(qū)，然后再把每個(gè)分區(qū)分為2個(gè)子hash分區(qū)，此表一共是6個(gè)分區(qū)。createtablet_emp(

empnonumber(4),enamevarchar2(30),hiredatedate)

partitionbyrange(hiredate)subpartitionbyhash(empno)subpartitions2(

partitione1valueslessthan(to_date("201*0501","YYYYMMDD")),partitione2valueslessthan(to_date("201*1001","YYYYMMDD")),partitione3valueslessthan(maxvalue));

4、列表分區(qū)：此分區(qū)允許用戶明確地控制行到分區(qū)的映射。語法如下：

PARTITIONBYLIST(column_name)(

PARTITIONpart1VALUES(values_list1),PARTITIONpart2VALUES(values_list2),....

PARTITIONpartNVALUES(DEFAULT));

其中：column_name是以其為基礎(chǔ)創(chuàng)建列表分區(qū)的列。part1...partN是分區(qū)的名稱。

values_list是對應(yīng)分區(qū)的分區(qū)鍵值的列表。DEFAULT關(guān)鍵字允許存儲(chǔ)前面的分區(qū)不能存儲(chǔ)的記錄。示例6：

createtablet_emp1(

empnonumber(4),enamevarchar2(30),locationvarchar2(30))

partitionbylist(location)(

partitione1values("北京"),

partitione2values("上海","天津","重慶"),partitione3values("廣東","福建"),);

要查詢表分區(qū)中的數(shù)據(jù)行：select*fromtable_namePARTITION(p1);注：p1是分區(qū)名。分區(qū)維護(hù)操作：

1、添加分區(qū)：ALTER...ADDPARTITION語句用于在現(xiàn)有的最后一個(gè)分區(qū)之后添加新的分區(qū)。

示例7：演示如何將名為E4的新分區(qū)添加到示例3中創(chuàng)建的t_emp表。ALTERTABLEt_empADDPARTITIONE4VALUESLESSTHAN(3000);在此請注意：上例公適用于已使用特定的鍵值定義了最后一個(gè)分區(qū)的表。如果要在表的開始或中間位置添加分區(qū)，或者最高分區(qū)的分區(qū)

邊界是MAXVALUE，則應(yīng)使用SPLITPARTITION語句。

2、刪除分區(qū)：使用ALTERTABLE...DROPPARTITION語句。

示例8：演示了如何刪除t_emp表的E4分區(qū)。刪除分區(qū)時(shí)，分區(qū)中的數(shù)據(jù)也隨之刪除。ALTERTABLEt_empDROPPARTITIONE4;

3、截?cái)喾謪^(qū)：使用ALTERTABLE...TRUNCATEPARTITION語句來截?cái)喾謪^(qū)，只刪除表分區(qū)中的所有記錄。

示例9：演示了如何刪除t_emp表中e3的分區(qū)的所有記錄。ALTERTABLEt_empTRUNCATEPARTITIONe3;

4、合并分區(qū)：可以將范圍分區(qū)或復(fù)合分區(qū)表的兩個(gè)相鄰分區(qū)連接起來。結(jié)果分區(qū)將繼承被合并的兩個(gè)分區(qū)的較高上界。語法如下：

ALTERTABLEtable_nameMERGEPARTITIONSpartitions_name,partitions_nameINTOPARTITIONpartition_name;

示例10：演示了如何將e1和e2合并成一個(gè)e2分區(qū)。

ALTERTABLEt_empMERGEPARTITIONSe1,e2INTOPARTITIONe2;

5、拆分分區(qū)：使用SPLITPARTITION語句在表的開頭或中間添加分區(qū)。拆分分區(qū)允許用戶將一個(gè)分區(qū)拆分為兩個(gè)分區(qū)。語法如下：ALTERTABLEtable_nameSPLIYPARTITIONpartition_nameAT(value)INTO(PARTITIONpartition1,PARTITIONpartition2);

示例11：演示了如何將t_emp表中的e3分區(qū)拆分為e31和e32兩個(gè)分區(qū)。

ALTERTABLEt_empSPLITPARTITIONe3AT(Date"201*-01-01")INTO(PARTITIONe31,PARTITIONe32);

6、重新命名拆分后的分區(qū):

ALTERTABLEt_empRENAMEPARTITIONe31TOP3;ALTERTABLEt_empRENAMEPARTITIONe32TOP4;

可以查詢字典視圖user_tab_partitions來查看用戶所創(chuàng)建的分區(qū)的詳細(xì)信息示例12：selecttable_name,partition_name,high_valuefromuser_tab_partitions;

可以查詢字典視圖dba_tab_subpartitions來查看用戶所創(chuàng)建的子分區(qū)的詳細(xì)信息

示例13：selecttable_name,partition_name,subpartition_namefromdba_tab_subpartitions;

四、數(shù)據(jù)庫對象

表、視圖、序列、過程、函數(shù)、程序包，甚至其它同義詞都可以創(chuàng)建同義詞。

1、同義詞：私有同義詞、公有同義詞。私有同義詞只能被當(dāng)前模式的用戶訪問。私有同義詞名稱不可與當(dāng)前模式的對象名稱相同。要在自身的模式創(chuàng)建私有同義詞，

用戶必須擁有CreateSynonym系統(tǒng)權(quán)限。要在其它用戶模式創(chuàng)建私有同義詞，用戶必須擁有CreateAnySynonym系統(tǒng)權(quán)限。

公有同義詞可被所有的數(shù)據(jù)庫用戶訪問。要?jiǎng)?chuàng)建公有同義詞，用戶必須擁有CreatePublicSynonym系統(tǒng)權(quán)限。

創(chuàng)建私有同義詞語法：

Create[ORREPLACE]SYNONYM[schema.]synonym_nameFOR[schema.]object_name;其中：ORREPLACE表示在同義詞存在的情況下替換該同義詞。synonym_name表示要?jiǎng)?chuàng)建的同義詞的名稱。

object_name指定要為之創(chuàng)建同義詞的對象的名稱。示例1：createsynonyms_empforscott.emp;

創(chuàng)建公有同義詞語法：

CreatePUBLICSYNONYMsynonym_nameFOR[schema.]object_name;示例2：createpublicsynonymemp_synfromscott.emp;

可以查詢字典視圖User_Synonyms來查看用戶所創(chuàng)建的同義詞的詳細(xì)信息

刪除同義詞語法：DropSynonymssynonym_name;刪除公有同義詞加上一個(gè)Public此命令只刪除同義詞，不會(huì)刪除對應(yīng)的表。

2、序列：是用來生成唯一、連續(xù)的整數(shù)的數(shù)據(jù)庫對象。序列通常用來自動(dòng)生成主鍵或唯一鍵的值。

創(chuàng)建序列語法如下：

CreateSEQUENCEsequence_name[STARTWITHinteger][INCREMENTBYinteger]

[MAXVALUEinteger|NOMAXVALUE][MINVALUEinteger|NOMINVALUE][CYCLE|NOCYCLE]

[CACHEinterger|NOCACHE];

其中：STARTWITH是指定要生成的第一個(gè)序列號。對于升序序列，其默認(rèn)值為序列的最小值。對于降序序列，其默認(rèn)值為序列的最大值。

INCREMENTBY是用于指定序列號之間的間隔。其默認(rèn)值為1。如果integer為正值，則生成的序列將按升序排列，否則按降序排列。MAXVALUE指定序列可以生成的最大值。

NOMAXVALUE這是默認(rèn)選項(xiàng)，將升序序列的最大值設(shè)為10的27次冪，將降序序列的最大值設(shè)為-1。

MINVALUE指定序列的最小值。MINVALUE必須小于或等于STARTWITH的值，并且必須小于MAXVALUE。

NOMINVALUE這是默認(rèn)選項(xiàng)，將升序序列的最小值設(shè)為1，將降序序列的最小值設(shè)為-10的26次冪。

CYCLE指定序列在達(dá)到最大值或最小值后，將繼續(xù)從頭開始生成值。

NOCYCLE這是默認(rèn)選項(xiàng)。指定序列在達(dá)到最大值或最小值后，將不能再繼續(xù)生成值。CACHE使用CACHE選項(xiàng)可以預(yù)先分配一組序列號，并將其保留在內(nèi)存中，這樣可以更快的訪問序列號。

NOCACHE此項(xiàng)則不會(huì)為加快速度而預(yù)先分配序列號。如果在創(chuàng)建序列時(shí)忽略了CACHE和NOCACHE選項(xiàng)，Oracle將默認(rèn)緩存20個(gè)序列號。示例3：CreateSEQUENCEtoys_seqSTARTWITH10INCREMENTBY2MAXVALUE201*MINVALUE10NOCYCLECACHE30;

訪問序列：可以通過CURRVAL和NEXTVAL偽列來訪問該序列的值。示例4：演示從序列toys_seq中選擇值插入toys表中的toyid列。執(zhí)行成功將會(huì)在該表的toyid列插入值"P10"和"P12"。

INSERTINTOtoys(toyid,toyname,toyprice)values("p"||toys_seq.NEXTVAL,"TWENTY",25);INSERTINTOtoys(toyid,toyname,toyprice)values("p"||toys_seq.NEXTVAL,"MAGICPENCIL",75);

示例5：演示如何查看序列當(dāng)前值

Selecttoys_seq.CURRVALfromdual;

更改序列：ALTERSEQUENCE命令用于設(shè)置或刪除MINVALUE或MAXVALUE、修改增量值、修改緩存中的序列號的數(shù)目。

修改序列語法如下：注意，不能修改序列的STARTWITH參數(shù)。在修改序列時(shí)，應(yīng)注意升序序列的最小值應(yīng)小于最大值。

ALTERSEQUENCE[schema.]sequence_name[INCREMENTBYinteger]

[MAXVALUEinteger|NOMAXVALUE][MINVALUEinteger|NOMINVALUE][CYCLE|NOCYCLE]

[CACHEinterger|NOCACHE];

示例6：演示如何設(shè)置一個(gè)新的MAXVALUE，并為toys_seq序列打開了CYCLE。ALTERSEQUENCEtoys_seqMAXVALUE5000CYCLE;

可以查詢字典視圖User_Sequences來查看用戶所創(chuàng)建的序列的詳細(xì)信息

刪除序列語法：DropSEQUENCEtoys_seq;

[local]2[/local]

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

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

《Oracle學(xué)習(xí)之總結(jié)》由互聯(lián)網(wǎng)用戶整理提供,轉(zhuǎn)載分享請保留原作者信息,謝謝!
鏈接地址：http://m.seogis.com/gongwen/712075.html

• 上一篇：oracle實(shí)訓(xùn)總結(jié)
• 下一篇：我個(gè)人關(guān)于ORACLE課程提綱的總結(jié)