本節將示範以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。