mysql function & procedure 学习使用小结:
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 就保存了查询结果。
参考链接:http://jonllen.javaeye.com/blog/370343
http://topic.csdn.net/u/20100402/00/b13c3d37-2f06-48d2-99c8-697f8a75e441.html
http://hi.baidu.com/annleecn/blog/item/794e3efbf2525a809e514696.html
附上一个例子:
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 ;
|
多思考,多总结,快乐成长!