SyBase PROCEDURE運用(參考範例) |
|
JLATSTAR
一般會員 發表:8 回覆:16 積分:4 註冊:2007-05-08 發送簡訊給我 |
-----------------------------------刪除舊PROCEDURE------------------------------ DROP PROCEDURE AddMinutes GO -----------------------------------建立新PROCEDURE------------------------------ create procedure AddMinutes @OTIME VARCHAR(4),@ADDMIN int, @Result VARCHAR(4) output as begin DECLARE @SM INT ,@SH INT SET @SH=CAST(LEFT(@OTIME,2) AS INT) SET @SM=CAST(RIGHT(@OTIME,2) AS INT) SET @SM=@SM @ADDMIN WHILE @SM<0 BEGIN SET @SM=@SM 60 SET @SH=@SH-1 END WHILE @SM>=60 BEGIN SET @SM=@SM-60 SET @SH=@SH 1 END IF @SH>10 SET @Result=CAST(@SH AS VARCHAR) ELSE SET @Result='0' CAST(@SH AS VARCHAR) IF @SM>10 SET @Result=@Result CAST(@SM AS VARCHAR) ELSE SET @Result=@Result '0' CAST(@SM AS VARCHAR) end go ----------------------------------------------------------測試PROCEDURE效果-------------------------------------- begin DECLARE @OTIME VARCHAR(4),@ADDMIN INT,@NTIME VARCHAR(4) SET @OTIME='1330' SET @ADDMIN=50 exec AddMinutes @OTIME,@ADDMIN,@NTIME Output PRINT @OTIME PRINT '===>' PRINT @NTIME end |
eaglewolf
資深會員 發表:4 回覆:268 積分:429 註冊:2006-07-06 發送簡訊給我 |
未考慮到 "時" 不夠減或大於23的情況
例如 DECLARE @OTIME VARCHAR(4),@ADDMIN INT,@NTIME VARCHAR(4) SET @OTIME='0000' SET @ADDMIN= -1 exec AddMinutes @OTIME,@ADDMIN,@NTIME Output result 會是'0-159' (應該會產生Error吧) DECLARE @OTIME VARCHAR(4),@ADDMIN INT,@NTIME VARCHAR(4) SET @OTIME='2359' SET @ADDMIN= 2 exec AddMinutes @OTIME,@ADDMIN,@NTIME Output result 會是'2401' (雖不會產生Error 但是值不合理) ===================引 用 JLATSTAR 文 章=================== -----------------------------------刪除舊PROCEDURE------------------------------ DROP PROCEDURE AddMinutes GO -----------------------------------建立新PROCEDURE------------------------------ create procedure AddMinutes @OTIME VARCHAR(4),@ADDMIN int, @Result VARCHAR(4) output as begin DECLARE @SM INT ,@SH INT SET @SH=CAST(LEFT(@OTIME,2) AS INT) SET @SM=CAST(RIGHT(@OTIME,2) AS INT) SET @SM=@SM @ADDMIN WHILE @SM<0 BEGIN SET @SM=@SM 60 SET @SH=@SH-1 END WHILE @SM>=60 BEGIN SET @SM=@SM-60 SET @SH=@SH 1 END IF @SH>10 SET @Result=CAST(@SH AS VARCHAR) ELSE SET @Result='0' CAST(@SH AS VARCHAR) IF @SM>10 SET @Result=@Result CAST(@SM AS VARCHAR) ELSE SET @Result=@Result '0' CAST(@SM AS VARCHAR) end go ----------------------------------------------------------測試PROCEDURE效果-------------------------------------- begin DECLARE @OTIME VARCHAR(4),@ADDMIN INT,@NTIME VARCHAR(4) SET @OTIME='1330' SET @ADDMIN=50 exec AddMinutes @OTIME,@ADDMIN,@NTIME Output PRINT @OTIME PRINT '===>' PRINT @NTIME end
------
先查HELP 再查GOOGLE 最後才發問 沒人有義務替你解答問題 在標題或文章中標明很急 並不會增加網友回答速度 Developing Tool: 1.Delphi 6 2.Visual Studio 2005 3.Visual Studio 2008 DBMS: MS-SQL |
JLATSTAR
一般會員 發表:8 回覆:16 積分:4 註冊:2007-05-08 發送簡訊給我 |
感謝eaglewolf追求真理的意見,原本只是以師父領進門,修行看個人的心態發表。
以下是更正後的 -----------------------------------刪除舊PROCEDURE------------------------------ DROP PROCEDURE AddMinutes GO -----------------------------------建立新PROCEDURE------------------------------ create procedure AddMinutes @OTIME VARCHAR(4),@ADDMIN int, @Result VARCHAR(4) output as begin DECLARE @SM INT ,@SH INT SET @SH=CAST(LEFT(@OTIME,2) AS INT) SET @SM=CAST(RIGHT(@OTIME,2) AS INT) SET @SM=@SM @ADDMIN WHILE @SM<0 BEGIN SET @SM=@SM 60 SET @SH=@SH-1 END WHILE @SM>=60 BEGIN SET @SM=@SM-60 SET @SH=@SH 1 END WHILE @SH<0 BEGIN SET @SH=@SH 24 END WHILE @SH>=24 BEGIN SET @SH=@SH-24 END IF @SH>10 SET @Result=CAST(@SH AS VARCHAR) ELSE SET @Result='0' CAST(@SH AS VARCHAR) IF @SM>10 SET @Result=@Result CAST(@SM AS VARCHAR) ELSE SET @Result=@Result '0' CAST(@SM AS VARCHAR) end go ----------------------------------------------------------測試PROCEDURE效果-------------------------------------- begin DECLARE @OTIME VARCHAR(4),@ADDMIN INT,@NTIME VARCHAR(4) SET @OTIME='1330' SET @ADDMIN=50 exec AddMinutes @OTIME,@ADDMIN,@NTIME Output PRINT @OTIME PRINT '===>' PRINT @NTIME end 當然無法防止他人蓄意使用異常資料測試 |
本站聲明 |
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇! |