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;