mysql自定义函数的一个例子

create function testdate(pbirthday date)
returns integer
begin
return year(now()) - year(pbirthday);
end;

select  testdate(birthday)<40 from user;

set global log_bin_trust_function_creators = 1;  
DROP FUNCTION IF EXISTS rand_string;  
CREATE FUNCTION rand_string(n INT)  
RETURNS VARCHAR(255)  
BEGIN  
    DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';  
    DECLARE return_str varchar(255) DEFAULT '';  
    DECLARE i INT DEFAULT 0;  
    WHILE i < n DO  
        SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1));  
        SET i = i +1;  
    END WHILE;  
    RETURN return_str;  
END ;

select rand_string(32);

set global log_bin_trust_function_creators = 1;  
DROP FUNCTION IF EXISTS imeiToUerId;
create function imeiToUerId(myiImei varchar(30))
returns integer
begin
DECLARE result integer DEFAULT 0;
select user_id into result from user_users where imei=myiImei LIMIT 1;
return result;
end;

set global log_bin_trust_function_creators = 1; 
DROP FUNCTION IF EXISTS subValue;
create function subValue(p_str VARCHAR(10000), p_begin_str VARCHAR(50), p_end_str VARCHAR(50))
returns VARCHAR(255)
begin
DECLARE p_result varchar(255) DEFAULT null;

DECLARE m_len INT DEFAULT 0;
DECLARE m_index INT DEFAULT 0;
select locate(p_begin_str,p_str)+char_length(p_begin_str) into m_index;
select locate(p_end_str,p_str,m_index) into m_len;
if(m_len=0) then
select SUBSTRING(p_str,m_index,char_length(p_str)) INTO p_result;
end if;

if(m_len>0) then
select SUBSTRING(p_str,m_index,m_len-m_index) INTO p_result;
end if;

select CONCAT(p_begin_str,p_result) into p_result;

return p_result;
end;