Edo::World::Blog

〜Linux/Solaris関連の作業メモ〜

Oracle::SQLPlus

[Oracle] SQLをシェルスクリプトから実行

[シェル作成時のチェック]

  • sqlplusのコネクションは何度も行わない
  • 日付/時間の挿入
  • SQL部分のコメントは"--"とする("#"だと実行されてしまう)
  • 整形関連
    • set head off : 列ヘッダー削除
    • set newpage none: 1行目の表示間隔を除去
    • set feed off : 問い合わせ結果の改行を除去
    • set trims on:ファイルに余白の空白文字を出力しない
    • set line 1000:1行の表示文字数(折り返しまでの制限文字数)を大きめにセット

■ヒアドキュメントを利用

[基本編]

ここからコピーした...

#!/bin/sh
table_name=dual
sql_select=select sysdate from $table_name;"

exec_query ()
{
    local _result_set=''
    local _query="${1}"

    _result_set="`sqlplus -s /nolog << EndOfSQL
        conn scott/tiger
        $_query
    exit
    EndOfSQL`"
}

exec_query $sql_select

[修正版]

エラーチェックや関数化...

#!/bin/sh
table_name='dual'
user='scott'
passwd='tiger'
sql_select="select sysdate from $table_name;"

_is_success ()
{
    # 引数存在チェック
    if [ ! " ${_query}" ]; then
        echo "sqlが指定されていません。(_is_success)"
        return 1
    fi

    # ORAが含まれるかをチェック
    echo "${_query}" | grep -q 'ORA' && return 1

    # 正常
    return 0

}

exec_query ()
{
    local _result_set=''
    local _query="${1}"
    local _set_format='set newpage none
                       set feed off
                       set line 1000
                       set trims on'

    # 引数存在チェック
    if [ ! " ${_query}" ]; then
        echo "sqlが指定されていません。(exec_query)"
        exit 1
    fi

    _result_set="$(sqlplus -s /nolog << EndOfSQL
        conn ${user}/${passwd}
        ${_set_format}
        ${_query}
    exit
    EndOfSQL)"

    if [ "${_query}" ]; then
        _is_success $_query || (echo "SQL失敗:${_result_set}"; exit 1)
    fi

    # 結果を出力
    echo "${_result_set}"
}

result=$(exec_query "${sql_select}")
echo "${result}"



■echoコマンドを利用

若干、読みやすくなった?


#!/bin/sh
table_name='dual'
user='scott'
passwd='tiger'
sql_select="select sysdate from $table_name;"

_is_success ()
{
    # 引数存在チェック
    if [ ! " ${_query}" ]; then
        echo "sqlが指定されていません。(_is_success)"
        return 1
    fi

    # ORAが含まれるかをチェック
    echo "${_query}" | grep -q 'ORA' && return 1

    # 正常
    return 0

}

exec_query ()
{
    local _result_set=''
    local _query="${1}"
    local _set_format='set newpage none
                       set feed off
                       set line 1000
                       set trims on'

    # 引数存在チェック
    if [ ! " ${_query}" ]; then
        echo "sqlが指定されていません。(exec_query)"
        exit 1
    fi

    (
    echo "conn ${user}/${passwd}"
    echo "${_set_format}"
    echo "${_query}"
    ) | sqlplus -s /nolog

    # TODO:
    # 「sqlplus -s /nolog >> ファイル名 」としてファイル内にORAが含まれるか
    #  エラーチェックする。 

    #if [ "${_query}" ]; then
    #    _is_success $_query || (echo "SQL失敗:${_result_set}"; exit 1)
    #fi

    # 結果を出力
    #echo "${_result_set}"
}

exec_query "${sql_select}"
#echo "${result}"

[Oracle] よく使うSQL、Tips

よく使うSQL


  • 現在時刻を表示
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
  • テーブル一覧表示
select table_name from user_tables;
select table_name from user_tables where table_name like '%hoge%';   ※部分一致
  • ビュー一覧表示
select view_name from user_views;
  • インデックス一覧表示
col index_name for a10
col table_name for a10
col column_name for a10
select * from user_ind_columns;
  • ビュー定義表示
select view_name,text from user_views where view_name = <ビュー名>;
  • データベース情報表示(sysdbaで実行)
select host_name, instance_name, database_status from v$instance;


Tips


  • SQL実行中にdescを実行
SQL> select ename,
  2  #desc emp    ※select文の実行中にdescを実行
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 EMPNO					   NOT NULL NUMBER(4)
 ENAME						    VARCHAR2(10)
 JOB						    VARCHAR2(9)
 MGR						    NUMBER(4)
 HIREDATE					    DATE
 SAL						    NUMBER(7,2)
 COMM						    NUMBER(7,2)
 DEPTNO 					    NUMBER(2)

  2  job from emp;   ※select文は続けて実行可能

ENAME	   JOB
---------- ---------
hoge	   musician
SMITH	   CLERK
ALLEN	   SALESMAN
WARD	   SALESMAN
 〜略〜
  • 直前に利用したSQL文を部分置換して実行
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
PLAN_TABLE
SALGRADE
BONUS
EMP
DEPT

SQL> c/user_tables/all_tables  ※"user_tables"を"all_tables"に置換
  1* select table_name from all_tables   ※置換された結果が表示される
SQL> /                               ※実行

TABLE_NAME
------------------------------
DUAL
SYSTEM_PRIVILEGE_MAP
TABLE_PRIVILEGE_MAP
STMT_AUDIT_OPTION_MAP
  〜略〜

[Oracle] SPOOLファイル追加書き

SQL*Plus10.1より有効

  • 構文
spool 出力ファイル名 { cre[ate] | rep[lace] | app[end] }
  • バージョン確認
$ sqlplus -v

SQL*Plus: Release 11.1.0.6.0 - Production

[Oracle] SQLPlusのプロファイル

[種類]

  • glogin.sql:マシン毎のプロファイル
  • login.sql:OS ユーザーのプロファイル

※glogin.sqlはマシン全体に影響があるので、set linesizeやset pagesize、set long等、SQLPLUS独自の設定だけにとどめておく

[読み込み順序と格納場所]

①glogin.sql(配置場所:$ORACLE_HOME/sqlplus/admin/glogin.sql)

②login.sql(配置場所:任意指定。カレントディレクトリ->SQLPATH変数の順で探索)

[login.sql]

こんな感じで作成

$ pwd
/home/oracle
$ cat login.sql 
set serveroutput on size 1000000
set trimspool on
set linesize 120
set pagesize 9999
set termout on

[Oracle] SQLPLUS

SQLPLUSの基本的な使用法まとめ

続きを読む
プロフィール

edworld

記事検索
アクセスカウンター
  • 今日:
  • 昨日:
  • 累計:

livedoor 天気
  • ライブドアブログ