預存程序查詢

4 預存程序查詢

預存程序查詢是針對預存程序定義查詢規則和內容的一類資料集。

4.1 前提條件

• 在產品中使用的預存程序查詢在資料庫創建時是有一些要求的，具體請先參考預存程序查詢要求。

• 在創建預存程序查詢前，必須確保“預存程序查詢管理”視窗中通過自動檢測和檢測結果集按鈕分別檢測出相關的參數和獲取預存程序查詢的結果集，詳細資訊請參見表屬性。

4.2 操作入口

在平台管理>平台資源節點及其目錄節點上右鍵選擇新建查詢>預存程序查詢，或在資源快捷按鈕區域選擇資料集>預存程序查詢，則彈出選擇資料來源視窗，根據需要選擇資料來源及其預存程序查詢，進入“預存程序查詢”介面。

4.3 介面介紹

“預存程序查詢”介面主要分為以下幾區域：

• 可選資源區：該區列出可供選擇的欄位。

• 欄位區：該區列出了所有可被預存程序查詢使用的資源

• 參數綁定區：將預存程序查詢中的參數與事實表中相應的參數進行綁定的操作區。

• 屬性區：列出預存程序查詢輸出的資源，並可以在下方設定屬性。在“預存程序查詢”介面中同樣可以進行警示設定、局部參數設定

• 工具列：列出了所有可用於編輯預存程序查詢的工具按鈕。

4.3.1 可選資源區

預存程序查詢必須確保在“預存程序查詢表屬性”視窗中通過自動檢測和 檢測結果集按鈕分別檢測出相關的參數和獲取預存程序查詢的結果集，詳細資訊請參見表屬性。

這樣才會在可選資源區列出了所有可被預存程序查詢使用的欄位。使用者只需將各欄位拖到該區域即可。

4.3.2 參數綁定區

將預存程序查詢中的參數與事實表中相應的參數進行綁定的操作區。如下圖，會把預存程序查詢檢測出來的參數放到參數綁定區域，點擊綁定按鈕。

則會彈出選擇參數對話方塊，根據需要選擇參數後點擊確定則實現預存程序查詢參數和產品中的參數的綁定。

預覽資料可以看到綁定參數後預存程序查詢生成的靈活分析的效果。

4.3.3 屬性區

屬性區用於設定查詢相關資源的屬性。它主要包含兩個部分：查詢輸出區和屬性面板。

• 查詢輸出區：該區顯示所有資料集輸出的相關資源，該區通常與“屬性面板”結合起來用於對這些資源的屬性進行管理，包含：輸出欄位屬性、私有參數、輸出參數、警示設定。

• 屬性面板：該區需要與“工作區”結合使用，按一下工作區中的任一資源，即在屬性面板中列出該資源的相關屬性資訊。使用者可以在屬性面板中重新設定資源的相關屬性。

4.3.4 工具列

預存程序查詢工具列的按鈕說明如下：

工具列的按鈕說明如下表，對應的功能點擊超連結跳轉到詳細分析

按鈕名稱	說明
儲存	儲存資料集到預設路徑。
另存為	儲存資料集到使用者指定路徑。
視圖	用於區域的顯示和隱藏。包含顯示/隱藏資源區、顯示/隱藏屬性區。
定義多重表頭	對SQL查詢的表頭進行修改增加，形成多重表頭。
參數佈局	調整參數在SQL查詢對應的報表中的位置。
Excel模板設定	把當前資料集的資料結果填充到使用者自訂的Excel範本中。
進階設定	用於設定當前資料集的快取策略和分頁策略。
預覽資料	對新建或修改的資料集進行預覽。
資料抽取	用於將當前資料集的資料抽取到MPP。

4.4 預存程序查詢要求

4.4.1 前提條件

在Synapse中使用預存程序查詢的前提條件是：預存程序查詢必須有結果集返回。注意：Oracle是通過輸出參數作為結果集返回的。預存程序查詢的OUT參數只支援游標類型，而且只支援單個輸出參數。

4.4.2 呼叫方法說明

在Synapse中預存程序查詢檢測參數的呼叫方法如下：

資料庫類型	預存程序查詢獲取參數
Teradata	Teradata JDBC介面沒有直接提供獲取巨集引數和欄位的方法，所以是通過執行以下SQL語句查詢獲得參數的： SELECT C.* FROM DBC.TABLES T, DBC.COLUMNS C WHERE T.DATABASENAME=? AND T.TABLENAME=? AND T.TABLEKIND='M' AND T.DATABASENAME=C.DATABASENAME AND T.TABLENAME=C.TABLENAME
Oracel、DB2等其他資料庫	呼叫JDBC中的標準方法：connection.getMetaData().getProcedureColumns(...)

在Synapse中預存程序查詢檢測結果集的呼叫方法如下。檢測結果集：是在前端彈出介面讓使用者輸入參數後，真正的執行一次預存程序查詢而獲得輸出欄位。

資料庫類型	預存程序查詢執行過程
Teradata	呼叫下面語句執行巨集： connection.prepareStatement("{Exec macroName(?,?)}")
Oracel、DB2等其他資料庫	呼叫JDBC中的標準方法： connection.prepareCall("{Call procName(?,?)}")

4.4.3 資料庫權限要求

資料庫類型	資料庫權限要求
Teradata	要求連接使用者必須具有訪問DBC.TABLES、DBC.COLUMNS的權限，及呼叫Exec方法的權限。
Oracel、DB2等其他資料庫	要求連接使用者必須具有呼叫getProcedureColumns和Call方法的權限。

4.5 Oracle預存程序查詢範例

下面我們以Oracle為例創建一個帶游標返回的預存程序查詢。

create or replace procedure "FOODMART"."SP_ACCOUNT"(I_ID in number,cur out RefCursorPkg.tRefCursor) as
begin
    open cur for
        select * from account where account_parent = I_ID
        order by account_id;
end;

其中預存程序查詢中的返回值RefCursorPkg.tRefCursor是在ORACLE包頭裡創建的一個TYPE變數，需要先建，腳本如下。

create or replace package RefCursorPkg
as
    type tRefCursor is ref cursor ;
end;

4.6 Netezza預存程序查詢範例

下面我們以Netezza為例創建一個返回結果集的預存程序查詢。

CREATE OR REPLACE PROCEDURE pro_1(CHARACTER VARYING(10))
 RETURNS VARCHAR(ANY)
 EXECUTE AS OWNER
 LANGUAGE NZPLSQL AS
 BEGIN_PROC
 DECLARE
 jqid_cursor record;
 BEGIN
 -- procedure body
 FOR jqid_cursor for select f_jqid from t_scenicday1 group by f_jqid  LOOP
     RAISE NOTICE '景區編號: % ', jqid_cursor;
     insert into mytest(
     select w.*, f_ctname from
     (select f_jqid,f_ykprovinceid,sum(f_xzyhs) as su from t_scenicday1
     where f_tjsj like '2014-06-01%' and f_jqid=jqid_cursor
     group by f_jqid,f_ykprovinceid) w
     left join t_province pro on w.f_ykprovinceid=pro.f_ctid);
 END LOOP;