oracleメモ(1)

これまで、業務システム開発でなんとなくSQLを使ってきましたが、最近、大量のデータを扱ったり直接データベースから(プログラムを介さず)データを抽出する作業などが増え、パフォーマンス等も考慮しなくてはならないなぁと、勉強を始めたので、そのメモです。

まずは、これを読む

言葉の定義

スキーマ・オブジェクト(oracleにおける)

  • スキーマは、データの論理構造(スキーマ・オブジェクト)の集まりです。
  • スキーマはデータベース・ユーザーによって所有され、そのユーザーと同じ名前を持ちます。ユーザーはそれぞれ1つのスキーマを所有します。

スキーマの定義はデータベースによって違うのでややこしいですね…。

リテラル

  • リテラルと定数値という用語の意味は同じで、固定データ値のこと。
  • 文字リテラルは、一重引用符で囲みます。一重引用符を付けることで、Oracleは文字リテラルスキーマ・オブジェクト名を区別します。

日付リテラル

  • DATE値を文字列リテラルに指定するか、文字値や数値をTO_DATEファンクションによって日付値に変換できます。
  • DATE値をリテラルに指定する場合は、グレゴリオ暦を使用する必要があります。次の例に示すように、ANSIリテラルを指定できますが、ANSIの日付リテラルには、時刻部分を含めず、書式'YYYY-MM-DD'で指定する必要があります。
DATE '1998-12-25'

PL/SQL

  • PL/SQLは、Oracle社が、Oracle Databaseのためにコンピュータのデータベース言語SQLを独自に拡張したプログラミング言語です。
  • PL/SQLを使用すると、手続き型言語で埋め込みSQLによる処理と同様に、変数の利用やif、for、loopなどの制御構造を記述して業務ロジックをデータベース内に実装することができ、問い合わせ結果の行を一件ずつ処理する場合には、カーソルによるFETCHループでの処理を行うことが出来ます。

ストアドプロシージャ・ストアドファンクション

  • PL/SQLのコードに名前を付けて、解析したコードをサーバに登録し、呼び出しが可能な状態にすることができる機能
  • プロシージャは戻り値無し、ファンクションは戻り値があるという違いだけ。
  • クライアントとサーバーを繋ぐネットワークという遅い通信経路に SQL と結果が何度も行き交うような処理に対して非常に有効です。

とりあえず実際に作ってみる!

[sample1] 10進数->x進数変換

 CREATE OR REPLACE FUNCTION TO_BASE
(
    p_dec   IN    NUMBER, 
    p_base  IN    NUMBER 
)

RETURN VARCHAR2--戻り値のデータ型
IS
    l_str   VARCHAR2(255) default NULL;
    l_num   NUMBER     default p_dec;
    l_hex   VARCHAR2(50)  default '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
BEGIN
    IF (TRUNC(p_dec) <> p_dec OR p_dec < 0) THEN
        RAISE PROGRAM_ERROR;
    END IF;
    LOOP
        l_str := SUBSTR(l_hex, MOD(l_num, p_base) + 1, 1) || l_str;
        l_num := TRUNC(l_num / p_base);
        EXIT WHEN (l_num = 0);
    END LOOP;
    RETURN l_str;
END;
/ 

ストアドファンクションを実際に呼んでみます。

  • 10進数の「22 」を->2進数変換
select TO_BASE(22,2) from dual

return ==> 10110

[sample2] x進数->10進数変換

CREATE OR REPLACE FUNCTION TO_DEC
(
    p_str   IN    VARCHAR2,
    p_base  IN    NUMBER
)
RETURN NUMBER-- 戻り値のデータ型
IS
    l_str   VARCHAR2(255) default p_str;
    l_num   NUMBER     default 0;
BEGIN
    FOR i IN 1..LENGTH(p_str) LOOP
        l_num := SUBSTR(l_str, LENGTH(l_str), 1) * POWER(p_base, i - 1) + l_num;
        l_str := SUBSTR(l_str, 1, LENGTH(l_str) - 1);
    END LOOP;
    RETURN l_num;
END;
/

今更ながら、とりあえずこんな感じで簡単に定義して関数として呼び出すことができるので、とても便利です。    実際、データ抽出作業において、複雑な条件やサブクエリを駆使して無理やりSQLでデータ抽出を行うよりは、ストアドプロシージャを定義して処理を行った方がいい場面もあるので、これからは使っていこう!!