こんにちは。SI部の安江です。
今回は、DBA(DataBaseAdministator)になりたての技術者向けに
「SQL文を作るSQL文」の活用方法を紹介したいと思います。
SQL文を作るSQL文って？
RDBMSを扱うシステムにおいて、DBAはデータベースに対して様々な作業をSQLを使用して行います。
その中の作業の一つに、RDBMS実機情報をインプットにして、SQLを作成し実行するといったことがあります。
例えば、これから次のような作業を行うとします。
- 開発中のシステムに作成している、テーブル名とテーブル毎の件数を実機から取得し一覧化したい
単純に作業を進めていくと、
①RDBMSにログインして、テーブル一覧取得のSQLを実行、テーブル一覧をファイルに保存
②テーブル一覧から、テーブル毎の件数を取得するSQLを作成するために、EXCELやテキストエディタを使って加工
③加工したSQLを用いてRDBMSに再度ログインしSQLを実行、結果を各テーブルと件数の一覧をファイルに保存
と、地道に作業していきます。
しかし、②の加工作業などある程度時間を要してしまう事、加工する際に人為的ミスが発生するリスクが発生します。
そこで、①と②のSQLを作成する作業を「SQL文を作るSQL文」を使って作成し、作業の効率化と人為的ミスを減らす方法を紹介していきたいと思います。
RDBMSによって若干手法が異なるため今回は、OracleDatabase・MySQL・PostgreSQL、3つのRDBMSで検証してみました。
SQL文を作るSQL文を作成してみる
環境の準備
まずは、検証で使用するデータベース環境を各RDBMS毎に環境作成します。
環境作成についての記述は今回省略させていただきます。
| データベース名 | company |
| データベースユーザ | casley |
| 作成テーブル | development_salary development_staff engineering_salary engineering_staff sales_salary sales_staff |
SQL文を作るSQL文を実行してみる
まずはSQL構文を整理していきます。
例として、OracleDatabaseで使用したSQLを紹介します。
①RDBMSから情報を取得するSQL部分です。where句でデータベースユーザ「casley」が所持すテーブルに条件を絞って検索しています。
②件数を取得するSQL（select count(*) from [テーブル名];）を生成する部分です。’’で閉じられた部分は文字列、||は文字列とテーブルの列項目を結合する演算子
①の構文だけを実行すると、「CASLEY」ユーザにはテーブルが6つ作成されていることがわかります。
SQL> select table_name from dba_tables where owner='CASLEY'; TABLE_NAME ------------------------------------------------------------ SALES_STAFF SALES_SALARY DEVELOPMENT_STAFF DEVELOPMENT_SALARY ENGINEERING_STAFF ENGINEERING_SALARY 6行が選択されました。
②の文字列と結合演算子を組み合わせることで、実行結果にSQL文として出力できます。
次にRDBMS毎に「SQL文を作るSQL文を実行してみる」を実行していきましょう。
1．OracleDatabaseの場合
[実行文]
C:\>sqlplus system/[password]
SQL> select ‘select ”’ || table_name || ”’,count(*) from ‘ || table_name || ‘;’
2 from dba_tables where owner=’CASLEY’;
[実行結果]
実行結果に生成されたSQLが表示されていることがわかります。
C:\>sqlplus system/[password] ←--- RDBMSログイン SQL*Plus: Release 11.2.0.2.0 Production on 日 8月 9 13:08:34 2015 Copyright (c) 1982, 2010, Oracle. All rights reserved. Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production に接続されました。 SQL> select 'select ''' || table_name || ''',count(*) from ' || table_name || ';' ←--- SQL実行文 2 from dba_tables where owner='CASLEY'; ←--- SQL実行文 'SELECT'''||TABLE_NAME||''',COUNT(*)FROM'||TABLE_NAME||';' ←--- 実行結果「ヘッダ」 -------------------------------------------------------------- ←--- 実行結果「ヘッダ」 select 'SALES_STAFF',count(*) from SALES_STAFF; ←--- 実行結果 select 'SALES_SALARY',count(*) from SALES_SALARY; ←--- 実行結果 select 'DEVELOPMENT_STAFF',count(*) from DEVELOPMENT_STAFF; ←--- 実行結果 select 'DEVELOPMENT_SALARY',count(*) from DEVELOPMENT_SALARY; ←--- 実行結果 select 'ENGINEERING_STAFF',count(*) from ENGINEERING_STAFF; ←--- 実行結果 select 'ENGINEERING_SALARY',count(*) from ENGINEERING_SALARY; ←--- 実行結果 6行が選択されました。 ←--- 実行結果「フッタ」 SQL>
2．MySQLの場合
[実行文]
c:\>mysql -u root -p[password] company
mysql> select concat(‘select \”, table_name, ‘\’,count(*) from ‘, table_name, ‘;’)
-> from information_schema.tables where TABLE_SCHEMA='company';
[実行結果]
実行結果に生成されたSQLが表示されます。実行結果の内容はOracleDatabaseと変わりませんが、ヘッダやフッタなどの出力形式若干異なります。
c:\>mysql -u root -p[password] company ←--- RDBMSログイン
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select concat('select \'', table_name, '\',count(*) from ', table_name, ';') ←--- SQL実行文
-> from information_schema.tables where TABLE_SCHEMA='company'; ←--- SQL実行文
+-----------------------------------------------------------------------+ ←--- 実行結果「ヘッダ」
| concat('select \'', table_name, '\',count(*) from ', table_name, ';') | ←--- 実行結果「ヘッダ」
+-----------------------------------------------------------------------+ ←--- 実行結果「ヘッダ」
| select 'development_salary',count(*) from development_salary; | ←--- 実行結果
| select 'development_staff',count(*) from development_staff; | ←--- 実行結果
| select 'engineering_salary',count(*) from engineering_salary; | ←--- 実行結果
| select 'engineering_staff',count(*) from engineering_staff; | ←--- 実行結果
| select 'sales_salary',count(*) from sales_salary; | ←--- 実行結果
| select 'sales_staff',count(*) from sales_staff; | ←--- 実行結果
+-----------------------------------------------------------------------+ ←--- 実行結果「フッタ」
6 rows in set (0.00 sec) ←--- 実行結果「フッタ」
mysql>
3．PostgreSQLの場合
[実行文]
c:\>psql -U casley -d company
company=# select ‘select ”’ || tablename || ”’,count(*) from ‘ || tablename || ‘;’
company-# from pg_tables where tableowner=’casley’;
[実行結果]
c:\>psql -U casley -d company ←--- RDBMSログイン
ユーザ casley のパスワード:[password] ←--- RDBMSログイン
psql (9.4.4)
"help" でヘルプを表示します.
company=# select 'select ''' || tablename || ''',count(*) from ' || tablename || ';' ←--- SQL実行文
company-# from pg_tables where tableowner='casley'; ←--- SQL実行文
?column? ←--- 実行結果「ヘッダ」
--------------------------------------------------------------- ←--- 実行結果「ヘッダ」
select 'sales_staff',count(*) from sales_staff; ←--- 実行結果
select 'sales_salary',count(*) from sales_salary; ←--- 実行結果
select 'development_staff',count(*) from development_staff; ←--- 実行結果
select 'development_salary',count(*) from development_salary; ←--- 実行結果
select 'engineering_staff',count(*) from engineering_staff; ←--- 実行結果
select 'engineering_salary',count(*) from engineering_salary; ←--- 実行結果
(6 行) ←--- 実行結果「フッタ」
このようにRDBMSの情報と文字列をうまく連結することによって、次に実行したいSQL文を生成して実行結果に出力することができます。
※補足
RDBMSによってSQL実行文の書き方に違いがあります。
＜文字列の連結方法＞
| OracleDatabase | 文字列1 || 文字列2 または、 CONCAT(文字列1, 文字列2) |
| MySQL | CONCAT(文字列1, 文字列2) |
| PostgreSQL | 文字列1 || 文字列2 |
＜テーブル一覧情報を所持しているRDBMS管理テーブル＞
| OracleDatabase | dba_tables |
| MySQL | information_schema.tables |
| PostgreSQL | pg_tables |
＜エスケープ文字の指定方法(‘を文字列として扱うとき)＞
| OracleDatabase | ”’ |
| MySQL | \’ |
| PostgreSQL | ”’ |
実行結果をsqlテキストに出力
先に紹介した実行結果のSQL文をコピーして使うのもいいのですが、せっかくなのでsqlファイルに書き出しもっと簡略化していきましょう。
実行結果を見ていただくとわかりますが、実行結果でもヘッダやフッタやログイン時メッセージなど余分な出力も含まれてしまうため、実行結果だけ出力するよう限定してsqlテキストに書き出していきます。
1．OracleDatabaseの場合
[SQL文を作るSQL文をsqlファイルに保存]
C:\sql_create\oracle\createsql_oracle.sql
C:\sql_create\oracle>type createsql_oracle.sql set head off ←--- 実行結果に「ヘッダ」を表示させない set feed off ←--- 実行結果に「フッタ」を表示させない spool "C:\sql_create\oracle\select.sql" ←--- SQLの問い合わせ結果を指定のファイルに保存 select 'select ''' || table_name || ''',count(*) from ' || table_name || ';' ←--- SQL実行文 from dba_tables ; ←--- SQL実行文 spool off ←--- ファイルに保存終了 exit
[SQL文を作るSQL文を実行]
sqlplus起動オプションで[@実行するSQLファイル名]を指定して実行します。
C:\>sqlplus system/[password] @C:\sql_create\oracle\createsql_oracle.sql ←--- RDBMSログイン+sqlファイル実行 SQL*Plus: Release 11.2.0.2.0 Production on 日 8月 9 15:24:46 2015 Copyright (c) 1982, 2010, Oracle. All rights reserved. Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production に接続されました。 select 'SALES_STAFF',count(*) from SALES_STAFF; select 'SALES_SALARY',count(*) from SALES_SALARY; select 'DEVELOPMENT_STAFF',count(*) from DEVELOPMENT_STAFF; select 'DEVELOPMENT_SALARY',count(*) from DEVELOPMENT_SALARY; select 'ENGINEERING_STAFF',count(*) from ENGINEERING_STAFF; select 'ENGINEERING_SALARY',count(*) from ENGINEERING_SALARY; Oracle Database 11g Express Edition Release 11.2.0.2.0 - Productionとの接続が切断されました。 C:\sql_create\oracle> C:\sql_create\oracle>type C:\sql_create\oracle\select.sql ←--- 実行結果を保存したファイルを開く select 'SALES_STAFF',count(*) from SALES_STAFF; ←--- select.sqlファイル内容 select 'SALES_SALARY',count(*) from SALES_SALARY; ←--- select.sqlファイル内容 select 'DEVELOPMENT_STAFF',count(*) from DEVELOPMENT_STAFF; ←--- select.sqlファイル内容 select 'DEVELOPMENT_SALARY',count(*) from DEVELOPMENT_SALARY; ←--- select.sqlファイル内容 select 'ENGINEERING_STAFF',count(*) from ENGINEERING_STAFF; ←--- select.sqlファイル内容 select 'ENGINEERING_SALARY',count(*) from ENGINEERING_SALARY; ←--- select.sqlファイル内容 C:\sql_create\oracle>
2．MySQLの場合
[SQL文を作るSQL文をsqlファイルに保存]
C:\sql_create\mysql\createsql_mysql.sql
select concat('select \'', table_name, '\',count(*) from ', table_name, ';') ←--- SQL実行文
from information_schema.tables where TABLE_SCHEMA='company'; ←--- SQL実行文
[SQL文を作るSQL文を実行]
mysqlの起動オプションで[-N(ヘッダ非表示)]と[-e 実行するSQLファイル名]を指定して実行結果をリダイレクトします。
C:\>mysql -u root -p[password] -N -e "source C:\sql_create\mysql\createsql_mysql.sql" > C:\sql_create\mysql\select.sql ←--- RDBMSログイン+sqlファイル実行 Warning: Using a password on the command line interface can be insecure. c:\> c:\>type C:\sql_create\mysql\select.sql ←--- 実行結果を保存したファイルを開く select 'development_salary',count(*) from development_salary; ←--- select.sqlファイル内容 select 'development_staff',count(*) from development_staff; ←--- select.sqlファイル内容 select 'engineering_salary',count(*) from engineering_salary; ←--- select.sqlファイル内容 select 'engineering_staff',count(*) from engineering_staff; ←--- select.sqlファイル内容 select 'sales_salary',count(*) from sales_salary; ←--- select.sqlファイル内容 select 'sales_staff',count(*) from sales_staff; ←--- select.sqlファイル内容 c:\>
3．PostgreSQLの場合
[SQL文を作るSQL文をsqlファイルに保存]
C:\sql_create\PostgreSQL\create_postgre.sql
select 'select ''' || tablename || ''',count(*) from ' || tablename || ';' ←--- SQL実行文 from pg_tables where tableowner='casley'; ←--- SQL実行文
[SQL文を作るSQL文を実行]
psqlの起動オプションで[-t(ヘッダフッタ非表示)]と[-f “実行するSQLファイル名”]、[–output=”実行結果ファイル名”]を指定して実行します。
c:\>psql -U casley -d company -t -f "C:\sql_create\PostgreSQL\create_postgre.sql" --output="C:\sql_create\PostgreSQL\select.sql" ←--- RDBMSログイン+sqlファイル実行 ユーザ casley のパスワード:[password] c:\> c:\>type C:\sql_create\PostgreSQL\select.sql ←--- 実行結果を保存したファイルを開く select 'sales_staff',count(*) from sales_staff; ←--- select.sqlファイル内容 select 'sales_salary',count(*) from sales_salary; ←--- select.sqlファイル内容 select 'development_staff',count(*) from development_staff; ←--- select.sqlファイル内容 select 'development_salary',count(*) from development_salary; ←--- select.sqlファイル内容 select 'engineering_staff',count(*) from engineering_staff; ←--- select.sqlファイル内容 select 'engineering_salary',count(*) from engineering_salary; ←--- select.sqlファイル内容 c:\>
最後に
今回紹介させていただいた記事の方法を使って、他にも様々なSQL文を作成することが可能です。
（私の経験上、Oracleのインデックス再編成や統計情報取得を行うときにこの方法をよく使っていました）
DBAをある程度経験をしてきている人は知っている方も多いと思いますが、これからRDBMSを扱う業務を経験するような方は、こういった小技を多く経験し、効率的で安全に作業を行ってもらえたらなと思います。
