巧用DataWindwo返回计算表达式的值

一、返回比较表达式如60 < 70的函数

函数f_exp_compare

//====================================================================

// 函数: f_exp_compare

//--------------------------------------------------------------------

// 描述: 返回比较表达式的结果

//--------------------------------------------------------------------

// 参数:

//     value    string    as_str    表达式，如 60 < 70

//--------------------------------------------------------------------

// 返回值:  string   比较表达试的结果 1为真，0为假，-1为表达式错误

//====================================================================

String ls_Ret

DataStore lds_Evaluate

lds_Evaluate = CREATE DataStore

lds_Evaluate.Create('release 8;~r~ntable()')

ls_Ret = lds_Evaluate.Describe("Evaluate('" + "if(" + as_str + ", 1, 0)" + "', 1)")

DESTROY lds_Evaluate

IF ls_Ret = '!' THEN ls_Ret = '-1'

RETURN ls_Ret

 

 

示例：

String ls_Exp, ls_Ret

 

ls_Exp = '60 < 70'

ls_Ret = f_exp_compare(ls_Exp)

MessageBox('', ls_Exp + ' --> ' + ls_Ret )

 

 

二：返回计算表达式如2 * (3+5) + 2^3的函数

//====================================================================

// 函数: f_exp_ret

//--------------------------------------------------------------------

// 描述: 返回计算表达式的值

//--------------------------------------------------------------------

// 参数:

//     value    string    as_str    计算表达式，如 2 * (3+5) + 2^3

//--------------------------------------------------------------------

// 返回值:  string   计算表达式的结果值，如果表达式不正确，返回'FALSE'

//====================================================================

String ls_Ret

DataStore lds_Evaluate

lds_Evaluate = CREATE DataStore

lds_Evaluate.Create('release 8;~r~ntable()')

ls_Ret = lds_Evaluate.Describe("Evaluate('" + as_str + "', 1)")

DESTROY lds_Evaluate

IF ls_Ret = '' OR ls_Ret = '!' THEN ls_Ret = 'FALSE'

RETURN ls_Ret

 

 

示例：

String ls_Exp, ls_Ret

 

ls_Exp = '60 * ( 1 + 3 ) + 2^3'

ls_Ret = f_exp_ret(ls_Exp)

MessageBox('', ls_Exp + ' --> ' + ls_Ret )

 

 

三、下面是收藏的在SQL中返回计算表达式的函数，但只能算加、减、乘、除，不能算幂：

CREATE FUNCTION dbo.GetExp(@pstrExpress AS VARCHAR(8000))

RETURNS DECIMAL(18,6) AS

BEGIN

DECLARE @i INT,@j INT

DECLARE @c1 CHAR(1),@c2 CHAR(1),@c VARCHAR(100)

DECLARE @v1 DECIMAL(18,6),@v2 DECIMAL(18,6),@v DECIMAL(18,6)

DECLARE @t TABLE(ID INT IDENTITY(1,1),s VARCHAR(100))

DECLARE @s TABLE(ID INT IDENTITY(1,1),s VARCHAR(100))

DECLARE @sv TABLE(ID INT IDENTITY(1,1),v DECIMAL(18,6))

 

SET @pstrExpress = REPLACE(@pstrExpress,' ','')

SELECT @i = 0,@j = LEN(@pstrExpress),@c2 = '',@c = ''

WHILE @i<@j

BEGIN

 SELECT @c1 = @c2,@i = @i+1

 SELECT @c2 = SUBSTRING(@pstrExpress,@i,1)

 IF CHARINDEX(@c2,'.0123456789') > 0 or (@c2 = '-' and @c1 IN('','*','-','+','/','('))

 BEGIN   SELECT @c = @c + @c2   CONTINUE  END

 IF @c <> ''  BEGIN INSERT @t(s)  SELECT @c SELECT @c = '' END

 IF CHARINDEX(@c2,')')>0

 BEGIN

   INSERT @t(s)  SELECT s FROM @s WHERE ID > ISNULL((SELECT MAX(ID) FROM @s WHERE s IN('(')),0) ORDER BY ID DESC

   DELETE @s WHERE ID >= ISNULL((SELECT MAX(ID) FROM @s WHERE s IN('(')),0)

   CONTINUE

 END

 IF CHARINDEX(@c2,'+-)')>0

 BEGIN

   INSERT @t(s)  SELECT s FROM @s WHERE ID > ISNULL((SELECT MAX(ID) FROM @s WHERE s IN('(')),0) ORDER BY ID DESC

   DELETE @s WHERE ID > ISNULL((SELECT MAX(ID) FROM @s WHERE s IN('(')),0)

   IF @c2 <> ')' INSERT @s(s) SELECT @c2

   CONTINUE

 END

 IF CHARINDEX(@c2,'*/')>0

 BEGIN

   INSERT @t(s)  SELECT s FROM @s WHERE ID > ISNULL((SELECT MAX(ID) FROM @s WHERE s IN('(','+','-')),0) ORDER BY ID DESC

   DELETE @s WHERE ID > ISNULL((SELECT MAX(ID) FROM @s WHERE s IN('(','+','-')),0)

   INSERT  @s SELECT @c2

   CONTINUE

 END

 IF CHARINDEX(@c2,'(')>0 INSERT  @s SELECT @c2

END

IF @c <> '' INSERT @t(s) SELECT @c

INSERT @t(s)  SELECT s FROM @s ORDER BY ID DESC

SELECT @i = 0,@j = MAX(ID) FROM @t

WHILE @i < @j

BEGIN

 SELECT @i = @i + 1

 SELECT @c = s FROM @t WHERE ID = @i

 IF @c = '(' CONTINUE

 IF @c NOT IN('*','-','+','/')  BEGIN  INSERT @sv(v) SELECT CONVERT(float,@c) CONTINUE END

 SELECT @v2 = v FROM @sv  DELETE @sv  WHERE ID = (SELECT MAX(ID) FROM @sv)

 SELECT @v1 = v FROM @sv  DELETE @sv  WHERE ID = (SELECT MAX(ID) FROM @sv)

 SELECT @v = CASE @c WHEN '+' THEN @v1 + @v2 WHEN '-' THEN @v1 - @v2

                     WHEN '*' THEN @v1 * @v2 WHEN '/' THEN @v1 / @v2 END

 INSERT @sv(v) SELECT @v

END

SELECT @v = v FROM @sv

RETURN @v

END

 

示例：

SELECT dbo.GetExp('60 * ( 1 + 3 )')

/*结果

--------------------

240.000000

 

（所影响的行数为 1 行）

*/