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 (別の新規ページに分割予定)
データがありなしのチェック (指定条件のデータがあるか、ないかだけの確認)
移転
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 関数 に移動
■
■
■
印刷表示