oracleメモ(1)
これまで、業務システム開発でなんとなくSQLを使ってきましたが、最近、大量のデータを扱ったり直接データベースから(プログラムを介さず)データを抽出する作業などが増え、パフォーマンス等も考慮しなくてはならないなぁと、勉強を始めたので、そのメモです。
まずは、これを読む
言葉の定義
スキーマ・オブジェクト(oracleにおける)
- スキーマは、データの論理構造(スキーマ・オブジェクト)の集まりです。
- スキーマはデータベース・ユーザーによって所有され、そのユーザーと同じ名前を持ちます。ユーザーはそれぞれ1つのスキーマを所有します。
※スキーマの定義はデータベースによって違うのでややこしいですね…。
リテラル
日付リテラル
- 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でデータ抽出を行うよりは、ストアドプロシージャを定義して処理を行った方がいい場面もあるので、これからは使っていこう!!