ユーザー定義関数の便利さがわかってくると、頻繁に行うクエリは関数にしてしまうと便利なことに気づく。また便利であるとともに、複雑なクエリを作ったときでも構造がシンプルになり見やすくなるメリットもあるだろう。
たとえば本校のシステムでは、毎年の授業講座について「年度」、「受講登録番号」、「講座展開番号」の3つのコードで管理している。ある年度について、各曜日の時間帯に設定した講座を受講登録番号に割り当て、同時間帯に複数の講座に分割して実施する場合は異なる受講登録番号を割り当てる。この3つのパラメーターで講座が決定される。
そこで「年度」、「受講登録番号」、「講座展開番号」の値から、その講座の詳細情報を得られるユーザー定義関数を作る。関数の引数は、「年度」、「受講登録番号」、「講座展開番号」の3要素に加えて何を返すのかを指定する「パラメーター」の4つとする。パラメーターは意味がわかるように「名称」、「教員」、「教員名」といったテキストで指定することにする。
使い方は例えばこのようになる。
＜その講座の授業担当者名を知りたいとき＞
select fx講座('09',123,'01','教員名')
＜結果＞
松本吉生
とりあえず取得したい要素を「講座名称」、「授業担当教員の職員番号」、「授業担当教員名」、「出欠登録をする教員の職員番号」、「出欠登録をする教員名」と考えた。このように作ったユーザー定義関数は、パラメーターを追加することで拡張することができる。
しかし注意点としては、一度実装してしまったユーザー定義関数、パラメーターなどを変更することは難しい。その関数がどこで使われているかを全て洗い出し、問題がないことを確かめてから変更しなければならない。システムの規模が大きくなると事実上変更することは不可能になると思って最初から吟味して実装することがいいだろう。
＜ユーザー定義関数を作るCREATE FUNCTION文サンプル＞
CREATE function [dbo].[fx講座]
/*
現在の講座の情報を返す関数。講座を決定する要素は、年度、受講登録番号、講座展開番号、パラメータ。返す値は、名称、教員コード、教員名、・・・など。varchar(50)、後に拡張できる要素を残しておく。パラメータは’名称’、’教員’、’教員名’、'出欠教員'、'出欠教員名'・・・とする*/
(
@p_年度 char(2),
@p_受講登録番号 int,
@p_講座展開番号 char(2),
@p_パラメータ varchar(50)
)
returns varchar(50)
as
begin
if @p_パラメータ = '名称'
return (select koza_3_nm from c_k6 where kainendo_cd = @p_年度 and jyukotoroku_no = @p_受講登録番号)
if @p_パラメータ = '教員'
return (select 教員番号 from c_k9 where kainendo_cd = @p_年度 and jyukotoroku_no = @p_受講登録番号 and bunkatsu_no = @p_講座展開番号)
if @p_パラメータ = '教員名'
return (select 職員姓名 from 職員 where 職員番号 = (select 教員番号 from c_k9 where kainendo_cd = @p_年度 and jyukotoroku_no = @p_受講登録番号 and bunkatsu_no = @p_講座展開番号))
if @p_パラメータ = '出欠教員'
return (select 出欠教員番号 from c_k9 where kainendo_cd = @p_年度 and jyukotoroku_no = @p_受講登録番号 and bunkatsu_no = @p_講座展開番号)
if @p_パラメータ = '出欠教員名'
return (select 職員姓名 from 職員 where 職員番号 = (select 出欠教員番号 from c_k9 where kainendo_cd = @p_年度 and jyukotoroku_no = @p_受講登録番号 and bunkatsu_no = @p_講座展開番号))
return null
end