PL/SQL の倉庫

SQL の活用例と PL/SQL プログラムへ組み込む共通関数ライブラリ
ちりも積もれば山。あったら便利、なくても大して困らない。隙間を埋めるニッチなストアードプログラム
パッケージ化もしてないので、ごみの山…かもしれません。

※ このページはあまりに雑多なので無くす方向で努力しています。

数値関数 (切り上げ、端数、16進数、円周率)

TRUNC_MOD ファンクション(指定桁以下の端数を取り出す)

  • 指定桁の端数部を取り出す。(デフォルトは数値の小数部を取り出す)
CREATE OR REPLACE FUNCTION RIVUS.TRUNC_MOD(P_NUM IN NUMBER, P_PLACE IN INTEGER := 0)
RETURN NUMBER
IS
BEGIN
  RETURN MOD(P_NUM, 10**P_PLACE);
END;
/

HEXTODEC ファンクション(16進数→10進数変換)

16進数 ⇔ 10進数 変換関数 ⇒ 10進数、16進数 変換

PI ファンクション(円周率)

  • PI (十分な精度をもつ近似値: マニュアルの仕様 (ATAN) では 30 桁までの精度しかない)
    常に一定の数値の戻すので、キャッシュに残ることを期待して DETERMINISTIC にしている。
    38 桁の円周率を使用する場合には、それほどの精度が本当に必要かどうかを見極める必要があるのではないか? という意味不明な理由で不正確な計算式の方をデフォルトにしておきました。(通常計算では 10 桁のコンスタント値であれば十二分と思います。)
CREATE OR REPLACE FUNCTION RIVUS.PI
RETURN NUMBER
DETERMINISTIC
IS
    PI CONSTANT NUMBER  := 4*(4*ATAN(1/5)-ATAN(1/239));
--  PI CONSTANT NUMBER  := 3.1415926535897932384626433832795028841971;
BEGIN
	RETURN PI;
END;
/
-- 計算式を使用した近似値の場合
-- NUMBER型 の最大精度は 38 桁
SELECT TAN(PI/2), TAN(PI), SIN(PI)  FROM DUAL ;
TAN(PI/2)   TAN(PI)   SIN(PI)
---------- ---------- ----------
1.6667E+37 -1.100E-37 1.1000E-37
 
-- コンスタント
-- PI CONSTANT NUMBER  := 3.14〜を使用すると、TAN(PI/2) はオーバフローします。
SELECT TAN(PI), SIN(PI)  FROM DUAL ;
  TAN(PI)   SIN(PI)
---------- ----------
        0          0

文字関数 (複数文字 TRIM、逆さ読み、LENGTH(NULL)=0)

REVERSE_MULTIBYTE ファンクション(マルチバイトキャラクタ対応:逆さ読み)

文字列逆さ読み関数

  • REVERSE は 標準SQL関数(非公式 シングルバイト用)
    REVERSE は、何故かマニュアルにないため(マルチバイトキャラクタ対応する予定でもあるのかな?)、 ほぼ間違いなくサポート対象外の関数と思われる。(プログラミングでは UTL_RAW.REVERSE を使用する。)
CREATE OR REPLACE FUNCTION RIVUS.REVERSE_MULTIBYTE(P_STR IN VARCHAR2)
RETURN VARCHAR2
IS
	vReverse	VARCHAR2(4000);
BEGIN
	FOR i in REVERSE 1..LENGTH(P_STR)
	LOOP
		vReverse := vReverse || SUBSTR(P_STR, i, 1);
	END LOOP;
	RETURN vReverse;
END;
/

文字関数 (日本語処理:全角を含む文字、半角カナの変換)

IS_MULTIBYTE ファンクション (全角文字:マルチバイト文字が含まれるか)

効率が良いとは限りませんが、コンセプトは手抜きです。

CREATE OR REPLACE FUNCTION RIVUS.IS_MULTIBYTE(P_CHAR IN VARCHAR2)
RETURN PLS_INTEGER
IS
BEGIN
	IF ( P_CHAR <> CONVERT(P_CHAR, 'US7ASCII')) THEN
		RETURN 1;
	END IF;
	RETURN 0;
END;
/

開発補助コード (TRUNCATE TABLE)

TRUNCATE TABLE ラッパープロシージャ

通常セキュリティ上の理由からオブジェクトのオーナとプロシージャの実行者(システム利用者)は異なる設計を行なう。
そのため TRUNCATE TABLE を実行させるためにマニュアル通り DROP ANY TABLE 権限を付与してしまうケースも少なくない。
これは、他のスキーマオブジェクトを自由に DROP TABLE できる非常に強力な権限であるため、その権限の影響力は広範囲になり、 これを排除しなければならないケースがある。( 1 インスタンスに 複数のシステムとスキーマが共存している環境など )

そのためには DROP ANY TABLE(TRUNCATE TABLE) の実行は許可せずプロシージャ経由で実行させる。
これで少なくとも他システムのスキーマに影響が及ばないようにすることができる。
(自システムのスキーマへの DDL 実行監視は監査等で早期発見するしかない。)

切り捨てできるテーブルを制限しているのは気休め程度で削除しても問題はないが、スキーマ名による制限を削除すると存在価値がなくなる。 ( 'TRUNCATE TABLE RIVUS. || vTarget ) -- 斜体の部分

CREATE OR REPLACE PROCEDURE RIVUS.TRUNC_TABLE(P_TABLE IN VARCHAR2)
AUTHID DEFINER
IS
	vSearch	VARCHAR2(30);
	vTarget	VARCHAR2(30);
	TYPE  TABLE_LIST_T IS TABLE OF VARCHAR2(30);
	-- TRUNCATE TABLE LIST
	TRUNCATABLE_LIST TABLE_LIST_T := TABLE_LIST_T(
		'TABLE_A', 'TABLE_B', 'TABLE_C'
	);
BEGIN
	vTarget := NULL;
	vSearch := UPPER(P_TABLE);
	FOR i IN 1..TRUNCATABLE_LIST.COUNT
	LOOP
		IF (vSearch = TRUNCATABLE_LIST(i)) THEN
			vTarget := vSearch;
			EXIT;
		END IF;
	END LOOP;
 
	IF ( vTarget IS NOT NULL) THEN
		EXECUTE IMMEDIATE 'TRUNCATE TABLE RIVUS.' || vTarget ;
	ELSE
		NULL; -- or RAISE userdef_exception;
	END IF;
END;
/


SQL Tips (別の新規ページに分割予定)

データがありなしのチェック (指定条件のデータがあるか、ないかだけの確認)

  • 1 行取得で実行終了するための高速になる場合がある(使用するインデックスと検索件数が多い場合)
    SELECT /*+ FIRST_ROWS(1) */ COUNT(*) FROM table_name WHERE condition
           AND ROWNUM <= 1
    データがある場合 1 なければ 0 を戻す(hint 句は冗長かも)
    インデックスの未使用、小さな中間レコードセット、結果セットの場合には効果薄。

移転

ROUNDUP ファンクション(切り上げ)

ROUNDUP 関数

IS_DATE ファンクション (日付文字列の有効性チェック) ;

日付文字列をチェックするファンクション ⇒ 文字列が日付に変換できるかチェックする

YEAR_OF_AGE ファンクション (誕生日から年齢を算出)

システム日付における年齢を算出する。⇒ 誕生日から年齢を算出する

LENGTH0 ファンクション( 長さ0 の文字列を長さ0 とする LENGTH )

長さ0 の文字列を長さを計る ⇒ LENGTH

チェックデジット(C/D:チェックディジット)の算出 (JAN、ITF コード用)

移転済 ⇒ JAN コードのチェックデジットを計算する

TRIM_MULTI ファンクション(半角全角、複数文字対応の TRIM)

TRIM 関数

日付関数 (日付チェック、年齢、年号、月初、先週日付)

NAME_OF_ERA ファンクション (指定の日付の年号名を取得)

TO_CHAR 関数(日付)

FIRST_DAY ファンクション (月初めを取得)

LAST_DAY 関数

PREV_DAY ファンクション (先週のある曜日の日付を取得)

NEXT_DAY 関数

日付の平均値を取得する

MEDIAN 関数 に移動

Table of Contents

  1. PL/SQL の倉庫
  2. 数値関数 (切り上げ、端数、16進数、円周率)
  3. TRUNC_MOD ファンクション(指定桁以下の端数を取り出す)
  4. HEXTODEC ファンクション(16進数→10進数変換)
  5. PI ファンクション(円周率)
  6. 文字関数 (複数文字 TRIM、逆さ読み、LENGTH(NULL)=0)
  7. REVERSE_MULTIBYTE ファンクション(マルチバイトキャラクタ対応:逆さ読み)
  8. 文字関数 (日本語処理:全角を含む文字、半角カナの変換)
  9. IS_MULTIBYTE ファンクション (全角文字:マルチバイト文字が含まれるか)
  10. 開発補助コード (TRUNCATE TABLE)
  11. TRUNCATE TABLE ラッパープロシージャ
  12. SQL Tips (別の新規ページに分割予定)
  13. データがありなしのチェック (指定条件のデータがあるか、ないかだけの確認)
  14. 移転
  15. ROUNDUP ファンクション(切り上げ)
  16. IS_DATE ファンクション (日付文字列の有効性チェック) ;
  17. YEAR_OF_AGE ファンクション (誕生日から年齢を算出)
  18. LENGTH0 ファンクション( 長さ0 の文字列を長さ0 とする LENGTH )
  19. チェックデジット(C/D:チェックディジット)の算出 (JAN、ITF コード用)
  20. TRIM_MULTI ファンクション(半角全角、複数文字対応の TRIM)
  21. 日付関数 (日付チェック、年齢、年号、月初、先週日付)
  22. NAME_OF_ERA ファンクション (指定の日付の年号名を取得)
  23. FIRST_DAY ファンクション (月初めを取得)
  24. PREV_DAY ファンクション (先週のある曜日の日付を取得)
  25. 日付の平均値を取得する
日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ Oracle のライセンスがわからない…
Oracle Direct (ネットで聞いても最後はここで要確認)