PL/Sql學(xué)習(xí)筆記

聲明變量 一、概述：
1、PLSQL 塊結(jié)構(gòu)：
DECLARE --- 可選
變量聲明定義
BEGIN ---- 必選
SQL 和PLSQL 語句
EXCEPTION ---- 可選
錯(cuò)誤處理
END；---- 必選

constant ----表時(shí)聲明的是常量
:= ----賦值

二、實(shí)例：
declare
vjob varchar(9);
v_count number:=0;
vtotal date:=sysdate +7;
c_tax constant number(3,2):=8.25;
v_valid boolean not null:=true;
begin
select sysdate into vtotal from dual;
end;
/
上例中，如果沒有這個(gè)SELECT語句，會如何？
出錯(cuò)，說明必須有STATEMENTS
如果： select sysdate from dual into vtotal ;
同樣，也不行。而且變量與賦值的類型要匹配。
三、%TYPE的屬性
聲明一個(gè)變量使之與數(shù)據(jù)庫某個(gè)列的定義相同或與另一個(gè)已經(jīng)定義過的變量相同
所以%TYPE要作為列名的后綴：如：
v_last_name s_emp.last_name%TYPE;
v_first_name s_emp.first_name%TYPE; --這樣做的好處是我們不必去知曉此列的類型與定義
或：v_balance NUMBER(7,2);
v_minimum_balance v_balance%TYPE := 10;
四、聲明一個(gè)布爾類型的變量
1 只有TRUE、FALSE、NULL可以賦值給BOOLEAN變量
2 此變量可以接邏輯運(yùn)算符NOT、AND、OR。
3、變量只能產(chǎn)生TRUE、FALSE、NULL。
實(shí)例：
VSAL1：=50000；
VSQL2：=60000；
VCOMMSAL BOOLEAN：=(VSAL1--其實(shí)是把TRUE賦值給此變量。
五、LOB 類型的變量
共有CLOB、BLOB、BFILE、NCLOB幾種，這里不做為重點(diǎn)。
六：使用HOST VARIABLES
SQL> variable n number
SQL> print n
：n=v_sal /12;
:n這個(gè)加了：前綴的變量不是PLSQL變量，而是HOST。

控制結(jié)構(gòu)

一。IF..THEN
語法：
IF condition THEN
Statements 1;
Statements 2;
....
END IF

二。 IF..THEN...ELSE
語法:
IF condition THEN
Statements 1;
Statements 2;
....
ELSE
Statements 1;
Statements 2;
....
END IF

三。 IF..THEN..ELSIF
語法：
IF condition1 THEN
statement1;
ELSIF condition2 THEN
statement2;
ELSIF condition3 THEN
statement3;
ELSE
statement4;
END IF;
statement5;

循環(huán)控制

一。循環(huán)控制的基本形式是LOOP語句，LOOP和END LOOP之間的語句將無限次的執(zhí)行。在使用LOOP語句時(shí)必須使用EXIT語句，強(qiáng)制循環(huán)結(jié)束。

LOOP語句的語法如下：

LOOP
statements;
END LOOP

例如：
X:=100;
LOOP
X:=X+10;
IF X>1000 THEN
EXIT;
END IF
END LOOP;
Y:=X;

此時(shí)Y的值是1010.

WHILE..LOOP

WHILE..LOOP有一個(gè)條件與循環(huán)相聯(lián)系，如果條件為TRUE,則執(zhí)行循環(huán)體內(nèi)的語句，如果結(jié)果為FALSE,則結(jié)束循環(huán)。

X:=100;
WHILE X<=1000 LOOP
X:=X+10;
END LOOP;
Y=X;

FOR...LOOP

語法：

FOR counter IN [REVERSE] start_range....end_range LOOP
statements;
END LOOP;

LOOP和WHILE循環(huán)的循環(huán)次數(shù)都是不確定的，F(xiàn)OR循環(huán)的循環(huán)次數(shù)是固定的，counter是一個(gè)隱式聲明的變量，他的初始值是start_range,第二個(gè)值是start_range+1,直到end_range,如果start_range等于end _range,那么循環(huán)將執(zhí)行一次。如果使用了REVERSE關(guān)鍵字，那么范圍將是一個(gè)降序。

X:=100;
FOR v_counter in 1..10 loop
x:=x+10;

end loop
y:=x;

如果要退出for循環(huán)可以使用EXIT語句。

標(biāo)簽

用戶可以使用標(biāo)簽使程序獲得更好的可讀性。程序塊或循環(huán)都可以被標(biāo)記。標(biāo)簽的形式是<>。

1.標(biāo)記程序塊

<>
[DECLARE]
... ... ...
BEGIN
........
[EXCEPTION]
.......
END label_name

2.標(biāo)記循環(huán)

<>
LOOP
.........
<>
loop
..........
<>
loop
....

EXIT outer_loop WHEN v_condition=0;
end loop innermost_loop;
..........
END LOOP inner_loop;
END LOOP outer_loop;

GOTO語句

語法： GOTO LABEL;

執(zhí)行GOTO語句時(shí)，控制會立即轉(zhuǎn)到由標(biāo)簽標(biāo)記的語句。PL/SQL中對GOTO語句有一些限制，對于塊、循環(huán)、IF語句而言，從外層跳轉(zhuǎn)到內(nèi)層是非法的。

X ：=100;
FOR V_COUNTER IN 1..10 LOOP
IF V_COUNTER =4 THEN
GOTO end_of_loop
END IF
X:=X+10;
<>
NULL
END LOOP
Y:=X;

注意：NULL是一個(gè)合法的可執(zhí)行語句。

嵌套

程序塊的內(nèi)部可以有另一個(gè)程序塊這種情況稱為嵌套。嵌套要注意的是變量，定義在最外部程序塊中的變量可以在所有子塊中使用，如果在子塊中定義了與外部程序塊變量相同的變量名，在執(zhí)行子塊時(shí)將使用子塊中定義的變量。子塊中定義的變量不能被父塊引用。同樣GOTO語句不能由父塊跳轉(zhuǎn)道子塊中，反之則是合法的。

=================================================

set serveroutput on --設(shè)置環(huán)境變量serveroutput為打開狀態(tài)，以便可以在SQL*Plus等中輸出結(jié)果
dbms_output.PUT_LINE() --輸出變量等的值

=================================================

存儲過程

CREATE[OR REPLACE] PROCEDURE<過程名>
[<參數(shù)列表>]IS|AS
[<局部變量聲明>]
BEGIN
<過程體>
END[<過程名>];
參數(shù)聲明的格式如下：
<參數(shù)名>[IN|OUT|IN OUT]<數(shù)據(jù)類型>[:=<初始值>]
IN表示此參數(shù)接受過程外傳遞來的值；
OUT表示此參數(shù)將在過程中被賦值，并傳遞到過程體外；
INT OUT表示此參數(shù)同時(shí)具有IN和OUT的特性。
前面已經(jīng)了解了關(guān)于PL/SQL編程的基礎(chǔ)，本文將結(jié)合一個(gè)案例來加深對這些知識點(diǎn)的理解。

　　一． 案例介紹（轉(zhuǎn)自網(wǎng)絡(luò)）

　　某數(shù)據(jù)庫有兩張表，是關(guān)于某公司員工資料、薪水和部門信息的，它們分別是emp表和dept表，兩張表的結(jié)構(gòu)如下：

　　要求如下：

　　1、按照上表結(jié)構(gòu)建立相應(yīng)的表，并每張表寫入5組合法數(shù)據(jù)。

　　2、操縱相關(guān)表，使得“技術(shù)部”的員工的薪水上漲20%。

　　3、建立日志，追蹤薪水變動(dòng)情況。

　　4、建立測試包。

　　二． 案例的分析與實(shí)現(xiàn)

　　從前面案例的介紹不難看出，要求1考察點(diǎn)為基本SQL語句；要求2主要考察復(fù)合查詢；要求3是考察觸發(fā)器的應(yīng)用；要求4的考察面相對多一些，不僅考察了包的創(chuàng)建，而且也考察了在PL/SQL中的測試方法。了解了這些考察的知識點(diǎn)，就可以一一去解決。

　　要求1：

　　首先根據(jù)前面表的結(jié)構(gòu)可以創(chuàng)建兩張表：

　　——?jiǎng)?chuàng)建員工表

create table emp (emp_id number(5), emp_name varchar2(20), emp_salary number(4));

　　——部門表

create table dept (dept_id number(3), dept_name varchar2(20), emp_id number(5));

　　建立了表之后就可以往表里面寫數(shù)據(jù)了，這里把添加表記錄的代碼寫入到相應(yīng)的存儲過程。

/*給emp表添加記錄的存儲過程*/
create or replace procedure ins_table_emp(p_emp_id number,p_emp_name varchar2,p_emp_salary number) as
v_emp_id number:=p_emp_id;
v_emp_name varchar2(20):=p_emp_name;
v_emp_salary number:=p_emp_salary;
begin
　insert into emp values (v_emp_id,v_emp_name,v_emp_salary);
end ins_table_emp;

/*給dept表添加記錄的存儲過程*/
create or replace procedure ins_table_dept(p_dept_id number,p_dept_name varchar2,p_emp_id number) as
　v_dept_id number:=p_dept_id;
　v_dept_name varchar2(20):=p_dept_name;
　v_emp_id number:=p_emp_id;
begin
　insert into dept values (v_dept_id,v_dept_name,v_emp_id);
end ins_table_emp;

/*調(diào)用相應(yīng)的存儲過程實(shí)現(xiàn)記錄添加*/
begin
　ins_table_emp(10000,'',4000);
　ins_table_emp(10001,'??èy',2300);
　ins_table_emp(10002,'3?t',3500);
　ins_table_emp(10003,'à???',3500);
　ins_table_emp(10004,'á?ò?',3500);

　ins_table_dept(111,'DD?t2?',10000);
　ins_table_dept(111,'DD?t2?',10001);
　ins_table_dept(111,'DD?t2?',10002);
　ins_table_dept(112,'??ê?2?',10003);
　ins_table_dept(113,'êD3?2?',10004);
end;

　　要求2：

　　給指定部門的員工加薪，這實(shí)際上是一個(gè)復(fù)合查詢，首先需要把所有該部門的員工塞選出來，然后對這些員工的薪水進(jìn)行相應(yīng)的改動(dòng)。依照這一思路，代碼如下：（需要注意的是：將要加薪的部門作為參數(shù)，這樣的存儲過程更有靈活性。）

create or replace procedure add_salary(p_dept_name varchar2) as
v_dept_name varchar2(20):=p_dept_name;
begin
　update emp set emp.EMP_SALARY=emp.EMP_SALARY*1.2 where emp.EMP_ID in (select emp.EMP_ID

from emp,dept where emp.EMP_ID=dept.EMP_ID and dept.DEPT_ID='??ê?2?');
end add_salary;

　　要求3：

　　建立日志對薪水的變動(dòng)情況形成一個(gè)追蹤，也就是說，如果對某個(gè)職員的薪水進(jìn)行變更就應(yīng)該將其相應(yīng)的變更記錄全部記下來。如果對emp表的salary字段創(chuàng)建一個(gè)觸發(fā)器，來監(jiān)視對salary的更改，把每次更改進(jìn)行記錄，這樣就達(dá)到了要求3的目的了。

create or replace trigger print_salary_change 　
before delete or insert or update on emp　　--觸發(fā)事件
for each row　　　　　　　　　　　　 -- 每修改一行都需要調(diào)用此過程

declare 　　　　　　　　　　--只有觸發(fā)器的聲明需要declare，過程和函數(shù)都不需要
salary_balance number;
begin
--:new 與:old分別代表該行在修改前和修改后的記錄
salary_balance=:new.salary=:old.salary;
dbms_output.PUT_LINE('old salary is: '|| :old.salary);
dbms_output.PUT_LINE('old salary is: '|| :new.salary);
dbms_output.PUT_LINE('old salary is: '|| to_char(salary_balance));
end print_salary_change;

　　要求4：

　　與其他語言（c/c++等）相比，PL/SQL的測試有其不同之處，歸納下來有三種方法：

　　1、使用DBMS_OUTPUT包的PUT_LINE方法來顯示中間變量，以此來觀察程序是否存在邏輯錯(cuò)誤。

　　2、插入測試表的方法。即創(chuàng)建一個(gè)臨時(shí)的中間表，然后把所有涉及到的中間變量的結(jié)果都作為記錄插入到中間表中，這樣可以查詢表中的結(jié)果來觀察程序的執(zhí)行情況。

　　3、使用異常處理手段，對可疑的程序段使用begin … end ，然后可以在exception里進(jìn)行異常捕獲處理。

　　這里準(zhǔn)備使用第二種方法來建立一個(gè)測試包，PL/SQL里包的概念類似于面向?qū)ο罄锏念惖母拍?，包將一組操作和屬性封裝在一起，不僅增強(qiáng)了程序的模塊化，而且由于封裝了更多的操作和屬性而提高了執(zhí)行效能。建立一個(gè)PL/SQL需要兩個(gè)步驟：首先要建立包頭，類似于建立一個(gè)類的頭文件，里面主要對包中的過程，函數(shù)和變量的聲明；第二部分主要是包體部分，實(shí)現(xiàn)前面聲明的過程和函數(shù)，另外還需要對包進(jìn)行初始化等工作。

　　根據(jù)這一思路，建立測試包如下：

/*包頭部分*/
create or replace package debug as
procedure debug(v_description varchar2,v_valueOfvariable varchar2)
　procedure reset;
　v_numberOfLine number;
end debug;
/*包體部分*/
create or replace package body debug as
procedure debug(v_description varchar2,v_valueOfvariable varchar2) is
begin
　insert into debugtable
　values(v_numberOfLine,v_description,v_valueOfvariable);
　v_numberOfLine:=v_numberOfLine+1;
end debug;
procedure reset is
begin
　v_numberOfLine:=1;
　delete from debugtable;
end reset;
/*初始化部分*/
begin
　reset;
end debug;

　　三．小結(jié)

　　綜合前面對4個(gè)問題的解答，基本把PL/SQL的主要部分融會進(jìn)來了，雖然很多地方只是涉及到比較粗淺的層次，但是有了這一基礎(chǔ)，深入下去也是不難的。

　　總之，PL/SQL編程與其他語言編程有一定的區(qū)別，讀者只有把握好其特點(diǎn)才能更好的掌握數(shù)據(jù)庫開發(fā)

的方面知識。

觸發(fā)器(Triggers)

可以設(shè)置為在觸發(fā)器事件之前或之后觸發(fā)或執(zhí)行。能夠觸發(fā)觸發(fā)器事件的事件包括下面幾種：

DML事件 DDL事件 數(shù)據(jù)庫事件
DML事件觸發(fā)器可以是語句或行級觸發(fā)器。DML語句觸發(fā)器在觸發(fā)語句之前或之后觸發(fā)DML行級觸發(fā)器在語句影響的行變化之前或之后觸發(fā)。用戶可以給單一事件和類型定義多個(gè)觸發(fā)器，但沒有任何方法可以增強(qiáng)多觸發(fā)器觸發(fā)的命令。

觸發(fā)器事件：
INSERT 當(dāng)向表或視圖插入一行時(shí)觸發(fā)觸發(fā)器
UPDATE 更新表或視圖中的某一行時(shí)觸發(fā)觸發(fā)器
DELETE 從表或視圖中刪除某一行時(shí)觸發(fā)觸發(fā)器
CREATE 當(dāng)使用CREATE語句為數(shù)據(jù)庫或項(xiàng)目增加一個(gè)對象時(shí)觸發(fā)觸發(fā)器
ALTER 當(dāng)使用ALTER語句為更改一個(gè)數(shù)據(jù)庫或項(xiàng)目的對象時(shí)觸發(fā)觸發(fā)器
DROP 當(dāng)使用DROP語句刪除一個(gè)數(shù)據(jù)庫或項(xiàng)目的對象時(shí)觸發(fā)觸發(fā)器
START 打開數(shù)據(jù)庫時(shí)觸發(fā)觸發(fā)器，在事件后觸發(fā)
SHUTDOWN 關(guān)閉數(shù)據(jù)庫時(shí)觸發(fā)，事件前觸發(fā)
LOGON 當(dāng)一個(gè)會話建立時(shí)觸發(fā)，事件前觸發(fā)
LOGOFF 當(dāng)關(guān)閉會話時(shí)觸發(fā)，事件前觸發(fā)
SERVER 服務(wù)器錯(cuò)誤發(fā)生時(shí)觸發(fā)觸發(fā)器，事件后觸發(fā)

創(chuàng)建觸發(fā)器的語法如下：
CREATE [OR REPLACE] TRIGGER trigger_name
{before|after|instead of} event
ON {table_or_view_name|DATABASE}
[FOR EACH ROW[WHEN condition]]
trigger_body
trigger_name ---觸發(fā)器名
event ---觸發(fā)事件
table_or_view_name ---表名/視圖名
condition ---條件達(dá)式
trigger_body---PL-SQL程序主體
只有DML觸發(fā)器（INSERT、UPDATE、DELETE)語句可以使用INSTEAD OF觸發(fā)器并且只有表的DML觸發(fā)器可以是BEFORE或AFTER觸發(fā)器。

將觸發(fā)器設(shè)置為禁用或啟用使用ALTER TRIGGER語句：
ALTER TRIGGER trigger_name ENABLE;
ALTER TRIGGER trigger_name DISABLE;
要禁用或啟用表的所有觸發(fā)器，使用ALTER TABLE語句
ALTER TRIGGER table_name DISABLE ALL TRIGGER;
ALTER TRIGGER table_name ENABLE ALL TRIGGER;
刪除觸發(fā)器使用DROP TRIGGER
DROP TRIGGER trigger_name;

實(shí)例：
創(chuàng)建一個(gè)觸發(fā)器MyTrigger,它的作用是當(dāng)表HOTELMAN.RoomType中TypeId列的值發(fā)生改變時(shí)，自動(dòng)更新表HOTELMAN.Room中的TypeId列的值，從而保證數(shù)據(jù)的完整性。

CREATE OR REPLACE TRIGGER HOTELMAN.MyTrigger
AFTER UPDATE ON HOTELMAN.RoomType
FOR EACH ROW
BEGIN
UPDATE HOTELMAN.Room SET TypeId = :new.TypeId
WHERE TypeId = :old.TypeId;
END;

這里的:new和:old分別是兩個(gè)虛擬的表，分別表示執(zhí)行INSERT,UPDATE,DELETE等操作后的新表和執(zhí)行這些操作之前的舊表。