sqlplusとは

sqlplusはoracleに対して処理を指示するための最も基本的なCUIのSQLクライアントツールです。実質oracle databaseがインストールされている環境にはほぼすべてsqlplusもインストールされているため以下のような様々な用途で使用されています。

・データベースの起動や停止
・対話型でのSQLやPL/SQLの実行
・リカバリ
・バッチ処理

データベースへの接続、切断

sqlplusはデータベースに接続して使用するツールなのではじめにデータベースに接続する必要があります。接続方法には以下のような方法があります。

・対話形式でユーザIDやパスワードを入力する
sqlplus

・起動と同時に接続する
sqlplus ＜ユーザID＞/＜パスワード＞@＜接続識別子＞

・sysdba接続(データベースの起動や停止が可能なSYSユーザで接続する)
sqlplus "＜ユーザID＞/＜パスワード＞@＜接続識別子＞ as sysdba"

※SYSDBA接続の認証方式がOS認証の場合はユーザIDとパスワードが無視されるため「sqlplus / as sysdba」で接続可能です

・接続と同時にファイルを実行する
sqlplus ＜ユーザID＞/＜パスワード＞@＜接続識別子＞ @＜ファイル名＞

・切断する
exit

SQLの実行

sqlplusでSQLを実行するにはSQL文の最後に「;(セミコロン)」を付けます。
また、「/(スラッシュ)」を実行することで前回実行したSQL文を再実行することもできます。

sqlplusの初期化スクリプト

sqlplusを起動した際、再接続した際(10g～)に$ORACLE_HOME/sqlplus/admin/glogin.sqlというファイルと、 sqlplusを起動したカレントディレクトリのlogin.sqlというファイルの内容が暗黙的に実行されます。(glogin.sqlには全体に適用する内容を設定し、login.sqlには各処理やユーザ固有の設定を行う)
これを利用してsqlplusのバージョンが10g以降であれば以下を追記することでプロンプトにログインユーザ及び接続識別子を表示するといった設定が可能です。

SET ECHO OFF
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > "
SET ECHO ON

接続時の表示例

SQL> conn test/test@orcl112
接続されました。
SYS@ORCL112 >

sqlplus固有のコマンド

以下、sqlplusでよく使われるコマンドです。

・データベースへの接続を行う
connect ＜接続先情報＞

・データベースを起動する(SYSDBAやSYSOPERでの接続が必要)
startup ＜オプション＞

・データベースを停止する(SYSDBAやSYSOPERでの接続が必要)
shutdown ＜オプション＞

・標準出力の内容をファイルにも出力する(spool off で無効化)
spool ＜ファイル名＞

・1行あたりの出力文字数を変更する
set line ＜行数＞

・クエリの列ヘッダが出力される間隔を変更する(0の場合はヘッダなし)
set pagesize ＜行数＞

・処理時間を出力する
set timing on

・現在の時間をプロンプトに表示する
set time on

・エスケープ文字を変更する
set escape ＜エスケープ文字＞

・列の区切り文字を変更する(「,」カンマを設定するとcsv形式で出力可能)
set colseq ＜区切り文字＞ ・表やパッケージの定義を出力する
describe ＜オブジェクト名＞

・列のフォーマットを変更する
- col1列(文字列型)を10文字まで出力する
column col1 format a10
- col2列(number型)を4ケタの整数まで0埋めして出力する(5ケタ以上のケタ数になった場合は「#」で表示される)
column col2 format 0000

・ファイルを実行する
@＜ファイル名＞
※「?」を指定するとORACLE_HOMEに置換される。
※「@@」を指定するとファイル内からさらにファイルが呼び出された際にカレントディレクトリが飛び出されたファイルのカレントパスに置き換わる

・単一のPL/SQLを実行する(複数行記述する場合は小文字ハイフン(-)を最後尾に付与する)
execute ＜PL/SQLパッケージ＞

・OSコマンドを実行する(unix系OSでは「!」でも代用可能)
host ＜OSコマンド＞

・リカバリを実行する(SYSDBAでの接続が必要)
recover　＜リカバリオプション＞

・現在の設定を表示する
show all

・データベースのパラメータを表示する
show parameter

・バインド変数の定義及び表示(以下の例は「b1」というバインド変数に「abc」という値をセットする場合)
variable b1 varchar2(10);
execute :b1 := 'abc';
print b1;

・置換変数を定義する
define a1=a
置換変数はコマンドを実行する場合に自動的に値を置換して実行される。

select '&a1.' from dual;
旧   1: select '&a1.' from dual
新   1: select 'a' from dual

'A
--
a

TIPS

・特殊記号の意味
&(アンパサンド)・・・置換変数を表します。また&1、&2等の数字は引数が自動的に設定されます。最後に.(ドット)を付与することで置換変数の終わりを明示的に設定することができます。&&とすることで一度標準入力から設定した置換変数の値が自動的にdefineされます。
-(ハイフン)・・・続き行があることを示します。
@(アットマーク)・・・@ファイル名でファイルを実行することができる。「.sql」の拡張子は省略することが可能。@@とすることでカレントディレクトリをスクリプトのパスがカレントディレクトリとして設定することができます。
?(クエスチョンマーク)・・・@後のファイル名に含めた場合ORACLE_HOMEのパスに置換されます。
・setコマンドについて
setコマンドは以下のように複数の設定を1行で記述することができます。
set time on timing on line 200 pagesize 1000
・long列の表示(dbms_metadataやv$sql.sql_fulltext等)
set long 1000000 等サイズ明示的に設定することでlong列のデータを指定したサイズまで出力することでできます。
・数値型データを全桁表示する
SET NUMWIDTH 20 等でデフォルトの桁数が変更できます。
・バッチ時のエラーハンドリング
以下の二つを宣言しておくことでエラーが発生した場合に自動的にスクリプトを中断することができます。「FAILURE」はリターンコード1を表しておりその他の数字を指定することも可能です。
WHENEVER SQLERROR exit FAILURE rollback
WHENEVER OSERROR exit FAILURE rollback
・リターンコードについて
unix系OSではリターンコードは0～255の範囲でしか定義できないためORACLEのエラーコードをそのまま戻り値で設定した場合エラーコードを256で除算したあまりの値が表示されます。
・PUP(PRODUCT_USER_PROFILE)表によるセキュリティ設定
sqlplus固有の設定ではないがSYSTEMユーザ所有のPRODUCT_USER_PROFILE表に対して設定を行うことでクライアントプログラムやユーザ単位で禁止コマンド等を設定することができます。
・コマンドの省略
ほとんどのコマンドは省略することができます。
connect=conn、set autotrace=set autot等。詳細は下記マニュアル参照。
・autotraceコマンドのSQL統計について
autotraceコマンドを実行するとシャドウセッションが作成され、シャドウセッションがSQL実行前後でそれぞれSQL統計を取得して差分を表示しています。そのため、autotraceコマンドで表示されるSQL統計にはハードパース時等の再帰SQLを実行された際の統計も含まれており実行SQL以外の統計が混じって表示される場合があります。

マニュアル

SQL*Plus クイック・リファレンス10g リリース2（10.2）
SQL*Plus ユーザーズ・ガイドおよびリファレンス10g リリース2（10.2）