黑猴子的家：mysql 存储过程 - dos 实操

1、创建存储过程，实现 查询beauty表的大于3号id的记录

C:\> mysql -uroot -proot
mysql> use girls;
# 创建
mysql> DELIMITER $$
mysql> CREATE PROCEDURE mypro1()
->BEGIN
    ->SELECT * FROM beauty WHERE id>3;
->END $$

mysql> call mypro1()$$   #调用
mysql> set names gbs$$   #显示乱码问题
mysql> call mypro1()$$   #调用

2、根据女神名称，查询男神信息

C:\> mysql -uroot –proot
mysql> use girls;
#创建
mysql> DELIMITER $$
mysql> CREATE PROCEDURE mypro2(IN bname VARCHAR(20))
-> BEGIN
->SELECT bo.* FROM boys bo
    ->JOIN beauty b ON bo.id=b.boyfriend_id
    ->WHERE b.name=bname;
->
->END $$

mysql> call mypro2('小昭')$$   #调用

3、根据姓名查询用户

C:\> mysql -uroot –proot
mysql> use girls;
#创建
mysql> DELIMITER $$
mysql> CREATE PROCEDURE mypro3(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
-> BEGIN
    ->SELECT COUNT(*) FROM admin 
    ->WHERE admin.username=username AND admin.password=PASSWORD;
->
->END $$

mysql> call mypro3(‘lucy’,’000’)$$   #调用

4、创建out类型参数的存储过程

C:\> mysql -uroot –proot
mysql> use girls;
#创建
mysql> DELIMITER $$
mysql> CREATE PROCEDURE mypro4(
->IN username VARCHAR(20),
->IN PASSWORD VARCHAR(20),
->OUT result INT)
->BEGIN
    ->SELECT COUNT(*) INTO result FROM admin 
    ->WHERE admin.username=username AND admin.password=PASSWORD;
    ->
->END $$

mysql > select @a $$
mysql > call mypro4('','',@a) $$ #调用
mysql > select @a $$

5、创建out类型参数的存储过程

C:\> mysql -uroot –proot
mysql> use girls;
#创建
mysql> DELIMITER $$
mysql> CREATE PROCEDURE mypro5(IN a INT,IN b INT,OUT c INT,OUT d INT)
-> BEGIN
    -> SET c=a*2;
    -> SET d=b*2;
->
->END $$

mysql> call mypro5(10,20,@c,@d) $$  #调用
mysql> select @c,@d $$

6、创建inout类型参数的存储过程

C:\> mysql -uroot –proot
mysql> use girls;
//创建
mysql> DELIMITER $$
mysql> set @age=12 $$
mysql> CREATE PROCEDURE mypro6(INOUT age INT)
->BEGIN
    -> SET age=age*3;
->END $$

mysql> call mypro6(@age) $$  #调用
mysql >select @age $$

7、删除存储过程

C:\> mysql -uroot –proot
mysql> use girls;
mysql> DELIMITER ;
mysql> DROP PROCEDURE mypro6 ;

8、查看存储过程

C:\> mysql -uroot –proot
mysql> use girls;
mysql> DELIMITER ;
mysql> SHOW CREATE PROCEDURE mypro1;
mysql> SHOW CREATE PROCEDURE mypro1\G;