mysql+存储过程

#创建存储过程

create PROCEDURE p1()

BEGIN

select count(*) from test01;

END;

call P1;

CREATE PROCEDURE p2()
BEGIN
  DECLARE score int DEFAULT 58;
	
	DECLARE result VARCHAR(10);
	
	IF score > 85 then set result := '优秀';
	ELSEIF score >= 60 AND score <85 then set result := '及格';
  else set result := '不及格';
	end if;
	
	select result;
	
END;

drop PROCEDURE p2;


call p2;



CREATE PROCEDURE p3(in score int , out result VARCHAR(10))
BEGIN
  #DECLARE score int DEFAULT 58;
	
	#DECLARE result VARCHAR(10);
	
	IF score > 85 then set result := '优秀';
	ELSEIF score >= 60 AND score <85 then set result := '及格';
  else set result := '不及格';
	end if;
	
	#select result;
	
END;


call p3(66,@result);

select @result;

 call p3(1,@result)
 
 #将分数化成百分制
 CREATE PROCEDURE p5(INOUT score double)
 begin
 
 set score := score*0.01;

end;
set @score := 12
call p5(@score);

CREATE PROCEDURE p7(in mouth int)

BEGIN 
  
	DECLARE result VARCHAR(10);
  case 
	   when mouth >=1 and mouth <=3 then SET result := '一季度';
		 when mouth >=4 and mouth <=6 then SET result := '二季度';
		 when mouth >=7 and mouth <=9 then SET result := '三季度';
		 when mouth >=10 and mouth <=12 then SET result := '四季度';
		 
		 ELSE SET result :='入参错误';
	   
		 end CASE;
		 
		 select result;
END;

call p7(33);

select ;


CREATE PROCEDURE p8(in n int)

BEGIN
   DECLARE sum int DEFAULT 0;
   WHILE n>=0 DO
	 set sum := sum+n;
	 set n := n-1;
END WHILE;
   select CONCAT('将',n,'求和的结果；',sum);
end;

call p8(10) #将-1求和的结果；55


CREATE PROCEDURE p9(in n int)
BEGIN
DECLARE result INT DEFAULT 0;
 REPEAT
	set result := result+n;
	set n := n-1;
UNTIL n<=0 

END REPEAT;

select result;

end;

create PROCEDURE p10(in n int)

begin

DECLARE result int DEFAULT 0;

sum: LOOP
	#累加
	set result :=result+n;
	#递减 n
	set n :=n-1;
  #判断是否为退出的条件
	IF n<=0 THEN
		LEAVE sum; 
	END IF; 
END LOOP sum;
 select result;
end;
call p10(10);


create PROCEDURE p12(in n int)
BEGIN
DECLARE result int DEFAULT 0;

sum: LOOP
	#累加
	if n %2 = 1 then 
		set result :=result+n;
	end if;
	#递减 n
	set n :=n-1;
  #判断是否为退出的条件
	IF n<=0 THEN
		LEAVE sum; 
	END IF; 
END LOOP sum;
 select result;
end;

call p12(10); # 2 4 6 8 10 


create PROCEDURE p13(in n int)
BEGIN

DECLARE c_id  int;
DECLARE c_order_num VARCHAR(255);
#定义游标
DECLARE c_cursor CURSOR FOR select id,orderNum from test01 where id < n;
#定义游标退出标志
-- DECLARE EXIT HANDLER for SQLSTATE '02000' CLOSE c_cursor; #02开头的错误码的意思就是  not found
DECLARE EXIT HANDLER for not found CLOSE c_cursor;


drop if EXISTS c_test01;
#创建表
CREATE TABLE if not EXISTS c_test01(
  `id` int(64) PRIMARY KEY AUTO_INCREMENT,
	`c_id` int(64) DEFAULT 0,
	`c_order_num` VARCHAR(255)
);

open c_cursor;

WHILE true DO
fetch c_cursor into c_id,c_order_num;
INSERT into c_test01 VALUES(null,c_id,c_order_num);
END WHILE;
close c_cursor;
END;

call p13(10);