ORACLE PIVOT Stored Procedures PL/SQL

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

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

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.NET以GridView繫結資料顯示，以下就概述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 Procedures：https://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  LISTAGG：https://blog.csdn.net/weixiaohuai/article/details/84998212

l  MS SQL 動態 PIVOT：http://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

 

 

陳淑敏
相關文章
Ora2pg組態設定(下)
Ora2pg組態設定(上)
SQL Server 2019 資料庫鏡像設定實戰
Neo4j 因果叢集實作簡介
使用CTE遞迴實作階層式報表
SQL Server Function Table 應用
最新文章
Playwright : 'wait' For System Stability
Visual Regression Test
快速生成API測試案例 - Keploy
Visual Testing 原理簡介
API安全風險大揭密 (下集) - OWASP Top 10 API Security Risk 2023
探索性測試指南