カスタム外部関数とプロシージャによるSQLとPL/SQLの拡張 (2022/01/31)
カスタム外部関数とプロシージャによるSQLとPL/SQLの拡張 (2022/01/31)
多くのDBMSは特徴的な機能を持っています。例えば、プロシージャやSQLクエリで直接使用できるカスタム関数で拡張することができます。
カスタム関数はどのような場合に必要なのでしょうか。例えば、非標準的な計算、エンコード、暗号化、変換を実装する必要がある場合です。このような関数は、外部システムとのやり取りにも役立つかもしれません。
それは次のようなものでしょう。
| select id, pkg_mask.mask(card) as masked_card |
| from customer_cards |
where customer_id = 100;
私は、異なるDBMSでこの方法で拡張を繰り返してきました。今日は、Oracleでの方法を説明し、お見せします。11gから19cのOracleバージョンで動作することが証明されました。
これは決して隠れた機能ではありません。オラクルが開発した合法的なものです。しかも、オラクル自身はこの機能を使うことを推奨しています。
カスタム関数でSQLやPL/SQLを拡張する方法
全体の流れは非常にシンプルで、4つのステップを踏むだけです。
- この関数を実装するライブラリを作成し、コンパイル
- Oracle がライブラリを検出できるようにセットアップ
- Oracleにライブラリを参照するLibraryオブジェクトを作成
- SQL クエリでライブラリにアクセスするためのプロシージャラッパーを Oracle に作成
実験には、LinuxやOracle Databaseなど、作業に必要なものがすでに設定されている既製の仮想マシンのインスタンスを使うことが多いですね。ここでイメージをダウンロードし、VirtualBox（ダウンロードはこちら）にインポートすると、5分後にはOracle 19cを搭載したすぐに使える仮想マシンが手に入ります。
まず、仮想マシンにアクセスしてみましょう。
ステップ1：ライブラリの作成
これはおそらく、やらなければならないことの中で最も困難な部分でしょう。しかし、私の指示に従えば、すべてうまくいくでしょう。
この例のライブラリは、Linuxを搭載した仮想マシンがあるので、Linuxプラットフォーム用のC言語で書かれています。
注：Oracleでは、C、C++、COBOL、Java、.NETなどで書かれたライブラリーをインクルードすることができます。
ターミナルはすでに開かれているので、始めましょう。
ここでは、/home/oracleディレクトリにいることを確認します。
[oracle@localhost ~]$ pwd /home/oracle
ライブラリを配置するディレクトリを作成します。例えば、maskと呼ぶことにしましょう。そして、それを開きます。
[oracle@localhost ~]$ mkdir mask [oracle@localhost ~]$ cd mask [oracle@localhost mask]$
拡張子が "c" のファイルを作成します。これはC言語のソースコードを含むファイルです。
[oracle@localhost mask]$ nano mask.cエディターが開きます。そして、あなたのテキストを入力するか、私のテキストを貼り付けることができます。私のコードはエレガントではないかもしれませんが、2つの関数と1つのプロシージャを実装する方法の一例です。
| #include "mask.h" |
| #include <stdio.h> |
| #include <string.h> |
| char result[40]; |
| char* echo(char* str) { |
| sprintf(result, "echo %s", str); |
| return result; |
| } |
| void logit(char* str) { |
| FILE* file; |
| file = fopen("/home/oracle/mask/log.txt", "a"); |
| fprintf(file, "%s\n", str); |
| fclose(file); |
| } |
| char* mask(char* str) { |
| char cardr[16]; |
| snprintf(cardr, 7, "%s", str); |
| snprintf(result, 16, "%sXXXXXXXXXX", cardr); |
| return result; |
}
echo 関数は、単に渡された文字列を返すだけです。Oracle に組み込む前に、この関数が動作することを確認するために使用します。
mask関数は、文字列を受け取って、7番目から最後の文字までを「X」という文字に置き換えて、マスクして返します。
logitプロシージャは、渡された文字列をlog.txtファイルに書き出します。そう、これはやってはいけないことなのです。デモのためにやっています。
挿入後、エディタを閉じ（Ctrl-Xを押しながらY、Enter）、この名前で保存することに同意してください（Enterを押す）。
mask.hをもう一つ作成します。
[oracle@localhost mask]$ nano mask.h先ほどと同じように、自分でテキストを入力するか、下のテキストをコピーして保存してください。
| #ifndef mask_h_ |
| #define mask_h_ |
| extern char* echo(char* str); |
| extern void logit(char* str); |
| extern char* mask(char* str); |
#endif // mask_h_
両方のファイルが作成されたことを確認します。
[oracle@localhost mask]$ ls -l total 8 -rw-r — r — . 1 oracle oinstall 456 Jan 20 09:46 mask.c -rw-r — r — . 1 oracle oinstall 141 Jan 20 09:57 mask.h [oracle@localhost mask]$
ソースファイルをダイナミックライブラリにコンパイルします。
[oracle@localhost mask]$ gcc -shared -o libmask.so -fPIC mask.c[oracle@localhost mask]$エラーなし、素晴らしい ライブラリが作成されたことを確認します。libmask.soという名前で作成されているはずです。
[oracle@localhost mask]$ ls -l total 20 -rwxr-xr-x. 1 oracle oinstall 8416 Jan 20 10:02 libmask.so -rw-r — r — . 1 oracle oinstall 458 Jan 20 10:02 mask.c-rw -r — r — . 1 oracle oinstall 143 Jan 20 10:02 mask.h [oracle@localhost mask]$
ここで、私たちが作成しコンパイルしたライブラリが動作することを確認する必要があります。そのために、私たちのライブラリを呼び出す小さなプログラムを書いてみましょう。
[oracle@localhost mask]$ nano testlib.cこのコードを貼り付けてください。
| #include "mask.h" |
| int main(void) { |
| logit(echo("testing...")); |
| return 0; |
}
閉じる、保存、コンパイル
[oracle@localhost mask]$ gcc -Wall -L/home/oracle/mask -o testlib testlib.c -lmask [oracle@localhost mask]$
ここまではいいんです。必要なファイルが作成されたことを確認します。
[oracle@localhost mask]$ ls -l total 36 -rwxr-xr-x. 1 oracle oinstall 8416 Jan 20 10:02 libmask.so -rw-r — r — . 1 oracle oinstall 458 Jan 20 10:02 mask.c -rw-r — r — . 1 oracle oinstall 143 Jan 20 10:02 mask.h -rwxr-xr-x. 1 oracle oinstall 8472 Jan 20 10:14 testlib -rw-r — r — . 1 oracle oinstall 83 Jan 20 10:11 testlib.c [oracle@localhost mask]$
環境変数の設定を忘れないようにしましょう。
[oracle@localhost mask]$ export LD_LIBRARY_PATH=/home/oracle/mask:$LD_LIBRARY_PATH[oracle@localhost mask]$次に 実行
[oracle@localhost mask]$ ./testlib[oracle@localhost mask]$実行結果を確認します。log.txtファイルが作成され、その中に「echo testing...」という行があるはずです。
[oracle@localhost mask]$ ls -l total 40 -rwxr-xr-x. 1 oracle oinstall 8416 Jan 20 10:02 libmask.so -rw-r — r — . 1 oracle oinstall 16 Jan 20 10:18 log.txt -rw-r — r — . 1 oracle oinstall 458 Jan 20 10:02 mask.c -rw-r — r — . 1 oracle oinstall 143 Jan 20 10:02 mask.h -rwxr-xr-x. 1 oracle oinstall 8472 Jan 20 10:14 testlib -rw-r — r — . 1 oracle oinstall 83 Jan 20 10:11 testlib.c [oracle@localhost mask]$ cat log.txt echo testing… [oracle@localhost mask]$
これです。これでライブラリが動作することになり、Oracleのセットアップを開始することができます。
ステップ2: ライブラリを検出できるようにOracleをセットアップ
これを行うには、特別な設定ファイルextproc.oraにライブラリのパスを記述します。これを行うには、このファイルを開いてください。
[oracle@localhost mask]$ nano $ORACLE_HOME/hs/admin/extproc.ora開いたウィンドウの最後に、ライブラリのパスを書いた行を追加してください。
SET EXTPROC_DLLS=ONLY:/home/oracle/mask/libmask.so保存して閉じます。
これで、Oracle Database自体に直接必要なオブジェクトを作成する作業に移ることができます。
ステップ3：データベースへのオブジェクトの作成
わかりやすくするために、仮想マシンにすでにインストールされているOracle SQL Developerアプリケーションを使用します。実行してみましょう。
パスワードは標準の "oracle"です。
空のSQLワークシートを開き(Alt-F10)、Libraryを作成するコマンドを貼り付けます。
create or replace library lib_mask as '/home/oracle/mask/libmask.so';
実行(Ctrl-Enter)します。ライブラリが作成されたことを示すメッセージが表示されます。
Library LIB_MASK compiledステップ4: C言語で書かれた関数やプロシージャを呼び出すために使用するPLSQL関数とプロシージャを作成
そのためにpkg_maskパッケージを作成し、その中に1つの関数と1つのプロシージャを記述します。このコードをワークシートに貼り付けて実行します。
| create or replace package pkg_mask as |
| function mask(str varchar2) return varchar2 |
| as language c |
| library lib_mask |
| name "mask"; |
| procedure logit(str varchar2) |
| as language c |
| library lib_mask |
| name "logit" |
| parameters (str string); |
end pkg_mask;
パッケージが作成されたことを確認します。
Package PKG_MASK compiledでは、どのように動作するかを確認しましょう。そのために、パッケージ関数を呼び出す普通のselectを実行します。
select pkg_mask.mask(‘1234567890ABCD’) from dual;その結果がこちら、マスクされた文字列です。
すべて正常に動作します ))
注意：突然エラーが発生した場合は、一度SQL Developerを終了して、再度開いてみてください。
また、2つ目のロジットプロシージャがどのように動作するか確認してみましょう。
call pkg_mask.logit(‘Test write to log’);手順が正常に終了しました。ログエントリーがあることを確認してみましょう。そのために、ターミナルに戻って見てください。
[oracle@localhost mask]$ cat log.txt ‘Test write to log’
というわけで、期待通りの結果を得ることができました。素晴らしい!
まとめ
今回は、Oracleに外部ライブラリを作成してインクルードし、クエリやプロシージャで簡単に使用する方法について説明しました。この方法で実装する場合は、Oracleのドキュメントを確認するようにしてください。- 様々なバージョンで細かいニュアンスが異なる可能性があります。
私は、組織がデータによってビジネスを強化するのを支援するのが好きです。したがって、上記の件に関する皆さんのご意見をコメントでお聞きしたいと思います。
コメント
コメントを投稿