Oracle數(shù)據(jù)庫(kù)開(kāi)發(fā)

1、oracle 數(shù)據(jù)庫(kù)開(kāi)發(fā)數(shù)據(jù)庫(kù)開(kāi)發(fā) 第一章第一章 oracle 入門(mén) 課程地位 .net q 創(chuàng)建表空間的語(yǔ)法是： oracle 邏輯組件 3-3 段段 q 段是構(gòu)成表空間的邏輯存儲(chǔ)結(jié)構(gòu)，段由一組區(qū)組成。 q 按照段所存儲(chǔ)數(shù)據(jù)的特征，將段分為四種類型，即數(shù)據(jù)段、索引段、 回退段和臨時(shí)段。 區(qū)區(qū) q 區(qū)為段分配空間，它由連續(xù)的數(shù)據(jù)塊組成。 q 當(dāng)段中的所有空間已完全使用時(shí)，系統(tǒng)自動(dòng)為該段分配一個(gè)新區(qū)。 q 區(qū)不能跨數(shù)據(jù)文件存在，只能存在于一個(gè)數(shù)據(jù)文件中。 數(shù)據(jù)塊數(shù)據(jù)塊 q 數(shù)據(jù)塊是oracle服務(wù)器所能分配、讀取或?qū)懭氲淖钚〈鎯?chǔ)單元。 q oracle服務(wù)器以數(shù)據(jù)塊為單位管理數(shù)據(jù)文件的存儲(chǔ)空間。

2、 模式模式 q 模式是對(duì)用戶所創(chuàng)建的數(shù)據(jù)庫(kù)對(duì)象的總稱。 q 模式對(duì)象包括表、視圖、索引、同義詞、序列、過(guò)程和程序包等。 oracle 網(wǎng)絡(luò)配置 2-1 qoracle產(chǎn)品安裝完成后，服務(wù)器和客戶端都需要進(jìn) 行網(wǎng)絡(luò)配置才能實(shí)現(xiàn)網(wǎng)絡(luò)連接。 q 服務(wù)器端配置監(jiān)聽(tīng)器，客戶端配置網(wǎng)絡(luò)服務(wù)名。 tnsnames.ora oracle 客戶端客戶端 listener.ora oracle 服務(wù)器服務(wù)器 oracle 網(wǎng)絡(luò)配置 2-2 q服務(wù)器端監(jiān)聽(tīng)器配置信息包括監(jiān)聽(tīng)協(xié)議、地址及 其他相關(guān)信息。 配置信息保存在名為listener.ora 的文件中。在安裝服務(wù)器軟件時(shí)自動(dòng)配置一個(gè)監(jiān) 聽(tīng)器 q客戶端的網(wǎng)絡(luò)服務(wù)名

3、配置信息包括服務(wù)器地址、 監(jiān)聽(tīng)端口號(hào)和數(shù)據(jù)庫(kù)sid等，與服務(wù)器的監(jiān)聽(tīng)器 建立連接。配置信息保存在名為tnsnames.ora的 文件中 qoracle中的 net configuration assistant和net manager工具都能用來(lái)配置監(jiān)聽(tīng)器和網(wǎng)絡(luò)服務(wù)名 oracle 查詢工具 oracle 查詢工具查詢工具 sql*plusisql*pluspl/sql qoracle 提供的工具非常容易使用。oracle 的 查詢工具包括： sql*plus是oracle最常用的工具之一，用于接受和執(zhí)行sql命令以 及pl/sql塊。 isql*plus可以執(zhí)行能用sql*plus完成的所有

4、任務(wù)。該工具的優(yōu)勢(shì) 在于能通過(guò)瀏覽器訪問(wèn)它。 pl/sql 是 sql 的擴(kuò)展。pl/sql 結(jié)合了sql語(yǔ)言的數(shù)據(jù)操縱能力 和過(guò)程語(yǔ)言的流程控制能力 oracle 企業(yè)管理器 qoracle 企業(yè)管理器為管理員提供了圖形化的用 戶界面，并集成了管理數(shù)據(jù)庫(kù)的工具 oracle 默認(rèn)用戶 q只有用合法的用戶帳號(hào)才能訪問(wèn)oracle數(shù)據(jù)庫(kù) qoracle 有幾個(gè)默認(rèn)的數(shù)據(jù)庫(kù)用戶 oracle 默認(rèn)用戶 syssystemscott 數(shù)據(jù)庫(kù)中所有數(shù)據(jù)字典表和視圖都存儲(chǔ)在 sys 模式中。sys用戶主要用 來(lái)維護(hù)系統(tǒng)信息和管理實(shí)例。 system 是默認(rèn)的系統(tǒng)管理員，該用戶擁有oracle管理工具使用

5、的內(nèi)部 表和視圖。通常通過(guò)system用戶管理數(shù)據(jù)庫(kù)用戶、權(quán)限和存儲(chǔ)等 scott用戶是oracle 數(shù)據(jù)庫(kù)的一個(gè)示范帳戶，在數(shù)據(jù)庫(kù)安裝時(shí)創(chuàng)建 創(chuàng)建新用戶 q要連接到oracle數(shù)據(jù)庫(kù)，就需要?jiǎng)?chuàng)建一個(gè)用戶帳戶 q每個(gè)用戶都有一個(gè)默認(rèn)表空間和一個(gè)臨時(shí)表空間 qcreate user命令用于創(chuàng)建新用戶 create user username identified by password default tablespace tablespace temporary tablespace tablespace; qcreate user 命令的語(yǔ)法是： create user martin ide

6、ntified by martinpwd default tablespace users temporary tablespace temp; 創(chuàng)建一個(gè)名稱為 martin 的用戶，其密碼為 martinpwd 授予權(quán)限 3-1 q權(quán)限指的是執(zhí)行特定命令或訪問(wèn)數(shù)據(jù)庫(kù)對(duì)象的權(quán) 利 q權(quán)限有兩種類型，系統(tǒng)權(quán)限和對(duì)象權(quán)限 q系統(tǒng)權(quán)限允許用戶執(zhí)行某些數(shù)據(jù)庫(kù)操作，如創(chuàng)建表就 是一個(gè)系統(tǒng)權(quán)限 q對(duì)象權(quán)限允許用戶對(duì)數(shù)據(jù)庫(kù)對(duì)象（如表、視圖、序列 等）執(zhí)行特定操作 q角色是一組相關(guān)權(quán)限的組合，可以將權(quán)限授予角 色，再把角色授予用戶，以簡(jiǎn)化權(quán)限管理。 授予權(quán)限 3-2 qgrant 命令可用于為用戶分配權(quán)限或角

7、色 grant connect to martin; connect角色允許用戶連接至數(shù)據(jù)庫(kù)， 并創(chuàng)建數(shù)據(jù)庫(kù)對(duì)象 grant resource to martin; resource角色允許用戶使用 數(shù)據(jù)庫(kù)中的存儲(chǔ)空間 grant create sequence to martin; 此系統(tǒng)權(quán)限允許用戶在當(dāng)前模式中創(chuàng)建 序列，此權(quán)限包含在connect角色中 授予權(quán)限 3-3 q授予用戶 martin 操作test表對(duì)象的權(quán)限 grant select on test to martin; 允許用戶查詢 test 表的記錄 grant update on test to martin; 允許用

8、戶更新 test 表中的記錄 grant all on test to martin; 允許用戶插入、刪除、更新和查詢 test 表中的記錄 更改和刪除用戶 qalter user 命令可用于更改口令 alter user martin identified by martinpass; 修改 martin 用戶的密碼 qdrop user 命令用于刪除用戶 drop user martin cascade; 刪除 martin 用戶模式 oracle 9i 的安裝和卸載 q通過(guò)運(yùn)行oracle universal installer來(lái)安裝和卸載 oracle數(shù)據(jù)庫(kù) 選擇 oracle 9i

9、database .0 來(lái)安裝數(shù)據(jù)庫(kù)服務(wù)器。 選擇 oracle 9i client .0 來(lái)安裝 oracle 客戶端 啟動(dòng)universal installer，選擇“卸裝產(chǎn)品”，選中要卸載的組件 使用 oracle 數(shù)據(jù)庫(kù)的開(kāi)發(fā)流程 服務(wù)器端 安裝 oracle 服務(wù)器軟件 創(chuàng)建數(shù)據(jù)庫(kù)(安裝時(shí)自動(dòng)創(chuàng)建) 配置監(jiān)聽(tīng)器(安裝時(shí)自動(dòng)配置) 啟動(dòng)oracle實(shí)例(自動(dòng)啟動(dòng)服務(wù)) 安裝 oracle 客戶端軟件 配置網(wǎng)絡(luò)服務(wù)名 以新用戶登錄 oracle 提交 sql 查詢 創(chuàng)建新用戶并授權(quán) 創(chuàng)建用戶表空間 客戶端 windows 中的 oracle 服務(wù) 3-1 q

10、oracle 9i的每個(gè)實(shí)例在windows中都作為一項(xiàng)服務(wù)啟動(dòng) q 服務(wù)是在 windows 注冊(cè)表中注冊(cè)的可執(zhí)行進(jìn)程，由 windows 操作系統(tǒng)管理 q “服務(wù)”對(duì)話框中的各項(xiàng) oracle 服務(wù)如圖所示： windows 中的 oracle 服務(wù) 3-2 oraclehome_nametnslistener q 該服務(wù)啟動(dòng)數(shù)據(jù)庫(kù)服務(wù)器的監(jiān)聽(tīng)器，監(jiān)聽(tīng)器接受來(lái)自客戶端應(yīng)用程 序的連接請(qǐng)求 q 若監(jiān)聽(tīng)器未啟動(dòng)，則客戶端將無(wú)法連接到數(shù)據(jù)庫(kù)服務(wù)器 oracleservicesid q 該服務(wù)啟動(dòng)系統(tǒng)標(biāo)識(shí)符為sid的數(shù)據(jù)庫(kù)實(shí)例，其中 sid 是在安裝 oracle 9i 時(shí)輸入的數(shù)據(jù)庫(kù)名稱 ora

11、clehome_nameagent q oracle企業(yè)管理器組件智能代理服務(wù)，此智能代理用于執(zhí)行作業(yè)和 監(jiān)控 oracle 服務(wù)或?qū)ο蟮男阅?q 在使用oracle管理服務(wù)器網(wǎng)絡(luò)的環(huán)境中必須啟動(dòng)此服務(wù) windows 中的 oracle 服務(wù) 3-3 oraclehome_namehttpserver q 該服務(wù)用于啟動(dòng)oracle http服務(wù)器，它提供文檔信息、 基于web的企業(yè)管理器和isql*plus等服務(wù) oraclehome_namemanagementserver q 該服務(wù)啟動(dòng)oracle管理服務(wù)器，它用于維護(hù)管理控制臺(tái)對(duì) 各個(gè)被管理服務(wù)器節(jié)點(diǎn)之間的集中的、智能的和分布式的

12、控制 q 該服務(wù)必須經(jīng)過(guò)企業(yè)管理器配置向?qū)渲猛瓿珊蟛艅?chuàng)建 總結(jié) oracle 服務(wù)器由oracle 數(shù)據(jù)庫(kù)和 oracle 實(shí)例組 成 oracle 實(shí)例由系統(tǒng)全局區(qū)內(nèi)存結(jié)構(gòu)和用于管理數(shù) 據(jù)庫(kù)的后臺(tái)進(jìn)程組成 oracle 中用于訪問(wèn)數(shù)據(jù)庫(kù)的主要查詢工具有 sql*plus、isql*plus 和 pl/sql oracle 企業(yè)管理器是用于管理、診斷和調(diào)整多個(gè) 數(shù)據(jù)庫(kù)的工具 oracle 中的 system 用戶和 sys 用戶具有管理 權(quán)限，而 scott 用戶只有基本的權(quán)限 oracle 服務(wù)在 windows 注冊(cè)表中注冊(cè)，并由 windows 操作系統(tǒng)管理 第二章第二章 sql 查詢

13、和 sql 函數(shù) qoracle 服務(wù)器由oracle 數(shù)據(jù)庫(kù)和 oracle 實(shí)例組 成 qoracle 實(shí)例由系統(tǒng)全局區(qū)內(nèi)存結(jié)構(gòu)和用于管理數(shù) 據(jù)庫(kù)的后臺(tái)進(jìn)程組成 qoracle 中用于訪問(wèn)數(shù)據(jù)庫(kù)的主要查詢工具有 sql*plus、isql*plus 和 pl/sql qoracle 企業(yè)管理器是用于管理、診斷和調(diào)整多個(gè) 數(shù)據(jù)庫(kù)的工具 qoracle 中的 system 用戶和 sys 用戶具有管理 權(quán)限，而 scott 用戶只有基本的權(quán)限 回顧 目標(biāo) 了解 oracle 數(shù)據(jù)類型 了解數(shù)據(jù)定義語(yǔ)言和數(shù)據(jù)操縱語(yǔ)言 了解事務(wù)控制語(yǔ)言和數(shù)據(jù)控制語(yǔ)言 掌握 sql 操作符和 sql 函數(shù) sql

14、簡(jiǎn)介 2-1 sql 是 structured query language（結(jié) 構(gòu)化查詢語(yǔ)言）的首字母縮寫(xiě)詞 sql 是數(shù)據(jù)庫(kù)語(yǔ)言，oracle 使用該語(yǔ)言存 儲(chǔ)和檢索信息 表是主要的數(shù)據(jù)庫(kù)對(duì)象，用于存儲(chǔ)數(shù)據(jù) 通過(guò) sql可以實(shí)現(xiàn)與 oracle 服務(wù)器的通 信 select ename from emp; 發(fā)送 sql 查詢 oracle 服務(wù)器 ename blake smith allen david martin 發(fā)送命令輸出到用戶端 用戶 sql 簡(jiǎn)介 2-2 sql 支持下列類別的命令： 數(shù)據(jù)定義語(yǔ)言（ddl） 數(shù)據(jù)操縱語(yǔ)言（dml） 事務(wù)控制語(yǔ)言（tcl） 數(shù)據(jù)控制語(yǔ)言（dcl

15、） 數(shù)據(jù)定義語(yǔ)言數(shù)據(jù)定義語(yǔ)言 createalterdrop 數(shù)據(jù)操縱語(yǔ)言數(shù)據(jù)操縱語(yǔ)言 insertselectdeleteupdate 事務(wù)控制語(yǔ)言事務(wù)控制語(yǔ)言 commit savepoint rollback 數(shù)據(jù)控制語(yǔ)言數(shù)據(jù)控制語(yǔ)言 grantrevoke oracle 數(shù)據(jù)類型 5-1 創(chuàng)建表時(shí)，必須為各個(gè)列指定數(shù)據(jù)類型 以下是 oracle 數(shù)據(jù)類型的類別： 數(shù)據(jù)類型數(shù)據(jù)類型 字符數(shù)值日期時(shí)間raw/long rawlob oracle 數(shù)據(jù)類型5-2 字符數(shù)據(jù)類型字符數(shù)據(jù)類型 charvarchar2long 當(dāng)需要固定長(zhǎng)度的字符串時(shí)，使用 char 數(shù)據(jù) 類型。 char 數(shù)據(jù)

16、類型存儲(chǔ)字母數(shù)字值。 char 數(shù)據(jù)類型的列長(zhǎng)度可以是 1 到 2000 個(gè)字 節(jié)。 varchar2數(shù)據(jù)類型支持可變長(zhǎng)度字符串 varchar2數(shù)據(jù)類型存儲(chǔ)字母數(shù)字值 varchar2數(shù)據(jù)類型的大小在1至4000個(gè)字節(jié)范 圍內(nèi) long 數(shù)據(jù)類型存儲(chǔ)可變長(zhǎng)度字符數(shù)據(jù) long 數(shù)據(jù)類型最多能存儲(chǔ) 2gb oracle 數(shù)據(jù)類型 5-3 數(shù)值數(shù)據(jù)類型 可以存儲(chǔ)整數(shù)、浮點(diǎn)數(shù)和實(shí)數(shù) 最高精度為 38 位 數(shù)值數(shù)據(jù)類型的聲明語(yǔ)法： number ( p, s) p表示精度，s表示小數(shù)點(diǎn)的位數(shù) 日期時(shí)間數(shù)據(jù)類型存儲(chǔ)日期和時(shí)間值，包 括年、月、日，小時(shí)、分鐘、秒 主要的日期時(shí)間類型有： date - 存

17、儲(chǔ)日期和時(shí)間部分，精確到整個(gè)的 秒 timestamp - 存儲(chǔ)日期、時(shí)間和時(shí)區(qū)信息， 秒值精確到小數(shù)點(diǎn)后6位 oracle 數(shù)據(jù)類型 5-4 raw 數(shù)據(jù)類型用于存儲(chǔ)二進(jìn)制數(shù)據(jù) raw 數(shù)據(jù)類型最多能存儲(chǔ) 2000 字節(jié) long raw 數(shù)據(jù)類型用于存儲(chǔ)可變長(zhǎng)度 的二進(jìn)制數(shù)據(jù) long raw 數(shù)據(jù)類型最多能存儲(chǔ) 2 gb lob lob 稱為“大對(duì)象”數(shù)據(jù)類型，可以存儲(chǔ) 多達(dá) 4gb 的非結(jié)構(gòu)化信息，例如聲音剪 輯和視頻文件等 lob 數(shù)據(jù)類型允許對(duì)數(shù)據(jù)進(jìn)行高效、隨機(jī)、 分段的訪問(wèn) blobclobbfile clob 即 character lob（字符 lob），它能夠存儲(chǔ)大量字符數(shù)

18、據(jù) blob 即 binary lob（二進(jìn)制 lob），可以存儲(chǔ)較大的二進(jìn)制對(duì) 象，如圖形、視頻剪輯和聲音文件 bfile 即 binary file（二進(jìn)制文 件），它用于將二進(jìn)制數(shù)據(jù)存儲(chǔ)在 數(shù)據(jù)庫(kù)外部的操作系統(tǒng)文件中 oracle 數(shù)據(jù)類型 5-5 qoracle 中偽列就像一個(gè)表列，但是它并沒(méi)有存 儲(chǔ)在表中 q偽列可以從表中查詢，但不能插入、更新和刪除 它們的值 q常用的偽列有rowid和rownum rowid 是表中行的存儲(chǔ)地址，該地址可以唯一地標(biāo)識(shí)數(shù)據(jù)庫(kù)中 的一行，可以使用 rowid 偽列快速地定位表中的一行 rownum 是查詢返回的結(jié)果集中行的序號(hào)，可以使用它來(lái)限 制查詢返

19、回的行數(shù) 數(shù)據(jù)定義語(yǔ)言 數(shù)據(jù)定義語(yǔ)言用于改變數(shù)據(jù)庫(kù)結(jié)構(gòu)，包括創(chuàng) 建、更改和刪除數(shù)據(jù)庫(kù)對(duì)象 用于操縱表結(jié)構(gòu)的數(shù)據(jù)定義語(yǔ)言命令有： create table alter table truncate table drop table 數(shù)據(jù)操縱語(yǔ)言 q數(shù)據(jù)操縱語(yǔ)言用于檢索、插入和修改數(shù)據(jù) q數(shù)據(jù)操縱語(yǔ)言是最常見(jiàn)的sql命令 q數(shù)據(jù)操縱語(yǔ)言命令包括： qselect qinsert qupdate qdelete dml select 命令 2-1 q利用現(xiàn)有的表創(chuàng)建表 q語(yǔ)法： create table as select column_names from ; sql create table n

20、ewitemfile as select * from itemfile; sql create table newitemfile1 as select itemcode, itemdesc, qty_hand from itemfile; sql create table newitemfile2 as select * from itemfile where 1 = 2; dml select 命令 2-2 sql select distinct vencode from vendor_master; q選擇無(wú)重復(fù)的行 q在select子句，使用distinct關(guān)鍵字 q使用列別名 q為

21、列表達(dá)式提供不同的名稱 q該別名指定了列標(biāo)題 sql select itemcode, itemdesc, max_level, max_level*2 as new_maxlevel from itemfile; sql select itemcode,itemdesc, max_level, max_level* 2 “new maximum level” from itemfile; dml insert 命令 2-1 q插入日期類型的值 q日期數(shù)據(jù)類型的默認(rèn)格式為“dd-mon-rr” q使用日期的默認(rèn)格式 q使用to_date函數(shù)轉(zhuǎn)換 insert into my_table (da

22、te_col) values (to_date(2005-10-18, yyyy-mm-dd); insert into order_master values(o001, 12-5月-05, v002, c, 25-5月-05); dml insert 命令 2-2 q插入來(lái)自其它表中的記錄 q語(yǔ)法： insert into (cloumn_list) select column_names from ; sql insert into newvendor_master select * from vendor_master; sql insert into newvendor_master

23、(vencode,venname) select vencode, venname from vendor_master; 事務(wù)控制語(yǔ)言 q事務(wù)是最小的工作單元，作為一個(gè)整體進(jìn)行工作 q保證事務(wù)的整體成功或失敗，稱為事務(wù)控制 q用于事務(wù)控制的語(yǔ)句有： qcommit - 提交并結(jié)束事務(wù)處理 qrollback - 撤銷事務(wù)中已完成的工作 qsavepoint 標(biāo)記事務(wù)中可以回滾的點(diǎn) sql update order_master set del_date = 30-8月-05 where orderno savepoint mark1; sql delete from order_master

24、 where orderno = o002; sql savepoint mark2; sql rollback to savepoint mark1; sql commit; 數(shù)據(jù)控制語(yǔ)言 q數(shù)據(jù)控制語(yǔ)言為用戶提供權(quán)限控制命令 q用于權(quán)限控制的命令有： qgrant 授予權(quán)限 qrevoke 撤銷已授予的權(quán)限 sql grant select, update on order_master to martin; sql grant update(qty_hand, re_level) on itemfile to martin; sql grant select on vendor_mast

25、er to accounts with grant option; sql revoke select, update on order_master from martin; sql 操作符 sql 操作符 集合操作符 邏輯操作符 比較操作符 算術(shù)操作符連接操作符 qoracle 支持的 sql 操作符分類如下： 算術(shù)操作符 算術(shù)操作符用于執(zhí)行數(shù)值計(jì)算 可以在sql語(yǔ)句中使用算術(shù)表達(dá)式，算術(shù) 表達(dá)式由數(shù)值數(shù)據(jù)類型的列名、數(shù)值常量 和連接它們的算術(shù)操作符組成 算術(shù)操作符包括加(+)、減(-)、乘(*)、除(/) sql select itemdesc, max_level - qty_hand

26、 avble_limit from itemfile where p_category=spares; sql select itemdesc, itemrate*(max_level - qty_hand) from itemfile where p_category=spares; 比較操作符 q比較操作符用于比較兩個(gè)表達(dá)式的值 q比較操作符包括 =、!=、=、 betweenand、in、like 和 is null等 sql select itemdesc, re_level from itemfile where qty_hand select orderno from order_

27、master where del_date in (06-1月-05,05-2月-05); sql select vencode,venname,tel_no from vendor_master where venname like j_s; 邏輯操作符 sql select * from order_master where odate 10-5月-05 and del_date select orderno from order_master intersect select orderno from order_detail; sql select orderno from order

28、_master minus select orderno from order_detail; minus 操作符返回從第一個(gè)查詢結(jié)果中排除第二個(gè)查 詢中出現(xiàn)的行。 連接操作符 q連接操作符用于將多個(gè)字符串或數(shù)據(jù)值合并成一 個(gè)字符串 sql select (venname| 的地址是 |venadd1| |venadd2 | |venadd3) address from vendor_master where vencode=v001; 通過(guò)使用連接操作符可以將表中 的多個(gè)列合并成邏輯上的一行列 操作符的優(yōu)先級(jí) sql 操作符的優(yōu)先級(jí)從高到低的順序是： 算術(shù)操作符 -最高優(yōu)先級(jí) 連接操作符 比

29、較操作符 not 邏輯操作符 and 邏輯操作符 or 邏輯操作符 -最低優(yōu)先級(jí) sql 函數(shù) oracle 提供一系列用于執(zhí)行特定操作的函 數(shù) sql 函數(shù)帶有一個(gè)或多個(gè)參數(shù)并返回一個(gè) 值 以下是sql函數(shù)的分類： sql 函數(shù) 單行函數(shù) 分析函數(shù)分組函數(shù) 單行函數(shù)分類 單行函數(shù)對(duì)于從表中查詢的每一行只返回 一個(gè)值 可以出現(xiàn)在 select 子句中和 where 子 句中 單行函數(shù)可以大致劃分為： 日期函數(shù) 數(shù)字函數(shù) 字符函數(shù) 轉(zhuǎn)換函數(shù) 其他函數(shù) 日期函數(shù) 日期函數(shù)對(duì)日期值進(jìn)行運(yùn)算，并生成日期 數(shù)據(jù)類型或數(shù)值類型的結(jié)果 日期函數(shù)包括： add_months months_between la

30、st_day round next_day trunc extract 字符函數(shù) 2-1 函數(shù)函數(shù) 輸入輸入 輸出輸出 initcap(char) select initcap(hello) from dual;hello lower(char) select lower(fun) from dual;fun upper(char) select upper(sun) from dual;sun ltrim(char,set) select ltrim( xyzadams,xyz) from dual; adams rtrim(char,set) select rtrim(xyzadams,a

31、ms) from dual; xyzad translate(char, from, to) select translate(jack,j ,b) from dual; back replace(char, searchstring,rep string) select replace(jack and jue ,j,bl) from dual; black and blue instr (char, m, n) select instr (worldwide,d) from dual; 5 substr (char, m, n) select substr(abcdefg,3,2) fro

32、m dual; cd 字符函數(shù)字符函數(shù) 字符函數(shù)接受字符輸入并返回字符或數(shù) 值 字符函數(shù) 2-2 sql select chr(67) from dual; q以下是一些其它的字符函數(shù)： qchr和ascii qlpad和rpad qtrim qlength qdecode sql select lpad(function,15,=) from dual; sql select trim(9 from 9999876789999) from dual; sql select length(frances) from dual; sql select vencode, decode(vennam

33、e,frances,francis) name from vendor_master where vencode=v001; 數(shù)字函數(shù) q數(shù)字函數(shù)接受數(shù)字輸入并返回?cái)?shù)值結(jié)果 函數(shù) 輸入 輸出 abs(n) select abs(-15) from dual; 15 ceil(n) select ceil(44.778) from dual; 45 cos(n) select cos(180) from dual; -.5984601 cosh(n) select cosh(0) from dual; 1 floor(n) select floor(100.2) from dual; 100 p

34、ower(m,n) select power(4,2) from dual; 16 mod(m,n) select mod(10,3) from dual; 1 round(m,n) select round(100.256,2) from dual; 100.26 trunc(m,n) select trunc(100.256,2) from dual; 100.25 sqrt(n) select sqrt(4) from dual; 2 sign(n)select sign(-30) from dual;-1 數(shù)字函數(shù)數(shù)字函數(shù) 轉(zhuǎn)換函數(shù) q轉(zhuǎn)換函數(shù)將值從一種數(shù)據(jù)類型轉(zhuǎn)換為另一種數(shù)據(jù) 類型

35、q常用的轉(zhuǎn)換函數(shù)有： qto_char qto_date qto_number select to_char(sysdate,yyyy年年fmmm月月fmdd日日 hh24:mi:ss) from dual; select to_char(itemrate,c99999) from itemfile; select to_date(2005-12-06 , yyyy-mm-dd) from dual; select to_number(100) from dual; 其它函數(shù) q以下是幾個(gè)用來(lái)轉(zhuǎn)換空值的函數(shù)： qnvl qnvl2 qnullif select itemdesc, nvl(re

36、_level,0) from itemfile; select itemdesc, nvl2(re_level,re_level,max_level) from itemfile; select itemdesc, nullif(re_level,max_level) from itemfile; 分組函數(shù) q分組函數(shù)基于一組行來(lái)返回結(jié)果 q為每一組行返回一個(gè)值 avgminmaxsumcount select avg(re_level) from itemfile where p_category=accessories; select max(max_level) from itemfil

37、e; select sum(itemrate*max_level) from itemfile; select count(*) from itemfile; select count(itemrate) from itemfile; select count(distinct qty_hand) from itemfile; 分組函數(shù) group by和having子句 qgroup by子句 q用于將信息劃分為更小的組 q每一組行返回針對(duì)該組的單個(gè)結(jié)果 qhaving子句 q用于指定 group by 子句檢索行的條件 select p_category, max(itemrate) fr

38、om itemfile group by p_category; select p_category, max(itemrate) from itemfile group by p_category having p_category not in (accessories); 分析函數(shù) 2-1 q分析函數(shù)根據(jù)一組行來(lái)計(jì)算聚合值 q用于計(jì)算完成聚集的累計(jì)排名、移動(dòng)平均數(shù)等 q分析函數(shù)為每組記錄返回多個(gè)行 分析函數(shù) dense_rankrankrow_number 分析函數(shù) 2-2 以下三個(gè)分析函數(shù)用于計(jì)算一個(gè)行在一組有序行 中的排位，序號(hào)從1開(kāi)始 row_number 返回連續(xù)的排位，不論值是

39、 否相等 rank 具有相等值的行排位相同，序數(shù)隨后跳 躍 dense_rank 具有相等值的行排位相同，序 號(hào)是連續(xù)的 select ename, job, deptno, sal, row_number() over (order by sal desc) as sal_rank from scott.emp; select deptno, ename, sal, comm, rank() over (partition by deptno order by sal desc, comm) rank from emp; select d.dname, e.ename, e.sal, den

40、se_rank() over (partition by e.deptno order by e.sal desc) as denrank from emp e, dept d where e.deptno = d.deptno; 總結(jié) sql 是通用的數(shù)據(jù)庫(kù)語(yǔ)言 sql 命令可分為數(shù)據(jù)定義語(yǔ)言、數(shù)據(jù)操縱 語(yǔ)言、事務(wù)控制語(yǔ)言和數(shù)據(jù)控制語(yǔ)言 oracle 支持的數(shù)據(jù)類型包括字符、數(shù)值、 日期時(shí)間、raw 和 lob 等 sql 支持的操作符包括算術(shù)、比較、邏輯、 集合和連接操作符 sql 函數(shù)可大致分為單行函數(shù)、聚合函數(shù) 和分析函數(shù) 第三章第三章 鎖和表分區(qū) 回顧 sql 是數(shù)據(jù)庫(kù)語(yǔ)言，orac

41、le 使用該語(yǔ)言在 數(shù)據(jù)庫(kù)中存儲(chǔ)和檢索信息 oracle 支持各種數(shù)據(jù)類型，如 varchar2、 number、long、raw 和 date 等 數(shù)據(jù)操縱語(yǔ)言用于查詢和修改表中的數(shù)據(jù) 事務(wù)控制語(yǔ)言管理事務(wù)的一致性 sql 操作符包括算術(shù)、比較、邏輯、集合 和連接操作符 sql 函數(shù)可以大致分為單行函數(shù)、聚合函 數(shù)和分析函數(shù) 目標(biāo) 理解鎖定的概念 了解和使用表分區(qū) 鎖的概念 2-1 鎖是數(shù)據(jù)庫(kù)用來(lái)控制共享資源并發(fā)訪問(wèn)的 機(jī)制。 鎖用于保護(hù)正在被修改的數(shù)據(jù) 直到提交或回滾了事務(wù)之后，其他用戶才 可以更新數(shù)據(jù) 鎖的概念 2-2 修改表修改表 拒絕訪問(wèn) toy_i d name price t00

42、1 barbi e 20 t002 gi joe 45 鎖定的優(yōu)點(diǎn) 一致性一致性 - 一次只允許一個(gè)用戶修改數(shù)據(jù) 完整性完整性 - 為所有用戶提供正確的數(shù)據(jù)。如果一 個(gè)用戶進(jìn)行了修改并保存，所做的修改將反映 給所有用戶 q并行性并行性 允許多個(gè)用戶訪問(wèn)同一數(shù)據(jù) toy_i d nam e price t001 barbi e 20 t002 gi joe 45 修改表中 的數(shù)據(jù) 查看表中 的數(shù)據(jù) 允許訪問(wèn) 表級(jí)鎖行級(jí)鎖 鎖的類型 鎖的類型 行級(jí)鎖 3-1 toy_i d nameprice t001barbie20 t002gi joe45 更新 t002 行更新 t001 行 行被鎖定 q對(duì)

43、正在被修改的行進(jìn)行鎖定。其他用戶可以訪問(wèn) 除被鎖定的行以外的行 允許訪問(wèn) 行級(jí)鎖 3-2 行級(jí)鎖是一種排他鎖，防止其他事務(wù)修改 此行 在使用以下語(yǔ)句時(shí)，oracle會(huì)自動(dòng)應(yīng)用行級(jí) 鎖： insert update delete select for update select for update語(yǔ)句允許用戶一 次鎖定多條記錄進(jìn)行更新 使用commit或rollback語(yǔ)句釋放鎖 行級(jí)鎖 3-3 select for update語(yǔ)法: select for update of columns wait n | nowait; sql select * from order_master wh

44、ere vencode=v002 for update of odate, del_date; sql update order_master set del_date=28-8月-05 where vencode=v002; sql commit; sql select * from order_master where vencode=v002 for update wait 5; sql select * from order_master where vencode=v002 for update nowait; 表級(jí)鎖 3-1 toy_id nameprice t001barbie2

45、0 t002gi joe45 修改表中的行 更新表 拒絕訪問(wèn) 鎖定整個(gè)表，限制其他用戶對(duì)表的訪問(wèn)。 表級(jí)鎖 3-2 表級(jí)鎖類型 行共享 行排他 共享 使用命令顯示地鎖定表，應(yīng)用表級(jí)鎖的語(yǔ)法 是: lock table table_name in mode mode; 共享行排他 排他 表級(jí)鎖 3-3 行共享 (row share) 禁止排他鎖定表 行排他(row exclusive) 禁止使用排 他鎖和共享鎖 共享鎖(share) 鎖定表，僅允許其他用戶查詢表中的行 禁止其他用戶插入、更新和刪除行 多個(gè)用戶可以同時(shí)在同一個(gè)表上應(yīng)用此鎖 共享行排他(share row exclusive) 比共

46、享鎖更多的限制，禁止使用共享鎖及 更高的鎖 排他(exclusive) 限制最強(qiáng)的表鎖，僅 允許其他用戶查詢?cè)摫淼男?。禁止修改?鎖定表 死鎖 當(dāng)兩個(gè)事務(wù)相互等待 對(duì)方釋放資源時(shí)，就 會(huì)形成死鎖 oracle會(huì)自動(dòng)檢測(cè)死 鎖，并通過(guò)結(jié)束其中 的一個(gè)事務(wù)來(lái)解決死 鎖 右邊是一個(gè)死鎖的例 子 t1t2 lock (d1) 時(shí)間時(shí)間 lock (d2) lock (d2) lock (d1) 等待等待 等待等待 表分區(qū) 允許用戶將一個(gè)表分成多個(gè)分區(qū) 用戶可以執(zhí)行查詢，只訪問(wèn)表中的特定分 區(qū) 將不同的分區(qū)存儲(chǔ)在不同的磁盤(pán)，提高訪 問(wèn)性能和安全性 可以獨(dú)立地備份和恢復(fù)每個(gè)分區(qū) nameaddressst

47、atecountr y john smith 34th rd.californi a us micheal clarke lk rd. new york us jack jones 9th roadcaliforni a us bob simmon s 12th street new york us jim taylor 53rd road new york us 更新表 只訪問(wèn) p1 p1 分區(qū) p2 分區(qū) 表分區(qū)的類型 9-1 分區(qū)方法分區(qū)方法 范圍分區(qū)散列分區(qū)列表分區(qū)復(fù)合分區(qū) 表分區(qū)的類型 9-2 范圍分區(qū) 以表中的一個(gè)列或一組列的值的范圍分區(qū) 范圍分區(qū)的語(yǔ)法： partition by

48、 range (column_name) ( partition part1 value less than(range1), partition part2 value less than(range2), . partition partn value less than(maxvalue) ); 表分區(qū)的類型 9-3 sql create table sales ( product_id varchar2 (5), sales_cost number (10) ) partition by range (sales_cost) ( partition p1 values less tha

49、n (1000), partition p2 values less than (2000), partition p3 values less than (3000) ); 根據(jù) sales_cost 創(chuàng)建分區(qū) 分區(qū)的名稱 包含銷售成本低于1000 的所有產(chǎn)品的值 sql create table sales2 ( product_id varchar2(5), sales_date date not null, sales_cost number(10) partition by range (sales_date) ( partition p1 values less than (dat

50、e 2003-01-01), partition p2 values less than (date 2004-01-01), partition p3 values less than (maxvalue) ); 范圍分區(qū)示例 表分區(qū)的類型 9-4 散列分區(qū) 允許用戶對(duì)不具有邏輯范圍的數(shù)據(jù)進(jìn)行分區(qū) 通過(guò)在分區(qū)鍵上執(zhí)行hash函數(shù)決定存儲(chǔ)的分 區(qū) 將數(shù)據(jù)平均地分布到不同的分區(qū) 散列分區(qū)語(yǔ)法 partition by hash (column_name) partitions number_of_partitions; 或 partition by hash (column_name) ( pa

51、rtition part1 tablespace tbs1, partition part2 tablespace tbs2, . partition partn tablespace tbsn); 表分區(qū)的類型 9-5 散列分區(qū)示例 sql create table employee ( employee_id varchar2 (5), employee_name varchar2(20), department varchar2 (10) ) partition by hash (department) ( partition d1, partition d2, partition d3

52、 ); 在表 employee上創(chuàng)建 分區(qū)鍵 department 分區(qū)的名稱 創(chuàng)建 3 個(gè)分區(qū) sql create table employee ( emp_id number(4), emp_name varchar2(14), emp_address varchar2(15), department varchar2(10) ) partition by hash (department) partitions 4; 表分區(qū)的類型 9-6 列表分區(qū) 允許用戶將不相關(guān)的數(shù)據(jù)組織在一起 列表分區(qū)的語(yǔ)法： partition by list (column_name) ( partition

53、 part1 values (values_list1), partition part2 values (values_list2), . partition partn values (default) ); 表分區(qū)的類型 9-7 sql create table employee ( emp_id number (4), emp_name varchar2 (14), emp_address varchar2 (15) ) partition by list (emp_address) ( partition north values (芝加哥), partition west valu

54、es (舊金山, 洛杉磯), partition south values (亞特蘭大, 達(dá)拉斯, 休斯頓), partition east values (紐約, 波斯頓) ); 包含住在芝加哥的職員 的記錄 根據(jù)職員住址在 表上創(chuàng)建的列表 分區(qū) 分區(qū)的名稱 列表分區(qū)示例 表分區(qū)的類型 9-8 復(fù)合分區(qū) 范圍分區(qū)與散列分區(qū)或列表分區(qū)的組合 復(fù)合分區(qū)的語(yǔ)法： partition by range (column_name1) subpartition by hash (column_name2) subpartitions number_of_partitions ( partition pa

55、rt1 value less than(range1), partition part2 value less than(range2), . partition partn value less than(maxvalue) ); 表分區(qū)的類型 9-9 sql create table sales ( product_id varchar2 (5), sales_date date not null, sales_cost number (10) ) partition by range (sales_date) subpartition by hash (product_id) subpa

56、rtitions 5 ( partition s1 values less than (to_date(01/4月/2001, dd/mon/yyyy), partition s2 values less than (to_date(01/7月/2001, dd/mon/yyyy), partition s3 values less than (to_date(01/9月/2001, dd/mon/yyyy), partition s4 values less than (maxvalue) );創(chuàng)建的四個(gè)范圍分區(qū)的名稱 在表的 sales_date 列中創(chuàng)建范圍分區(qū) 在表的 product_

57、id 列創(chuàng)建散列子分區(qū) 在每個(gè)范圍分區(qū)中 創(chuàng)建 5 個(gè)散列子分區(qū) 復(fù)合分區(qū)示例 操縱已分區(qū)的表 在已分區(qū)的表中插入數(shù)據(jù)與操作普通表完 全相同，oracle會(huì)自動(dòng)將數(shù)據(jù)保存到對(duì)應(yīng)的 分區(qū) 查詢、修改和刪除分區(qū)表時(shí)可以顯式指定 要操作的分區(qū) insert into sales3 values (p001, 02-3月-2001, 2000); insert into sales3 values (p002, 10-5月-2001, 2508); insert into sales3 values (p003, 05-7月-2001, 780); insert into sales3 values

58、(p004, 12-9月-2001, 1080); select * from sales3 partition (p3); delete from sales3 partition (p2); 分區(qū)維護(hù)操作 分區(qū)維護(hù)操作修改已分區(qū)表的分區(qū)。 分區(qū)維護(hù)的類型： 計(jì)劃事件 定期刪除最舊的分區(qū) 非計(jì)劃事件 解決應(yīng)用程序或系統(tǒng)問(wèn)題 分區(qū)維護(hù)操作有： 添加分區(qū) 刪除分區(qū) 截?cái)喾謪^(qū) 合并分區(qū) 拆分分區(qū) 維護(hù)分區(qū) 2-1 添加分區(qū) 在最后一個(gè)分區(qū)之后添加新分 區(qū) sql alter table sales add partition p4 values less than (4000); 刪除分區(qū) 刪除一

59、個(gè)指定的分區(qū)，分區(qū)的 數(shù)據(jù)也隨之刪除 sql alter table sales drop partition p4; 截?cái)喾謪^(qū) 刪除指定分區(qū)中的所有記 錄 sql alter table sales truncate partition p3; 維護(hù)分區(qū) 2-2 合并分區(qū) - 將范圍分區(qū)或復(fù)合分區(qū)的兩個(gè)相 鄰分區(qū)連接起來(lái) sql alter table sales merge partitions s1, s2 into partition s2; 拆分分區(qū) - 將一個(gè)大分區(qū)中的記錄拆分到兩 個(gè)分區(qū)中 sql alter table sales split partition p2 at (

60、1500) into (partition p21, partition p22); 總結(jié) 鎖用于保護(hù)多用戶環(huán)境下被修改的數(shù)據(jù) 鎖分為兩種級(jí)別，即行級(jí)鎖和表級(jí)鎖 表分區(qū)允許將一個(gè)表劃分成幾部分，以改 善大型應(yīng)用系統(tǒng)的性能 分區(qū)方法包括范圍分區(qū)、散列分區(qū)、復(fù)合 分區(qū)和列表分區(qū) 分區(qū)維護(hù)操作包括添加、刪除、截?cái)?、?并和拆分分區(qū) 第四章第四章 數(shù)據(jù)庫(kù)對(duì)象 回顧 鎖用于保護(hù)多用戶環(huán)境下被修改的數(shù)據(jù) 鎖分為兩種級(jí)別，即行級(jí)鎖和表級(jí)鎖 表分區(qū)允許將一個(gè)表劃分成幾部分，以改 善大型應(yīng)用系統(tǒng)的性能 分區(qū)方法包括范圍分區(qū)、散列分區(qū)、復(fù)合 分區(qū)和列表分區(qū) 分區(qū)維護(hù)操作包括添加、刪除、截?cái)唷⒑?并和拆分分區(qū) 目標(biāo)