本節將示範以PROC來取代Stored
Procedure，如下為以員工資料的到職日來產生年度特休假為例的Stored Procedure(特休假計算採用周年制)。
CREATE stored procedure sp_vacation @this_year varchar(4)
BEGIN
declare @today date
declare @emp_no nvarchar(20)
declare @name nvarchar(30)
declare @on_board date
declare @begin_date date
declare @end_date date
declare @temp_date varchar(10)
declare @dayn int
declare @job_day int
declare @job_year int
declare @next_year varchar(4)
set @today = getdate()
set @next_year =
STR(convert(int,@this_year)+1,4)
if (year(@today)>convert(int,@this_year))
set @today = @this_year+'-12-31'
declare MyCursor Cursor for
select 員工編號,姓名,到職日期
from 員工資料表
open MyCursor
fetch next from MyCursor into
@emp_no,@name,@on_board
while @@fetch_status =0
begin
set @job_day =
DATEDIFF(DAY,@on_board,@today)
set @job_year =
DATEDIFF(YEAR,@on_board,@today)
if @job_day>=183 and
@job_day<=365 -- 半年內3天
begin
set @begin_date =
DATEADD(MONTH,6,@on_board)
set @end_date = DATEADD(YEAR,1,@on_board)
set @dayn= 3
end;
if @job_year>0
begin
set @temp_date = (select
CONVERT(char(10), @on_board, 120))
set @begin_date =
@this_year+substring(@temp_date,5,6)
set @end_date = @next_year+substring(@temp_date,5,6)
set @dayn = CASE
when @job_year=1 then
7
when @job_year=2 then
10
when @job_year>=3
and @job_year<=4 then 14
when @job_year>=5
and @job_year<=9 then 15
when @job_year>=10
then @job_year+6
end;
if @dayn>30 set @dayn
= 30;
end;
if (@job_day>=183)
begin
IF EXISTS (select *
from 年度特休假表 where 年度=@this_year
and 員工編號=@emp_no)
update 年度特休假表 set 到職日期=@on_board,應休起始日期=@begin_date,應休終止日期=@end_date,應休天數=@dayn where 年度=@this_year and 員工編號=@emp_no;
ELSE
insert 年度特休假表 (年度,員工編號,姓名,到職日期,應休起始日期,應休終止日期,應休天數) values this_year,@emp_no,@name,@on_board,@begin_date,@end_date,@dayn)
end;
fetch next from MyCursor into
@emp_no,@name,@on_board
end;
close MyCursor
deallocate MyCursor
END
以上的SP不但不容易閱讀與維護，更會因為資料庫的類型不同語法會不同，將其改用PROC之後，如下的格式:
Step1>新增一個PROC，命名為"年度特休假表產生"，[開始]的作業設定一個yyyy的傳入變數代表年度。
Step2>貼入一個[sql命令]活動，用來刪除該年度的所有特休假資料，好重新產生。CommandText設定為:
DELETE 年度特休假表 WHERE 年度=@yyyy
Parameters則選擇@的"yyyy"對應到"yyyy"變數。
Step3>貼入一個[sql查詢]活動，用來讀取員工資料。CommandText設定為:
select 員工編號,姓名,到職日期 from 員工資料表
因為沒有'@'參數所以不必設定Parameters，設定Var為rows存放查詢的結果。
Step4>貼入一個[迴圈]活動，用來處理每個員工的特休假天數，設定LoopArrayVar為rows，LoopObjVar為row來取出每筆rows的資料。
Step5>[迴圈]活動中貼入一個[設值]活動，來計算該員工的年資與年假的可休起始日與終止日，如下:
因為yyyy是由使用者決定的，所以計算年資須配合yyyy的內容。
$today.FORMAT("yyyy-MM-dd").SUBSTR(4,6)
代表取出今天的日期(不包含年度)，前面加上yyyy後設值到today的變數(代表指定年度的今天)。
row.到職日期.DIFF(today)代表從該員工的到值日起算到today這天為止，共有幾天，存到days變數上。
(days/365.25).FLOOR()代表計算出年資並捨去小數，年資存放到years變數。
Row.到職日期.ADD(years*365.25)可以計算出特休假的可休起始日。
Row.到職日期.add((years+1)*365.25)則可以計算出特休假的可休終止日。
Step6>[設值]活動的下一個貼入一個[條件]活動，原本只有2個[分支]活動在內，為了能滿足法令，可以在[條件]活動內，拖入5個[分支] 活動，如下圖:
依法令分成:未滿1年超過半年、滿1年、滿2年、滿3~4年、滿5~9年、10年以上、24年以上；並設定各分支條件的expression(以years來判斷)；每個分支依照不同的條件給不同的Holiday值(以[設值] 活動來設定)。
以上只有 [未滿1年超過半年] 這個分支比較特殊，需要重新設定start_date與end_date，如下: (到職滿半年才能休)
Step7>接著往下持續貼入一個 [條件]活動，將左邊的分支條件設定為"days>=183"代表超過半年的才有特休假，右邊分支則代表沒有特休假(不設條件)，並在左邊的分支中貼入[sql命令]活動，設定如下:
CommandText設定:
insert into 年度特休假表 (年度,員工編號,姓名,到職日期,應休起始日期,應休終止日期,應休天數)
values (@yyyy,@emp_no,@name,@on_board,@start_date,@end_date,@holiday)
用來Insert到[年度特休假表]中，用到多個'@'參數，分別為yyyy、emp_no、name、on_board、start_date、end_date及holiday。
Parameters設定如下:
|
Name (@參數) |
Var (帶入內容) |
|
yyyy |
yyyy |
|
emp_no |
row.員工編號 |
|
name |
row.姓名 |
|
on_board |
row.到職日期.FORMAT("yyyy/MM/dd") |
|
start_day |
start_date.FORMAT("yyyy/MM/dd") |
|
end_date |
end_date.FORMAT("yyyy/MM/dd") |
|
holiday |
Holiday |
以上如果為日期型態欄位的回寫，需透過 FORMAT("yyyy/MM/dd")轉換格式。
Step10>接著我們可以打開[員工資料表]查看各員工的"到職日期"，如下:
然後按下【預覽】來debug，如下:
首先模擬使用者傳送過來的yyyy，在debug下方設定yyyy='2022'。
設定迴圈內[計算到職年數與天數]為中斷點，執行後，再【下一步】，此時第一筆001員工的到職日期為"2003/05/18"，所以計算出來的days為6918天，years年資為18年，所以會進入 [10年以上]這個條件分支，Holiday為年資+6，所以為24天。
最後寫入到[年度特休假表]中的SQL語句為:
insert
into 年度特休假表 (年度,員工編號,姓名,到職日期,應休起始日期,應休終止日期,應休天數) values
@yyyy,@emp_no,@name,@on_board,@start_date,@end_date,@holiday) ;
@yyyy='2022';@emp_no='001';@name='高志明';@on_board='2003/05/18'; @start_date='2021/05/17';@end_date='2022/05/18';@holiday='24'
可以透過【執行】功能，讓循環迴圈執行完畢(所有員工)，並結束PROC。