存储过程-插入数据

-- 删除存储过程
drop procedure test_insert;
-- 向数据库表中account，tb_user批量插入数据
create procedure test_insert()
begin
	declare n int default 1;
	while n<=10 DO
		INSERT INTO account
		(name, money)
		VALUES('李四', 40);
	
		INSERT INTO tb_user
		(username, password)
		VALUES(n, '123');

	set n=n+1;
end while;
end;

```sql
CREATE TABLE `account1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `money` double(10,2) DEFAULT NULL,
  `address` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
)

CREATE UNIQUE INDEX idx_add ON account1 (address);
drop pr
ocedure test_insert1;

CREATE PROCEDURE test_insert1()
BEGIN
    DECLARE n INT DEFAULT 1;
    WHILE n <= 10 DO
        INSERT INTO account1
        (name, money, address)
        VALUES('李四2', 40,SUBSTRING(UUID(), 1, 10));        
        SET n = n + 1;
    END WHILE;
END

call test_insert1();

call test_insert();

```sql
select * from information_schema.ROUTINES ;
show create procedure p1;
drop procedure p8;
show session variables;
show session variables like 'auto%';
show global variables like 'auto%';
select @@global.autocommit;
select @@session.autocommit;
set session autocommit=0;


set @myname :='red';
select @myname;

create procedure p1()
begin
	declare stu_count int default 0;
	select count(*) into stu_count from stu s ;
    select stu_count;
end

call p1();



create procedure p3()
begin
	declare score int default 68;
	declare result varchar(10);
	if score>=80 then
		set result :='优秀';
	elseif score >=60 then
		set result :='及格';
	else
		set result :='不及格';
	end if;

	select result ;
end

call p3();


create procedure p4(in score int, out result varchar(10))
begin

	if score>=80 then
		set result :='优秀';
	elseif score >=60 then
		set result :='及格';
	else
		set result :='不及格';
	end if;
end;
call p4(87,@result);
select @result;


create procedure p5(inout score double)
begin
	set score :=score*0.5;
end;

set @score =160;
call p5(@score);
select @score;


create procedure p6(in month int)
begin
	declare result varchar(10);
	case
		when month>=1 and month<=3 then
		set result:='第一季度';
	when month>=4 and month<=6 then
		set result:='第二季度';
	when month>=7 and month<=9 then
		set result:='第三季度';
	when month>=10 and month<=12 then
		set result:='第四季度';
	else
	set result:='非法参数';
	end case;
select concat('您输入的月份为：',month,',   所属的月份为：',result); 
end;
call p6(10);


create procedure p7(in n int)
begin
	declare total int default 0;
	while n>0 do
		set total:=total+n;
		set n:=n-1;
	end while;
	select total;
end;
call p7(10);


create procedure p8(in n int)
begin
	declare total int default 0;
	repeat
	set total:=total+n;
	set n:=n-1;
	until n=0
	end repeat;
	select total;
end

call p8(10);

-- 累加
create procedure p9(in n int)
begin
	declare total int default 0;

	sum:Loop
	if n<=0 then
		leave sum;
	end if;
	set total:=total+n;
	set n:=n-1;	
	END Loop sum;
select total;
	
end

call p9(10);


-- 累加偶数
create procedure p10(in n int)
begin
	declare total int default 0;

	sum:Loop
	if n<=0 then
		leave sum;
	end if;

	if n%2 =1 then
	set n:=n-1;
	iterate sum;
	end if;

	set total:=total+n;
	set n:=n-1;	
	END Loop sum;
select total;
	
end

call p10(3);