程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle學習（11）：PLSQL程序設計

Oracle學習（11）：PLSQL程序設計

編輯：Oracle教程

Oracle學習（11）：PLSQL程序設計

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) 基本數據類型變量
1. 基本數據類型
Number 數字型
Int 整數型
Pls_integer 整數型，產生溢出時出現錯誤
Binary_integer 整數型，表示帶符號的整數
Char 定長字符型，最大255個字符
Varchar2 變長字符型，最大2000個字符
Long 變長字符型，最長2GB
Date 日期型
Boolean 布爾型（TRUE、FALSE、NULL三者取一）
在PL/SQL中使用的數據類型和Oracle數據庫中使用的數據類型，有的含義是完全一致的，
有的是有不同的含義的。
2. 基本數據類型變量的定義方法
變量名類型標識符 [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 select ename from emp;

2.打開光標： openc1; (打開光標執行查詢)
3.取一行光標的值：fetch c1 into pjob;(取一行到變量中)
4.關閉光標： close c1;(關閉游標釋放資源)
注意：上面的pjob必須與emp表中的job列類型一致： ?定義：pjobemp.empjob%type;

示例

帶參數的光標

定義語句：

cursor c2(jobc varchar2)

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;
/

示例2

l用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;