经过测试,语句运行注释的地方就出现句柄加2的现象,分不够回答后会追加分
http://topic.csdn.net/u/20100407/13/af185963-a533-4445-a548-4c999f0777f9.html?90684 DECLARE @pattern VARCHAR(255),
@matchstring VARCHAR(4000),
@global BIT ,
@Multiline bit
set @pattern ='[a-z]'
set @matchstring = 'abedeafdd'
set @global = 1
set @Multiline =1
DECLARE @result TABLE
(
Match_ID INT,
FirstIndex INT ,
length INT ,
Value VARCHAR(2000),
Submatch_ID INT,
SubmatchValue VARCHAR(2000),
Error VARCHAR(255)
)
DECLARE @objRegexExp INT,
@objErrorObject INT,
@objMatch INT,
@objSubMatches INT,
@strErrorMessage VARCHAR(255),
@error VARCHAR(255),
@Substituted VARCHAR(8000),
@hr INT,
@matchcount INT,
@SubmatchCount INT,
@ii INT,
@jj INT,
@FirstIndex INT,
@length INT,
@Value VARCHAR(2000),
@SubmatchValue VARCHAR(2000),
@objSubmatchValue INT,
@command VARCHAR(8000),
@Match_ID INT
DECLARE @match TABLE
(
Match_ID INT IDENTITY(1, 1)
NOT NULL,
FirstIndex INT NOT NULL,
length INT NOT NULL,
Value VARCHAR(2000)
)
DECLARE @Submatch TABLE
(
Submatch_ID INT IDENTITY(1, 1),
match_ID INT NOT NULL,
SubmatchNo INT NOT NULL,
SubmatchValue VARCHAR(2000)
)
SELECT @strErrorMessage = 'creating a regex object',@error=''
EXEC @hr= sp_OACreate 'VBScript.RegExp', @objRegexExp OUT
IF @hr = 0
SELECT @strErrorMessage = 'Setting the Regex pattern',
@objErrorObject = @objRegexExp
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'Pattern', @pattern
IF @hr = 0
SELECT @strErrorMessage = 'Specifying a case-insensitive match'
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'IgnoreCase', 1
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'MultiLine', @Multiline
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'Global', @global
IF @hr = 0
SELECT @strErrorMessage = 'Doing a match'
IF @hr = 0
EXEC @hr= sp_OAMethod @objRegexExp, 'execute', @objMatch OUT, @matchstring
IF @hr = 0
SELECT @strErrorMessage = 'Getting the number of matches'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objmatch, 'count', @matchcount OUT
SELECT @ii = 0
WHILE @hr = 0
AND @ii < @Matchcount
BEGIN
/*The Match object has four read-only properties.
The FirstIndex property indicates the number of characters in the string to the left of the match.
The Length property of the Match object indicates the number of characters in the match.
The Value property returns the text that was matched.*/
SELECT @strErrorMessage = 'Getting the FirstIndex property',
@command = 'item(' + CAST(@ii AS VARCHAR) + ').FirstIndex'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objmatch, @command,@Firstindex OUT
EXEC @hr = sp_OADestroy @objmatch--经过测试,语句运行到这就出现句柄加2的现象
--select @hr
--return
IF @hr = 0
SELECT @strErrorMessage = 'Getting the length property',
@command = 'item(' + CAST(@ii AS VARCHAR) + ').Length'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objmatch, @command, @Length OUT
IF @hr = 0
SELECT @strErrorMessage = 'Getting the value property',
@command = 'item(' + CAST(@ii AS VARCHAR) + ').Value'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objmatch, @command, @Value OUT
INSERT INTO @match
(
Firstindex,
[Length],
[Value]
)
SELECT @firstindex + 1,
@Length,
@Value
SELECT @Match_ID = @@Identity
/*The SubMatches property of the Match object is a collection of strings. It will only hold values if your regular expression has capturing groups. The collection will hold one string for each capturing group. The Count property (returned as SubmatchCount) indicates the number of string in the collection. The Item property takes an index parameter, and returns the text matched by the capturing group.
*/
IF @hr = 0
SELECT @strErrorMessage = 'Getting the SubMatches collection',
@command = 'item(' + CAST(@ii AS VARCHAR)
+ ').SubMatches'
IF @hr = 0
SELECT @strErrorMessage = 'Getting the number of submatches'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objSubmatches, 'count',
@submatchCount OUT
SELECT @jj = 0 WHILE @hr = 0
AND @jj < @submatchCount
BEGIN
IF @hr = 0
SELECT @strErrorMessage = 'Getting the submatch value property',
@command = 'item(' + CAST(@jj AS VARCHAR)
+ ')' ,@submatchValue=NULL
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objSubmatches, @command,
@SubmatchValue OUT
INSERT INTO @Submatch
(
Match_ID,
SubmatchNo,
SubmatchValue
)
SELECT @Match_ID,
@jj+1,
@SubmatchValue
SELECT @jj = @jj + 1
END
EXEC @hr= sp_OAGetProperty @objmatch, @command,
@objSubmatches OUT
SELECT @ii = @ii + 1
END
IF @hr <> 0
BEGIN
DECLARE @Source VARCHAR(255),
@Description VARCHAR(255),
@Helpfile VARCHAR(255),
@HelpID INT
EXECUTE sp_OAGetErrorInfo @objErrorObject, @source OUTPUT,
@Description OUTPUT, @Helpfile OUTPUT, @HelpID OUTPUT
SELECT @Error = 'Error whilst '
+ COALESCE(@strErrorMessage, 'doing something') + ', '
+ COALESCE(@Description, '')
END
EXEC sp_OADestroy @objRegexExp
EXEC sp_OADestroy @objMatch
EXEC sp_OADestroy @objSubMatchesINSERT INTO @result
(Match_ID,
FirstIndex,
[length],
[Value],
Submatch_ID,
SubmatchValue, error)
SELECT m.[Match_ID],
[FirstIndex],
[length],
[Value],[SubmatchNo],
[SubmatchValue],@error
FROM @match m
LEFT OUTER JOIN @submatch s
ON m.match_ID=s.match_ID
IF @@rowcount=0 AND LEN(@error)>0
INSERT INTO @result(error) SELECT @error
http://topic.csdn.net/u/20100407/13/af185963-a533-4445-a548-4c999f0777f9.html?90684 DECLARE @pattern VARCHAR(255),
@matchstring VARCHAR(4000),
@global BIT ,
@Multiline bit
set @pattern ='[a-z]'
set @matchstring = 'abedeafdd'
set @global = 1
set @Multiline =1
DECLARE @result TABLE
(
Match_ID INT,
FirstIndex INT ,
length INT ,
Value VARCHAR(2000),
Submatch_ID INT,
SubmatchValue VARCHAR(2000),
Error VARCHAR(255)
)
DECLARE @objRegexExp INT,
@objErrorObject INT,
@objMatch INT,
@objSubMatches INT,
@strErrorMessage VARCHAR(255),
@error VARCHAR(255),
@Substituted VARCHAR(8000),
@hr INT,
@matchcount INT,
@SubmatchCount INT,
@ii INT,
@jj INT,
@FirstIndex INT,
@length INT,
@Value VARCHAR(2000),
@SubmatchValue VARCHAR(2000),
@objSubmatchValue INT,
@command VARCHAR(8000),
@Match_ID INT
DECLARE @match TABLE
(
Match_ID INT IDENTITY(1, 1)
NOT NULL,
FirstIndex INT NOT NULL,
length INT NOT NULL,
Value VARCHAR(2000)
)
DECLARE @Submatch TABLE
(
Submatch_ID INT IDENTITY(1, 1),
match_ID INT NOT NULL,
SubmatchNo INT NOT NULL,
SubmatchValue VARCHAR(2000)
)
SELECT @strErrorMessage = 'creating a regex object',@error=''
EXEC @hr= sp_OACreate 'VBScript.RegExp', @objRegexExp OUT
IF @hr = 0
SELECT @strErrorMessage = 'Setting the Regex pattern',
@objErrorObject = @objRegexExp
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'Pattern', @pattern
IF @hr = 0
SELECT @strErrorMessage = 'Specifying a case-insensitive match'
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'IgnoreCase', 1
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'MultiLine', @Multiline
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'Global', @global
IF @hr = 0
SELECT @strErrorMessage = 'Doing a match'
IF @hr = 0
EXEC @hr= sp_OAMethod @objRegexExp, 'execute', @objMatch OUT, @matchstring
IF @hr = 0
SELECT @strErrorMessage = 'Getting the number of matches'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objmatch, 'count', @matchcount OUT
SELECT @ii = 0
WHILE @hr = 0
AND @ii < @Matchcount
BEGIN
/*The Match object has four read-only properties.
The FirstIndex property indicates the number of characters in the string to the left of the match.
The Length property of the Match object indicates the number of characters in the match.
The Value property returns the text that was matched.*/
SELECT @strErrorMessage = 'Getting the FirstIndex property',
@command = 'item(' + CAST(@ii AS VARCHAR) + ').FirstIndex'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objmatch, @command,@Firstindex OUT
EXEC @hr = sp_OADestroy @objmatch--经过测试,语句运行到这就出现句柄加2的现象
--select @hr
--return
IF @hr = 0
SELECT @strErrorMessage = 'Getting the length property',
@command = 'item(' + CAST(@ii AS VARCHAR) + ').Length'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objmatch, @command, @Length OUT
IF @hr = 0
SELECT @strErrorMessage = 'Getting the value property',
@command = 'item(' + CAST(@ii AS VARCHAR) + ').Value'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objmatch, @command, @Value OUT
INSERT INTO @match
(
Firstindex,
[Length],
[Value]
)
SELECT @firstindex + 1,
@Length,
@Value
SELECT @Match_ID = @@Identity
/*The SubMatches property of the Match object is a collection of strings. It will only hold values if your regular expression has capturing groups. The collection will hold one string for each capturing group. The Count property (returned as SubmatchCount) indicates the number of string in the collection. The Item property takes an index parameter, and returns the text matched by the capturing group.
*/
IF @hr = 0
SELECT @strErrorMessage = 'Getting the SubMatches collection',
@command = 'item(' + CAST(@ii AS VARCHAR)
+ ').SubMatches'
IF @hr = 0
SELECT @strErrorMessage = 'Getting the number of submatches'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objSubmatches, 'count',
@submatchCount OUT
SELECT @jj = 0 WHILE @hr = 0
AND @jj < @submatchCount
BEGIN
IF @hr = 0
SELECT @strErrorMessage = 'Getting the submatch value property',
@command = 'item(' + CAST(@jj AS VARCHAR)
+ ')' ,@submatchValue=NULL
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objSubmatches, @command,
@SubmatchValue OUT
INSERT INTO @Submatch
(
Match_ID,
SubmatchNo,
SubmatchValue
)
SELECT @Match_ID,
@jj+1,
@SubmatchValue
SELECT @jj = @jj + 1
END
EXEC @hr= sp_OAGetProperty @objmatch, @command,
@objSubmatches OUT
SELECT @ii = @ii + 1
END
IF @hr <> 0
BEGIN
DECLARE @Source VARCHAR(255),
@Description VARCHAR(255),
@Helpfile VARCHAR(255),
@HelpID INT
EXECUTE sp_OAGetErrorInfo @objErrorObject, @source OUTPUT,
@Description OUTPUT, @Helpfile OUTPUT, @HelpID OUTPUT
SELECT @Error = 'Error whilst '
+ COALESCE(@strErrorMessage, 'doing something') + ', '
+ COALESCE(@Description, '')
END
EXEC sp_OADestroy @objRegexExp
EXEC sp_OADestroy @objMatch
EXEC sp_OADestroy @objSubMatchesINSERT INTO @result
(Match_ID,
FirstIndex,
[length],
[Value],
Submatch_ID,
SubmatchValue, error)
SELECT m.[Match_ID],
[FirstIndex],
[length],
[Value],[SubmatchNo],
[SubmatchValue],@error
FROM @match m
LEFT OUTER JOIN @submatch s
ON m.match_ID=s.match_ID
IF @@rowcount=0 AND LEN(@error)>0
INSERT INTO @result(error) SELECT @error
@pattern VARCHAR(255),
@matchstring VARCHAR(4000),
@global BIT = 1,
@Multiline bit =1)
RETURNS
@result TABLE
(
Match_ID INT,
FirstIndex INT ,
length INT ,
Value VARCHAR(2000),
Submatch_ID INT,
SubmatchValue VARCHAR(2000),
Error VARCHAR(255)
)
AS -- columns returned by the function
BEGIN
DECLARE @objRegexExp INT,
@objErrorObject INT,
@objMatch INT,
@objSubMatches INT,
@strErrorMessage VARCHAR(255),
@error VARCHAR(255),
@Substituted VARCHAR(8000),
@hr INT,
@matchcount INT,
@SubmatchCount INT,
@ii INT,
@jj INT,
@FirstIndex INT,
@length INT,
@Value VARCHAR(2000),
@SubmatchValue VARCHAR(2000),
@objSubmatchValue INT,
@command VARCHAR(8000),
@Match_ID INT
DECLARE @match TABLE
(
Match_ID INT IDENTITY(1, 1)
NOT NULL,
FirstIndex INT NOT NULL,
length INT NOT NULL,
Value VARCHAR(2000)
)
DECLARE @Submatch TABLE
(
Submatch_ID INT IDENTITY(1, 1),
match_ID INT NOT NULL,
SubmatchNo INT NOT NULL,
SubmatchValue VARCHAR(2000)
)
SELECT @strErrorMessage = 'creating a regex object',@error=''
EXEC @hr= sp_OACreate 'VBScript.RegExp', @objRegexExp OUT
IF @hr = 0
SELECT @strErrorMessage = 'Setting the Regex pattern',
@objErrorObject = @objRegexExp
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'Pattern', @pattern
IF @hr = 0
SELECT @strErrorMessage = 'Specifying a case-insensitive match'
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'IgnoreCase', 1
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'MultiLine', @Multiline
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'Global', @global
IF @hr = 0
SELECT @strErrorMessage = 'Doing a match'
IF @hr = 0
EXEC @hr= sp_OAMethod @objRegexExp, 'execute', @objMatch OUT,
@matchstring
IF @hr = 0
SELECT @strErrorMessage = 'Getting the number of matches'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objmatch, 'count', @matchcount OUT
SELECT @ii = 0
WHILE @hr = 0
AND @ii < @Matchcount
BEGIN
/*The Match object has four read-only properties.
The FirstIndex property indicates the number of characters in the string to the left of the match.
The Length property of the Match object indicates the number of characters in the match.
The Value property returns the text that was matched.*/
SELECT @strErrorMessage = 'Getting the FirstIndex property',
@command = 'item(' + CAST(@ii AS VARCHAR) + ').FirstIndex'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objmatch, @command,
@Firstindex OUT
IF @hr = 0
SELECT @strErrorMessage = 'Getting the length property',
@command = 'item(' + CAST(@ii AS VARCHAR) + ').Length'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objmatch, @command, @Length OUT
IF @hr = 0
SELECT @strErrorMessage = 'Getting the value property',
@command = 'item(' + CAST(@ii AS VARCHAR) + ').Value'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objmatch, @command, @Value OUT
INSERT INTO @match
(
Firstindex,
[Length],
[Value]
)
SELECT @firstindex + 1,
@Length,
@Value
SELECT @Match_ID = @@Identity
/*The SubMatches property of the Match object is a collection of strings. It will only hold values if your regular expression has capturing groups. The collection will hold one string for each capturing group. The Count property (returned as SubmatchCount) indicates the number of string in the collection. The Item property takes an index parameter, and returns the text matched by the capturing group.
*/
IF @hr = 0
SELECT @strErrorMessage = 'Getting the SubMatches collection',
@command = 'item(' + CAST(@ii AS VARCHAR)
+ ').SubMatches'
IF @hr = 0
SELECT @strErrorMessage = 'Getting the number of submatches'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objSubmatches, 'count',
@submatchCount OUT
SELECT @jj = 0
WHILE @hr = 0
AND @jj < @submatchCount
BEGIN
IF @hr = 0
SELECT @strErrorMessage = 'Getting the submatch value property',
@command = 'item(' + CAST(@jj AS VARCHAR)
+ ')' ,@submatchValue=NULL
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objSubmatches, @command,
@SubmatchValue OUT
INSERT INTO @Submatch
(
Match_ID,
SubmatchNo,
SubmatchValue
)
SELECT @Match_ID,
@jj+1,
@SubmatchValue
SELECT @jj = @jj + 1
END
EXEC @hr= sp_OAGetProperty @objmatch, @command,
@objSubmatches OUT
SELECT @ii = @ii + 1
END
IF @hr <> 0
BEGIN
DECLARE @Source VARCHAR(255),
@Description VARCHAR(255),
@Helpfile VARCHAR(255),
@HelpID INT
EXECUTE sp_OAGetErrorInfo @objErrorObject, @source OUTPUT,
@Description OUTPUT, @Helpfile OUTPUT, @HelpID OUTPUT
SELECT @Error = 'Error whilst '
+ COALESCE(@strErrorMessage, 'doing something') + ', '
+ COALESCE(@Description, '')
END
EXEC sp_OADestroy @objRegexExp
EXEC sp_OADestroy @objMatch
EXEC sp_OADestroy @objSubMatchesINSERT INTO @result
(Match_ID,
FirstIndex,
[length],
[Value],
Submatch_ID,
SubmatchValue,
error)
SELECT m.[Match_ID],
[FirstIndex],
[length],
[Value],[SubmatchNo],
[SubmatchValue],@error
FROM @match m
LEFT OUTER JOIN @submatch s
ON m.match_ID=s.match_ID
IF @@rowcount=0 AND LEN(@error)>0
INSERT INTO @result(error) SELECT @error
RETURN
END
@matchstring VARCHAR(4000),
@global BIT ,
@Multiline bit
set @pattern ='[a-z]'
set @matchstring = 'abedeafdd'
set @global = 1
set @Multiline =1
DECLARE @result TABLE
(
Match_ID INT,
FirstIndex INT ,
length INT ,
Value VARCHAR(2000),
Submatch_ID INT,
SubmatchValue VARCHAR(2000),
Error VARCHAR(255)
)
DECLARE @objRegexExp INT,
@objErrorObject INT,
@objMatch INT,
@objSubMatches INT,
@strErrorMessage VARCHAR(255),
@error VARCHAR(255),
@Substituted VARCHAR(8000),
@hr INT,
@matchcount INT,
@SubmatchCount INT,
@ii INT,
@jj INT,
@FirstIndex INT,
@length INT,
@Value VARCHAR(2000),
@SubmatchValue VARCHAR(2000),
@objSubmatchValue INT,
@command VARCHAR(8000),
@Match_ID INT
DECLARE @match TABLE
(
Match_ID INT IDENTITY(1, 1)
NOT NULL,
FirstIndex INT NOT NULL,
length INT NOT NULL ,
Value VARCHAR(2000)
)
DECLARE @Submatch TABLE
(
Submatch_ID INT IDENTITY(1, 1),
match_ID INT NOT NULL,
SubmatchNo INT NOT NULL,
SubmatchValue VARCHAR(2000)
)
SELECT @strErrorMessage = 'creating a regex object',@error=''
EXEC @hr= sp_OACreate 'VBScript.RegExp', @objRegexExp OUT
IF @hr = 0
SELECT @strErrorMessage = 'Setting the Regex pattern',
@objErrorObject = @objRegexExp
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'Pattern', @pattern
IF @hr = 0
SELECT @strErrorMessage = 'Specifying a case-insensitive match'
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'IgnoreCase', 1
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'MultiLine', @Multiline
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'Global', @global
IF @hr = 0
SELECT @strErrorMessage = 'Doing a match'
IF @hr = 0
EXEC @hr= sp_OAMethod @objRegexExp, 'execute', @objMatch OUT, @matchstring
IF @hr = 0
SELECT @strErrorMessage = 'Getting the number of matches'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objmatch, 'count', @matchcount OUT
SELECT @ii = 0
SET @objSubMatches=0 WHILE @hr = 0
AND @ii < @Matchcount
BEGIN
IF @objSubMatches>0
BEGIN
EXEC sp_OADestroy @objSubMatches
SET @objSubMatches=0
END
SELECT @strErrorMessage = 'Getting the FirstIndex property',
@command = 'item(' + CAST(@ii AS VARCHAR) + ').FirstIndex'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objmatch, @command,@Firstindex OUT
IF @hr = 0
SELECT @strErrorMessage = 'Getting the length property',
@command = 'item(' + CAST(@ii AS VARCHAR) + ').Length'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objmatch, @command, @Length OUT
IF @hr = 0
SELECT @strErrorMessage = 'Getting the value property',
@command = 'item(' + CAST(@ii AS VARCHAR) + ').Value'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objmatch, @command, @Value OUT
INSERT INTO @match
(
Firstindex,
[Length],
[Value]
)
SELECT @firstindex + 1,
@Length,
@Value
SELECT @Match_ID = @@Identity IF @hr = 0
SELECT @strErrorMessage = 'Getting the SubMatches collection',
@command = 'item(' + CAST(@ii AS VARCHAR)
+ ').SubMatches'
IF @hr = 0
SELECT @strErrorMessage = 'Getting the number of submatches'
IF @hr = 0
BEGIN
EXEC @hr= sp_OAGetProperty @objSubmatches, 'count',
@submatchCount OUT
END
SELECT @jj = 0 WHILE @hr = 0
AND @jj < @submatchCount
BEGIN
IF @hr = 0
SELECT @strErrorMessage = 'Getting the submatch value property',
@command = 'item(' + CAST(@jj AS VARCHAR)
+ ')' ,@submatchValue=NULL
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objSubmatches, @command,
@SubmatchValue OUT
INSERT INTO @Submatch
(
Match_ID,
SubmatchNo,
SubmatchValue
)
SELECT @Match_ID,
@jj+1,
@SubmatchValue
SELECT @jj = @jj + 1
END
EXEC @hr= sp_OAGetProperty @objmatch, @command,
@objSubmatches OUT
SELECT @ii = @ii + 1
END
IF @hr <> 0
BEGIN
DECLARE @Source VARCHAR(255),
@Description VARCHAR(255),
@Helpfile VARCHAR(255),
@HelpID INT
EXECUTE sp_OAGetErrorInfo @objErrorObject, @source OUTPUT,
@Description OUTPUT, @Helpfile OUTPUT, @HelpID OUTPUT
SELECT @Error = 'Error whilst '
+ COALESCE(@strErrorMessage, 'doing something') + ', '
+ COALESCE(@Description, '')
END
EXEC sp_OADestroy @objSubMatches
EXEC sp_OADestroy @objMatch
EXEC sp_OADestroy @objRegexExp
INSERT INTO @result
(Match_ID,
FirstIndex,
[length],
[Value],
Submatch_ID,
SubmatchValue, error)
SELECT m.[Match_ID],
[FirstIndex],
[length],
[Value],[SubmatchNo],
[SubmatchValue],@error
FROM @match m
LEFT OUTER JOIN @submatch s
ON m.match_ID=s.match_ID
IF @@rowcount=0 AND LEN(@error)>0
INSERT INTO @result(error) SELECT @error
IF @objSubMatches>0
BEGIN
EXEC sp_OADestroy @objSubMatches
SET @objSubMatches=0
END
你copy试过我上面帖的代码吗?
handle增加不一定是sp_OAxxx创建的。你最好放回到Function里,然后循环运行1W次,看看内存有没耗尽的现象。
你那函数每运行一次便会返回一个结果集,如果你在循环运行时没有DELETE掉,那内存肯定会吃完啊,这不关sp_OAXXX的事。你COPY下面的代码运行看看,内存会GAMEOVER吗????SET NOCOUNT ON
DECLARE @i INT
SET @i=1WHILE @i<=10000000 --循环运行一千万次
BEGIN
DECLARE @pattern VARCHAR(255),
@matchstring VARCHAR(4000),
@global BIT ,
@Multiline bit
set @pattern ='[a-z]'
set @matchstring = 'abedeafdd'
set @global = 1
set @Multiline =1
DECLARE @result TABLE
(
Match_ID INT,
FirstIndex INT ,
length INT ,
Value VARCHAR(2000),
Submatch_ID INT,
SubmatchValue VARCHAR(2000),
Error VARCHAR(255)
)
DECLARE @objRegexExp INT,
@objErrorObject INT,
@objMatch INT,
@objMatchCollection INT,
@objSubMatches INT,
@strErrorMessage VARCHAR(255),
@error VARCHAR(255),
@Substituted VARCHAR(8000),
@hr INT,
@matchcount INT,
@SubmatchCount INT,
@ii INT,
@jj INT,
@FirstIndex INT,
@length INT,
@Value VARCHAR(2000),
@SubmatchValue VARCHAR(2000),
@objSubmatchValue INT,
@command VARCHAR(8000),
@Match_ID INT
DECLARE @match TABLE
(
Match_ID INT IDENTITY(1, 1)
NOT NULL,
FirstIndex INT NOT NULL,
length INT NOT NULL ,
Value VARCHAR(2000)
)
DECLARE @Submatch TABLE
(
Submatch_ID INT IDENTITY(1, 1),
match_ID INT NOT NULL,
SubmatchNo INT NOT NULL,
SubmatchValue VARCHAR(2000)
)
SELECT @strErrorMessage = 'creating a regex object',@error=''
EXEC @hr= sp_OACreate 'VBScript.RegExp', @objRegexExp OUT
IF @hr = 0
SELECT @strErrorMessage = 'Setting the Regex pattern',
@objErrorObject = @objRegexExp
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'Pattern', @pattern
IF @hr = 0
SELECT @strErrorMessage = 'Specifying a case-insensitive match'
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'IgnoreCase', 1
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'MultiLine', @Multiline
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'Global', @global
IF @hr = 0
SELECT @strErrorMessage = 'Doing a match'
IF @hr = 0
EXEC @hr= sp_OAMethod @objRegexExp, 'execute', @objMatchCollection OUT, @matchstring
IF @hr = 0
SELECT @strErrorMessage = 'Getting the number of matches'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objMatchCollection, 'count', @matchcount OUT
SELECT @ii = 0 WHILE @hr = 0
AND @ii < @Matchcount
BEGIN
SELECT @command = 'item(' + CAST(@ii AS VARCHAR) + ')'
EXEC @hr= sp_OAGetProperty @objMatchCollection, @command , @objmatch OUT
SELECT @strErrorMessage = 'Getting the FirstIndex property',
@command = 'FirstIndex'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objmatch, @command,@Firstindex OUT
IF @hr = 0
SELECT @strErrorMessage = 'Getting the length property',
@command = 'Length'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objmatch, @command, @Length OUT
IF @hr = 0
SELECT @strErrorMessage = 'Getting the value property',
@command = 'Value'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objmatch, @command, @Value OUT
INSERT INTO @match
(
Firstindex,
[Length],
[Value]
)
SELECT @firstindex + 1,
@Length,
@Value
SELECT @Match_ID = @@Identity
---Handle Submatchs------------------------------
IF @hr = 0
SELECT @strErrorMessage = 'Getting the SubMatches collection',
@command = 'SubMatches'
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objmatch, @command, @objSubmatches OUT
IF @hr = 0
SELECT @strErrorMessage = 'Getting the number of submatches'
IF @hr = 0
BEGIN
EXEC @hr= sp_OAGetProperty @objSubmatches, 'count',
@submatchCount OUT
END
SELECT @jj = 0
WHILE @hr = 0
AND @jj < @submatchCount
BEGIN
IF @hr = 0
SELECT @strErrorMessage = 'Getting the submatch value property',
@command = 'item(' + CAST(@jj AS VARCHAR)
+ ')' ,@submatchValue=NULL
IF @hr = 0
EXEC @hr= sp_OAGetProperty @objSubmatches, @command,
@SubmatchValue OUT
INSERT INTO @Submatch
(
Match_ID,
SubmatchNo,
SubmatchValue
)
SELECT @Match_ID,
@jj+1,
@SubmatchValue
SELECT @jj = @jj + 1
END
EXEC sp_OADestroy @objSubmatches
---Handle Submatchs------------------------------
EXEC sp_OADestroy @objMatch
SELECT @ii = @ii + 1
END IF @hr <> 0
BEGIN
DECLARE @Source VARCHAR(255),
@Description VARCHAR(255),
@Helpfile VARCHAR(255),
@HelpID INT
EXECUTE sp_OAGetErrorInfo @objErrorObject, @source OUTPUT,
@Description OUTPUT, @Helpfile OUTPUT, @HelpID OUTPUT
SELECT @Error = 'Error whilst-> '
+ COALESCE(@strErrorMessage, 'doing something') + ', '
+ COALESCE(@Description, '')
PRINT 'Error Object : '+cast( @objErrorObject as varchar(max)) +' Loop in '+cast(@i as varchar(30))
PRINT @error
END
EXEC sp_OADestroy @objMatchCollection
EXEC sp_OADestroy @objRegexExp
INSERT INTO @result
(Match_ID,
FirstIndex,
[length],
[Value],
Submatch_ID,
SubmatchValue, error)
SELECT m.[Match_ID],
[FirstIndex],
[length],
[Value],[SubmatchNo],
[SubmatchValue],@error
FROM @match m LEFT JOIN @submatch s
ON m.match_ID=s.match_ID
IF @@rowcount=0 AND LEN(@error)>0
BEGIN
PRINT 'ERROR!'
INSERT INTO @result(error) SELECT @error
END
SELECT * FROM @result
DELETE FROM @result --清掉每一次的结果 set @objRegexExp=0
set @objErrorObject=0
SET @i=@i+1
END
DELETE掉它.
Buffer Distribution Buffers
------------------------------ -----------
Stolen 138
Free 84
Procedures 123
Inram 0
Dirty 24
Kept 0
I/O 0
Latched 15
Other 282(所影响的行数为 9 行)Buffer Counts Buffers
------------------------------ -----------
Commited 666
Target 70619
Hashed 321
InternalReservation 119
ExternalReservation 0
Min Free 16(所影响的行数为 6 行)Procedure Cache Value
------------------------------ -----------
TotalProcs 44
TotalPages 123
InUsePages 52(所影响的行数为 3 行)Dynamic Memory Manager Buffers
------------------------------ -----------
Stolen 261
OS Reserved 128
OS Committed 100
OS In Use 93
General 197
QueryPlan 113
Optimizer 0
Utilities 9
Connection 35(所影响的行数为 9 行)
Global Memory Objects Buffers
------------------------------ -----------
Resource 80
Locks 18
XDES 5
SQLCache 19
Replication 2
LockBytes 2
ServerGlobal 42(所影响的行数为 7 行)
Query Memory Objects Value
------------------------------ -----------
Grants 0
Waiting 0
Available (Buffers) 52176
Maximum (Buffers) 52176(所影响的行数为 4 行)Optimization Queue Value
------------------------------ -----------
Optimizing 0
Waiting 0
Available 8
Maximum 8(所影响的行数为 4 行)DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。