RWAServer/DBScript/李庚谱/003-FUNCTION.sql

delimiter //
drop function if exists getLastTerm//
create function getLastTerm()
    RETURNS varchar(20)
    DETERMINISTIC
BEGIN
    DECLARE str varchar(20);
    select nounvalue into str from sys_noun where NOUNITEM = 'TERM';
    RETURN str;
END //


DROP FUNCTION IF EXISTS `nextval`;
delimiter //
CREATE FUNCTION nextval(sequence_name varchar(64)) RETURNS int(11)
BEGIN
    declare current integer;
    set current = 0;
    select t.value into current from t_sequence t where t.sequence_name = sequence_name for update;
    update t_sequence t set t.value = t.value + 1 where t.sequence_name = sequence_name;
    set current = current + 1;
    return current;
end//


-- （匹配两个以逗号分隔的字符串是否存在相同的字符串）函数 用于发布信息查询
DROP FUNCTION IF EXISTS `func_haveSameContent`;
CREATE FUNCTION `func_haveSameContent` (setA varchar(255),setB varchar(255)) RETURNS int(1)
BEGIN
    DECLARE idx INT DEFAULT 0 ;
    DECLARE len INT DEFAULT 0;
    DECLARE llen INT DEFAULT 0;
    DECLARE clen INT DEFAULT 0;
    DECLARE tmpStr varchar(255);
    DECLARE curt varchar(255);
    SET len = LENGTH(setB);
    WHILE idx < len DO
        SET idx = idx + 1;
        SET tmpStr = SUBSTRING_INDEX(setB,",",idx);
        SET clen = LENGTH(tmpStr);
        IF idx = 1 THEN SET curt = tmpStr;
        ELSE SET curt = SUBSTRING(setB,llen+2,clen-llen-1);
        END IF;
        IF FIND_IN_SET(curt,setA) > 0 THEN RETURN 1;
        END IF;
        IF clen <= llen THEN RETURN 0;
        END IF;
        SET llen = clen;
    END WHILE;
    RETURN 0;
end//

delimiter ;