Oracle數(shù)據(jù)庫應用

1、Oracle 數(shù)據(jù)庫應用-Oracle PL/SQL編程SQLSQL及及PL/SQLPL/SQL簡介簡介SQLSQL語言基礎語言基礎 PL/SQLPL/SQL編程編程21.1 SQL簡介SQL是Structure Query Language（結(jié)構化查詢語言）的簡稱，是用戶與數(shù)據(jù)庫交流所需要的標準語言1986年，ANSI（American National Standards Institute）數(shù)據(jù)庫標準委員會推出第一個SQL語言標準SQL-86SQL-89，SQL-92，SQL-99SQL-2003，SQL-2006，SQL-2008 XML、window函數(shù)、Merge語句 Oracle

2、完全遵從ANSI的SQL標準，Oracle9i/Oracle10g集成了SQL-99標準SQL是應用程序與數(shù)據(jù)庫進行交互操作的接口。它將數(shù)據(jù)查詢（Data Query）、數(shù)據(jù)操縱（Data Manipulation）、數(shù)據(jù)定義（Data Definition）和數(shù)據(jù)控制（Data Control）功能集于一體，從而使得應用開發(fā)人員、數(shù)據(jù)庫管理員、最終用戶都可以通過SQL語言訪問數(shù)據(jù)庫，并執(zhí)行相應操作31.1.1 SQL語言特點 SQL采用集合操作方式，對數(shù)據(jù)的處理是成組進行的。通過使用集合操作方式，可以加快數(shù)據(jù)的處理速度。 執(zhí)行SQL語句時，每次只能發(fā)送并處理一條語句。 執(zhí)行SQL語句時，用戶

3、只需要知道其邏輯含義，而不需要知道SQL語句的具體執(zhí)行步驟。例如，在使用WHERE子句檢索數(shù)據(jù)時，用戶可以取得所需要的記錄，而這些記錄如何存儲、如何檢索不需要用戶進行干預。 使用SQL語句時，既可以采用交互方式（例如SQL*Plus），也可以將SQL語句嵌入到高級語言中執(zhí)行（例如C，C+，Java） 41.1.2 SQL語言分類 數(shù)據(jù)查詢語言（SELECT）語句：用于檢索數(shù)據(jù) 數(shù)據(jù)操縱語言（Data Manipulation Language，DML）：用于改變數(shù)據(jù)庫數(shù)據(jù)，包括INSERT,UPDATE,DELETE 數(shù)據(jù)定義語言（Data Definition Language，DDL）：用

4、于建立、修改和刪除數(shù)據(jù)庫對象，包括CREATE，ALTER，DROP等語句 數(shù)據(jù)控制語言（Data Control Language，DCL）：用于執(zhí)行權限授予和權限收回操作，包括GRANT和REVOKE語句 事務控制語言（Transactional Control Language，TCL）：用于維護數(shù)據(jù)的一致性，包括COMMIT,ROLLBACK和SAVEPOINT語句51.1.3 SQL語言編寫規(guī)則 SQL關鍵字不區(qū)分大小寫 對象名和列名不區(qū)分大小寫 字符值和日期值區(qū)分大小寫 一條SQL語句可以單行或多行編寫61.2 PL/SQL簡介 PL/SQL是Oracle在標準SQL語言上的過程性

5、擴展 允許嵌入SQL語句 允許定義變量和常量 允許過程語言結(jié)構（條件分支語句和循環(huán)語句等） 一個PL/SQL塊中可包含多條SQL語句和PL/SQL語句 允許使用例外處理Oracle錯誤等71.2.1 PL/SQL優(yōu)點和特征提高應用程序的運行性能提供模塊化的程序設計功能允許定義標識符具有過程語言控制結(jié)構支持面向?qū)ο缶幊讨С之惓Ｌ幚砉δ?SQLSQL及及PL/SQLPL/SQL簡介簡介SQLSQL語言基礎語言基礎 PL/SQLPL/SQL編程編程9SQL語言基礎 基本查詢基本查詢 常用函數(shù)常用函數(shù) DMLDML語句語句 DDLDDL語句語句 DCLDCL語句和事務語句和事務102.1 基本查詢 2

6、.1.1使用FROM子句指定表SELECT語句使用FROM子句指定查詢中包含的行和列所在的表。在查詢其他角色對應的方案中的表時，需要指定這個方案的名字可以在FROM子句中指定多個表，表與表之間用（,）隔開可以為表指定別名select * from gram pr, gramcontent pcwhere gramid=gramid112.1.2 使用SELECT指定列可以指定查詢的表中所有列，在SELECT關鍵字后指定（*）。應盡可能少用通配符（*）。可以指定查詢的表中的某些列而不是全部。這些列名跟在SELECT關鍵詞后，列名與列名之

7、間用（,）隔開?？梢允褂昧袆e名。如果要使用列別名，那么列別名應在列或表達式之后，在二者之間可以加AS關鍵字或空格。如果列別名有大小寫之分，或者含特殊字符或空格，那么別名必須用雙引號引住select gramid, gramcodefromicsp as programcode, p.price*0.001, p.price*0.001 price , p.price*0.001 價格, p.price*0.001 價 格(元) from program pwhere price0122.1.3 使用WHERE子句指定行運算符運算符比較：、=、=、!=、=確定范圍：Betwe

8、en A and B、Not Between A and B 確定集合：IN、NOT IN字符匹配：LIKE、NOT LIKE空值：IS NULL、IS NOT NULL多重條件：AND、OR、NOT 132.1.3 使用WHERE子句指定行l(wèi)ike字符匹配：Like、Not Like通配符： % 匹配任意字符串 _ 匹配任意一個字符大小寫敏感列出姓張的學生的學號、姓名Select sno，sname From Student Where sname LIKE 張%列出張姓且單名的學生的學號、姓名 ?轉(zhuǎn)義符轉(zhuǎn)義符 escapeselect table_name,tablespace_name,

9、num_rows from user_tableswhere table_name like %_% escape 142.1.4 使用ORDER BY子句 ORDER BY子句對SELECT語句檢索到的數(shù)據(jù)進行排序SELECT column_name_1, , column_name_nFROM table_name_1, , table_name_nWHERE ORDER BY column_name_1, , column_name_n 使用該語句，首先根據(jù)column_name_1列進行排序，如果出現(xiàn)該列中有多個相同的數(shù)據(jù)，則根據(jù)column_name_2列進行排序。 默認根據(jù)指定的列

10、進行升序排序。要按降序排序，則可以在列名后加上關鍵詞DESC（descend縮寫）。升序在列名后加關鍵詞ASC（ascend縮寫），升序是默認的排序法，一般不必加關鍵詞。152.1.5 使用DISTINCT 使用DISTINCT檢索唯一的表列值，即去掉了重復值。select distinct arealevel from area;2.1.6 使用算術運算符 SQL語句中使用算術運算符(+, -, *, /)。與平常使用算術表達式的方式一樣，SQL語句中帶有運算符的表達式同樣是從左到右按照乘除優(yōu)先加減的順序進行運算 162.1.7 處理NULL值NULL表示未知值，它既不是空格也不是表示未知值

11、，它既不是空格也不是0。需注意的是，當表達式。需注意的是，當表達式(算術運算表達式算術運算表達式)包包含含NULL時，其結(jié)果也是時，其結(jié)果也是NULLselect mod(18,4), mod(18,null), 3+null, 3-null,3*null,3/null from dual; MOD(18,4) MOD(18,NULL) 3+NULL 3-NULL 3*NULL 3/NULL- - - - - - 2 使用使用NVL函數(shù)函數(shù)NVL函數(shù)用于將函數(shù)用于將NULL轉(zhuǎn)變?yōu)閷嶋H值，其語法格式為轉(zhuǎn)變?yōu)閷嶋H值，其語法格式為NVL(expr1,expr2)。如果如果expr1是是null，則返

12、回，則返回expr2；如果；如果expr1不是不是null，則返回，則返回expr1。參。參數(shù)數(shù)expr1和和expr2可以是任意數(shù)據(jù)類型，但二者的數(shù)據(jù)類型必須匹配可以是任意數(shù)據(jù)類型，但二者的數(shù)據(jù)類型必須匹配select ename,sal,comm, sal+comm, sal+nvl(comm,0) 月收入 from emp; ENAME SAL COMM SAL+COMM 月收入- - - - -SMITH 800.00 800ALLEN 1600.00 300.00 1900 1900MARTIN 1250.00 1400.00 2650 2650172.2 常用函數(shù)2.2.1字符函數(shù)

13、字符函數(shù)ASCII(c1): c1為字符串，該函數(shù)返回第一個字符的ASCII碼，它的逆函數(shù)是CHR()CHR(i): i是一個數(shù)字，函數(shù)返回十進制表示的字符CONCAT(c1,c2): c1,c2都為字符串，函數(shù)將c2連接到c1的后面，如果c1為null，將返回c2，如果c2為null，則返回c1，如果c1、c2都為null，則返回null。和操作符|相同。INITCAP(c1): c1為一個字符串。函數(shù)將每個單詞的第一個字母大寫其他字母小寫返回。單詞由空格，控制字符，標點符號限制。182.2.1 字符函數(shù)INSTR(c1,c2,i,j)其中，c1,c2均為字符串，i,j為整數(shù)。函數(shù)返回c2在

14、c1中第j次出現(xiàn)的位置，搜索從c1的第i個字符開始。當沒有發(fā)現(xiàn)需要的字符時返回0，如果i為負數(shù)，那么搜索將從右到左進行，但是位置的計算還是從左到右，i和j的默認值為1。INSTRB(c1,c2,i,j)與INSTR()函數(shù)一樣，只是它返回的是字節(jié)，對于單字節(jié)INSTRB()等于INSTR()。LENGTH(c1)其中，c1為字符串，函數(shù)返回c1的字符長度，如果c1為null，將返回null值。LENGTHB(c1): 返回字符串c1的字節(jié)長度。192.2.1 字符函數(shù)LPAD(c1,i,c2): c1，c2均為字符串，i為整數(shù)。在c1的左側(cè)用c2字符串補足至長度i，可多次重復，如果i小于c1的

15、長度，那么只返回i那么長的c1字符，其他的將被截去。c2的默認值為單空格。RPAD(c1,i,c2): c1，c2均為字符串，i為整數(shù)。在c1的右側(cè)用c2字符 串補足至長度i，可多次重復，如果i小于c1的長度，那么只返回i那么長的c1字符，其他的將被截去。c2的默認值為單空格。LTRIM(c1,c2): 用于去掉字符串c1左端所包含的c2中的任何字符。c2默認為空格符RTRIM(c1,c2): 把c1中最右邊的字符去掉，使其最后一個字符不在c2中TRIM(c1 from c2): 該函數(shù)用于從字符串的頭部、尾部或兩端截斷特定字符，參數(shù)c1為要截去的字符，c2是源字符串。如果c1為空格，則可以省

16、略c1參數(shù)和from關鍵字。202.2.1 字符函數(shù)SUBSTR(c1,i,j)c1為一個字符串，i,j為整數(shù)，從c1的第i位開始返回長度為j的子字符串，如果j為空，則直到字符串的末尾。SUBSTRB(c1,i,j)與SUBSTR大致相同，只是i,j是以字節(jié)計算的。REPLACE(c1,c2,c3)c1,c2,c3都是字符串,函數(shù)用c3代替出現(xiàn)在c1中的c2后返回TRANSLATE(c1,c2,c3)將c1中與c2中相同的字符以c3代替。LOWER(c1): 返回c1的小寫字符。UPPER(c1): 返回c1的大寫字符。212.2.2 數(shù)字函數(shù)ABS(n): 返回n的絕對值。CEIL(n):

17、返回大于或等于n的最小整數(shù)select ceil(20.22222),ceil(20.88888),ceil(21.000) from dual; CEIL(20.22222) CEIL(20.88888) CEIL(21.000)- - - 21 21 21FLOOR(n): 返回小于等于n的最大整數(shù)select floor(20.22222),floor(20.88888),floor(21.000) from dual; FLOOR(20.22222) FLOOR(20.88888) FLOOR(21.000)- - - 20 20 21222.2.2 數(shù)字函數(shù)MOD(n1,n2): 返

18、回n1除以n2的余數(shù)POWER(n1,n2): 返回n1的n2次方ROUND(n1,n2): 返回舍入小數(shù)點右邊n2位的n1的值， n2的默認值為0select round(20.22222,2),round(20.88888,2),round(21.000,2) from dual; ROUND(20.22222,2) ROUND(20.88888,2) ROUND(21.000,2)- - - 20.22 20.89 21232.2.2 數(shù)字函數(shù)SIGN(n)如果n為正數(shù)返回1，為負數(shù)返回-1，為0返回0TRUNC(n1,n2)該函數(shù)用于截取數(shù)字。如果省略n2，則將數(shù)字n1的小數(shù)部分截去；

19、如果n2是正數(shù)，則將數(shù)字n1截取至小數(shù)點后的第n2位；如果n2是負數(shù)，則將數(shù)字n1截取至小數(shù)點的前n2位例如：select trunc(22.889), trunc(22.889,2), trunc(22.889,-1) from dual; TRUNC(22.889) TRUNC(22.889,2) TRUNC(22.889,-1)- - - 22 22.88 20242.2.2 數(shù)字函數(shù)SIN(n)、SINH(n)、COS(n)、COSH(n)、 TAN(n)、 TANH(n)正弦函數(shù)、雙曲正弦函數(shù)、余弦函數(shù)、雙曲余弦函數(shù)、正切函數(shù)、雙曲正切函數(shù)ASIN(n) 、 ACOS(n)、ATAN

20、(n)反正弦函數(shù)、反余弦函數(shù)、反正切函數(shù)EXP(n): 返回e的n次冪，e=2.71828183LN(n): 返回n的自然對數(shù)，n必須大于0LOG(n1,n2): 返回以n1為底n2的對數(shù)SQRT(n): 返回n的平方根252.2.3日期函數(shù)SYSDATE返回當前日期和時間 LAST_DAY(d)返回包含日期d的月份的最后一天。TRUNC(d,fmt)返回由fmt指定的單位的日期dselect sysdate,trunc(sysdate,yyyy), trunc(sysdate,mm), trunc(sysdate,dd) from dual;262.2.3日期函數(shù)ADD_MONTHS(d,i

21、)返回日期d加上i個月后的結(jié)果。i可以是任意整數(shù)。MONTHS_BETWEEN(d1,d2)返回d1和d2之間月的數(shù)目。select MONTHS_BETWEEN(to_date(2011-04-02,yyyy-mm-dd),to_date(2011-04-20,yyyy-mm-dd) MONTHS_BETWEEN1,from dual; MONTHS_BETWEEN1 -0.580645161290NEW_TIME(d1,tz1,tz2)該函數(shù)返回時區(qū)一的日期時間所對應的時區(qū)二的日期時間272.2.4 轉(zhuǎn)換函數(shù)TO_CHAR(character)該函數(shù)用于將NCHAR,NVARCHAR2,C

22、LOB和NCLOB數(shù)據(jù)轉(zhuǎn)變?yōu)閿?shù)據(jù)庫字符集數(shù)據(jù)，當用于NCHAR, NVARCHAR2和NCLOB類型時，字符串用單引號括起來，前面加上n。TO_CHAR(date,fmt,nlsparam)該函數(shù)用于將日期值轉(zhuǎn)變?yōu)樽址琭mt用于指定日期格式，nls_param用于指定NLS參數(shù)。TO_CHAR(n,fmt,nlsparam)該函數(shù)用于將數(shù)字值轉(zhuǎn)變?yōu)閂ARCHAR2數(shù)據(jù)類型。282.2.4 轉(zhuǎn)換函數(shù)TO_CLOB(char)將字符串轉(zhuǎn)變?yōu)镃LOB類型。TO_DATE(c,fmt,nlsparam)將符合特定日期格式的字符串轉(zhuǎn)變?yōu)镈ATE類型的值。TO_NUMBER(c,fmt,nlspara

23、m)將符合特定數(shù)字格式的字符串值轉(zhuǎn)變?yōu)閿?shù)字值。TO_MULTI_BYTE(c)將單字節(jié)字符串轉(zhuǎn)變?yōu)槎嘧止?jié)字符串。 TO_SINGLE_BYTE(c)將多字節(jié)字符串轉(zhuǎn)變?yōu)閱巫止?jié)字符串。select to_multi_byte(abcd), to_single_byte() from dual; TO_MULTI_BYTE(ABCD) TO_SINGLE_BYTE()- - abcd292.2.4 轉(zhuǎn)換函數(shù)CONVERT(c,dset,sset)c是字符串，dset、sset是兩個字符集，函數(shù)將字符串c由sset字符集轉(zhuǎn)換為dset字符集。HEXTORAW(x)x為16進制字符串，函數(shù)將16進制的

24、x轉(zhuǎn)換為RAW數(shù)據(jù)類型。RAWTOHEX(x)用于將RAW數(shù)值轉(zhuǎn)換為VARCHAR2的16進制字符串。ROWIDTOCHAR(rowid)用于將ROWID數(shù)值轉(zhuǎn)換為VARCHAR2數(shù)據(jù)類型。302.2.5組函數(shù)AVG(ALL|DISTINCTexpr)該函數(shù)用于計算平均值。COUNT(ALL|DISTINCTexpr)該函數(shù)用于返回總計行數(shù)。MAX(ALL|DISTINCTexpr)該函數(shù)用于取得列或表達式的最大值。MIN(ALL|DISTINCTexpr)該函數(shù)用于取得列或表達式的最小值。 SUM(ALL|DISTINCTexpr)計算列或表達式的總和。STDDEV(ALL|DISTINCT

25、expr)取得標準偏差，標準偏差是方差的平方根。VARIANCE(ALL|DISTINCTexpr)返回列或表達式的方差。312.2.6 使用GROUP BY組函數(shù)用于對數(shù)據(jù)進行分組或分類，當在SELECT字句中使用組函數(shù)時，必須把分組列或者非“常數(shù)類”放在GROUP BY字句中GROUP BY字句中使用字句中使用HAVINGGROUP BY字句中使用字句中使用GROUP_ID()該函數(shù)用于區(qū)分分組結(jié)果中的重復行。GROUP BY字句中使用字句中使用GROUPING(expr)GROUP BY字句中使用字句中使用GROUPING_ID(expr1,exp2,)322.3 DML語句2.3.1

26、INSERT語句插入表數(shù)據(jù)語句插入表數(shù)據(jù)一般一般INSERT語句的使用語句的使用語法如下INSERT INTO owner.tabledb_link(column1,column2)Values(express1,express2)INSERT INTO owner.tabledb_link(column1,column2)subquery 332.3.2 UPDATE語句更新表數(shù)據(jù)UPDATE語句語法如下：UPDATE owner.table SET column1= express1 ,column2= express2WHERE condition342.3.3 DELETE語句刪除表數(shù)

27、據(jù)DELETE語句語法如下：DELETE FROM owner.tableWHERE condition352.3.4 MERGE語句修改表數(shù)據(jù)使用MERGE語句能夠在一個SQL語句中對表同時執(zhí)行插入和更新操作。MERGE命令從一個或多個數(shù)據(jù)源中選擇行來更新或插入一個或多個表。MERGE INTO products pUSING newproducts npON (duct_id=duct_id)WHEN MATCHED THENUPDATESET duct_name=duct_name; 362.3.5 使用TRUNCATE語句TRUNCATE T

28、ABLE與不含有WHERE子句的DELETE語句在功能上相同。但TRUNCATE語句具有以下優(yōu)點：所用的事務日志空間較少 DELETE 語句每次刪除一行，并在事務日志中為所刪除的每行記錄一項。TRUNCATE TABLE 通過釋放用于存儲表數(shù)據(jù)的數(shù)據(jù)頁來刪除數(shù)據(jù)，并且在事務日志中只記錄頁釋放。使用的鎖通常較少 當使用行鎖執(zhí)行 DELETE 語句時，將鎖定表中各行以便刪除。TRUNCATE TABLE 始終鎖定表和頁，而不是鎖定各行。如無例外，在表中不會留有任何空頁 執(zhí)行 DELETE 語句后，表、索引會留下一些空頁。372.4 DDL語句2.4.1 常用數(shù)據(jù)類型常用數(shù)據(jù)類型字符類型CHAR、V

29、ARCHAR2、NCHAR、NVARCHAR2、RAW數(shù)值類型NUMBER(P,S)、INTEGERP精度(138) S刻度(-84127)日期時間類型DATE、TIMESTAMPLOB類型CLOB、 NCLOB、 BLOB、BFILEROWID 類型ROWID、NROWIDLONG類型(已棄用)LONG、LONG RAW382.4.2 表1. 創(chuàng)建表創(chuàng)建表使用使用CREATE TABLE語句創(chuàng)建表語句創(chuàng)建表使用CREATE TABLE語句創(chuàng)建表基本語法如下：CREATE TABLE owner.table_name(column_name datatype default expr not

30、null| null constraint,column_name datatype default expr not null| null constraint)tablespace tablespace_name 392.4.2 表通過子查詢建表通過子查詢建表可以使用查詢從基于一個或多個表中建立表，表的列的數(shù)據(jù)類型和大小由查詢結(jié)果決定?？梢赃x擇其他表中所有列或部分列。在CREATE TABLE語句中使用關鍵字ASCREATE TABLE program_temp1 asselect * from program; 子查詢建表的注意事項： 可以關連多個表及用集合函數(shù)生成新表,注意選擇出來的字

31、段必須有合法的字段名稱,且不能重復。 用子查詢方式建立的表，只有NOT NULL的約束條件能繼承過來, 其它的約束條件、索引和默認值都不會繼承過來。 根據(jù)需要，可以用alter table add constraint 再建立其它的約束條件，如primary key等。 402.4.2 表2. 修改表修改表增加列增加列語法：ALTER TABLE owner. table_name ADD column_definition例如：ALTER TABLE orders ADD orderdate DATE;刪除列刪除列語法：ALTER TABLE owner. table_name DROP C

32、OLUMN column_names | (column_names)CASCADE CONSTRAINS例如：alter table emp drop column col_test;412.4.2 表更改列更改列語法：ALTER TABLE owner. table_name MODIFY column_name new_attributes;例如：ALTER TABLE orders MODIFY (quantity number(10,3),status varchar2(15);或ALTER TABLE orders MODIFY quantity number(10,3);ALTE

33、R TABLE orders MODIFY status varchar2(15);修改列的規(guī)則如下： 可以增加字符串數(shù)據(jù)類型的列的長度，數(shù)字數(shù)據(jù)類型列的精度 減少列的長度時，該列應該不包含任何值，所有數(shù)據(jù)行都為NULL 改變數(shù)據(jù)類型時，該列的值必須是NULL 對于十進制數(shù)字，可以增加或減少但不能降低他的精度422.4.2 表3. 更改表名更改表名語法：RENAME old_tablename TO new_tablename;例如：rename emp to emp11; 4. 刪除表刪除表語法：DROP TABLE owner. table_name CASCADE CONSTRAINTS

34、例如：drop table emp_temp1;432.4.3 主鍵創(chuàng)建表時定義主鍵創(chuàng)建表時定義主鍵例如：create table DEPT_TEMP(DEPTNO NUMBER(2) not null,DNAME VARCHAR2(14),constraint PK_DEPT_TEMP primary key (DEPTNO)using index tablespace USERS) tablespace USERS; create table DEPT_TEMP(DEPTNO NUMBER(2) constraint PK_DEPT_TEMP primary keyusing index

35、tablespace USERS,DNAME VARCHAR2(14) tablespace USERS;442.4.3 主鍵使用使用alter table語句定義主鍵語句定義主鍵語法：ALTER TABLE owner. tablenameADD CONSTRAINT constraint_name PRIMARY KEY (column1 ,column2,)例如：create table DEPT_TEMP (DEPTNO NUMBER(2) not null, DNAME VARCHAR2(14) tablespace USERS;alter table DEPT_TEMPadd co

36、nstraint PK_DEPT_TEMP primary key (DEPTNO)using index tablespace USERS; 452.4.3 主鍵使用使用alter table語句修改主鍵狀態(tài)語句修改主鍵狀態(tài)語法：ALTER TABLE owner. tablename DISABLE CONSTRAINT constraint_name;ALTER TABLE owner. tablename ENABLE CONSTRAINT constraint_name;ALTER TABLE owner. tablename DISABLE PRIMARY KEY;ALTER TA

37、BLE owner. tablename ENABLE PRIMARY KEY;刪除主鍵刪除主鍵語法：ALTER TABLE owner. tablenameDROP CONSTRAINT constraint_name CASCADE462.4.4 外鍵創(chuàng)建表時定義外鍵創(chuàng)建表時定義外鍵語法：CREATE TABLE owner.table_name(/省略建表部分CONSTRAINT constraint_name FOREIGN KEY (column1 ,column2,)REFERENCES owner.table_name (column1 ,column2,)ON DELETE C

38、ASCADE | SET NULL ); 使用使用alter table語句定義外鍵語句定義外鍵語法：ALTER TABLE owner.table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column1 ,column2,)REFERENCES owner.table_name (column1 ,column2,)ON DELETE CASCADE | SET NULL472.4.4 外鍵使用使用alter table語句修改外鍵狀態(tài)語句修改外鍵狀態(tài)語法：ALTER TABLE owner. tablename DISABLE CO

39、NSTRAINT constraint_name;ALTER TABLE owner. tablename ENABLE CONSTRAINT constraint_name;刪除外鍵刪除外鍵語法：ALTER TABLE owner. tablename DROP CONSTRAINT constraint_name; 482.4.5 約束約束，也稱為完整性約束，Oracle系統(tǒng)的完整性約束包括主鍵、外鍵以及CHECK,NOT NULL,Unique Index共5種類型Check約束約束CHECK約束可以為列級別上的約束，也可為一個表級別上的約束語法：CONSTRAINT constrain

40、t_name CHECK(condition)Not null約束約束Not null約束應用在單一的數(shù)據(jù)列上。唯一性約束唯一性約束唯一性約束要求一列或一組列中的每一個值都是唯一的，唯一性約束允許空值。使用CREATE UNIQUE INDEX語句創(chuàng)建唯一性索引492.4.6 索引索引是關系數(shù)據(jù)庫中用于存放表中每一條記錄位置的一種對象，主要目的是加快數(shù)據(jù)的讀取速度和完整性檢查創(chuàng)建索引創(chuàng)建索引創(chuàng)建索引語法如下CREATE UNIQUE INDEX owner. index_nameON owner. tablename (column1 ASC | DESC ,column2 ASC | DES

41、C,)PCTFREE nINITRANS nMAXTRANS nSTORAGE TABLESPACE tablespace_nameNOSORTONLINENOLOGGINGCOMPUTE STATISTICSPARALLEL 502.4.6 索引關鍵參數(shù)說明。PCTFREE：索引數(shù)據(jù)塊空閑空間的百分比；INITRANS、MAXTRANS：指定初始和最大的事務表大?。籗TORAGE：存儲參數(shù)；TABLESPACE：索引存儲的表空間；NOSORT：不排序。默認情況下，在表中創(chuàng)建索引的時候，會對表中的記錄進行排序，排序成功后再創(chuàng)建索引。但是當記錄比較多時，這個排序作業(yè)會占用比較多的時間。特殊情況下

42、，可以使用該參數(shù)加快建索引的速度。 512.4.6 索引ONLINE：在線建索引。默認情況下，數(shù)據(jù)庫系統(tǒng)是不允許DML操作與創(chuàng)建索引的操作同時進行的。使用ONLINE選項，DML操作與創(chuàng)建索引操作是可以同時進行，但是代價就是建索引的時間會延長。NOLOGGING: 是否需要記錄日志信息。就是建索引時記不記日志，一般用在在大型表上建索引，使用該參數(shù)，默認是記日志。 COMPUTE STATISTICS: 是否更新統(tǒng)計信息。數(shù)據(jù)庫在選擇執(zhí)行計劃時，依據(jù)就是表、索引的統(tǒng)計信息，該參數(shù)會提示數(shù)據(jù)庫建索引的同時，更新對應的統(tǒng)計信息。PARALLEL: 多服務進程創(chuàng)建索引。數(shù)據(jù)庫服務器若是多CPU情況下，

43、使用該參數(shù)會增加并發(fā)，提高效率。522.4.6 索引創(chuàng)建索引例子：create index INDX_PROGRAM_SSP on PROGRAM (SHORTSPELL) tablespace MEDIADATA_INDX; create unique index UI_PROGRAM_CODE on PROGRAM (ICSPID, PROGRAMCODEFROMICSP) tablespace MEDIADATA_INDX; 刪除索引刪除索引語法：DROP INDEX owner. index_name532.4.7 視圖創(chuàng)建視圖創(chuàng)建視圖語法：CREATE OR REPLACE VIEW

44、 view_name ASSELECT(一組語句)刪除視圖刪除視圖語法：DROP VIEW view_name542.4.8 序列創(chuàng)建序列創(chuàng)建序列語法：CREATE SEQUENCE owner.sequence_nameMINVALUE n |NOMINVALUEMAXVALUE n |NOMAXVALUESTART WITH nINCREMENT BY nCACHE n | NOCACHEORDER | NOORDER 552.4.8 序列修改序列修改序列語法：ALTER SEQUENCE owner.sequence_nameMAXVALUE n |NOMAXVALUEINCREMENT

45、 BY nCACHE n | NOCACHEORDER | NOORDER刪除序列刪除序列語法：DROP SEQUENCE owner.sequence_name562.4.9 同義詞 對另一個數(shù)據(jù)對象而言同義詞是一個別名。 public同義詞是針對所有用戶的，相對而言private同義詞則只針對對象擁有者或被授予權限的賬戶。 在本地數(shù)據(jù)庫中同義詞可以表示表、視圖、序列、過程、函數(shù)或包等數(shù)據(jù)對象，也可以通過鏈接表示另一個數(shù)據(jù)庫的對象。 創(chuàng)建同義詞創(chuàng)建同義詞語法：CREATE OR REPLACE PUBLIC SYNONYM synonym_name FOR owner.objectdb_li

46、nk 刪除同義詞刪除同義詞語法：DROP PUBLIC SYNONYM synonym_name572.5 DCL語句和事務2.5.1 使用使用GRANT語句授權語句授權在Oracle 中，根據(jù)系統(tǒng)管理方式不同，將權限分為系統(tǒng)權限與實體權限。系統(tǒng)權限是指是否被授權用戶可以連接到數(shù)據(jù)庫上，在數(shù)據(jù)庫中可以進行哪些系統(tǒng)操作。實體權限是指用戶對具體的模式實體 (schema)所擁有的權限。例如：select any table是系統(tǒng)權限，它表示可以查看任何表。而select on table1是實體權限，表示對表table1的查詢權限。 582.5.1 使用GRANT語句授權系統(tǒng)權限授權命令的使用系統(tǒng)

47、權限授權命令的使用GRANT 權限名 TO用戶|角色|PUBLIC 其中，PUBLIC表示將權限賦給數(shù)據(jù)庫中所有的用戶 例如：賦給用戶HITV權限CREATE TABLE的授權命令如下： GRANT CREATE ANY TABLE TO HITV; 實體權限授權命令的使用實體權限授權命令的使用GRANT 實體權限名|ALL TO 用戶|角色|PUBLIC 其中，ALL表示實體的所有實體權限。 例如： GRANT SELECT ON PROGRAM TO HITV;592.5.2使用REVOKE語句回收授權 系統(tǒng)權限回收授權命令REVOKE 權限名 FROM用戶|角色|PUBLICREVOKE

48、 CREATE ANY TABLE FROM HITV;實體權限回收授權命令REVOKE 實體權限名|ALL FROM 用戶|角色|PUBLIC例如：REVOKE SELECT ON PROGRAM FROM HITV; 602.5.3 事務和鎖 當執(zhí)行事務操作（DML語句）時，Oracle會在被作用表上加表鎖，以防止其他用戶改變表結(jié)構；同時會在被作用行上加行鎖，以防止其他事務在相應行上執(zhí)行DML操作。在Oracle數(shù)據(jù)庫中，為了確保數(shù)據(jù)庫數(shù)據(jù)的讀一致性，不允許其他用戶讀取臟數(shù)據(jù)（其他會話未提交事務）。 提交事務：使用COMMIT語句可以提交事務。執(zhí)行DDL語句、 DCL語句自動提交事務 回退

49、事務： 使用SQL命令SAVEPOINT savepoint_name設置保存點設置保存點 使用命令ROLLBACK to savepoint_name回退到保存點 使用ROLLBACK回退事務61SQLSQL及及PL/SQLPL/SQL簡介簡介SQLSQL語言基礎語言基礎 PL/SQLPL/SQL編程編程62PL/SQL編程 PL/SQLPL/SQL編程基礎編程基礎 過程和函數(shù)過程和函數(shù) 觸發(fā)器觸發(fā)器 異常處理異常處理633.1 PL/SQL編程基礎3.1.1 PL/SQL塊結(jié)構塊結(jié)構塊是PL/SQL的基本程序單元，編寫PL/SQL程序?qū)嶋H就是編寫PL/SQL塊。一個PL/SQL塊中可以嵌套

50、其他PL/SQL塊。編寫PL/SQL應用模塊，塊的嵌套層次沒有限制。PL/SQL塊由三個部分組成：定義部分、執(zhí)行部分、例外處理部分。其中，定義部分用于定義常量、變量、游標、例外、復雜數(shù)據(jù)類型等；執(zhí)行部分用于實現(xiàn)應用模塊功能，該部分包含了要執(zhí)行的PL/SQL語句和SQL語句;例外處理部分用于處理執(zhí)行部分可能出現(xiàn)的運行錯誤。643.1.1 PL/SQL塊結(jié)構PL/SQL塊的基本結(jié)構如下所示：DECLARE/*定義部分。 定義常量、變量、游標、例外、復雜數(shù)據(jù)類型等*/BEGIN/*執(zhí)行部分。SQL語句和PL/SQL語句*/EXCEPTION/*例外處理部分。*/END; /*塊結(jié)束標記*/ 653.

51、1.2 PL/SQL塊分類匿名塊命名塊(以在塊的開始和結(jié)束標示)子程序過程函數(shù)包觸發(fā)器 663.1.3 PL/SQL詞匯單元PL/SQL的詞匯單元有分隔符、標識符、文字串和注釋等四種。分隔符分隔符單分隔符 + % . / ( ) : , * “ = ; -組合分隔符:= = | /* */ . != = = - 673.1.3 PL/SQL詞匯單元標識符標識符標識符用于指定PL/SQL程序單元和程序項的名稱。通過使用標識符，可以定義常量、變量、例外、顯示游標、游標變量、參數(shù)、子程序以及包的名稱。標識符使用必須滿足以下規(guī)則：標識符定義變量、常量時，每行只能定義一個標識符。標識符名稱必須要以（AZ

52、,az）開始，并且最大長度為30個字符。如果以其他字符開始，則必須要使用雙引號引住。標識符名稱只能使用AZ，az，09，_，$和#。如果要使用其他字符，則必須要使用雙引號引住。標識符名稱不能使用Oracle關鍵字。683.1.3 PL/SQL詞匯單元文本文本文本是指數(shù)字、字符、字符串、日期值或布爾值，而不是標識符。文本包括數(shù)字文本、字符文本、字符串文本、布爾文本、日期時間文本注釋注釋注釋用于解釋單行代碼或多行代碼的作用，從而提高了PL/SQL程序的可讀性。注釋包括單行注釋和多行注釋單行注釋：使用-符號編寫單行注釋多行注釋：使用/*/來編寫多行注釋693.1.4 變量和常量變量和常量聲明變量和常

53、量聲明變量就是由程序讀取或賦值的存儲單元。每個變量都有一個特定的類型與其關聯(lián)，變量的類型定義了變量可以存放的信息類別。PL/SQL還支持用戶自定義的數(shù)據(jù)類型，如記錄類型、表類型等。變量名稱需遵循以下規(guī)則：變量名必須以字母（az,AZ）開頭；其后跟可選的一個或多個字母、數(shù)字（09）或特殊字符$、#或_；變量長度不超過30個字符；變量名中不能有空格。聲明常量與聲明變量一樣，只是需要在前面加入CONSTANT，同時，將值賦給常量。例如：credit_limit CONSTANT number(10,2) := 5000.00;703.1.4 變量和常量變量屬性變量屬性使用%type屬性定義變量當使用

54、%type屬性定義變量時，它會按照數(shù)據(jù)庫列或其他變量來確定新變量的類型和長度。示例如下：my_title books.title%type;使用%type聲明變量my_title有兩個優(yōu)點，第一，不必知道title確切的數(shù)據(jù)類型；第二，如果改變了title的數(shù)據(jù)庫定義，my_title的數(shù)據(jù)類型在運行時也會自動修改。使用%ROWTYPE屬性聲明描述表的行數(shù)據(jù)的記錄示例如下：declaredept_rec dept%ROWTYPE; 713.1.5 表達式和運算符表達式表達式表達式是一個變量和文字的序列，用運算符進行分隔。表達式的數(shù)值由組成它的變量和文字的取值及運算符的定義來決定。例如：9+2*

55、5 (9+2)*5運算符運算符算數(shù)運算符+ - + - * /關系運算符 != = = = =邏輯運算符NOT AND OR其他符號 /* */ - | := . ; () 723.1.6 流程控制IF-THEN-ELSE語法如下：IF boolean_expression1 THENsequence_of_statements;ELSIF boolean_expression2 THENsequence_of_statements;ELSE sequence_of_statements;END IF; 733.1.6 流程控制CASE語法如下：CASE test_varWHEN value1

56、 THEN sequence_of_statements1;WHEN valuen THEN sequence_of_statementsn;ELSE else_sequence;END CASE;另一種形式語法：CASEWHEN boolean_expression1 THEN sequence_of_statements1;WHEN boolean_expressionn THEN sequence_of_statementsn;ELSE else_sequence;END CASE;743.1.6 流程控制LOOP語法如下：LOOP -sequence of statementsEND

57、LOOP;一般通過加入exit語句來終結(jié)循環(huán)。FOR-LOOP語法如下：FOR loop_counter IN low_bound.high_bound LOOP sequence_of_statements;END LOOP;loop_counter為循環(huán)變量，low_bound和high_bound為循環(huán)的最小、最大值，其中l(wèi)ow_bound和high_bound可以動態(tài)定義。 753.1.6 流程控制WHILE-LOOP語法如下：WHILE condition LOOPsequence_of_statements;END LOOP;GOTO語法如下：GOTO label;其中，label

58、是用雙箭頭括號括起來的標記。GOTO不便于代碼的理解和維護，盡量不用GOTO語句763.2 過程和函數(shù)3.2.1 過程過程創(chuàng)建過程創(chuàng)建過程創(chuàng)建過程的基本語法為：CREATE OR REPLACE PROCEDURE procedure_name ( argument IN | OUT | IN OUT type,argument IN | OUT | IN OUT type ) IS | AS procedure_body刪除過程刪除過程使用DROP PROCEDURE procedure_name 命令刪除過程。773.2.2 函數(shù)1. 創(chuàng)建函數(shù)創(chuàng)建函數(shù)創(chuàng)建函數(shù)的基本語法為：CREATE O

59、R REPLACE FUNCTION function_name ( argument IN | OUT | IN OUT type,argument IN | OUT | IN OUT type ) RETURN datatype IS | AS function_body783.2.2 函數(shù)2. 函數(shù)調(diào)用及限制函數(shù)調(diào)用及限制函數(shù)可通過以下形式調(diào)用：作為表達式的一部分調(diào)用；SELECT命令的選擇列表；WHERE和HAVING子句中；START WITH，ORDER BY和GROUP BY；INSERT命令的VALUES子句中；UPDATE命令的SET子句中。SQL語句中函數(shù)調(diào)用的限制：函數(shù)只

60、能帶有輸入?yún)?shù)，不能帶輸出參數(shù)和輸入輸出參數(shù)；函數(shù)只能使用SQL所支持的標準數(shù)據(jù)類型，而不能使用PL/SQL的特有數(shù)據(jù)類型（例如TABLE和RECORD等）；函數(shù)不能包含INSERT,UPDATE和DELETE語句。 3. 刪除函數(shù)刪除函數(shù)使用DROP FUNCTION function_name 命令刪除函數(shù)793.3 觸發(fā)器 觸發(fā)器是指存放在數(shù)據(jù)庫中，并被隱含執(zhí)行的存儲過程。 在Oracle8i之前，只允許基于表或視圖的DML操作（INSERT，UPDATE和DELETE）建立觸發(fā)器；而從Oracle8i開始，不僅支持DML觸發(fā)器，也允許基于系統(tǒng)事件（啟動數(shù)據(jù)庫、關閉數(shù)據(jù)庫、登錄）和DDL