Sybase(sqlanywhere)比较应用版本大小数据库函数
前置条件：版本以"."进行分割。
[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
DO
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') ;
，