存储过程循环变量与字符连接
Java代码
- mysql> DELIMITER $$
- mysql> drop PROCEDURE sp_test_switch$$
- Query OK, 0 rows affected (0.01 sec)
- mysql> CREATE PROCEDURE sp_test_switch()
- -> BEGIN
- -> declare a int;
- -> declare b varchar(5000);
- -> set a=1;
- -> set b='';
- -> while a<10 do
- -> set b = concat(b,',',a);
- -> set a=a+1;
- -> end while;
- -> select b;
- -> END
- -> $$
- Query OK, 0 rows affected (0.00 sec)
- mysql> DELIMITER ;
- mysql>
- mysql> call sp_test_switch();
- +--------------------+
- | b |
- +--------------------+
- | ,1,2,3,4,5,6,7,8,9 |
- +--------------------+
- 1 row in set (0.00 sec)
- Query OK, 0 rows affected (0.00 sec)
- mysql>
MySQL存储过程的变量定义：declare和直接set @var什么区别？
我的理解是, declare定义的是局部变量, 只能用在存储过程或函数中, 其有效区间就是存储过程或函数中声明该变量的begin end区间.而@var属于用户变量(自己声明的), 其有效区间就是该session, 你既可以在存储过程或函数中使用, 也可以在自己写的SQL语句中使用.还有@不需要声明类型，declare必须指定类型
Java代码
- CREATE PROCEDURE sp_starnight_cbj()
- BEGIN
- DECLARE A INT DEFAULT 1;
- DECLARE B VARCHAR(5000) DEFAULT '';
- WHILE A<10 DO
- SET B = CONCAT(B,',',A);
- SET A=A+1;
- END WHILE;
- SELECT B;
- END;
应用：所有分表
Java代码
- DROP PROCEDURE IF EXISTS `merage_branch_table`;
- CREATE PROCEDURE `merage_branch_table`(
- IN p_table_name VARCHAR(200),
- IN p_branch_size INT,
- in p_lpad INT
- )
- BEGIN
- DECLARE m_begin_row INT DEFAULT 0;
- SET @m_merage_sql = '';
- WHILE m_begin_row<p_branch_size DO
- IF(m_begin_row=p_branch_size-1)
- THEN SET @m_merage_sql = CONCAT(@m_merage_sql, 'SELECT * FROM ', p_table_name, '_', LPAD(m_begin_row, p_lpad, 0));
- ELSE SET @m_merage_sql = CONCAT(@m_merage_sql, 'SELECT * FROM ', p_table_name, '_', LPAD(m_begin_row, p_lpad, 0),' UNION ALL ');
- END IF;
- SET m_begin_row = m_begin_row+1;
- END WHILE;
- PREPARE main_stmt FROM @m_merage_sql;
- EXECUTE main_stmt;
- END;
