トップ 履歴 一覧 カテゴリ ソース 検索 ヘルプ RSS ログイン

Oracle/SqlTips

INDEX

Oracle SQL文の小技メモ Oracle固有の関数等を使用しています。 固有関数は、Oracle/SqlFuncを参照。

日付

 日付書式の変更

SQL*Plus等で表示されるデフォルトの日付書式を変更するには、 次のSQLで日付書式を指定する。ただし、そのセッションでのみ有効

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';

 時間隔(期間リテラル)

DATE型を加減算する場合に、1日(24時間)を1として加減算をすることで計算できる。例えば、"5:00"に1時間を加算する場合、1/24を加算することで、"6:00"とすることが出来る。

また、interval 句を利用することで、定数の加減算を行うことも出来る。

interval '2:20' hour to minute

ちなみに、変数(項目の値)で時間隔を扱う場合は、変換関数(NUMTODSINTERVAL, NUMTOYMINTERVAL)を利用する。利用方法は下記参照のこと。

数値

 数値判定 ( IsNumeric関数もどき )

( REPLACE( TRANSLATE( 項目, '123456789', '000000000'), '0', '') IS NULL )

※'項目'がALL-SPACEの場合、NULLとするならば、TRIMを行う

 拡張版数値変換関数

Oracleの数値変換関数(TO_NUMBER)の機能拡張版です。通常、TO_NUMBERに非数値の場合エラーとなります(確か)。数値判定処理を追加して非数値の場合に指定した値(未指定は、NULL)を返すように機能拡張をしました。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
/* **************************************************************** */
/* * to_number2 : Numeric transform function with numeric check   * */
/* *                                                              * */
/* *                                     FileName  to_number2.sql * */
/* *                                     Version   0.00           * */
/* *                                     MakeDate  2005/02/01     * */
/* *                                     Creator   T.Imamura      * */
/* **************************************************************** */
CREATE OR REPLACE FUNCTION TO_NUMBER2 ( 
    InputString  IN VARCHAR2         , /* 変換元文字列 */
    DefaultValue IN NUMBER   := NULL , /* 非数値時の値 */
    FromatString IN VARCHAR2 := NULL   /* 数値書式 */
    ) RETURN NUMBER
    AUTHID CURRENT_USER DETERMINISTIC PARALLEL_ENABLE
IS
BEGIN
    RETURN(
        CASE WHEN REPLACE( TRANSLATE(  InputString, '123456789', 
                '000000000'), '0', '') IS NULL 
        THEN CASE WHEN FromatString IS NULL 
            THEN TO_NUMBER(InputString) 
            ELSE TO_NUMBER(InputString,  FromatString) END
        ELSE DefaultValue END
    );
END;

/* **************************************************************** */
/* *    End Of File  ( to_number2.sql )                           * */
/* **************************************************************** */

文字

 半角カナ変換関数

Oracle の標準関数にはない、半角カナから全角カナに変換する関数です。半濁点、濁点をREPLACE関数で地道に変換し、残りの半角カナをTRANSLATE関数で変換します。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
/* **************************************************************** */
/* * to_zenkana :                                                 * */
/* *                                                              * */
/* *                                     FileName  to_zenkana.sql * */
/* *                                     Version   0.00           * */
/* *                                     MakeDate  2007/08/08     * */
/* *                                     Creator   T.Imamura      * */
/* **************************************************************** */
CREATE OR REPLACE FUNCTION TO_ZENKANA ( 
    InputString  IN VARCHAR2 /* 変換元文字列 */
    ) RETURN VARCHAR2
    AUTHID CURRENT_USER DETERMINISTIC PARALLEL_ENABLE
IS
BEGIN
    RETURN(
        TRANSLATE( REPLACE( REPLACE( REPLACE( 
        REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( 
        REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( 
        REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( 
        REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( 
        REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( InputString,
        'ﾊﾟ', 'パ'), 'ﾋﾟ', 'ピ'), 'ﾌﾟ', 'プ'), 'ﾍﾟ', 'ペ'), 'ﾎﾟ', 'ポ'),
        'ｶﾞ', 'ガ'), 'ｷﾞ', 'ギ'), 'ｸﾞ', 'グ'), 'ｹﾞ', 'ゲ'), 'ｺﾞ', 'ゴ'),
        'ｻﾞ', 'ザ'), 'ｼﾞ', 'ジ'), 'ｽﾞ', 'ズ'), 'ｾﾞ', 'ゼ'), 'ｿﾞ', 'ゾ'),
        'ﾀﾞ', 'ダ'), 'ﾁﾞ', 'ヂ'), 'ﾂﾞ', 'ヅ'), 'ﾃﾞ', 'デ'), 'ﾄﾞ', 'ド'),
        'ﾊﾞ', 'バ'), 'ﾋﾞ', 'ビ'), 'ﾌﾞ', 'ブ'), 'ﾍﾞ', 'ベ'), 'ﾎﾞ', 'ボ'),
        'ｳﾞ', 'ヴ'), 'ﾞ' , '゛'), 'ﾟ' , '゜'),
        'ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾗﾘﾙﾚﾛﾜｦﾝｧｨｩｪｫｯｬｭｮｰ',
        'アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホ' ||
        'マミムメモヤユヨラリルレロワヲンァィゥェォッャュョー'));
END;

/* **************************************************************** */
/* *    End Of File  ( to_zenkana.sql )                           * */
/* **************************************************************** */

 文字列変換関数 (10g R2 以降)

Oracle 10g R2 から UTL_I18N パッケージに文字列変換の関数が用意された。

SELECT UTL_I18N.TRANSLITERATE(半角カナ文字, 'hwkatakana_fwkatakana') FROM DUAL ;

第一引数に変換するデータ。第二引数に変換名文字列を指定します。指定できる変換名文字列は以下の通り。
値 説明
'kana_fwkatakana' すべてのタイプの仮名文字を全角カタカナに変換します。
'kana_hwkatakana' すべてのタイプの仮名文字を半角カタカナに変換します。
'kana_hiragana' すべてのタイプの仮名文字を全角ひらがなに変換します。
'fwkatakana_hwkatakana' 全角カタカナのみを半角カタカナに変換します。
'fwkatakana_hiragana' 全角カタカナのみを全角ひらがなに変換します。
'hwkatakana_fwkatakana' 半角カタカナのみを全角カタカナに変換します。
'hwkatakana_hiragana' 半角カタカナのみを全角ひらがなに変換します。
'hiragana_fwkatakana' 全角ひらがなのみを全角カタカナに変換します。
'hiragana_hwkatakana' 全角ひらがなのみを半角カタカナに変換します。

データ出力

※Oracleの文字連結機能を利用してるため連結後の文字列長が4000字を超えると処理できません。

・CSV形式出力CSV(Comma Separated Value)形式でテーブルデータを返します。

SELECT
    KEY1 || ',' ||
    NVL(VAR1,' ') || ',' ||
    NVL(VAR1,' ')
FROM tbTABLE;

・TSV形式出力TSV(Tab Separated Value)形式でテーブルデータを返します。

SELECT
    KEY1 || CHR(9) ||
    NVL(VAR1,' ') || CHR(9) ||
    NVL(VAR1,' ')
FROM tbTABLE;

・固定長出力固定長テキスト形式でテーブルデータを返します。

SELECT
    KEY1 ||
    RPAD(NVL(VAR1,' '),10,' ') ||
    RPAD(NVL(VAR1,' '),10,' ')
FROM tbTABLE;

ロック取得/待ちトランザクションの一括表示

オラクルでロックしているトランザクションと待たされているトランザクションを表示する。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
 
/* **************************************************************** */
/* * ロックを獲得しているトランザクションと待っているトランザクションの一括表示 * */
/* * OTN Code Tips # 782  */
/* * http://otn.oracle.co.jp/otn_pl/otn_tool/code_detail?n_code_id=782 * */
/* **************************************************************** */
( /** ロックしている人たち **/
    SELECT  /*+ ORDERED ORDERED_PREDICATES */ A.ID2
    ,       ' B ' AS "B/R"  -- ブロックか待ちか
                            --   "B" : ブロックしているトランザクション
                            --   "R" : 待っているトランザクション
    ,       B.USERNAME      -- ユーザ名
    ,       B.SID || ',' || B.SERIAL# AS USER_PROCESS   -- セッションID
    ,       B.COMMAND       -- 実行中のコマンド
                            --   2:INSERT 3:SELECT 6:UPDATE 7:DELETE
    ,       B.LOGON_TIME    -- 
    ,       B.OSUSER        -- OSユーザ
    ,       B.MACHINE       -- マシン名
    ,       B.TERMINAL      -- 端末名
    ,       B.PROGRAM       -- プログラム名
    ,       A.TYPE          -- ロックのタイプ
    ,       A.CTIME         -- ロックを保持/要求している時間
    ,       D.OWNER         -- ロックを保持/要求しているオブジェクトのオーナ
    ,       D.OBJECT_NAME   -- ロックを保持/要求しているオブジェクトの名前
    FROM    V$LOCK A
    ,       V$SESSION B
    ,       V$LOCKED_OBJECT C
    ,       ALL_OBJECTS D
    WHERE   A.BLOCK > 0
    AND     A.SID = B.SID
    AND     A.ID2 = C.XIDSQN
    AND     C.OBJECT_ID = D.OBJECT_ID
) UNION ALL ( /** 待っている人たち **/
    SELECT  /*+ ORDERED ORDERED_PREDICATES */ A.ID2
    ,       ' R ' AS "B/R"
    ,       B.USERNAME
    ,       B.SID || ',' || B.SERIAL# AS USER_PROCESS
    ,       B.COMMAND
    ,       B.LOGON_TIME
    ,       B.OSUSER
    ,       B.MACHINE
    ,       B.TERMINAL
    ,       B.PROGRAM
    ,       A.TYPE
    ,       A.CTIME
    ,       D.OWNER
    ,       D.OBJECT_NAME
    FROM    V$LOCK A
    ,       V$SESSION B
    ,       V$LOCKED_OBJECT C
    ,       ALL_OBJECTS D
    WHERE   B.LOCKWAIT IS NOT NULL
      AND   B.LOCKWAIT = A.KADDR
      AND   B.SID = C.SESSION_ID
      AND   C.XIDSQN = 0
      AND   C.OBJECT_ID = D.OBJECT_ID
)

/********************************************************************/
/* *    End Of File  ( LOCK.SQL )                                 * */
/********************************************************************/

DML

 SELECTした結果をテーブルにする

CREATE文に、SELECT文を併用することで、抽出結果でテーブルを作成できる。

CREATE TABLE 作るテーブル名 AS SELECT 〜 FROM 〜;

ただし、プライマリーキー等の制約は設定されないので、必要なら後で作成するかCREATE文の中に追加する必要がある

また、件数が多い表の場合、UNRECOVERABLE を指定して実行すると、トランザクションはノーアーカイブモードで実行されロールバックセグメント拡張エラーも発生しない。

 表のプライマリーキーを削除する

通常のINDEXは"DROP INDEX index_name"で削除できるが、プライマリーキーはこの方法では削除できない。削除する場合は、"ALTER TABLE"で表を変更で削除する。

ALTER TABLE テーブル名 DROP PRIMARY KEY;

 Oracle Recyclebin

Oracle10g から DROP した表を戻せるようになった。DROP TABLE の際に、実際に表を消すのではなくRecyclebin領域に入れる(Windows のごみ箱みたいな機能)。よって、テーブル一覧に「BIN$+/yo2Si2RcKPDYe9x3+yCg==$0」のような表が現れる。直接内容を参照することも可能。

ごみ箱を空にするには、PURGE コマンドを利用します。ゴミ箱全体を掃除する場合 「PURGE RECYCLEBIN」特定の表や索引を掃除する場合 「PURGE TABLE (または INDEX) ゴミ箱内の名称」データベース全体のゴミ箱を掃除する場合 「PURGE DBA_RECYCLEBIN」※DBA権限が必要

いきなり削除する場合は、DROP文にキーワード「PURGE」をつけます。

Recyclebinの機能自体要らない場合は止められるらしいが、隠しパラメータ(サポート外)らしい。。。

※参考・おら！オラ！Oracle -どっぷり検証生活- Oracle10g フラッシュバック アゲインの巻 その７http://www.insight-tec.com/mailmagazine/ora3/vol240.html

ANALYZE - 統計情報の収集

 ANALYZE コマンドを利用する

・オブジェクトを走査し、正確な統計値 (サンプリング=100%) を取得する

ANALYZE オブジェクト名 COMPUTE STATISTICS;

・指定したパーセンテージのサンプリングを行い、統計値を推測する

ANALYZE オブジェクト名 ESTIMATE STATISTICS SAMPLE ##%;

・統計値を削除する

ANALYZE オブジェクト名 DELETE STATISTICS;

 DBMS_UTILITY / DBMS_STATS パッケージを利用する

EXECUTE DBMS_UTILITY. ANALYZE_SCHEMA(
  'スキーマ名', '{COMPUTE | ESTIMATE} STATISTICS');

EXECUTE DBMS_STATS. GATHER_SCHEMA_STATS (
  'スキーマ名', サンプリングパーセンテージ);

・スキーマ、テーブル指定、コンプリート

dbms_stats.gather_table_stats(
ownname=> 'スキーマ',
tabname=> 'テーブル',
granularity=> 'DEFAULT',
block_sample=> FALSE,
cascade=> TRUE,
degree=> DBMS_STATS.DEFAULT_DEGREE,
method_opt=> 'FOR ALL COLUMNS SIZE AUTO');

・スキーマ指定、サンプリング25%

dbms_stats.gather_schema_stats(
ownname=> 'スキーマ',
estimate_percent=> 25,
granularity=> 'DEFAULT',
block_sample=> FALSE,
cascade=> TRUE,
degree=> DBMS_STATS.DEFAULT_DEGREE,
method_opt=> 'FOR ALL COLUMNS SIZE AUTO',
options=> 'GATHER AUTO');

最終更新時間：2009年02月06日 12時55分58秒　指摘や意見などあればSandBoxのBBSへ。

ようこそ、ゲスト さん

メニュー

検索

キーワード

最新

2026/6/1

2026/5/23

2026/5/20

2026/5/19

2026/5/17

Copyright (C) 2002 たけ
Powered by FreeStyleWiki3.6.5 with Perl5.016003