前言
本文
PL/SQL
(Procedure Language,過程化語言)SQL 1999各大資料庫廠商通用的一種結構化語言 PL/SQL只支援Oracle資料庫
基本文法
多行注釋 /* */ 單行注釋 --
PLSQL程式塊
結構
[declare]
定義變數
begin
過程語句
[exception]
處理 異常
end;
例子 輸出helloworld
begin
dbms_output.put_line('hello');
end;
--如果過程執行完沒有輸出，必須將伺服器輸出開啟
執行
SQL> set serveroutput on/off;
SQL>
SQL> begin
2 dbms_output.put_line('hello');
3 end;
4 /
hello
PL/SQL procedure successfully completed
變數
declare
變數名稱 變數類型;
begin
end;
標識符：不能以數字開始 長度不能超過30個字元
變數初始化
採用:= 進行賦值
v_age number(10) := 10;
變數類型
number(m,n) 數字類型
char() 固定長度字串
varchar2() 可變長度字串
date 日期類型
boolean(true/false/null) 布爾類型
binary_integer 有符號整型
注意：布爾類型不能直接輸出
程式碼範例：
注意：變數名稱不能是SQL關鍵字，最好不要和表中欄位同名
變數命名：
v_實際含義的單詞
常量命名：
c_實際含義
參數命名
p_實際含義
遊標類型
名稱_cursor
表類型
名稱_table
記錄類型
名稱_record
變數範圍
變數從聲明開始到對應模組的end結束為它的範圍
--變數範圍程式碼範例
變數的其他類型
1.表類型
定義表類型
type 表類型名稱 is table of 表類型儲存的基本類型 index by binary_integer
聲明變數為表類型
變數名稱 表類型名稱
表類型類似java中的數組，用來儲存具有同種類型的資料
屬性：
first 表示獲得表類型的第一個下標
last 表示獲得表類型的最後一個下標
count 表示表類型的資料個數
next(下標) 表示下一個下標
prior(下標) 表示前一個下標
程式碼範例：
表類型通過下標操作
2.記錄類型
用來儲存一條記錄
type 記錄類型名稱 is record(field1 類型,field2 類型)
定義變數，將變數類型設定成記錄類型
v_record 記錄類型名稱
程式碼範例
定義記錄類型的簡單方式
%rowtype 定義記錄類型
表名%rowtype
程式碼範例
<div></div>
定義表類型的簡單方式
%type 定義表類型
表名.欄位名%type
程式碼範例
程式碼範例： 將表中的所有記錄存在一個變數中
操作符
算數運算子
跟java一致
v_age number(3) := 0;
v_age := v_age +1;
比較子
> < >= = 不等於!= <> ~= ^=
邏輯運算子
and or not
between and
賦值運算子
:=
流程式控制制
分支：
1.if分支
if 布林運算式 then
…………
else
…………
end if;
多個分支：
if 布林運算式 then
…………
elsif 布林運算式 then
…………
elsif 布林運算式 then
…………
else
…………
end if;
案例： --尋找員工，根據職位進行工資的增加
2.case分支，類似java中的switch````case
case 變數
when 條件 then
語句·····
when 條件 then
語句·····
when 條件 then
語句·····
else
語句·····
end case;
案例
迴圈：
1.loop迴圈
定義一個迴圈指數
v_index binary_integer := 0;
loop
if v_index=迴圈次數 then
exit;
end if;
迴圈體
迴圈指數自增
v_index := v_index+1;
end loop;
案例：
--loop迴圈
declare
--定義迴圈指數
v_index binary_integer :=1;
begin
--定義loop迴圈
loop
--定義推出迴圈的條件
if v_index=5 then
exit;
end if;
--執行迴圈體
dbms_output.put_line(v_index);
--迴圈指數自增
v_index := v_index+1;
end loop;
end;
2.for迴圈
定義一個迴圈指數
v_index binary_integer;
for 迴圈指數 in 下限指數..上限指數 loop
迴圈體
end loop;
案例：
--for迴圈
注意：迴圈指數的開始一定要小於結束
通過reverse關鍵字實現迴圈反轉
--for迴圈反轉
declare
v_index binary_integer;
begin
for v_index in reverse 10..20 loop
dbms_output.put_line(v_index);
end loop;
end;
3.while迴圈
定義迴圈指數
v_index binary_integer := 1;
while 布林運算式 loop
迴圈體
迴圈指數自增
end loop;
案例：
--while迴圈
declare
v_index binary_integer := 1;
begin
while v_index<5 loop
dbms_output.put_line(v_index);
v_index := v_index+1;
end loop;
end;
迴圈嵌套
通過<<label>>標籤方式來控制迴圈
案例：
4.goto跳轉
PL/SQL中書寫定義sql
1.DQL語言
select 語句要求：1.必須寫into關鍵字
2.查詢語句只能有一條返回值
異常樣本： 沒有值 no_data_found；值過多 too_many_rows
2.DML(insert/update/delete)
注意：執行DML語句要處理事務
動態傳參
&+變數來實現動態傳參
--執行DML語句
declare
begin
--普通DML語句
delete from emp where empno=&xx;
--交易處理
commit;
end;
3.DDL(create/drop/alter/truncate)
DDL語句要在 execute immediate('DDL')
遊標(cursor)
sql語句執行時會在記憶體中開闢一個地區，用來存放執行的sql語句以及返回的資料,我們把這個記憶體地區叫做上下文環境（context）;遊標就是指向這個上下文環境的指標。
遊標分類
隱式遊標：由資料庫管理系統建立執行
顯示遊標：有程式員負責建立執行和關閉的遊標
遊標的屬性
%rowcount 儲存的是遊標執行時所影響的記錄條數
操作屬性
顯示遊標：自訂遊標名稱%rowcount
隱式遊標：sql%rowcount
%found 判斷當前資料有沒有下一條 true/fase
%notfound 判斷當前資料有沒有下一條 true/fase
%isopen 判斷遊標是否開啟
顯示遊標
1.建立遊標
cursor 遊標名稱 is 查詢語句
2.開啟遊標
open 遊標名稱
3.擷取資料
fetch 遊標名稱 into 變數
4.關閉遊標
close 遊標名稱
案例： --顯示遊標
for迴圈來迴圈遊標
定義遊標時傳遞參數
注意：設定形參時不要寫參數的長度，實參是在開啟遊標的時候傳遞
遊標不能重複開啟和關閉
定義遊標類型的變數
1.定義遊標類型
type 遊標類型名稱 is ref cursor return 返回結果類型
2.定義遊標類型的變數
變數名稱 遊標類型名稱
過程和函數
過程（procedure）又叫預存程序（stored procedure），是一個有名稱的PL/SQL程式塊
過程相當於java中的方法，它注重的是實現某種業務功能
函數（function）也相當於java中的方法，它注重計算並且總是有返回結果
過程和函數都是能夠永久儲存在資料庫中的程式碼塊，應用時通過調用執行
過程的基本結構
create [or replace ] procedure 過程名稱(形參名稱 形參類型,形參名稱 形參類型······)
is | as
定義變數
begin
過程體
exception
異常
end;
1.含有輸入參數的過程
輸入參數 用in 標識 可省略
2.無參的過程
3.有輸出參數的過程
輸出參數用 out 標識
過程的調用
1.通過匿名塊調用
輸入參數
輸出參數過程
無參的過程
begin
mypro_noparam;
end;
2.命令列調用
調用輸入參數
SQL> exec mypro(7788,3000);
輸出參數
SQL> var v_sal number; 註冊變數
SQL> exec mypro(7788,:v_sal); :變數名稱 使用變數接收輸出
調用無參
SQL> exec mypro;
範例程式碼：
1.寫一個過程封裝emp表中的資料
2.寫一個過程輸入員工編號，通過遊標擷取輸出該員工對應下屬的資訊
3./*計算100-200的素數*/
3.通過java調用過程java調用過程程式碼範例
自訂函數
function.sql程式碼範例
異常
系統異常分為預定義異常和非預定義異常預定義異常，是由資料庫定義好，含有異常編碼，異常名稱，異常資訊的這麼一種異常；大概有20種，例如too_many_rouws;no_data_found;zero_divide.非預定義異常：異常編碼，異常資訊，但沒有異常名稱
非預定義異常程式碼範例
自訂異常
自訂異常程式碼範例
包包用於管理過程和函數包分為包頭和包體
包的程式碼範例
jdbc調用含有包的過程體的程式碼範例
註：包一定要有包頭，包頭負責聲明函數、過程、變數和常量包體具體來實現包頭所聲明定義的函數和過程包體封裝實現包頭可單獨定義，那麼單獨定義的包頭只能含有常量
觸發器 trigger
類似java中的Listener
觸發器由資料庫管理系統負責調用和執行通過觸發觸發器所監聽的事物來實現觸發器的調用
表層級的觸發器（對於整個資料庫表做監聽）行層級的觸發器（對於表中的每一行做監聽）
觸發器程式碼範例
註：觸發器的執行順序
有表層級的觸發器，行層級的觸發器作用於同一個表
1.before表層級觸發器2.before 行層級觸發器3.after 行層級觸發器4.after 表層級觸發器
系統觸發器
DBA用來調試系統on database
註：觸發器不帶參數，沒有放回值，不作交易處理
總結
