Oracle數(shù)據(jù)庫(kù)第3講(1)

1、oracle第第3講講 1. java程序如何操作oracle 2. 如何在oracle中操作數(shù)據(jù) 3. oracle事務(wù)處理 4. sql函數(shù)的使用 主講 李 珩oracle oracle 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) java連接連接oracle 介紹介紹 前面我們一直在plsql中操作oracle,那么如何在java程序中操作數(shù)據(jù)庫(kù) 呢? 我們?cè)趀clipse中,編寫一個(gè)java程序,完成對(duì)emp表的crud操作. java連接oracle odbc橋連接: Class.forName(sun.jdbc.odbc.JdbcOdbcDriver); Connection ct=DriverManager.

2、getConnection(jdbc:odbc:testlh,scott,“1234); java連接oracle jdbc連接 Class.forName(oracle.jdbc.driver.OracleDriver); Connection ct=DriverManager.getConnection(jdbc:oracle:thin:127.0.0.1:1521:orcl,scott,“1234); 主講 李 珩oracle oracle 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) 在在oracle中操作數(shù)據(jù)中操作數(shù)據(jù)使用特定的格式插入日期使用特定的格式插入日期 使用 to_date函數(shù) 請(qǐng)大家思考：如何插入列帶有

3、日期的表,并按照年-月-日的格式插入? 主講 李 珩oracle oracle 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) oracle中操作數(shù)據(jù)中操作數(shù)據(jù)使用子查詢插入數(shù)據(jù)使用子查詢插入數(shù)據(jù) 介紹介紹 當(dāng)使用values子句時(shí),一次只能插入一行數(shù)據(jù),當(dāng)使用子查詢插入數(shù)據(jù)時(shí), 一條insert語(yǔ)句可以插入大量的數(shù)據(jù).當(dāng)處理行遷移或者裝載外部表的數(shù) 據(jù)到數(shù)據(jù)庫(kù)時(shí),可以使用子查詢來插入數(shù)據(jù). 主講 李 珩oracle oracle 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) oracle中操作數(shù)據(jù)中操作數(shù)據(jù)使用子查詢更新數(shù)據(jù)使用子查詢更新數(shù)據(jù) 介紹介紹 使用update語(yǔ)句更新數(shù)據(jù)時(shí),既可以使用表達(dá)式或者數(shù)值直接修改數(shù)據(jù), 也可以使用子查詢修改數(shù)據(jù)。 ?希

4、望員工scott的崗位、工資、補(bǔ)助與smith員工一樣 主講 李 珩oracle oracle 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) oracle中事務(wù)處理中事務(wù)處理 什么是事務(wù)什么是事務(wù) 事務(wù)用于保證數(shù)據(jù)的一致性,它由一組相關(guān)的dml語(yǔ)句組成,該組的dml語(yǔ) 句要么全部成功，要么全部失敗。 如:網(wǎng)上轉(zhuǎn)賬就是典型的要用事務(wù)來處理，用以保證數(shù)據(jù)的一致性。 事務(wù)和鎖事務(wù)和鎖 當(dāng)執(zhí)行事務(wù)操作時(shí)(dml語(yǔ)句）,oracle會(huì)在被作用的表上加鎖,防止其它用 戶改表表的結(jié)構(gòu).這里對(duì)我們用戶來講是非常重要的。 提交事務(wù)提交事務(wù) 使用commit語(yǔ)句可以提交事務(wù).當(dāng)執(zhí)行了commit語(yǔ)句子后,會(huì)確認(rèn)事務(wù)的 變化、結(jié)束事務(wù)、刪除保存點(diǎn)

5、、釋放鎖，當(dāng)使用commit語(yǔ)句結(jié)束事務(wù) 之后，其它會(huì)話將可以查看到事務(wù)變化后的新數(shù)據(jù) 主講 李 珩oracle oracle 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) oracle中事務(wù)處理中事務(wù)處理 回退事務(wù)回退事務(wù) 在介紹回退事務(wù)前，我們先介紹一下保存點(diǎn)(savepoint)的概念和作用.保 存點(diǎn)是事務(wù)中的一點(diǎn).用于取消部分事務(wù)，當(dāng)結(jié)束事務(wù)時(shí)，會(huì)自動(dòng)的刪 除該事務(wù)所定義的所有保存點(diǎn). 當(dāng)執(zhí)行rollback時(shí)，通過指定保存點(diǎn)可以回退到指定的點(diǎn),這里我們作圖 說明 事務(wù)的幾個(gè)重要操作事務(wù)的幾個(gè)重要操作 1) 設(shè)置保存點(diǎn) savepoint 保存點(diǎn)名 2) 取消部分事務(wù) rollback to 保存點(diǎn)名 3) 取消全部

6、事務(wù) rollback 主講 李 珩oracle oracle 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) oracle中事務(wù)處理中事務(wù)處理 java程序中如何使用事務(wù)程序中如何使用事務(wù) 在java操作數(shù)據(jù)庫(kù)時(shí)，為了保證數(shù)據(jù)的一致性，比如轉(zhuǎn)帳操作(圖): (1)從一個(gè)帳戶減掉10$(2)在另一個(gè)帳戶上加入10$,我們看看如何使用事 務(wù)? 主講 李 珩oracle oracle 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) oracle中事務(wù)處理中事務(wù)處理 事務(wù)隔離級(jí)別事務(wù)隔離級(jí)別 事務(wù)隔離級(jí)別事務(wù)隔離級(jí)別 概念:隔離級(jí)別定義了事務(wù)與事務(wù)之間的隔離程度。 ANSI/ISO SQL92標(biāo)準(zhǔn)定義了一些數(shù)據(jù)庫(kù)操作的隔離級(jí)別(這是國(guó)際標(biāo)準(zhǔn)化 組織定義的一個(gè)標(biāo)準(zhǔn)而已

7、，不同的數(shù)據(jù)庫(kù)在實(shí)現(xiàn)時(shí)有所不同不同的數(shù)據(jù)庫(kù)在實(shí)現(xiàn)時(shí)有所不同)： 隔離級(jí)別隔離級(jí)別 臟讀臟讀 不可重復(fù)讀不可重復(fù)讀 幻讀幻讀 讀未提交（Read uncommitted）VVV 讀已提交（Read committed）xVV 可重復(fù)讀（Repeatable read）xxV 可串行化（Serializable ）xxx V 可能出現(xiàn) x 不會(huì)出現(xiàn) 主講 李 珩oracle oracle 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) oracle中事務(wù)處理中事務(wù)處理 事務(wù)隔離級(jí)別事務(wù)隔離級(jí)別 事務(wù)隔離級(jí)別事務(wù)隔離級(jí)別 臟讀（臟讀（dirty read)：當(dāng)一個(gè)事務(wù)讀取另一個(gè)事務(wù)尚未提交的修改時(shí)，產(chǎn) 生臟讀。 不可重復(fù)讀（不可重復(fù)

8、讀（nonrepeatable read)：同一查詢?cè)谕皇聞?wù)中多次進(jìn)行， 由于其他提交事務(wù)所做的修改或刪除，每次返回不同的結(jié)果集，此時(shí) 發(fā)生非重復(fù)讀。 幻讀（幻讀（phantom read)：同一查詢?cè)谕皇聞?wù)中多次進(jìn)行，由于其他提 交事務(wù)所做的插入操作，每次返回不同的結(jié)果集，此時(shí)發(fā)生幻讀。 主講 李 珩oracle oracle 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) oracle中事務(wù)處理中事務(wù)處理 事務(wù)隔離級(jí)別事務(wù)隔離級(jí)別 oracle的事務(wù)隔離級(jí)別的事務(wù)隔離級(jí)別 ORACLE提供了SQL92標(biāo)準(zhǔn)中的read committed和serializable，同時(shí)提供 了非SQL92標(biāo)準(zhǔn)的read-only ora

9、cle的 read committed 說明： 這是ORACLE缺省的事務(wù)隔離級(jí)別。 保證不會(huì)臟讀；但可能出現(xiàn)非重復(fù)讀和幻像。 oracle的 serializable 說明： serializable就是使事務(wù)看起來象是一個(gè)接著一個(gè)地順序地執(zhí)行(從效 果上可以這樣理解) 僅僅能看見在本事務(wù)開始前由其它事務(wù)提交的更改和在本事務(wù)中所 做的更改 保證不會(huì)出現(xiàn)臟讀、不可重復(fù)讀和幻讀 Serializable隔離級(jí)別提供了read-only事務(wù)所提供的讀一致性（事務(wù) 級(jí)的讀一致性），同時(shí)又允許DML操作 主講 李 珩oracle oracle 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) oracle中事務(wù)處理中事務(wù)處理 事務(wù)隔離級(jí)

10、別事務(wù)隔離級(jí)別 oracle的事務(wù)隔離級(jí)別的事務(wù)隔離級(jí)別 oracle的 read only 說明： 遵從事務(wù)級(jí)的讀一致性，僅僅能看見在本事務(wù)開始前由其它事務(wù)提 交的更改。 不允許在本事務(wù)中進(jìn)行DML操作。 read only是serializable的子集。它們都避免了不可重復(fù)讀和幻讀。區(qū) 別是在read only中是只讀；而在serializable中可以進(jìn)行DML操作 主講 李 珩oracle oracle 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) oracle中事務(wù)處理中事務(wù)處理 事務(wù)隔離級(jí)別事務(wù)隔離級(jí)別 oracle的事務(wù)隔離級(jí)設(shè)置的事務(wù)隔離級(jí)設(shè)置 設(shè)置一個(gè)事務(wù)的隔離級(jí)別 SET TRANSACTION ISO

11、LATION LEVEL READ COMMITTED;(默認(rèn)） SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;（手動(dòng)） SET TRANSACTION READ ONLY; 設(shè)置整個(gè)會(huì)話的隔離級(jí)別 ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE; ALTER SESSION SET ISOLATION_LEVEL READ COMMITED; 主講 李 珩oracle oracle 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) oracle中事務(wù)處理中事務(wù)處理 事務(wù)隔離級(jí)別事務(wù)隔離級(jí)別 oracle的事務(wù)隔離級(jí)的事務(wù)隔離級(jí)-案例案例

12、我們舉例一個(gè)案例來說明oracle的事務(wù)隔離級(jí)別. 以對(duì)emp表進(jìn)行 操作為例。 sqlplus控制臺(tái)sqlplus控制臺(tái) 主講 李 珩oracle oracle 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) oracle中事務(wù)處理中事務(wù)處理 事務(wù)隔離級(jí)別事務(wù)隔離級(jí)別 oracle的事務(wù)隔離級(jí)在的事務(wù)隔離級(jí)在java程序中如何使用程序中如何使用 ct.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); 特別說明特別說明: java程序中Connection.XXXXXX有五個(gè)，但不是所有的數(shù)據(jù)庫(kù)有對(duì)應(yīng)的 五個(gè)事務(wù)隔離級(jí)別實(shí)現(xiàn)。 在實(shí)際工作中，我們極少

13、去修改各個(gè)數(shù)據(jù)庫(kù)默認(rèn)的隔離級(jí)別。 主講 李 珩oracle oracle 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) oracle中事務(wù)處理中事務(wù)處理只讀事務(wù)只讀事務(wù) 只讀事務(wù)實(shí)際運(yùn)用案例只讀事務(wù)實(shí)際運(yùn)用案例 只讀事務(wù)是指只允許執(zhí)行查詢的操作，而不允許執(zhí)行任何其它dml操作 的事務(wù),使用只讀事務(wù)可以確保用戶只能取得某時(shí)間點(diǎn)的數(shù)據(jù)。假定機(jī) 票代售點(diǎn)每天18點(diǎn)開始統(tǒng)計(jì)今天的銷售情況，這時(shí)可以使用只讀事務(wù). 在設(shè)置了只讀事務(wù)后，盡管其它會(huì)話可能會(huì)提交新的事務(wù),但是只讀事 務(wù)將不會(huì)取得最新數(shù)據(jù)的變化,從而可以保證取得特定時(shí)間點(diǎn)的數(shù)據(jù)信 息。(圖示) 設(shè)置只讀事務(wù)設(shè)置只讀事務(wù) set transaction read only 主講

14、 李 珩oracle oracle 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) sqlsql函數(shù)的使用函數(shù)的使用介紹介紹 主講 李 珩oracle oracle 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) l函數(shù)的分類 單行函數(shù)：對(duì)每一行輸入值進(jìn)行計(jì)算，得到相應(yīng)的計(jì)算結(jié)果 返回給用戶，也就是說，每行作為一個(gè)輸入?yún)?shù)，經(jīng)過函數(shù)計(jì)算 得到每行的計(jì)算結(jié)果，比如length 多行函數(shù)：對(duì)多行的輸入值進(jìn)行計(jì)算，得到多行對(duì)應(yīng)的單個(gè)結(jié)果 比如max, min sqlsql函數(shù)的使用函數(shù)的使用單行函數(shù)分類單行函數(shù)分類 主講 李 珩oracle oracle 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) 單行函數(shù) 字符函數(shù) 系統(tǒng)函數(shù) 數(shù)學(xué)函數(shù) 轉(zhuǎn)換函數(shù) 日期函數(shù) sql函數(shù)的使用函數(shù)的使用字符函數(shù)字符

15、函數(shù) 介紹介紹 字符函數(shù)是oracle中最常用的函數(shù)，我們來看看有哪些字符函數(shù): replace(char1,search_string,replace_string) instr(char1,char2,n,m)取子串在字符串的位置 char1:源字符串，要在此字符串中查找 char2:要在char1中查找的字符串 n:代表char1 的哪個(gè)位置開始查找，默認(rèn)為1,如果此參數(shù)為負(fù)，從右到左檢索 m:代表要查找第幾次出現(xiàn)的char2，默認(rèn)為1 練習(xí)?顯示所有員工的姓名,用”我是A”替換所有A“ ?請(qǐng)找出oracle training第二個(gè)ra出現(xiàn)的位置 主講 李 珩oracle oracle

16、數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) sql函數(shù)的使用函數(shù)的使用數(shù)學(xué)函數(shù)數(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) trunc(n,m) mod(m,n) floor(n) ceil(n) 對(duì)數(shù)字的處理，在財(cái)務(wù)系統(tǒng)或銀行系統(tǒng)中用的最多，不同的處理方法， 對(duì)財(cái)務(wù)報(bào)表有不同的結(jié)果。 主講 李 珩oracle oracle 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) sql函數(shù)的使用函數(shù)的使用數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù) 介紹介紹 round(n,m)

17、 該函數(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ù) 案例數(shù)據(jù): 2345.56 45.94 ?顯示在一個(gè)月為30天的情況所有員工的日薪金,忽略余數(shù). 主講 李 珩oracle oracle 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) sql函數(shù)的使用函數(shù)的使用數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)

18、介紹介紹 其它的數(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次冪 主講 李 珩oracle oracle 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) sql函數(shù)的使用函數(shù)的使用日期函數(shù)日期函數(shù) 介紹介紹 日期函數(shù)用于處理date類型的數(shù)據(jù). 默認(rèn)情況下日期格式是dd-mon-yy 即12-7月-78 (1)sysdate: 該

19、函數(shù)返回系統(tǒng)時(shí)間 (2)add_months(d,n) (3)last_day(d):返回指定日期所在月份的最后一天 ?查找已經(jīng)入職8個(gè)月多的員工 ?顯示滿10年服務(wù)年限的員工的姓名和受雇日期. ?對(duì)于每個(gè)員工,顯示其加入公司的天數(shù). ?找出各月倒數(shù)第3天受雇的所有員工. 主講 李 珩oracle oracle 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) sql函數(shù)的使用函數(shù)的使用轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù) 介紹介紹 轉(zhuǎn)換函數(shù)用于將數(shù)據(jù)類型從一種轉(zhuǎn)為另外一種.在某些情況下，oracle server允許值的數(shù)據(jù)類型和實(shí)際的不一樣，這時(shí)oracle server會(huì)隱含的隱含的 轉(zhuǎn)化數(shù)據(jù)類型轉(zhuǎn)化數(shù)據(jù)類型，比如: create table

20、 t1(id number); 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; 我們要說的是盡管oracle可以進(jìn)行隱含的數(shù)據(jù)類型的轉(zhuǎn)換,但是它 并不適應(yīng)所有的情況，為了提高程序的可靠性,我們應(yīng)該使用轉(zhuǎn)換函 數(shù)進(jìn)行轉(zhuǎn)換 主講 李 珩oracle oracle 數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) sql函數(shù)的使用函數(shù)的使用轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù) to_char 你可以使用 select ename,hiredate,sa

21、l from emp where deptno=10; 顯示信息，可是，在某些情況下，這個(gè)并不能滿足你的需求。 ?日期是否可以顯示 時(shí)/分/秒 ?薪水是否可以顯示指定的貨幣符號(hào) yy: yy: 兩位數(shù)字的年份兩位數(shù)字的年份 2004-04 2004-04 yyyy: yyyy: 四位數(shù)字的年份四位數(shù)字的年份 20042004年年 mm :mm :兩位數(shù)字的月份兩位數(shù)字的月份 8 8月月-08-08 dd: 2dd: 2位數(shù)字的天位數(shù)字的天 3030號(hào)號(hào)-30-30 hh24: 8hh24: 8點(diǎn)點(diǎn)2020 hh12: 8hh12: 8點(diǎn)點(diǎn)0808 mimi、ss -ss -顯示分鐘顯示分鐘 秒秒 9:9:顯示數(shù)字顯示數(shù)字, ,并忽略前面并忽略前面0 0 0:0:顯示數(shù)字顯示數(shù)字, ,如位數(shù)不足如位數(shù)不足, ,則用則用0 0補(bǔ)齊補(bǔ)齊 . .：在指定位置顯示小數(shù)點(diǎn)：在指定位置顯示小數(shù)點(diǎn) ,: ,: 在指定位置顯示逗號(hào)在指定