MySQL存储过程学习记录

 -- 创建存储过程
-- DELIMITER //
CREATE PROCEDURE pro_first()
BEGIN
	select NOW();
END -- //
-- DELIMITER ;
-- 删除存储过程
DROP PROCEDURE pro_first;
-- 查看指定存储过程
SHOW CREATE PROCEDURE pro_first;
-- 查看所有存储过程
SHOW PROCEDURE STATUS;
-- 调用存储过程
CALL pro_first();  

-- 练习
create procedure pro_practise()
BEGIN


	select 1+3 from dual;
END

call pro_practise();

-- 变量
CREATE procedure pro_variable()
BEGIN
	-- 声明局部变量
  DECLARE num1 int;
	DECLARE username VARCHAR(20) DEFAULT '张三';

	select num1, username;
END

CALL pro_variable();

-- 变量赋值
CREATE PROCEDURE pro_variable2()
BEGIN
	-- 声明局部变量
  DECLARE num1 int;
  DECLARE num2 int;
	DECLARE result int;

	-- 变量赋值
	SET num1 = 10;
  SET num2 := 15;
	SET result = num1 + num2;

	select result;
END

CALL pro_variable2();

-- 定义用户变量（注意：使用用户变量的时候，不需要声明数据类型）
set @userVar := '我是用户变量';

CREATE PROCEDURE pro_testuservar()
BEGIN
	set @userVar = '用户变量值改了';
	select @userVar;
END
DROP PROCEDURE pro_testuservar;
CALL pro_testuservar();

CREATE PROCEDURE pro_testuservar2()
BEGIN
	select @userVar;
END
CALL pro_testuservar2();

-- select into 语句（注意：使用时要保证select语句查回的数据只有一条）
CREATE PROCEDURE pro_selectinto()
BEGIN
	DECLARE vcity VARCHAR(40);
	select city into vcity from extend_mobile where pre = '1300000';

	select vcity;
END
DROP PROCEDURE pro_selectinto;
CALL pro_selectinto();

-- 练习
create PROCEDURE pro_selectinto_practise()
BEGIN
	DECLARE vpre int;
	DECLARE vprovice VARCHAR(40);
	DECLARE vcity VARCHAR(40);

	select pre, provice, city into vpre, vprovice, vcity from extend_mobile where pre = '1300000';

	select vpre, vprovice, vcity;
END

CALL pro_selectinto_practise();

-- 参数 in （不写，默认是in  输入参数）
CREATE procedure pro_param(in p_pre int)
BEGIN
	DECLARE vcity VARCHAR(40);

	SELECT city into vcity from extend_mobile where pre = p_pre;

	select vcity;
END

CALL pro_param('1300001');

-- 参数out
set @provice = '';
create PROCEDURE pro_param_out(out p_out VARCHAR(40), in p_in INT)
BEGIN
	select provice into p_out from extend_mobile where pre = p_in;
END

CALL pro_param_out(@provice, '1300002');
select @provice;

-- 参数inout
CREATE PROCEDURE pro_param_inout(INOUT num int)
BEGIN
	SET num = num * 5;
END
set @userNum = 10;
select @userNum;
CALL pro_param_inout(@userNum);

-- if语句1
CREATE PROCEDURE pro_if(in p_pre int)
BEGIN
	DECLARE vcode int;

	select code into vcode from extend_mobile where pre = p_pre;

	IF vcode = 10 THEN
		select 1;
	ELSE
		select 2;
	END IF;
END

CALL pro_if('1300001');
-- if语句2
CREATE PROCEDURE pro_if2(in p_pre int)
BEGIN
	DECLARE vcity VARCHAR(40);

	select city into vcity from extend_mobile where pre = p_pre;

	IF vcity = '北京' THEN
			select 1;
  ELSEIF vcity = '常州' THEN
      select 2;
	ELSE 
			select 3;
	end if;
END

CALL pro_if2('1300002');

-- case when 语句
CREATE PROCEDURE pro_case_when(in p_pre int)
BEGIN
	DECLARE vcity VARCHAR(40);

	select city into vcity from extend_mobile where pre = p_pre;

	CASE vcity
	when '北京' THEN
			select 1;
	when '常州' THEN
			select 2;
  ELSE
			select 3;
  END CASE;
END

CALL pro_case_when('1300002');

CREATE PROCEDURE pro_case_when2(in p_pre int)
BEGIN
	DECLARE vcity VARCHAR(40);

	select city into vcity from extend_mobile where pre = p_pre;

	CASE
	when vcity = '北京' THEN
			select 1;
	when vcity = '常州' THEN
			select 2;
  ELSE
			select 3;
  END CASE;
END

CALL pro_case_when2('1300002');

-- case when 用于普通查询语句
select id, name, case sex when 0 then '女' 
	when 1 then '男'
	else '未知' end  as gender
 from  t_person;

-- while语句
create procedure pro_while()
BEGIN
	DECLARE i INT DEFAULT 1;
	DECLARE sum INT DEFAULT 0;

	WHILE i < 11  DO
		set sum = sum + i;
		set i = i + 1;
	END WHILE;

	select sum;
END

CALL pro_while();

-- REPEAT语句
create PROCEDURE pro_repeat()
BEGIN
	DECLARE i INT DEFAULT 1;
	DECLARE sum INT DEFAULT 0;

	REPEAT
		SET sum = sum + i;
		SET i = i + 1;
	until i > 10 END REPEAT;

	select sum;
END

CALL pro_repeat();

-- LOOP语句
CREATE PROCEDURE pro_loop()
BEGIN
	DECLARE i INT DEFAULT 1;
	DECLARE sum INT DEFAULT 0;

	looplabel: LOOP
		SET sum = sum + i;
		SET i = i + 1;

		IF i > 10 THEN
			 LEAVE looplabel; -- 跳出循环
		END IF;

	END LOOP looplabel;

	select sum;

END

CALL pro_loop();

-- JDBC调用存储过程
CREATE PROCEDURE pro_jdbc(in p_pre int, OUT p_city VARCHAR(40))
BEGIN
	SELECT city into p_city from extend_mobile where pre = p_pre;
END
set @uCity = '';
CALL pro_jdbc('1300000', @uCity);
SELECT @uCity;
drop PROCEDURE pro_jdbc

--  登录业务 p_flag: 0 失败  1 成功
CREATE PROCEDURE pro_login(in p_username VARCHAR(255), in p_password VARCHAR(255), out p_flag INT)
BEGIN
	DECLARE count int;

	SELECT count(*) into count FROM t_user where f_name = p_username and f_password = p_password;

	IF count = 1 THEN
		SET p_flag = 1;
	ELSE
		SET p_flag = 0;
	END IF;
END