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

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

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

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

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

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

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

7、yee (EMP_ID);例三，在數(shù)據(jù)庫中創(chuàng)建一個用戶，使該用戶具有基本的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ā)就夠了。例四，操作一般，對于一個對象而言，可以具有以下對象：ALTER、DELETE、EXECUTE（只對于函數(shù)和過程而言）、INSERT、REFERENCES、SELECT和UPDATE。具體到第十章會詳細定義?，F(xiàn)在將表employe

8、e的除了DELETE的為下面兩步：外所有對象授予用戶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)建的這個表employee：DROP TABLE employee;7.3 數(shù)據(jù)語言(DML)數(shù)據(jù)語言用戶對數(shù)據(jù)庫中的數(shù)據(jù)進行insert、update、delete和select等操作。正如名字所示，用戶可以通過DML語句來

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

10、ubquery;其中：table_name 為要column_name 為要的表名稱；數(shù)據(jù)的字段名稱；Values 是要放到字段中的數(shù)據(jù)。如果 INSERT 語句包含 subquery，則表示從一個子例如，假設(shè)在用戶 usr1 模式下創(chuàng)建表 employee：來中數(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);語句：INSERT INTO employee VALUES (1,令狐沖,1,3000,恒山); INSERT INTO employee VALUES (2,岳不群,1,3500,華山);INSERT INTO employee VALUES (3,東方不敗,2,4000,黑木崖);COMMIT;還可以通過 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 語句SELECT 語句的作用是從數(shù)據(jù)庫中獲取數(shù)據(jù)。同時和可以將返回的進行排序、分組、進行多表連接甚至將結(jié)果交給一個INSERT語句到另外一個表中。SELECT 語句的基本語法為：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àn)在 PL/SQL 程序塊中。表示將結(jié)果返回給變量或者集合。Variable 為結(jié)果返回給哪個變量，collection 表示結(jié)果要返回到的集合。table_name,為從哪個表中，可以從多個表中進

14、行。where_condition 為group_expr 為分組表回單行的總計信息。的條件，的結(jié)果為那些滿足條件 where_condition 的。，即返回的按 group_expr 的值進行分組，對于每一組將返conn_condition 指定層次結(jié)構(gòu)的雙親行與子行之間的。order_expr 為指定返回行的排序順序，DESC 表示降序排列。下面介紹 SELECT 語句的幾個范例。例一，表 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恒山華山黑木崖黑木崖福州例二，分組各個部門的工資總數(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例三，多表連接。還需要創(chuàng)建一個表 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,財務(wù)部);INSERT INTO department VALUES(3,測試部);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ā)部財務(wù)部財務(wù)部測試部例四，通過中數(shù)據(jù)。假設(shè)我們創(chuàng)建了另外一個表：CREATE TABLE programmer (emp_id INTEGER, emp_name VARCHAR2(8),Address VARCHAR(100);可以采用如下語句進行操作：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 語句可以使用在 PL/SQL 程序塊中，但其目的是將或集合。這時需要在 SELECT 語句中加 INTO 選項。例如：的結(jié)果賦值給某個變量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 語句UPDATE 語句使用戶可以修改表中已經(jīng)有的UPDATE table_nameSET (column_name=value) WHERE condition;其中 table_name 為要更改的表名；的值。UPDATE 語句的基本語法如下：column_name 為要更新的字段；value 表示將要更新的字段更改為 value 值；WHERE 子句標識哪些需要更改，condition 表明滿足條件 condition 的才會被更改，其它被更改。例如我們修改 department 表，讓其部門號為 1 的部門名稱更改為軟件開發(fā)部：UPDATE departmentSET d

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

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

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

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

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

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

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

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

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

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 語句為事務(wù)中的提交語句，它將緩沖區(qū)的內(nèi)容寫入數(shù)據(jù)庫。由于我們使用DML 中的 INSERT、UPDATE、DELETE 語句更改表的時候，結(jié)果只是反映在緩沖區(qū)中，要最終把結(jié)果寫入數(shù)據(jù)庫，則需要運行這個 COMMIT 語句。Rollback 命令則正好相反。如：INSERT INTO emp

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

31、這樣：SAVEPOINT pointname;144如果我們定義了一個保存點以后，就可以用下面的語法將一個事務(wù)撤消到該保存點：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會話命令與系統(tǒng)命令命令有 ALTER會話命令可動態(tài)地一個用戶會

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

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

34、ET SQL_TRACE=TRUE;145例二，修改 NLS 參數(shù)，設(shè)置語言為法語，日期格式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)化方法和目標來優(yōu)化 SQL 語句，這些優(yōu)化方法有：FIRST_ROW：最快響應(yīng)時間； ALL_ROW：最大吞吐量； RULE：基于規(guī)則。語句如下：ALTER SESSION SET OPTIMIZER_GOAL=FIRST_ROW此外 ALTER SESSION

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

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

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

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

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

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

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

42、申明部分（declarative section）、執(zhí)行部分（executable section）和異常處理部分（exception section）。其中只有執(zhí)行部分是必須的，其它部分都是可選的。下面給出含有三個基本部分的一個塊的例子：DECLARE/* 申明部分。所有的變量、游標和類型都在這里申明 */* 申明一個NUMBER型的變量v_ncolumn，并賦予初始值6 */ v_ncolumn NUMBER:=6;v_vcolumn VARCHAR2(30); -申明一個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('沒有找到匹配的數(shù)據(jù)');END;從上例中我們可以看出，在一個 PL/SQL 塊中所要使用的變量、類型和游標等都是在聲明部分進行。部分一般都是以關(guān)鍵字 DECLARE 開始的。部分也可以本地過程和函數(shù)，但是這些過程和函數(shù)只能

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

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

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

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

48、指向另一個類型的指針；LOB 類型用來大型的在本章中，我們只介紹標量類型。一數(shù)字型數(shù)字類型可以讓我們數(shù)字型數(shù)據(jù)（整形、實數(shù)型和浮點型）。它有三種基本類型：BINARY_INTEGER、NUMBER 和 PLS_INTEGER。1. NUMBERLOB 型BFILE BLOB CLOB NCLOB型REF CURSORREF object_type復(fù)合型RECORD TABLE VARRAY標量類型型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ù)類型用來整數(shù)和符點數(shù)。它的范圍是 1E-130 . 10E12

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

51、指定的位數(shù)進行四舍五入，如果給的刻度是負數(shù)的話則值會按照刻度指定的位數(shù)往左進行四舍五入。如一個 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 來一個有符號的整形值。它的大小范圍是-2*31 .2*31（-2 的 31 次方到+2 的 31 次方）。和 PLS_IN

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

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

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