【Oracle】SQL*Plus 完全ガイド｜SET コマンド・SPOOL・スクリプト変数・よく使うコマンドまで解説

# 基本形: sqlplus ユーザー名/パスワード@接続文字列
sqlplus hr/hr_password@localhost:1521/orcl          # Easy Connect（ホスト:ポート/サービス名）
sqlplus hr/hr_password@ORCL                         # TNS 名（tnsnames.ora のエントリ）
sqlplus hr/"P@ss#word"@localhost:1521/orcl          # パスワードに特殊文字がある場合は引用符

# パスワードを後で入力する（コマンド履歴にパスワードを残さない）
sqlplus hr@localhost:1521/orcl                      # パスワードはプロンプトで入力

# パスワードなしで起動してから接続する
sqlplus /nolog
SQL> CONNECT hr/hr_password@localhost:1521/orcl

# SYS として接続する（AS SYSDBA が必要）
sqlplus sys/oracle@localhost:1521/orcl AS SYSDBA
sqlplus / AS SYSDBA                                 # OS 認証（ORACLE_SID 設定済みの環境）

# SQL ファイルをバッチ実行する（対話なし）
sqlplus hr/hr_password@localhost:1521/orcl @/path/to/script.sql
sqlplus -s hr/hr_password@localhost:1521/orcl @script.sql  # -s: サイレントモード（バナー非表示）

-- PAGESIZE: 1ページあたりの行数（ヘッダーが繰り返される間隔）
SET PAGESIZE 100        -- 100行ごとにヘッダーを表示
SET PAGESIZE 0          -- ヘッダーを表示しない（ファイル出力時に便利）

-- LINESIZE: 1行の表示幅（デフォルト 80）
SET LINESIZE 200        -- 1行に 200 文字まで表示（長い行が折り返されなくなる）

-- FEEDBACK: 実行結果の行数メッセージの制御
SET FEEDBACK ON         -- "N rows selected." を表示（デフォルト）
SET FEEDBACK OFF        -- 非表示
SET FEEDBACK 1          -- 1行以上のとき表示

-- SERVEROUTPUT: DBMS_OUTPUT の出力を表示する
SET SERVEROUTPUT ON             -- DBMS_OUTPUT.PUT_LINE が表示される
SET SERVEROUTPUT ON SIZE 1000000  -- バッファサイズを指定

-- TIMING: SQL の実行時間を表示する
SET TIMING ON           -- "Elapsed: 00:00:01.23" 形式で表示

-- ECHO: スクリプトの内容を実行時に表示する
SET ECHO ON             -- スクリプトの各コマンドを表示してから実行
SET ECHO OFF            -- 表示しない（バッチ処理向け）

-- TRIMSPOOL: ファイル出力時の末尾スペースを削除する
SET TRIMSPOOL ON        -- 各行の末尾スペースを除去（推奨）

-- NULL: NULL 値の表示文字（デフォルト: 空白）
SET NULL "(null)"       -- NULL を "(null)" と表示

-- NUMFORMAT: 数値の表示形式
SET NUMFORMAT 999,999,990.00   -- 数値をカンマ区切りで表示

-- AUTOCOMMIT: DML を自動コミットするか
SET AUTOCOMMIT OFF      -- 手動コミット（デフォルト）推奨
SET AUTOCOMMIT ON       -- 各 DML 後に自動コミット（注意して使う）

-- SPOOL: 以降の出力を指定ファイルに書き込む
SPOOL /tmp/query_result.txt

SELECT employee_id, last_name, salary FROM employees WHERE department_id = 60;

SPOOL OFF   -- ファイルへの書き込みを終了する（忘れると内容が保存されない）

-- SPOOL APPEND: 既存ファイルに追記する（上書きではなく追記）
SPOOL /tmp/query_result.txt APPEND

-- CSV ライクなファイルを生成する
SET PAGESIZE 0          -- ヘッダーなし
SET FEEDBACK OFF        -- "N rows selected" を非表示
SET COLSEP ","          -- 列の区切りをカンマに
SET LINESIZE 1000       -- 折り返しを防ぐ
SET TRIMSPOOL ON        -- 末尾スペースを除去

SPOOL /tmp/employees.csv
SELECT employee_id || ',' || last_name || ',' || salary FROM employees;
SPOOL OFF

-- 注意: SET COLSEP のカンマ設定では文字列に含まれるカンマがエスケープされないため
-- TO_CHAR で明示的に連結する方が安全

-- DEFINE: SQL*Plus 変数（& で参照する置換変数）
DEFINE dept_id = 60
SELECT * FROM employees WHERE department_id = &dept_id;
-- &dept_id が 60 に置換されて実行される

-- スクリプトに引数を渡す場合: コマンドラインから sqlplus @script.sql 60
-- スクリプト内: &1 が最初の引数
-- 例: SELECT * FROM employees WHERE department_id = &1;

-- && を使うと同じ変数の再利用時に再度確認を求めない（1回だけ確認）
SELECT * FROM &table_name WHERE dept = &&dept_id;

-- ACCEPT: ユーザーからの入力を受け付ける
ACCEPT dept_id NUMBER PROMPT 'Department ID を入力してください: '
ACCEPT dept_id NUMBER DEFAULT 60 PROMPT 'Department ID [60]: '
SELECT * FROM employees WHERE department_id = &dept_id;

-- バインド変数（: で参照）: PL/SQL と SQL で共有できる
VARIABLE v_count NUMBER
BEGIN
    SELECT COUNT(*) INTO :v_count FROM employees WHERE department_id = 60;
END;
/
PRINT v_count   -- バインド変数の値を表示する

-- DEFINE で確認・削除する
DEFINE              -- すべての定義済み変数を表示
DEFINE dept_id      -- dept_id の値を表示
UNDEFINE dept_id    -- 変数を削除

-- $ORACLE_HOME/sqlplus/admin/glogin.sql に記述する（全ユーザー共通）
-- またはカレントディレクトリの login.sql に記述する（プロジェクト固有の設定）

SET PAGESIZE 100
SET LINESIZE 200
SET FEEDBACK ON
SET SERVEROUTPUT ON SIZE 1000000
SET TIMING ON
SET TRIMSPOOL ON
SET NULL "(null)"

-- プロンプトに接続情報を表示する（SQL> の代わりに user@DB> と表示）
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER> "

-- COLUMN 設定: 列の表示幅を指定する
COLUMN last_name FORMAT A30          -- VARCHAR2 列を 30 文字幅で表示
COLUMN salary FORMAT 999,999.00      -- 数値を固定フォーマットで表示
COLUMN hire_date FORMAT A12          -- DATE 列の表示幅

-- 毎回使う COLUMN FORMAT は glogin.sql に書いておくと便利

-- WHENEVER SQLERROR: SQL エラーが発生したときの動作を指定する
WHENEVER SQLERROR EXIT FAILURE ROLLBACK
-- EXIT FAILURE: エラー時に終了コード 1 で終了する
-- ROLLBACK: 未コミットの変更をロールバックして終了する

WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
-- SQL.SQLCODE: Oracle のエラーコードを終了コードとして返す

WHENEVER SQLERROR CONTINUE
-- CONTINUE: エラーが起きても処理を続行する（デフォルト）

-- WHENEVER OSERROR: OS コマンドエラー（EXIT の失敗など）の処理
WHENEVER OSERROR EXIT FAILURE

-- バッチスクリプトでのエラーハンドリングの例（script.sql）
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
WHENEVER OSERROR EXIT FAILURE

-- メイン処理
INSERT INTO orders (order_id, customer_id, amount)
VALUES (1001, 200, 5000);
COMMIT;

EXIT 0   -- 正常終了