Oracle SQL*PLUS チートシート

cheatsheet
DB操作OracleDBSQL*PlusSQL実行コマンドライン

接続と切断 🔗

データベースへの接続、ユーザーの切り替え、切断を行います。

コマンド 説明 書式例
CONNECT / CONN 指定したユーザーでデータベースに接続します。 
CONNECT ユーザー名/パスワード@接続識別子
CONNECT ユーザー名/パスワード
CONN scott/tiger@orclpdb
OS認証: 
CONNECT / AS SYSDBA
DISCONNECT / DISC データベースから切断します。 
DISCONNECT
PASSWORD 現在のユーザーのパスワードを変更します。 
PASSWORD
旧パスワードを入力してください: ********
新規パスワードを入力してください: ********
新規パスワードを再入力してください: ********
SHOW USER 現在接続しているユーザー名を表示します。 
SHOW USER
EXIT / QUIT SQL*Plusを終了します。COMMITせずに終了すると未確定のトランザクションはロールバックされます（AUTOCOMMIT設定による）。 
EXIT
QUIT
コミットして終了: 
EXIT COMMIT
ロールバックして終了: 
EXIT ROLLBACK

SQLとSQL*Plusコマンドの実行 ⚡

SQL文やSQL*Plusコマンドを実行する方法です。

方法 説明 例
セミコロン (;) SQL文の終端にセミコロンを付けてEnterキーを押すと、SQL文が実行されます。 
SELECT table_name FROM user_tables;
スラッシュ (/) SQLバッファに格納されているSQL文またはPL/SQLブロックを実行します。 
SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = 'EMPLOYEES'
/
DECLARE
  v_emp_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_emp_count FROM employees;
  DBMS_OUTPUT.PUT_LINE('社員数: ' || v_emp_count);
END;
/
RUN / R SQLバッファの内容を表示してから実行します。 
SELECT status FROM v$instance
RUN
@ / START 指定したスクリプトファイルを実行します。 
@myscript.sql
START c:\scripts\setup.sql
@@ 現在実行中のスクリプトと同じディレクトリにある別のスクリプトファイルを実行します。ネストされたスクリプトで使用します。 
-- main.sql内
PROMPT メインスクリプト開始...
@@common_setup.sql
SELECT * FROM employees;
PROMPT メインスクリプト終了。

SQLバッファの編集 📝

直前に実行した、あるいはバッファにあるSQL文を編集します。

コマンド 説明 書式例
LIST / L SQLバッファの内容（カレント行に*が付く）を表示します。 
LIST
L
L 3  -- 3行目をカレント行にする
L 2 5 -- 2行目から5行目を表示
APPEND <text> / A <text> カレント行の末尾にテキストを追加します。 
L 2 -- 2行目をカレント行に
  2* WHERE department_id = 10
A AND job_id = 'CLERK'
L 2
  2* WHERE department_id = 10 AND job_id = 'CLERK'
CHANGE /<old>/<new>/
C /<old>/<new>/		 カレント行の最初に見つかった<old>を<new>に置換します。 
L 1
  1* SELECT emp_name, salary FROM emp
C /emp/employees/
L 1
  1* SELECT emp_name, salary FROM employees
INPUT / I カレント行の後ろに新しい行を挿入します。 
L
  1  SELECT employee_id, last_name
  2* FROM employees
I ORDER BY last_name;
L
  1  SELECT employee_id, last_name
  2  FROM employees
  3* ORDER BY last_name;
テキストを指定して挿入: 
I WHERE hire_date > SYSDATE - 365
DEL カレント行を削除します。範囲指定も可能です。 
L 3
  3* ORDER BY hire_date DESC
DEL
L
  1  SELECT employee_id, last_name
  2* FROM employees
-- 範囲削除
DEL 1 2
EDIT / ED OSのエディタ（環境変数 `_EDITOR` で定義）を起動し、SQLバッファの内容を編集します。 
EDIT
ファイル名を指定して編集: 
EDIT myquery.sql

コマンドファイルの操作 💾

SQLバッファの内容をファイルに保存したり、ファイルから読み込んだりします。

コマンド 説明 書式例
SAVE <filename> [CREATE|REPLACE|APPEND] SQLバッファの内容を指定したファイルに保存します。拡張子はデフォルトで `.sql` になります。 
SAVE my_query
SAVE c:\temp\emp_list REPLACE
SAVE setup_script APPEND
GET <filename> [LIST|NOLIST] 指定したファイルの内容をSQLバッファに読み込みます。LIST (デフォルト) で読み込んだ内容を表示します。 
GET my_query
GET c:\temp\dept_query.sql NOLIST
START <filename> [引数...]
@ <filename> [引数...]		 指定したスクリプトファイルを実行します。引数を渡すこともできます（スクリプト内では &1, &2… で参照）。 
START get_emp.sql 100 'SALES'
@'c:\my scripts\report.sql' 'Q1'
SPOOL <filename> [CREATE|REPLACE|APPEND]
SPOOL OFF
SPOOL OUT		 SQL*Plusの出力を指定したファイルに書き込みます (スプール)。SPOOL OFF で書き込みを終了します。SPOOL OUT はプリンタへ出力します（環境依存）。 
SPOOL query_result.log REPLACE
SELECT * FROM departments;
SPOOL OFF

出力の書式設定 📊

COLUMN コマンドと SET コマンドを使用して、クエリ結果の表示形式を調整します。

COLUMN コマンド

特定の列の表示属性を設定します。

書式オプション 説明 例
COLUMN <column_name> FORMAT <format_model> 列の表示形式を指定します。数値 (9, 0, $, ., ,), 文字列 (A<n>) など。 
COLUMN salary FORMAT $999,999.00
COLUMN last_name FORMAT A20 -- 文字列を20桁で表示
COLUMN hire_date FORMAT A10 HEADING 'Hire Date'
COLUMN <column_name> HEADING '<heading_text>' 列ヘッダーのテキストを指定します。改行は | を使用。 
COLUMN employee_id HEADING 'Emp|ID'
COLUMN department_name HEADING 'Department Name'
COLUMN <column_name> JUSTIFY [LEFT|CENTER|RIGHT] 列ヘッダーとデータの表示位置揃えを指定します。デフォルトは数値:右、文字列:左。 
COLUMN job_id JUSTIFY CENTER
COLUMN <column_name> WRAPPED / WORD_WRAPPED / TRUNCATED 列幅を超えるデータの表示方法を指定します。WRAPPED:文字単位で折り返し, WORD_WRAPPED:単語単位で折り返し, TRUNCATED:切り捨て。 
COLUMN notes FORMAT A30 WORD_WRAPPED
COLUMN description FORMAT A40 TRUNCATED
COLUMN <column_name> NULL '<text>' NULL値の場合に表示する文字列を指定します。 
COLUMN commission_pct NULL 'N/A'
COLUMN <column_name> NOPRINT / PRINT 列を表示しない (NOPRINT) / 表示する (PRINT) か指定します。 
COLUMN internal_id NOPRINT
COLUMN <column_name> CLEAR 指定した列の書式設定をすべてクリアします。 
COLUMN salary CLEAR
CLEAR COLUMNS すべての列の書式設定をクリアします。 
CLEAR COLUMNS

SET コマンド (書式関連)

SQL*Plus環境全体の表示設定を変更します。

コマンド 説明 デフォルト値 設定例
SET LINESIZE <n> 1行に表示する最大文字数を設定します。 80 (環境依存) 
SET LINESIZE 150
SET PAGESIZE <n> 1ページあたりの行数を設定します。0にするとヘッダーが繰り返し表示されなくなります（ファイル出力時などに便利）。 14 (環境依存) 
SET PAGESIZE 50
SET PAGESIZE 0 -- ヘッダー非表示
SET HEADING ON|OFF 列ヘッダーの表示/非表示を切り替えます。 ON 
SET HEADING OFF
SET FEEDBACK ON|OFF|<n> “rows selected”のようなフィードバックメッセージの表示/非表示、または指定行数以上の場合のみ表示するかを設定します。 6 (環境依存) 
SET FEEDBACK OFF
SET FEEDBACK 1 -- 1行でも表示
SET FEEDBACK ON -- デフォルトに戻す
SET NULL <text> NULL値を表すデフォルトの文字列（通常は空白）を変更します。COLUMN <col> NULL が優先されます。 “” (空文字列) 
SET NULL '(null)'
SET NUMFORMAT <format_model> 数値列のデフォルト表示書式を設定します。COLUMN <col> FORMAT が優先されます。 “” (DB設定依存) 
SET NUMFORMAT 9,999,990.00
SET WRAP ON|OFF LINESIZE を超える行を折り返すかどうか。COLUMN の設定が優先されることがあります。 ON 
SET WRAP OFF
SET COLSEP '<text>' 列間の区切り文字を指定します。CSV出力などで利用できます。 ” ” (スペース1つ) 
SET COLSEP ','
SET UNDERLINE ON|OFF|<char> 列ヘッダーの下線の表示/非表示、または下線に使用する文字を設定します。 ON, ‘-‘ 
SET UNDERLINE OFF
SET UNDERLINE '='

変数の使用 💡

置換変数や定義変数を使用して、スクリプトを動的にします。

置換変数 (& / &&)

&変数名 または &&変数名 の形式でSQL文やSQL*Plusコマンド内に記述します。実行時に値の入力を求められます。&& は同じセッション内で再度同じ変数が現れた際に再入力を求めません（DEFINEされた状態になる）。

SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id = &dept_id
ORDER BY &order_col;

-- 実行時
dept_idに値を入力してください: 60
旧1: WHERE department_id = &dept_id
新1: WHERE department_id = 60
order_colに値を入力してください: last_name
旧2: ORDER BY &order_col
新2: ORDER BY last_name

-- && を使用
SELECT * FROM jobs WHERE job_id = '&&job_title';
-- job_title に 'IT_PROG' と入力した場合、以降の &job_title や &&job_title は 'IT_PROG' で置換される
SELECT * FROM employees WHERE job_id = '&job_title';

注意: 文字列リテラル内で使用する場合は、値をシングルクォートで囲む必要があります。

SELECT * FROM employees WHERE last_name = '&lname';

DEFINE / UNDEFINE

DEFINE コマンドでユーザー変数（置換変数）を明示的に定義、または確認します。UNDEFINE で削除します。

コマンド 説明 書式例
DEFINE 定義済みの変数を一覧表示します。 
DEFINE
DEFINE <variable> 指定した変数の値を確認します。 
DEFINE dept_id
DEFINE <variable> = <value> 変数を定義し、値を割り当てます。値は文字列として扱われます。 
DEFINE mytable = employees
DEFINE cutoff_date = '01-JAN-2020'
DEFINE status = 'ACTIVE'
UNDEFINE <variable> 指定した変数を削除します。 
UNDEFINE mytable
 
DEFINE target_table = user_tables
SELECT table_name, num_rows FROM &target_table WHERE table_name LIKE 'EMP%';
UNDEFINE target_table

ACCEPT

ユーザーからの入力を受け付け、定義変数に格納します。

コマンド 説明 書式例
ACCEPT <variable> [NUMBER|CHAR|DATE] [FORMAT <fmt>] [DEFAULT <def>] [PROMPT '<text>'] [HIDE] ユーザー入力を変数に格納します。データ型、書式、デフォルト値、プロンプトメッセージ、入力非表示（パスワード用）を指定できます。 
ACCEPT dept_num NUMBER PROMPT '部署番号を入力してください: '
ACCEPT hire_date_str CHAR FORMAT 'YYYY-MM-DD' PROMPT '入社日 (YYYY-MM-DD): ' DEFAULT '2023-01-01'
ACCEPT user_password CHAR PROMPT 'パスワードを入力: ' HIDE

-- 使用例
SELECT * FROM employees WHERE department_id = &dept_num AND hire_date > TO_DATE('&hire_date_str', 'YYYY-MM-DD');

SET VERIFY / SET DEFINE

置換変数の動作を制御します。

コマンド 説明 デフォルト値 設定例
SET VERIFY ON|OFF 置換変数が値に置き換わる前後の行を表示するかどうかを設定します。 ON 
SET VERIFY OFF
SET DEFINE ON|OFF|<char> 置換変数の機能を有効/無効にするか、置換変数の接頭辞文字を変更します。& が含まれるテキストをそのまま扱いたい場合に OFF にします。 ON, ‘&’ 
SET DEFINE OFF -- & を通常の文字として扱う
SET DEFINE '#' -- 接頭辞を # に変更 (#varname の形式になる)
SET DEFINE ON  -- デフォルトに戻す

PL/SQLの実行とデバッグ支援 ⚙️

PL/SQLブロックの実行や、デバッグに役立つ機能です。

コマンド/設定 説明 使用例
DECLARE ... BEGIN ... END; / 無名PL/SQLブロックを記述し、/ で実行します。 
DECLARE
  v_message VARCHAR2(100) := 'Hello, Oracle!';
BEGIN
  DBMS_OUTPUT.PUT_LINE(v_message);
END;
/
SET SERVEROUTPUT ON|OFF [SIZE <n>] [FORMAT [WRAPPED|WORD_WRAPPED|TRUNCATED]] DBMS_OUTPUT.PUT_LINE による出力を表示するかどうかを設定します。バッファサイズや折り返し形式も指定できます。 
SET SERVEROUTPUT ON SIZE 1000000 FORMAT WORD_WRAPPED
SET SERVEROUTPUT ON
EXECUTE <procedure_name>(...)
EXEC <procedure_name>(...)		 ストアドプロシージャを実行するための短縮形です。内部的には無名PL/SQLブロックに変換されます。 
EXECUTE my_proc(10, 'Test');
EXEC dbms_stats.gather_table_stats('SCOTT', 'EMP');
VARIABLE / VAR バインド変数を宣言します。PL/SQLブロックの内外で値を共有するのに使います。 
VARIABLE g_ret_val NUMBER
VARIABLE g_msg VARCHAR2(100)

BEGIN
  :g_ret_val := some_function(:input_param);
  :g_msg := '処理完了';
END;
/

PRINT g_ret_val g_msg
REFCURSORの宣言: 
VARIABLE rc REFCURSOR
PRINT <variable> VARIABLE コマンドで宣言したバインド変数の現在値を表示します。特にREFCURSORの結果表示に便利です。 
PRINT g_ret_val
PRINT rc
SHOW ERRORS [FUNCTION|PROCEDURE|PACKAGE|PACKAGE BODY|TRIGGER|VIEW|TYPE|TYPE BODY] [<schema>.]<name> 直前のコンパイルエラー、または指定したオブジェクトのコンパイルエラーを表示します。 
CREATE OR REPLACE PROCEDURE my_proc AS BEGIN SELECT * FROM non_existent_table; END;
/
-- Warning: Procedure created with compilation errors.

SHOW ERRORS

-- 特定のオブジェクトのエラーを表示
SHOW ERRORS PROCEDURE scott.process_emp

環境設定 (その他のSETコマンド) 🛠️

SQL*Plusの動作を制御する様々な SET コマンドがあります。

コマンド 説明 デフォルト値 設定例
SET AUTOCOMMIT ON|OFF|IMMEDIATE|<n> DML文の自動コミットを設定します。ON/IMMEDIATEは各SQL実行後、<n>はn個のDML実行後にコミット。通常はOFF推奨。 OFF 
SET AUTOCOMMIT ON
SET AUTOCOMMIT 10 -- 10 DMLごとにコミット
SET TIMING ON|OFF 各SQL文の実行時間を表示します。パフォーマンス測定に役立ちます。 OFF 
SET TIMING ON
SET ECHO ON|OFF スクリプトファイル内のコマンドを画面に表示するかどうかを設定します。デバッグ時にONにします。 OFF 
SET ECHO ON
SET TERMOUT ON|OFF スクリプトの実行結果を画面に表示するかどうか。スプールファイルのみに出力したい場合にOFFにします。 ON 
SET TERMOUT OFF
SPOOL report.txt
-- ... SQL queries ...
SPOOL OFF
SET TERMOUT ON
SET SCAN ON|OFF 置換変数のスキャンを有効/無効にします。SET DEFINE と似ていますが、DEFINE は接頭辞文字の変更も可能です。 ON 
SET SCAN OFF
SET SQLBLANKLINES ON|OFF SQL文の中に空行を含めることを許可するかどうか。デフォルト(OFF)では空行があるとコマンドが終了したとみなされることがあります。 OFF 
SET SQLBLANKLINES ON
SET SQLPROMPT "<prompt_text>" SQL*Plusのプロンプト文字列を変更します。_USER, _CONNECT_IDENTIFIER などの置換も利用可能です。 “SQL> “ 
SET SQLPROMPT "_USER@_CONNECT_IDENTIFIER> "
SET EDITFILE <filename> EDITコマンドで使用されるデフォルトの作業ファイル名を指定します。デフォルトは afiedt.buf。 afiedt.buf 
SET EDITFILE "myedit.sql"
SHOW <option> / SHOW ALL 特定のSETコマンドの設定値、またはすべての設定値を表示します。 N/A 
SHOW LINESIZE
SHOW FEEDBACK
SHOW ALL

オブジェクト情報の表示 🔍

DESCRIBE コマンドでテーブルやビューなどのオブジェクト構造を表示します。

コマンド 説明 書式例
DESCRIBE <object_name>
DESC <object_name>		 指定したテーブル、ビュー、シノニム、ファンクション、プロシージャなどの定義を表示します。 
DESCRIBE employees
DESC user_tables
DESC sys.dba_data_files
DESC my_package.my_function

エラー処理 ⚠️

スクリプト実行中のSQLエラーやOSエラー発生時の動作を定義します。

コマンド 説明 書式例
WHENEVER SQLERROR EXIT [SUCCESS|FAILURE|WARNING|<n>|SQL.SQLCODE|SQL.USERCODE] [COMMIT|ROLLBACK]
WHENEVER SQLERROR CONTINUE [COMMIT|ROLLBACK|NONE]		 SQLエラーが発生した場合の動作を指定します。EXITでSQL*Plusを終了、CONTINUEで続行します。終了コードや、終了前のCOMMIT/ROLLBACKを指定できます。 
-- エラー発生時にロールバックして失敗コードで終了
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

-- エラーが発生しても続行（デフォルト）
WHENEVER SQLERROR CONTINUE NONE

-- エラー発生時にコミットして終了コード 5 で終了
WHENEVER SQLERROR EXIT 5 COMMIT
WHENEVER OSERROR EXIT [SUCCESS|FAILURE|WARNING|<n>] [COMMIT|ROLLBACK]
WHENEVER OSERROR CONTINUE [COMMIT|ROLLBACK|NONE]		 OSエラー（ファイル書き込み失敗など）が発生した場合の動作を指定します。WHENEVER SQLERROR と同様のオプションがあります。 
-- OSエラー発生時にロールバックして失敗コードで終了
WHENEVER OSERROR EXIT FAILURE ROLLBACK

-- OSエラーが発生しても続行（デフォルト）
WHENEVER OSERROR CONTINUE NONE

注意: これらの設定はスクリプト内でのみ有効で、インタラクティブモードには影響しません。

その他の便利なコマンド ✨

コマンド 説明 書式例
PROMPT [<message>] 画面にメッセージを表示します。スクリプトの進行状況を示すのに便利です。 
PROMPT テーブル作成開始...
CREATE TABLE ... ;
PROMPT テーブル作成完了。
PROMPT -- 空行を出力
PAUSE [<message>] メッセージを表示し、ユーザーがEnterキーを押すまでスクリプトの実行を一時停止します。 
PROMPT 続行するにはEnterキーを押してください...
PAUSE
PAUSE スキーマを削除します。よろしいですか？
REMARK / REM コメント行を示します。スクリプト内に注釈を記述します。 
REM これはコメントです
REMARK スクリプトの目的: ...

SQL標準のコメント (-- や /* ... */) も使用可能です。
HOST [<os_command>] / ! [<os_command>] SQL*Plusから一時的に抜けてOSコマンドを実行します。コマンドを指定しない場合はOSのシェルを起動します。 
HOST dir c:\temp -- Windows
HOST ls -l /home/oracle -- Linux/Unix
! rm temp_file.log -- Linux/Unix
HOST -- OSシェル起動 (終了は exit)
CLEAR BUFFER / CLEAR SQL SQLバッファの内容をクリアします。 
CLEAR BUFFER
CLEAR SCREEN SQL*Plusの画面をクリアします。 
CLEAR SCREEN
TTITLE / BTITLE 各ページの先頭 (Top Title) または末尾 (Bottom Title) に表示するタイトルを設定します。 
TTITLE CENTER '社員レポート' SKIP 2 LEFT '日付: ' _DATE RIGHT 'ページ: ' FORMAT 999 SQL.PNO
BTITLE LEFT '機密情報'
TTITLE OFF -- タイトル表示をオフにする
BTITLE OFF
BREAK ON <column> [SKIP <n>|SKIP PAGE] [NODUPLICATES|DUPLICATES] 指定した列の値が変わるタイミングで処理（改行、改ページ、重複値非表示など）を行います。レポート作成時に使用します。 
-- 部門ごとに改ページし、部門名は一度だけ表示
BREAK ON department_id SKIP PAGE NODUPLICATES
-- ジョブIDが変わるたびに1行空ける
BREAK ON job_id SKIP 1

SELECT department_id, job_id, last_name
FROM employees
ORDER BY department_id, job_id;

CLEAR BREAKS -- 設定解除
COMPUTE <function> OF <column> ON <break_column> BREAK ON と組み合わせて、ブレーク列ごとに集計（合計、平均、件数など）を行います。 
BREAK ON department_id SKIP 1 ON report
COMPUTE SUM LABEL '部門合計' OF salary ON department_id
COMPUTE SUM LABEL '総合計' OF salary ON report

SELECT department_id, last_name, salary
FROM employees
ORDER BY department_id;

CLEAR COMPUTES -- 設定解除

コメント

タイトルとURLをコピーしました