e-learning、オラクル研修、LMS(学習管理システム)のiStudy

e-learning、オラクル研修、LMS(学習管理システム)のiStudy

第99回 「ツール作成(問い合わせ結果を縦に表示する)」

2014.06.12

こんにちは。インストラクターの蓑島です。
今回は新しいトピックではなく、これまで解説してきたPL/SQLを使って「こんなことができれば便利!」と思ったものを作りました。

皆さんは、表を問い合わせるとき、SELECT * FROM ~ で問い合わせたら、列の数が多く結果が横に長くなり見づらいと思ったことはありませんか?
そこで、縦に表示できれば見やすくなると思い、今回PL/SQLでそのようなツールを作ってみました。ストアドプロシージャです。

ただし、複数行となるとその1行1行を縦に表示すると大変な量になるので、結果が1行となる表の問い合わせに限定しました。0行や複数行ではエラーとします。また、結果表示はDBMS_OUTPUTパッケージによる画面表示なので、SQL*Plusや、SQL*Developerの画面、つまりコマンドラインでの使用となります。

では以下の作成例をご覧ください。
SQL> SHOW USER
ユーザーは"SCOTT"です

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
create or replace procedure vdict
    ( p_dict_name in varchar2,     -- パラメータ1=表名
      p_where     in varchar2)     -- パラメータ2=それを1行に特定するWHERE句の条件
is  -- 宣言部
    -- 表の各列の名前とデータ型および後に値もセットする変数
    type  col_rec is record          -- レコード型
      ( column_name varchar2(30),
        data_type   varchar2(106),
        value       varchar2(32767));
    type  col_rec_tab_type is table of col_rec;   -- そのレコード型のコレクションの型
    col_rec_tab  col_rec_tab_type;   -- その型の変数
    v_check_cnt    number;
    v_sql_str      varchar2(1000);
begin  -- 実行部
   -- 指定された表の各列の名前とデータ型を取得
   select column_name, data_type , null  bulk collect into col_rec_tab
   from  user_tab_columns
   where table_name = upper (p_dict_name)
   order by column_id;
   -- もしもその数が0件なら、指定した表が存在しない
   if col_rec_tab. count = 0 then
      raise_application_error
       (-20000, '表・ビュー('
           || upper (p_dict_name) || ')が存在しません' );
   end if;
   -- 指定された条件(p_where)でその表の行数を取得
   v_sql_str := 'select count(*) from  ' || p_dict_name || ' where ' || p_where;
   execute immediate v_sql_str into v_check_cnt;
   -- もしもその行数が1件でなければ、エラーとする
   if v_check_cnt <> 1  then
      raise_application_error
    (-20001, '指定された条件では1件にヒットしません ('
         ||to_char(v_check_cnt) || '件ヒット)' );
   end if;
   -- 指定された条件でその表の各列の値をコレクション変数に代入
   for i in 1..col_rec_tab. count loop
       if col_rec_tab(i).data_type in ( 'CHAR' , 'DATE' , 'VARCHAR2' , 'NUMBER' then
          v_sql_str := 'select ' || col_rec_tab(i).column_name
                       || ' from ' || p_dict_name || ' where ' || p_where;
          execute immediate v_sql_str into col_rec_tab(i).value;
       else
          col_rec_tab(i).value :=
                'データ型が、CHAR,VARCHAR2,NUMBER,DATE以外なのでスキップします' ;
       end if;
   end loop;
   -- コレクション変数に格納された各列の値を縦に画面表示
    for i in col_rec_tab. first ..col_rec_tab. last loop
       dbms_output.put_line( '●' || col_rec_tab(i).column_name);
       dbms_output.put_line(nvl(col_rec_tab(i).value, '(null)' ));
   end loop;
end ;
/
 
プロシージャが作成されました。

ここで作成したプロシージャの名前は、VDICTとしました。
第一パラメータ(p_dict_name)は表の名前、第2パラメータ(p_where)はその問い合わせを1行に特定するWHERE句の条件です。(2~3行目)

最初に5行目~11行目にかけて、対象となる表のすべての列の名前、データ型、そして後のステップでセットする値を格納するコレクション変数を定義します。
そして、15行目~19行目にかけて、user_tab_columnsビューを問い合わせてp_dict_nameで指定した表のすべての列名、データ型をコレクション変数に格納します。このとき、bulk cllect into文を使っていますが、これはバルクバインドでの一括フェッチです。
(詳しくは、バックナンバー第61回「バルク・バインド(SELECT BULK COLLECT INTOの場合)」を参照)

そして、ここでコレクション変数の件数が0件であれば指定した表が存在しないことになるので、エラーとしています。(20~25行目)

よって、それ以下の処理は指定した表が実際に存在する場合の処理となります。そして、次のチェックは指定された条件(p_where)で1件に絞り込みできるかどうかのチェックです。
そのために、動的SQLで、SELECT COUNT(*) FROM ~の文を生成し、その値を取得します。(26~28行目)
このパターンの動的SQLは、バックナンバー第45回「動的SQL (1行返す問い合わせの場合)」に詳しく解説してあります。

ここで取得した件数が1件でないときは、エラーとします。(29~34行目)

これ以下の処理は、確実に指定されたパラメータ(p_dict_name, p_where)で確実に1行の値を取得できるので、これ以上のチェックロジックは不要です。

そのような状態の中で、上記のチェックでコレクション変数に格納された列名を使って、ループ処理で1列ずつ該当列の値を取得し、コレクション変数のVALUEフィールドにその値を取得します。(35~45行目) 
途中で、データ型がCHAR, DATE, VARCHAR2, NUMBERでない場合は、文字にして表示することが難しいので、スキップする旨のメッセージをVALUEフィールドに格納します。(37行目のif文)
しかし、ほとんどの列はこの4つのデータ型のどれかに当てはまるのが実情です。

そして、最終的に各列の値をコレクション変数に取得した後、その変数の値をloop処理で1件ずつ、画面に表示します。(46~50行目) 
仮に上記のデータ型でない列があっても、コレクション変数の値には、「データ型が、CHAR,VARCHAR2,NUMBER,DATE以外なのでスキップします」というメッセージが格納されてますので、該当のその列の値にはそのメッセージが表示されます。

以上が、このプロシージャの処理の流れです。

では早速、「SELECT * FROM EMP WHERE EMPNO = 7934」という問い合わせをこのプロシージャを使って結果を縦に表示してみましょう。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SHOW USER
ユーザーは "SCOTT" です。
SET SERVEROUTPUT ON    -- 画面出力を有効にして
EXEC VDICT( 'EMP' , 'EMPNO = 7934' )   -- プロシージャ実行
--以下はプロシージャによる実行結果の画面表示
●EMPNO
7934
●ENAME
MILLER
●JOB
CLERK
●MGR
7782
●HIREDATE
82-01-23
●SAL
6050
●COMM
( null )
●DEPTNO
10
 
PL/SQLプロシージャが正常に完了しました。

そうすると、いかがですか?各列の値を縦に並べて表示されていますね。
各列の前には、●をつけて列名であることを表してます。その下の表示がその列の値です。NULL値のときは、(null)と表示してあります。

ここでプロシージャを実行しているのは、4行目です。
第1パラメータは対象となる表の名前ですから、「'EMP'」ですね。第2パラメータは1行に特定する条件である、「'EMPNO = 7934'」です。

もしも、「ENAME = 'MILLER'」といった条件を指定したいときは次のようにしてください。
EXEC VDICT ('EMP','ENAME =''MILLER''')

つまり文字列にシングルコート(')がある場合は、2個連続させるわけですね。

それでは、今回はここまでとします。他にも「こんなことができれば便利!」と思ったものがあれば作成してみます。
では次回、ご期待ください。

先頭へ戻る