Oracle基本修練: PL/SQL Stored procedures and Functions
本篇記錄PL/SQL procedures如何建立、編譯過程，以及搭配cursor, functions如何操作，並以範例進行練習。
Content
- PL/SQL procedure syntax
- Oracle Implicit Statement Results
- PL/SQL Function
- Cursor Variables with REF CURSOR
Let’s Start!
PL/SQL procedure syntax
Procedures是一種可以被重複使用的基本單位，包含了封裝特定過程的業務邏輯(例如要如何轉換資料、進行運算與儲存等)。而在oracle，procedures 作為可以自由命名的模塊，以schema object存放在DB中。
其基本結構為
CREATE [OR REPLACE ] PROCEDURE procedure_name (parameter_list)
IS如同基本結構所示，我們可以建立或覆蓋一個procedure，並決定是否要帶入參數表，而這些參數可以擁有IN, OUT, INOUT 模式。
- 例如
IN是指僅能被讀取(read-only)，像是存放在view中的數值，不可被變更。而這也是oracle預設執行procedure的模式。 OUT是可以編寫(writable)，通常被用作要return值使用。INOUT則包含了可讀可寫特性(readable and writable)。
編寫procedure就如先前建立一個 anonymous block 的方式雷同，我們依據需求進行宣告(declaration)、執行(execution)、異常處理(exception)。
宣告部分可以使用 variables, constants, cursors等等，但在procedure不需要使用
DECLARE這個關鍵字在開頭。
以範例來看
CREATE OR REPLACE PROCEDURE print_contact(
in_customer_id NUMBER
)
IS
r_contact contacts%ROWTYPE;
BEGIN
SELECT *
INTO r_contact
FROM contacts
WHERE customer_id = p_customer_id; dbms_output.put_line( r_contact.first_name || ' ' ||
r_contact.last_name || '<' || r_contact.email ||'>' );EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line( SQLERRM );
END;
進行編譯如同一般DQL，按下run statement(或ctrl+enter)。
就會看到我們建立新的procedure在oracle db中。
建立完成以後，未來需要再執行此procedure，只需要使用
EXECUTE procedure_name( arguments);
-- 或是使用
-- EXEC procedure_name( arguments);裡面帶入的arguments 如前面設計，可以選擇顧客id帶入，例如我們想要查詢customer_id = 100的emails，就輸入
EXEC procedure_name(100);-- 回傳結果:
-- Elisha Lloyd<elisha.lloyd@verizon.com>
指令的方式以外，我們也可以在procedure目錄底下找到已建立物件來執行
刪除的方式也如一般Table操作，只需要透過
DROP PROCEDURE print_contact;或是一樣至procedure目錄下找到該物件，進行DROP。
Oracle Implicit Statement Results
在執行procedure時，我們可能會需要return單筆或多筆資料，這時候我們就會在procedure中使用到implicit statement results。
在12c以前我們需要用 OUT REF CURSOR來幫助我們完成這個任務，但12c release 1新增了一個feature就是 implicit statement result。
本次範例採用sample database中的 customers table。
A. 回傳單一結果
CREATE OR REPLACE PROCEDURE get_customer_by_credit(
min_credit NUMBER
)
AS
c_customers SYS_REFCURSOR; -- 宣告cursor
BEGIN
OPEN c_customers FOR -- open cursor
SELECT customer_id, credit_limit, name
FROM customers
WHERE credit_limit > min_credit
ORDER BY credit_limit;
-- 允許cursor當作argument 並回傳結果
dbms_sql.return_result(c_customers);
END;建立後我們執行此procedure
EXEC get_customer_by_credit(5000);便可以得到此output結果
B. 回傳多重結果
建立兩個ref cursors，分別回傳資料筆數，用count(*)
與顧客資訊，用page no and page size，撈出前10筆。
CREATE OR REPLACE PROCEDURE get_customers(
page_no NUMBER,
page_size NUMBER
)
AS
c_customers SYS_REFCURSOR;
c_total_row SYS_REFCURSOR;
BEGIN
-- return the total of customers
OPEN c_total_row FOR
SELECT COUNT(*)
FROM customers;
dbms_sql.return_result(c_total_row);
-- return the customers
OPEN c_customers FOR
SELECT customer_id, name
FROM customers
ORDER BY name
OFFSET page_size * (page_no - 1) ROWS
FETCH NEXT page_size ROWS ONLY;
dbms_sql.return_result(c_customers);
END;進行執行
EXEC get_customers(1,10)回傳結果
PL/SQL Function
function也是一種可以被重複使用的物件，其結構與procedure概念接近。body的部分是一樣的，也有宣告(declaration)、執行(execution)、異常處理(exception)。
但不同的是，使用function在執行階段，至少要有一個 RETURN statement。
基本結構為
CREATE [OR REPLACE] FUNCTION function_name (parameter_list)
RETURN return_type
IS
...
Begin
...
RETURN ...
END;範例如
CREATE OR REPLACE FUNCTION get_total_sales(
in_year PLS_INTEGER
)
RETURN NUMBER
IS
l_total_sales NUMBER := 0; -- 預設為0
BEGIN
SELECT SUM(unit_price * quantity) -- 選出SUM結果到l_total_sales
INTO l_total_sales
FROM order_items
INNER JOIN orders USING (order_id)
WHERE status = 'Shipped'
GROUP BY EXTRACT(YEAR FROM order_date)
HAVING EXTRACT(YEAR FROM order_date) = in_year;--可以帶入年分(整數)
RETURN l_total_sales;
END;建構了一個function，要如何call?
- in an assignment statement:
DECLARE
l_sales_2017 NUMBER := 0;
BEGIN
l_sales_2017 := get_total_sales (2017);
DBMS_OUTPUT.PUT_LINE('Sales 2017: ' || l_sales_2017);
END;2. in a Boolean expression
BEGIN
IF get_total_sales (2017) > 10000000 THEN
DBMS_OUTPUT.PUT_LINE('Sales 2017 is above target');
END IF;
END;3. in an SQL statement
SELECT
get_total_sales(2017)
FROM
dual;而移除function也很容易，也使用DROP，如
DROP FUNCTION function_name;Cursor Variables with REF CURSOR
cursor variables與implicit and explicit cursors最大的不同就是，前者不必綁定在一個特定的query上，而可以自由傳遞result於PL/SQL程式。
而如果我們沒有使用 cursor variables，通常的做法就是直接把所有資料先fetch出來到一個 variable上，再往下執行。
本質上分為strong與weak兩種，結構分別為
-- strong typed REF CURSOR (因為有特定的record structure or type)
DECLARE
TYPE customer_t IS REF CURSOR RETURN customers%ROWTYPE;
c_customer customer_t;-- weak typed REF CURSOR
DECLARE
TYPE customer_t IS REF CURSOR;
c_customer customer_t;-- 更新的版本可以將weak REF CURSOR縮寫為
DECLARE
c_customer SYS_REFCURSOR;
範例如
我們要取得給manager角度的員工資訊
先建立funtion
CREATE OR REPLACE FUNCTION get_direct_reports(
in_manager_id IN employees.manager_id%TYPE)
RETURN SYS_REFCURSOR
AS
c_direct_reports SYS_REFCURSOR;
BEGIN OPEN c_direct_reports FOR
SELECT
employee_id,
first_name,
last_name,
FROM
employees
WHERE
manager_id = in_manager_id
ORDER BY
first_name,
last_name; RETURN c_direct_reports;
END;
使用匿名塊來執行
DECLARE
c_direct_reports SYS_REFCURSOR;
l_employee_id employees.employee_id%TYPE;
l_first_name employees.first_name%TYPE;
l_last_name employees.last_name%TYPE;
l_email employees.email%TYPE;
BEGIN
-- get the ref cursor from function
c_direct_reports := get_direct_reports(46);
-- process each employee
LOOP
FETCH
c_direct_reports
INTO
l_employee_id,
l_first_name,
l_last_name,
l_email;
EXIT
WHEN c_direct_reports%notfound;
dbms_output.put_line(l_first_name || ' ' || l_last_name || ' - ' || l_email );
END LOOP;
-- close the cursor
CLOSE c_direct_reports;
END;
/以上為PL/SQL的Procedure與function使用方式。
資料來源:
https://www.oracletutorial.com/plsql-tutorial/plsql-procedure/
https://www.oracletutorial.com/plsql-tutorial/oracle-implicit-statement-results/
https://www.oracletutorial.com/plsql-tutorial/plsql-function/
https://www.oracletutorial.com/plsql-tutorial/plsql-cursor-variables/
