在公司中有時候會因為被客戶稽核，因此會要求我們DBA人員定期提供特定的條件，但這些通常需要透過串接其他系統或各式各樣的 table 形成 user 特定的資料，我們過往通常會用 View撈取資料提供，VIEW一直幫助開發人員、分析師和最終用戶簡化即席查詢和報表。但定義View 問題是它們往往要么太具體（不容易重用），要么不夠具體（太通用而無法提高效能）。
想像一下，我們需要一個視圖來幫助開發人員快速找到等待發貨的訂單的詳細信息，這些訂單的總價值為 100 美元或更多。您將如何建立這樣的視圖？您是否會建立一個特定視圖，僅傳回此場景所需的列，並使用限制性 where 子句謂詞將訂單限制為僅那些尚未發貨且價值大於 100 美元的訂單？
然而實際上的系統有時候 Oracle 會因為 Join 過多的 table 導致我們就算用 where 撈取效能一樣過差, 甚至撈取時間過於長久，超過百萬bytes撈整天都撈不出來。那是否能夠在 Join 中傳入特定的參數減少其效能? 這時候就是可以使用 Oracle 19c 以後推出的 Parameter view (稱作 function view) 進行操作。
簡述1 : 如果想要在 function 中 return view 可以使用此用法 ( 支援 19c 以上 )
簡述2 : 如果想要在view 中傳入參數可使用此方法
簡述3 : 語法概述
CREATE OR REPLACE FUNCTION func...name
RETURN CLOB sql_macro AS
stmt CLOB;
BEGIN
...
RETURN stmt;
END func...name;參考 : link
oracle sql_macro 練習範例
實際練習才有辦法在後續應付各種靈活的需求，以下為練習運算表達式的範例
所用的 fruits table 可以參考 (link) 進行建立 。
1. 先建立一個供應商的 table
CREATE TABLE VENDOR
(
NAME VARCHAR2(40 BYTE)
, F_ID VARCHAR2(20 BYTE)
, DESCRIPTION VARCHAR2(400 BYTE)
) 2. 匯入供應商資料
insert into vendor (name, f_id, description) values('jerry', 'a1', '蘋果供應商');
insert into vendor (name, f_id, description) values('rororo', 'a2', '香蕉供應商');
insert into vendor (name, f_id, description) values('laruku', 'a3', '橘子供應商');
insert into vendor (name, f_id, description) values('janet', 'a4', '西瓜供應商');
insert into vendor (name, f_id, description) values('lyte', 'a5', '椰子供應商');
insert into vendor (name, f_id, description) values('jimmyc', 'a6', '葡萄供應商');
3. 先 join 出需要的表
select
f.f_id,
f.s_id,
f.f_name,
v.name as vendor_name,
v.description
from fruits f, vendor v
where
1=1
and f.f_id = v.f_id
and v.name = 'rororo'
4-1. 沒帶參數的 function view
CREATE OR REPLACE FUNCTION fruit_list
RETURN CLOB sql_macro AS
stmt CLOB;
BEGIN
stmt := q'[
select
f.f_id,
f.s_id,
f.f_name,
v.name as vendor_name,
v.description
from fruits f, vendor v
where
1=1
and f.f_id = v.f_id
]';
RETURN stmt;
END fruit_list;- 直接 select 出結果 : select * from fruit_list()
- function view oracle 官網叫做 sql_macro
- RETURN CLOB sql_macro AS stmt CLOB : 記得要帶入 sql_macro
4-1. 帶參數的 function view
function view 優點在於可以帶參數若不帶參數就用原本的 view 就好了
CREATE OR REPLACE FUNCTION fruit_list_p(name VARCHAR2)
RETURN CLOB sql_macro AS
stmt CLOB;
BEGIN
stmt := q'[
select
f.f_id,
f.s_id,
f.f_name,
v.name as vendor_name,
v.description
from fruits f, vendor v
where
1=1
and f.f_id = v.f_id
and v.name = fruit_list_p.name
]';
RETURN stmt;
END fruit_list_p;- name VARCHAR2 : 宣告要帶的參數名稱 name 與其型態 varchar2
- and v.name = fruit_list_p.name : 動態綁定參數 function名稱.參數名稱
- 直接select 這個 function name 並帶參數 : select * from fruit_list_p(‘rororo’)
