Oracle學習之總結

一、學會操作庫

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

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

createuserNMJYidentifiedbynmjy201*defaulttablespaceNMJYquota10monusers;//授權

grantconnect,resource,dbatoNMJY;

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

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*）@服務器（orcl）shemas=用戶（nmjy）（DIRECTORY）=dumdirdumpfile=文件名（nmjy11117.dmp）】

--還原

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

二、怎樣從sqlserver導入表到oracle

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

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

在服務資源管理器中找到此連接，，右擊點‘導入表…’

路徑彈出如下頁面：

點新建一個連接，之后：

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

確定后，根據(jù)向導向后操作，到如下頁：

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

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

注：Oracle是嚴格區(qū)分大小寫的,凡牽扯到用戶ID之類的務必用Upper()轉換后導入。

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

三、SqlServer和Oracle語句編碼比較

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

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

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

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

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

擴展閱讀：有關Oracle學習總結

表xyzabc1mhj1mhjk1njk2uwe2uwert3qs4ads4adsaa

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

DELETEFROMLIANXI

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

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

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

ORACLE相關語法及命令一、Oracle入門理論知識：

Oracle的物理組件有三個：

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

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

Oracle的邏輯組件：

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

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

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

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

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

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

2、程序全局區(qū)(PGA)服務器進程啟動時分配該內(nèi)存區(qū)。PGA為非共享區(qū)，只能單個進程使用，當一個用戶會話結束后，PGA釋放。

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

語法知識：

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

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

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

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

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

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

disconn//退出當前登錄conn用戶名/密碼//連接Oracle

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

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

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

createuser用戶名identifiedby密碼[passwordexpire]

[defaulttablespace表空間名][temporarytablespace臨時表空間名];//創(chuàng)建用戶相關權限：

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

grantselectonemptoscott;//將emp表的查詢權限授予用戶scott

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

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

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

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

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

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

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

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

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

systimestamp返回當前日期、時間。格式為08-9月-0704.08.30.000000下午。

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

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

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

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

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

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

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

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

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

commit;//提交事務

savepoint標記名;//標記事務點rollback;//回滾整個事務處理

rollbackto[savepoint]標記名;//回滾到事務中某個特定的保存點集合操作符：

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

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

語法：selectorderno,enamefromorder_masterUNIONALLselectorderno,pronamefromorder_detailorderby2;

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

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

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

語法：selectordernofromorder_masterINTERSECTselectordernofromorder_detail;

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

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

連接(||)操作符：

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

1、日期函數(shù)：

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

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

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

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

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

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

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

trunc:此函數(shù)將指定日期截斷為由格式模型指定的單位日期，與Round函數(shù)不同的是它只舍不入，語法為trunc(d,[fmt])，與round格式相同。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

示例：selectto_char(sysdate,"yyyy"年"fmmm"月"fmdd"日"hh24:mi:ss")fromdual;輸出結果: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"時"mi"分"ss"秒"")fromdual;示例：selectto_char(sal,"$99999")fromemp;輸出結果:$1600。

to_date(char[,fmt]):此函數(shù)將char或varchar2數(shù)據(jù)類型轉換為日期數(shù)據(jù)類型。示例：selectto_date("201*-12-06","yyyy-mm-dd")fromdual;輸出結果: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時08分08秒","yyyy"年"mm"月"dd"日"hh24"時"mi"分"ss"秒"")fromdual;

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

示例：selectsqrt(to_number("100"))fromdual;輸出結果: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ù)比較兩個表達式，如果它們相等，則返回空值，否則返回expr1。nullif函數(shù)等價于以下的case表達式：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

fromempe,deptdwheree.deptno=d.deptno;

三、鎖和表分區(qū)

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

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

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

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

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

select*fromdeptwheredeptno=10forupdateofdname,loc;

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

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

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

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

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

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

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

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

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

在CreateTable語句后增加

PARTITIONBYRANGE(column_name)(

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

PARTITIONpartNVALUELESSTHAN(MAXVALUE)[TABLESPACEtbsN]);

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

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

tbs1...tbsN是分區(qū)所在的表空間，TABLESPACE子句是可選項。示例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是以其為基礎創(chuàng)建散列分區(qū)的列。

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

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

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

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

partitionbyhash(empno)partitions4;

3、復合分區(qū)：是范圍分區(qū)和散列分區(qū)的結合。在創(chuàng)建復合分區(qū)時，先根據(jù)范圍對數(shù)據(jù)進行分區(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是以其為基礎創(chuàng)建范圍分區(qū)的列。column_name2是以其為基礎創(chuàng)建散列分區(qū)的列。number_of_partitions是要創(chuàng)建的子分區(qū)的數(shù)目。part1...partN是分區(qū)的名稱。

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

示例5：將雇員表先按照雇傭時間hiredate進行了范圍分區(qū)，然后再把每個分區(qū)分為2個子hash分區(qū)，此表一共是6個分區(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是以其為基礎創(chuàng)建列表分區(qū)的列。part1...partN是分區(qū)的名稱。

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

createtablet_emp1(

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

partitionbylist(location)(

partitione1values("北京"),

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

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

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

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

邊界是MAXVALUE，則應使用SPLITPARTITION語句。

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

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

3、截斷分區(qū)：使用ALTERTABLE...TRUNCATEPARTITION語句來截斷分區(qū)，只刪除表分區(qū)中的所有記錄。

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

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

ALTERTABLEtable_nameMERGEPARTITIONSpartitions_name,partitions_nameINTOPARTITIONpartition_name;

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

ALTERTABLEt_empMERGEPARTITIONSe1,e2INTOPARTITIONe2;

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

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

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

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

ALTERTABLEt_empRENAMEPARTITIONe31TOP3;ALTERTABLEt_empRENAMEPARTITIONe32TOP4;

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

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

示例13：selecttable_name,partition_name,subpartition_namefromdba_tab_subpartitions;

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

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

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

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

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

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

Create[ORREPLACE]SYNONYM[schema.]synonym_nameFOR[schema.]object_name;其中：ORREPLACE表示在同義詞存在的情況下替換該同義詞。synonym_name表示要創(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)建的同義詞的詳細信息

刪除同義詞語法：DropSynonymssynonym_name;刪除公有同義詞加上一個Public此命令只刪除同義詞，不會刪除對應的表。

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

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

CreateSEQUENCEsequence_name[STARTWITHinteger][INCREMENTBYinteger]

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

[CACHEinterger|NOCACHE];

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

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

NOMAXVALUE這是默認選項，將升序序列的最大值設為10的27次冪，將降序序列的最大值設為-1。

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

NOMINVALUE這是默認選項，將升序序列的最小值設為1，將降序序列的最小值設為-10的26次冪。

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

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

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

訪問序列：可以通過CURRVAL和NEXTVAL偽列來訪問該序列的值。示例4：演示從序列toys_seq中選擇值插入toys表中的toyid列。執(zhí)行成功將會在該表的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：演示如何查看序列當前值

Selecttoys_seq.CURRVALfromdual;

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

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

ALTERSEQUENCE[schema.]sequence_name[INCREMENTBYinteger]

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

[CACHEinterger|NOCACHE];

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

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

刪除序列語法：DropSEQUENCEtoys_seq;

[local]2[/local]

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

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

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

• 上一篇：oracle實訓總結
• 下一篇：我個人關于ORACLE課程提綱的總結