Ta-kaku.jp Tools
Update 2010/02/06
技術メモ内容
トップページ
ハードやソフトの技術メモです。 ほとんど、個人の備忘録のレベルですので高度なことは書かれていません。 皆様に少しでもお役に立てれば幸いです。
MENU
SQL
サイト更新情報
2008/10/18
「Ta-kaku Tools 住所検索・郵便番号検索・事業所名検索・駅検索・電話局番検索」を Ver2.202 にリビジョンアップしました。
更新内容は以下の通りです。
・「xxx1丁目」の様に「全角の数字」と「丁目」が続いている文字列が入力されると検索できない事がある不具合を改修しました。
2008/10/01
Ta-kaku Tools Whois検索 Ver1.000をリリースしました。
2008/09/30
「Ta-kaku Tools 住所検索・郵便番号検索・駅検索・電話番号(局番)検索 」を Ver2.201 にリビジョンアップしました。
更新内容は以下の通りです。
・電話番号(局番)検索機能を正式版にしました。 ・無効な数値に対して住所の検索を行っていた処理を改善しました。 ・局番0120指定でGoogleMapがおかしな場所を示していた不具合を修正しました。 ・「東京駅駅」のように入力されると、関係の無いデータが出力される不具合を修正しました。
過去の情報
関連キーワード
技術メモ インデックス
カテゴリーを選択してください。
インストール
プログラミング言語
マークアップ言語
データベース言語
スクリプト言語
MS-Office
その他
Webアプリケーション
の開発方法
SQL 技術メモ
追加日時の新しい方から掲載しています。
空き番号の範囲を求める
登録日:2007/05/17   分類:Oracle
select DAT01 + 1 as 空き番号の範囲, '開始' as 判別
    from TBL01
    where
    (DAT01 + 1) not in (
        select DAT01
        from TBL01
    )
and DAT01 between 1 and (select count(*) from TBL01)-1
union
select DAT01 - 1 as 空き番号の範囲, '終了' as 判別
    from TBL01
    where
    (DAT01 - 1) not in (
        select DAT01
        from TBL01
    )
    and DAT01 between 2 and (select count(*) from TBL01)
order by 空き番号の範囲
;
金額に0を補う
登録日:2007/05/15   分類:Oracle
select rpad(DAT01, 6, 0)
from TBL01
;
階層表示
登録日:2007/05/15   分類:Oracle
select
DAT01 as 商品名称,
(lpad(' ', 4 * (level - 1)) || DAT02) as 商品番号
from
TBL01
start with DAT02 in (
100, 200
)
connect by prior DAT02 = DAT03 -- DAT02=流用商品, DAT03=親商品
order by 商品名称, level, 商品番号
;
ある金額以上の合計
登録日:2007/05/15   分類:Oracle
select
sum(case when DAT01 <= 1000000 then DAT01 else 0 end) as 金額
from TBL01
;
decodeのcase的使用例
登録日:2007/05/15   分類:Oracle
select
decode(DAT01,
    1, 'A店',
    2, 'B店',
    3, 'C店',
       '該当なし'
) as 店名,
DAT02, DAT03 from TBL01 where
 :
 : 略
 :
SQL関数クイックリファレンス
登録日:2007/05/05   分類:Oracle
書式
    function(argument, argument, ...)

注意
    ・引数に型以外を指定すると「暗黙の型変換」が発生しレスポンスが低下する。
    ・引数にNULLを指定すると通常NULLが戻るが、以下の関数は例外。
      concat(), decode(), dump(), nvl(), replace()

スカラー関数(単一行関数)
    ・表やビューの各行に対して単一の結果行を返す。
    ・group by を含まない、select の where, start, with, conect by に指定可能。

    数値関数
        ・入力に数値を受け取り、結果に数値を返す。

        abs(N)                  Nの絶対値を返す。
        ln(N)                   Nの自然対数を返す(N<0)
        ceil(N)                 N以上の最小値を返す。
        floor(N)                N以下の最大値を返す。

        sign(N)                 N<0⇒-1, N=0⇒0, 0<N⇒1 を返す。
        mod(M, N)               M/Nの余りを返す(N=0⇒Mを返す)
        power(M, N)             M^Nを返す(M<0⇒N=整数)

        log(M, N)               Nの対数Mの基底を返す(0<N, M=0 or M!=1)
        exp(N)                  eをN乗した値を返す(e=2.71828183...)
        sqrt(N)                 Nの平方根を返す(0<N)

        sin(N)                  角度Nのサインを返す。
        cos(N)                  角度Nのコサインを返す。
        tan(N)                  角度Nのタンジェントを返す。

        sinh(N)                 Nの双曲線サインを返す。
        cosh(N)                 Nの双曲線コサインを返す。
        tanh(N)                 Nの双曲線タンジェントを返す。

        asin(N)                 Nの逆サインを返す(N=-1?1, 出力=-pi/2?pi/2)
        acos(N)                 Nの逆コサインを返す(N=-1?1, 出力=0?pi)
        atan(N)                 [※1]Nの逆タンジェントを返す(N=∞, 出力=-pi/2?pi/2)
        atan2(N, M)             [※1]NとMの逆タンジェントを返す(N=∞, 出力=-pi?pi)

        tanc(N, [M])            Nを少数点M桁に切り捨てた値を返す([M]=0と同様, M=0⇒0)
        round(N, [M])           Nを少数点以下M桁に丸めた値を返す([M]=0と同様)

    文字関数
        ・入力に文字を受け取り、結果に文字を返す。
        ・char型の関数は255バイト以下で返す。
        ・varchar2型の関数は2000バイト以下で返す。

        length(C)               Cの長さを返す(C=null⇒nullを返す)
        lengthb(C)              結果をバイトで返す事意外、length()と同等。
        instr(C1, C2, [N,[M]])  C1のN桁からC2を検索しC2がM番目に出現した位置を返す。
                                (N<0⇒逆方向から, M<0, [N]⇒1, [M]⇒1)
        instrb(C1, C2, [N,[M]]) 結果をバイトで返す事意外、instr()と同等。

        chr(A)                  アスキーをキャラクターで返す。
        ascii(C)                Cキャラクターをアスキーで返す。

        substr(C, S, [N])       CのS番目からN文字分を返す([N]⇒末尾まで返す)
        concat(C1, C2)          C1とC2を連結して返す。

        lower(C)                小文字で返す。
        upper(C)                大文字で返す。
        initcap(C)              各単語の先頭を大文字でその他は小文字で返す。

        lpad(C1, N, [C2])       C1の前にC2をN桁になるまで挿入して返す([C2]⇒" ")
        rpad(C1, N, [C2])       C1の後にC2をN桁になるまで追加して返す([C2]⇒" ")

        ltrim(C1, [S])          C1の先頭からSが無くなるまで削除して返す([S]⇒" ")
        rtrim(C1, [S])          C1の末尾からSが無くなるまで削除して返す([S]⇒" ")

        replace(C, S, [R])      CからSを探してRに置き換えて返す([R]⇒"")
        translate(C, F, T)      CがFにある場合Tに置き換えて返す。無い場合は消える。

        nlssort(C, [nls])       [※2]Cをソートする為の文字列のバイトを返す。
        nls_initcap(C, [nls])   [※2]各単語の先頭を大文字でその他は小文字で返す。
        nls_lower(C, [nls])     [※2]小文字で返す。
        nls_upper(C, [nls])     [※2]大文字で返す。

        soundex(C)              Cと同じ発音を持つ文字を返す。

    日付関数
        ・date型の値を操作する。

        sysdate                 現在の日時を返す。

        last_day(D)             日付Dに対応する月の月末日を返す。
        add_months(D, N)        Nヶ月後の日付Dを返す。

        months_between(D1, D2)  日付D1とD2の月数を返す。


        tanc(D, [F])            [※3][※7]Dを書式Fで指定した単位に切り捨てた結果を返す
                                ([F]=最も近い日)
        round(D, [F])           [※3][※7]Dを書式Fで指定した単位に丸めた結果を返す
                                ([F]=最も近い日)

        next_day(D, W)          日付Dより後の最初の曜日Wを返す。
        new_time(D, Z1, Z2)     [※4]時間帯Z1の日付Dが時間帯Z2の日付を返す。

    変換関数
        ・型を変換して返す。

        to_date(C, [F,[nls]])   [※6][※7]Cをdate型に変換して返す。
        to_number(C, [F,[nls]]) [※6][※7]Cをnumber型に変換して返す。
        to_char(D, [F, [nls]])  [※6][※7]Dを書式Fで編集した結果をvarchar2型で返す。
                                Dには、日付, ラベル, 数値を指定する事が可能。
        to_single_byte(C)       Cをシングルバイトにして返す。
        to_label(C, [F])        [※7]Cをmlslabel型に変換して返す。

        chartorowid(C)          char型, varchar2型 ⇒ rowid型に変換して返す。
        hextoraw(C)             16進数で表したC ⇒ raw型に変換して返す。
        rawtohex(R)             raw型 ⇒ 16進数で表したCに変換して返す。
        rowidtochar(R)          rowid値 ⇒ varchar2型に変換して返す。

        convert(C, D, [S])      [※5]DB上SのキャラクターセットのCをDの
                                キャラクターセットに変換して返す。

    その他
        nvl(E1, E2)             E1がnullの場合E2を返す。

        least(list)             リスト内の最小値を返す。
        greatest(list)          リスト内の最大値を返す。

        least_ub(label)         ラベルリスト内の最小上限を返す。
        greatest_lb(label)      ラベルリスト内の最大下限を返す。

        user                    ユーザ名を返す。
        uid                     ユーザ番号を返す。
        userenv(option)         セッション情報を返す(主なoptionは以下参照)
                                'TERMINAL'⇒クライアント端末名を返す。
                                'SESSIONID'⇒セッションIDを返す。
                                'LANGUAGE'⇒使用言語とキャラクターセットを返す。
                                'LANG'⇒使用言語の略称(ISO略称)を返す。

        dump(E, [F, [S, [L]]])  Eのdump情報を返す。
                                (F=8⇒8進, F=10⇒10進, F=16⇒16進, F=17⇒単一文字
                                 S=スタート位置, L=長さ)

グループ関数(集合体関数)
    ・問い合わせ対象の行のグループに対して単一の結果行を返す。
    ・選択リスト, having に指定可能。
    ・nullは無視される。無視させないようにするには、nvl()を使用。
    ・オプション(op)が指定可能
      (distinct⇒異なる値のみ作用, all⇒全ての値に作用:デフォルト)

        min([op] E)             Eの最小値を返す。
        max([op] E)             Eの最大値を返す。
        sum([op] N)             Nの合計値を返す。
        avg([op] N)             Nの平均値を返す。

        stddev([op] X)          Xの標準偏差を返す。
        variance([op] X)        Xの分散を返す。

        lub([op] L)             ラベルLの最小上限を返す。
        glb([op] L)             ラベルLの最大下限を返す。

備考
    ・※1 ∞は制限無しを示しています。
    ・※2 nls関数の詳細は省略します。
    ・※3 切り捨て単位の詳細は省略します。
    ・※4 new_time()の時間帯の詳細は省略します。
    ・※5 キャラクターセットの種類は省略します。
    ・※6 nls指定は省略します。
    ・※7 書式については省略します。
PR