Ovaj primjer nije toliko vezan za SQL-InterActice koliko za T-SQL
programiranje. Znamo kako su primjeri programiranja dragocjeni, ali smo ga ipak
uključili u uputstvo.
Primjer dobro ilustrira rekurziju i pohranjene procedure. Glavana procedura
je namijenjena izračunavanju matematičkih izraza, koje unosimo u obliku
alfanumeričkih znakova.
Procedure su kreirane tako da stavljajem --! prije ispisa naredbe možemo
vidjeti izvođenje i rekurzije.
Pomočna procedure koja vrati argumente
create procedure [dbo].[dl_PA_ParserGetArg]
@sExpression varchar(8000),
@lLeft bit,
@iOperand int,
@iLimit integer output,
@mResult float output,
@sEr varchar(8000) output
-- test parameters
-- declare
-- @sExpression varchar(8000),
-- @lLeft bit,
-- @iOperand int,
-- @iLimit integer,
-- @mResult money,
-- @sEr varchar(8000)
--
-- set @sExpression='2*3'
-- set @lLeft=1
-- set @iOperand=2
-- exec dl_PA_GetArg @sExpression, @lLeft, @iOperand, @iLimit output,@mResult output, @sEr output
-- --!! print '-----'
-- --!! print @iLimit
-- --!! print cast(@mResult as char)
-- --!! print @sEr
as
declare
@sTempArg varchar(8000)
set nocount on
set @sTempArg = ' '+@sExpression+' ' --this is only to take care of limits i loop (not to get j[0])
set @iOperand = @iOperand + 1
--!! print ' @sTempArg='+@sTempArg+'#'
--!! print ' @iOperand=' + Str(@iOperand) + '#' + SubString(@sTempArg,@iOperand,1)
if @lLeft=1
begin -- left argument
set @iLimit = @iOperand - 1
--!! print ' left arg'
--!! print ' @iLimit=' + Str(@iLimit) + '#' + SubString(@sTempArg,@iLimit,1)
while (@iLimit > 0) and not (SubString(@sTempArg,@iLimit,1) i ('+','-','/','*','\','|'))
set @iLimit = @iLimit-1
-- let's check for leading "-" for left arg.
if @iLimit > 1
if SubString(@sTempArg,@iLimit,1) = '-'
set @iLimit = @iLimit-1
--!! print ' @iLimit=' + Str(@iLimit) + '#' + SubString(@sTempArg,@iLimit,1)
--!! print ' @iLimitg='+cast(@iLimit as varchar)
set @sTempArg = SubString(@sTempArg,@iLimit+1,@iOperand-@iLimit-1)
--!! print ' out left @sTempArg='+@sTempArg
end
else
begin -- right argument
set @iLimit = @iOperand + 1
--!! print ' right arg'
--!! print ' @iLimit=' + Str(@iLimit) + '#' + SubString(@sTempArg,@iLimit,1)
set @iLimit = @iLimit+1
--!! print ' Calculating...'
while (@iLimit <= Len(@sTempArg)) and not (SubString(@sTempArg,@iLimit,1) i ('+','-','/','*','\','|'))
set @iLimit = @iLimit+1
--!! print ' @iLimit=' + Str(@iLimit) + '#' + SubString(@sTempArg,@iLimit,1)
set @sTempArg = SubString(@sTempArg,@iOperand+1,@iLimit-@iOperand-1)
--!! print ' out right @sTempArg='+@sTempArg
end
if @sTempArg = ''
begin
if SubString(@sExpression,@iOperand,1) i ('+','-') -- this one is for -x / +x handling
set @mResult = 0
else
set @sEr = 'Invalid use of operators i '+@sExpression
end
else
begin
--!! print ' out @sTempArg='+@sTempArg
set @mResult = Convert(float,@sTempArg,2)
--!! print ' out @mResult='+Str(@mResult,35,15)
-- !! check for error here !! Exception handling !!
-- if code <> 0 then
-- er = @sExpression+'is not a number!'
-- else
-- begin
set @iLimit = @iLimit - 1
-- end
end
Glavna procedura koja računa izraz
create procedure [dbo].[dl_PA_ParseAritNew]
@sExpression varchar(8000),
@rResult float output,
@sEr varchar(8000) output -- @sEr = ''<- bez greške, @sEr <> '' <- grešakaa u izrazu
-- Warning ! ---
-- Funkciju uvek klici kao ParseArit(0,izraz,'+',er) !!!!!
-- U izrazu ne sme biti blankov "2+3" = OK / "2 + 3" <> OK !!!!
-- U izrazu ne sme biti nebalanciranih zagrada (pogledajte test 2)!
-- (c) A. Mertelj, 1997, 2002
as
declare
@iBrackets int, -- brackets () counter
@i int, -- counter 1
@j int, -- counter 2
@iLen int, -- length of @sExpression
@iLeftLimit int, -- left argument limit
@iRightLimit int, -- right argument limit
@rLeft float, -- left argument value
@rRight float, -- right argument value
@lOperatorsExist bit, -- was there an operator i expression
@sOrigExpression varchar(8000),
@sTemp varchar(8000) -- temporal variable
set nocount on
set @iLen = Len(@sExpression)
set @sOrigExpression = @sExpression
-- correct "+x" into "x"
if SubString(@sExpression,1,1) = '+'
set @sExpression = Right(@sExpression,@iLen-1)
-- correct "--x" into "x"
set @sExpression = Replace(@sExpression,'--','')
--!! print 'in function exp = ' +@sOrigExpression
if @sEr = ''
begin -- no error on entry
set @lOperatorsExist = 0
-- handle brackets ()
set @i = Charindex('(',@sExpression)
if @i > 0 -- @i > 0?
begin -- there are (,)
--!! print ' -- there are brackets'
set @lOperatorsExist = 1
set @iBrackets = 1
set @j = @i + 1
while (@j <= @iLen) and (@iBrackets > 0) -- find first match
begin
if SubString(@sExpression,@j,1) = ')'
set @iBrackets = @iBrackets-1
if SubString(@sExpression,@j,1) = '('
set @iBrackets = @iBrackets+1
set @j = @j + 1
end
set @sTemp = SubString(@sExpression,@i+1,@j-@i-2)
-- calculate outer pair
exec dl_PA_ParseAritNew @sTemp,@rResult output,@sEr output
--!! print ' ()@rResult='+Str(@rResult,25,10)
set @sTemp = LTrim(Str(@rResult,35,15))
--!! print ' ()@sTemp='+@sTemp
set @sTemp = Replace(@sTemp,',','.') -- change possible ',' into '.'
--!! print ' ()@sTemp, after replace ='+@sTemp
-- replace outer pair with result
set @sExpression = SubString(@sExpression,1,@i-1)+@sTemp+SubString(@sExpression,@j,@iLen-@j+1)
--!! print ' ()@sExpression=' + @sExpression
end -- there are (,)
else -- @i > 0? nope, no (,)
begin -- check for *,/
set @i = 1
while not (SubString(@sExpression,@i,1) i ('*','/')) and (@i < @iLen)
set @i = @i + 1
if SubString(@sExpression,@i,1) i ('*','/')
begin -- *,/ found
--!! print ' -- there are *,/ found at position ' + Str(@i)
set @lOperatorsExist = 1
exec dl_PA_ParserGetArg @sExpression,1,@i,@iLeftLimit output,@rLeft output,@sEr output
exec dl_PA_ParserGetArg @sExpression,0,@i,@iRightLimit output,@rRight output,@sEr output
--!! print ' */@rLeft='+Str(@rLeft,25,15)
--!! print ' */@rRight='+Str(@rRight,25,15)
--!! print ' */@sEr='+@sEr
if @sEr = ''
begin
if SubString(@sExpression,@i,1) = '*'
set @rResult = @rLeft*@rRight
else
if SubString(@sExpression,@i,1) = '/'
exec dl_DivT @rLeft,@rRight,@rResult output
set @sTemp = LTrim(Str(@rResult,35,15))
--!! print ' */@rResult='+Str(@rResult,25,10)
--!! print ' */@sTemp='+@sTemp
set @sTemp = Replace(@sTemp,',','.') -- change possible ',' into '.'
--!! print ' */@sTemp, after replace ='+@sTemp
--!! print ' */Desni del=#'+ SubString(@sExpression,@iRightLimit,Len(@sExpression)-@iRightLimit+1) + '#'
-- replace *,/ with result
if @iLeftLimit > 0
set @sExpression = SubString(@sExpression,1,@iLeftLimit) + @sTemp + SubString(@sExpression,@iRightLimit,Len(@sExpression)-@iRightLimit+1)
else
set @sExpression = @sTemp + SubString(@sExpression,@iRightLimit,Len(@sExpression)-@iRightLimit+1)
--!! print ' */@sExpression(Final)=' + @sExpression
end
end -- *,/n found
else
begin -- check for div, mod
set @i = 1
while not (SubString(@sExpression,@i,1) i ('\','|')) and (@i < @iLen)
set @i = @i + 1
if SubString(@sExpression,@i,1) i ('\','|')
begin -- div, mod found
--!! print ' -- there are \,| (div,mod) found at position ' + Str(@i)
set @lOperatorsExist = 1
exec dl_PA_ParserGetArg @sExpression,1,@i,@iLeftLimit output,@rLeft output,@sEr output
exec dl_PA_ParserGetArg @sExpression,0,@i,@iRightLimit output,@rRight output,@sEr output
--!! print ' \,|@rLeft='+Str(@rLeft,25,15)
--!! print ' \,|@rRight='+Str(@rRight,25,15)
--!! print ' \,|@sEr='+@sEr
if @sEr = ''
begin
if SubString(@sExpression,@i,1) = '\'
begin
if @rRight <> 0
set @rResult = cast((@rLeft / @rRight + 0.0) as int)
else
set @rResult = 0
end
else
if SubString(@sExpression,@i,1) = '|'
begin
if @rRight <> 0
set @rResult = cast(@rLeft as int) % cast(@rRight as int)
else
set @rResult = 0
end
set @sTemp = LTrim(Str(@rResult,35,15))
set @sTemp = Replace(@sTemp,',','.') -- change possible ',' into '.'
-- replace div, mod with result
if @iLeftLimit > 0
set @sExpression = SubString(@sExpression,1,@iLeftLimit) + @sTemp + SubString(@sExpression,@iRightLimit,Len(@sExpression)-@iRightLimit+1)
else
set @sExpression = @sTemp + SubString(@sExpression,@iRightLimit,Len(@sExpression)-@iRightLimit+1)
end
end -- div, mod found
else
begin -- check for additions and subtractions
set @i = 1
if SubString(@sExpression,@i,1) = '-'
set @i = @i + 1
while not (SubString(@sExpression,@i,1) i ('+','-')) and (@i < @iLen)
set @i = @i + 1
--!! print ' +,- @i='+cast(@i as varchar)
if SubString(@sExpression,@i,1) i ('+','-')
begin -- +,- found
--!! print ' -- there are +,- found at position ' + Str(@i)
set @lOperatorsExist = 1
exec dl_PA_ParserGetArg @sExpression,1,@i,@iLeftLimit output,@rLeft output,@sEr output
exec dl_PA_ParserGetArg @sExpression,0,@i,@iRightLimit output,@rRight output,@sEr output
--!! print ' +,- @rLeft='+Str(@rLeft,25,15)
--!! print ' +,- @rRight='+Str(@rRight,25,15)
--!! print ' +,- @sEr='+@sEr
if SubString(@sExpression,@i,1) = '+'
set @rResult = @rLeft + @rRight
else
if SubString(@sExpression,@i,1) = '-'
set @rResult = @rLeft - @rRight
set @sTemp = LTrim(Str(@rResult,35,15))
--!! print ' +-@rResult='+Str(@rResult,25,10)
--!! print ' +-@sTemp='+@sTemp
set @sTemp = Replace(@sTemp,',','.') -- change possible ',' into '.'
--!! print ' +-@sTemp, after replace ='+@sTemp
-- replace +,- with result
if @iLeftLimit > 0
set @sExpression = SubString(@sExpression,1,@iLeftLimit) + @sTemp + SubString(@sExpression,@iRightLimit,Len(@sExpression)-@iRightLimit+1)
else
set @sExpression = @sTemp + SubString(@sExpression,@iRightLimit,Len(@sExpression)-@iRightLimit+1)
--!! print ' +-@sExpression=' + @sExpression
end -- +,- found
end -- check for additions and subtractions
end -- check for div, mod
end -- check for multiplications, divisions
if @sEr = ''
begin -- no error su far
if @lOperatorsExist = 1
begin
--!! print ' out for recursion orig. @sExpression='+@sExpression+' @sExpression=' + @sExpression
exec dl_PA_ParseAritNew @sExpression,@rResult output,@sEr output -- parse with value
end
else
begin
--!! print ' out for orig. @sExpression='+@sExpression+' @sExpression=' + @sExpression
set @rResult = Convert(float,@sExpression,2)
end
end -- no error su far
end -- no error on entry
Testni primjeri
-- test case 1
declare
@sExpression varchar(8000),
@mResult money,
@sEr varchar(8000)
set @sExpression='2*3'
set @sEr = ''
--!! print @sExpression
exec dl_PA_ParseAritNew @sExpression, @mResult output,@sEr output
--!! print '-----'
--!! print Str(@mResult,35,15)
--!! print @sEr
--test case 2
create table _TEST_PARSER
(
FORMULA varchar(300),
TEST money,
ER varchar(500),
REZULTAT money
)
declare
@sExpression varchar(8000),
@mTest float,
@mResult float,
@i int,
@iBrackets int,
@sEr varchar(8000)
set nocount on
declare crKurzor cursor local fast_forward for
select FORMULA,TEST
from _TEST_PARSER
open crKurzor
fetch next from crKurzor into @sExpression,@mTest
while @@fetch_status = 0
while @@fetch_status = 0
begin
-- check for bracket balance
print '--------------------------------------------------------'
print ' Testing: ' + @sExpression
print ' Expected result = ' + Str(@mTest)
set @i = 1
set @iBrackets = 0
while (@i <= Len(@sExpression)) -- check whole expression
begin
if SubString(@sExpression,@i,1) = ')'
set @iBrackets = @iBrackets-1
if SubString(@sExpression,@i,1) = '('
set @iBrackets = @iBrackets+1
set @i = @i + 1
end
if @iBrackets <> 0
set @sEr = 'Unbalanced brackets i ' + @sExpression
else
begin
set @sEr = ''
exec dl_PA_ParseAritNew @sExpression, @mResult output,@sEr output
end
print ' Calculated result = ' + Str(@mResult)
if (@mResult = @mTest)
print ' OK'
else
print ' Error (@mResult - @mTest) = ' + Str(@mResult - @mTest)
print '--------------------------------------------------------'
update _TEST_PARSER
set REZULTAT = @mResult,
ER = @sEr
where LTrim(RTrim(FORMULA)) = LTrim(RTrim(@sExpression))
fetch next from crKurzor into @sExpression,@mTest
end
close crKurzor
deallocate crKurzor
select LTrim(RTrim(FORMULA)) as 'Formula',TEST,REZULTAT,LTrim(RTrim(ER)) as 'ErrorMsg'
from _TEST_PARSER
where (TEST <> REZULTAT) or (TEST is null)