oracle的sql語(yǔ)句
// 提交事務(wù)
ct.commit();
// 關(guān)閉打開(kāi)的資源
sm.close();
ct.close();
} catch (Exception e) {
// 如果發(fā)生異常，就回滾
try {
ct.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}
}
}
再運(yùn)行一下，會(huì)出現(xiàn)異常，查看數(shù)據(jù)庫(kù)，數(shù)據(jù)沒(méi)變化。。 ? 只讀事務(wù) 只讀事務(wù)是指只允許執(zhí)行查詢的操作，而不允許執(zhí)行任何其它dml操作的事務(wù)，使用只讀事務(wù)可以確保用戶只能取得某時(shí)間點(diǎn)的數(shù)據(jù)。假定機(jī)票代售點(diǎn)每天18點(diǎn)開(kāi)始統(tǒng)計(jì)今天的銷售情況，這時(shí)可以使用只讀事務(wù)。在設(shè)置了只讀事務(wù)后，盡管其它會(huì)話可能會(huì)提交新的事務(wù)，但是只讀事務(wù)將不會(huì)取得最新數(shù)據(jù)的變化，從而可以保證取得特定時(shí)間點(diǎn)的數(shù)據(jù)信息。 ? 設(shè)置只讀事務(wù) set transaction read only;
9.oracle的函數(shù)
sql函數(shù)的使用 字符函數(shù)? 介紹 字符函數(shù)是oracle中最常用的函數(shù)，我們來(lái)看看有哪些字符函數(shù)： ? lower(char)：將字符串轉(zhuǎn)化為小寫(xiě)的格式。 ? upper(char)：將字符串轉(zhuǎn)化為大寫(xiě)的格式。 ? length(char)：返回字符串的長(zhǎng)度。 ? substr(char,m,n)：取字符串的子串 n代表取n個(gè)的意思，不是代表取到第n個(gè) ? replace(char1,search_string,replace_string) ? instr(char1,char2,[,n[,m]])取子串在字符串的位置 問(wèn)題：將所有員工的名字按小寫(xiě)的方式顯示 SQL> select lower(ename) from emp; 問(wèn)題：將所有員工的名字按大寫(xiě)的方式顯示。 SQL> select upper(ename) from emp; 問(wèn)題：顯示正好為5個(gè)字符的員工的姓名。 SQL> select * from emp where length(ename)=5; 問(wèn)題：顯示所有員工姓名的前三個(gè)字符。
Oracle 筆記
19
SQL> select substr(ename,1,3) from emp; 問(wèn)題：以首字母大寫(xiě),后面小寫(xiě)的方式顯示所有員工的姓名。 SQL> select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp; 問(wèn)題：以首字母小寫(xiě),后面大寫(xiě)的方式顯示所有員工的姓名。 SQL> select lower(substr(ename,1,1)) || upper(substr(ename,2,length(ename)-1)) from emp; 問(wèn)題：顯示所有員工的姓名，用“我是老虎”替換所有“A” SQL> select replace(ename,'A', '我是老虎') from emp; 數(shù)學(xué)函數(shù)? 介紹 數(shù)學(xué)函數(shù)的輸入?yún)?shù)和返回值的數(shù)據(jù)類型都是數(shù)字類型的。數(shù)學(xué)函數(shù)包括cos，cosh，exp，ln, log，sin，sinh，sqrt，tan，tanh，acos，asin，atan，round，我們講最常用的： ? round(n,[m]) 該函數(shù)用于執(zhí)行四舍五入，如果省掉m，則四舍五入到整數(shù)，如果m是正數(shù)，則四舍五入到小數(shù)點(diǎn)的m位后。如果m是負(fù)數(shù)，則四舍五入到小數(shù)點(diǎn)的m位前。 ? trunc(n,[m]) 該函數(shù)用于截取數(shù)字。如果省掉m，就截去小數(shù)部分，如果m是正數(shù)就截取到小數(shù)點(diǎn)的m位后，如果m是負(fù)數(shù)，則截取到小數(shù)點(diǎn)的前m位。 ? mod(m,n) ? floor(n) 返回小于或是等于n的最大整數(shù) ? ceil(n) 返回大于或是等于n的最小整數(shù) 對(duì)數(shù)字的處理，在財(cái)務(wù)系統(tǒng)或銀行系統(tǒng)中用的最多，不同的處理方法，對(duì)財(cái)務(wù)報(bào)表有不同的結(jié)果。 問(wèn)題：顯示在一個(gè)月為30天的情況下，所有員工的日薪金，忽略余數(shù)。 SQL> select trunc(sal/30), ename from emp; or SQL> select floor(sal/30), ename from emp; 在做oracle測(cè)試的時(shí)候，可以使用dual表 select mod(10,2) from dual;結(jié)果是0 select mod(10,3) from dual;結(jié)果是1 其它的數(shù)學(xué)函數(shù)，有興趣的同學(xué)可以自己去看看： abs(n)： 返回?cái)?shù)字n的絕對(duì)值 select abs(-13) from dual; acos(n)： 返回?cái)?shù)字的反余弦值 asin(n)： 返回?cái)?shù)字的反正弦值 atan(n)： 返回?cái)?shù)字的反正切值 cos(n)： exp(n)： 返回e的n次冪 log(m,n)： 返回對(duì)數(shù)值 power(m,n)： 返回m的n次冪 日期函數(shù)? 介紹 日期函數(shù)用于處理date類型的數(shù)據(jù)。 默認(rèn)情況下日期格式是dd-mon-yy 即12-7月-78 (1)sysdate: 該函數(shù)返回系統(tǒng)時(shí)間 (2)add_months(d,n) (3)last_day(d)：返回指定日期所在月份的最后一天 問(wèn)題：查找已經(jīng)入職8個(gè)月多的員工 SQL> select * from emp where sysdate>=add_months(hiredate,8); 問(wèn)題：顯示滿10年服務(wù)年限的員工的姓名和受雇日期。
Oracle 筆記
20
SQL> select ename, hiredate from emp where sysdate>=add_months(hiredate,12*10); 問(wèn)題：對(duì)于每個(gè)員工，顯示其加入公司的天數(shù)。 SQL> select floor(sysdate-hiredate) "入職天數(shù)",ename from emp; or SQL> select trunc(sysdate-hiredate) "入職天數(shù)",ename from emp; 問(wèn)題：找出各月倒數(shù)第3天受雇的所有員工。 SQL> select hiredate,ename from emp where last_day(hiredate)-2=hiredate; 轉(zhuǎn)換函數(shù) ? 介紹√ 轉(zhuǎn)換函數(shù)用于將數(shù)據(jù)類型從一種轉(zhuǎn)為另外一種。在某些情況下，oracle server允許值的數(shù)據(jù)類型和實(shí)際的不一樣，這時(shí)oracle server會(huì)隱含的轉(zhuǎn)化數(shù)據(jù)類型 比如： create table t1(id int); insert into t1 values('10');-->這樣oracle會(huì)自動(dòng)的將'10' -->10 create table t2 (id varchar2(10)); insert into t2 values(1); -->這樣oracle就會(huì)自動(dòng)的將1 -->'1'; 我們要說(shuō)的是盡管oracle可以進(jìn)行隱含的數(shù)據(jù)類型的轉(zhuǎn)換，但是它并不適應(yīng)所有的情況，為了提高程序的可靠性，我們應(yīng)該使用轉(zhuǎn)換函數(shù)進(jìn)行轉(zhuǎn)換。 ? to_char 你可以使用select ename, hiredate, sal from emp where deptno = 10;顯示信息，可是，在某些情況下，這個(gè)并不能滿足你的需求。 問(wèn)題：日期是否可以顯示 時(shí)/分/秒 SQL> select ename, to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss') from emp; 問(wèn)題：薪水是否可以顯示指定的貨幣符號(hào) SQL> yy：兩位數(shù)字的年份 2004-->04 yyyy：四位數(shù)字的年份 2004年 mm：兩位數(shù)字的月份 8月-->08 dd：兩位數(shù)字的天 30號(hào)-->30 hh24： 8點(diǎn)-->20 hh12：8點(diǎn)-->08 mi、ss-->顯示分鐘\秒 9：顯示數(shù)字，并忽略前面0 0：顯示數(shù)字，如位數(shù)不足，則用0補(bǔ)齊 .：在指定位置顯示小數(shù)點(diǎn) ,：在指定位置顯示逗號(hào) $：在數(shù)字前加美元 L：在數(shù)字前面加本地貨幣符號(hào) C：在數(shù)字前面加國(guó)際貨幣符號(hào) G：在指定位置顯示組分隔符、 D：在指定位置顯示小數(shù)點(diǎn)符號(hào)(.) 問(wèn)題：顯示薪水的時(shí)候，把本地貨幣單位加在前面 SQL> select ename, to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss'), to_char(sal,'L99999.99') from emp; 問(wèn)題：顯示1980年入職的所有員工 SQL> select * from emp where to_char(hiredate, 'yyyy')=1980;
Oracle 筆記
21
問(wèn)題：顯示所有12月份入職的員工 SQL> select * from emp where to_char(hiredate, 'mm')=12; ? to_date 函數(shù)to_date用于將字符串轉(zhuǎn)換成date類型的數(shù)據(jù)。 問(wèn)題：能否按照中國(guó)人習(xí)慣的方式年—月—日添加日期。 系統(tǒng)函數(shù) ? sys_context 1)terminal：當(dāng)前會(huì)話客戶所對(duì)應(yīng)的終端的標(biāo)示符 2)lanuage: 語(yǔ)言 3)db_name： 當(dāng)前數(shù)據(jù)庫(kù)名稱 4)nls_date_format： 當(dāng)前會(huì)話客戶所對(duì)應(yīng)的日期格式 5)session_user： 當(dāng)前會(huì)話客戶所對(duì)應(yīng)的數(shù)據(jù)庫(kù)用戶名 6)current_schema： 當(dāng)前會(huì)話客戶所對(duì)應(yīng)的默認(rèn)方案名 7)host： 返回?cái)?shù)據(jù)庫(kù)所在主機(jī)的名稱 通過(guò)該函數(shù)，可以查詢一些重要信息，比如你正在使用哪個(gè)數(shù)據(jù)庫(kù)? select sys_context('USERENV','db_name') from dual; 注意：USERENV是固定的，不能改的，db_name可以換成其它,比如select sys_context('USERENV','lanuage') from dual;又比如select sys_context('USERENV','current_schema') from dual;
10.數(shù)據(jù)庫(kù)管理，表的邏輯備份與恢復(fù)
內(nèi)容介紹 1.上節(jié)回顧 2.數(shù)據(jù)庫(kù)管理員 3.數(shù)據(jù)庫(kù)(表)的邏輯備份與恢復(fù) √ 4.數(shù)據(jù)字典和動(dòng)態(tài)性能視圖 √ 5.管理表空間和數(shù)據(jù)文件 √ 期望目標(biāo) 1.了解oracle管理員的基本職責(zé) 2.掌握備份和恢復(fù)數(shù)據(jù)庫(kù)/表的方法 3.理解表空間、數(shù)據(jù)字典、性能視圖 數(shù)據(jù)庫(kù)管理員 ? 介紹 每個(gè)oracle數(shù)據(jù)庫(kù)應(yīng)該至少有一個(gè)數(shù)據(jù)庫(kù)管理員(dba)，對(duì)于一個(gè)小的數(shù)據(jù)庫(kù)，一個(gè)dba就夠了，但是對(duì)于一個(gè)大的數(shù)據(jù)庫(kù)可能需要多個(gè)dba分擔(dān)不同的管理職責(zé)。那么一個(gè)數(shù)據(jù)庫(kù)管理員的主要工作是什么呢： ? 職責(zé) 1.安裝和升級(jí)oracle數(shù)據(jù)庫(kù) 2.建庫(kù)，表空間，表，視圖，索引? 3.制定并實(shí)施備份和恢復(fù)計(jì)劃 4.數(shù)據(jù)庫(kù)權(quán)限管理，調(diào)優(yōu)，故障排除 5.對(duì)于高級(jí)dba，要求能參與項(xiàng)目開(kāi)發(fā)，會(huì)編寫(xiě)sql語(yǔ)句、存儲(chǔ)過(guò)程、觸發(fā)器、規(guī)則、約束、包 ? 管理數(shù)據(jù)庫(kù)的用戶主要是sys和system (sys好像是董事長(zhǎng)，system好像是總經(jīng)理，董事長(zhǎng)比總經(jīng)理大，但是通常是總經(jīng)理干事) 在前面我們已經(jīng)提到這兩個(gè)用戶，區(qū)別主要是： 1.最重要的區(qū)別，存儲(chǔ)的數(shù)據(jù)的重要性不同
Oracle 筆記
22
sys：所有oracle的數(shù)據(jù)字典的基表和視圖都存放在sys用戶中，這些基表和視圖對(duì)于oracle的運(yùn)行是至關(guān)重要的，由數(shù)據(jù)庫(kù)自己維護(hù)，任何用戶都不能手動(dòng)更改。sys用戶擁有dba，sysdba，sysoper角色或權(quán)限，是oracle權(quán)限最高的用戶。 system：用于存放次一級(jí)的內(nèi)部數(shù)據(jù)，如oracle的一些特性或工具的管理信息。system用戶擁有dba，sysdba角色或系統(tǒng)權(quán)限。 看圖： sysdba可以建數(shù)據(jù)庫(kù)，sysope不能建數(shù)據(jù)庫(kù) 2. 其次的區(qū)別，權(quán)限的不同。 sys用戶必須以as sysdba或as sysoper形式登錄。不能以normal方式登錄數(shù)據(jù)庫(kù) system如果正常登錄，它其實(shí)就是一個(gè)普通的dba用戶，但是如果以as sysdba登錄，其結(jié)果實(shí)際上它是作為sys用戶登錄的，從登錄信息里面我們可以看出來(lái)。 sysdba和sysoper權(quán)限區(qū)別圖，看圖： sysdba>sysoper>dba 可以看到：只要是sysoper擁有的權(quán)限，sysdba都有;藍(lán)色是它們區(qū)別的地方。(它們的最大區(qū)別是：sysdba可以創(chuàng)建數(shù)據(jù)庫(kù)，sysoper不可以創(chuàng)建數(shù)據(jù)庫(kù)) ? dba權(quán)限的用戶 dba用戶是指具有dba角色的數(shù)據(jù)庫(kù)用戶。特權(quán)用戶可以執(zhí)行啟動(dòng)實(shí)例，關(guān)閉實(shí)例等特殊操作，而dba用戶只有在啟動(dòng)數(shù)據(jù)庫(kù)后才能執(zhí)行各種管理工作。 (相當(dāng)于說(shuō)dba連startup和shutdown這兩個(gè)權(quán)限都沒(méi)有) 兩個(gè)主要的用戶，三個(gè)重要權(quán)限，他們的區(qū)別和聯(lián)系，大家要弄清楚 管理初始化參數(shù) ? 管理初始化參數(shù)(調(diào)優(yōu)的一個(gè)重要知識(shí)點(diǎn)，憑什么可以對(duì)數(shù)據(jù)庫(kù)進(jìn)行調(diào)優(yōu)呢?是因?yàn)樗梢詫?duì)數(shù)據(jù)庫(kù)的一些參數(shù)進(jìn)行修改修正) 初始化參數(shù)用于設(shè)置實(shí)例或是數(shù)據(jù)庫(kù)的特征。oracle9i提供了200多個(gè)初始化參數(shù)，并且每個(gè)初始化參數(shù)都有默認(rèn)值。 ? 顯示初始化參數(shù) (1) show parameter命令 ? 如何修改參數(shù) 需要說(shuō)明的如果你希望修改這些初始化的參數(shù)，可以到文件D:\oracle\admin\myoral\pfile\init.ora文件中去修改比如要修改實(shí)例的名字 數(shù)據(jù)庫(kù)(表)的邏輯備份與恢復(fù) 介紹 ? 介紹 邏輯備份是指使用工具export將數(shù)據(jù)對(duì)象的結(jié)構(gòu)和數(shù)據(jù)導(dǎo)出到文件的過(guò)程，邏輯恢復(fù)是指當(dāng)數(shù)據(jù)庫(kù)對(duì)象被誤操作而損壞后使用工具import利用備份的文件把數(shù)據(jù)對(duì)象導(dǎo)入到數(shù)據(jù)庫(kù)的過(guò)程。 物理備份即可在數(shù)據(jù)庫(kù)open的狀態(tài)下進(jìn)行也可在關(guān)閉數(shù)據(jù)庫(kù)后進(jìn)行，但是邏輯備份和恢復(fù)只能在open的狀態(tài)下進(jìn)行。 看圖： ? 導(dǎo)出 導(dǎo)出具體的分為：導(dǎo)出表，導(dǎo)出方案，導(dǎo)出數(shù)據(jù)庫(kù)三種方式。 導(dǎo)出使用exp命令來(lái)完成的，該命令常用的選項(xiàng)有： userid： 用于指定執(zhí)行導(dǎo)出操作的用戶名，口令，連接字符串 tables： 用于指定執(zhí)行導(dǎo)出操作的表 owner： 用于指定執(zhí)行導(dǎo)出操作的方案 full=y： 用于指定執(zhí)行導(dǎo)出操作的數(shù)據(jù)庫(kù) inctype： 用于指定執(zhí)行導(dǎo)出操作的增量類型 rows： 用于指定執(zhí)行導(dǎo)出操作是否要導(dǎo)出表中的數(shù)據(jù) file： 用于指定導(dǎo)出文件名
Oracle 筆記
23
? 導(dǎo)出表 1.導(dǎo)出自己的表 exp userid=scott/tiger@myoral tables=(emp,dept) file=d:\e1.dmp 2.導(dǎo)出其它方案的表 如果用戶要導(dǎo)出其它方案的表，則需要dba的權(quán)限或是exp_full_database的權(quán)限，比如system就可以導(dǎo)出scott的表 E:\oracle\ora92\bin>exp userid=system/manager@myoral tables=(scott.emp) file=d:\e2.emp 特別說(shuō)明：在導(dǎo)入和導(dǎo)出的時(shí)候，要到oracle目錄的bin目錄下。 3. 導(dǎo)出表的結(jié)構(gòu) exp userid=scott/tiger@accp tables=(emp) file=d:\e3.dmp rows=n 4. 使用直接導(dǎo)出方式 exp userid=scott/tiger@accp tables=(emp) file=d:\e4.dmp direct=y 這種方式比默認(rèn)的常規(guī)方式速度要快，當(dāng)數(shù)據(jù)量大時(shí)，可以考慮使用這樣的方法。 這時(shí)需要數(shù)據(jù)庫(kù)的字符集要與客戶端字符集完全一致，否則會(huì)報(bào)錯(cuò)... ? 導(dǎo)出方案 導(dǎo)出方案是指使用export工具導(dǎo)出一個(gè)方案或是多個(gè)方案中的所有對(duì)象(表，索引，約束...)和數(shù)據(jù)。并存放到文件中。 1. 導(dǎo)出自己的方案 exp userid=scott/tiger@myorcl owner=scott file=d:\scott.dmp 2. 導(dǎo)出其它方案 如果用戶要導(dǎo)出其它方案，則需要dba的權(quán)限或是exp_full_database的權(quán)限，比如system用戶就可以導(dǎo)出任何方案 exp userid=system/manager@myorcl owner=(system,scott) file=d:\system.dmp ? 導(dǎo)出數(shù)據(jù)庫(kù) 導(dǎo)出數(shù)據(jù)庫(kù)是指利用export導(dǎo)出所有數(shù)據(jù)庫(kù)中的對(duì)象及數(shù)據(jù)，要求該用戶具有dba的權(quán)限或者是exp_full_database權(quán)限 增量備份(好處是第一次備份后，第二次備份就快很多了) exp userid=system/manager@myorcl full=y inctype=complete file=d:\all.dmp 導(dǎo)入 ? 介紹 導(dǎo)入就是使用工具import將文件中的對(duì)象和數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫(kù)中，但是導(dǎo)入要使用的文件必須是export所導(dǎo)出的文件。與導(dǎo)出相似，導(dǎo)入也分為導(dǎo)入表，導(dǎo)入方案，導(dǎo)入數(shù)據(jù)庫(kù)三種方式。 imp常用的選項(xiàng)有 userid： 用于指定執(zhí)行導(dǎo)入操作的用戶名，口令，連接字符串 tables： 用于指定執(zhí)行導(dǎo)入操作的表 formuser： 用于指定源用戶 touser： 用于指定目標(biāo)用戶 file： 用于指定導(dǎo)入文件名 full=y： 用于指定執(zhí)行導(dǎo)入整個(gè)文件 inctype： 用于指定執(zhí)行導(dǎo)入操作的增量類型 rows： 指定是否要導(dǎo)入表行(數(shù)據(jù)) ignore： 如果表存在，則只導(dǎo)入數(shù)據(jù) ? 導(dǎo)入表 1. 導(dǎo)入自己的表 imp userid=scott/tiger@myorcl tables=(emp) file=d:\xx.dmp 2. 導(dǎo)入表到其它用戶 要求該用戶具有dba的權(quán)限，或是imp_full_database imp userid=system/tiger@myorcl tables=(emp) file=d:\xx.dmp touser=scott 3. 導(dǎo)入表的結(jié)構(gòu)
Oracle 筆記
24
只導(dǎo)入表的結(jié)構(gòu)而不導(dǎo)入數(shù)據(jù) imp userid=scott/tiger@myorcl tables=(emp) file=d:\xx.dmp rows=n 4. 導(dǎo)入數(shù)據(jù) 如果對(duì)象(如比表)已經(jīng)存在可以只導(dǎo)入表的數(shù)據(jù) imp userid=scott/tiger@myorcl tables=(emp) file=d:\xx.dmp ignore=y ? 導(dǎo)入方案 導(dǎo)入方案是指使用import工具將文件中的對(duì)象和數(shù)據(jù)導(dǎo)入到一個(gè)或是多個(gè)方案中。如果要導(dǎo)入其它方案，要求該用戶具有dba的權(quán)限，或者imp_full_database 1. 導(dǎo)入自身的方案 imp userid=scott/tiger file=d:\xxx.dmp 2. 導(dǎo)入其它方案 要求該用戶具有dba的權(quán)限 imp userid=system/manager file=d:\xxx.dmp fromuser=system touser=scott ? 導(dǎo)入數(shù)據(jù)庫(kù) 在默認(rèn)情況下，當(dāng)導(dǎo)入數(shù)據(jù)庫(kù)時(shí)，會(huì)導(dǎo)入所有對(duì)象結(jié)構(gòu)和數(shù)據(jù)，案例如下： imp userid=system/manager full=y file=d:\xxx.dmp
11.數(shù)據(jù)字典和動(dòng)態(tài)性能視圖 介紹：數(shù)據(jù)字典是什么 數(shù)據(jù)字典是oracle數(shù)據(jù)庫(kù)中最重要的組成部分，它提供了數(shù)據(jù)庫(kù)的一些系統(tǒng)信息。 動(dòng)態(tài)性能視圖記載了例程啟動(dòng)后的相關(guān)信息。 ? 數(shù)據(jù)字典 數(shù)據(jù)字典記錄了數(shù)據(jù)庫(kù)的系統(tǒng)信息，它是只讀表和視圖的集合，數(shù)據(jù)字典的所有者為sys用戶。 用戶只能在數(shù)據(jù)字典上執(zhí)行查詢操作(select語(yǔ)句)，而其維護(hù)和修改是由系統(tǒng)自動(dòng)完成的。 這里我們談?wù)剶?shù)據(jù)字典的組成：數(shù)據(jù)字典包括數(shù)據(jù)字典基表和數(shù)據(jù)字典視圖，其中基表存儲(chǔ)數(shù)據(jù)庫(kù)的基本信息，普通用戶不能直接訪問(wèn)數(shù)據(jù)字典的基表。數(shù)據(jù)字典視圖是基于數(shù)據(jù)字典基表所建立的視圖，普通用戶可以通過(guò)查詢數(shù)據(jù)字典視圖取得系統(tǒng)信息。數(shù)據(jù)字典視圖主要包括user_xxx，all_xxx，dba_xxx三種類型。 ? user_tables; 用于顯示當(dāng)前用戶所擁有的所有表，它只返回用戶所對(duì)應(yīng)方案的所有表 比如：select table_name from user_tables; ? all_tables; 用于顯示當(dāng)前用戶可以訪問(wèn)的所有表，它不僅會(huì)返回當(dāng)前用戶方案的所有表，還會(huì)返回當(dāng)前用戶可以訪問(wèn)的其它方案的表： 比如：select table_name from all_tables; ? dba_tables; 它會(huì)顯示所有方案擁有的數(shù)據(jù)庫(kù)表。但是查詢這種數(shù)據(jù)庫(kù)字典視圖，要求用戶必須是dba角色或是有select any table系統(tǒng)權(quán)限。 例如：當(dāng)用system用戶查詢數(shù)據(jù)字典視圖dba_tables時(shí)，會(huì)返回system，sys，scott...方案所對(duì)應(yīng)的數(shù)據(jù)庫(kù)表。 ? 用戶名，權(quán)限，角色 在建立用戶時(shí)，oracle會(huì)把用戶的信息存放到數(shù)據(jù)字典中，當(dāng)給用戶授予權(quán)限或是角色時(shí)，oracle會(huì)將權(quán)限和角色的信息存放到數(shù)據(jù)字典。 通過(guò)查詢dba_users可以顯示所有數(shù)據(jù)庫(kù)用戶的詳細(xì)信息; 通過(guò)查詢數(shù)據(jù)字典視圖dba_sys_privs，可以顯示用戶所具有的系統(tǒng)權(quán)限; 通過(guò)查詢數(shù)據(jù)字典視圖dba_tab_privs，可以顯示用戶具有的對(duì)象權(quán)限; 通過(guò)查詢數(shù)據(jù)字典dba_col_privs可以顯示用戶具有的列權(quán)限; 通過(guò)查詢數(shù)據(jù)庫(kù)字典視圖dba_role_privs可以顯示用戶所具有的角色。
Oracle 筆記
25
這里給大家再講講角色和權(quán)限的關(guān)系。 例如：要查看scott具有的角色，可查詢dba_role_privs; SQL> select * from dba_role_privs where grantee='SCOTT'; //查詢orale中所有的系統(tǒng)權(quán)限，一般是dba select * from system_privilege_map order by name; //查詢oracle中所有對(duì)象權(quán)限，一般是dba select distinct privilege from dba_tab_privs; //查詢oracle中所有的角色，一般是dba select * from dba_roles; //查詢數(shù)據(jù)庫(kù)的表空間 select tablespace_name from dba_tablespaces; 問(wèn)題1：如何查詢一個(gè)角色包括的權(quán)限? a.一個(gè)角色包含的系統(tǒng)權(quán)限 select * from dba_sys_privs where grantee='角色名' 另外也可以這樣查看： select * from role_sys_privs where role='角色名' b.一個(gè)角色包含的對(duì)象權(quán)限 select * from dba_tab_privs where grantee='角色名' 問(wèn)題2：oracle究竟有多少種角色? SQL> select * from dba_roles; 問(wèn)題3：如何查看某個(gè)用戶，具有什么樣的角色? select * from dba_role_privs where grantee='用戶名' ? 顯示當(dāng)前用戶可以訪問(wèn)的所有數(shù)據(jù)字典視圖。 select * from dict where comments like '%grant%'; ? 顯示當(dāng)前數(shù)據(jù)庫(kù)的全稱 select * from global_name; ? 其它說(shuō)明 數(shù)據(jù)字典記錄有oracle數(shù)據(jù)庫(kù)的所有系統(tǒng)信息。通過(guò)查詢數(shù)據(jù)字典可以取得以下系統(tǒng)信息：比如 1.對(duì)象定義情況 2.對(duì)象占用空間大小 3.列信息 4.約束信息 ... 但是因?yàn)檫@些個(gè)信息，可以通過(guò)pl/sql developer工具查詢得到，所以這里我就飄過(guò)。 ? 動(dòng)態(tài)性能視圖 動(dòng)態(tài)性能視圖用于記錄當(dāng)前例程的活動(dòng)信息，當(dāng)啟動(dòng)oracle server時(shí)，系統(tǒng)會(huì)建立動(dòng)態(tài)性能視圖;當(dāng)停止oracle server時(shí)，系統(tǒng)會(huì)刪除動(dòng)態(tài)性能視圖。oracle的所有動(dòng)態(tài)性能視圖都是以v_$開(kāi)始的，并且oracle為每個(gè)動(dòng)態(tài)性能視圖都提供了相應(yīng)的同義詞，并且其同義詞是以V$開(kāi)始的，例如v_$datafile的同義詞為v$datafile;動(dòng)態(tài)性能視圖的所有者為sys，一般情況下，由dba或是特權(quán)用戶來(lái)查詢動(dòng)態(tài)性能視圖。 因?yàn)檫@個(gè)在實(shí)際中用的較少，所以飛過(guò)。
12.數(shù)據(jù)庫(kù)管理 -- 管理表空間和數(shù)據(jù)文件 ? 介紹 表空間是數(shù)據(jù)庫(kù)的邏輯組成部分。從物理上講，數(shù)據(jù)庫(kù)數(shù)據(jù)存放在數(shù)據(jù)文件中;從邏輯上講，數(shù)據(jù)庫(kù)則是存放在表空間中，表空間由一個(gè)或多個(gè)數(shù)據(jù)文件組成。
Oracle 筆記
26
數(shù)據(jù)庫(kù)的邏輯結(jié)構(gòu) ? 介紹 oracle中邏輯結(jié)構(gòu)包括表空間、段、區(qū)和塊。 說(shuō)明一下數(shù)據(jù)庫(kù)由表空間構(gòu)成，而表空間又是由段構(gòu)成，而段又是由區(qū)構(gòu)成，而區(qū)又是由oracle塊構(gòu)成的這樣的一種結(jié)構(gòu)，可以提高數(shù)據(jù)庫(kù)的效率。 為了讓大家明白，我們畫(huà)圖說(shuō)明邏輯關(guān)系：看圖： 表空間 ? 介紹 表空間用于從邏輯上組織數(shù)據(jù)庫(kù)的數(shù)據(jù)。數(shù)據(jù)庫(kù)邏輯上是由一個(gè)或是多個(gè)表空間組成的。通過(guò)表空間可以達(dá)到以下作用： 1. 控制數(shù)據(jù)庫(kù)占用的磁盤(pán)空間 2. dba可以將不同數(shù)據(jù)類型部署到不同的位置，這樣有利于提高i/o性能，同時(shí)利于備份和恢復(fù)等管理操作。 ? 建立表空間 建立表空間是使用crate tablespace命令完成的，需要注意的是，一般情況下，建立表空間是特權(quán)用戶或是dba來(lái)執(zhí)行的，如果用其它用戶來(lái)創(chuàng)建表空間，則用戶必須要具有create tablespace的系統(tǒng)權(quán)限。 ? 建立數(shù)據(jù)表空間 在建立數(shù)據(jù)庫(kù)后，為便于管理表，最好建立自己的表空間 create tablespace data01 datafile 'd:\test\dada01.dbf' size 20m uniform size 128k; 說(shuō)明：執(zhí)行完上述命令后，會(huì)建立名稱為data01的表空間，并為該表空間建立名稱為data01.dbf的數(shù)據(jù)文件，區(qū)的大小為128k ? 使用數(shù)據(jù)表空間 create table mypart(deptno number(4), dname varchar2(14), loc varchar2(13)) tablespace data01; ? 改變表空間的狀態(tài) 當(dāng)建立表空間時(shí)，表空間處于聯(lián)機(jī)的(online)狀態(tài)，此時(shí)該表空間是可以訪問(wèn)的，并且該表空間是可以讀寫(xiě)的，即可以查詢?cè)摫砜臻g的數(shù)據(jù)，而且還可以在表空間執(zhí)行各種語(yǔ)句。但是在進(jìn)行系統(tǒng)維護(hù)或是數(shù)據(jù)維護(hù)時(shí)，可能需要改變表空間的狀態(tài)。一般情況下，由特權(quán)用戶或是dba來(lái)操作。 1. 使表空間脫機(jī) alter tablespace 表空間名 offline; 2. 使表空間聯(lián)機(jī) alter tablespace 表空間名 online; 3. 只讀表空間 當(dāng)建立表空間時(shí)，表空間可以讀寫(xiě)，如果不希望在該表空間上執(zhí)行update，delete，insert操作，那么可以將表空間修改為只讀 alter tablespace 表空間名 read only; (修改為可寫(xiě)是 alter tablespace 表空間名 read write;) ? 改變表空間的狀態(tài) 我們給大家舉一個(gè)實(shí)例，說(shuō)明只讀特性： 1. 知道表空間名，顯示該表空間包括的所有表 select * from all_tables where tablespace_name=’表空間名’; 2. 知道表名，查看該表屬于那個(gè)表空間 select tablespace_name, table_name from user_tables where table_name=’emp’; 通過(guò)2.我們可以知道scott.emp是在system這個(gè)表空間上，現(xiàn)在我們可以將system改為只讀的但是我們不會(huì)成功，因?yàn)閟ystem是系統(tǒng)表空間，如果是普通表空間，那么我們就可以將其設(shè)為只讀的，給大家做一個(gè)演示，可以加強(qiáng)理解。 3. 4. 使表空間可讀寫(xiě) alter tablespace 表空間名 read write; ? 刪除表空間
Oracle 筆記
27
一般情況下，由特權(quán)用戶或是dba來(lái)操作，如果是其它用戶操作，那么要求用戶具有drop tablespace系統(tǒng)權(quán)限。 drop tablespace ‘表空間’ including contents and datafiles; 說(shuō)明：including contents表示刪除表空間時(shí)，刪除該空間的所有數(shù)據(jù)庫(kù)對(duì)象，而datafiles表示將數(shù)據(jù)庫(kù)文件也刪除。 ? 擴(kuò)展表空間 表空間是由數(shù)據(jù)文件組成的，表空間的大小實(shí)際上就是數(shù)據(jù)文件相加后的大小。那么我們可以想象，假定表employee存放到data01表空間上，初始大小就是2M，當(dāng)數(shù)據(jù)滿2M空間后，如果在向employee表插入數(shù)據(jù)，這樣就會(huì)顯示空間不足的錯(cuò)誤。 案例說(shuō)明： 1. 建立一個(gè)表空間 sp01 2. 在該表空間上建立一個(gè)普通表 mydment 其結(jié)構(gòu)和dept一樣 3. 向該表中加入數(shù)據(jù) insert into mydment select * from dept; 4. 當(dāng)一定時(shí)候就會(huì)出現(xiàn)無(wú)法擴(kuò)展的問(wèn)題，怎么辦? 5. 就擴(kuò)展該表空間，為其增加更多的存儲(chǔ)空間。有三種方法： 1. 增加數(shù)據(jù)文件 SQL> alter tablespace sp01 add datafile ‘d:\test\sp01.dbf’ size 20m; 2. 增加數(shù)據(jù)文件的大小 SQL> alter tablespace 表空間名 ‘d:\test\sp01.dbf’ resize 20m; 這里需要注意的是數(shù)據(jù)文件的大小不要超過(guò)500m。 3. 設(shè)置文件的自動(dòng)增長(zhǎng)。 SQL> alter tablespace 表空間名 ‘d:\test\sp01.dbf’ autoextend on next 10m maxsize 500m; ? 移動(dòng)數(shù)據(jù)文件 有時(shí)，如果你的數(shù)據(jù)文件所在的磁盤(pán)損壞時(shí)，該數(shù)據(jù)文件將不能再使用，為了能夠重新使用，需要將這些文件的副本移動(dòng)到其它的磁盤(pán)，然后恢復(fù)。 下面以移動(dòng)數(shù)據(jù)文件sp01.dbf為例來(lái)說(shuō)明： 1. 確定數(shù)據(jù)文件所在的表空間 select tablespace_name from dba_data_files where file_name=’d:\test\sp01.dbf’; 2. 使表空間脫機(jī) 確保數(shù)據(jù)文件的一致性，將表空間轉(zhuǎn)變?yōu)閛ffline的狀態(tài)。 alter tablespace sp01(表空間名) offline; 3. 使用命令移動(dòng)數(shù)據(jù)文件到指定的目標(biāo)位置 host move d:\test\sp01.dbf c:\test\sp01.dbf 4. 執(zhí)行alter tablespace命令 在物理上移動(dòng)了數(shù)據(jù)后，還必須執(zhí)行alter tablespace命令對(duì)數(shù)據(jù)庫(kù)文件進(jìn)行邏輯修改： alter tablespace sp01 rename datafile ‘d:\test\sp01.dbf’ to ‘c:\test\sp01.dbf’; 5. 使得表空間聯(lián)機(jī) 在移動(dòng)了數(shù)據(jù)文件后，為了使用戶可以訪問(wèn)該表空間，必須將其轉(zhuǎn)變?yōu)閛nline狀態(tài)。 alter tablespace sp01(表空間名) online; ? 顯示表空間信息 查詢數(shù)據(jù)字典視圖dba_tablespaces，顯示表空間的信息： select tablespace_name from dba_tablespaces; ? 顯示表空間所包含的數(shù)據(jù)文件 查詢數(shù)據(jù)字典視圖dba_data_files,可顯示表空間所包含的數(shù)據(jù)文件，如下： select file_name, bytes from dba_data_files where tablespce_name=’表空間’; ? 表空間小結(jié) 1. 了解表空間和數(shù)據(jù)文件的作用
Oracle 筆記
28
2. 掌握常用表空間，undo表空間和臨時(shí)表空間的建立方法 3. 了解表空間的各個(gè)狀態(tài)(online, offline, read write, read only)的作用，及如何改變表空間的狀態(tài)的方法。 4. 了解移動(dòng)數(shù)據(jù)文件的原因，及使用alter tablespace 和alter datatable命令移動(dòng)數(shù)據(jù)文件的方法。 ? 其它表空間 除了最常用的數(shù)據(jù)表空間外，還有其它類型表空間： 1. 索引表空間 2. undo表空間 3. 臨時(shí)表空間 4. 非標(biāo)準(zhǔn)塊的表空間 這幾種表空間，大家伙可以自己參考書(shū)籍研究，這里我就不講。 ? 其它說(shuō)明 關(guān)于表空間的組成部分 段/區(qū)/塊，我們?cè)诤竺娼o大家講解。
13.約束 玩轉(zhuǎn)oracle實(shí)戰(zhàn)教程(第五天) 期望目標(biāo) 1.掌握維護(hù)oracle數(shù)據(jù)完整性的技巧 2.理解索引概念，會(huì)建立索引 3.管理oracle的權(quán)限和角色 維護(hù)數(shù)據(jù)的完整性 ? 介紹 數(shù)據(jù)的完整性用于確保數(shù)據(jù)庫(kù)數(shù)據(jù)遵從一定的商業(yè)和邏輯規(guī)則，在oracle中，數(shù)據(jù)完整性可以使用約束、觸發(fā)器、應(yīng)用程序(過(guò)程、函數(shù))三種方法來(lái)實(shí)現(xiàn)，在這三種方法中，因?yàn)榧s束易于維護(hù)，并且具有最好的性能，所以作為維護(hù)數(shù)據(jù)完整性的首選。 約束 ? 約束 約束用于確保數(shù)據(jù)庫(kù)數(shù)據(jù)滿足特定的商業(yè)規(guī)則。在oracle中，約束包括：not null、 unique， primary key， foreign key，和check五種。 使用 ? not null(非空) 如果在列上定義了not null，那么當(dāng)插入數(shù)據(jù)時(shí)，必須為列提供數(shù)據(jù)。 ? unique(唯一) 當(dāng)定義了唯一約束后，該列值是不能重復(fù)的，但是可以為null。 ? primary key(主鍵) 用于唯一的標(biāo)示表行的數(shù)據(jù)，當(dāng)定義主鍵約束后，該列不但不能重復(fù)而且不能為null。 需要說(shuō)明的是：一張表最多只能有一個(gè)主鍵，但是可以有多個(gè)unqiue約束。 ? foreign key(外鍵) 用于定義主表和從表之間的關(guān)系。外鍵約束要定義在從表上，主表則必須具有主鍵約束或是unique約束，當(dāng)定義外鍵約束后，要求外鍵列數(shù)據(jù)必須在主表的主鍵列存在或是為null。 ? check 用于強(qiáng)制行數(shù)據(jù)必須滿足的條件，假定在sal列上定義了check約束，并要求sal列值在1000-2000之間如果不在1000-2000之間就會(huì)提示出錯(cuò)。 ? 商店售貨系統(tǒng)表設(shè)計(jì)案例 現(xiàn)有一個(gè)商店的數(shù)據(jù)庫(kù)，記錄客戶及其購(gòu)物情況，由下面三個(gè)表組成：商品goods(商品號(hào)goodsId，商品名 goodsName，單價(jià) unitprice，商品類別category，供應(yīng)商provider); 客戶customer(客戶號(hào)customerId，姓名name，住在address，電郵email，性別sex，身份證cardId);
Oracle 筆記
29
購(gòu)買(mǎi)purchase(客戶號(hào)customerId，商品號(hào)goodsId，購(gòu)買(mǎi)數(shù)量nums); 請(qǐng)用SQL語(yǔ)言完成下列功能： 1. 建表，在定義中要求聲明： (1). 每個(gè)表的主外鍵; (2). 客戶的姓名不能為空值; (3). 單價(jià)必須大于0，購(gòu)買(mǎi)數(shù)量必須在1到30之間; (4). 電郵不能夠重復(fù); (5). 客戶的性別必須是 男 或者 女，默認(rèn)是男; SQL> create table goods(goodsId char(8) primary key, --主鍵 goodsName varchar2(30)， unitprice number(10,2) check(unitprice>0), category varchar2(8), provider varchar2(30) ); SQL> create table customer( customerId char(8) primary key, --主鍵 name varchar2(50) not null, --不為空 address varchar2(50), email varchar2(50) unique, sex char(2) default '男' check(sex in ('男'，'女'))， -- 一個(gè)char能存半個(gè)漢字，兩位char能存一個(gè)漢字 cardId char(18) ); SQL> create table purchase( customerId char(8) references customer(customerId), goodsId char(8) references goods(goodsId), nums number(10) check (nums between 1 and 30) ); 表是默認(rèn)建在SYSTEM表空間的 維護(hù) ? 商店售貨系統(tǒng)表設(shè)計(jì)案例(2) 如果在建表時(shí)忘記建立必要的約束，則可以在建表后使用alter table命令為表增加約束。但是要注意：增加not null約束時(shí)，需要使用modify選項(xiàng)，而增加其它四種約束使用add選項(xiàng)。 1. 增加商品名也不能為空 SQL> alter table goods modify goodsName not null; 2. 增加身份證也不能重復(fù) SQL> alter table customer add constraint xxxxxx unique(cardId); 3. 增加客戶的住址只能是’海淀’,’朝陽(yáng)’,’東城’,’西城’,’通州’,’崇文’,’昌平’; SQL> alter table customer add constraint yyyyyy check (address in (’海淀’,’朝陽(yáng)’,’東城’,’西城’,’通州’,’崇文’,’昌平’)); ? 刪除約束 當(dāng)不再需要某個(gè)約束時(shí)，可以刪除。 alter table 表名 drop constraint 約束名稱; 特別說(shuō)明一下： 在刪除主鍵約束的時(shí)候，可能有錯(cuò)誤，比如： alter table 表名 drop primary key; 這是因?yàn)槿绻趦蓮埍泶嬖谥鲝年P(guān)系，那么在刪除主表的主鍵約束時(shí)，必須帶上cascade選項(xiàng) 如像：
Oracle 筆記
30
alter table 表名 drop primary key cascade; ? 顯示約束信息 1.顯示約束信息 通過(guò)查詢數(shù)據(jù)字典視圖user_constraints，可以顯示當(dāng)前用戶所有的約束的信息。 select constraint_name, constraint_type, status, validated from user_constraints where table_name = '表名'; 2.顯示約束列 通過(guò)查詢數(shù)據(jù)字典視圖user_cons_columns，可以顯示約束所對(duì)應(yīng)的表列信息。 select column_name, position from user_cons_columns where constraint_name = '約束名'; 3.當(dāng)然也有更容易的方法，直接用pl/sql developer查看即可。簡(jiǎn)單演示一下下... 表級(jí)定義 列級(jí)定義 ? 列級(jí)定義 列級(jí)定義是在定義列的同時(shí)定義約束。 如果在department表定義主鍵約束 create table department4(dept_id number(12) constraint pk_department primary key, name varchar2(12), loc varchar2(12)); ? 表級(jí)定義 表級(jí)定義是指在定義了所有列后，再定義約束。這里需要注意： not null約束只能在列級(jí)上定義。 以在建立employee2表時(shí)定義主鍵約束和外鍵約束為例： create table employee2(emp_id number(4), name varchar2(15), dept_id number(2), constraint pk_employee primary key (emp_id), constraint fk_department foreign key (dept_id) references department4(dept_id));
14.Oracle索引、權(quán)限
管理索引-原理介紹 ? 介紹 索引是用于加速數(shù)據(jù)存取的數(shù)據(jù)對(duì)象。合理的使用索引可以大大降低i/o次數(shù)，從而提高數(shù)據(jù)訪問(wèn)性能。索引有很多種我們主要介紹常用的幾種： 為什么添加了索引后，會(huì)加快查詢速度呢? 創(chuàng)建索引 ? 單列索引 單列索引是基于單個(gè)列所建立的索引，比如： create index 索引名 on 表名(列名); ? 復(fù)合索引 復(fù)合索引是基于兩列或是多列的索引。在同一張表上可以有多個(gè)索引，但是要求列的組合必須不同，比如： create index emp_idx1 on emp (ename, job); create index emp_idx1 on emp (job, ename); 使用原則 ? 使用原則 1. 在大表上建立索引才有意義 2. 在where子句或是連接條件上經(jīng)常引用的列上建立索引 3. 索引的層次不要超過(guò)4層 這里能不能給學(xué)生演示這個(gè)效果呢? 如何構(gòu)建一個(gè)大表呢?
Oracle 筆記
31
索引的缺點(diǎn) ? 索引缺點(diǎn)分析 索引有一些先天不足： 1. 建立索引，系統(tǒng)要占用大約為表1.2倍的硬盤(pán)和內(nèi)存空間來(lái)保存索引。 2. 更新數(shù)據(jù)的時(shí)候，系統(tǒng)必須要有額外的時(shí)間來(lái)同時(shí)對(duì)索引進(jìn)行更新，以維持?jǐn)?shù)據(jù)和索引的一致性。 實(shí)踐表明，不恰當(dāng)?shù)乃饕坏谑聼o(wú)補(bǔ)，反而會(huì)降低系統(tǒng)性能。因?yàn)榇罅康乃饕谶M(jìn)行插入、修改和刪除操作時(shí)比沒(méi)有索引花費(fèi)更多的系統(tǒng)時(shí)間。 比如在如下字段建立索引應(yīng)該是不恰當(dāng)?shù)模?1. 很少或從不引用的字段; 2. 邏輯型的字段，如男或女(是或否)等。 綜上所述，提高查詢效率是以消耗一定的系統(tǒng)資源為代價(jià)的，索引不能盲目的建立，這是考驗(yàn)一個(gè)DBA是否優(yōu)秀的很重要的指標(biāo)。 其它索引 ? 介紹 按照數(shù)據(jù)存儲(chǔ)方式，可以分為B*樹(shù)、反向索引、位圖索引; 按照索引列的個(gè)數(shù)分類，可以分為單列索引、復(fù)合索引; 按照索引列值的唯一性，可以分為唯一索引和非唯一索引。 此外還有函數(shù)索引，全局索引，分區(qū)索引... 對(duì)于索引我還要說(shuō)： 在不同的情況，我們會(huì)在不同的列上建立索引，甚至建立不同種類的索引，請(qǐng)記住，技術(shù)是死的，人是活的。比如： B*樹(shù)索引建立在重復(fù)值很少的列上，而位圖索引則建立在重復(fù)值很多、不同值相對(duì)固定的列上。 顯示索引信息 ? 顯示表的所有索引 在同一張表上可以有多個(gè)索引，通過(guò)查詢數(shù)據(jù)字典視圖dba_indexs和user_indexs，可以顯示索引信息。其中dba_indexs用于顯示數(shù)據(jù)庫(kù)所有的索引信息，而user_indexs用于顯示當(dāng)前用戶的索引信息： select index_name, index_type from user_indexes where table_name = '表名'; ? 顯示索引列 通過(guò)查詢數(shù)據(jù)字典視圖user_ind_columns,可以顯示索引對(duì)應(yīng)的列的信息 select table_name, column_name from user_ind_columns where index_name = 'IND_ENAME'; ? 你也可以通過(guò)pl/sql developer工具查看索引信息 管理權(quán)限和角色 介紹 ? 介紹 這一部分我們主要看看oracle中如何管理權(quán)限和角色，權(quán)限和角色的區(qū)別在那里。 當(dāng)剛剛建立用戶時(shí)，用戶沒(méi)有任何權(quán)限，也不能執(zhí)行任何操作。如果要執(zhí)行某種特定的數(shù)據(jù)庫(kù)操作，則必須為其授予系統(tǒng)的權(quán)限;如果用戶要訪問(wèn)其它方案的對(duì)象，則必須為其授予對(duì)象的權(quán)限。為了簡(jiǎn)化權(quán)限的管理，可以使用角色。這里我們會(huì)詳細(xì)的介紹�？磮D： 權(quán)限 ? 權(quán)限 權(quán)限是指執(zhí)行特定類型sql命令或是訪問(wèn)其它方案對(duì)象的權(quán)利，包括系統(tǒng)權(quán)限和對(duì)象權(quán)限兩種。 系統(tǒng)權(quán)限 ? 系統(tǒng)權(quán)限介紹 系統(tǒng)權(quán)限是指執(zhí)行特定類型sql命令的權(quán)利。它用于控制用戶可以執(zhí)行的一個(gè)或是一組數(shù)據(jù)庫(kù)操作。比如當(dāng)用戶具有create table權(quán)限時(shí)，可以在其方案中建表，當(dāng)用戶具有create any table權(quán)限時(shí)，可以在任何方案中建表。oracle提供了100多種系統(tǒng)權(quán)限。
Oracle 筆記
32
常用的有： create session 連接數(shù)據(jù)庫(kù) create table 建表 create view 建視圖 create public synonym 建同義詞 create procedure 建過(guò)程、函數(shù)、包 create trigger 建觸發(fā)器 create cluster 建簇 ? 顯示系統(tǒng)權(quán)限 oracle提供了100多種系統(tǒng)權(quán)限，而且oracle的版本越高，提供的系統(tǒng)權(quán)限就越多，我們可以查詢數(shù)據(jù)字典視圖system_privilege_map，可以顯示所有系統(tǒng)權(quán)限。 select * from system_privilege_map order by name; ? 授予系統(tǒng)權(quán)限 一般情況，授予系統(tǒng)權(quán)限是由dba完成的，如果用其他用戶來(lái)授予系統(tǒng)權(quán)限，則要求該用戶必須具有g(shù)rant any privilege的系統(tǒng)權(quán)限。在授予系統(tǒng)權(quán)限時(shí)，可以帶有with admin option選項(xiàng)，這樣，被授予權(quán)限的用戶或是角色還可以將該系統(tǒng)權(quán)限授予其它的用戶或是角色。為了讓大家快速理解，我們舉例說(shuō)明： 1.創(chuàng)建兩個(gè)用戶ken，tom。初始階段他們沒(méi)有任何權(quán)限，如果登錄就會(huì)給出錯(cuò)誤的信息。 create user ken identfied by ken; 2 給用戶ken授權(quán) 1). grant create session, create table to ken with admin option; 2). grant create view to ken; 3 給用戶tom授權(quán) 我們可以通過(guò)ken給tom授權(quán)，因?yàn)閣ith admin option是加上的。當(dāng)然也可以通過(guò)dba給tom授權(quán)，我們就用ken給tom授權(quán)： 1. grant create session, create table to tom; 2. grant create view to ken; --ok嗎?不ok ? 回收系統(tǒng)權(quán)限 一般情況下，回收系統(tǒng)權(quán)限是dba來(lái)完成的，如果其它的用戶來(lái)回收系統(tǒng)權(quán)限，要求該用戶必須具有相應(yīng)系統(tǒng)權(quán)限及轉(zhuǎn)授系統(tǒng)權(quán)限的選項(xiàng)(with admin option)�；厥障到y(tǒng)權(quán)限使用revoke來(lái)完成。 當(dāng)回收了系統(tǒng)權(quán)限后，用戶就不能執(zhí)行相應(yīng)的操作了，但是請(qǐng)注意，系統(tǒng)權(quán)限級(jí)聯(lián)收回的問(wèn)題?[不是級(jí)聯(lián)回收!] system --------->ken ---------->tom (create session)(create session)( create session) 用system執(zhí)行如下操作： revoke create session from ken; --請(qǐng)思考tom還能登錄嗎? 答案：能，可以登錄 對(duì)象權(quán)限 ? 對(duì)象權(quán)限介紹 指訪問(wèn)其它方案對(duì)象的權(quán)利，用戶可以直接訪問(wèn)自己方案的對(duì)象，但是如果要訪問(wèn)別的方案的對(duì)象，則必須具有對(duì)象的權(quán)限。 比如smith用戶要訪問(wèn)scott.emp表(scott：方案，emp：表) 常用的有： alter 修改 delete 刪除 select 查詢 insert 添加 update 修改 index 索引 references 引用 execute 執(zhí)行 ? 顯示對(duì)象權(quán)限 通過(guò)數(shù)據(jù)字段視圖可以顯示用戶或是角色所具有的對(duì)象權(quán)限。視圖為dba_tab_privs SQL> conn system/manager; SQL> select distinct privilege from dba_tab_privs; SQL> select grantor, owner, table_name, privilege from dba_tab_privs where grantee = 'BLAKE';
Oracle 筆記
33
1.授予對(duì)象權(quán)限 在oracle9i前，授予對(duì)象權(quán)限是由對(duì)象的所有者來(lái)完成的，如果用其它的用戶來(lái)操作，則需要用戶具有相應(yīng)的(with grant option)權(quán)限，從oracle9i開(kāi)始，dba用戶(sys，system)可以將任何對(duì)象上的對(duì)象權(quán)限授予其它用戶。授予對(duì)象權(quán)限是用grant命令來(lái)完成的。 對(duì)象權(quán)限可以授予用戶，角色，和public。在授予權(quán)限時(shí)，如果帶有with grant option選項(xiàng)，則可以將該權(quán)限轉(zhuǎn)授給其它用戶。但是要注意with grant option選項(xiàng)不能被授予角色。 1.monkey用戶要操作scott.emp表，則必須授予相應(yīng)的對(duì)象權(quán)限 1). 希望monkey可以查詢scott.emp表的數(shù)據(jù)，怎樣操作? grant select on emp to monkey; 2). 希望monkey可以修改scott.emp的表數(shù)據(jù)，怎樣操作? grant update on emp to monkey; 3). 希望monkey可以刪除scott.emp的表數(shù)據(jù)，怎樣操作? grant delete on emp to monkey; 4). 有沒(méi)有更加簡(jiǎn)單的方法，一次把所有權(quán)限賦給monkey? grant all on emp to monkey; 2.能否對(duì)monkey訪問(wèn)權(quán)限更加精細(xì)控制。(授予列權(quán)限) 1). 希望monkey只可以修改scott.emp的表的sal字段，怎樣操作? grant update on emp(sal) to monkey 2).希望monkey只可以查詢scott.emp的表的ename，sal數(shù)據(jù)，怎樣操作? grant select on emp(ename,sal) to monkey ... 3.授予alter權(quán)限 如果black用戶要修改scott.emp表的結(jié)構(gòu)，則必須授予alter對(duì)象權(quán)限 SQL> conn scott/tiger SQL> grant alter on emp to blake; 當(dāng)然也可以用system，sys來(lái)完成這件事。 4.授予execute權(quán)限 如果用戶想要執(zhí)行其它方案的包/過(guò)程/函數(shù)，則須有execute權(quán)限。 比如為了讓ken可以執(zhí)行包dbms_transaction，可以授予execute權(quán)限。 SQL> conn system/manager SQL> grant execute on dbms_transaction to ken; 5.授予index權(quán)限 如果想在別的方案的表上建立索引，則必須具有index對(duì)象權(quán)限。 如果為了讓black可以在scott.emp表上建立索引，就給其index的對(duì)象權(quán)限 SQL> conn scott/tiger SQL> grant index on scott.emp to blake; 6.使用with grant option選項(xiàng) 該選項(xiàng)用于轉(zhuǎn)授對(duì)象權(quán)限。但是該選項(xiàng)只能被授予用戶，而不能授予角色 SQL> conn scott/tiger; SQL> grant select on emp to blake with grant option; SQL> conn black/shunping SQL> grant select on scott.emp to jones; ? 回收對(duì)象權(quán)限 在oracle9i中，收回對(duì)象的權(quán)限可以由對(duì)象的所有者來(lái)完成，也可以用dba用戶(sys，system)來(lái)完成。 這里要說(shuō)明的是：收回對(duì)象權(quán)限后，用戶就不能執(zhí)行相應(yīng)的sql命令，但是要注意的是對(duì)象的權(quán)限是否會(huì)被級(jí)聯(lián)收回?【級(jí)
Oracle 筆記
34
聯(lián)回收】 如：scott------------->blake-------------->jones select on emp select on emp select on emp SQL> conn scott/tiger@accp SQL> revoke select on emp from blake 請(qǐng)大家思考，jones能否查詢scott.emp表數(shù)據(jù)。 答案：查不了了(和系統(tǒng)權(quán)限不一樣，剛好相反)
15.角色
? 介紹 角色就是相關(guān)權(quán)限的命令集合，使用角色的主要目的就是為了簡(jiǎn)化權(quán)限的管理，假定有用戶a，b，c為了讓他們都擁有權(quán)限 1. 連接數(shù)據(jù)庫(kù) 2. 在scott.emp表上select，insert，update。 如果采用直接授權(quán)操作，則需要進(jìn)行12次授權(quán)。 因?yàn)橐M(jìn)行12次授權(quán)操作，所以比較麻煩喔!怎么辦? 如果我們采用角色就可以簡(jiǎn)化： 首先將creat session，select on scott.emp，insert on scott.emp， update on scott.emp授予角色，然后將該角色授予a，b，c用戶，這樣就可以三次授權(quán)搞定。 角色分為預(yù)定義和自定義角色兩類： ? 預(yù)定義角色 預(yù)定義角色是指oracle所提供的角色，每種角色都用于執(zhí)行一些特定的管理任務(wù)，下面我們介紹常用的預(yù)定義角色connect，resource，dba 1.connect角色 connect角色具有一般應(yīng)用開(kāi)發(fā)人員需要的大部分權(quán)限，當(dāng)建立了一個(gè)用戶后，多數(shù)情況下，只要給用戶授予connect和resource角色就夠了，那么connect角色具有哪些系統(tǒng)權(quán)限呢? alter session create cluster create database link create session create table create view create sequence 2.resource角色 resource角色具有應(yīng)用開(kāi)發(fā)人員所需要的其它權(quán)限，比如建立存儲(chǔ)過(guò)程，觸發(fā)器等。這里需要注意的是resource角色隱含了unlimited tablespace系統(tǒng)權(quán)限。 resource角色包含以下系統(tǒng)權(quán)限： create cluster create indextype create table create sequence create type create procedure create trigger 3.dba角色
Oracle 筆記
35
dba角色具有所有的系統(tǒng)權(quán)限，及with admin option選項(xiàng)，默認(rèn)的dba用戶為sys和system，它們可以將任何系統(tǒng)權(quán)限授予其他用戶。但是要注意的是dba角色不具備sysdba和sysoper的特權(quán)(啟動(dòng)和關(guān)閉數(shù)據(jù)庫(kù))。 ? 自定義角色 顧名思義就是自己定義的角色，根據(jù)自己的需要來(lái)定義。一般是dba來(lái)建立，如果用別的用戶來(lái)建立，則需要具有create role的系統(tǒng)權(quán)限。在建立角色時(shí)可以指定驗(yàn)證方式(不驗(yàn)證，數(shù)據(jù)庫(kù)驗(yàn)證等)。 1.建立角色(不驗(yàn)證) 如果角色是公用的角色，可以采用不驗(yàn)證的方式建立角色。 create role 角色名 not identified; 2.建立角色(數(shù)據(jù)庫(kù)驗(yàn)證) 采用這樣的方式時(shí)，角色名、口令存放在數(shù)據(jù)庫(kù)中。當(dāng)激活該角色時(shí)，必須提供口令。在建立這種角色時(shí)，需要為其提供口令。 create role 角色名 identified by 密碼; 角色授權(quán) 當(dāng)建立角色時(shí)，角色沒(méi)有任何權(quán)限，為了使得角色完成特定任務(wù)，必須為其授予相應(yīng)的系統(tǒng)權(quán)限和對(duì)象權(quán)限。 1.給角色授權(quán) 給角色授予權(quán)限和給用戶授權(quán)沒(méi)有太多區(qū)別，但是要注意，系統(tǒng)權(quán)限的unlimited tablespace和對(duì)象權(quán)限的with grant option選項(xiàng)是不能授予角色的。 SQL> conn system/manager; SQL> grant create session to 角色名 with admin option SQL> conn scott/tiger@myoral; SQL> grant select on scott.emp to 角色名; SQL> grant insert, update, delete on scott.emp to 角色名; 通過(guò)上面的步驟，就給角色授權(quán)了。 2.分配角色給某個(gè)用戶 一般分配角色是由dba來(lái)完成的，如果要以其它用戶身份分配角色，則要求用戶必須具有g(shù)rant any role的系統(tǒng)權(quán)限。 SQL> conn system/manager; SQL> grant 角色名 to blake with admin option; 因?yàn)槲医o了with admin option選項(xiàng)，所以，blake可以把system分配給它的角色分配給別的用戶。 ? 刪除角色 使用drop role，一般是dba來(lái)執(zhí)行，如果其它用戶則要求該用戶具有drop any role系統(tǒng)權(quán)限。 SQL> conn system/manager; SQL> drop role 角色名; 問(wèn)題：如果角色被刪除，那么被授予角色的用戶是否還具有之前角色里的權(quán)限? 答案：不具有了 ? 顯示角色信息 1.顯示所有角色 SQL> select * from dba_roles; 2.顯示角色具有的系統(tǒng)權(quán)限 SQL> select privilege, admin_option from role_sys_privs where role='角色名'; 3.顯示角色具有的對(duì)象權(quán)限 通過(guò)查詢數(shù)據(jù)字典視圖dba_tab_privs可以查看角色具有的對(duì)象權(quán)限或是列的權(quán)限。 4.顯示用戶具有的角色，及默認(rèn)角色 當(dāng)以用戶的身份連接到數(shù)據(jù)庫(kù)時(shí)，oracle會(huì)自動(dòng)的激活默認(rèn)的角色，通過(guò)查詢數(shù)據(jù)字典視圖dba_role_privs可以顯示某個(gè)用戶具有的所有角色及當(dāng)前默認(rèn)的角色
Oracle 筆記
36
SQL> select granted_role, default_role from dba_role_privs where grantee = ‘用戶名’; ? 精細(xì)訪問(wèn)控制 精細(xì)訪問(wèn)控制是指用戶可以使用函數(shù)，策略實(shí)現(xiàn)更加細(xì)微的安全訪問(wèn)控制。如果使用精細(xì)訪問(wèn)控制，則當(dāng)在客戶端發(fā)出sql語(yǔ)句(select，insert，update，delete)時(shí)，oracle會(huì)自動(dòng)在sql語(yǔ)句后追加謂詞(where子句)，并執(zhí)行新的sql語(yǔ)句，通過(guò)這樣的控制，可以使得不同的數(shù)據(jù)庫(kù)用戶在訪問(wèn)相同表時(shí)，返回不同的數(shù)據(jù)信息，如： 用戶 scott blake jones 策略 emp_access 數(shù)據(jù)庫(kù)表 emp 如上圖所示，通過(guò)策略emp_access，用戶scott，black，jones在執(zhí)行相同的sql語(yǔ)句時(shí)，可以返回不同的結(jié)果。例如：當(dāng)執(zhí)行select ename from emp; 時(shí)，根據(jù)實(shí)際情況可以返回不同的結(jié)果。
16.PL/SQL 塊的結(jié)構(gòu)和實(shí)例 韓順平.玩轉(zhuǎn)oralce第24講.plsql編程(1) 玩轉(zhuǎn)orcle實(shí)戰(zhàn)教程(第六天) 內(nèi)容介紹 1.上節(jié)回顧 2.pl/sql的介紹 √ 3.pl/sql的基礎(chǔ) √ 期望目標(biāo) 1.理解oracle的pl/sql概念 2.掌握pl/sql編程技術(shù)(包括編寫(xiě)過(guò)程、函數(shù)、觸發(fā)器...) pl/sql的介紹 pl/sql是什么 pl/sql(procedural language/sql)是oracle在標(biāo)準(zhǔn)的sql語(yǔ)言上的擴(kuò)展。pl/sql不僅允許嵌入sql語(yǔ)言，還可以定義變量和常量，允許使用條件語(yǔ)句和循環(huán)語(yǔ)句，允許使用例外處理各種錯(cuò)誤，這樣使得它的功能變得更加強(qiáng)大。 為什么學(xué)pl/sql ? 學(xué)習(xí)必要性 1.提高應(yīng)用程序的運(yùn)行性能 2.模塊化的設(shè)計(jì)思想【分頁(yè)的過(guò)程，訂單的過(guò)程，轉(zhuǎn)賬的過(guò)程。。】 3.減少網(wǎng)絡(luò)傳輸量 4.提高安全性(sql會(huì)包括表名，有時(shí)還可能有密碼，傳輸?shù)臅r(shí)候會(huì)泄露。PL/SQL就不會(huì)) 為什么PL/SQL會(huì)快呢?看圖： 不好的地方： 移植性不好(換數(shù)據(jù)庫(kù)就用不了)， 用什么編寫(xiě)pl/sql ? sqlplus開(kāi)發(fā)工具 sqlplus是oracle公司提供的一個(gè)工具，這個(gè)因?yàn)槲覀冊(cè)谝郧敖榻B過(guò)的： 舉一個(gè)簡(jiǎn)單的案例： 編寫(xiě)一個(gè)存儲(chǔ)過(guò)程，該過(guò)程可以向某表中添加記錄。 1.創(chuàng)建一個(gè)簡(jiǎn)單的表
create table mytest(name varchar2(30),passwd varchar2(30));
2.創(chuàng)建過(guò)程
create or replace procedure sp_pro1 is
Oracle 筆記
37
begin--執(zhí)行部分
insert into mytest values('韓順平','m1234');
end;
/ replace:表示如果有sp_pro1，就替換 如何查看錯(cuò)誤信息：show error; 如何調(diào)用該過(guò)程： 1)exec 過(guò)程名(參數(shù)值1，參數(shù)值2...); 2)call 過(guò)程名(參數(shù)值1，參數(shù)值2...); ? pl/sql developer開(kāi)發(fā)工具 pl/sql developer是用于開(kāi)發(fā)pl/sql塊的集成開(kāi)發(fā)環(huán)境(ide)，它是一個(gè)獨(dú)立的產(chǎn)品，而不是oracle的一個(gè)附帶品。 舉一個(gè)簡(jiǎn)單案例： 編寫(xiě)一個(gè)存儲(chǔ)過(guò)程，該過(guò)程可以刪除某表記錄。
create or replace procedure sp_pro2 is
begin--執(zhí)行部分
delete from mytest where name='韓順平';
end; pl/sql基礎(chǔ) pl/sql介紹 ? 介紹 開(kāi)發(fā)人員使用pl/sql編寫(xiě)應(yīng)用模塊時(shí)，不僅需要掌握sql語(yǔ)句的編寫(xiě)方法，還要掌握pl/sql語(yǔ)句及語(yǔ)法規(guī)則。pl/sql編程可以使用變量和邏輯控制語(yǔ)句，從而可以編寫(xiě)非常有用的功能模塊。比如：分頁(yè)存儲(chǔ)過(guò)程模塊、訂單處理存儲(chǔ)過(guò)程模塊、轉(zhuǎn)賬存儲(chǔ)過(guò)程模塊。而且如果使用pl/sql編程，我們可以輕松地完成非常復(fù)雜的查詢要求。 pl/sql可以做什么 ? 簡(jiǎn)單分類 |————過(guò)程(存儲(chǔ)過(guò)程) | |————函數(shù) 塊(編程)—————| |————觸發(fā)器 | |————包 編寫(xiě)規(guī)范 ? 編寫(xiě)規(guī)范 1.注釋 單行注釋 --
select * from emp where empno=7788; --取得員工信息 多行注釋 /*...*/來(lái)劃分 2.標(biāo)志符號(hào)的命名規(guī)范 1).當(dāng)定義變量時(shí)，建議用v_作為前綴v_sal 2).當(dāng)定義常量時(shí)，建議用c_作為前綴c_rate 3).當(dāng)定義游標(biāo)時(shí)，建議用_cursor作為后綴emp_cursor 4).當(dāng)定義例外時(shí)，建議用e_作為前綴e_error pl/sql塊介紹 ? 介紹
Oracle 筆記
38
塊(block)是pl/sql的基本程序單元，編寫(xiě)pl/sql程序?qū)嶋H上就是編寫(xiě)pl/sql塊，要完成相對(duì)簡(jiǎn)單的應(yīng)用功能，可能只需要編寫(xiě)一個(gè)pl/sql塊，但是如果想要實(shí)現(xiàn)復(fù)雜的功能，可能需要在一個(gè)pl/sql塊中嵌套其它的pl/sql塊。 ? 塊結(jié)構(gòu)示意圖 pl/sql塊由三個(gè)部分構(gòu)成：定義部分，執(zhí)行部分，例外處理部分。 如下所示： declare /*定義部分——定義常量、變量、游標(biāo)、例外、復(fù)雜數(shù)據(jù)類型*/ begin /*執(zhí)行部分——要執(zhí)行的pl/sql語(yǔ)句和sql語(yǔ)句*/ exception /*例外處理部分——處理運(yùn)行的各種錯(cuò)誤*/ end; 定義部分是從declare開(kāi)始的，該部分是可選的; 執(zhí)行部分是從begin開(kāi)始的，該部分是必須的; 例外處理部分是從exception開(kāi)始的，該部分是可選的。 可以和java編程結(jié)構(gòu)做一個(gè)簡(jiǎn)單的比較。 pl/sql塊的實(shí)例(1) ? 實(shí)例1-只包括執(zhí)行部分的pl/sql塊
set serveroutput on --打開(kāi)輸出選項(xiàng)
begin
dbms_output.put_line('hello');
end;
相關(guān)說(shuō)明： dbms_output是oracle所提供的包(類似java的開(kāi)發(fā)包)，該包包含一些過(guò)程，put_line就是dbms_output包的一個(gè)過(guò)程。 pl/sql塊的實(shí)例(2) ? 實(shí)例2-包含定義部分和執(zhí)行部分的pl/sql塊
declare
v_ename varchar2(5); --定義字符串變量
begin
select ename into v_ename from emp where empno=&aa;
dbms_output.put_line('雇員名：'||v_ename);
end;
/ 如果要把薪水也顯示出來(lái)，那么執(zhí)行部分就應(yīng)該這么寫(xiě)：
select ename,sal into v_ename,v_sal from emp where empno=&aa;
相關(guān)說(shuō)明： & 表示要接收從控制臺(tái)輸入的變量。 pl/sql塊的實(shí)例(3) ? 實(shí)例3-包含定義部分，執(zhí)行部分和例外處理部分 為了避免pl/sql程序的運(yùn)行錯(cuò)誤，提高pl/sql的健壯性，應(yīng)該對(duì)可能的錯(cuò)誤進(jìn)行處理，這個(gè)很有必要。 1.比如在實(shí)例2中，如果輸入了不存在的雇員號(hào)，應(yīng)當(dāng)做例外處理。 2.有時(shí)出現(xiàn)異常，希望用另外的邏輯處理，[網(wǎng)示] 我們看看如何完成1的要求。 相關(guān)說(shuō)明： oracle事先預(yù)定義了一些例外，no_data_found就是找不到數(shù)據(jù)的例外。
Oracle 筆記
39
declare
--定義變量
v_ename varchar2(5);
v_sal number(7,2);
begin
--執(zhí)行部分
select ename,sal into v_ename,v_sal from emp where empno=&aa;
--在控制臺(tái)顯示用戶名
dbms_output.put_line('用戶名是：'||v_ename||' 工資：'||v_sal);
--異常處理
exception
when no_data_found then
dbms_output.put_line('朋友，你的編號(hào)輸入有誤!');
end;
/
17.pl/sql分類 -- 過(guò)程，函數(shù)，包，觸發(fā)器
? 過(guò)程 過(guò)程用于執(zhí)行特定的操作，當(dāng)建立過(guò)程時(shí)，既可以指定輸入?yún)?shù)(in)，也可以指定輸出參數(shù)(out)， 通過(guò)在過(guò)程中使用輸入?yún)?shù)，可以將數(shù)據(jù)傳遞到執(zhí)行部分;通過(guò)使用輸出參數(shù)，可以將執(zhí)行部分的數(shù)據(jù)傳遞到應(yīng)用環(huán)境。在sqlplus中可以使用create procedure命令來(lái)建立過(guò)程。 實(shí)例如下： 1.請(qǐng)考慮編寫(xiě)一個(gè)過(guò)程，可以輸入雇員名，新工資，可修改雇員的工資 2.如何調(diào)用過(guò)程有兩種方法; exec call 3.如何在java程序中調(diào)用一個(gè)存儲(chǔ)過(guò)程 問(wèn)題：如何使用過(guò)程返回值? 特別說(shuō)明： 對(duì)于過(guò)程我們會(huì)在以后給大家詳細(xì)具體的介紹，現(xiàn)在請(qǐng)大家先有一個(gè)概念。 create procedure sp_pro3(spName varchar2, newSal number) is --不要寫(xiě)成number(3,2),表明類型就可以了，不需要大小。就好像Java寫(xiě)方法時(shí)的參數(shù)一樣
begin
--執(zhí)行部分，根據(jù)用戶名去修改工資
update emp set sal=newSal where ename=spName;
end;
/ java程序中調(diào)用一個(gè)存儲(chǔ)過(guò)程 //演示java程序去調(diào)用oracle的存儲(chǔ)過(guò)程案例
import java.sql.*;
public class TestOraclePro{
public static void main(String[] args){
try{
//1.加載驅(qū)動(dòng)
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.得到連接
Oracle 筆記
40
Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");
//3.創(chuàng)建CallableStatement
CallableStatement cs = ct.prepareCall("{call sp_pro3(?,?)}");
//4.給?賦值
cs.setString(1,"SMITH");
cs.setInt(2,10);
//5.執(zhí)行
cs.execute();
//關(guān)閉
cs.close();
ct.close();
} catch(Exception e){
e.printStackTrace();
}
}
} ? 函數(shù) 函數(shù)用于返回特定的數(shù)據(jù)，當(dāng)建立函數(shù)時(shí)，在函數(shù)頭部必須包含return子句。而在函數(shù)體內(nèi)必須包含return語(yǔ)句返回的數(shù)據(jù)。我們可以使用create function來(lái)建立函數(shù)，實(shí)際案例：
--輸入雇員的姓名，返回該雇員的年薪
create function annual_incomec(name varchar2)
return number is
annual_salazy number(7,2);
begin
--執(zhí)行部分
select sal*12+nvl(comm, 0) into annual_salazy from emp where ename=name;
return annual_salazy;
end;
/
如果函數(shù)創(chuàng)建過(guò)程有編譯錯(cuò)誤，可以使用show error;命令顯示錯(cuò)誤 在sqlplus中調(diào)用函數(shù)
SQL> var income number
SQL> call annual_incomec('scott') into: income;
SQL> print income 同樣我們可以在java程序中調(diào)用該函數(shù) select annual_income('SCOTT') from dual; 這樣可以通過(guò)rs.getInt(l)得到返回的結(jié)果。 ? 包 包用于在邏輯上組合過(guò)程和函數(shù)，它由包規(guī)范和包體兩部分組成。 1.我們可以使用create package命令來(lái)創(chuàng)建包。 實(shí)例： --創(chuàng)建一個(gè)包sp_package --聲明該包有一個(gè)過(guò)程update_sal --聲明該包有一個(gè)函數(shù)annual_income
Oracle 筆記
41
create package sp_package is
procedure update_sal(name varchar2, newsal number);
function annual_income(name varchar2) return number;
end; 包的規(guī)范只包含了過(guò)程和函數(shù)的說(shuō)明，但是沒(méi)有過(guò)程和函數(shù)的實(shí)現(xiàn)代碼。包體用于實(shí)現(xiàn)包規(guī)范中的過(guò)程和函數(shù)。 2.建立包體可以使用create package body命令 --給包sp_package實(shí)現(xiàn)包體
create or replace package body sp_package is
procedure update_sal(name varchar2, newsal number)
is
begin
update emp set sal = newsal where ename = name;
end;
function annual_income(name varchar2) return number is
annual_salary number;
begin
select sal * 12 + nvl(comm, 0) into annual_salary from emp
where ename = name;
return annual_salary;
end;
end;
/ 3.如何調(diào)用包的過(guò)程或是函數(shù) 當(dāng)調(diào)用包的過(guò)程或是函數(shù)時(shí)，在過(guò)程和函數(shù)前需要帶有包名，如果要訪問(wèn)其它方案的包，還需要在包名前加方案名。 如： SQL> call sp_package.update_sal('SCOTT', 1500); 特別說(shuō)明： 包是pl/sql中非常重要的部分，我們?cè)谑褂眠^(guò)程分頁(yè)時(shí)，將會(huì)再次體驗(yàn)它的威力呵呵。 ? 觸發(fā)器 觸發(fā)器是指隱含的執(zhí)行的存儲(chǔ)過(guò)程。當(dāng)定義觸發(fā)器時(shí)，必須要指定觸發(fā)的事件和觸發(fā)的操作，常用的觸發(fā)事件包括insert,update,delete語(yǔ)句，而觸發(fā)操作實(shí)際就是一個(gè)pl/sql塊。可以使用create trigger來(lái)建立觸發(fā)器。 特別說(shuō)明： 我們會(huì)在后面詳細(xì)為大家介紹觸發(fā)器的使用，因?yàn)橛|發(fā)器是非常有用的，可維護(hù)數(shù)據(jù)庫(kù)的安全和一致性。
18.定義并使用變量,復(fù)合類型 定義并使用變量 ? 介紹 在編寫(xiě)pl/sql程序時(shí)，可以定義變量和常量;在pl/sql程序中包括有： 1.標(biāo)量類型(scalar) 2.復(fù)合類型(composite) 3.參照類型(reference) 4.lob(large object) ? 標(biāo)量(scalar)——常用類型 在編寫(xiě)pl/sql塊時(shí)，如果要使用變量，需在定義部分定義變量。pl/sql中定義變量和常量的語(yǔ)法如下： identifier [constant] datatype [not null] [:=| default expr]
Oracle 筆記
42
identifier : 名稱 constant ：指定常量。需要指定它的初始值，且其值是不能改變的 datatype ：數(shù)據(jù)類型 not null ：指定變量值不能為null := 給變量或是常量指定初始值 default 用于指定初始值 expr ：指定初始值的pl/sql表達(dá)式，可以是文本值、其它變量、函數(shù)等。 ? 標(biāo)量定義的案例 1.定義一個(gè)變長(zhǎng)字符串 v_ename varchar2(10); 2.定義一個(gè)小數(shù),范圍 -9999.99~9999.99 v_sal number(6,2); 3.定義一個(gè)小數(shù)并給一個(gè)初始值為5.4 :=是pl/sql的賦值號(hào) v_sal2 number(6,2):=5.4; 4.定義一個(gè)日期類型的數(shù)據(jù) v_hiredate date; 5.定義一個(gè)布爾變量，不能為空，初始值為false v_valid boolean not null default false; ? 標(biāo)量(scalar)——使用標(biāo)量 在定義好變量后，就可以使用這些變量。這里需要說(shuō)明的是pl/sql塊為變量賦值不同于其它的編程語(yǔ)言，需要在等號(hào)前面加冒號(hào)(:=) 下面以輸入員工號(hào)，顯示雇員姓名、工資、個(gè)人所得稅(稅率為0.03)為例。說(shuō)明變量的使用，看看如何編寫(xiě)。
declare
c_tax_rate number(3,2):=0.03;
--用戶名
v_ename varchar2(5);
v_sal number(7,2);
v_tax_sal number(7,2);
begin
--執(zhí)行
select ename,sal into v_ename,v_sal from emp where empno=&no;
--計(jì)算所得稅
v_tax_sal := v_sal*c_tax_rate;
--輸出
dbms_output.put_line('姓名是：'||v_ename||'工資：'||v_sal||' 交稅：'||v_tax_sal);
end;
/ ? 標(biāo)量(scalar)——使用%type類型 對(duì)于上面的pl/sql塊有一個(gè)問(wèn)題： 就是如果員工的姓名超過(guò)了5個(gè)字符的話，就會(huì)有錯(cuò)誤，為了降低pl/sql程序的維護(hù)工作量，可以使用%type屬性定義變量，這樣它會(huì)按照數(shù)據(jù)庫(kù)列來(lái)確定你定義的變量的類型和長(zhǎng)度。 我們看看這個(gè)怎么使用： 標(biāo)識(shí)符名 表名.列名%type; 比如上例的v_ename，這樣定義： v_ename emp.ename%type;
Oracle 筆記
43
? 復(fù)合變量(composite)——介紹 用于存放多個(gè)值的變量。主要包括這幾種： 1.pl/sql記錄 2.pl/sql表 3.嵌套表 4.varray ? 復(fù)合類型——pl/sql記錄 類似于高級(jí)語(yǔ)言中的結(jié)構(gòu)體，需要注意的是，當(dāng)引用pl/sql記錄成員時(shí)，必須要加記錄變量作為前綴(記錄變量.記錄成員)如下：
declare
--定義一個(gè)pl/sql記錄類型emp_record_type,類型包含3個(gè)數(shù)據(jù)name,salary,title。說(shuō)白了，就是一個(gè)類型可以存放3個(gè)數(shù)據(jù)，主要是為了好管理
type emp_record_type is record(
name emp.ename%type,
salary emp.sal%type,
title emp.job%type);
--定義了一個(gè)sp_record變量，這個(gè)變量的類型是emp_record_type
sp_record emp_record_type;
begin
select ename, sal, job into sp_record from emp where empno = 7788;
dbms_output.put_line ('員工名:' || sp_record.name);
end; ? 復(fù)合類型-pl/sql表 相當(dāng)于高級(jí)語(yǔ)言中的數(shù)組，但是需要注意的是在高級(jí)語(yǔ)言中數(shù)組的下標(biāo)不能為負(fù)數(shù)，而pl/sql是可以為負(fù)數(shù)的，并且表元素的下標(biāo)沒(méi)有限制。實(shí)例如下：
declare
--定義了一個(gè)pl/sql表類型sp_table_type，該類型是用于存放emp.ename%type
--index by binary_integer 表示下標(biāo)是整數(shù)
type sp_table_type is table of emp.ename%type
index by binary_integer;
--定義了一個(gè)sp_table變量，這個(gè)變量的類型是sp_table_type
sp_table sp_table_type;
begin
select ename into sp_table(-1) from emp where empno = 7788;
dbms_output.put_line('員工名:' || sp_table(-1));
end;
說(shuō)明： sp_table_type 是pl/sql表類型 emp.ename%type 指定了表的元素的類型和長(zhǎng)度 sp_table 為pl/sql表變量 sp_table(0) 則表示下標(biāo)為0的元素 注意：如果把select ename into sp_table(-1) from emp where empno = 7788;變成select ename into sp_table(-1) from emp;則運(yùn)行時(shí)會(huì)出現(xiàn)錯(cuò)誤，錯(cuò)誤如下： ORA-01422:實(shí)際返回的行數(shù)超出請(qǐng)求的行數(shù) 解決方法是：使用參照變量(這里不講)
Oracle 筆記
44
? 復(fù)合變量——嵌套表(nested table) ? 復(fù)合變量——變長(zhǎng)數(shù)組(varray) ? 參照變量——介紹 參照變量是指用于存放數(shù)值指針的變量。通過(guò)使用參照變量，可以使得應(yīng)用程序共享相同對(duì)象，從而降低占用的空間。在編寫(xiě)pl/sql程序時(shí)，可以使用游標(biāo)變量(ref cursor)和對(duì)象類型變量(ref obj_type)兩種參照變量類型。 ? 參照變量——ref cursor游標(biāo)變量 使用游標(biāo)時(shí)，當(dāng)定義游標(biāo)時(shí)不需要指定相應(yīng)的select語(yǔ)句，但是當(dāng)使用游標(biāo)時(shí)(open時(shí))需要指定select語(yǔ)句，這樣一個(gè)游標(biāo)就與一個(gè)select語(yǔ)句結(jié)合了。實(shí)例如下： 1.請(qǐng)使用pl/sql編寫(xiě)一個(gè)塊，可以輸入部門(mén)號(hào)，并顯示該部門(mén)所有員工姓名和他的工資。 2.在1的基礎(chǔ)上，如果某個(gè)員工的工資低于200元，就添加100元。 1.
declare
--定義游標(biāo)sp_emp_cursor
type sp_emp_cursor is ref cursor;
--定義一個(gè)游標(biāo)變量
test_cursor sp_emp_cursor;
--定義變量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
--執(zhí)行
--把test_cursor和一個(gè)select結(jié)合
open test_cursor for select ename,sal from emp where deptno=&no;
--循環(huán)取出
loop
fetch test_cursor into v_ename,v_sal;
--判斷是否test_cursor為空
exit when test_cursor%notfound;
dbms_output.put_line('名字：'||v_ename||' 工資：'||v_sal);
end loop;
end;
/
19.pl/sql的進(jìn)階--控制結(jié)構(gòu)(分支，循環(huán)，控制)
玩轉(zhuǎn)oracle實(shí)戰(zhàn)教程(第七天) 內(nèi)容介紹 1.上節(jié)回顧 2.pl/sql的進(jìn)階 √ 3.oracle的視圖(具有安全性，和簡(jiǎn)化復(fù)雜查詢的功能) √ 4.oracle的觸發(fā)器 √ 期望目標(biāo) 1.掌握pl/sql的高級(jí)用法(能縮寫(xiě)分頁(yè)過(guò)程模塊，下訂單過(guò)程模塊...) 2.會(huì)處理oracle常見(jiàn)的例外 3.會(huì)編寫(xiě)oracle各種觸發(fā)器
Oracle 筆記
45
4.理解視圖的概念并能靈活使用視圖 pl/sql的進(jìn)階--控制結(jié)構(gòu) ? 介紹 在任何計(jì)算機(jī)語(yǔ)言(c，java，pascal)都有各種控制語(yǔ)句(條件語(yǔ)句，循環(huán)結(jié)構(gòu)，順序控制結(jié)構(gòu)...)在pl/sql中也存在這樣的控制結(jié)構(gòu)。 在本部分學(xué)習(xí)完成后，希望大家達(dá)到： 1.使用各種if語(yǔ)句 2.使用循環(huán)語(yǔ)句 3.使用控制語(yǔ)句——goto和null; ? 條件分支語(yǔ)句 pl/sql中提供了三種條件分支語(yǔ)句if—then，if – then – else，if – then – elsif – then 這里我們可以和java語(yǔ)句進(jìn)行一個(gè)比較 ? 簡(jiǎn)單的條件判斷 if – then 問(wèn)題：編寫(xiě)一個(gè)過(guò)程，可以輸入一個(gè)雇員名，如果該雇員的工資低于2000，就給該員工工資增加10%。
create or replace procedure sp_pro6(spName varchar2) is
--定義
v_sal emp.sal%type;
begin
--執(zhí)行
select sal into v_sal from emp where ename=spName;
--判斷
if v_sal<2000 then
update emp set sal=sal+sal*10% where ename=spName;
end if;
end;
/ ? 二重條件分支 if – then – else 問(wèn)題：編寫(xiě)一個(gè)過(guò)程，可以輸入一個(gè)雇員名，如果該雇員的補(bǔ)助不是0就在原來(lái)的基礎(chǔ)上增加100;如果補(bǔ)助為0就把補(bǔ)助設(shè)為200;
create or replace procedure sp_pro6(spName varchar2) is
--定義
v_comm emp.comm%type;
begin
--執(zhí)行
select comm into v_comm from emp where ename=spName;
--判斷
if v_comm<>0 then
update emp set comm=comm+100 where ename=spName;
else
update emp set comm=comm+200 where ename=spName;
end if;
end;
/ ? 多重條件分支 if – then – elsif – then
Oracle 筆記
46
問(wèn)題：編寫(xiě)一個(gè)過(guò)程，可以輸入一個(gè)雇員編號(hào)，如果該雇員的職位是PRESIDENT就給他的工資增加1000，如果該雇員的職位是MANAGER就給他的工資增加500，其它職位的雇員工資增加200。
create or replace procedure sp_pro6(spNo number) is
--定義
v_job emp.job%type;
begin
--執(zhí)行
select job into v_job from emp where empno=spNo;
if v_job='PRESIDENT' then
update emp set sal=sal+1000 where empno=spNo;
elsif v_job='MANAGER' then
update emp set sal=sal+500 where empno=spNo;
else
update emp set sal=sal+200 where empno=spNo;
end if;
end;
/ ? 循環(huán)語(yǔ)句 –loop 是pl/sql中最簡(jiǎn)單的循環(huán)語(yǔ)句，這種循環(huán)語(yǔ)句以loop開(kāi)頭，以end loop結(jié)尾，這種循環(huán)至少會(huì)被執(zhí)行一次。 案例：現(xiàn)有一張表users，表結(jié)構(gòu)如下： 用戶id | 用戶名 | 請(qǐng)編寫(xiě)一個(gè)過(guò)程，可以輸入用戶名，并循環(huán)添加10個(gè)用戶到users表中，用戶編號(hào)從1開(kāi)始增加。
create or replace procedure sp_pro6(spName varchar2) is
--定義 :=表示賦值
v_num number:=1;
begin
loop
insert into users values(v_num,spName);
--判斷是否要退出循環(huán)
exit when v_num=10;
--自增
v_num:=v_num+1;
end loop;
end;
/ ? 環(huán)語(yǔ)句 –while循環(huán) 基本循環(huán)至少要執(zhí)行循環(huán)體一次，而對(duì)于while循環(huán)來(lái)說(shuō)，只有條件為true時(shí)，才會(huì)執(zhí)行循環(huán)體語(yǔ)句，while循環(huán)以while...loop開(kāi)始，以end loop結(jié)束。 案例：現(xiàn)有一張表users，表結(jié)構(gòu)如下： 用戶id 用戶名 問(wèn)題：請(qǐng)編寫(xiě)一個(gè)過(guò)程，可以輸入用戶名，并循環(huán)添加10個(gè)用戶到users表中，用戶編號(hào)從11開(kāi)始增加。
create or replace procedure sp_pro6(spName varchar2) is
--定義 :=表示賦值
v_num number:=11;
Oracle 筆記
47
begin
while v_num<=20 loop
--執(zhí)行
insert into users values(v_num,spName);
v_num:=v_num+1;
end loop;
end;
/ ? 循環(huán)語(yǔ)句 –for循環(huán) 基本for循環(huán)的基本結(jié)構(gòu)如下
begin
for i in reverse 1..10 loop
insert into users values (i, 'shunping');
end loop;
end;
我們可以看到控制變量i，在隱含中就在不停地增加。 ? 順序控制語(yǔ)句 –goto，null 1.goto語(yǔ)句 goto語(yǔ)句用于跳轉(zhuǎn)到特定符號(hào)去執(zhí)行語(yǔ)句。注意由于使用goto語(yǔ)句會(huì)增加程序的復(fù)雜性，并使得應(yīng)用程序可讀性變差，所以在做一般應(yīng)用開(kāi)發(fā)時(shí)，建議大家不要使用goto語(yǔ)句。 基本語(yǔ)法如下 goto lable，其中l(wèi)able是已經(jīng)定義好的標(biāo)號(hào)名，
declare
i int := 1;
begin
loop
dbms_output.put_line('輸出i=' || i);
if i = 1{} then
goto end_loop;
end if;
i := i + 1;
end loop;
<<end_loop>>
dbms_output.put_line('循環(huán)結(jié)束');
end; 2.null null語(yǔ)句不會(huì)執(zhí)行任何操作，并且會(huì)直接將控制傳遞到下一條語(yǔ)句。使用null語(yǔ)句的主要好處是可以提高pl/sql的可讀性。
declare
v_sal emp.sal%type;
v_ename emp.ename%type;
begin
select ename, sal into v_ename, v_sal from emp where empno = &no;
if v_sal < 3000 then
update emp set comm = sal * 0.1 where ename = v_ename;
else
null;
Oracle 筆記
48
end if;
end;
20.PL/SQL分頁(yè) 編寫(xiě)分頁(yè)過(guò)程 ? 介紹 分頁(yè)是任何一個(gè)網(wǎng)站(bbs,網(wǎng)上商城，blog)都會(huì)使用到的技術(shù)，因此學(xué)習(xí)pl/sql編程開(kāi)發(fā)就一定要掌握該技術(shù)�？磮D： ? 無(wú)返回值的存儲(chǔ)過(guò)程 古人云：欲速則不達(dá)，為了讓大家伙比較容易接受分頁(yè)過(guò)程編寫(xiě)，我還是從簡(jiǎn)單到復(fù)雜，循序漸進(jìn)的給大家講解。首先是掌握最簡(jiǎn)單的存儲(chǔ)過(guò)程，無(wú)返回值的存儲(chǔ)過(guò)程： 案例：現(xiàn)有一張表book，表結(jié)構(gòu)如下：看圖： 書(shū)號(hào) 書(shū)名 出版社 請(qǐng)寫(xiě)一個(gè)過(guò)程，可以向book表添加書(shū)，要求通過(guò)java程序調(diào)用該過(guò)程。 --in:表示這是一個(gè)輸入?yún)?shù)，默認(rèn)為in --out:表示一個(gè)輸出參數(shù)
create or replace procedure sp_pro7(spBookId in number,spbookName in varchar2,sppublishHouse in varchar2) is
begin
insert into book values(spBookId,spbookName,sppublishHouse);
end;
/ --在java中調(diào)用
//調(diào)用一個(gè)無(wú)返回值的過(guò)程
import java.sql.*;
public class Test2{
public static void main(String[] args){
try{
//1.加載驅(qū)動(dòng)
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.得到連接
Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");
//3.創(chuàng)建CallableStatement
CallableStatement cs = ct.prepareCall("{call sp_pro7(?,?,?)}");
//4.給?賦值
cs.setInt(1,10);
cs.setString(2,"笑傲江湖");
cs.setString(3,"人民出版社");
//5.執(zhí)行
cs.execute();
} catch(Exception e){
e.printStackTrace();
} finally{
//6.關(guān)閉各個(gè)打開(kāi)的資源
cs.close
//Oracle 筆記
ct.close();
}
}
}
執(zhí)行，記錄被加進(jìn)去了 ? 有返回值的存儲(chǔ)過(guò)程(非列表) 再看如何處理有返回值的存儲(chǔ)過(guò)程： 案例：編寫(xiě)一個(gè)過(guò)程，可以輸入雇員的編號(hào)，返回該雇員的姓名。 案例擴(kuò)張：編寫(xiě)一個(gè)過(guò)程，可以輸入雇員的編號(hào)，返回該雇員的姓名、工資和崗位。
--有輸入和輸出的存儲(chǔ)過(guò)程
create or replace procedure sp_pro8
(spno in number, spName out varchar2) is
begin
select ename into spName from emp where empno=spno;
end;
/
import java.sql.*;
public class Test2{
public static void main(String[] args){
try{
//1.加載驅(qū)動(dòng)
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.得到連接
Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");
//3.創(chuàng)建CallableStatement
/*CallableStatement cs = ct.prepareCall("{call sp_pro7(?,?,?)}");
//4.給?賦值
cs.setInt(1,10);
cs.setString(2,"笑傲江湖");
cs.setString(3,"人民出版社");*/
//看看如何調(diào)用有返回值的過(guò)程
//創(chuàng)建CallableStatement
/*CallableStatement cs = ct.prepareCall("{call sp_pro8(?,?)}");
//給第一個(gè)?賦值
cs.setInt(1,7788);
//給第二個(gè)?賦值
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
//5.執(zhí)行
cs.execute();
//取出返回值,要注意?的順序
String name=cs.getString(2);
System.out.println("7788的名字"+name);
} catch(Exception e){
e.printStackTrace();
Oracle 筆記
50
} finally{
//6.關(guān)閉各個(gè)打開(kāi)的資源
cs.close();
ct.close();
}
}
}
運(yùn)行，成功得出結(jié)果。。 案例擴(kuò)張：編寫(xiě)一個(gè)過(guò)程，可以輸入雇員的編號(hào)，返回該雇員的姓名、工資和崗位。
--有輸入和輸出的存儲(chǔ)過(guò)程
create or replace procedure sp_pro8
(spno in number, spName out varchar2,spSal out number,spJob out varchar2) is
begin
select ename,sal,job into spName,spSal,spJob from emp where empno=spno;
end;
/
import java.sql.*;
public class Test2{
public static void main(String[] args){
try{
//1.加載驅(qū)動(dòng)
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.得到連接
Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");
//3.創(chuàng)建CallableStatement
/*CallableStatement cs = ct.prepareCall("{call sp_pro7(?,?,?)}");
//4.給?賦值
cs.setInt(1,10);
cs.setString(2,"笑傲江湖");
cs.setString(3,"人民出版社");*/
//看看如何調(diào)用有返回值的過(guò)程
//創(chuàng)建CallableStatement
/*CallableStatement cs = ct.prepareCall("{call sp_pro8(?,?,?,?)}");
//給第一個(gè)?賦值
cs.setInt(1,7788);
//給第二個(gè)?賦值
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
//給第三個(gè)?賦值
cs.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE);
//給第四個(gè)?賦值
cs.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR);
//5.執(zhí)行
cs.execute();
//取出返回值,要注意?的順序
Oracle 筆記
51
String name=cs.getString(2);
String job=cs.getString(4);
System.out.println("7788的名字"+name+" 工作："+job);
} catch(Exception e){
e.printStackTrace();
} finally{
//6.關(guān)閉各個(gè)打開(kāi)的資源
cs.close();
ct.close();
}
}
}
運(yùn)行，成功找出記錄 ? 有返回值的存儲(chǔ)過(guò)程(列表[結(jié)果集]) 案例：編寫(xiě)一個(gè)過(guò)程，輸入部門(mén)號(hào)，返回該部門(mén)所有雇員信息。 對(duì)該題分析如下： 由于oracle存儲(chǔ)過(guò)程沒(méi)有返回值，它的所有返回值都是通過(guò)out參數(shù)來(lái)替代的，列表同樣也不例外，但由于是集合，所以不能用一般的參數(shù)，必須要用pagkage了。所以要分兩部分： 返回結(jié)果集的過(guò)程 1.建立一個(gè)包，在該包中，我定義類型test_cursor,是個(gè)游標(biāo)。 如下：
create or replace package testpackage as
TYPE test_cursor is ref cursor;
end testpackage; 2.建立存儲(chǔ)過(guò)程。如下：
create or replace procedure sp_pro9(spNo in number,p_cursor out testpackage.test_cursor) is
begin
open p_cursor for
select * from emp where deptno = spNo;
end sp_pro9; 3.如何在java程序中調(diào)用該過(guò)程
import java.sql.*;
public class Test2{
public static void main(String[] args){
try{
//1.加載驅(qū)動(dòng)
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.得到連接
Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");
//看看如何調(diào)用有返回值的過(guò)程
//3.創(chuàng)建CallableStatement
/*CallableStatement cs = ct.prepareCall("{call sp_pro9(?,?)}");
//4.給第?賦值
cs.setInt(1,10);
//給第二個(gè)?賦值
Oracle 筆記
52
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
//5.執(zhí)行
cs.execute();
//得到結(jié)果集
ResultSet rs=(ResultSet)cs.getObject(2);
while(rs.next()){
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}
} catch(Exception e){
e.printStackTrace();
} finally{
//6.關(guān)閉各個(gè)打開(kāi)的資源
cs.close();
ct.close();
}
}
}
運(yùn)行，成功得出部門(mén)號(hào)是10的所有用戶 ? 編寫(xiě)分頁(yè)過(guò)程 有了上面的基礎(chǔ)，相信大家可以完成分頁(yè)存儲(chǔ)過(guò)程了。 要求，請(qǐng)大家編寫(xiě)一個(gè)存儲(chǔ)過(guò)程，要求可以輸入表名、每頁(yè)顯示記錄數(shù)、當(dāng)前頁(yè)。返回總記錄數(shù)，總頁(yè)數(shù)，和返回的結(jié)果集。 如果大家忘了oracle中如何分頁(yè)，請(qǐng)參考第三天的內(nèi)容。 先自己完成，老師在后面給出答案，并講解。 --oracle的分頁(yè)
select t1.*, rownum rn from (select * from emp) t1 where rownum<=10;
--在分頁(yè)時(shí)，大家可以把下面的sql語(yǔ)句當(dāng)做一個(gè)模板使用
select * from
(select t1.*, rownum rn from (select * from emp) t1 where rownum<=10)
where rn>=6; --開(kāi)發(fā)一個(gè)包 --建立一個(gè)包，在該包中，我定義類型test_cursor,是個(gè)游標(biāo)。 如下：
create or replace package testpackage as
TYPE test_cursor is ref cursor;
end testpackage;
--開(kāi)始編寫(xiě)分頁(yè)的過(guò)程
create or replace procedure fenye
(tableName in varchar2,
Pagesize in number,--一頁(yè)顯示記錄數(shù)
pageNow in number,
myrows out number,--總記錄數(shù)
myPageCount out number,--總頁(yè)數(shù)
p_cursor out testpackage.test_cursor--返回的記錄集
) is
--定義部分
--定義sql語(yǔ)句 字符串
Oracle 筆記
53
v_sql varchar2(1000);
--定義兩個(gè)整數(shù)
v_begin number:=(pageNow-1)*Pagesize+1;
v_end number:=pageNow*Pagesize;
begin
--執(zhí)行部分
v_sql:='select * from (select t1.*, rownum rn from (select * from '||tableName||') t1 where rownum<='||v_end||') where rn>='||v_begin;
--把游標(biāo)和sql關(guān)聯(lián)
open p_cursor for v_sql;
--計(jì)算myrows和myPageCount
--組織一個(gè)sql語(yǔ)句
v_sql:='select count(*) from '||tableName;
--執(zhí)行sql,并把返回的值，賦給myrows;
execute inmediate v_sql into myrows;
--計(jì)算myPageCount
--if myrows%Pagesize=0 then這樣寫(xiě)是錯(cuò)的
if mod(myrows,Pagesize)=0 then
myPageCount:=myrows/Pagesize;
else
myPageCount:=myrows/Pagesize+1
end if;
--關(guān)閉游標(biāo)
close p_cursor;
end;
/ --使用java測(cè)試 //測(cè)試分頁(yè)
import java.sql.*;
public class FenYe{
public static void main(String[] args){
try{
//1.加載驅(qū)動(dòng)
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.得到連接
Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");
//3.創(chuàng)建CallableStatement
CallableStatement cs = ct.prepareCall("{call fenye(?,?,?,?,?,?)}");
//4.給第?賦值
cs.seString(1,"emp");
cs.setInt(2,5);
cs.setInt(3,2);
//注冊(cè)總記錄數(shù)
Oracle 筆記
54
cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);
//注冊(cè)總頁(yè)數(shù)
cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);
//注冊(cè)返回的結(jié)果集
cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);
//5.執(zhí)行
cs.execute();
//取出總記錄數(shù) /這里要注意，getInt(4)中4，是由該參數(shù)的位置決定的
int rowNum=cs.getInt(4);
int pageCount = cs.getInt(5);
ResultSet rs=(ResultSet)cs.getObject(6);
//顯示一下，看看對(duì)不對(duì)
System.out.println("rowNum="+rowNum);
System.out.println("總頁(yè)數(shù)="+pageCount);
while(rs.next()){
System.out.println("編號(hào)："+rs.getInt(1)+" 名字："+rs.getString(2)+" 工資："+rs.getFloat(6));
}
} catch(Exception e){
e.printStackTrace();
} finally{
//6.關(guān)閉各個(gè)打開(kāi)的資源
cs.close();
ct.close();
}
}
}
運(yùn)行，控制臺(tái)輸出： rowNum=19 總頁(yè)數(shù)：4 編號(hào)：7369 名字：SMITH 工資：2850.0 編號(hào)：7499 名字：ALLEN 工資：2450.0 編號(hào)：7521 名字：WARD 工資：1562.0 編號(hào)：7566 名字：JONES 工資：7200.0 編號(hào)：7654 名字：MARTIN 工資：1500.0 --新的需要，要求按照薪水從低到高排序，然后取出6-10 過(guò)程的執(zhí)行部分做下改動(dòng)，如下：
begin
--執(zhí)行部分
v_sql:='select * from (select t1.*, rownum rn from (select * from '||tableName||' order by sal) t1 where rownum<='||v_end||') where rn>='||v_begin; 重新執(zhí)行一次procedure,java不用改變，運(yùn)行，控制臺(tái)輸出： rowNum=19 總頁(yè)數(shù)：4 編號(hào)：7900 名字：JAMES 工資：950.0 編號(hào)：7876 名字：ADAMS 工資：1100.0
Oracle 筆記
55
編號(hào)：7521 名字：WARD 工資：1250.0 編號(hào)：7654 名字：MARTIN 工資：1250.0 編號(hào)：7934 名字：MILLER 工資：1300.0
21.例外處理 例外處理 ? 例外的分類 oracle將例外分為預(yù)定義例外，非預(yù)定義例外和自定義例外三種。 預(yù)定義例外用于處理常見(jiàn)的oracle錯(cuò)誤 非預(yù)定義例外用于處理預(yù)定義例外不能處理的例外 自定義例外用于處理與oracle錯(cuò)誤無(wú)關(guān)的其它情況 ? 例外傳遞 如果不處理例外我們看看會(huì)出現(xiàn)什么情況： 案例，編寫(xiě)一個(gè)過(guò)程，可接收雇員的編號(hào)，并顯示該雇員的姓名。 問(wèn)題是，如果輸入的雇員編號(hào)不存在，怎樣去處理呢?
--例外案例
declare
--定義
v_ename emp.ename%type;
begin
--
select ename into v_ename from emp where empno=&gno;
dbms_output.put_line('名字：'||v_ename)
/ 執(zhí)行，彈出框，看圖： 隨便輸個(gè)不在的編號(hào)，回車，會(huì)拋出異常，顯示： ORA-01403: 未找到數(shù)據(jù) ORA-06512: 在line 6
declare
--定義
v_ename emp.ename%type;
begin
--
select ename into v_ename from emp where empno=&gno;
dbms_output.put_line('名字：'||v_ename)
exception
when no_data_found then
dbms_output.put_line('編號(hào)沒(méi)有!');
/
執(zhí)行，輸入一個(gè)不存在的編號(hào)，回車，顯示： 編號(hào)沒(méi)有! ? 處理預(yù)定義例外 預(yù)定義例外是由pl/sql所提供的系統(tǒng)例外。當(dāng)pl/sql應(yīng)用程序違反了oracle 規(guī)定的限制時(shí)，則會(huì)隱含的觸發(fā)一個(gè)內(nèi)部例外。pl/sql為開(kāi)發(fā)人員提供了二十多個(gè)預(yù)定義例外。我們給大家介紹常用的例外。
Oracle 筆記
56
? 預(yù)定義例外 case_not_found 在開(kāi)發(fā)pl/sql塊中編寫(xiě)case語(yǔ)句時(shí)，如果在when子句中沒(méi)有包含必須的條件分支，就會(huì)觸發(fā)case_not_found的例外：
create or replace procedure sp_pro6(spno number) is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno = spno;
case
when v_sal < 1000 then
update emp set sal = sal + 100 where empno = spno;
when v_sal < 2000 then
update emp set sal = sal + 200 where empno = spno;
end case;
exception
when case_not_found then
dbms_output.put_line('case語(yǔ)句沒(méi)有與' || v_sal || '相匹配的條件');
end; ? 預(yù)定義例外 cursor_already_open 當(dāng)重新打開(kāi)已經(jīng)打開(kāi)的游標(biāo)時(shí)，會(huì)隱含的觸發(fā)例外cursor_already_open
declare
cursor emp_cursor is select ename, sal from emp;
begin
open emp_cursor;
for emp_record1 in emp_cursor loop
dbms_output.put_line(emp_record1.ename);
end loop;
exception
when cursor_already_open then
dbms_output.put_line('游標(biāo)已經(jīng)打開(kāi)');
end;
/ ? 預(yù)定義例外 dup_val_on_index 在唯一索引所對(duì)應(yīng)的列上插入重復(fù)的值時(shí)，會(huì)隱含的觸發(fā)例外dup_val_on_index例外
begin
insert into dept values (10, '公關(guān)部', '北京');
exception
when dup_val_on_index then
dbms_output.put_line('在deptno列上不能出現(xiàn)重復(fù)值');
end; ? 預(yù)定義例外 invalid_cursor 當(dāng)試圖在不合法的游標(biāo)上執(zhí)行操作時(shí)，會(huì)觸發(fā)該例外 例如：試圖從沒(méi)有打開(kāi)的游標(biāo)提取數(shù)據(jù)，或是關(guān)閉沒(méi)有打開(kāi)的游標(biāo)。則會(huì)觸發(fā)該例外
declare
cursor emp_cursor is select ename, sal from emp;
emp_record emp_cursor%rowtype;
begin
Oracle 筆記
57
--open emp_cursor; --打開(kāi)游標(biāo)
fetch emp_cursor into emp_record;
dbms_output.put_line(emp_record.ename);
close emp_cursor;
exception
when invalid_cursor then
dbms_output.put_line('請(qǐng)檢測(cè)游標(biāo)是否打開(kāi)');
end; ? 預(yù)定義例外 invalid_number 當(dāng)輸入的數(shù)據(jù)有誤時(shí)，會(huì)觸發(fā)該例外 比如：數(shù)字100寫(xiě)成了loo就會(huì)觸發(fā)該例外
begin
update emp set sal= sal + 'loo';
exception
when invalid_number then
dbms_output.put_line('輸入的數(shù)字不正確');
end; 預(yù)定義例外 no_data_found 下面是一個(gè)pl/sql塊，當(dāng)執(zhí)行select into 沒(méi)有返回行，就會(huì)觸發(fā)該例外
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp
when ename='&name';
exception
when no_data_found then
dbms_output.put_line('不存在該員工');
end; ? 預(yù)定義例外 too_many_rows 當(dāng)執(zhí)行select into語(yǔ)句時(shí)，如果返回超過(guò)了一行，則會(huì)觸發(fā)該例外。
declare
v_ename emp.ename%type;
begin
select ename into v_ename from emp;
exception
when too_many_rows then
dbms_output.put_line('返回了多行');
end; ? 預(yù)義例外 zero_divide 當(dāng)執(zhí)行2/0語(yǔ)句時(shí)，則會(huì)觸發(fā)該例外。 ? 預(yù)定義例外 value_error 當(dāng)在執(zhí)行賦值操作時(shí)，如果變量的長(zhǎng)度不足以容納實(shí)際數(shù)據(jù)，則會(huì)觸發(fā)該例外value_error，比如：
declare
v_ename varchar2(5);
begin
Oracle 筆記
58
select ename into v_ename from emp where empno = &no1;
dbms_output.put_line(v_ename);
exception
when value_error then
dbms_output.put_line('變量尺寸不足');
end; ? 其它預(yù)定義例外(這些例外不是在pl/sql里觸發(fā)的，而是在用oracle時(shí)觸發(fā)的，所以取名叫其它預(yù)定義例外) 1.login_denied 當(dāng)用戶非法登錄時(shí)，會(huì)觸發(fā)該例外 2.not_logged_on 如果用戶沒(méi)有登錄就執(zhí)行dml操作，就會(huì)觸發(fā)該例外 3.storage_error 如果超過(guò)了內(nèi)存空間或是內(nèi)存被損壞，就觸發(fā)該例外 4.timeout_on_resource 如果oracle在等待資源時(shí)，出現(xiàn)了超時(shí)就觸發(fā)該例外 ? 非預(yù)定義例外 非預(yù)定義例外用于處理與預(yù)定義例外無(wú)關(guān)的oracle錯(cuò)誤。使用預(yù)定義例外只能處理21個(gè)oracle錯(cuò)誤，而當(dāng)使用pl/sql開(kāi)發(fā)應(yīng)用程序時(shí)，可能會(huì)遇到其它的一些oracle錯(cuò)誤。比如在pl/sql塊中執(zhí)行dml語(yǔ)句時(shí)，違反了約束規(guī)定等等。在這樣的情況下，也可以處理oracle的各種例外，因?yàn)榉穷A(yù)定義例外用的不多，這里我就不舉例了。 ? 處理自定義例外 預(yù)定義例外和自定義例外都是與oracle錯(cuò)誤相關(guān)的，并且出現(xiàn)的oracle錯(cuò)誤會(huì)隱含的觸發(fā)相應(yīng)的例外;而自定義例外與oracle錯(cuò)誤沒(méi)有任何關(guān)聯(lián)，它是由開(kāi)發(fā)人員為特定情況所定義的例外. 問(wèn)題：請(qǐng)編寫(xiě)一個(gè)pl/sql塊，接收一個(gè)雇員的編號(hào)，并給該雇員工資增加1000元，如果該雇員不存在，請(qǐng)?zhí)崾尽?/p>
--自定義例外
create or replace procedure ex_test(spNo number)
is
begin
--更新用戶sal
update emp set sal=sal+1000 where empno=spNo;
end;
/ 運(yùn)行，該過(guò)程被成功創(chuàng)建。 SQL> exec ex_test(56); PL/SQL過(guò)程被成功完成 這里，編號(hào)為56是不存在的，剛才的報(bào)異常了，為什么現(xiàn)在不報(bào)異常呢? 因?yàn)閯偛诺氖莝elect語(yǔ)句 怎么解決這個(gè)問(wèn)題呢? 修改代碼，如下：
--自定義例外
create or replace procedure ex_test(spNo number)
is
--定義一個(gè)例外
myex exception;
begin
--更新用戶sal
update emp set sal=sal+1000 where empno=spNo;
Oracle 筆記
59
--sql%notfound這是表示沒(méi)有update
--raise myex;觸發(fā)myex
if sql%notfound then
raise myex;
end if;
exception
when myex then
dbms_output.put_line('沒(méi)有更新任何用戶');
end;
/ 現(xiàn)在再測(cè)試一次： SQL> exec ex_test(56); 沒(méi)有更新任何用戶
22.oracle的視圖 oracle的視圖 ? 介紹 視圖是一個(gè)虛擬表，其內(nèi)容由查詢定義，同真實(shí)的表一樣，視圖包含一系列帶有名稱的列和行數(shù)據(jù)。但是，視圖并不在數(shù)據(jù)庫(kù)中以存儲(chǔ)的數(shù)據(jù)值集形式存在。行和列數(shù)據(jù)來(lái)自由定義視圖的查詢所引用的表，并且在引用視圖時(shí)動(dòng)態(tài)生成。(視圖不是真實(shí)存在磁盤(pán)上的) 看圖： 視與表的區(qū)別 ? 視圖與表的區(qū)別 1.表需要占用磁盤(pán)空間，視圖不需要 2.視圖不能添加索引(所以查詢速度略微慢點(diǎn)) 3.使用視圖可以簡(jiǎn)化，復(fù)雜查詢 比如：學(xué)生選課系統(tǒng) 4.視圖的使用利于提高安全性 比如：不同用戶查看不同視圖 創(chuàng)建/修改視圖 ? 創(chuàng)建視圖 create view 視圖名 as select 語(yǔ)句 [with read only] ? 創(chuàng)建或修改視圖 create or replace view 視圖名 as select 語(yǔ)句 [with read only] ? 刪除視圖 drop view 視圖名 當(dāng)表結(jié)構(gòu)國(guó)語(yǔ)復(fù)雜，請(qǐng)使用視圖吧! --創(chuàng)建視圖，把emp表的sal<1000的雇員映射到該視圖(view)
create view myview as select * from emp where sal<1000; --為簡(jiǎn)化操作，用一個(gè)視圖解決 顯示雇員編號(hào)，姓名和部門(mén)名稱
create view myview2 as select emp.empno,emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno; 視圖之間也可以做聯(lián)合查詢
一、基礎(chǔ)
1、說(shuō)明：創(chuàng)建數(shù)據(jù)庫(kù)
CREATE DATABASE database-name
2、說(shuō)明：刪除數(shù)據(jù)庫(kù)
drop database dbname
3、說(shuō)明：備份sql server
--- 創(chuàng)建 備份數(shù)據(jù)的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- 開(kāi)始 備份
BACKUP DATABASE pubs TO testBack
4、說(shuō)明：創(chuàng)建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根據(jù)已有的表創(chuàng)建新表：
A：create table tab_new like tab_old (使用舊表創(chuàng)建新表)
B：create table tab_new as select col1,col2… from tab_old definition only
5、說(shuō)明：刪除新表
drop table tabname
6、說(shuō)明：增加一個(gè)列
Alter table tabname add column col type
注：列增加后將不能刪除。DB2中列加上后數(shù)據(jù)類型也不能改變，唯一能改變的是增加varchar類型的長(zhǎng)度。
7、說(shuō)明：添加主鍵： Alter table tabname add primary key(col)
說(shuō)明：刪除主鍵： Alter table tabname drop primary key(col)
8、說(shuō)明：創(chuàng)建索引：create [unique] index idxname on tabname(col….)
刪除索引：drop index idxname
注：索引是不可更改的，想更改必須刪除重新建。
9、說(shuō)明：創(chuàng)建視圖：create view viewname as select statement
刪除視圖：drop view viewname
10、說(shuō)明：幾個(gè)簡(jiǎn)單的基本的sql語(yǔ)句
選擇：select * from table1 where 范圍
插入：insert into table1(field1,field2) values(value1,value2)
刪除：delete from table1 where 范圍
更新：update table1 set field1=value1 where 范圍
查找：select * from table1 where field1 like ’%value1%’ ---like的語(yǔ)法很精妙，查資料!
排序：select * from table1 order by field1,field2 [desc]
總數(shù)：select count as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最�。簊elect min(field1) as minvalue from table1
11、說(shuō)明：幾個(gè)高級(jí)查詢運(yùn)算詞
A： UNION 運(yùn)算符
UNION 運(yùn)算符通過(guò)組合其他兩個(gè)結(jié)果表(例如 TABLE1 和 TABLE2)并消去表中任何重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng) ALL 隨 UNION 一起使用時(shí)(即 UNION ALL)，不消除重復(fù)行。兩種情況下，派生表的每一行不是來(lái)自 TABLE1 就是來(lái)自 TABLE2。
B： EXCEPT 運(yùn)算符
EXCEPT 運(yùn)算符通過(guò)包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng) ALL 隨 EXCEPT 一起使用時(shí) (EXCEPT ALL)，不消除重復(fù)行。
C： INTERSECT 運(yùn)算符
INTERSECT 運(yùn)算符通過(guò)只包括 TABLE1 和 TABLE2 中都有的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng) ALL 隨 INTERSECT 一起使用時(shí) (INTERSECT ALL)，不消除重復(fù)行。
注：使用運(yùn)算詞的幾個(gè)查詢結(jié)果行必須是一致的。
12、說(shuō)明：使用外連接
A、left (outer) join：
左外連接(左連接)：結(jié)果集幾包括連接表的匹配行，也包括左連接表的所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B：right (outer) join:
右外連接(右連接)：結(jié)果集既包括連接表的匹配連接行，也包括右連接表的所有行。
C：full/cross (outer) join：
全外連接：不僅包括符號(hào)連接表的匹配行，還包括兩個(gè)連接表中的所有記錄。
12、分組:Group by:
一張表，一旦分組 完成后，查詢后只能得到組相關(guān)的信息。
組相關(guān)的信息：(統(tǒng)計(jì)信息) count,sum,max,min,avg 分組的標(biāo)準(zhǔn))
在SQLServer中分組時(shí)：不能以text,ntext,image類型的字段作為分組依據(jù)
在selecte統(tǒng)計(jì)函數(shù)中的字段，不能和普通的字段放在一起;
13、對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作：
分離數(shù)據(jù)庫(kù)： sp_detach_db; 附加數(shù)據(jù)庫(kù)：sp_attach_db 后接表明，附加需要完整的路徑名
14.如何修改數(shù)據(jù)庫(kù)的名稱:
sp_renamedb 'old_name', 'new_name'
二、提升
1、說(shuō)明：復(fù)制表(只復(fù)制結(jié)構(gòu),源表名：a 新表名：b) (Access可用)
法一：select * into b from a where 1<>1(僅用于SQlServer)
法二：select top 0 * into b from a
2、說(shuō)明：拷貝表(拷貝數(shù)據(jù),源表名：a 目標(biāo)表名：b) (Access可用)
insert into b(a, b, c) select d,e,f from b;
3、說(shuō)明：跨數(shù)據(jù)庫(kù)之間表的拷貝(具體數(shù)據(jù)使用絕對(duì)路徑) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具體數(shù)據(jù)庫(kù)’ where 條件
例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..
4、說(shuō)明：子查詢(表名1：a 表名2：b)
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)
5、說(shuō)明：顯示文章、提交人和最后回復(fù)時(shí)間
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
6、說(shuō)明：外連接查詢(表名1：a 表名2：b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
7、說(shuō)明：在線視圖查詢(表名1：a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;
8、說(shuō)明：between的用法,between限制查詢數(shù)據(jù)范圍時(shí)包括了邊界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 數(shù)值1 and 數(shù)值2
9、說(shuō)明：in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
10、說(shuō)明：兩張關(guān)聯(lián)表，刪除主表中已經(jīng)在副表中沒(méi)有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
11、說(shuō)明：四表聯(lián)查問(wèn)題：
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
12、說(shuō)明：日程安排提前五分鐘提醒
SQL: select * from 日程安排 where datediff('minute',f開(kāi)始時(shí)間,getdate())>5
13、說(shuō)明：一條sql 語(yǔ)句搞定數(shù)據(jù)庫(kù)分頁(yè)
select top 10 b.* from (select top 20 主鍵字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主鍵字段 = a.主鍵字段 order by a.排序字段
具體實(shí)現(xiàn)：
關(guān)于數(shù)據(jù)庫(kù)分頁(yè)：
declare @start int,@end int
@sql nvarchar(600)
set @sql=’select top’+str(@end-@start+1)+’+from T where rid not in(select top’+str(@str-1)+’Rid from T where Rid>-1)’
exec sp_executesql @sql
注意：在top后不能直接跟一個(gè)變量，所以在實(shí)際應(yīng)用中只有這樣的進(jìn)行特殊的處理。Rid為一個(gè)標(biāo)識(shí)列，如果top后還有具體的字段，這樣做是非常有好處的。因?yàn)檫@樣可以避免 top的字段如果是邏輯索引的，查詢的結(jié)果后實(shí)際表中的不一致(邏輯索引中的數(shù)據(jù)有可能和數(shù)據(jù)表中的不一致，而查詢時(shí)如果處在索引則首先查詢索引)
14、說(shuō)明：前10條記錄
select top 10 * form table1 where 范圍
15、說(shuō)明：選擇在每一組b值相同的數(shù)據(jù)中對(duì)應(yīng)的a最大的記錄的所有信息(類似這樣的用法可以用于論壇每月排行榜,每月熱銷產(chǎn)品分析,按科目成績(jī)排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
16、說(shuō)明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)
17、說(shuō)明：隨機(jī)取出10條數(shù)據(jù)
select top 10 * from tablename order by newid()
18、說(shuō)明：隨機(jī)選擇記錄
select newid()
19、說(shuō)明：刪除重復(fù)記錄
1),delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
2),select distinct * into temp from tablename
delete from tablename
insert into tablename select * from temp
評(píng)價(jià)： 這種操作牽連大量的數(shù)據(jù)的移動(dòng)，這種做法不適合大容量但數(shù)據(jù)操作
3),例如：在一個(gè)外部表中導(dǎo)入數(shù)據(jù)，由于某些原因第一次只導(dǎo)入了一部分，但很難判斷具體位置，這樣只有在下一次全部導(dǎo)入，這樣也就產(chǎn)生好多重復(fù)的字段，怎樣刪除重復(fù)字段
alter table tablename
--添加一個(gè)自增列
add column_b int identity(1,1)
delete from tablename where column_b not in(
select max(column_b) from tablename group by column1,column2,...)
alter table tablename drop column column_b
20、說(shuō)明：列出數(shù)據(jù)庫(kù)里所有的表名
select name from sysobjects where type='U' // U代表用戶
21、說(shuō)明：列出表里的所有的列名
select name from syscolumns where id=object_id('TableName')
22、說(shuō)明：列示type、vender、pcs字段，以type字段排列，case可以方便地實(shí)現(xiàn)多重選擇，類似select 中的case。
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type
顯示結(jié)果：
type vender pcs
電腦 A 1
電腦 A 1
光盤(pán) B 2
光盤(pán) A 2
手機(jī) B 3
手機(jī) C 3
23、說(shuō)明：初始化表table1
TRUNCATE TABLE table1
24、說(shuō)明：選擇從10到15的記錄
select top 5 * from (select top 15 * from table order by id asc) table_別名 order by id desc
三、技巧
1、1=1，1=2的使用，在SQL語(yǔ)句組合時(shí)用的較多
“where 1=1” 是表示選擇全部 “where 1=2”全部不選，
如：
if @strWhere !=''
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere
end
else
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end
我們可以直接寫(xiě)成
錯(cuò)誤!未找到目錄項(xiàng)。
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere 2、收縮數(shù)據(jù)庫(kù)
--重建索引
DBCC REINDEX
DBCC INDEXDEFRAG
--收縮數(shù)據(jù)和日志
DBCC SHRINKDB
DBCC SHRINKFILE
3、壓縮數(shù)據(jù)庫(kù)
dbcc shrinkdatabase(dbname)
4、轉(zhuǎn)移數(shù)據(jù)庫(kù)給新用戶以已存在用戶權(quán)限
exec sp_change_users_login 'update_one','newname','oldname'
go
5、檢查備份集
RESTORE VERIFYONLY from disk='E:\dvbbs.bak'
6、修復(fù)數(shù)據(jù)庫(kù)
ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GO
7、日志清除
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
USE tablename -- 要操作的數(shù)據(jù)庫(kù)名
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1 -- 你想設(shè)定的日志文件的大小(M)
Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF
8、說(shuō)明：更改某個(gè)表
exec sp_changeobjectowner 'tablename','dbo'
9、存儲(chǔ)更改全部表
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS
DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)
DECLARE curObject CURSOR FOR
select 'Name' = name,
'Owner' = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name
OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN
if @Owner=@OldOwner
begin
set @OwnerName = @OldOwner + '.' + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner
FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
GO
10、SQL SERVER中直接循環(huán)寫(xiě)入數(shù)據(jù)
declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i+1
end
案例：
有如下表，要求就裱中所有沒(méi)有及格的成績(jī)，在每次增長(zhǎng)0.1的基礎(chǔ)上，使他們剛好及格:
Name score
Zhangshan 80
Lishi 59
Wangwu 50
Songquan 69
while((select min(score) from tb_table)<60)
begin
update tb_table set score =score*1.01
where score<60
if (select min(score) from tb_table)>60
break
else
continue
end
數(shù)據(jù)開(kāi)發(fā)-經(jīng)典
1.按姓氏筆畫(huà)排序:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //從少到多
2.數(shù)據(jù)庫(kù)加密:
select encrypt('原始密碼')
select pwdencrypt('原始密碼')
select pwdcompare('原始密碼','加密后密碼') = 1--相同;否則不相同 encrypt('原始密碼')
select pwdencrypt('原始密碼')
select pwdcompare('原始密碼','加密后密碼') = 1--相同;否則不相同
3.取回表中字段:
declare @list varchar(1000),
@sql nvarchar(1000)
select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A'
set @sql='select '+right(@list,len(@list)-1)+' from 表A'
exec (@sql)
4.查看硬盤(pán)分區(qū):
EXEC master..xp_fixeddrives
5.比較A,B表是否相等:
if (select checksum_agg(binary_checksum(*)) from A)
=
(select checksum_agg(binary_checksum(*)) from B)
print '相等'
else
print '不相等'
6.殺掉所有的事件探察器進(jìn)程:
DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocesses
WHERE program_name IN('SQL profiler',N'SQL 事件探查器')
EXEC sp_msforeach_worker '?'
7.記錄搜索:
開(kāi)頭到N條記錄
Select Top N * From 表
-------------------------------
N到M條記錄(要有主索引ID)
Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc
----------------------------------
N到結(jié)尾記錄
Select Top N * From 表 Order by ID Desc
案例
例如1：一張表有一萬(wàn)多條記錄，表的第一個(gè)字段 RecID 是自增長(zhǎng)字段， 寫(xiě)一個(gè)SQL語(yǔ)句， 找出表的第31到第40個(gè)記錄。
select top 10 recid from A where recid not in(select top 30 recid from A)
分析：如果這樣寫(xiě)會(huì)產(chǎn)生某些問(wèn)題，如果recid在表中存在邏輯索引。
select top 10 recid from A where……是從索引中查找，而后面的select top 30 recid from A則在數(shù)據(jù)表中查找，這樣由于索引中的順序有可能和數(shù)據(jù)表中的不一致，這樣就導(dǎo)致查詢到的不是本來(lái)的欲得到的數(shù)據(jù)。
解決方案
1， 用order by select top 30 recid from A order by ricid 如果該字段不是自增長(zhǎng)，就會(huì)出現(xiàn)問(wèn)題
2， 在那個(gè)子查詢中也加條件：select top 30 recid from A where recid>-1
例2：查詢表中的最后以條記錄，并不知道這個(gè)表共有多少數(shù)據(jù),以及表結(jié)構(gòu)。
set @s = 'select top 1 * from T where pid not in (select top ' + str(@count-1) + ' pid from T)'
print @s exec sp_executesql @s
9：獲取當(dāng)前數(shù)據(jù)庫(kù)中的所有用戶表
select Name from sysobjects where xtype='u' and status>=0
10：獲取某一個(gè)表的所有字段
select name from syscolumns where id=object_id('表名')
select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = '表名')
兩種方式的效果相同
11：查看與某一個(gè)表相關(guān)的視圖、存儲(chǔ)過(guò)程、函數(shù)
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'
12：查看當(dāng)前數(shù)據(jù)庫(kù)中所有存儲(chǔ)過(guò)程
select name as 存儲(chǔ)過(guò)程名稱 from sysobjects where xtype='P'
13：查詢用戶創(chuàng)建的所有數(shù)據(jù)庫(kù)
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01
14：查詢某一個(gè)表的字段和數(shù)據(jù)類型
select column_name,data_type from information_schema.columns
where table_name = '表名'
15：不同服務(wù)器數(shù)據(jù)庫(kù)之間的數(shù)據(jù)操作
--創(chuàng)建鏈接服務(wù)器
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '遠(yuǎn)程服務(wù)器名或ip地址 '
exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用戶名 ', '密碼 '
--查詢示例
select * from ITSV.數(shù)據(jù)庫(kù)名.dbo.表名
--導(dǎo)入示例
select * into 表 from ITSV.數(shù)據(jù)庫(kù)名.dbo.表名
--以后不再使用時(shí)刪除鏈接服務(wù)器
exec sp_dropserver 'ITSV ', 'droplogins '
--連接遠(yuǎn)程/局域網(wǎng)數(shù)據(jù)(openrowset/openquery/opendatasource)
--1、openrowset
--查詢示例
select * from openrowset( 'SQLOLEDB ', 'sql服務(wù)器名 '; '用戶名 '; '密碼 ',數(shù)據(jù)庫(kù)名.dbo.表名)
--生成本地表
select * into 表 from openrowset( 'SQLOLEDB ', 'sql服務(wù)器名 '; '用戶名 '; '密碼 ',數(shù)據(jù)庫(kù)名.dbo.表名)
--把本地表導(dǎo)入遠(yuǎn)程表
insert openrowset( 'SQLOLEDB ', 'sql服務(wù)器名 '; '用戶名 '; '密碼 ',數(shù)據(jù)庫(kù)名.dbo.表名)
select *from 本地表
--更新本地表
update b
set b.列A=a.列A
from openrowset( 'SQLOLEDB ', 'sql服務(wù)器名 '; '用戶名 '; '密碼 ',數(shù)據(jù)庫(kù)名.dbo.表名)as a inner join 本地表 b
on a.column1=b.column1
--openquery用法需要?jiǎng)?chuàng)建一個(gè)連接
--首先創(chuàng)建一個(gè)連接創(chuàng)建鏈接服務(wù)器
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '遠(yuǎn)程服務(wù)器名或ip地址 '
--查詢
select *
FROM openquery(ITSV, 'SELECT * FROM 數(shù)據(jù)庫(kù).dbo.表名 ')
--把本地表導(dǎo)入遠(yuǎn)程表
insert openquery(ITSV, 'SELECT * FROM 數(shù)據(jù)庫(kù).dbo.表名 ')
select * from 本地表
--更新本地表
update b
set b.列B=a.列B
FROM openquery(ITSV, 'SELECT * FROM 數(shù)據(jù)庫(kù).dbo.表名 ') as a
inner join 本地表 b on a.列A=b.列A
--3、opendatasource/openrowset
SELECT *
FROM opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陸名;Password=密碼 ' ).test.dbo.roy_ta
--把本地表導(dǎo)入遠(yuǎn)程表
insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陸名;Password=密碼 ').數(shù)據(jù)庫(kù).dbo.表名
select * from 本地表
SQL Server基本函數(shù)
SQL Server基本函數(shù)
1.字符串函數(shù) 長(zhǎng)度與分析用
1,datalength(Char_expr) 返回字符串包含字符數(shù),但不包含后面的空格
2,substring(expression,start,length) 取子串，字符串的下標(biāo)是從“1”，start為起始位置，length為字符串長(zhǎng)度，實(shí)際應(yīng)用中以len(expression)取得其長(zhǎng)度
3,right(char_expr,int_expr) 返回字符串右邊第int_expr個(gè)字符，還用left于之相反
4,isnull( check_expression , replacement_value )如果check_expression為空，則返回replacement_value的值，不為空，就返回check_expression字符操作類
5,Sp_addtype 自定義數(shù)據(jù)類型
例如：EXEC sp_addtype birthday, datetime, 'NULL'
6,set nocount {on|off}
使返回的結(jié)果中不包含有關(guān)受 Transact-SQL 語(yǔ)句影響的行數(shù)的信息。如果存儲(chǔ)過(guò)程中包含的一些語(yǔ)句并不返回許多實(shí)際的數(shù)據(jù)，則該設(shè)置由于大量減少了網(wǎng)絡(luò)流量，因此可顯著提高性能。SET NOCOUNT 設(shè)置是在執(zhí)行或運(yùn)行時(shí)設(shè)置，而不是在分析時(shí)設(shè)置。
SET NOCOUNT 為 ON 時(shí)，不返回計(jì)數(shù)(表示受 Transact-SQL 語(yǔ)句影響的行數(shù))。
SET NOCOUNT 為 OFF 時(shí)，返回計(jì)數(shù)
常識(shí)
在SQL查詢中：from后最多可以跟多少?gòu)埍砘蛞晥D：256
在SQL語(yǔ)句中出現(xiàn) Order by,查詢時(shí)，先排序，后取
在SQL中，一個(gè)字段的最大容量是8000，而對(duì)于nvarchar(4000),由于nvarchar是Unicode碼。
SQLServer2000同步復(fù)制技術(shù)實(shí)現(xiàn)步驟
一、 預(yù)備工作
1.發(fā)布服務(wù)器,訂閱服務(wù)器都創(chuàng)建一個(gè)同名的windows用戶,并設(shè)置相同的密碼,做為發(fā)布快照文件夾的有效訪問(wèn)用戶
--管理工具
--計(jì)算機(jī)管理
--用戶和組
--右鍵用戶
--新建用戶
--建立一個(gè)隸屬于administrator組的登陸windows的用戶(SynUser)
2.在發(fā)布服務(wù)器上,新建一個(gè)共享目錄,做為發(fā)布的快照文件的存放目錄,操作:
我的電腦--D:\ 新建一個(gè)目錄,名為: PUB
--右鍵這個(gè)新建的目錄
--屬性--共享
--選擇"共享該文件夾"
--通過(guò)"權(quán)限"按紐來(lái)設(shè)置具體的用戶權(quán)限,保證第一步中創(chuàng)建的用戶(SynUser) 具有對(duì)該文件夾的所有權(quán)限
--確定
3.設(shè)置SQL代理(SQLSERVERAGENT)服務(wù)的啟動(dòng)用戶(發(fā)布/訂閱服務(wù)器均做此設(shè)置)
開(kāi)始--程序--管理工具--服務(wù)
--右鍵SQLSERVERAGENT
--屬性--登陸--選擇"此賬戶"
--輸入或者選擇第一步中創(chuàng)建的windows登錄用戶名(SynUser)
--"密碼"中輸入該用戶的密碼
4.設(shè)置SQL Server身份驗(yàn)證模式,解決連接時(shí)的權(quán)限問(wèn)題(發(fā)布/訂閱服務(wù)器均做此設(shè)置)
企業(yè)管理器
--右鍵SQL實(shí)例--屬性
--安全性--身份驗(yàn)證
--選擇"SQL Server 和 Windows"
--確定
5.在發(fā)布服務(wù)器和訂閱服務(wù)器上互相注冊(cè)
企業(yè)管理器
--右鍵SQL Server組
--新建SQL Server注冊(cè)...
--下一步--可用的服務(wù)器中,輸入你要注冊(cè)的遠(yuǎn)程服務(wù)器名 --添加
--下一步--連接使用,選擇第二個(gè)"SQL Server身份驗(yàn)證"
--下一步--輸入用戶名和密碼(SynUser)
--下一步--選擇SQL Server組,也可以創(chuàng)建一個(gè)新組
--下一步--完成
6.對(duì)于只能用IP,不能用計(jì)算機(jī)名的,為其注冊(cè)服務(wù)器別名(此步在實(shí)施中沒(méi)用到)
(在連接端配置,比如,在訂閱服務(wù)器上配置的話,服務(wù)器名稱中輸入的是發(fā)布服務(wù)器的IP)
開(kāi)始--程序--Microsoft SQL Server--客戶端網(wǎng)絡(luò)實(shí)用工具
--別名--添加
--網(wǎng)絡(luò)庫(kù)選擇"tcp/ip"--服務(wù)器別名輸入SQL服務(wù)器名
--連接參數(shù)--服務(wù)器名稱中輸入SQL服務(wù)器ip地址
--如果你修改了SQL的端口,取消選擇"動(dòng)態(tài)決定端口",并輸入對(duì)應(yīng)的端口號(hào)
二、 正式配置
1、配置發(fā)布服務(wù)器
打開(kāi)企業(yè)管理器，在發(fā)布服務(wù)器(B、C、D)上執(zhí)行以下步驟:
(1) 從[工具]下拉菜單的[復(fù)制]子菜單中選擇[配置發(fā)布、訂閱服務(wù)器和分發(fā)]出現(xiàn)配置發(fā)布和分發(fā)向?qū)?/p>
(2) [下一步] 選擇分發(fā)服務(wù)器 可以選擇把發(fā)布服務(wù)器自己作為分發(fā)服務(wù)器或者其他sql的服務(wù)器(選擇自己)
(3) [下一步] 設(shè)置快照文件夾
采用默認(rèn)\\servername\Pub
(4) [下一步] 自定義配置
可以選擇:是,讓我設(shè)置分發(fā)數(shù)據(jù)庫(kù)屬性啟用發(fā)布服務(wù)器或設(shè)置發(fā)布設(shè)置
否,使用下列默認(rèn)設(shè)置(推薦)
(5) [下一步] 設(shè)置分發(fā)數(shù)據(jù)庫(kù)名稱和位置 采用默認(rèn)值
(6) [下一步] 啟用發(fā)布服務(wù)器 選擇作為發(fā)布的服務(wù)器
(7) [下一步] 選擇需要發(fā)布的數(shù)據(jù)庫(kù)和發(fā)布類型
(8) [下一步] 選擇注冊(cè)訂閱服務(wù)器
(9) [下一步] 完成配置
2、創(chuàng)建出版物
發(fā)布服務(wù)器B、C、D上
(1)從[工具]菜單的[復(fù)制]子菜單中選擇[創(chuàng)建和管理發(fā)布]命令
(2)選擇要?jiǎng)?chuàng)建出版物的數(shù)據(jù)庫(kù)，然后單擊[創(chuàng)建發(fā)布]
(3)在[創(chuàng)建發(fā)布向?qū)的提示對(duì)話框中單擊[下一步]系統(tǒng)就會(huì)彈出一個(gè)對(duì)話框。對(duì)話框上的內(nèi)容是復(fù)制的三個(gè)類型。我們現(xiàn)在選第一個(gè)也就是默認(rèn)的快照發(fā)布(其他兩個(gè)大家可以去看看幫助)
(4)單擊[下一步]系統(tǒng)要求指定可以訂閱該發(fā)布的數(shù)據(jù)庫(kù)服務(wù)器類型,
SQLSERVER允許在不同的數(shù)據(jù)庫(kù)如 orACLE或ACCESS之間進(jìn)行數(shù)據(jù)復(fù)制。
但是在這里我們選擇運(yùn)行"SQL SERVER 2000"的數(shù)據(jù)庫(kù)服務(wù)器
(5)單擊[下一步]系統(tǒng)就彈出一個(gè)定義文章的對(duì)話框也就是選擇要出版的表
注意: 如果前面選擇了事務(wù)發(fā)布 則再這一步中只能選擇帶有主鍵的表
(6)選擇發(fā)布名稱和描述
(7)自定義發(fā)布屬性 向?qū)峁┑倪x擇:
是 我將自定義數(shù)據(jù)篩選,啟用匿名訂閱和或其他自定義屬性
否 根據(jù)指定方式創(chuàng)建發(fā)布 (建議采用自定義的方式)
(8)[下一步] 選擇篩選發(fā)布的方式
(9)[下一步] 可以選擇是否允許匿名訂閱
1)如果選擇署名訂閱,則需要在發(fā)布服務(wù)器上添加訂閱服務(wù)器
方法: [工具]->[復(fù)制]->[配置發(fā)布、訂閱服務(wù)器和分發(fā)的屬性]->[訂閱服務(wù)器] 中添加
否則在訂閱服務(wù)器上請(qǐng)求訂閱時(shí)會(huì)出現(xiàn)的提示:改發(fā)布不允許匿名訂閱
如果仍然需要匿名訂閱則用以下解決辦法
[企業(yè)管理器]->[復(fù)制]->[發(fā)布內(nèi)容]->[屬性]->[訂閱選項(xiàng)] 選擇允許匿名請(qǐng)求訂閱
2)如果選擇匿名訂閱,則配置訂閱服務(wù)器時(shí)不會(huì)出現(xiàn)以上提示
(10)[下一步] 設(shè)置快照 代理程序調(diào)度
(11)[下一步] 完成配置
當(dāng)完成出版物的創(chuàng)建后創(chuàng)建出版物的數(shù)據(jù)庫(kù)也就變成了一個(gè)共享數(shù)據(jù)庫(kù)
有數(shù)據(jù)
srv1.庫(kù)名..author有字段:id,name,phone,
srv2.庫(kù)名..author有字段:id,name,telphone,adress
要求：
srv1.庫(kù)名..author增加記錄則srv1.庫(kù)名..author記錄增加
srv1.庫(kù)名..author的phone字段更新，則srv1.庫(kù)名..author對(duì)應(yīng)字段telphone更新
--*/
--大致的處理步驟
--1.在 srv1 上創(chuàng)建連接服務(wù)器,以便在 srv1 中操作 srv2,實(shí)現(xiàn)同步
exec sp_addlinkedserver 'srv2','','SQLOLEDB','srv2的sql實(shí)例名或ip'
exec sp_addlinkedsrvlogin 'srv2','false',null,'用戶名','密碼'
go
--2.在 srv1 和 srv2 這兩臺(tái)電腦中,啟動(dòng) msdtc(分布式事務(wù)處理服務(wù)),并且設(shè)置為自動(dòng)啟動(dòng)
。我的電腦--控制面板--管理工具--服務(wù)--右鍵 Distributed Transaction Coordinator--屬性--啟動(dòng)--并將啟動(dòng)類型設(shè)置為自動(dòng)啟動(dòng)
go
--然后創(chuàng)建一個(gè)作業(yè)定時(shí)調(diào)用上面的同步處理存儲(chǔ)過(guò)程就行了
企業(yè)管理器
--管理
--SQL Server代理
--右鍵作業(yè)
--新建作業(yè)
--"常規(guī)"項(xiàng)中輸入作業(yè)名稱
--"步驟"項(xiàng)
--新建
--"步驟名"中輸入步驟名
--"類型"中選擇"Transact-SQL 腳本(TSQL)"
--"數(shù)據(jù)庫(kù)"選擇執(zhí)行命令的數(shù)據(jù)庫(kù)
--"命令"中輸入要執(zhí)行的語(yǔ)句: exec p_process
--確定
--"調(diào)度"項(xiàng)
--新建調(diào)度
--"名稱"中輸入調(diào)度名稱
--"調(diào)度類型"中選擇你的作業(yè)執(zhí)行安排
--如果選擇"反復(fù)出現(xiàn)"
--點(diǎn)"更改"來(lái)設(shè)置你的時(shí)間安排
然后將SQL Agent服務(wù)啟動(dòng),并設(shè)置為自動(dòng)啟動(dòng),否則你的作業(yè)不會(huì)被執(zhí)行
設(shè)置方法:
我的電腦--控制面板--管理工具--服務(wù)--右鍵 SQLSERVERAGENT--屬性--啟動(dòng)類型--選擇"自動(dòng)啟動(dòng)"--確定.
--3.實(shí)現(xiàn)同步處理的方法2,定時(shí)同步
--在srv1中創(chuàng)建如下的同步處理存儲(chǔ)過(guò)程
create proc p_process
as
--更新修改過(guò)的數(shù)據(jù)
update b set name=i.name,telphone=i.telphone
from srv2.庫(kù)名.dbo.author b,author i
where b.id=i.id and
(b.name <> i.name or b.telphone <> i.telphone)
--插入新增的數(shù)據(jù)
insert srv2.庫(kù)名.dbo.author(id,name,telphone)
select id,name,telphone from author i
where not exists(
select * from srv2.庫(kù)名.dbo.author where id=i.id)
--刪除已經(jīng)刪除的數(shù)據(jù)(如果需要的話)
delete b
from srv2.庫(kù)名.dbo.author b
where not exists(
select * from author where id=b.id)
go
MERGE INTO TLASTGPS T1 USING (SELECT '3109716' AS deviceid FROM dual) T2 ON (T1.deviceid=T2.deviceid) WHEN MATCHED THEN UPDATE SET status=0,y=693413.854883,x=603015.756594,hight=0,speed=0,direction=13,cartime=to_date('2016-09-01 11:35:20','yyyy-MM-dd hh24:mi:ss') WHEN NOT MATCHED THEN INSERT (status,y,x,hight,speed,direction,cartime,deviceid) values(0,693413.854883,603015.756594,0,0,13,to_date('2016-09-01 11:35:20','yyyy-MM-dd hh24:mi:ss'),'3109716')
【oracle的sql語(yǔ)句】相關(guān)文章：
5.ORACLE 常用的SQL語(yǔ)法和數(shù)據(jù)部分