用户定义的函数与存储过程
CREATE FUNCTION [dbo].[find_regular_expression]
(
@source varchar(5000), --需要匹配的源字符串
@regexp varchar(1000), --正则表达式
@ignorecase bit = 0 --是否区分大小写，默认为false
)
RETURNS bit --返回结果0-false，1-true
AS
BEGIN
--0（成功）或非零数字（失败），是由 OLE 自动化对象返回的 HRESULT 的整数值。
DECLARE @hr integer
---用于保存返回的对象令牌，以便之后对该对象进行操作
DECLARE @objRegExp integer DECLARE @objMatches integer
---保存结果
DECLARE @results bit
/**//*
创建 OLE 对象实例,只有 sysadmin 固定服务器角色的成员才能执行 sp_OACreate,并确定机器中有VBScript.RegExp类库
*/
EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
/**//*
33以下三个分别是设置新建对象的三个属性。下面是'VBScript.RegExp'中常用的属性举例：
34 Dim regEx,Match,Matches '建立变量。
35 Set regEx = New RegExp '建立一般表达式。
36 regEx.Pattern= patrn '设置模式。
37 regEx.IgnoreCase = True '设置是否区分大小写。
38 regEx.Global=True '设置全局可用性。
39 set Matches=regEx.Execute(string) '重复匹配集合
40 RegExpTest = regEx.Execute(strng) '执行搜索。
41 for each match in matches '重复匹配集合
42 RetStr=RetStr &"Match found at position "
43 RetStr=RetStr&Match.FirstIndex&".Match Value is '"
44 RetStr=RetStr&Match.Value&"'."&vbCRLF Next
45 RegExpTest=RetStr
46
47*/
EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'Global', false
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignorecase
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
--调用对象方法
EXEC @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
--释放已创建的 OLE 对象
EXEC @hr = sp_OADestroy @objRegExp
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
RETURN @results
END
--
CREATE FUNCTION [gc_split]
(
@splitstring NVARCHAR(4000),
@separator CHAR(1) = ','
)
RETURNS @splitstringstable TABLE
(
[item] NVARCHAR(200)
)
AS
BEGIN
DECLARE @currentindex INT
DECLARE @nextindex INT
DECLARE @returntext NVARCHAR(200)
SELECT @currentindex=1
WHILE(@currentindex<=datalength(@splitstring)/2)
BEGIN
SELECT @nextindex=charindex(@separator,@splitstring,@currentindex)
IF(@nextindex=0 OR @nextindex IS NULL)
SELECT @nextindex=datalength(@splitstring)/2+1
SELECT @returntext=substring(@splitstring,@currentindex,@nextindex-@currentindex)
INSERT INTO @splitstringstable([item])
VALUES(@returntext)
SELECT @currentindex=@nextindex+1
END
RETURN
END
-------
/*
** Generate an ansi name that is unique in the dtproperties.value column
*/
create procedure dbo.dt_generateansiname(@name varchar(255) output)
as
declare @prologue varchar(20)
declare @indexstring varchar(20)
declare @index integer
set @prologue = 'MSDT-A-'
set @index = 1
while 1 = 1
begin
set @indexstring = cast(@index as varchar(20))
set @name = @prologue + @indexstring
if not exists (select value from dtproperties where value = @name)
break
set @index = @index + 1
if (@index = 10000)
goto TooMany
end
Leave:
return
TooMany:
set @name = 'DIAGRAM'
goto Leave
GO
--
CREATE PROCEDURE [Pr_doExchange]
(
@username [nchar](20),
@num [int],
@wareid [int]
)
AS
begin
declare @begintime datetime
declare @endtime datetime
declare @remainder int
declare @waretype int
declare @integral int /*对应奖品的积分*/
declare @contribution int /*对应奖品的贡献*/
declare @userintegral int /*查询相应用户的积分*/
declare @usercontribution int /*查询相应用户的贡献*/
declare @cardid varchar(200)
declare _tempp cursor
for select top 3 CardID from gh_cardinf where status=0 and wareid=@wareid
select @remainder=remainder,@begintime=begintime,@endtime=endtime,
@waretype=sortid,@integral=integral,@contribution=contribution
from gh_exchangerule
where wareid=@wareid
if(datediff(d,@begintime,getdate())<0 or datediff(d,@endtime,getdate())>0)
/*兑换还没开始或已经结束*/
begin
return 1;
end
/******/
else/*判断剩余数量是否大于要求的数量*/
begin
if(@remainder>@num)/*剩余数量大于要求数量*/
begin
/*判断用户积分和贡献是否够*/
select @userintegral=credits,@usercontribution=contribution from gc_users where username=@username
/*不够*/ if(@userintegral<(@num*@integral) or @usercontribution<(@num*@contribution))
begin
return 4;
end
/*够*/else
begin
insert into [gh_user-exchangeware] values
(@wareid,@waretype,@num,@num*@integral,@num*@contribution,getdate(),0,@username);
open _tempp
fetch next from _tempp into @cardid
while @@FETCH_STATUS=0
begin
insert into gh_user_card values(@cardid,@username,getdate(),@wareid);
fetch next from _tempp into @cardid
end
close _tempp
/*扣除用户积分和贡献*/
update gc_users set credits=credits-@num*@integral,contribution=contribution-@num*@contribution where username = @username;
/*减去对应奖品的数量*/
update gh_exchangerule set remainder = remainder-@num where wareid=@wareid
return 2;
end
end
else if(@remainder<@num)
begin
/*剩余数量小于要求数量*/
return 3;
end
end
end
GO
