通用合並表存儲過程循環變量與字符連接 存儲過程循環變量與字符連接 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_view = CONCAT('CREATE VIEW ', p_table_name, ' AS '); WHILE m_begin_row<p_branch_size DO IF(m_begin_row=p_branch_size-1) THEN SET @m_merage_sql_view = CONCAT(@m_merage_sql_view, 'SELECT * FROM ', p_table_name, '_', LPAD(m_begin_row, p_lpad, 0)); ELSE SET @m_merage_sql_view = CONCAT(@m_merage_sql_view, '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_view; EXECUTE main_stmt; END; 所有分表的視圖
