ORACLE PIVOT Stored Procedures PL/SQL

ORACLE SQL函數_PIVOT動態縱向資料轉橫向查詢

陳淑敏 2019/12/18 09:27:20
489

1. 前言

因現行專案是Oracle資料庫且資料表Schema建構成Key/Value的結

構,因此為了將多筆縱向資料且動態轉為橫向欄位(因資料會有不同筆

數的變化)Oracle也有跟MS SQL一樣名稱的PIVOT函數可使用,

因此就依循MS SQL動態PIVOT的作法實作Oracle版本。此運算式適

用於Oracle 11g以上版本。

 

2. 開始前準備

實作此運算式是建立於以下版本的環境:

        l    ORACLE Database 19C

       l SqlDeveloper 18.2.0.183

       l Visual Studio Community 2017

 

3. PIVOT實作演練

1.    先建立資料表[SCV_CST_INF],執行以下語法即可:

--刪除資料表
drop table SCV_CST_INF;

--建立資料表
create table SCV_CST_INF
(
CST_ID  varchar2(11),
CST_INF_TYP_ID  number(10),
CST_INF_VAL  varchar2(100),
SRC_STM_ID  number(10),
UPD_DT  char(8)
);

--修改資料表
ALTER TABLE SCV_CST_INF
ADD CONSTRAINT PK_SCV_CST_INF
PRIMARY KEY (CST_ID, CST_INF_TYP_ID);

--編輯欄位名稱
COMMENT ON COLUMN SCV_CST_INF.CST_ID IS '客戶ID';
COMMENT ON COLUMN SCV_CST_INF.CST_INF_TYP_ID IS '客戶產品資訊類別';
COMMENT ON COLUMN SCV_CST_INF.CST_INF_VAL IS '客戶產品資訊內容';
COMMENT ON COLUMN SCV_CST_INF.SRC_STM_ID IS '資料來源代碼';
COMMENT ON COLUMN SCV_CST_INF.UPD_DT IS '資料更新日';

--清除資料表 
TRUNCATE TABLE SCV_CST_INF; 


--新增資料表

--基本資料
Insert into SCV_CST_INF (CST_ID,CST_INF_TYP_ID,CST_INF_VAL, UPD_DT) values ('A123xxxxxx',1041000001, N'陳x伶', N'20190821');
Insert into SCV_CST_INF (CST_ID,CST_INF_TYP_ID,CST_INF_VAL, UPD_DT) values ('A123xxxxxx',1041000002, N'1968xxxx', N'20190821');
Insert into SCV_CST_INF (CST_ID,CST_INF_TYP_ID,CST_INF_VAL, UPD_DT) values ('A123xxxxxx',1041000003, N'1', N'20190821');
Insert into SCV_CST_INF (CST_ID,CST_INF_TYP_ID,CST_INF_VAL, UPD_DT) values ('A123xxxxxx',1041000004, N'text', N'20190821');
Insert into SCV_CST_INF (CST_ID,CST_INF_TYP_ID,CST_INF_VAL, UPD_DT) values ('A123xxxxxx',1041000005, N'text', N'20190821');
Insert into SCV_CST_INF (CST_ID,CST_INF_TYP_ID,CST_INF_VAL, UPD_DT) values ('A123xxxxxx',1041000006, N'text', N'20190821');
Insert into SCV_CST_INF (CST_ID,CST_INF_TYP_ID,CST_INF_VAL, UPD_DT) values ('A123xxxxxx',1041000007, N'text', N'20190821');
Insert into SCV_CST_INF (CST_ID,CST_INF_TYP_ID,CST_INF_VAL, UPD_DT) values ('A123xxxxxx',1041000008, N'text', N'20190821');
Insert into SCV_CST_INF (CST_ID,CST_INF_TYP_ID,CST_INF_VAL, UPD_DT) values ('A123xxxxxx',1041000009, N'text', N'20190821');
Insert into SCV_CST_INF (CST_ID,CST_INF_TYP_ID,CST_INF_VAL, UPD_DT) values ('A123xxxxxx',1041000010, N'text', N'20190821');

--職業資訊
Insert into SCV_CST_INF (CST_ID,CST_INF_TYP_ID,CST_INF_VAL, UPD_DT) values ('A123xxxxxx',1041000011, N'鴻海精密工業股份有限公司', N'20190821');
Insert into SCV_CST_INF (CST_ID,CST_INF_TYP_ID,CST_INF_VAL, UPD_DT) values ('A123xxxxxx',1041000012, N'會計', N'20190821');
Insert into SCV_CST_INF (CST_ID,CST_INF_TYP_ID,CST_INF_VAL, UPD_DT) values ('A123xxxxxx',1041000013, N'經理', N'20190821');
Insert into SCV_CST_INF (CST_ID,CST_INF_TYP_ID,CST_INF_VAL, UPD_DT) values ('A123xxxxxx',1041000014, N'新北市土城區自由街2號', N'20190821');

--聯絡資訊
Insert into SCV_CST_INF (CST_ID,CST_INF_TYP_ID,CST_INF_VAL, UPD_DT) values ('A123xxxxxx',1041000015, N'09101xxxxx', N'20190821');
Insert into SCV_CST_INF (CST_ID,CST_INF_TYP_ID,CST_INF_VAL, UPD_DT) values ('A123xxxxxx',1041000016, N'02-234xxxxx', N'20190821');
Insert into SCV_CST_INF (CST_ID,CST_INF_TYP_ID,CST_INF_VAL, UPD_DT) values ('A123xxxxxx',1041000017, N'02-226xxxxx', N'20190821');
Insert into SCV_CST_INF (CST_ID,CST_INF_TYP_ID,CST_INF_VAL, UPD_DT) values ('A123xxxxxx',1041000018, N'linlin@yahoo.com.tw', N'20190821');
Insert into SCV_CST_INF (CST_ID,CST_INF_TYP_ID,CST_INF_VAL, UPD_DT) values ('A123xxxxxx',1041000019, N'新北市板橋區三民路200號', N'2019xxxx');

--金控產品持有
Insert into SCV_CST_INF (CST_ID,CST_INF_TYP_ID,CST_INF_VAL, UPD_DT) values ('A123xxxxxx',1041000020, N'1', N'20190821');
Insert into SCV_CST_INF (CST_ID,CST_INF_TYP_ID,CST_INF_VAL, UPD_DT) values ('A123xxxxxx',1041000021, N'0', N'20190821');

 

2.    撈取已建立完成的資料表[SCV_CST_INF]

SELECT * FROM SCV_CST_INF


 

3.    Sql Developer撰寫預儲程序實作PIVOT運算子,撈取資料表[SCV_CST_INF]CST_INF_TYP_ID欄位的資料由縱轉橫轉成輸出多個資料行(此處即為動態產生),再將CST_INF_VAL欄位的資料由縱轉橫轉成對應CST_INF_TYP_ID資料行資料值

create or replace PROCEDURE p_GetCustomerBasicInfo(p_CstId IN VARCHAR2, p_Collection OUT SYS_REFCURSOR) AS
v_Getstrulvlsql Varchar2(2000) := '';
v_Getsql      Varchar2(2000) := '';

BEGIN 

--獲取查詢並拼接成一定格式的字元串(xxxx,xxxx,xxxx),作為參數傳遞到動態SQL裡
SELECT LISTAGG(CST_INF_TYP_ID,',') INTO v_Getstrulvlsql FROM (SELECT DISTINCT CST_INF_TYP_ID FROM SCV_CST_INF WHERE CST_ID = p_CstId ORDER BY CST_INF_TYP_ID) SCV_CST_INF;

--顯示上述v_Getstrulvlsql變數的結果
--DBMS_OUTPUT.PUT_LINE( 'SQL:' || v_Getstrulvlsql ); 

--動態SQL語法Pivot函數
v_Getsql := v_Getsql || 'SELECT * FROM   (SELECT  CST_INF_TYP_ID, CST_INF_VAL FROM SCV_CST_INF WHERE CST_ID = '''||p_CstId||''' ';
v_Getsql := v_Getsql || ' UNION ALL SELECT  CST_OTR_INF_TYP_ID AS CST_INF_TYP_ID, CST_OTR_INF_VAL AS CST_INF_VAL ';
v_Getsql := v_Getsql || ' FROM SCV_CST_OTR_INF WHERE CST_ID = '''||p_CstId||''' ';
v_Getsql := v_Getsql || ' AND CST_OTR_INF_TYP_ID = 1043000001 ORDER BY CST_INF_TYP_ID) c';
v_Getsql := v_Getsql || ' PIVOT (Max(c.CST_INF_VAL) FOR CST_INF_TYP_ID IN ( '||v_Getstrulvlsql||' ))'; 

--顯示上述v_Getsql變數的結果
--DBMS_OUTPUT.PUT_LINE( 'SQL1:' || v_Getsql );

--開啟 cursor,取得資料集合
OPEN p_Collection FOR v_Getsql;

END p_GetCustomerBasicInfo;

 

4.    如欲檢測預儲程序執行結果正確與否,可在Sql Developer上點選『執行鈕』(如紅框所示)即開啟【執行PL/SQL】視窗,並輸入模式為IN的參數:P_CSTID資料值:A123xxxxxx(如紅框所示),按下『確定鈕』


 

5.    執行後,切換至【輸出變數】頁籤(如紅框所示),即可查看OUT的參數:P_ COLLECTION結果資料集


 

4. UI實作演練

       接下來,要將預儲程序的結果資料實作呈現在UI畫面上,因想簡單快速撰寫前端功能,故選擇ASP.NETGridView繫結資料顯示,以下就概述UI及程式碼說明:

     

    1.    建立一個ASP.NET Web應用程式專案,新建WebForm並在畫面上佈署以下物件:TextBox(預儲程序IN參數)Button(執行預儲程序)GridView(呈現執行結果)Lable(顯示錯誤訊息)

           2.    Button_Click事件撰寫程式碼,如下圖所示:


dataGridView1.DataSource = null;
lblMessage.Visible = false;

if (txtID.Text != "")
{
    DataSet ds = new DataSet("dsDataList"); 

    //連線Oracle資料庫
    using (OracleConnection sc = new OracleConnection("連線字串"))
    {
       sc.Open(); 

       try
       {
          //執行預儲程序
          using (OracleCommand cmd1 = new OracleCommand("P_GETCUSTOMERBASICINFO", sc))
          {
             cmd1.CommandType = CommandType.StoredProcedure; 

             //傳入UI的TextBox輸入值
             cmd1.Parameters.Add(new OracleParameter("p_CstId", OracleDbType.NVarchar2)).Value = txtID.Text.ToUpper(); 

             //定義預儲程序回傳值
             OracleParameter param1 = cmd1.Parameters.Add("p_Collection", OracleDbType.RefCursor); 

             param1.Direction = ParameterDirection.Output; 

             OracleDataAdapter da = new OracleDataAdapter(cmd1); 

             da.Fill(ds, "dtDataList"); 

             //呈現回傳的結果資料集
             dataGridView1.DataSource = ds.Tables["dtDataList"];
             dataGridView1.DataBind();
          }
       }
       catch (Exception ex)
       {
          //顯示錯誤訊息
          lblMessage.Visible = true;
          lblMessage.Text = ex.ToString();
       }
    }
}
else
{
    //顯示錯誤訊息
    lblMessage.Visible = true;
    lblMessage.Text = "請輸入身分證字號!!";
}

 

3.    完成上述程式碼並執行程式,隨即輸入身分證字號,按下『OracleTestPivot鈕』後,即可查看執行PIVOT動態縱向資料轉橫向的結果資料。


 

5. 參考來源

l  pl/sql 變數的宣告與賦值:

https://www.itread01.com/content/1542902644.html

http://dbtim.blogspot.com/2016/04/3-plsql.html

http://alan4eyes.blogspot.com/2016/07/plsql-variables-plsql-data-types.html

l  Oracle 如何建立Stored Procedureshttps://matthung0807.blogspot.com/2017/08/oracle-stored-procedures.html

l  Oracle儲存過程返回 結果集 table形式 (使用sys_refcursor 及程式包package 兩種方式)https://www.itread01.com/p/1402257.html

l  Oracle 連接字串:https://matthung0807.blogspot.com/2017/09/oracle_18.html

l  LISTAGGhttps://blog.csdn.net/weixiaohuai/article/details/84998212

l  MS SQL 動態 PIVOThttp://sharedderrick.blogspot.com/2013/02/pivot-dynamic-pivot.html

l  使用 SYS_REFCURSOR 來回傳資料集:https://blog.xuite.net/f8789/DCLoveEP/47319330-C%23+-+%E4%BD%BF%E7%94%A8+SYS_REFCURSOR+%E4%BE%86%E5%9B%9E%E5%82%B3%E8%B3%87%E6%96%99%E9%9B%86

l  Oracle 動態SQL實現SQL查詢子集行轉列:https://ifun01.com/VNJXFL5.html

 

 

陳淑敏