Sybase(sqlanywhere)比较应用版本大小数据库函数

前置条件：版本以"."进行分割。 www.zzzyk.com

[sql]

ALTER FUNCTION "SMM"."fn_justsy_zh_compare_app_version"(

IN p_original VARCHAR(50)

,IN p_compare VARCHAR(50)

)

RETURNS INTEGER

DETERMINISTIC

-- 比较应用版本、

-- p_original > p_compare return -1

-- p_original = p_compare return 0

-- p_original > p_compare return 1

BEGIN

DECLARE lv_comp INTEGER;

DECLARE lv_ori_index INTEGER;

DECLARE lv_com_index INTEGER;

DECLARE lv_sort_key INTEGER;

DECLARE lv_sort_index INTEGER;

-- DECLARE lv_last_ori VARCHAR(20);

-- DECLARE lv_last_com VARCHAR(20);

-- DECLARE lv_temp_ori VARCHAR(20) ;

-- DECLARE lv_temp_com VARCHAR(20) ;

-- DECLARE lv_temp_sort INTEGER;

DECLARE LOCAL TEMPORARY TABLE SplitTable ( SortKey INTEGER DEFAULT AUTOINCREMENT, OriVal VARCHAR(20), ComVal VARCHAR(20), PRIMARY KEY(SortKey));

-------- Init Value-------

SET lv_comp = -1 ;

SET p_original = TRIM(p_original) ;

SET p_compare = TRIM(p_compare) ;

SET lv_sort_key = 1 ;

-------- Init Value-------

-- 拆分出p_original变量中字符串

SET lv_ori_index = CHARINDEX('.', p_original) ;

WHILE lv_ori_index >= 1 LOOP

INSERT INTO SplitTable( OriVal) VALUES (LEFT(p_original,lv_ori_index -1)) ;

SET p_original = SUBSTR(p_original,lv_ori_index + 1,LENGTH(p_original)-lv_ori_index) ;

SET lv_ori_index = CHARINDEX('.', p_original) ;

END LOOP ;

INSERT INTO SplitTable(OriVal) VALUES (p_original) ;

-- 拆分p_compare变量中字符串

SET lv_com_index = CHARINDEX('.', p_compare) ;

WHILE lv_com_index >= 1 LOOP

INSERT INTO SplitTable(SortKey, ComVal) ON EXISTING UPDATE VALUES(lv_sort_key,LEFT(p_compare,lv_com_index -1)) ;

SET p_compare = SUBSTR(p_compare,lv_com_index + 1,LENGTH(p_compare)-lv_com_index) ;

SET lv_com_index = CHARINDEX('.', p_compare) ;

SET lv_sort_key = 1 + lv_sort_key ;

END LOOP ;

INSERT INTO SplitTable(SortKey, ComVal) ON EXISTING UPDATE VALUES(lv_sort_key, p_compare) ;

-- FOR compare_temp AS compare_temp_cursor NO SCROLL CURSOR FOR

-- SELECT OriVal, ComVal, SortKey FROM SplitTable ORDER BY SortKey FOR READ ONLY

-- DO

-- INSERT INTO dba.ml_temp(PFName,PFXMLValue) VALUES('fn_justsy_zh_compare_app_version',OriVal+'---'+ComVal+'--'+STRING(SortKey)) ;

-- END FOR ;

-- 比较

compare_loop_for:

FOR compare_loop AS compare_loop_cursor NO SCROLL CURSOR FOR

SELECT OriVal, ComVal, SortKey FROM SplitTable ORDER BY SortKey FOR READ ONLY

IF(OriVal IS NOT NULL AND ComVal IS NOT NULL) THEN

IF(OriVal > ComVal) THEN

SET lv_comp = 1 ;

LEAVE compare_loop_for ;

ELSEIF(OriVal < ComVal) THEN

SET lv_comp = -1 ;

LEAVE compare_loop_for ;

ENDIF ;

-- 版本相同的情况

IF(lv_sort_index = SortKey AND OriVal = ComVal) THEN

SET lv_comp = 0 ;

ENDIF ;

ELSEIF(OriVal IS NULL ) THEN

SET lv_comp = -1 ;

LEAVE compare_loop_for ;

ELSEIF(ComVal IS NULL) THEN

SET lv_comp = 1 ;

LEAVE compare_loop_for ;

END IF ;

SET lv_sort_index = lv_sort_index + 1 ;

END FOR ;

RETURN lv_comp;

END

调用：

[sql]

select fn_justsy_zh_compare_app_version('c.26.89.1','b.25.89.1') ;