Oracle SQL*Plus使用方法

SQL*Plusとは?

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

インストール

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

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

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行当たり文字列数
推奨:レコードの最大バイト数×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使用方法 置換変数」参照