MYSQL PROCEDURE 测试用例

/**
 * 查询俱协信息
 */
DROP PROCEDURE IF EXISTS `get_club_list`;
DELIMITER $$
CREATE PROCEDURE `get_club_list`(
    -- 页码
    IN i_page_num int unsigned,
    -- 一页数量
    IN i_page_size int unsigned
)
BEGIN
    -- 定义变量
    DECLARE v_num int unsigned DEFAULT 0;
    SET v_num = i_page_num*i_page_size;
    SELECT * FROM (
        SELECT 
            `t`.*,
            `club_area`.`name` AS `area_name`,
            `club_city`.`name` AS `city_name`
        FROM (
            SELECT * FROM `club_record` WHERE `status` = '0' ORDER BY `createtime` DESC LIMIT v_num,i_page_size
        ) AS `t` LEFT JOIN `club_area` ON (
            `t`.`area_id` = `club_area`.`id`
        ) LEFT JOIN `club_city` ON (
            `t`.`city_id` = `club_city`.`id`
        )
    ) AS `tt` ORDER BY `createtime` DESC;
END $$
DELIMITER ;

CALL get_club_list(0,10);

/**
 * 测试-删除
 */
DROP PROCEDURE IF EXISTS `test_del`;
DELIMITER $$
CREATE PROCEDURE `test_del`(
    -- 删除一组id
    IN i_ids varchar(255)
)
BEGIN
    -- 定义变量
    DECLARE v_i int unsigned DEFAULT 1;
    DECLARE v_num int unsigned DEFAULT 0;
    DECLARE v_id int unsigned DEFAULT 0;
    SET v_num = LENGTH(i_ids) - LENGTH( REPLACE(i_ids,',','') ) + 1;

    WHILE v_i <= v_num DO
        SET v_id = SUBSTRING_INDEX( SUBSTRING_INDEX(i_ids,',',v_i),',',-1 );
        DELETE FROM `club_area_copy` WHERE `id` = v_id;
        DELETE FROM `club_city_copy` WHERE `area_id` = v_id;
        -- SELECT SUBSTRING_INDEX( SUBSTRING_INDEX(i_ids,',',v_i),',',-1 );
        SET v_i = v_i+1;
    END WHILE;
END $$
DELIMITER ;

 

 

MYSQL函数：

1. SUBSTRING_INDEX(str, delim, count)

str: 要处理的字符串

delim: 分割符

count: 计数 如果为正数，则从左开始数，如果为负数，则从右开始数

-- SET @str = 'www.baidu.com';

SELECT substring_index(@str,'.',1); # www

SELECT substring_index(@str,'.',2); # www.baidu

SELECT substring_index(@str,'.',-1); # com

SELECT substring_index(@str,'.',-2); # baidu.com

SELECT substring_index( substring_index(@str,'.',-2),'.',1 ); # baidu

SELECT LENGTH(@str); # 13

SELECT LENGTH( REPLACE(@str,'.','') ); # 11

-- 得到结果数
SELECT LENGTH(@str) - LENGTH( REPLACE(@str,'.','') ) + 1; # 3