Oracle SQL*Plus使用方法

SQL*Plusとは？

OracleデータベースにおけるSQLを実行するためのクライアント用CUIツール
データベース接続/切断、SQLコマンドを実行可能

インストール

公式ダウンロードサイト
Windows(32bit/64bit)、Linux等の各実行環境に応じたファイルをダウンロードする

インスタントクライアント

ORACLEデータベースに接続するためのライブラリ
・ instantclient-Basic～
・ instantclient-Basiclite～
をダウンロードして同一フォルダへまとめる
接続以外の機能が無く、データベースを操作する為には別途SQL*Plus等が必要。
・ instantclient-sqlplus～
をダウンロードしてインスタントクライアントと同一フォルダへまとめる

SQL*Plusコマンド

一覧

コマンド	説明
SQLPLUS	SQLPLUS起動
CONNECT	接続
@	外部スクリプト
SHOW ERRORS	エラー表示
SHOW PARAMETER SPFILE	SPFileの表示
DESCRIBE (表名)	テーブル定義出力
PROMPT (文字列)	文字列ログ出力
STARTUP	インスタンスの起動～MOUNT、OPENまで全て実行
SHUTDOWN	「切断」参照

接続

ログイン対象サーバー	コマンド		次コマンド	意味
ローカル	sqlplus		ユーザー名？	起動
ローカル	sqlplus	as sysdba	ユーザー名？	起動(権限付き)
ローカル	sqlplus	/nolog	connect (ユーザー名)	起動＆ログイン無し
ローカル	sqlplus	ユーザー名	パスワード？	起動＆ユーザ指定ログイン
ローカル	sqlplus	ユーザー名/パスワード		起動＆ユーザ・パス指定ログイン
外部	sqlplus	/	ユーザ名？	起動＆外部サーバログイン情報入力へ
外部	sqlplus	/ as sysdba	ユーザ名？	起動(権限付き)＆外部サーバログイン情報入力へ
外部	sqlplus	ユーザー名/パスワード@ホスト:ポート番号/サービス名		起動＆ユーザ他指定、ログイン

powershell/コマンドプロンプトから
> sqlplus (ユーザ名)/(パスワード)
> sqlplus system/*******
> sqlplus (ユーザ名)/(パスワード)@(ホスト名)[:(ポート番号)]/(サービス名)
> sqlplus system/*******@host:100/service

結果 ↓
SQL>
sqlplus.exeから
ユーザー名を入力してください:system
パスワードを入力してください:*******
Oracle Database 11g ～ に接続されました。
SQL>

切断

SQL> SHUTDOWN;
SQL> SHUTDOWN オプション;

オプション	トランザクション	セッション	備考
NORMAL	完了まで待機	終了まで待機	規定
TRANSACTIONAL	完了まで待機	強制切断
IMMEDIATE	ロールバック	強制切断
ABORT	無視	強制切断	通常の終了ができない場合用 (メディア障害時等) インスタンスリカバリが実行される

「データベース起動の流れ」参照

OSコマンド実行

先頭に「HOST」または「!」を付ける
HOST ls
HOST cd ～
!ls
!cd ～

外部スクリプト

実行

・ 実行するSQL命令が大量にある場合
・ 実行するSQL命令が長い場合(Create文等)
等の場合にSQL文が記述されたテキストファイルをSQL*Plusから実行できる。
実行
SQL> @(ファイル名)
SQLPLUS実行・接続と同時にスクリプト実行
SQLPLUS (接続情報) @(ファイル名)

例
※test.sql
Create ～;

※SQL*Plus
SQL> @C:\～\test.sql
→ Create ～ が実行される

パラメータ

外部スクリプトに変数を渡す場合
> SQLPLUS (接続情報);
VAR 変数1 型;
VAR 変数2 型;
BEGIN
　: 変数1 := 値
　: 変数2 := 値
END;
SQL> @(ファイル名) 変数1 変数2 10

※(ファイル名)
SELECT * FROM (表)
WHERE
　(列1) = &1 AND　←変数1
　(列2) = ''&2'' AND　←変数2
　文字列として扱う場合は''(シングルコーテーション×2)で囲む
　(列3) = &3　←10
;

戻り値

【例外をキャッチして戻り値を設定】
VAR RETURN_CODE NUMBER;
BEGIN
　:RETURN_CODE = 0;
　～
EXCEPTION
　WHEN SQLERROR THEN
　　:RETURN_CODE = SQL.SQLCODE;
　WHEN OTHERS THEN
　　:RETURN_CODE = 9;
END;
/
EXIT :RETURN_CODE

【エラー発生時に自動で戻り値を返す場合】
WEHNEVER SQLERROR EXIT N
WEHNEVER SQLERROR EXIT SQL.SQLCODE
WEHNEVER OSERROR EXIT N
BEGIN
　～
END;
/

【戻り値の格納先】

種類		組込変数
Windowsバッチ		%errorlevel%
シェルスクリプト	sh系(bsh, ksh, bash)	$? 変数
	csh系(csh, tch)	$status 変数

変数定義

・置換変数
SQL*Plusによる置換機能
・バインド変数
SQL*Plusによるバインド機能。
PL/SQL内で定義する事はできないが、SQL*Plusで定義した変数をPL/SQLに対して使用する事ができる。
PL/SQL内で定義する変数と違い、PL/SQLブロックが終了した後も値が保持される。

置換変数

DEFINE 変数名=～;
SQL文： ～ = &変数名;
※変数名は大文字/小文字が区別されない。
&AAAと&aaaは同じ変数扱い
DEFINE value = 1;
SELECT ～ FROM ～ WHERE ～ = &value;
スクリプトの実行結果
SELECT ～ FROM ～ WHERE ～ = &value
↓
SELECT ～ FROM ～ WHERE ～ = 1
置換変数へ値が置換される

定義削除
UNDEFINE 変数名;
定義削除しない場合、SQL*Plusを終了するまで定義は有効

DEFINE無しで置換変数を使用した場合
SELECT ～ FROM ～ WHERE ～ = &value
↓
ユーザーにキー入力を求める

ユーザー入力値を引き続き使用する場合
入力時：&&置換変数名
SELECT ～ FROM ～ WHERE ～ = &&value; –①
SELECT ～ FROM ～ WHERE ～ = &&value; –②
を実行する場合
ユーザーキー入力
↓
入力された値で置換変数を置換＆SQL文①を実行
入力された値で置換変数を置換＆SQL文②を実行
&&～は以下の2つの機能を持つ
・DEFINEによる変数定義
・&～によるユーザー入力値の置換

バインド変数

SQL*Plus実行中、ホストコンピュータのメモリ領域に保持される変数
PL/SQL外で定義する変数。
PL/SQL外で使用する他、
PL/SQLへ値を渡したり、受け取ったりできる。
使用例
バインド変数定義
EXECUTE ストアド名(:バインド変数)
SELECT ～ FROM ～ WHERE ～ = :バインド変数

VARIABLE 変数名 型; --バインド変数定義
VAR 変数名 型;
変数定義はPL/SQL外のみ
BEGIN
　: 変数名 := 値
　値の設定はPL/SQL内のみ
END;
/
SELECT ～ WHERE ～ = : 変数名;
EXECUTE ストアド(: 変数名);
/

VAR TEST NUMBER;
:NUMBER = 5; ←エラー
BEGIN
　VAR TEST NUMBER; ←エラー
　:NUMBER = 5;
END

PRINT :バインド変数名;
バインド変数の中身を出力
PRINT;
バインド変数一覧を出力

ログ出力

SQL> spool sql.log
SQL> (SQL命令)
SQL> spool off
→ SQLPlusの実行ファイル場所にsql.logが作成され、
・ 実行日時
・ SQL文
・ エラー情報
が出力される。

制御文

PL/SQLでの制御文については「SQL DML：制御文」参照

実行結果設定(SET～)

一覧

SQL*Plusコマンド	機能	デフォルト
SET AUTOCOMMIT	DML文実行時に自動COMMIT	OFF
SET DEFINE OFF	置換変数切り替え	ON
SET VERIFY ON	置換前後の値を表示	OFF
SET ECHO ON	DML実行結果を表示	OFF
SET FEEDBACK ON	問い合わせ結果行数を表示	OFF
SET HEADING OFF	列見出しを表示	OFF
SET TIME ON	現在時刻を表示	OFF
SET TIMING ON	経過時刻を表示	OFF
SET ARRAYSIZE N SET ARRAY N	SELECT文における一度にFETCHする行数 推奨：500	15
SET LINESIZE N SET LINE N	表示画面における１行当たり文字列数 推奨：レコードの最大バイト数×1.5	80
SET TRIMSPOOL ON SET TRIMS ON	SPOOL処理の末尾の空白スペースをTRIM	OFF
ALTER SESSION SET nls_date_format = 'DD-MON-RR'	日付書式設定	'RR-MM-DD'
ALTER SESSION SET nls_date_language = 'AMERICAN'	日付言語設定	'JAPANESE'

置換変数切り替え

SET DEFINE ON
SET DEFINE OFF
定義した変数をクエリー内に埋め込む機能のON/OFF
デフォルトではONで、かつ「&」が置換命令
SET DEFINE ON ＝ SET DEFINE &
よってクエリー内に&があると変数の置換が実行され、キー入力を求められたりする。
SET DEFINE OFF;
で解決。
「Oracle SQL Developer使用方法 置換変数」参照