PL/SQL編程基礎簡介及實踐

PL/SQL編程基礎簡介及實踐

PL/SQL編程基礎簡介及實踐

1、開始(p1~p2)
2、背景介紹(p3)
3、特性優點(p4~p5)
4、使用說明(p6)
5、文法結構(p7)
6、命名參考(p8~p9)
7、複合類型(p10~p16)
8、運算子(p17)
9、流程式控制制語句(p18~p28)
10、異常和錯誤處理(p29~32)
11、函數與預存程序(p33~p41)
12、結束(p42)

1、背景介紹
1、 PL/SQL是一種程式語言，叫做過程化SQL語言（Procedural Language/SQL）。 PL/SQL是Oracle資料庫對SQL語句的擴充。在普通SQL語句的使用上增加了程式設計語言的特點，所以PL/SQL就是把資料操作和查詢語句組織在PL/SQL代碼的過程性單元中，通過邏輯判斷、迴圈等操作實現複雜的功能或者計算程式語言。
2、 PL/SQL類別：資料庫引擎、工具引擎（嵌入到其他語言如：C、JAVA）。
3、 PL/SQL包括：編程結構、文法和邏輯機制，工具引擎還增加了支援（如ORACLE Forms）的句法。

3、特性優點
1、提高運行效率
 可以將大量資料的處理放在服務端，減少資料在網路上的傳輸時間。
2、用戶端
 可以在客服端執行本地的PL/SQL，或通過向伺服器發送SQL命令或啟用伺服器端來運行PL/SQL程式。
3、支援過程化
 可嵌入SQL語句，及使用各種類型的條件分支語句和迴圈語句。
4、支援模組化
 可通過使用程式包、過程、函數、觸發器等將各類處理邏輯分開，方便管理。
5、支援處理異常
 可通過使用自訂異常或使用內建的異常，來處理代碼中可能發生的異常，提高代碼的健壯性。
6、提供大量內建程式包
 可使用Oralce內建程式包，從而更加方便的處理資料及商務程序。
7、其他：更好的效能、可移植性和相容性、可維護性、易用性等。

4、使用說明
1、允許的語句：
 可使用INSERT、UPDATE、DELETE、SELECT INTO、COMMIT、ROLLBACK、SAVEPOINT語句，在PL/SQL中需要以動態方式使用DDL(CREATE、ALTER、DROP、TRUNCATE)語句。
2、 啟動並執行方式：
PL/SQL可在SQL*PLUS、進階語言、 ORACLE開發工具中使用(如：SQL Developer或Procedure Builder等。
3、啟動並執行過程：
PL/SQL程式的運行是通過Oracle中的一個引擎來進行的。這個引擎可能在Oracle伺服器端，也可能在 Oracle 用戶端。引擎執行PL/SQL中的過程性語句，然後將SQL語句發送給資料庫伺服器來執行，再將結果返回給執行端。

5、文法結構
1、PL/SQL組成：由3部分組成：聲明部分、執行部分、異常處理部分。
2、結構說明：
--聲明部分：聲明用到的常量、類型、遊標、局部的預存程序、函數
declare
…
--執行部分：具體的SQL語句，包含處理的流程。
begin
…
--異常部分：針對異常處理的SQL語句。
exception
…
end;

6、命名參考
1)標識符：
 不區分大小寫、不能包含減號(-)、首字元必須為字母，不能是SQL保留字、不能超過30個字元。
2)命名參考：
程式變數：v_name v_orderId 程式常量：c_name c_cityId 遊標變數：cursor_name cursor_storeId 異常標示符：e_name e_agentId 記錄類型：name_record test_city_record 綁定變數：g_name g_userId 錯誤：e_error
3)資料類型：(5大類)
1字元類型(CHAR、NCHAR 、VARCHAR、 VARCHAR2、 NVARCHAR2)、2數字類型(NUMBER 、INTEGER 、BINARY_FLOAT 、BINARY_DOUBLE)、3時間類型(DATE 、TIMESTAMP 、INTERVAL YEAR 、INTERVAL DAY)、4大物件類型(BLOB 、CLOB 、BFILE 、NCLOB)、5其他類型(LONG 、RAW LONG RAW 、ROWID 、UROWID) 。

bfile(moive)：存放大的位元據對象，表中只存放檔案的目錄。大小<=4GB
blob(photo)：存放大的位元據對象的位置，位置指向二進位對象的資料區塊。大小<=4GB
clob(book)：存放大的字元資料對象的位置，位置指向字元的資料區塊。大小<=4GB
nclob(ncahr字元資料)：存放大的nchar字元資料對象的位置，位置指向nchar字元的資料區塊。大小<=4GB
4)變數聲明
v_flag boolean not null default false;
identifier [constant] datetype [not null] [:=value|default value |expression]
identifier：變數名稱
datetype：變數類型
:=value 變數或常量的初始值
default value：預設值
expression 為函數 其他變數、文本值等
5)注釋
--單行注釋/*多行注釋*/

7、複合類型介紹
1、複合類型：(記錄類型、數群組類型、一維表類型、二維表類型)
1)記錄類型：記錄類型類似於c語言中的結構資料類型，它把邏輯相關的、分離的、基礎資料型別 (Elementary Data Type)的變數組成一個整體儲存起來，它必須包括至少一個標量型或record 資料類型的成員，稱作pl/sql record 的域(field)，其作用是存放互不相同但邏輯相關的資訊。在使用記錄資料類型變數時，需要先在聲明部分先定義記錄的組成、記錄的變數，然後在執行部分引用該記錄變數本身或其中的成員。
type record_name is record(
 v1 data_type1 [not null] [:= default_value ],
 ...
 vn data_typen [not null] [:= default_value ] );
2)說明：%type：表示變數的資料類型與表對應的列的類型一致
%rowtype：表示變數的資料類型與表對應的所有列的類型一致
可以不用知道列的資料類型、當列的資料類型改變後，修改pl/sql代碼
被賦值的變數與select中的列名要一一對應。

declare
 id varchar2(32); --證件號碼
province varchar2(10); -省份編號
city varchar2(10); --城市編號
district varchar2(10); --地區編號

--定義省份、城市、地區編號記錄表對象
type base_info_type is record(
 province base_info.province%type,
 city base_info.city%type,
 district base_info.district%type);

sp_record base_info_type;

begin
 id := sys_guid();
--查詢出關聯的省份編號、城市編號、地區編號資訊
select province, city, district
 into sp_record
 from base_info bi
 where bi.store_id = 'storeId ′ ;−−更新省份編號、城市編號、地區編號資訊updatetest h ousefohsetfoh.province=sp r ecord.province,foh.city=sp r ecord.city,foh.region=sp r ecord.district,foh.address= ′ 商務路 ′ ||lpad(abs(dbms r andom.random),4,dbms r andom.string( ′ x ′ ,2))wherefoh.order i d= ′  storeId′;−−更新省份編號、城市編號、地區編號資訊updatetesthousefohsetfoh.province=sprecord.province,foh.city=sprecord.city,foh.region=sprecord.district,foh.address=′商務路′||lpad(abs(dbmsrandom.random),4,dbmsrandom.string(′x′,2))wherefoh.orderid=′{orderId}';
 commit;
end;

2)數群組類型：具有相同資料類型的記錄的集合。
type array_name is varray(size) of elementType [not null];
array_name：數群組類型名稱 size：元素的大小 elementType：資料類型
--位置從1開始
declare
 type city_array is varray(3) of varchar2(10);
 v_city_array city_array;
begin
 v_city_array := city_array('北京市', '上海市', '深圳市');
dbms_output.put_line('第3個城市名稱 =' || v_city_array(3));
end;

1、綁定變數：使用variable來定義
variable return_cityId number;

SQL> variable returnValue number;
SQL> begin
 2 select 3*6 into :returnValue from dual;
 3 end;
 4 /
PL/SQL procedure successfully completed
returnValue
---------
18
SQL> print returnValue;
returnValue
---------

3)表類型：定義記錄表（或索引表）資料類型。它與記錄類型相似，但它是對記錄類型的擴充。它可以處理多行記錄，類似於進階中的二維數組，使得可以在pl/sql中模仿其他資料庫中的表。
type table is table of elementType [not null]
index by [binary_integer | pls_integer |varray2]
關鍵字index by表示建立一個主鍵索引，以便引用記錄表變數中的特定行
--按一維數組使用記錄表的樣本
declare
 type city_table is table of varchar2(20) index by binary_integer;
 v_city_table city_table;
begin
 v_city_table(1) := '北京市 ';
v_city_table(2) := ' 深圳市 ';
dbms_output.put_line(' 第2個城市名稱 = ' || v_city_table(2));
end;

--按二維數組使用記錄表的樣本
declare
type bse_city_table is table of test_city%rowtype index by binary_integer;
v_bse_city_table bse_city_table;
begin
 select city_id, city_name
 into v_bse_city_table(1).city_id,v_bse_city_table(1).city_name
 from test_city bc
 where bc.p_city_id = '020'
 and rownum = 1;
 select city_id, city_name
 into v_bse_city_table(2).city_id,v_bse_city_table(2).city_name
 from test_city bc
 where bc.p_city_id = '0755'
 and rownum = 1;
 dbms_output.put_line('記錄1中地區編號=' || v_bse_city_table(1).city_id ||
 '_記錄1中地區名稱=' || v_bse_city_table(1).city_name);
 dbms_output.put_line('記錄1中地區編號=' || v_bse_city_table(2).city_id ||
 '_記錄1中地區名稱=' || v_bse_city_table(2).city_name);
end;

8、運算子
1、關係運算子：
=、<> ~= != ^= 、>、>=、<、<=
2、一般運算子：
+、-、*、/、:=(賦值號)、..(範圍運算子)、||、=>(關係號)
3、邏輯運算子：
is null、in、and、or、not、between and
4、注意事項：
1)變數賦值：先聲明再賦值。
v_storePhone varchar2(11); --手機號碼
v_storePhone := '158' || lpad(abs(dbms_random.random), 8, 0);
2)null+數字 為null，null||字串 為字串
3)boolean類型的值只能取 true false null3個值

9、流程式控制制語句
1)語句分類：控制語句(IF)、迴圈語句(LOOP 、EXIT) 順序語句(GOTO、NULL)
2)結構說明
a)
IF <布林運算式> THEN
 PL/SQL語句和SQL語句
END IF;
b)
IF <布林運算式> THEN
 PL/SQL語句和SQL語句
ELSE
其他語句
END IF;

IF <布林運算式1> THEN
 PL/SQL語句和SQL語句1
ELSIF <布林運算式2> THEN
其他語句1
ELSIF <布林運算式3> THEN
其他語句2
ELSE
其他語句3
END IF;

IF語句樣本
declare
 v_roleId varchar2(20); --角色編號
v_result varchar2(60);
begin
 for vv in (select distinct su.role_id
 from test_ur su
 where su.role_id in ('project_sz',
 'project_bj',
 'project_gz',
 'project_sh')) loop
 if (vv.role_id = 'project_sz') then
 v_result := vv.role_id || '_表示的是_角色1';
 dbms_output.put_line(v_result);
 elsif (vv.role_id = 'project_sh') then
 v_result := vv.role_id || '_表示的是_角色2';
 dbms_output.put_line(v_result);

elsif (vv.role_id = 'project_gz') then
 v_result := vv.role_id || '_表示的是_角色3';
 dbms_output.put_line(v_result);
elsif (vv.role_id = 'project_bj') then
 v_result := vv.role_id || '_表示的是_角色4';
 dbms_output.put_line(v_result);
else
 v_result := vv.role_id || '_表示的是_未知角色';
dbms_output.put_line(v_result);
 end if;
end loop;
 dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss') || '處理成功');
end;

loop語句樣本
loop
執行的語句
exit when <條件陳述式>; --當條件為滿足時，退出迴圈
end loop;
--loop樣本
declare
 v_count number;
 v_time number;
begin
 v_count := 0;
 loop
 v_count := v_count + 1;
 dbms_output.put_line('第' || v_count || '次迴圈');
exit when(v_count > 3);
 end loop;
end;

while語句樣本
while <布林運算式> loop
執行的語句
end loop;
--while樣本
declare
 v_count number;
 v_time number;
begin
 v_count := 0;
 while (v_count < 3) loop
 v_count := v_count + 1;
 dbms_output.put_line('第' || v_count || '次迴圈');
end loop;
end;

for迴圈語句樣本
for 迴圈計數器 in [reverse]下限 .. 上限 loop
執行的語句
end loop;
每迴圈1次，計數器自動加1，加上reverse關鍵字則自動減1，必須為從小到大的整數，可以使用exit when 退出迴圈
declare
 v_count number;
begin
 v_count := 8;
 for i in 1 .. v_count loop
 dbms_output.put_line('第' || i || '次迴圈');
exit when(i > 3);
 end loop;
end;

case when迴圈文法
--文法1
case 條件運算式
when 運算式結果1 then
語句1
 ...
 when 運算式結果n then
語句n
 [else 運算式結果]
end case;

--文法2
case 條件運算式
when 運算式結果1 then
語句1
 ...
 when 運算式結果n then
語句n
 [else 語句]
end case;

case when語句樣本
select trunc(tur.created_date, 'dd'),
 count(case
 when tur.role_id = 'project_sh' then
 1
 else
 null
 end) as 角色1,
 count(case
 when tur.role_id = 'project_gz' then
 1
 else
 null
 end) as 角色2,

count(case
 when tur.role_id = 'project_sz' then
 1
 else
 null
 end) as 角色3,
 from test_ur tur
 group by trunc(tur.created_date, 'dd')
 order by trunc(tur.created_date, 'dd') desc;

5)goto 無條件跳轉到指定標籤所在部分
goto lable
…
<<lable>>
6)null 不執行任何操作的語句

10、異常和錯誤處理
1、Oracle提供異常情況(exception)和異常處理(exception handler)來實現對錯誤的處理。
2、異常情況(exception)指在正常執行過程中未預料的事件，程式塊的異常處理預定義錯誤和自訂錯誤，運行PL/SQL塊時一旦產生異常而沒有指出如何處理時，就會自動終止整個PL/SQL塊的運行。
3、異常錯誤分為3類(預定義錯誤、非預定義錯誤、自訂錯誤)
預定義錯誤：無需在程式中定義，由Oracle自動引發，共24個，直接在異常中使用。
非預定義錯誤：需在程式中定義，由Oracle自動引發
自訂錯誤：需在程式中定義，且需在程式中引發。

1)預定義錯誤
exception
 when No_data_found then
dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss') ||'execute failure');
2)非預定義錯誤
--定義錯誤
<異常情況> exception;
--與標準的Oracle錯誤關聯
pragma exception_init(<異常情況>,<異常代碼>);
--處理錯誤
exception
 when foundError then
dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss') ||'execute failure');

3)自訂錯誤
--定義錯誤
<異常情況> exception;
--通過raise引發錯誤
raise 異常情況
--處理錯誤
exception
 when raiseError then
dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss') ||'execute failure');

4)修改自訂錯誤訊息
dbms_standard.raise_application_error(errorNumber,errorCode,errorsMsg);
errorNumber：錯誤編號： -20000~-20999
errorMsg：提示的錯誤訊息(<2014KB)
errorFlag：true 將錯誤添加到錯誤清單，false 替換當前的錯誤清單，預設為false
dbms_standard.raise_application_error(-20001,'錯誤的訊息');
記錄錯誤關鍵字：
sqlcode：錯誤編號 如：6502
sqlerrm：錯誤訊息 如：ORA-06502: PL/SQL: 數字或值錯誤 : character string buffer too small (<500KB)
dbms_output.put_line('錯誤編號_' || sqlcode || '_錯誤資訊_' || sqlerrm);
when others exception必須放在異常處理部分的最後面，以作為預設異常的處理，when … exception 沒有數量限制，沒被處理的異常將檢測調用異常的程式，並將異常傳播到外面，異常被處理並被解決或達到最外層迴圈後停止，在聲明部分的拋出的異常將控制轉到上一層部分。

11、函數和預存程序
1)函數：
create [or replace] function functionName
(arg1 [{in out in out}] type1 default value1,
...
argn [{in out in out}] typen default valuen)
[authid definer|current_user]--許可權控制
return resultType
{is | as}
變數的聲明部分
begin
執行語句部分
return expression
exception
異常處理部分
end functionName;
in out in out--表示參數的模式，有入參、出參，不寫的話預設為入參，其中只能為入參設定預設值，當調用函數後，不指定入參的值時，就使用入參的預設值。
create or replace function funTranslateRole(v_roleId in varchar2,
 v_result out varchar2)
 return varchar2
 is
 role_id varchar2(20); --角色編號
begin
 role_id := v_roleId;
 if (role_id = 'project_sz') then
 v_result := role_id || '_表示的是_客服';
else
 v_result := role_id || '_表示的是_未知';
end if;
 return v_result;
 dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss') || '處理成功');
exception
 when others then
 dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss') || '處理失敗');
end funTranslateRole;

1)位置標記法
--argvalue1,argvalue2,..argvaluen
funTranslateRole(v_roleId,v_result)

2)名稱標記法
--這種方式與參數的順序沒關係，v_result 、 v_roleId與函數中的參數名稱需一致
declare
 roleId varchar2(20); --角色編號
vresult varchar2(60); --角色的結果
begin
 roleId := 'project_bj';
 vresult := funTranslateRole(v_result => vresult, v_roleId => roleId);
 dbms_output.put_line(vresult);
end;

3)組合(名稱表示+位置標記法)
如果前一個參數用名稱標記法，則後面的所有參數都要用名稱標記法。
--調用方式
declare
 v_roleId varchar2(20); --角色編號
v_result varchar2(60); --角色的結果
begin
 v_roleId := 'project_bj';
 v_result := funTranslateRole(v_roleId,v_result);
 dbms_output.put_line(v_result);
end;

2)預存程序：
create [or replace] procedure procedure Name
(arg1 [{in out in out}] type1 default value1,
...
argn [{in out in out}] typen default valuen)
[authid definer|current_user]--許可權控制
{is |as}
變數的聲明部分
begin
執行語句部分
exception
異常處理部分
end procedureName;
in out in out—表示參數的模式，有入參、出參，不寫的話預設為入參，其中只能為入參設定預設值，當調用函數後，不指定入參的值時，就使用入參的預設值。
create or replace procedure proTranslateRole(v_roleId in varchar2,
 v_result out varchar2)
is
 role_id varchar2(20); --角色編號
begin
 role_id := v_roleId;
 if (role_id = 'project_sz') then
 v_result := role_id || '_表示的是_客服';
else
 v_result := role_id || '_表示的是_未知';
end if;
dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss') || '處理成功');
exception
 when others then
 dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss') || '處理失敗');
end proTranslateRole;

1)位置標記法
--argvalue1,argvalue2,..argvaluen
proTranslateRole(v_roleId,v_result)
2)名稱標記法
--這種方式與參數的順序沒關係，v_result 、 v_roleId與函數中的參數名稱需一致
declare
 roleId varchar2(20); --角色編號
vresult varchar2(60); --角色的結果
begin
 roleId := 'project_bj';
 vresult := proTranslateRole (v_result => vresult, v_roleId => roleId);
 dbms_output.put_line(vresult);
end;
3)組合(名稱表示+位置標記法)
如果前一個參數用名稱標記法，則後面的所有參數都要用名稱標記法。
--調用方式1
declare
 v_roleId varchar2(20); --角色編號
v_result varchar2(60); --角色的結果
begin
 v_roleId := 'project_bj';
 v_result := proTranslateRole (v_roleId,v_result);
 dbms_output.put_line(v_result);
end;

--調用方式2
exec[ute] 預存程序名稱(參數1,..參數n);
--可以在PL/SQL塊中建立本地函數和過程，但不能使用 create or replace關鍵字

1)函數與過程的差異
1、如果要返回多個值或不傳回值，可以使用過程；如果只返回1個值，可以使用函數。
2、過程用於執行一系列的動作，而函數用於計算和返回1個值。
3、可以在SQL語句內部通過調用函數來完成複雜的計算，而過程則做不到。

本文永久更新連結地址：https://www.bkjia.com/Linux/2018-03/151359.htm