o數(shù)據(jù)庫(kù)技術(shù)第07章與基礎(chǔ)

1、第七章 SQL 與 PL/SQL 基礎(chǔ)7.1 SQL 命令概述SQL(Structured Query Language)是結(jié)構(gòu)化語(yǔ)言的縮寫，它是目前使用最廣泛的數(shù)據(jù)庫(kù)語(yǔ)言，SQL 是由 IBM 發(fā)展起來(lái)的，后來(lái)被許多數(shù)據(jù)庫(kù)軟件公司接受的一個(gè)標(biāo)準(zhǔn)。SQL 是一種靈活、有效的語(yǔ)言，它的一些功能特性專門用來(lái)處理和檢驗(yàn)關(guān)系型數(shù)據(jù)。SQL 是一種第四代語(yǔ)言，這就意味著該語(yǔ)言會(huì)描述應(yīng)該做些什么，但是描述如何去做。PL/SQL 是 ANSI 標(biāo)準(zhǔn) SQL 的 Oracle 版本的過(guò)程化語(yǔ)言的擴(kuò)展。PL/SQL 像第三代（3GL）過(guò)程化語(yǔ)言一樣，要求一步步指導(dǎo)來(lái)確定下一步做什么。類似其他的工業(yè)標(biāo)準(zhǔn)語(yǔ)言，PL

2、/SQL提供變量、賦值、條件測(cè)試與分支以及迭代的語(yǔ)言元素。但真正實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)的操作還是通過(guò) SQL 命令來(lái)實(shí)現(xiàn)的。所以在介紹 PL/SQL 之前，我們必須要知道基本的 SQL 命令。Oracle 中 SQL 語(yǔ)言命令共分為六個(gè)類別，如下表所示：表 7-1 SQL 基本命令分類這些命令語(yǔ)句的主要含義如下：Ø 數(shù)據(jù)定義語(yǔ)言(Data definition language，簡(jiǎn)稱 DDL)語(yǔ)句用來(lái)創(chuàng)建、刪除或者更改一個(gè)數(shù)據(jù)模式對(duì)象的結(jié)構(gòu)。更改數(shù)據(jù)模式對(duì)象的令也是 DDL。Ø 數(shù)據(jù)語(yǔ)言(Data manipulation language，簡(jiǎn)稱 DML)語(yǔ)句用來(lái)改變表中的數(shù)據(jù)或者數(shù)

3、據(jù)庫(kù)表中的數(shù)據(jù)，但是改變表或其他對(duì)象的結(jié)構(gòu)。Ø 事務(wù)(Transaction control)語(yǔ)句通過(guò)將 SQL 語(yǔ)句組織成邏輯的事務(wù)(可以稱為一個(gè)單元，也可以不成為一個(gè)單元)數(shù)據(jù)的一致性。Ø 會(huì)話設(shè)置。Ø 系統(tǒng)理 rachiving)。(Session control 語(yǔ)句會(huì)更改單個(gè)數(shù)據(jù)庫(kù)連接的設(shè)置，例如，啟動(dòng) SQL 跟蹤(system control)語(yǔ)句會(huì)改變整個(gè)數(shù)據(jù)庫(kù)的設(shè)置，例如，啟動(dòng)或者文檔管類別SQL 語(yǔ)句數(shù)據(jù)定義語(yǔ)言(DDL)DROP、CREATE、ALTER、GRANT、REVOKE數(shù)據(jù)語(yǔ)言(DML)SELECT、INSERT、UPDATE、DE

4、LETE、SET TRANSACTION、EXPLAIN PLAN事務(wù)COMMIT、ROLLBACK、SAVEPOINT會(huì)話ALTER SESSION、SET ROLE系統(tǒng)ALTER SYSTEMSQLCONNECT、DECLARE CURSOR、ALLOCATE134SQL(Embedded SQL)命令用在 Oracle 預(yù)編譯程序中。¾7.2數(shù)據(jù)定義語(yǔ)言(DDL)DDL 語(yǔ)言命令用于執(zhí)行下列任務(wù)：Ø 建立、更改或刪除表、視圖、索引等模式對(duì)象。Ø 對(duì)和的或回收。Ø 建立審計(jì)選擇。Ø 在數(shù)據(jù)字典中增加注釋。當(dāng)用戶執(zhí)行DDL語(yǔ)句時(shí)，在每一條DD

5、L語(yǔ)句執(zhí)行前后，Oracle都將隱式提交當(dāng)前的事務(wù)，理解這一點(diǎn)很重要。因此，如果用戶使用insert命令到數(shù)據(jù)庫(kù)中后，執(zhí)行了一條DDL語(yǔ)句，如create table，此時(shí)來(lái)自insert命令的數(shù)據(jù)將被提交到數(shù)據(jù)庫(kù)而不需要在使用COMMIT來(lái)顯式提交。數(shù)據(jù)定義語(yǔ)言在第九有詳細(xì)介紹。下表中列出了一部分的DDL語(yǔ)句。表 7-2 部分 DDL 語(yǔ)句列表例一，我們創(chuàng)建一個(gè)如圖 3-2 所示的雇員表，并給表指定一個(gè)名字為 employee：CREATE TABLE employee (EMP_ID INTEGER,NAME VARCHAR2(8), ADDRESS VARCHAR2(100);SQL命令

6、功能Alter procedure重編譯過(guò)程Alter table增加表列、重定義表列、更改分配analyze收集數(shù)據(jù)庫(kù)對(duì)象的性能統(tǒng)計(jì)值并送入基于代價(jià)的優(yōu)化器Alter table add constraint在已有的表上增加約束Create procedure創(chuàng)建過(guò)程Create table創(chuàng)建表Create index創(chuàng)建索引Drop index刪除索引Drop table刪除表grant將或授予用戶或其他truncate刪除表中所有行revoke從用戶或數(shù)據(jù)庫(kù)回收135例二，為該表創(chuàng)建唯一索引，索引字段為EMP_ID：CREATE UNIQUE INDEX ibx_empON emplo

7、yee (EMP_ID);例三，在數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)用戶，使該用戶具有基本的RESOURCE和CONNECT系統(tǒng)特權(quán)（有關(guān)系統(tǒng)的概念可以參考相關(guān)的系統(tǒng)管理資料）。CREATE USER user1 IDENTIFIED BY user1;GRANT RESOURCE, CONNECT TO user1;具有 RESOURCE 和 CONNECT 這些注意：一般開發(fā)就夠了。例四，操作一般，對(duì)于一個(gè)對(duì)象而言，可以具有以下對(duì)象：ALTER、DELETE、EXECUTE（只對(duì)于函數(shù)和過(guò)程而言）、INSERT、REFERENCES、SELECT和UPDATE。具體到第十章會(huì)詳細(xì)定義?，F(xiàn)在將表employe

8、e的除了DELETE的為下面兩步：外所有對(duì)象授予用戶scott，可以分GRANT ALL ON employee TO user1;REVOKE DELETE ON employee FROM user1;例五，清空表employee：TRUNCATE TABLE employee;例六，刪除前面創(chuàng)建的索引ibx_emp：DROP INDEX ibx_emp;例七，刪除創(chuàng)建的這個(gè)表employee：DROP TABLE employee;7.3 數(shù)據(jù)語(yǔ)言(DML)數(shù)據(jù)語(yǔ)言用戶對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)進(jìn)行insert、update、delete和select等操作。正如名字所示，用戶可以通過(guò)DML語(yǔ)句來(lái)

9、數(shù)據(jù)庫(kù)中的數(shù)據(jù)內(nèi)容?？傊?，DML語(yǔ)句是用戶用來(lái)136處理數(shù)據(jù)庫(kù)中的數(shù)據(jù)的SQL命令。最常用的 DML 語(yǔ)句是 SELECT、INSERT、UPDATE 和 DELETE。當(dāng)從 SQL *PLUS 中執(zhí)行 SQL 語(yǔ)句時(shí)，結(jié)果會(huì)反饋到屏幕上。對(duì)于 UPDATE、INSERT或 DELETE 語(yǔ)句，SQI*Plus 會(huì)返回被處理的行的數(shù)量。對(duì)于 SELECT 語(yǔ)句，匹配回顯到屏幕上。的行會(huì)7.3.1 INSERT 語(yǔ)句INSERT 語(yǔ)句用于給已有的表或視圖添加。INSERT 語(yǔ)句的基本語(yǔ)法如下：INSERT INTO table_name column_name VALUES (values)|s

10、ubquery;其中：table_name 為要column_name 為要的表名稱；數(shù)據(jù)的字段名稱；Values 是要放到字段中的數(shù)據(jù)。如果 INSERT 語(yǔ)句包含 subquery，則表示從一個(gè)子例如，假設(shè)在用戶 usr1 模式下創(chuàng)建表 employee：來(lái)中數(shù)據(jù)。CREATE TABLE employee (EMP_ID INTEGER, NAME VARCHAR2(8), DEPT_ID INTEGER, SALARY NUMBER(10,2),ADDRESS VARCHAR2(100);ALTER TABLE employeeADD CONSTRAINT pk_emp_ primar

11、y key(EMP_ID);語(yǔ)句：INSERT INTO employee VALUES (1,令狐沖,1,3000,恒山); INSERT INTO employee VALUES (2,岳不群,1,3500,華山);INSERT INTO employee VALUES (3,東方不敗,2,4000,黑木崖);COMMIT;還可以通過(guò) PL/SQL 程序中添加，例如：DECLAREv_id INTEGER; v_name VARCHAR2(8); v_dept INTEGER; v_sal NUMBER(10,2);v_addr VARCHAR2(100);137BEGIN-直接使用DML

12、INSERT INTO employee VALUES (4,任我行,2,4500,黑木崖); COMMIT;-使用變量數(shù)據(jù)v_id:=5;v_name:=v_dept:=3; v_sal:=2000;v_addr:=福州;INSERT INTO employee VALUES(v_id,v_name,v_dept,v_sal,v_addr); COMMIT;END;/7.3.2 SELECT 語(yǔ)句SELECT 語(yǔ)句的作用是從數(shù)據(jù)庫(kù)中獲取數(shù)據(jù)。同時(shí)和可以將返回的進(jìn)行排序、分組、進(jìn)行多表連接甚至將結(jié)果交給一個(gè)INSERT語(yǔ)句到另外一個(gè)表中。SELECT 語(yǔ)句的基本語(yǔ)法為：SELECT*|colu

13、mn, INTO variable|collection FROM table_name,WHERE where_condition GROUP BY group_expr CONNECT BY conn_condition ORDER BY order_expr DESC這里，column,為一列或多列的列名列表。這些列將顯示在結(jié)果中。INTO variable|collection，該選項(xiàng)一般出現(xiàn)在 PL/SQL 程序塊中。表示將結(jié)果返回給變量或者集合。Variable 為結(jié)果返回給哪個(gè)變量，collection 表示結(jié)果要返回到的集合。table_name,為從哪個(gè)表中，可以從多個(gè)表中進(jìn)

14、行。where_condition 為group_expr 為分組表回單行的總計(jì)信息。的條件，的結(jié)果為那些滿足條件 where_condition 的。，即返回的按 group_expr 的值進(jìn)行分組，對(duì)于每一組將返conn_condition 指定層次結(jié)構(gòu)的雙親行與子行之間的。order_expr 為指定返回行的排序順序，DESC 表示降序排列。下面介紹 SELECT 語(yǔ)句的幾個(gè)范例。例一，表 employee 的：column name format a10;138column addr format a30;SELECT emp_id,name,address addr from emp

15、loyee;上述結(jié)果如下：EMP_ID NAMEADDR1 令狐沖2 岳不群3 東方不敗4 任我行5恒山華山黑木崖黑木崖福州例二，分組各個(gè)部門的工資總數(shù)和平均數(shù)，結(jié)果按照 dept_id 降序排列：SELECT dept_id,SUM(salary),AVG(salary) FROM employee GROUP BY dept_idORDER BY dept_id DESC;執(zhí)行結(jié)果如下：DEPT_ID SUM(SALARY) AVG(SALARY)321200085006500200042503250例三，多表連接。還需要?jiǎng)?chuàng)建一個(gè)表 department：CREATE TABLE depa

16、rtment (dept_id INTEGER, dept_name VARCHAR2(20); ALTER TABLE departmentADD CONSTRAINT pk_dept PRIMARY KEY (dept_id);然后給表添加：INSERT INTO department VALUES(1,開發(fā)部); INSERT INTO department VALUES(2,財(cái)務(wù)部);INSERT INTO department VALUES(3,測(cè)試部);139COMMIT;如果我們想要檢索 employee 表的所有的部門名稱：column name format a10;SELE

17、CT emp_id,name,dept_name from employee e, department d WHERE e.dept_id=d.dept_id;結(jié)果如下：EMP_ID NAMEDEPT_NAME1 令狐沖2 岳不群3 東方不敗4 任我行5開發(fā)部開發(fā)部財(cái)務(wù)部財(cái)務(wù)部測(cè)試部例四，通過(guò)中數(shù)據(jù)。假設(shè)我們創(chuàng)建了另外一個(gè)表：CREATE TABLE programmer (emp_id INTEGER, emp_name VARCHAR2(8),Address VARCHAR(100);可以采用如下語(yǔ)句進(jìn)行操作：INSERT INTO programmerSELECT emp_id,nam

18、e,address from employee e, department d WHERE e.dept_id=d.dept_id and d.dept_name=開發(fā)部;SELECT 語(yǔ)句可以使用在 PL/SQL 程序塊中，但其目的是將或集合。這時(shí)需要在 SELECT 語(yǔ)句中加 INTO 選項(xiàng)。例如：的結(jié)果賦值給某個(gè)變量DECLAREV_emp_name VARCHAR(8); BEGINSELECT name INTO v_emp_name FROM employeeWHERE emp_id=1;140DBMS_OUTPUT.PUT_LINE(v_emp_name); END;/7.3.3

19、 UPDATE 語(yǔ)句UPDATE 語(yǔ)句使用戶可以修改表中已經(jīng)有的UPDATE table_nameSET (column_name=value) WHERE condition;其中 table_name 為要更改的表名；的值。UPDATE 語(yǔ)句的基本語(yǔ)法如下：column_name 為要更新的字段；value 表示將要更新的字段更改為 value 值；WHERE 子句標(biāo)識(shí)哪些需要更改，condition 表明滿足條件 condition 的才會(huì)被更改，其它被更改。例如我們修改 department 表，讓其部門號(hào)為 1 的部門名稱更改為軟件開發(fā)部：UPDATE departmentSET d

20、ept_name=軟件開發(fā)部 WHERE dept_id=1;COMMIT;也可以通過(guò) PL/SQL 中調(diào)用PDATE 語(yǔ)句來(lái)完成：BEGINUPDATE departmentSET dept_name=軟件開發(fā)部 WHERE dept_id=1;COMMIT; END;/7.3.4 DELETE 語(yǔ)句DELETE 語(yǔ)句使用戶可以刪除表中的DELETE from table_name WHERE condition;其中 table_name 為要?jiǎng)h除的表名；。DELETE 語(yǔ)句的基本語(yǔ)法如下：WHERE 子句標(biāo)識(shí)哪些要被刪除，condition 表明滿足條件 condition 的才會(huì)被刪除

21、，其它被刪除。例如我們刪除 employee 表中所有 dept_id=2 的：DELETE from employee141WHERE dept_id=2;COMMIT;與其它的 DML 語(yǔ)句一樣，該語(yǔ)句可以在 PL/SQL 塊中實(shí)現(xiàn)：BEGINDELETE from employee WHERE dept_id=2; COMMIT;END;/7.3.5 LOCK TABLE 命令假如用戶甲正在對(duì) A 表進(jìn)行操作要更新或其中的數(shù)據(jù)，這時(shí)用戶乙卻要?jiǎng)h除 A 表。如果甲的操作還沒(méi)有完成，乙已經(jīng)將 A 表刪除了。這樣的后果將是破壞性的。這時(shí) ORACLE提供了鎖定，只有當(dāng)用戶甲完成對(duì) A 表的操作

22、 ORACLE 才會(huì)解除鎖定讓用戶乙刪除 A 表。ORACLE 提供了多種鎖定，其中最常用的鎖定為：表鎖定和鎖定。表鎖定是指當(dāng)一個(gè)事務(wù)一個(gè)表的時(shí)候，對(duì)這個(gè)表實(shí)行數(shù)據(jù)鎖定以確保當(dāng)前的事務(wù)可以數(shù)據(jù)，并防止別的用戶或事務(wù)同時(shí)對(duì)該表進(jìn)行的進(jìn)行鎖定。時(shí)造成，用于保護(hù)表中的數(shù)據(jù)。而鎖定是對(duì)表一些 DML 語(yǔ)句如 INSERT、UPDATE、DELETE 以及帶有 UPDATE 的 SELECT 語(yǔ)句會(huì)自動(dòng)對(duì)表進(jìn)行鎖定。在不影響完整性的前提下 ORACLE 自動(dòng)盡量以最低的模式進(jìn)行鎖定。也可以使用 LOCK TABLE 命令顯式地進(jìn)行鎖定并覆蓋默認(rèn)的鎖定機(jī)制。LOCK TABLE命令的語(yǔ)法如下：LOCK T

23、ABLE table_name IN lockmode MODENOWAIT其中:tablename 是你要鎖定的特定的表或的視圖名稱. Lockmode 是你想要設(shè)定的鎖定模式：1. ROW SHARE這是一種限制性最小的鎖定，它在鎖定表的同時(shí)別的事務(wù)并發(fā)任何事務(wù)對(duì)同、修改、刪除和鎖定（LOCK TABLE）。它不。的對(duì)同一個(gè)表執(zhí)行一個(gè)表進(jìn)行的寫2. ROW EXCLUSIVE這種鎖定發(fā)生在當(dāng)一個(gè)表的多條被更新時(shí)。它在作用上基本和 ROW SHARE 等價(jià)。它也別的事務(wù)對(duì)同一個(gè)表執(zhí)行、修改、刪除和鎖定。但它別的事務(wù)不能對(duì)同一個(gè)表進(jìn)行 SHARE LOCK、EXCLUSIVE 和 ROW SH

24、ARE 鎖定。3. SHARE LOCK這種鎖定只，執(zhí)行 SELECT FOR UPDATE 更新或、修改和刪除工作。如果兩個(gè)事務(wù)都別的事務(wù)加 SHARE LOCK 鎖。其防止任何事務(wù)對(duì)同一個(gè)表的加了 SHARE LOCK 鎖，則都不可以修改，除非改為 SHARE ROW EXCLUSIVE。1424. SHARE ROW EXCLUSIVE與 SHARE LOCK 類似只別的事務(wù)，執(zhí)行SELECT FOR UPDATE 更新，但不SELECT 語(yǔ)句中。加任何鎖。此種鎖定一般只用于帶 UPDATE 的5. EXCLUSIVE該事務(wù)是以獨(dú)占的方式寫一個(gè)表。此間只別的用戶和該表，而不進(jìn)行任何的、修

25、改和刪除工作。NOWAIT 該項(xiàng)是可選的.如果你指定它,則當(dāng)我們?cè)噲D鎖定一個(gè)表的時(shí)候,如果該表已被別的事務(wù)鎖定則立即把返還給事務(wù);如果我們不用 NOWAIT 選項(xiàng)則會(huì)一直在等待,只到該表的鎖定被解除,然后再鎖定該表,開始執(zhí)行事務(wù)。例如：LOCK TABLE TDZ,TDZJSJLIN EXLCUSIVE MODE NOWAIT;7.4 事務(wù)命令事務(wù)（Transaction）可以被看作一個(gè)工作邏輯單元，由一系列的 SQL 語(yǔ)句組成，這些語(yǔ)句要么全部執(zhí)行要么全部不執(zhí)行，如果其中有任一條 SQL 語(yǔ)句執(zhí)行失敗，則全部語(yǔ)句都執(zhí)行，這樣就數(shù)據(jù)的一致性和完整性。一個(gè)簡(jiǎn)單的例子：如果我們?cè)趯㈠X（amount

26、）從一個(gè)帳戶（ACCOUNTA）匯到另一個(gè)帳戶（ACCOUNTB），假設(shè)表為（TACCOUNT），則 SQL 語(yǔ)句可能如下：UPDATE TACCOUNT SET money=money-amount WHERE account_name=ACCOUNTA;UPDATE TACCOUNT SET money=money+amountWHERE account_name=ACCOUNTB;這時(shí)如果第一條語(yǔ)句執(zhí)行，帳戶（ACCOUNTA）的錢已經(jīng)匯出，但是由于某中異常第二條語(yǔ)句沒(méi)有執(zhí)行，那么帳戶（ACCOUNTB）就收到從帳戶（ACCOUNTA）匯出的錢。這時(shí)就造成了數(shù)據(jù)的不一致，事務(wù)處理就可以解

27、決這個(gè)問(wèn)題，我們只需要將上面的兩條語(yǔ)句合并成一個(gè)事務(wù)處理來(lái)執(zhí)行，兩條語(yǔ)句要么都執(zhí)行，要么都不執(zhí)行，這樣就數(shù)據(jù)的一致性。什么時(shí)候開始一個(gè)事務(wù)？一般事務(wù)開始有以下情況：Ø 當(dāng)?shù)谝粭l SQL 開始執(zhí)行時(shí)Ø 是前一個(gè)事務(wù)結(jié)束以后的第一條 SQL 語(yǔ)句Ø 顯式的調(diào)用 SET TRANSACTION 來(lái)開始一個(gè)事務(wù)。般是使用 COMMIT（提交）或 ROLLBACK（事務(wù)的）來(lái)標(biāo)志的。詳細(xì)的事務(wù)處理內(nèi)容請(qǐng)參考相關(guān)的 PL/SQL 書籍，這里只是簡(jiǎn)單地介紹這幾個(gè)命令。注意：LOCK TABLE 不僅用于鎖定表，也可以用來(lái)鎖定視圖。當(dāng) LOCK TABLE 用來(lái)鎖定視圖的時(shí)候?qū)嶋H

28、上是鎖定了組成視圖的基礎(chǔ)表。例如一個(gè)視圖 VIEW1 是由 TABLE1 和TABLE2，組成則鎖定 VIEW1 實(shí)際上是鎖定 TABLE1 和 TABLE2 兩個(gè)表，其 lockmode選項(xiàng)同時(shí)使用在 TABLE1 和 TABLE2 上。1437.4.1 Set TransactionSet Transaction 命令顯式地開始一個(gè)事務(wù)，其語(yǔ)法如下：set Transaction Read onlyWrite onlyUse Rollback segment段名Read only：事務(wù)級(jí)讀一致性：事務(wù)處理過(guò)程中只能夠夠看到事務(wù)處理前提交的事務(wù)。Read Write：語(yǔ)句級(jí)讀一致性如：，不能

29、夠其它操作。只能Set Transaction Read only; Select count (*) from ship;UPDATE departmentSET dept_name=軟件開發(fā)部 WHERE dept_id=1;Commit;7.4.2 Commit 與 Rollback 命令COMMIT 語(yǔ)句為事務(wù)中的提交語(yǔ)句，它將緩沖區(qū)的內(nèi)容寫入數(shù)據(jù)庫(kù)。由于我們使用DML 中的 INSERT、UPDATE、DELETE 語(yǔ)句更改表的時(shí)候，結(jié)果只是反映在緩沖區(qū)中，要最終把結(jié)果寫入數(shù)據(jù)庫(kù)，則需要運(yùn)行這個(gè) COMMIT 語(yǔ)句。Rollback 命令則正好相反。如：INSERT INTO emp

30、loyee VALUES (6,ROLLBACK;,4,3800,嵩山);INSERT INTO employee VALUES (7,莫大先生,5,3800,衡山);Commit;7.4.3 Save point(保存點(diǎn))在實(shí)際的工作中，有的時(shí)候我們可能不希望一個(gè)很大的事務(wù)，而是將一個(gè)大的事務(wù)分成很多小塊，每一個(gè)小塊作為一個(gè)保存點(diǎn)，這樣當(dāng)我們?cè)趫?zhí)行程序的時(shí)候，如果發(fā)生錯(cuò)誤，也只是到最近或指定的保存點(diǎn)。這在大量的多步更新時(shí)很有幫助。當(dāng)程序發(fā)生錯(cuò)誤時(shí)，ORACLE 只是到最近的保存點(diǎn)，而不是撤消整個(gè)事務(wù)，這樣就不用再次處理保存點(diǎn)以前的語(yǔ)句，減少不必要的數(shù)據(jù)庫(kù)開銷。SAVEPOINT 的語(yǔ)法一般是

31、這樣：SAVEPOINT pointname;144如果我們定義了一個(gè)保存點(diǎn)以后，就可以用下面的語(yǔ)法將一個(gè)事務(wù)撤消到該保存點(diǎn)：ROLLBACK TO SAVEPOINT pointname;如：BEGINSavepoint sp1 ;update employee set salary=5000 Where name=令狐沖; Savepoint sp2 ;update employee set salary=3000 Where name=岳不群; Rollback to savepoint sp2;Commit;END;/7.5會(huì)話命令與系統(tǒng)命令命令有 ALTER會(huì)話命令可動(dòng)態(tài)地一個(gè)用戶會(huì)

32、話的特性。主要的會(huì)話SESSION 命令和 SET ROLE 命令。7.5.1 Alter Session 命令A(yù)LTER SESSION 命令的作用可按下列方式更改當(dāng)前會(huì)話：Ø 使 SQL 跟蹤功能啟用或停用。Ø 改變 NLS 參數(shù)值。Ø 改變會(huì)話的缺省標(biāo)號(hào)格式。Ø 關(guān)閉一數(shù)據(jù)庫(kù)鏈。個(gè)忠告到數(shù)據(jù)庫(kù)，對(duì)一個(gè)懸而末決的分布式事務(wù)進(jìn)行處理。¾¾或在過(guò)程或函數(shù)中發(fā)出 COMMIT 和 ROLLBACK 語(yǔ)句。Ø 修改基于開銷的優(yōu)化方法的目標(biāo)。操作者為了實(shí)施使 SQL 跟蹤功能啟用或停用，或改變?nèi)笔?biāo)號(hào)格式執(zhí)行該命令，則必須具有 A

33、LTER SESSION 系統(tǒng)語(yǔ)法：。執(zhí)行其它操作不需要任何。ALTER SESSION SET paraname=value其中，paraname 指的是會(huì)話的參數(shù)變量名稱；value 指的是會(huì)話的參數(shù)變量要設(shè)置的值。Oracle 系統(tǒng)的初始化參數(shù)有些是在數(shù)據(jù)庫(kù)啟動(dòng)后不能夠動(dòng)態(tài)修改的，有些則是可以動(dòng)態(tài)進(jìn)行修改的。一般可以動(dòng)態(tài)修改的初始化參數(shù)是通過(guò)會(huì)話命令的 ALTER SESSION 和系統(tǒng)命令的 ALTER SYSTEM 進(jìn)行修改的。究竟哪些初始化參數(shù)可以進(jìn)行修改，哪些不能夠進(jìn)行修改請(qǐng)參考相關(guān)的初始化參數(shù)的資料或者系統(tǒng)管理員手冊(cè)。例一，啟用 SQL 跟蹤功能：ALTER SESSION S

34、ET SQL_TRACE=TRUE;145例二，修改 NLS 參數(shù)，設(shè)置語(yǔ)言為法語(yǔ)，日期格式Y(jié)YYY MM DD HH24:MI:SS。ALTER SESSION SET NLS_LANGUAGE=French;ALTER SESSION SET NLS_DATE_FORMAT=YYYY MM DD HH24:MI:SS;例三，改變優(yōu)化方法和目標(biāo)來(lái)優(yōu)化 SQL 語(yǔ)句，這些優(yōu)化方法有：FIRST_ROW：最快響應(yīng)時(shí)間； ALL_ROW：最大吞吐量； RULE：基于規(guī)則。語(yǔ)句如下：ALTER SESSION SET OPTIMIZER_GOAL=FIRST_ROW此外 ALTER SESSION

35、還可以關(guān)閉數(shù)據(jù)庫(kù)鏈，提出事務(wù)忠告等。7.5.2 系統(tǒng)系統(tǒng)命令命令動(dòng)態(tài)地管理一個(gè) ORACLE 數(shù)據(jù)庫(kù)實(shí)例的特性，該命令為 ALTER SYSTEM命令。其作用可以在下列方面動(dòng)態(tài)地更改 ORACLE 數(shù)據(jù)庫(kù)實(shí)例：使限制啟用或停用。¾¾¾¾¾¾¾¾¾¾為共享服務(wù)器結(jié)構(gòu)管理共享服務(wù)器進(jìn)程或調(diào)度進(jìn)程(dispatcher)。顯式地轉(zhuǎn)換日志文件組。顯式地執(zhí)行一個(gè)檢查點(diǎn)。檢驗(yàn)對(duì)數(shù)據(jù)文件的存取。限制用戶對(duì)ORACLE 登錄，僅使具有RESTRICTED SESSION 系統(tǒng)在單進(jìn)程環(huán)境下使分布式恢復(fù)啟用。手工

36、地歸檔日志文件組或者使自動(dòng)歸檔啟用成效不能。清除 SGA 的共享他中的全部數(shù)據(jù)。中止一個(gè)會(huì)話。的用戶登錄。ALTER SYSTEM 命令語(yǔ)法如下： 語(yǔ)法：ALTER SYSTEMSET paraname=value|ENABLE|DISABLERESTRICTED SESSION|FLUSH SHARED_POOL|SWITCH LOGFILE|ARCHIVE LOG archive_log_clause|KILL SESSION integer1,integer2其中，paraname 指的是系統(tǒng)動(dòng)態(tài)參數(shù)變量名稱；value 指的是系統(tǒng)動(dòng)態(tài)變量要設(shè)置的值。這里的動(dòng)態(tài)參數(shù)是初始化參數(shù)中可以利用

37、 ALTER SYSTEM 命令在數(shù)據(jù)庫(kù)啟動(dòng)后進(jìn)行動(dòng)態(tài)更改的參數(shù)。ENABLE|DISABLERESTRICTED SESSION：確定是否限制用戶對(duì) ORACLE 登錄，146ENABLE 僅使具有RESTRICTED SESSION 系統(tǒng)的用戶登錄。DISABLE 則取消這個(gè)限制。FLUSH SHARED_POOL：清除 SGA 共享池。SWITCH LOGFILE：強(qiáng)行日志轉(zhuǎn)換。ARCHIVE LOG archive_log_clause：手工地歸檔日志文件組或者使自動(dòng)歸檔啟用成效不能。KILL SESSION integer1,integer2：殺死會(huì)話，其中 integer1 為會(huì)話

38、的 SID，integer2 為會(huì)話的序列號(hào)。例一，清除 SGA 共享池：ALTER SYSTEM FLUSH SHARED_POOL;例二，設(shè)置共享服務(wù)器進(jìn)程數(shù)量最小為 25：Alter system set MTS_servers =25例三，動(dòng)態(tài)調(diào)整調(diào)度進(jìn)程數(shù)，對(duì) TCP/IP協(xié)議量 5 對(duì) DECNET 量 10：Atler system set MTS_Dispatchers =TCP,5MTS_Dispatchers =DECNET,10例四，動(dòng)態(tài)設(shè)置使用限制：Alter system Set license_Max_Sessions=64; Alter system Set Li

39、cense_Sessions_Wanning=54;例五，終止會(huì)話，首先查找會(huì)話的 SID 以及序號(hào)，然后再終止會(huì)話：select sid, serial#, username from V$session;alter system kill session 13,8;-sid=13, serial=87.5.3 SET ROLE 命令SET ROLE 命令作用為使對(duì)當(dāng)前會(huì)話啟用或停用。在用戶登錄時(shí)，ORACLE 通過(guò)該用戶的缺省為用戶建立缺省域。該缺省域包含所有顯式地授給用戶的全部以及該用戶的缺省的域中的全部。用戶可執(zhí)行缺省域中的所的任何操作。但不能夠使用非缺省的。在會(huì)話期間，利用 SET

40、ROLE 命令改變用戶的特權(quán)域，可改變對(duì)當(dāng)前會(huì)話啟用的，可在一會(huì)話期間改變啟用多次。但操作者執(zhí)行該命令時(shí)該必須已被。SET ROLE 命令還將的第十章 數(shù)據(jù)庫(kù)安全管理中介紹。SET ROLE 命令基本語(yǔ)法如下：SET ROLE role IDENTIFIED BY passwordrole IDENTIFIED BY password,.|ALL EXCEPT role|NONE其中：role：使其對(duì)當(dāng)前會(huì)話啟用的名，任何沒(méi)有別出的對(duì)當(dāng)前會(huì)話是為停用。啟用時(shí)必須提供口令。Password：為這個(gè)ALL EXCEPT：使的口令，如果給用戶的全部有口令，使該在當(dāng)前會(huì)話中啟用，除了那些在 EXCEP

41、T 子句中列出的。在 EXCEPT 子句中列出的必須是直接給用戶的，不能是間接的。注意不能使用這種方式使具有口令的啟用。147NONE：使當(dāng)前會(huì)話中全部停用。要改變用戶的缺省，使用 ALTER USER 命令實(shí)現(xiàn)。例一，使當(dāng)前用戶可以使用其被授予的但不是缺省的role1，其中令 tiger 標(biāo)識(shí)。SET ROLE role1 IDENTIFIED BY tiger;role1 使用口例二，使當(dāng)前用戶可以使用除了 BANKERSET ROLE EXCEPT BANKER;外的所有：7.6 PL/SQL 基礎(chǔ)7.6.1 PL/SQL 塊結(jié)構(gòu)在 PL/SQL 中，所有的塊都包含有下面的三個(gè)基本部分：

42、申明部分（declarative section）、執(zhí)行部分（executable section）和異常處理部分（exception section）。其中只有執(zhí)行部分是必須的，其它部分都是可選的。下面給出含有三個(gè)基本部分的一個(gè)塊的例子：DECLARE/* 申明部分。所有的變量、游標(biāo)和類型都在這里申明 */* 申明一個(gè)NUMBER型的變量v_ncolumn，并賦予初始值6 */ v_ncolumn NUMBER:=6;v_vcolumn VARCHAR2(30); -申明一個(gè)VARCHAR2型的變量v_vcolumn BEGIN/* 執(zhí)行部分 */SELECT vcolumn INTO v_

43、vcolumn FROM test_table WHERE ncolumn=v_ncolumn;DBMS_OUTPUT.PUT_LINE('The Message''s '|v_vcolumn);/* 異常處理部分 */ EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('沒(méi)有找到匹配的數(shù)據(jù)');END;從上例中我們可以看出，在一個(gè) PL/SQL 塊中所要使用的變量、類型和游標(biāo)等都是在聲明部分進(jìn)行。部分一般都是以關(guān)鍵字 DECLARE 開始的。部分也可以本地過(guò)程和函數(shù)，但是這些過(guò)程和函數(shù)只能

44、在該塊中使用。關(guān)鍵字 DECLARE 在我們創(chuàng)建過(guò)程或函數(shù)的時(shí)候是不需要的，我們可以直接所需的變量和類型等。但是在觸發(fā)器中，如果要申明變量的話，DECLARE 則是必須要有的。部分是可選的，如果在 PL/SQL 塊中我們不需要變量、類型和游標(biāo)等，則148部分可以省略。如果沒(méi)有申明部分，則塊就是以關(guān)鍵字 BEGIN 開始。在塊的執(zhí)行部分，我們可以使用 SQL 語(yǔ)句和/或過(guò)程性語(yǔ)句來(lái)完成和處理特定的工作。在塊的三個(gè)基本結(jié)構(gòu)中，只有執(zhí)行部分是不可缺少的，執(zhí)行部分一般是以 BEGIN 開頭，而以END 結(jié)束。如上例中我們用一條 SQL 語(yǔ)句從數(shù)據(jù)庫(kù)表 test_table 中取出復(fù)合條件的 vcolu

45、mn， 然后用 DBMS_OUTPUT 方法在屏幕上打印出來(lái)。異常處理部分在一個(gè) PL/SQL 塊中是可選的，在異常處理部分，我們可以檢查及處理在塊中可能發(fā)生的錯(cuò)誤。如果我們?cè)谝粋€(gè)塊中不需要進(jìn)行異常處理的話，那么異常處理部分可以省略。在塊中，異常處理部分是以 EXCEPTION 關(guān)鍵字開始的。PL/SQL 的異常在 7.8 節(jié)將詳細(xì)介紹。綜上所述。我們對(duì) PL/SQL 塊有了一個(gè)具體的概念。知道 部分是由關(guān)鍵字 DECLARE開始的，而執(zhí)行部分是由 BEGIN 來(lái)界定的，如果一個(gè)塊中沒(méi)有部分則這個(gè)塊就直接由關(guān)鍵字 BEGIN 開始。并且我們知道在子程序這樣的塊中象過(guò)程或函數(shù)中部分是不需要關(guān)鍵字

46、 DECLARE 的。還了解到異常部分是由關(guān)鍵字 EXCEPTION 開始的，并且一個(gè)塊中如果沒(méi)有異常處理的話，則關(guān)鍵字 EXCEPTION 也就被省略。關(guān)鍵字 END 用來(lái)標(biāo)志當(dāng)前塊的結(jié)束， 同時(shí)要注意的是：在 END 關(guān)鍵字后面一定要加上一個(gè)分號(hào)（；），這是必須的。這樣一個(gè)塊的框架結(jié)構(gòu)應(yīng)該是這樣：DECLARE/* 這里是申明部分 */ BEGIN/* 這里是執(zhí)行部分 */ EXCEPTION/* 這里是異常處理部分 */END; -在這里分號(hào)是必須的如果一個(gè)BEGIN塊中沒(méi)有部分，則是這樣：/* 這里是執(zhí)行部分 */ EXCEPTION/* 這里是異常處理部分 */END; -在這里分號(hào)

47、是必須的7.6.2 PL/SQL 變量類型每一個(gè)常量、變量和參數(shù)數(shù)據(jù)類型（或類型）。它用來(lái)指定一定的格式、約束和有效的范圍。PL/SQL 提供了多種的的數(shù)據(jù)類型。例如：整數(shù)型、日期型、集合型、型和 LOB 類型等。另外，PL/SQL 可以讓你定義中列處了所有的 PL/SQL 類型（圖 7-1）：、字符型、的子類型。下圖149圖 7-1 PL/SQL 變量類型由上圖可以看到（composite type）、PL/SQL 類型分為四類：標(biāo)量類型（scalar type）、復(fù)合類型類型（reference type）和 LOB 類型。標(biāo)量類型不含有任何組件；復(fù)合類型可以包含有很多的組件；對(duì)象。類型是

48、指向另一個(gè)類型的指針；LOB 類型用來(lái)大型的在本章中，我們只介紹標(biāo)量類型。一數(shù)字型數(shù)字類型可以讓我們數(shù)字型數(shù)據(jù)（整形、實(shí)數(shù)型和浮點(diǎn)型）。它有三種基本類型：BINARY_INTEGER、NUMBER 和 PLS_INTEGER。1. NUMBERLOB 型BFILE BLOB CLOB NCLOB型REF CURSORREF object_type復(fù)合型RECORD TABLE VARRAY標(biāo)量類型型BOOLEAN日期型DATEINTERVAL DAY TO SECOND INTERVAL YEAR TO MONTH TIMESTAMPTIMESTAMP WITH LOCAL TIME ZONE

49、 TIMESTAMP WITH TIME ZONE字符型CHAR CHARACTER LONGLONG RAW NCHAR NVARCHAR2 RAW ROWID STRING UROWID VARCHAR VARCHAR2數(shù)字型BINARY_INTEGER DECDECIMALDOUBLE PRECISION FLOATINT INTEGER NATURAL NATURALN NUMBER NUMERICPLS_INTEGER POSITOVE POSITIVEN REAL SIGNTYPESMALLINT150NUMBER 數(shù)據(jù)類型用來(lái)整數(shù)和符點(diǎn)數(shù)。它的范圍是 1E-130 . 10E12

50、5。我們可以用數(shù)字來(lái)指定精度和刻度范圍。精度是指數(shù)值中所有數(shù)字的個(gè)數(shù)，刻度是指小數(shù)點(diǎn)右邊的數(shù)字的個(gè)數(shù)，如果給定的刻度范圍是負(fù)數(shù)的話就由小數(shù)點(diǎn)開始向左邊計(jì)算數(shù)字的個(gè)數(shù)。其語(yǔ)法就象下面這樣：NUMBER(precision,scale)其中(precision,scale)是可選的，但是如果是固定刻度范圍的數(shù)的話，則(precision,scale)是必須的。如果申明的是一個(gè)整數(shù)型的話，則可以這樣：NUMBER(precision)。它和NUMBER(precision,0)是等價(jià)的?？潭扔脕?lái)確定在什么地方進(jìn)行舍入，它的范圍是從-84 到 127。如果被指派的值超過(guò)了指定的刻度范圍，則值會(huì)按照刻度

51、指定的位數(shù)進(jìn)行四舍五入，如果給的刻度是負(fù)數(shù)的話則值會(huì)按照刻度指定的位數(shù)往左進(jìn)行四舍五入。如一個(gè) NUMBER(8,3)類型的值為 12345.678。如果轉(zhuǎn)換為 NUMBER(4,-2)類型，則值為 12300。PL/SQL 給 NUMBER 類型預(yù)定義了下面幾類型：DEC、DECIMAL、DOUBLE、RECISION、FLOAT、INTEGER、INT、NUMERIC、REAL、SMALLINT2. BINARY_INTEGER我們可以用 BINARY_INTEGER 來(lái)一個(gè)有符號(hào)的整形值。它的大小范圍是-2*31 .2*31（-2 的 31 次方到+2 的 31 次方）。和 PLS_IN

52、TEGER 值一樣，BINARY_INTEGER 值所的空間比 NUMBER 值需要的空間少，然而，大多數(shù)情況下 BINARY_INTEGER需操作比 PLS_INTEGER 操作慢。子類型來(lái)源于數(shù)據(jù)類型，是數(shù)據(jù)類型的子集代表著數(shù)據(jù)類型的特殊情況，一般其取值范圍較父數(shù)據(jù)類型為小。為了方便起見(jiàn)，PL/SQL 預(yù)定義了下面幾個(gè) BINARY_INTEGER 的子集： NATURAL、NATURALN、POSITIVE、POSITIVEN、SIGNTYPE。3.PLS_INTEGER我們可以使用 PLS_INTEGER 來(lái)一個(gè)有符號(hào)的整型值。和 BINARY_INTEGER一樣它的精度范圍是-2*3

53、1 . 2*31（-2 的 31 次方到+2 的 31 次方）。和 NUMBER 比較起來(lái)，PL_INTEGER 占有較少的空間，并且 PLS_INTEGER 可以直接進(jìn)行算術(shù)運(yùn)算（在 NUMBER 上不能直接進(jìn)行算術(shù)運(yùn)算，如果要這樣的話 NUMBER 首先必須被轉(zhuǎn)變成二進(jìn)制），所以在進(jìn)行算法的時(shí)候他比 NUMBER 和 BINARY_INTEGER 操作起來(lái)要塊一點(diǎn)。所以在它的精度范圍內(nèi)使用 PLS_INTEGER 進(jìn)行所有的運(yùn)算操作是最合適的。二字符型字符類型可以讓我們1.CHAR文字型數(shù)據(jù)，描述性詞組和文本，并且可以讓我們操作字符串。我們可以用 CHAR 數(shù)據(jù)類型來(lái)固定長(zhǎng)度的字符數(shù)據(jù)。具

54、體有多少數(shù)據(jù)被要依賴于數(shù)據(jù)庫(kù)的字符集。CHAR 有一個(gè)可選的參數(shù)讓你來(lái)指定字符的長(zhǎng)度，最大有 32767 個(gè)字節(jié)。其語(yǔ)法就象下面這樣：CHAR(um_size CHAR | BYTE )151其中um_size 是指定的字符長(zhǎng)度。CHAR 和 BYTE 為可選項(xiàng)，用來(lái)指定um_size 是以字節(jié)（BYTE）為還是以字符（CHAR）為，一個(gè)字符可以包括um_size 必須要用 1.32767 之間的整數(shù)一個(gè)或多個(gè)的字節(jié)，這取決于你的字符集設(shè)置。來(lái)指定。如果沒(méi)有指定最大值，它缺省的是。盡管 PL/SQL 字符變量可以很長(zhǎng)，但是數(shù)據(jù)庫(kù)中 CHAR 類型的長(zhǎng)度只有 2000 個(gè)字節(jié)。所以我們不能將大于 2000 個(gè)字節(jié)的字符數(shù)據(jù)庫(kù)的字符列中。但是我們可以將任何的CHAR(n)值到一個(gè) LONG 型的數(shù)據(jù)庫(kù)列中，因?yàn)橐粋€(gè) LONG 數(shù)據(jù)庫(kù)列的最大長(zhǎng)度可以有2*31（2 的 31 次方）個(gè)字節(jié)。CHAR 有一個(gè)子類型為：CHARACTER。它的意義和 CHAR 完全一樣