Oracle學習（11）：PLSQL程式設計，oracleplsql
PL/SQL程式結構及組成什麼是PL/SQL?•PL/SQL（Procedure Language/SQL）•PLSQL是Oracle對sql語言的過程化擴充•指在SQL命令語言中增加了過程處理語句（如分支、迴圈等），使SQL語言具有過程處理能力。
SQL優點互動式非過程化；資料操縱功能強；自動導航語句簡單；調試容易使用方便。
把SQL語言的資料操縱能力與過程語言的資料處理能力結合起來，使得PLSQL面向過程但比過程語言簡單、高效、靈活和實用。
常用的結合語言lPlsql(oracle),Transact-sql(SQLserver)
PL/SQL的程式結構
declare
說明部分 （變數說明，游標申明，例外說明〕
begin
語句序列 （DML語句〕…
exception
例外處理語句
End;
/
變數和常量的說明
l說明變數 （char,varchar2, date, number, boolean,long)基礎資料型別 (Elementary Data Type)變數
1. 基礎資料型別 (Elementary Data Type)
Number 數字型
Int 整數型
Pls_integer 整數型，產生溢出時出現錯誤
Binary_integer 整數型，表示帶符號的整數
Char 定長字元型，最大255個字元
Varchar2 變長字元型，最大2000個字元
Long 變長字元型，最長2GB
Date 日期型
Boolean 布爾型（TRUE、FALSE、NULL三者取一）
在PL/SQL中使用的資料類型和Oracle資料庫中使用的資料類型，有的含義是完全一致的，
有的是有不同的含義的。
2. 基礎資料型別 (Elementary Data Type)變數的定義方法
變數名 類型標識符 [not null]:=值;
declare
age number(3):=26; --長度為3，初始值為26
begin
commit;
end;
其中，定義常量的文法格式：
常量名 constant 類型標識符 [not null]:=值;
declare
pi constant number(9):=3.1415926;--為pi的數字型常量，長度為9，初始值為3.1415926
begin
commit;
end;
if語句三種if語句
1. IF 條件 THEN 語句1;
語句2;
END IF;
2. IF 條件 THEN 語句序列1；
ESLE 語句序列 2；
END IF；
3. IF 條件 THEN 語句;
ELSIF 語句 THEN 語句;
ELSE 語句;
END IF;
小知識：擷取從鍵盤輸入的資料•從鍵盤輸入：
accept num prompt '請輸入一個數字';
•得到鍵盤輸入的值：
pnum number := #
迴圈語句三種迴圈語句
1. WHILE total <= 25000 LOOP
.. .
total : = total + salary;
END LOOP;
2. Loop
EXIT [when 條件];
……
End loop
3. FOR I IN 1 . . 3 LOOP
語句序列 ;
END LOOP ;
游標(Cursor)==ResultSet說明游標文法：
1.定義游標
CURSOR 游標名 [ (參數名 資料類型[,參數名 資料類型]...)]
IS SELECT 語句;
用於儲存一個查詢返回的多行資料
例如：
cursorc1 is selectename from emp;
2.開啟游標： openc1; (開啟游標執行查詢)
3.取一行游標的值：fetch c1 into pjob;(取一行到變數中)
4.關閉游標： close c1;(關閉遊標釋放資源)
注意： 上面的pjob必須與emp表中的job列類型一致：•定義：pjobemp.empjob%type;
樣本
帶參數的游標
定義語句：
cursor c2(jobcvarchar2)
is
select ename,salfrom emp
where job=jobc;
執行語句:
Open c2(‘clerk’);
Oracle的異常處理例外
l例外是程式設計語言提供的一種功能，用來增強程式的健壯性和容錯性。
系統定義例外•No_data_found (沒有找到資料)•Too_many_rows (select …into語句匹配多個行)•Zero_Divide ( 被零除)•Value_error (算術或轉換錯誤)•Timeout_on_resource (在等待資源時發生逾時)
使用者定義例外及處理例外
DECLARE
My_job char(10);
v_sal emp.sal%type;
No_data exception;
cursor c1 is select distinct jobfrom emp order by job;
begin
open c1;
Fetch c1 into v_job;
IF c1%notFOUND then raiseno_data;
end if;
…
EXCEPTION
WHEN no_data THEN insert into empvalues(‘fetch語句沒有獲得資料或資料已經處理完');
END;
在declare節中定義例外 •out_of exception ;在可行語句中引起例外 •raise out_of ；在Exception節處理例外•when Out_of then …
兩種賦值語句利用:=賦值
lvar1:='this is a argument';
lemp_rec.sal:=sal*2 +nvl(comm,0);
lsum_sal:=sum_sal+v_sal;
利用into賦值lFETCH c1 INTO e_eno , e_sal ;
commit語句l結束當前事務,使當前事務所執行的全部修改永久化。
在執行完DML語句之後一定不要忘記在代碼後面加上commit來提交！
注釋兩種注釋格式：
-- This is a comment
或
/* This is a comment */
執行個體樣本樣本1為員工長工資。從最低工資調起每人長10％，但工資總額不能超過5萬元,請計算長工資的人數和長工資後的工資總額，並輸出輸出長工資人數及工資總額。
可能用到的SQL語句：•select empno,sal from emp order by sal ;•select sum(sal) into s_sal from emp;
答案：
/*
為員工長工資。從最低工資調起每人長10％，但工資總額不能超過50萬元,
請計算長工資的人數和長工資後的工資總額，並輸出輸出長工資人數及工資總額。
先寫出可能用到的sql語句
select empno,sal from emp order by sal;
select sum(sal) from emp;
*/
set serveroutput on
declare
cursor c1 is select empno,sal from emp order by sal;
salTotal NUMBER; --記錄工資總額
empCount NUMBER := 0; --漲工資的人數
pempno emp.empno% TYPE; --記錄員工的編號
psal emp.sal%type; --記錄員工的工資
begin
--得到當前總工資
select sum(sal) into salTotal from emp;
--開啟遊標
open c1;
--執行迴圈
while salTotal <= 50000
loop
fetch c1 into pempno, psal;--取出一條記錄
exit when c1%notfound;
update emp set sal = sal * 1.1 where empno = pempno; --執行加薪
--記錄漲工資後的總額
salTotal := salTotal + psal*0.1;
--記錄漲工資的人數
empCount := empCount + 1;
end loop;
close c1;
commit;
dbms_output.put_line('漲工資人數:' || empCount || ' 工資總額:' || salTotal);
end;
/
樣本2l用PL/SQL語言編寫一程式，實現按部門分段（6000以上、(6000，3000)、3000元以下)統計各工資段的職工人數、以及各部門的工資總額(工資總額中不包括獎金)，參考如下格式：
部門 小於3000數 3000-6000 大於6000 工資總額
10 2 1 0 8750
20 3 2 0 10875
30 6 0 0 9400
l提示：可以建立一張新表用於儲存資料
createtable msg1
(deptno number,
emp_num1 number,
emp_num2 number,
emp_num3 number,
sum_salnumber);
/*
用PL/SQL語言編寫一程式，實現按部門分段（6000以上、(6000，3000)、3000元以下)統計各工資段的職工人數、
以及各部門的工資總額(工資總額中不包括獎金)
先寫出可能用到的查詢語句
a = select distinct deptno from dept;
select sal from emp where deptno= a中的某個值;
關於結果的輸出：
1. 直接輸出在螢幕上
2. 輸出到一張表中
create table salcount
(deptno number, --部門號
sg1 int, --3000以下的人數
sg2 int, -- 3000~6000的人數
sg3 int -- 6000以上的人數
);
*/
declare
--定義兩個遊標儲存結果
cursor c1 is select distinct deptno from dept;
cursor c2(pdno number) is select sal from emp where deptno=pdno;
--定義三個變數用於儲存每個部門三個工資段的人數
count1 NUMBER;
count2 number;
count3 number;
--記錄c1遊標中的部門號
pdeptno dept.deptno% TYPE;
--記錄c2遊標中的薪水值
psal emp.sal% TYPE;
begin
open c1;--開啟c1 獲得所有部門號
loop
fetch c1 into pdeptno;--取一個部門號
exit when c1%notfound;
--計數器清零
count1 := 0;
count2 := 0;
count3 := 0;
--得到該部門的所有員工
open c2(pdeptno);
loop
fetch c2 into psal; --得到該員工的工資
exit when c2%notfound;
if psal <=3000 then count1 := count1 + 1;
elsif psal > 3000 and psal <=6000 then count2 := count2 + 1;
else count3 := count3 + 1;
end if;
end loop;
close c2;
--儲存該部門的統計結果
insert into salcount values(pdeptno,count1,count2,count3);
commit;
end loop;
close c1;
end;
/
