PLSQL基礎(chǔ)培訓(xùn)

1、Restricted Confidential.(Procedural Language/Structured Query Language)Restricted Confidential.目 錄 PL/SQL PL/SQL 程序設(shè)計(jì)簡(jiǎn)介程序設(shè)計(jì)簡(jiǎn)介 PL/SQLPL/SQL塊結(jié)構(gòu)和組成元素塊結(jié)構(gòu)和組成元素 PL/SQLPL/SQL流程控制語句流程控制語句 游標(biāo)的使用游標(biāo)的使用 異常錯(cuò)誤處理異常錯(cuò)誤處理 存儲(chǔ)過程和函數(shù)存儲(chǔ)過程和函數(shù) 包的創(chuàng)建和應(yīng)用包的創(chuàng)建和應(yīng)用 觸發(fā)器觸發(fā)器Restricted Confidential.1.PL/SQL 1.PL/SQL 程序設(shè)計(jì)簡(jiǎn)介程序設(shè)計(jì)簡(jiǎn)介 Restr

2、icted Confidential. PL/SQL是 Procedure Language & Structured Query Language 的縮寫。ORACLE的SQL是支持ANSI(American national Standards Institute)和ISO92 (International Standards Organization)標(biāo)準(zhǔn)的產(chǎn)品。PL/SQL是對(duì)SQL語言存儲(chǔ)過程語言的擴(kuò)展。從ORACLE6以后，ORACLE的RDBMS附帶了PL/SQL。它現(xiàn)在已經(jīng)成為一種過程處理語言。目前的PL/SQL包括兩部分，一部分是數(shù)據(jù)庫(kù)引擎部分；另一部分是可嵌入到許多

3、產(chǎn)品（如C語言，JAVA語言等）工具中的獨(dú)立引擎?？梢詫⑦@兩部分稱為：數(shù)據(jù)庫(kù)PL/SQL和工具PL/SQL。兩者的編程非常相似。都具有編程結(jié)構(gòu)、語法和邏輯機(jī)制。工具PL/SQL另外還增加了用于支持工具（如ORACLE Forms）的句法，如：在窗體上設(shè)置按鈕等。本章主要介紹數(shù)據(jù)庫(kù)PL/SQL內(nèi)容。 什么是什么是PL/SQLPL/SQL Restricted Confidential.PL/SQLPL/SQL與網(wǎng)絡(luò)傳輸與網(wǎng)絡(luò)傳輸OracleOracle數(shù)據(jù)庫(kù)服務(wù)器數(shù)據(jù)庫(kù)服務(wù)器SQLSQLSQLSQLSQLSQL客戶應(yīng)用客戶應(yīng)用OracleOracle數(shù)據(jù)庫(kù)服務(wù)器數(shù)據(jù)庫(kù)服務(wù)器客戶應(yīng)用客戶應(yīng)用SQL

4、SQLSQLSQLSQLSQL使用使用SQLSQL使用使用PL/SQLPL/SQL對(duì)于客戶對(duì)于客戶/服務(wù)器環(huán)境來說，真正的瓶頸是網(wǎng)絡(luò)上。無論網(wǎng)絡(luò)多快，只要客戶端與服服務(wù)器環(huán)境來說，真正的瓶頸是網(wǎng)絡(luò)上。無論網(wǎng)絡(luò)多快，只要客戶端與服務(wù)器進(jìn)行大量的數(shù)據(jù)交換務(wù)器進(jìn)行大量的數(shù)據(jù)交換,應(yīng)用運(yùn)行的效率自然就會(huì)受到影響。應(yīng)用運(yùn)行的效率自然就會(huì)受到影響。在執(zhí)行期間，將所有在執(zhí)行期間，將所有的的SQLSQL語句傳遞給語句傳遞給SQLSQL語句執(zhí)行器組件執(zhí)行。相對(duì)于逐條發(fā)送一組語句執(zhí)行器組件執(zhí)行。相對(duì)于逐條發(fā)送一組SQLSQL語句，語句，PL/SQLPL/SQL的的打包傳輸，減少網(wǎng)絡(luò)流量。打包傳輸，減少網(wǎng)絡(luò)流量。R

5、estricted Confidential.PL/SQL PL/SQL 可用的可用的SQLSQL語句語句: : PL/SQL PL/SQL是是ORACLEORACLE系統(tǒng)的核心語言，系統(tǒng)的核心語言，現(xiàn)在現(xiàn)在ORACLEORACLE的許多部件都是由的許多部件都是由PL/SQLPL/SQL寫成。寫成。在在PL/SQLPL/SQL中可以使用的中可以使用的SQLSQL語句有：語句有：INSERTINSERT，UPDATEUPDATE，DELETEDELETE，SELECT INTOSELECT INTO，COMMITCOMMIT，ROLLBACKROLLBACK，SAVEPOINTSAVEPOINT

6、。提示：在提示：在 PL/SQLPL/SQL中只能用中只能用 SQLSQL語句中的語句中的 DML DML 部分，部分，不能用不能用 DDL DDL 部分，如果要在部分，如果要在PL/SQLPL/SQL中使用中使用DDLDDL( (如如CREATE table CREATE table 等等) )的話，只能以動(dòng)態(tài)的方式來使用。的話，只能以動(dòng)態(tài)的方式來使用。Restricted Confidential.2.PL/SQL塊結(jié)構(gòu)和組成元素塊結(jié)構(gòu)和組成元素 Restricted Confidential.PL/SQLPL/SQL塊塊PL/SQLPL/SQL的的程序結(jié)構(gòu)程序結(jié)構(gòu)PL/SQLPL/SQL

7、程序都是以塊（程序都是以塊（blockblock）為基本單位。）為基本單位。PL/SQL塊可以分為三類：塊可以分為三類：1.無名塊：動(dòng)態(tài)構(gòu)造，只能執(zhí)行一次。無名塊：動(dòng)態(tài)構(gòu)造，只能執(zhí)行一次。2.子程序：存儲(chǔ)在數(shù)據(jù)庫(kù)中的存儲(chǔ)過程、函數(shù)及包子程序：存儲(chǔ)在數(shù)據(jù)庫(kù)中的存儲(chǔ)過程、函數(shù)及包等。當(dāng)在數(shù)據(jù)庫(kù)上建立好后可以在其它程序中等。當(dāng)在數(shù)據(jù)庫(kù)上建立好后可以在其它程序中調(diào)用它們。調(diào)用它們。3.觸發(fā)器：當(dāng)數(shù)據(jù)庫(kù)發(fā)生操作時(shí)，會(huì)觸發(fā)一些事件，觸發(fā)器：當(dāng)數(shù)據(jù)庫(kù)發(fā)生操作時(shí)，會(huì)觸發(fā)一些事件，從而自動(dòng)執(zhí)行相應(yīng)的程序。從而自動(dòng)執(zhí)行相應(yīng)的程序。 整個(gè)整個(gè)PL/SQL塊分三部分：聲明部分（用塊分三部分：聲明部分（用declare

8、開頭）、執(zhí)開頭）、執(zhí)行部分（以行部分（以begin開頭開頭,以以end結(jié)尾）和異常處理部分（以結(jié)尾）和異常處理部分（以exception開頭）。其中執(zhí)行部分是必須的，其他兩個(gè)部分可選。開頭）。其中執(zhí)行部分是必須的，其他兩個(gè)部分可選。 Restricted Confidential.DECLARE n number(10,2);BEGIN DECLARE v varchar2(8); BEGIN END; END;建議少使用建議少使用“嵌套嵌套”DECLARE 變量、常量、游標(biāo)、自定義異常變量、常量、游標(biāo)、自定義異常 BEGIN SQL語句語句 PL/SQL控制語句控制語句 EXCEPTION

9、錯(cuò)誤發(fā)生時(shí)執(zhí)行的動(dòng)作錯(cuò)誤發(fā)生時(shí)執(zhí)行的動(dòng)作 END；Restricted Confidential.變量聲明變量聲明在大多數(shù)在大多數(shù)PL/SQLPL/SQL塊中，都需要使用變量來包含在程序邏塊中，都需要使用變量來包含在程序邏輯中使用的值。無論需要什么類型的變量，能夠在輯中使用的值。無論需要什么類型的變量，能夠在BEGINBEGIN部分中使用它之前，都必須在塊的部分中使用它之前，都必須在塊的DECLAREDECLARE段中聲段中聲明這些變量。明這些變量。 聲明變量必須提供變量名稱和數(shù)據(jù)類型。聲明變量必須提供變量名稱和數(shù)據(jù)類型。 變量名稱遵循以下約定：變量名稱遵循以下約定： （1 1）以一個(gè)字母字符

10、開頭）以一個(gè)字母字符開頭 （2 2）最多包含）最多包含3030個(gè)字符個(gè)字符 （3 3）可以包含大寫和小寫字母、數(shù)字以及）可以包含大寫和小寫字母、數(shù)字以及_ _、$ $、# #及其它特殊字符。及其它特殊字符。Restricted Confidential.變量聲明變量聲明變量命名在變量命名在PL/SQL中有特別的講究，建議在系統(tǒng)的設(shè)計(jì)階段就要求所有中有特別的講究，建議在系統(tǒng)的設(shè)計(jì)階段就要求所有編程人員共同遵守一定的要求，使得整個(gè)系統(tǒng)的文檔在規(guī)范上達(dá)到要求。編程人員共同遵守一定的要求，使得整個(gè)系統(tǒng)的文檔在規(guī)范上達(dá)到要求。下面是建議的命名方法：下面是建議的命名方法：標(biāo)識(shí)符命名規(guī)則例子程序變量V_na

11、me V_name 程序常量C_Name C_company_name 游標(biāo)變量 Name_cursor Emp_cursor 異常標(biāo)識(shí) E_name E_too_many 表類型 Name_table_type Emp_table_type 表 Name_table Emp_table 記錄類型 Name_record Emp_record SQL*Plus 替代變量 P_name P_sal 綁定變量 G_name G_year_sal Restricted Confidential.基本數(shù)據(jù)類型變量基本數(shù)據(jù)類型變量類型標(biāo)識(shí)符說明number數(shù)字型int 整數(shù)型pls_integer整數(shù)型

12、，產(chǎn)生溢出時(shí)出現(xiàn)錯(cuò)誤binary_integer整數(shù)型，表示帶符號(hào)的整數(shù)char定長(zhǎng)字符型，最大255個(gè)字符varchar2變長(zhǎng)字符型，最大2000個(gè)字符long變長(zhǎng)字符型，最大2GBdate日期型boolean布爾型(true、false、null三者取一)變量類型變量類型Restricted Confidential./ /* *聲明部分，以聲明部分，以declaredeclare開頭開頭* */ /declare declare v_id integer;v_id integer;v_name varchar(20);v_name varchar(20);cursor c_emp is

13、select cursor c_emp is select * * from employee where emp_id=3; from employee where emp_id=3; / /* *執(zhí)行部分，以執(zhí)行部分，以beginbegin開頭開頭* */ /begin begin open c_emp;open c_emp; -打開游標(biāo)打開游標(biāo)looploopfetch c_emp into v_id,v_name;fetch c_emp into v_id,v_name;-從游標(biāo)取數(shù)據(jù)從游標(biāo)取數(shù)據(jù)exit when c_emp%notfound ;exit when c_emp%not

14、found ;end loop ;end loop ;close c_emp;close c_emp; -關(guān)閉游標(biāo)關(guān)閉游標(biāo)dbms_output.PUT_LINE(v_name);dbms_output.PUT_LINE(v_name);/ /* *異常處理部分，以異常處理部分，以exceptionexception開始開始* */ /exceptionexceptionwhen no_data_found then when no_data_found then dbms_output.PUT_LINE(dbms_output.PUT_LINE(沒有數(shù)據(jù)沒有數(shù)據(jù)););end ; end ;

15、 Restricted Confidential.PL/SQLPL/SQL結(jié)構(gòu)結(jié)構(gòu)在塊的聲明部分對(duì)變量進(jìn)行聲明。聲明一個(gè)變量的語法是：在塊的聲明部分對(duì)變量進(jìn)行聲明。聲明一個(gè)變量的語法是：Variable_name CONSTANT type NOT NULL:=value; 如果在變量聲明中使用了如果在變量聲明中使用了CONSTANTCONSTANT，那么該變量必須要被初始化，并且，那么該變量必須要被初始化，并且它的取值不能被改變?yōu)榕c其初始值不同的數(shù)值。常量變量被作為只讀的方式它的取值不能被改變?yōu)榕c其初始值不同的數(shù)值。常量變量被作為只讀的方式進(jìn)行處理。在聲明中可以用關(guān)鍵字進(jìn)行處理。在聲明中可以

16、用關(guān)鍵字DEFAULTDEFAULT替換替換:=:=。DECLARE n1 number(10,2); v1 varchar2(20) DEFAULT 銅陵; v2 varchar2(20):=銅陵; v3 CONSTANT varchar2(20):=銅陵; v4 CONSTANT varchar2(20) DEFAULT 銅陵;BEGIN END;Restricted Confidential. 注意：在聲明部分，每一行只能有一個(gè)變量聲明，如以下的注意：在聲明部分，每一行只能有一個(gè)變量聲明，如以下的聲明是錯(cuò)誤的：聲明是錯(cuò)誤的： DeclareDeclare v_firstname , v_

17、lastname varchar2(20); v_firstname , v_lastname varchar2(20); 正確的聲明為：正確的聲明為： DeclareDeclare v_firstname varchar2(20); v_firstname varchar2(20); v_last_name varchar2(20); v_last_name varchar2(20); Restricted Confidential.另外還可以在變量中加入另外還可以在變量中加入NOT NULLNOT NULL 如果在聲明時(shí)指明如果在聲明時(shí)指明not nullnot null，那么應(yīng)該給該變量

18、賦初值，那么應(yīng)該給該變量賦初值，下面聲明是錯(cuò)誤的：下面聲明是錯(cuò)誤的： DeclareDeclare v_tempvar number not null; v_tempvar number not null; 正確的聲明為：正確的聲明為： DeclareDeclare v_tempvar number not null:=1; v_tempvar number not null:=1;注意：注意：CONSTANT關(guān)鍵字是在變量類型之前列出的，而關(guān)鍵字是在變量類型之前列出的，而NOT NULL是在是在數(shù)據(jù)類型之后列出的。數(shù)據(jù)類型之后列出的。Restricted Confidential.另外還可以

19、在另外還可以在PL/SQLPL/SQL塊中包括塊中包括SQLSQL： declare declare lv_qty_num number(3); lv_qty_num number(3); begin begin select count( select count(* *) into lv_qty_num ) into lv_qty_num from emp; from emp; dbms_output.put_line(lv_qty_num ); dbms_output.put_line(lv_qty_num ); End; End;Restricted Confidential.PL/S

20、QLPL/SQL結(jié)構(gòu)結(jié)構(gòu)變量類型變量類型NUMBER(P,S)P是長(zhǎng)度，是長(zhǎng)度，S是精度。是精度。 長(zhǎng)度是數(shù)值中所有數(shù)字位的個(gè)數(shù)，長(zhǎng)度是數(shù)值中所有數(shù)字位的個(gè)數(shù)， 而精度而精度是小數(shù)點(diǎn)右邊的數(shù)字位的個(gè)數(shù)（如果精度是個(gè)負(fù)數(shù)，那么就由小數(shù)點(diǎn)是小數(shù)點(diǎn)右邊的數(shù)字位的個(gè)數(shù)（如果精度是個(gè)負(fù)數(shù)，那么就由小數(shù)點(diǎn)開始向左邊進(jìn)行計(jì)算數(shù)字位的個(gè)數(shù)）。開始向左邊進(jìn)行計(jì)算數(shù)字位的個(gè)數(shù)）。定義賦值存儲(chǔ)值NUMBER;1234.56781234.5678NUMBER(3);123123NUMBER(3);1234錯(cuò)NUMBER(4,3);123.4567錯(cuò)NUMBER(4,3);1.2345671.235NUMBER(7,2

21、);12345.6712345.67NUMBER(4,-3);12341000NUMBER(4,-1);1234(1235)1230(1240)Restricted Confidential.PL/SQLPL/SQL結(jié)構(gòu)結(jié)構(gòu)變量類型變量類型使用記錄類型使用記錄類型記錄類型是把邏輯相關(guān)的數(shù)據(jù)作為一個(gè)單記錄類型是把邏輯相關(guān)的數(shù)據(jù)作為一個(gè)單元存儲(chǔ)起來。元存儲(chǔ)起來。DECLARE TYPE test_rec IS RECORD( Code VARCHAR2(10), Name VARCHAR2(30) NOT NULL :=a book); V_book test_rec;BEGIN V_book.c

22、ode :=123; V_ :=C+ Programming; DBMS_OUTPUT.PUT_LINE(v_book.code|v_);END;初始化記錄初始化記錄定義記錄型變量定義記錄型變量Restricted Confidential.PL/SQLPL/SQL結(jié)構(gòu)結(jié)構(gòu)變量類型變量類型使用使用%TYPE%TYPE定義一個(gè)變量，其數(shù)據(jù)類型與已經(jīng)定義的某個(gè)數(shù)據(jù)變量的類型相定義一個(gè)變量，其數(shù)據(jù)類型與已經(jīng)定義的某個(gè)數(shù)據(jù)變量的類型相同，或者與數(shù)據(jù)庫(kù)表的某個(gè)列的數(shù)據(jù)類型相同，這時(shí)可以使用同，或者與數(shù)據(jù)庫(kù)表的某個(gè)列的數(shù)據(jù)類型相同，這時(shí)可以使用%TYPE。 TYPE t

23、_Record IS RECORD( T_no emp.empno%TYPE, T_name emp.ename%TYPE, T_sal emp.sal%TYPE ); - 聲明接收數(shù)據(jù)的變量 v_emp t_Record;BEGIN SELECT empno, ename, sal INTO v_emp FROM emp WHERE empno=7788; DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_emp.t_no)|v_emp.t_name|TO_CHAR(v_emp.t_sal);END;使用%TYPE特性的優(yōu)點(diǎn)在于：n所引用的數(shù)據(jù)庫(kù)列的數(shù)據(jù)類型可以不必知道；n所引用

24、的數(shù)據(jù)庫(kù)列的數(shù)據(jù)類型可以實(shí)時(shí)改變。表的某一個(gè)字段的類型表的某一個(gè)字段的類型Restricted Confidential.PL/SQLPL/SQL結(jié)構(gòu)結(jié)構(gòu)變量類型變量類型使用使用%ROWTYPE%ROWTYPE可以將變量類型定義為與具有相同類型的數(shù)據(jù)庫(kù)行?？梢詫⒆兞款愋投x為與具有相同類型的數(shù)據(jù)庫(kù)行。%ROWTYPE將返回一個(gè)基于表定義的類型。將返回一個(gè)基于表定義的類型。DECLARE v_empno emp.empno%TYPE :=&empno; rec emp%ROWTYPE;BEGIN SELECT * INTO rec FROM emp WHERE empno=v_empno

25、; DBMS_OUTPUT.PUT_LINE(姓名:|rec.ename|工資:|rec.sal|工作時(shí)間:|rec.hiredate); END; 使用%ROWTYPE特性的優(yōu)點(diǎn)在于：n所引用的數(shù)據(jù)庫(kù)中列的個(gè)數(shù)個(gè)數(shù)和數(shù)據(jù)類型可以不必知道；n所引用的數(shù)據(jù)庫(kù)中列的個(gè)數(shù)個(gè)數(shù)和數(shù)據(jù)類型可以實(shí)時(shí)改變。Restricted Confidential.PL/SQLPL/SQL結(jié)構(gòu)結(jié)構(gòu)DECLAREDECLARE n number(10); n number(10);BEGINBEGIN n:=1; n:=1; insert into w_test1(bh,n) values(a,n); insert in

26、to w_test1(bh,n) values(a,n); DECLARE DECLARE n number(10); n number(10); BEGIN BEGIN insert into w_test1(bh,n) values(b,n); insert into w_test1(bh,n) values(b,n); n:=100; n:=100; insert into w_test1(bh,n) values(b,n); insert into w_test1(bh,n) values(b,n); END; END; insert into w_test1(bh,n) values

27、(a,n); insert into w_test1(bh,n) values(a,n);END;END; BH N- - a 1 b b 100 a 1CREATE TABLE W_TEST1(BH VARCHAR2(2),N NUMBER(10);變量作用域變量作用域Restricted Confidential.PL/SQLPL/SQL結(jié)構(gòu)結(jié)構(gòu)SELECTSELECTSELECT SELECT 字段名字段名1,1,字段名字段名2 2FROM FROM 表名表名WHERE WHERE 條件條件GROUP BY GROUP BY 字段名字段名ORDER BY ORDER BY 字段名字段名;

28、 ;SELECT BH,SUM(N) S FROM W_TEST1WHERE N8GROUP BY BHORDER BY BHBH N- -a 1a 2a 3a 4a 5a 6a 7a 8a 9C 10000C 10001d 10002d 10002c 10003c 10004d 10004d 10004BH S- -C 20001a 9c 20007d 40012SELECT BH,N INTO v_name,v_amount FROM W_TEST1WHERE N=1Restricted Confidential.PL/SQLPL/SQL結(jié)構(gòu)結(jié)構(gòu)INSERTINSERTDECLARE nu

29、m1 number;BEGIN num1:=8; INSERT INTO W_TEST1(BH,N) VALUES(e,10); INSERT INTO W_TEST1(BH,N) VALUES(e,num1);END;INSERT INTO INSERT INTO 表名表名( (字段字段1,1,字段字段2) VALUES(2) VALUES(值值1,1,值值2);2);Restricted Confidential.PL/SQLPL/SQL結(jié)構(gòu)結(jié)構(gòu)INSERTINSERTINSERT INTO INSERT INTO 表表1( 1( 字段字段1,1,字段字段2)2)SELECT SELECT

30、 字段字段1,1,字段字段2 2FROM FROM 表表2 2WHERE WHERE 條件條件INSERT INTO W_TEST1(BH,N)SELECT BH,NFROM W_TEST1WHERE N5 and n9Restricted Confidential.PL/SQLPL/SQL結(jié)構(gòu)結(jié)構(gòu)DELETEDELETEDELETE FROM 表名表名 WHERE 條件條件;DELETE FROM W_TEST1 WHERE n=2;Restricted Confidential.PL/SQLPL/SQL結(jié)構(gòu)結(jié)構(gòu)動(dòng)態(tài)動(dòng)態(tài)SQLSQLPL/SQL不直接支持不直接支持DDL，因此需要使用動(dòng)態(tài)，因

31、此需要使用動(dòng)態(tài)SQLSET SERVEROUTPUT ONDECLAREv_statement VARCHAR2(500);CURSOR trigger_curISSELECT trigger_nameFROMuser_triggers;BEGINFOR y IN trigger_curLOOP-Build the statementv_statement:=ALTER TRIGGER|y.trigger_name|DISABLE;-Run the statementEXECUTE IMMEDIATE v_statement;END LOOP;END;Restricted Confidenti

32、al.PL/SQLPL/SQL結(jié)構(gòu)結(jié)構(gòu)TABLESPACETABLESPACE/創(chuàng)建臨時(shí)表空間(臨時(shí)表空間,用于臨時(shí)數(shù)據(jù)的存放)create temporary tablespace user_tempTempfile D:oracleoradataOracle9iuser_temp.dbfsize 50mautoextend onnext 50m maxsize 20480mextent management local;一個(gè)表空間就是一片磁盤區(qū)域一個(gè)表空間就是一片磁盤區(qū)域, 它有一個(gè)或者多個(gè)磁盤文件組成它有一個(gè)或者多個(gè)磁盤文件組成,一個(gè)表空間可以容納許多表、索引或者一個(gè)表空間可以容納許多表

33、、索引或者簇等簇等 每個(gè)表空間有一個(gè)初始區(qū)間（每個(gè)表空間有一個(gè)初始區(qū)間（initial extent）用完這個(gè)區(qū)間后再用下一個(gè)，直到用完表空間，這時(shí)候）用完這個(gè)區(qū)間后再用下一個(gè)，直到用完表空間，這時(shí)候需要對(duì)表空間進(jìn)行擴(kuò)展，增加數(shù)據(jù)文件或者擴(kuò)大已經(jīng)存在的數(shù)據(jù)文件。需要對(duì)表空間進(jìn)行擴(kuò)展，增加數(shù)據(jù)文件或者擴(kuò)大已經(jīng)存在的數(shù)據(jù)文件。/創(chuàng)建數(shù)據(jù)表空間(用于用戶數(shù)據(jù)的存放)create temporary tablespace test_dataTempfile D:oracleoradataOracle9iuser_data.dbfsize 50mautoextend onnext 50m maxsize

34、 20480mextent management local;本地管理本地管理(LOCAL): 用二進(jìn)制的方式管理磁盤用二進(jìn)制的方式管理磁盤,有很高的效率有很高的效率,能最大限度的使用能最大限度的使用磁盤磁盤. 同時(shí)能夠自動(dòng)跟蹤記錄臨近空閑空間的情況，避免進(jìn)行空閑區(qū)的合并操作。同時(shí)能夠自動(dòng)跟蹤記錄臨近空閑空間的情況，避免進(jìn)行空閑區(qū)的合并操作。 Restricted Confidential.PL/SQLPL/SQL結(jié)構(gòu)結(jié)構(gòu)TABLETABLECREATE TABLE ST_BJ_JXBZ2(BGDMC VARCHAR2(20), - 報(bào)告單名稱SBLX VARCHAR2(20), - 設(shè)備類型

35、SBXH VARCHAR2(40), - 設(shè)備型號(hào)JXXZ VARCHAR2(10), - 檢修性質(zhì)(年檢/安裝/大修)JSGSSM VARCHAR2(60), - 檢修工時(shí)說明CONSTRAINT PK_ST_BJ_JXBZ2 PRIMARY KEY(BGDMC,SBXH),CONSTRAINT FK_ST_BJ_JXBZ2 FOREIGN KEY(BGDMC) REFERENCES ST_BJ_JXBZ)tablespace CUST_GENERAL_D pctfree 10 -用于指定BLOCK中必需保留的最小空間的比例。 pctused 40 -為一個(gè)百分比數(shù)值，當(dāng)BLOCK中已經(jīng)使用

36、的空間降低到該數(shù)值以下 時(shí)，該BLOCK才是可用的，達(dá)到或是超過這個(gè)數(shù)值的BLOCK是不可 用的。 initrans 1-指定可以并發(fā)操作該表的事務(wù)的數(shù)目。 maxtrans 255指定最大并發(fā)操作該表的事務(wù)處理?xiàng)l目。 storage ( initial 40K next 40K minextents 1 maxextents unlimited pctincrease 0-表示每個(gè)擴(kuò)展Extents的增長(zhǎng)率 );CREATE TABLE 表名（表名（字段名字段名1 數(shù)據(jù)類型，數(shù)據(jù)類型，字段名字段名2 數(shù)據(jù)類型，數(shù)據(jù)類型，CONSTRAINT 約束名約束名 PRIMARY KEY(字段名字段名

37、1,字段名字段名2),CONSTRAINT約束名約束名 FOREIGN KEY(字段名字段名) REFERENCESS 表名表名);Restricted Confidential.PL/SQLPL/SQL結(jié)構(gòu)結(jié)構(gòu)ALTERALTERALTER TABLE W_TEST1 MODIFY(BH VARCHAR2(4),N NUMBER(15);ALTER TABLE 表名表名 MODIFY（字段字段1 數(shù)據(jù)類型，數(shù)據(jù)類型，字段字段2 數(shù)據(jù)類型數(shù)據(jù)類型）；）；Restricted Confidential.PL/SQLPL/SQL結(jié)構(gòu)結(jié)構(gòu)INDEXINDEX（索引）（索引）CREATE INDEX

38、SI_W_TEST1 ON W_TEST1(BH,DH) TABLESPACE TDMIS;CREATE INDEX 索引名索引名 on 表名表名TABLESPACE 表空間名表空間名;Restricted Confidential.PL/SQLPL/SQL結(jié)構(gòu)結(jié)構(gòu)序列序列序列序列(sequence)(sequence)是一種是一種oracleoracle對(duì)象，用來產(chǎn)生唯一的數(shù)字。對(duì)象，用來產(chǎn)生唯一的數(shù)字。其中其中sequence.CURRVALsequence.CURRVAL表示序列當(dāng)前值表示序列當(dāng)前值 sequence.NEXTVALsequence.NEXTVAL使序列增值并返回新的取值

39、使序列增值并返回新的取值CREATE SEQUENCE W_TEST1_S1 INCREMENT BY 1 - 每次加幾個(gè) START WITH 1 - 從1開始計(jì)數(shù) NOMAXVALUE - 不設(shè)置最大值 NOCYCLE - 一直累加，不循環(huán) CACHE 10; Insert into w_test1(bh,n) values(c,W_TEST1_S1.NEXTVAL)Insert into w_test1(bh,n) values(c,W_TEST1_S1.NEXTVAL)Insert into w_test1(bh,n) values(d,W_TEST1_S1.CURRVAL)Inser

40、t into w_test1(bh,n) values(d,W_TEST1_S1.CURRVAL)Restricted Confidential.PL/SQLPL/SQL結(jié)構(gòu)結(jié)構(gòu)同義詞同義詞CREATE PUBLIC SYNONYM CREATE PUBLIC SYNONYM 同義詞名同義詞名 FOR FOR 表名或視圖名表名或視圖名CREATE PUBLIC SYNONYM CX_FXTJ FOR CX_FXTJ;Restricted Confidential.PL/SQLPL/SQL結(jié)構(gòu)結(jié)構(gòu)快照快照CREATE SNAPSHOT KZ_FH_YZDZSTORAGE (INITIAL 100

41、K NEXT 100K PCTINCREASE 0)TABLESPACE TDMISREFRESH COMPLETE -FAST,FORCE START WITH SYSDATE NEXT SYSDATE+1ASSELECT PID,ND,YF,MAX(FH) ZDZFROM KZ_FHGROUP BY PID,ND,YF;EXECUTE DBMS_SNAPSHOT.REFRESH(KZ_FH_YZDZ,C);Oracle數(shù)據(jù)庫(kù)的快照是一個(gè)表，它包含有對(duì)一個(gè)本地或遠(yuǎn)程數(shù)據(jù)庫(kù)上一個(gè)或多個(gè)表數(shù)據(jù)庫(kù)的快照是一個(gè)表，它包含有對(duì)一個(gè)本地或遠(yuǎn)程數(shù)據(jù)庫(kù)上一個(gè)或多個(gè)表或視圖的查詢的結(jié)果。也就是說快照根本的原理

42、就是將本地或遠(yuǎn)程數(shù)據(jù)庫(kù)上的一個(gè)或視圖的查詢的結(jié)果。也就是說快照根本的原理就是將本地或遠(yuǎn)程數(shù)據(jù)庫(kù)上的一個(gè)查詢結(jié)果保存在一個(gè)表中。查詢結(jié)果保存在一個(gè)表中。 Restricted Confidential.PL/SQLPL/SQL結(jié)構(gòu)結(jié)構(gòu)ROLEROLE角色名被授予的權(quán)限CONNECTALTER SESSION，CREATE CLUSTER，CREATE DATABASE LINK，CREATE SEQUENCE，CREATE TABLE，CREATE VIEW，CREATE SYNONYMRESOURCECREATE CLUSTER，CREATE PROCEDURE，CREATE SEQUENCE

43、，CREATE TABLE，CREATE PROCEDUREDBA所有系統(tǒng)權(quán)限CREATE ROLE CREATE ROLE 角色名角色名; ;CREATE CREATE 權(quán)限權(quán)限 ON ON 對(duì)象對(duì)象 TO TO 角色名角色名CREATE ROLE RW_TEST1;GRANT SELECT ON W_TEST1 TO RW_TEST1;GRANT SELECT ON W_TEST1 TO PUBLIC;Restricted Confidential.PL/SQLPL/SQL結(jié)構(gòu)結(jié)構(gòu)USERUSERcreate user 用戶名用戶名 identified by 密碼密碼 default t

44、ablespace 表空間表空間;create user study identified by study default tablespace data_test;grant connect,resource to study; Restricted Confidential.PL/SQLPL/SQL結(jié)構(gòu)結(jié)構(gòu)GRANT GRANT 和和 REVOKEREVOKEGRANT GRANT 權(quán)限權(quán)限 ON ON 對(duì)象對(duì)象 TO TO 用戶用戶/ /角色角色REVOKE REVOKE 權(quán)限權(quán)限 ON ON 對(duì)象對(duì)象 FROM FROM 用戶用戶/ /角色角色對(duì)象權(quán)限對(duì)象類型ALTER表、序列DEL

45、ETE表、視圖EXECUTE過程、函數(shù)、包INDEX表INSERT表、視圖REFERENCES表SELECT表、視圖、序列、快照UPDATE表、視圖grant alter on table to study; revoke alter on table from study;grant connect,resource to study; Restricted Confidential.PL/SQLPL/SQL結(jié)構(gòu)結(jié)構(gòu)COMMITCOMMIT、ROLLBACKROLLBACK、SAVEPOINTSAVEPOINTDELETE FRO W_TEST1WHERE n=10000;SAVEPOINT

46、 A;DELETE FRO W_TEST1WHERE n=10001;SAVEPOINT B;DELETE FRO W_TEST1WHERE n=10002;SAVEPOINT C;ROLLBACK TO BCOMMIT;Restricted Confidential.3.PL/SQL流程控制語句流程控制語句Restricted Confidential.PL/SQLPL/SQL結(jié)構(gòu)結(jié)構(gòu)控制結(jié)構(gòu)控制結(jié)構(gòu)DECLARE n number;BEGIN . IF n50 then . ELSIF n=7 then v:=是;a ELSE v:=否; END IF; IF n=7 THENc v:=是

47、; ELSE v:=否; END IF; END;如果如果n n為一個(gè)確定的值為一個(gè)確定的值則則a,ba,b的結(jié)果是一樣的的結(jié)果是一樣的如果如果n n為為NULLNULL則則a,ba,b的結(jié)果就是不一樣的的結(jié)果就是不一樣的, ,在條件判斷時(shí)要注意在條件判斷時(shí)要注意NULLNULL的情況。的情況。Restricted Confidential.PL/SQLPL/SQL結(jié)構(gòu)結(jié)構(gòu)控制結(jié)構(gòu)控制結(jié)構(gòu)CASE selectorWHEN expression1 THEN result1WHEN expression2 THEN result2WHEN expressionN THEN resultN ELS

48、E resultN+1END; DECLAREV_grade char(1) := UPPER(&p_grade);V_appraisal VARCHAR2(20);BEGINV_appraisal :=CASE v_gradeWHEN A THEN ExcellentWHEN B THEN Very GoodWHEN C THEN GoodELSE No such gradeEND;DBMS_OUTPUT.PUT_LINE(Grade:|v_grade| Appraisal: | v_appraisal);END;Restricted Confidential.PL/SQLPL/SQ

49、L結(jié)構(gòu)結(jié)構(gòu)循環(huán)結(jié)構(gòu)循環(huán)結(jié)構(gòu)DECLARE n number(10);BEGIN n:=1; LOOP insert into w_test1(bh,n) values(a,n); n:=n+1; EXIT WHEN n10; END LOOP;END;DECLARE n number(10);BEGIN n:=1; WHILE n10 LOOP insert into w_test1(bh,n) values(a,n); n:=n+1; END LOOP;END;DECLARE n number(10);BEGIN FOR n IN 1.9 LOOP insert into w_test1(b

50、h,n) values(a,n); END LOOP;END;FORFOR循環(huán)循環(huán)WHILEWHILE循環(huán)循環(huán)簡(jiǎn)單簡(jiǎn)單循環(huán)循環(huán)FOR n IN REVERSE 1.9 LOOPFOR n IN REVERSE 1.9 LOOP( (表示從大到小循環(huán)表示從大到小循環(huán)) )Restricted Confidential.PL/SQLPL/SQL結(jié)構(gòu)結(jié)構(gòu)循環(huán)結(jié)構(gòu)循環(huán)結(jié)構(gòu)GOTO label; . . . . . ./*標(biāo)號(hào)是用括起來的標(biāo)識(shí)符 */DECLARE V_counter NUMBER := 1;BEGIN LOOP DBMS_OUTPUT.PUT_LINE(V_counter的當(dāng)前值為:|

51、V_counter); V_counter := v_counter + 1; IF v_counter 10 THEN GOTO l_ENDofLOOP; END IF; END LOOP; DBMS_OUTPUT.PUT_LINE(V_counter的當(dāng)前值為:|V_counter);END ;當(dāng)程序運(yùn)行到當(dāng)程序運(yùn)行到GOTO I_ENDofLOOPGOTO I_ENDofLOOP語句時(shí)，會(huì)自動(dòng)跳轉(zhuǎn)到語句時(shí)，會(huì)自動(dòng)跳轉(zhuǎn)到標(biāo)簽處。標(biāo)簽處。Restricted Confidential.4.游標(biāo)的使用游標(biāo)的使用Restricted Confidential.為了處理 SQL 語句，ORACLE

52、 必須分配一片叫上下文( context area )的區(qū)域來處理所必需的信息，其中包括要處理的行的數(shù)目，一個(gè)指向語句被分析以后的表示形式的指針以及查詢的活動(dòng)集(active set)。游標(biāo)是一個(gè)指向上下文的句柄( handle)或指針。通過游標(biāo)，PL/SQL可以控制上下文區(qū)和處理語句時(shí)上下文區(qū)會(huì)發(fā)生些什么事情。對(duì)于不同的SQL語句，游標(biāo)的使用情況不同：SQL語句 游標(biāo)非查詢語句 隱式的 結(jié)果是單行的查詢語句 隱式的或顯示的 結(jié)果是多行的查詢語句 顯示的 Restricted Confidential.l定義游標(biāo)：就是定義一個(gè)游標(biāo)名，以及與其相對(duì)應(yīng)的SELECT語句。CURSOR cursor

53、_name(parameter, parameter) IS select_statement;在指定數(shù)據(jù)類型時(shí)，不能使用長(zhǎng)度約束。如NUMBER(4)、CHAR(10) 等都是錯(cuò)誤的。l打開游標(biāo)：OPEN cursor_name(parameter = value, parameter = value);l提取游標(biāo)數(shù)據(jù)：就是檢索結(jié)果集合中的數(shù)據(jù)行，放入指定的輸出變量中。FETCH cursor_name INTO variable_list | record_variable ; 對(duì)該記錄進(jìn)行處理 ，直到活動(dòng)集合中沒有記錄。關(guān)閉游標(biāo)，當(dāng)提取和處理完游標(biāo)結(jié)果集合數(shù)據(jù)后，應(yīng)及時(shí)關(guān)閉游標(biāo)，以釋放該

54、游標(biāo)所占用的系統(tǒng)資源。CLOSE cursor_name；游標(biāo)屬性 %FOUND 布爾型屬性，當(dāng)最近一次讀記錄時(shí)成功返回,則值為TRUE； %NOTFOUND 布爾型屬性，與%FOUND相反； %ISOPEN 布爾型屬性，當(dāng)游標(biāo)已打開時(shí)返回 TRUE； %ROWCOUNT 數(shù)字型屬性，返回已從游標(biāo)中讀取的記錄數(shù)。 Restricted Confidential.DECLAREDeptRec dept%ROWTYPE;Dept_name dept.dname%TYPE;Dept_loc dept.loc%TYPE;CURSOR c1 IS SELECT dname, loc FROM dept

55、WHERE deptno = 30;CURSOR c2(dept_no NUMBER DEFAULT 10) ISSELECT dname, loc FROM dept WHERE deptno = dept_no;CURSOR c3(dept_no NUMBER DEFAULT 10) IS SELECT * FROM dept WHERE deptno 20); -或者是或者是 OPEN c3(20);LOOPFETCH c3 INTO deptrec;EXIT WHEN c3%NOTFOUND;DBMS_OUTPUT.PUT_LINE(deptrec.deptno|-|deptrec.d

56、name |-|deptrec.loc);END LOOP;CLOSE c3;END;Restricted Confidential.顯式游標(biāo)主要是用于對(duì)查詢語句的處理，尤其是在查詢結(jié)果為多條記錄的情況下；而對(duì)于非查詢語句，如修改、刪除操作，則由ORACLE 系統(tǒng)自動(dòng)地為這些操作設(shè)置游標(biāo)并創(chuàng)建其工作區(qū)，這些由系統(tǒng)隱含創(chuàng)建的游標(biāo)稱為隱式游標(biāo)，隱式游標(biāo)的名字為SQL，這是由ORACLE 系統(tǒng)定義的。對(duì)于隱式游標(biāo)的操作，如定義、打開、取值及關(guān)閉操作，都由ORACLE 系統(tǒng)自動(dòng)地完成，無需用戶進(jìn)行處理。用戶只能通過隱式游標(biāo)的相關(guān)屬性，來完成相應(yīng)的操作。在隱式游標(biāo)的工作區(qū)中，所存放的數(shù)據(jù)是與用戶自定義的

57、顯示游標(biāo)無關(guān)的、最新處理的一條SQL 語句所包含的數(shù)據(jù)。格式調(diào)用為： SQL%注：INSERT, UPDATE, DELETE, SELECT 語句中不必明確定義游標(biāo)。隱式游標(biāo)屬性SQL%FOUND 布爾型屬性,當(dāng)最近一次讀記錄時(shí)成功返回，則值為 true；SQL%NOTFOUND 布爾型屬性,與%found相反；SQL%ROWCOUNT 數(shù)字型屬性, 返回已從游標(biāo)中讀取得記錄數(shù)；SQL%ISOPEN 布爾型屬性, 取值總是FALSE。SQL命令執(zhí)行完畢立即關(guān) 閉隱式游標(biāo)。 Restricted Confidential.游標(biāo)游標(biāo)隱式游標(biāo)用于處理INSERT、UPDATE、DELETE和單行的

58、SELECT .INTO語句因?yàn)镾QL游標(biāo)是通過PL/SQL引擎打開和關(guān)閉的，所以O(shè)PEN、FETCH和CLOSE命令是無關(guān)的。下面的語句在UPDATE語句沒有找到任何行的時(shí)候就將執(zhí)行一條INSERT語句。BEGIN UPDATE W_TEST1 SET N=200 WHERE BH=G; IF SQL%NOTFOUND THEN INSERT INTO W_TEST1(BH,N) VALUES(G,200) END IF;END;Restricted Confidential.PL/SQL語言提供了游標(biāo)FOR循環(huán)語句，自動(dòng)執(zhí)行游標(biāo)的OPEN、FETCH、CLOSE語句和循環(huán)語句的功能；當(dāng)進(jìn)入

59、循環(huán)時(shí)，游標(biāo)FOR循環(huán)語句自動(dòng)打開游標(biāo)，并提取第一行游標(biāo)數(shù)據(jù)，當(dāng)程序處理完當(dāng)前所提取的數(shù)據(jù)而進(jìn)入下一次循環(huán)時(shí)，游標(biāo)FOR循環(huán)語句自動(dòng)提取下一行數(shù)據(jù)供程序處理，當(dāng)提取完結(jié)果集合中的所有數(shù)據(jù)行后結(jié)束循環(huán)，并自動(dòng)關(guān)閉游標(biāo)。格式：FOR index_variable IN cursor_namevalue, value LOOP- 游標(biāo)數(shù)據(jù)處理代碼END LOOP;其中：index_variable為游標(biāo)FOR 循環(huán)語句隱含聲明的索引變量，該變量為記錄變量，其結(jié)構(gòu)與游標(biāo)查詢語句返回的結(jié)構(gòu)集合的結(jié)構(gòu)相同。在程序中可以通過引用該索引記錄變量元素來讀取所提取的游標(biāo)數(shù)據(jù)，index_variable中各元素的

60、名稱與游標(biāo)查詢語句選擇列表中所制定的列名相同。如果在游標(biāo)查詢語句的選擇列表中存在計(jì)算列，則必須為這些計(jì)算列指定別名后才能通過游標(biāo)FOR 循環(huán)語句中的索引變量來訪問這些列數(shù)據(jù)。注：不要在程序中對(duì)游標(biāo)進(jìn)行人工操作；不要在程序中定義用于控制FOR 循環(huán)的記錄。 PL/SQL還允許在游標(biāo)FOR循環(huán)語句中使用子查詢來實(shí)現(xiàn)游標(biāo)的功能。 FOR i IN (SELECT name, id FROM testtable) LOOP Restricted Confidential.DECLARECURSOR c1(dept_no NUMBER DEFAULT 10) ISSELECT dname, loc FROM dept WHERE d