Oracle
(PL/SQL)
Written by Alan Yeung 2016
Reference: Beginning Oracle PL_SQL, 2nd Ed - Donald Bales
PL/SQL 是在數據庫端執行的,若果把資料庫處理的程式都寫成stored procedure,撰寫程式時就不需要對資料庫進行頻繁的連線與資料往來,不會因為網絡擁擠而受影響,如此一來效能也會相對有所提昇.
在PL/SQL中,程式設計的基本單元為獨立的stored procedure, function或是 package:
1. procedure: 沒有返回值。
2. function: 有返回值。
3. package: 包含procedure ,
function
Blocks
Oracle的Stored Procedure撰寫程式時遵守PL/SQL的區塊結構(PL/SQL block),其中包含以下要素...
1. 宣告區 - 開始於DECLARE關鍵字,用來宣告程序會用到的變數或常數,有時,當你編寫一個PL/SQL程序,你甚至不會使用宣告區,但它仍然存在.
2.
執行區 - 開始於BEGIN ,程式邏輯放在這裡,是必要的區塊.每一個PL/SQL程序必須至少有一個可執行的代碼,即使它是關鍵字NULL,NULL在這種情況下表示沒有動作.
3. 例外處理區 - 開始於EXCEPTION關鍵字,用來處理執行區塊中可能出現的錯誤,為選擇性的區塊.
4. 結束區 - 每一個PL/SQL區塊以關鍵字END結束.
Anonymous Blocks
它叫做Anonymous Blocks,因為它不會被保存在數據庫中,所以它永遠不會有一個名字.在現實中,你不會在production用anonymous block,但是你會在開發過程使用他們.它們非常適合用於測試.最終你會存儲在PL/SQL Block.
在sqlplus輸入 set serveroutput on 顯示運行結果.
Functions
Function 一個 PL/SQL BLOCK或一個方法 return 一個值, 所以它可以在右側輸入值來使用
下面是一個例子:
n_value := to_number('123.45');
在這一行的PL/SQL代碼,
n_value 是一個數值變量, n_value後面是運算符,這是一個冒號後面跟一個等號( := ).
以下是 PL/SQL內置的Function, to_number(text in varchar2), 它分析VARCHAR2數據內容，接著轉換NUMBER值類型,VARCHAR2內容必須為數字,非文字;否則,Function提出了一個 INVALID_NUMBER exception.
SQL> select to_number('A') from dual;
select to_number('A') from dual
*
ERROR at line 1:
ORA-01722: invalid number
正確例子:
SQL> select to_number('9') from dual;
TO_NUMBER('9')
--------------
9
Create a Function
在 Oracle, 你可以建立自己的Function.
function 的語法: ( [ ] ) 為非必要
CREATE [OR REPLACE] FUNCTION function_name
[ (parameter [,parameter]) ]
RETURN return_datatype IS | AS [declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [function_name];
現在,讓我們嘗試創建一個無 exception的TO_NUMBER() function:
create or replace function to_number2
(v_input
in varchar2)
return number as
begin
return
to_number(v_input);
exception
when
others then return NULL;
end to_number2;
/
|
其中, <FUNCTION_NAME>
為 Function的名稱;
<PARAMETER_NAME>
是一個參數名稱通過IN,OUT,或IN OUT數值;
<parameter_data_type>是相應的 PL/SQL 數據類型;
<return_data_type> 是 PL/SQL 完成它執行的功能返回的數據值.
建立Function的語法有以下幾個重點：
語法是CREATE FUNCTION，可加入OR REPLACE來自動取代現有的子程序。
IS保留字也可以改成AS。
當建立一個 function時可以定義參數. 參數有三種:
1. IN –該參數可以在function 或procedure參考使用. 但function 或procedure不能修改該參數值.
2. OUT - 該參數無法在function 或procedure參考使用. function 或procedure可以設定該參數值.
3. IN OUT - 該參數可以function 或procedure參考使用, 也可以設定該參數值.
FUNCTION BLOCK 的結構是和 anonymous procedure 完全一樣的,除了額外的 DDL, optional 的parameters 和 return 條款. 現在,讓我們嘗試在SELECT語句中使用此 Function:
SQL> select to_number2('A') from dual;
TO_NUMBER2('A')
---------------
|
ß 由於在exception 設定當不是數字則反回 NULL
when others then return NULL;
所以沒有反回錯誤的結果
|
SQL> select to_number('9') from dual;
TO_NUMBER('9')
--------------
9
|
ß 使用了return to_number(v_input);
所以成功執行數字轉換類型 varchar2 -> number |
例子二: Hello
World : Anonymous Blocks + Function
set serveroutput on size 1000000
Declare -- anonymous block宣告區
out1 varchar2(20); -- 創見一個變量,類型為varchar2且需設長度.因為function需要一個輸入值
function hello(v_input in varchar2) -- 創見一個hello的function, 宣告一個v_input值為 IN的參數
return varchar2 as --
function 內參數返回的類型為 varchar2
begin -- BEGIN開始PL/SQL block 的執行部分.
return 'Hello '|| v_input; -- 返回'Hello ' 文字並加上 v_input 的參數
end hello; -- 結束 function hello
begin -- anonymous block執行區
out1 := hello('World'); --
out1進行運算, 執行function hello(v_input), v_input 值為'World'
sys.dbms_output.put_line(out1); -- 於擁有者sys調用dbms_put library的put_line功能,顯示到結果.
end; --
結束anonymous block
/
|
執行效果如下:
Hello World
PL/SQL procedure
successfully completed.
當然, 你也可以創建一個儲存在數據庫的function並調用它. 簡單的例子如下:
create or replace function
hello(input1 in varchar2)
return varchar2 as
begin
return 'Hello '||input1;
end hello;
/
|
執行效果如下:
SQL> select hello('World')
from dual;
HELLO('WORLD')
--------------------------------------------------------------------------------
Hello World
SQL> select hello('Alan')
from dual;
HELLO('ALAN')
--------------------------------------------------------------------------------
Hello Alan
SQL> begin
2
sys.dbms_output.put_line(hello('World'));
3 end;
4 /
Hello World
PL/SQL procedure successfully
completed.
沒有留言:
張貼留言