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/