MySQL存储过程

-- 创建存储过程
delimiter $$
create procedure proc01()
begin
	select emp_no,birth_date from employees;
end $$
delimiter ;
-- 调用存储过程
call proc01()

-- 声明变量
delimiter $$
create procedure proc02()
begin
			# 声明变量
			declare var_name01 varchar(20) DEFAULT "aaaa";
			set var_name01 = 'zhangsan';
			select var_name01;
end $$
delimiter ;
call proc02

-- 传入参数值
delimiter $$
create procedure proc03(in id int)
begin
		select emp_no,birth_date,last_name from employees where emp_no = id;
end $$
delimiter ;
call proc03(10001)

# 存储过程传参-out
delimiter $$
create procedure proc04(in id int, out out_ename varchar(20))
begin
		select last_name into out_ename from employees where emp_no = id;
end $$
delimiter ;

CALL proc04(10001,@out_ename);
select @out_ename;

# while循环
delimiter $$
create procedure proc05(in insertCount  int)
begin
		declare i int default 1;  -- 设置循环的增量
		label:while i<insertCount  do
		insert into mytbl (id,name) VALUES (i,concat('user-',i));
		set i=i+1;
		end while label;
end $$
delimiter ;
call proc05(100000)