1、动态sql, 即动态参数:
在存储过程中,想要直接用表名变量做参数,动态执行sql,不能直接写
1
2
3
4
5
6
7
|
create procedure ( $tableName char (20) )
BEGIN
select * from tableName;
END
|
mysql 不支持表名作为变量,这样会直接将变量名“tableName”作为表名去查询,解决的方法是:
1
2
3
4
5
6
7
8
9
10
11
|
BEGIN
SET @sql = concat( 'select * from ' , $tableName);
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END ;
|
2 得到动态sql, select查询的结果:
可以直接 在sql 语句中 得到, "select * into @var" 就行了,后边就可以直接运用该变量了。
例子:
1
2
3
4
5
6
7
|
SET @sql = concat( 'select max(id) into @max_id from ' , $tableName);
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
|
此时,@max_id 就保存了查询结果。
附上一个例子:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
DROP PROCEDURE IF EXISTS get_service_id;
DELIMITER $$
CREATE PROCEDURE get_service_id
(
IN $tableName varchar (25),
IN $beginDate varchar (25)
)
SET @sql = concat( 'select id into @mainId from ' , $tableName , ' where date_time>=\"' , $beginDate, '\" order by date_time limit 1' );
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END ;
$$
DELIMITER ;
|