495   MySQL

1,查看

mysql> show procedure status;
+------+---------+-----------+----------------+
| Db   | Name    | Type      | Definer        |
+------+---------+-----------+----------------+
| test | pp_test | PROCEDURE | root@localhost |
+------+---------+-----------+----------------+
1 row in set (0.00 sec)

2,删除

mysql> use test;
Database changed
mysql> drop procedure pp_test;
Query OK, 0 rows affected (0.00 sec)

3,创建

delimiter //
create procedure `test_function`(max_num int(11))
begin
	declare i int default 0;
	declare rand_num int;
	while i < max_num do
		select cast(rand()*100 as unsigned) into rand_num;
		insert into test_table(`number`) values(rand_num);
		set i = i +1;
	end while;
end
//
delimiter ;

其中
delimiter //
为了让MySQL把SQL结束符号;临时更改为//,避免在创建procedure语句时出现编译错误,创建完毕要改回来

4,调用

mysql> call test_function(500);
Query OK, 1 row affected (0.15 sec)



Leave a Reply

Your email address will not be published. Required fields are marked *