【Oracle PL/SQL】縦持ちのデータを横持ちに変換する表関数

正規化されたテーブルは基本的に同一属性を縦にデータを持っています。
ただし、帳票などにデータを展開したい場合、これを横に持った形に整形して出したい時がたまにあります。

例えば、こんな感じの発注先の発注が受けられる日と配送できる日のカレンダーを格納したテーブルがあったとします。
発注日発注可能（0:不可、1:可能）配送可能（0:不可、1:可能）
2019/07/0111
2019/07/0210
2019/07/0311
2019/07/0410
2019/07/0511
2019/07/0601
2019/07/0701
この縦持ちのデータを横持ちに変換するとこうなります。
発注日2019/07/012019/07/022019/07/032019/07/042019/07/052019/07/062019/07/07
発注可能1111100
配送可能1010111
RDBとしてはイケてないデータの持ち方ですが、人が紙で見るには横持ちに変換した方が見やすいときもあります。

これをOracleのPL/SQL表関数で実現してみるのが今回の趣旨です。
サーバサイド言語で整形した方が早いんじゃないかとか言わない。

実行環境はブラウザでクラウド環境のOracle Databeseが実行できるOracle Live SQLを使います。

データの準備

まずはテーブルとデータを用意します。
-- 発注カレンダー
CREATE TABLE order_calendar (
    order_date              VARCHAR2(10) NOT NULL,
    is_orderable            NUMBER(1)    NOT NULL,
    is_deliverable          NUMBER(1)    NOT NULL,
CONSTRAINT pk_order_calendar PRIMARY KEY(order_date)
);

-- １ヵ月分のデータ
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/01', 1, 1);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/02', 1, 0);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/03', 1, 1);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/04', 1, 0);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/05', 1, 1);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/06', 0, 1);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/07', 0, 1);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/08', 1, 1);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/09', 1, 0);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/10', 1, 1);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/11', 1, 0);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/12', 1, 1);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/13', 0, 1);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/14', 0, 1);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/15', 1, 1);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/16', 1, 0);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/17', 1, 1);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/18', 1, 0);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/19', 1, 1);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/20', 0, 1);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/21', 0, 1);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/22', 1, 1);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/23', 1, 0);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/24', 1, 1);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/25', 1, 0);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/26', 1, 1);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/27', 0, 1);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/28', 0, 1);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/29', 1, 1);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/30', 1, 0);
INSERT INTO order_calendar( order_date, is_orderable, is_deliverable)VALUES( '2019/07/31', 1, 1);

表関数の定義

ストアドパッケージを使います。
まずはパッケージ仕様部。 
CREATE OR REPLACE PACKAGE pkg_order_calendar IS

   -- レコード
  TYPE rec_cal IS RECORD(
     col1               VARCHAR2(10)
    ,col2               VARCHAR2(10)
    ,col3               VARCHAR2(10)
    ,col4               VARCHAR2(10)
    ,col5               VARCHAR2(10)
    ,col6               VARCHAR2(10)
    ,col7               VARCHAR2(10)
  );

  -- ネスト表
  TYPE tbl_cal IS TABLE OF rec_cal;

  -- 表関数
  FUNCTION get_cal(
    base_date         IN    order_calendar.order_date%TYPE -- 基準日
  ) RETURN tbl_cal PIPELINED;


END pkg_order_calendar;
次にパッケージ本体です。 
CREATE OR REPLACE PACKAGE BODY pkg_order_calendar IS

  -- 表関数
  FUNCTION get_cal(
    base_date         IN    order_calendar.order_date%TYPE -- 基準日
  ) RETURN tbl_cal PIPELINED IS
  
    v_rec_cal         rec_cal;
    v_to_date         order_calendar.order_date%TYPE;
    v_col_count       PLS_INTEGER;
    v_set_col         VARCHAR2(10);
    
  BEGIN
    v_to_date := TO_CHAR(TO_DATE(base_date ,'yyyy/mm/dd') + 6, 'yyyy/mm/dd');
    
    FOR i IN 1..3 LOOP
      v_rec_cal := NULL;  -- レコードクリア
      v_col_count := 0;
      
      FOR v_rec_order_calendar IN(
        SELECT *
          FROM order_calendar
         WHERE order_date BETWEEN base_date AND v_to_date
         ORDER BY order_date
      ) LOOP
      
        CASE i
          WHEN 1 THEN v_set_col := v_rec_order_calendar.order_date;
          WHEN 2 THEN v_set_col := v_rec_order_calendar.is_orderable;
          WHEN 3 THEN v_set_col := v_rec_order_calendar.is_deliverable;
        END CASE;
        
        v_col_count := v_col_count + 1;
        
        CASE v_col_count
          WHEN 1 THEN v_rec_cal.col1 := v_set_col;
          WHEN 2 THEN v_rec_cal.col2 := v_set_col;
          WHEN 3 THEN v_rec_cal.col3 := v_set_col;
          WHEN 4 THEN v_rec_cal.col4 := v_set_col;
          WHEN 5 THEN v_rec_cal.col5 := v_set_col;
          WHEN 6 THEN v_rec_cal.col6 := v_set_col;
          WHEN 7 THEN v_rec_cal.col7 := v_set_col;
        END CASE;
      
      END LOOP;
      
      PIPE ROW(v_rec_cal);  -- 1レコード返す
    END LOOP;
  END get_cal;

END pkg_order_calendar;
実行してみます 
select * from pkg_order_calendar.get_cal('2019/07/10');
縦持ちが横持ちに変換できました。

書いてみると相当無駄なコードですね。
列数×行数分クエリ発行してますし…。

まあ、例え効率が悪いとわかっててもやらなきゃいけないときって往々にしてあるのでこれも一つの実装例だと思って頂ければ…。

Web屋には嫌われがちなOracleですが、使ってての安心感と強力なストアドはやっぱり魅力的です。

複雑で高額なライセンス価格さえ何とかなればなあ…。
プロとしてのOracle PL/SQL入門 【第3版】(Oracle 12c、11g、10g対応) (Oracle現場主義)

作ってみたOracle Database, PL/SQL, SQL

Posted by danishi