sqlplus .sqlファイル実行：コマンドラインから結果を簡単出力

By /

SQL*Plus .sqlファイル実行：コマンドラインから結果を簡単出力：詳細解説

SQLPlusは、Oracleデータベースとインタラクティブに、あるいはスクリプトを通じて対話するためのコマンドラインインターフェースです。.sqlファイルにSQL文を記述し、SQLPlusを用いて実行することで、データベースの操作を自動化したり、繰り返し実行したりすることが可能です。本記事では、SQL*Plusで.sqlファイルを実行し、コマンドラインから結果を簡単に、そして柔軟に出力するための様々な方法について、詳細に解説します。

1. SQL*Plusの基本:

まず、SQL*Plusの基本的な概念と使い方を理解することが重要です。

  • 起動: コマンドラインからsqlplusと入力し、ユーザー名/パスワード@接続文字列を入力することでSQL*Plusを起動します。例: sqlplus scott/tiger@orcl
  • 終了: EXITまたはQUITコマンドでSQL*Plusを終了します。
  • コマンド: SQL*Plusは様々なコマンドを提供します。よく使うコマンドには、SET, SHOW, DESCRIBE, SPOOLなどがあります。
  • .sqlファイルの実行: @ファイル名またはSTART ファイル名コマンドで.sqlファイルを実行します。

2. .sqlファイル実行の基本:

.sqlファイルは、実行したいSQL文やSQL*Plusコマンドを記述したテキストファイルです。

  • 記述ルール: ファイルには、セミコロン(;)で区切られたSQL文やSQL*Plusコマンドを記述します。コメントは--で開始します。
  • 例:
    “`sql
    — テーブルの作成
    CREATE TABLE employees (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    salary NUMBER
    );

— データの挿入
INSERT INTO employees (id, name, salary) VALUES (1, ‘John Doe’, 50000);
INSERT INTO employees (id, name, salary) VALUES (2, ‘Jane Smith’, 60000);

— データのSELECT
SELECT * FROM employees;

COMMIT;
“`

この.sqlファイルをemployees.sqlという名前で保存し、SQL*Plusから@employees.sqlまたはSTART employees.sqlと入力することで実行できます。

3. コマンドラインからの実行と標準出力:

SQL*Plusを起動せずに、コマンドラインから直接.sqlファイルを実行し、その結果を標準出力（通常は画面）に出力する方法はいくつかあります。

  • 基本構文: sqlplus ユーザー名/パスワード@接続文字列 @ファイル名
  • 例: sqlplus scott/tiger@orcl @employees.sql

このコマンドを実行すると、employees.sqlファイルが実行され、その結果がコマンドラインに表示されます。エラーが発生した場合も、エラーメッセージが標準出力に出力されます。

4. 標準出力へのフォーマット制御:

標準出力に出力される結果をより見やすく、目的に合わせて調整するために、SQL*Plusの様々なSETコマンドを活用できます。

  • SET ECHO {ON | OFF}:
    • ON: .sqlファイル内の各コマンドを、実行前に標準出力に表示します。デバッグに役立ちます。
    • OFF: コマンドを表示しません。デフォルトはOFFです。
  • SET FEEDBACK {n | ON | OFF}:
    • n: SELECT文がn行以上を返した場合に、行数を表示します。
    • ON: 行数を常に表示します。
    • OFF: 行数を表示しません。
  • SET HEADING {ON | OFF}:
    • ON: SELECT文の結果にヘッダー（カラム名）を表示します。デフォルトはONです。
    • OFF: ヘッダーを表示しません。
  • SET LINESIZE n:
    • 1行の最大文字数をnに設定します。これにより、出力が途中で折り返されるのを防ぎ、見やすく整形できます。デフォルトは80です。通常は120以上、場合によっては200以上に設定します。
  • SET PAGESIZE n:
    • 1ページあたりの行数をnに設定します。n行ごとにヘッダーが再表示されます。0に設定すると、ヘッダーは1度しか表示されません。
  • SET TRIMSPOOL {ON | OFF}:
    • ON: SPOOLファイル内の行末の空白を削除します。SPOOLファイルに出力する際に便利です。
    • OFF: 空白を削除しません。デフォルトはOFFです。
  • SET TERMOUT {ON | OFF}:
    • ON: 画面（標準出力）に結果を表示します。
    • OFF: 画面に結果を表示しません。SPOOLファイルに出力する場合に、画面表示を抑制するために使用します。
  • SET NUMWIDTH n:
    • 数値データの表示幅をnに設定します。
  • SET NULL '文字列':
    • NULL値を指定された文字列で表示します。例えば、SET NULL 'N/A'とすると、NULL値が’N/A’と表示されます。
  • COLUMN カラム名 FORMAT A数字:
    • 特定のカラムの表示形式を設定します。A数字は文字列型カラムの表示幅を指定します。例: COLUMN name FORMAT A20はnameカラムの表示幅を20文字に設定します。
  • COLUMN カラム名 FORMAT 数字.数字:
    • 数値型カラムの表示形式を設定します。例: COLUMN salary FORMAT 999,999.00はsalaryカラムをカンマ区切りで、小数点以下2桁まで表示します。
  • SET SQLPROMPT '文字列':
    • SQL*Plusのプロンプトを変更します。例えば、SET SQLPROMPT 'MYDB>'とすると、プロンプトがMYDB>になります。

これらのSETコマンドを.sqlファイルに記述することで、実行時に自動的にフォーマットが適用され、コマンドラインへの出力が見やすくなります。

例:

“`sql
— employees_formatted.sql
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING ON
SET LINESIZE 120
SET PAGESIZE 50
SET NULL ‘N/A’

COLUMN name FORMAT A20
COLUMN salary FORMAT 999,999.00

SELECT id, name, salary FROM employees;
“`

コマンドラインから sqlplus scott/tiger@orcl @employees_formatted.sql を実行すると、employeesテーブルのデータが指定されたフォーマットで表示されます。

5. SPOOLコマンドによるファイル出力:

コマンドラインへの出力だけでなく、結果をファイルに保存することも重要です。SQL*PlusのSPOOLコマンドを使用することで、.sqlファイルの実行結果をテキストファイルに出力できます。

  • SPOOL ファイル名: 指定されたファイル名で出力ファイルを作成し、以降の出力をそのファイルに書き込みます。
  • SPOOL OFF: 出力ファイルの書き込みを停止します。

例:

“`sql
— employees_to_file.sql
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING ON
SET LINESIZE 120
SET PAGESIZE 50
SET NULL ‘N/A’

COLUMN name FORMAT A20
COLUMN salary FORMAT 999,999.00

SPOOL employees_output.txt

SELECT id, name, salary FROM employees;

SPOOL OFF
“`

コマンドラインから sqlplus scott/tiger@orcl @employees_to_file.sql を実行すると、employeesテーブルのデータがemployees_output.txtというテキストファイルに保存されます。

6. エラー処理:

.sqlファイル実行中にエラーが発生した場合の処理も考慮する必要があります。

  • SET ERRORLOGGING {ON | OFF}:
    • ON: エラーが発生した場合、エラーメッセージを自動的にエラーログファイルに書き込みます。
    • OFF: エラーログを書き込みません。

SQLPlusは、エラーが発生すると実行を中断します。しかし、WHENEVER SQLERROR EXIT ROLLBACKコマンドを使用することで、エラー発生時にロールバックを実行し、SQLPlusを終了させることができます。

  • WHENEVER SQLERROR {EXIT [SUCCESS | FAILURE | n] | CONTINUE | ROLLBACK EXIT [SUCCESS | FAILURE | n] | ROLLBACK CONTINUE}:
    • EXIT [SUCCESS | FAILURE | n]: エラーが発生した場合、SQL*Plusを終了します。SUCCESSは成功、FAILUREは失敗、nは終了コードを指定します。
    • CONTINUE: エラーを無視して、次のコマンドを実行します。
    • ROLLBACK EXIT [SUCCESS | FAILURE | n]: エラーが発生した場合、ロールバックを実行し、SQL*Plusを終了します。
    • ROLLBACK CONTINUE: エラーが発生した場合、ロールバックを実行し、次のコマンドを実行します。

例:

“`sql
— error_handling.sql
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

CREATE TABLE example (
id NUMBER PRIMARY KEY
);

— 存在しないテーブルにデータを挿入 (エラーが発生する)
INSERT INTO non_existent_table (id) VALUES (1);

— この行は実行されない
SELECT * FROM example;

DROP TABLE example;
“`

このスクリプトを実行すると、INSERT文でエラーが発生し、ロールバックが実行され、SQL*Plusは終了コードFAILUREで終了します。exampleテーブルは作成されますが、エラー発生時にロールバックされるため、削除されます。

7. パラメータの引き渡し:

.sqlファイルにパラメータを渡すことで、汎用性の高いスクリプトを作成できます。

  • パラメータの指定: コマンドラインで.sqlファイル名の後にパラメータをスペースで区切って指定します。
  • パラメータの参照: .sqlファイル内で&1, &2, &3…のようにパラメータを参照します。&1は最初のパラメータ、&2は2番目のパラメータを表します。

例:

sql
-- parameterized_query.sql
SELECT * FROM employees WHERE id = &1;

コマンドラインから sqlplus scott/tiger@orcl @parameterized_query.sql 2 を実行すると、employeesテーブルからidが2の従業員が選択されます。

8. 複雑な処理:

PL/SQLブロックを.sqlファイルに記述することで、より複雑な処理を実行できます。

sql
-- plsql_example.sql
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM employees;
DBMS_OUTPUT.PUT_LINE('Number of employees: ' || v_count);
END;
/

このスクリプトを実行するには、SET SERVEROUTPUT ONコマンドを事前に実行する必要があります。

SET SERVEROUTPUT ON
sqlplus scott/tiger@orcl @plsql_example.sql

9. その他の便利なコマンド:

  • EDIT ファイル名: 指定されたファイルをテキストエディタで開きます。
  • DESCRIBE テーブル名: テーブルの構造を表示します。
  • SHOW ALL: 現在のSQL*Plusの設定を表示します。

10. 実践的な例:

例1: 特定の期間のトランザクションログを抽出するスクリプト:

“`sql
— extract_transactions.sql
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING ON
SET LINESIZE 120
SET PAGESIZE 50
SET NULL ‘N/A’

COLUMN transaction_id FORMAT 9999999
COLUMN transaction_date FORMAT A20
COLUMN amount FORMAT 999,999.00

SPOOL transactions_&1.txt

SELECT transaction_id, transaction_date, amount
FROM transactions
WHERE transaction_date BETWEEN TO_DATE(‘&2’, ‘YYYY-MM-DD’) AND TO_DATE(‘&3’, ‘YYYY-MM-DD’);

SPOOL OFF
“`

このスクリプトは、開始日と終了日をパラメータとして受け取り、指定された期間のトランザクションログをファイルに出力します。コマンドラインから sqlplus scott/tiger@orcl @extract_transactions.sql 2023-01-01 2023-01-31 を実行すると、2023年1月1日から1月31日までのトランザクションログがtransactions_2023-01-01.txtに保存されます。

例2: テーブルのバックアップスクリプト:

“`sql
— backup_table.sql
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET LINESIZE 120
SET PAGESIZE 0
SET TERMOUT OFF

SPOOL &1_backup.sql

SELECT ‘CREATE TABLE ‘ || ‘&1’ || ‘_backup AS SELECT * FROM ‘ || ‘&1’ || ‘;’;

SPOOL OFF

SET TERMOUT ON

@&1_backup.sql
“`

このスクリプトは、テーブル名をパラメータとして受け取り、そのテーブルのバックアップを作成するSQL文を生成し、実行します。コマンドラインから sqlplus scott/tiger@orcl @backup_table.sql employees を実行すると、employees_backupというテーブルが作成され、employeesテーブルのデータがコピーされます。

11. まとめ:

本記事では、SQL*Plusで.sqlファイルを実行し、コマンドラインから結果を簡単に出力するための様々な方法について解説しました。SETコマンドによるフォーマット制御、SPOOLコマンドによるファイル出力、エラー処理、パラメータの引き渡し、PL/SQLブロックの利用など、様々なテクニックを組み合わせることで、より効率的で柔軟なデータベース操作を実現できます。これらの知識を活かし、日々のデータベース管理業務を効率化し、自動化を進めてください。

補足:

  • 接続文字列（@orclなど）は、tnsnames.oraファイルに定義されている必要があります。
  • セキュリティ上の理由から、パスワードをコマンドラインに直接記述することは避けるべきです。環境変数やプロンプトを使用するなど、より安全な方法を検討してください。
  • 本記事で紹介したコマンドやオプションは、Oracleデータベースのバージョンによって異なる場合があります。ご自身の環境に合わせて、適切なドキュメントを参照してください。
  • SQL*Plusは強力なツールですが、GUIベースのツールと比較して学習コストが高い場合があります。しかし、コマンドライン操作に慣れることで、より効率的なデータベース管理が可能になります。
  • 常にバックアップを取ってから、データベースに変更を加えるスクリプトを実行するようにしてください。

この記事が、SQL*Plusを使った.sqlファイル実行と結果出力に関する理解を深める一助となれば幸いです。

コメントする

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

上部へスクロール