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使用方法 置換変数」参照
