DEFAULT CHARACTER SET utf8;
use test;

DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

insert  into `class`(`id`,`name`) values 
(1,'Java'),
(2,'UI'),
(3,'產(chǎn)品');


DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

/*Data for the table `student` */

insert  into `student`(`id`,`name`,`class_id`) values 
(1,'張三',1),
(2,'李四',1),
(3,'王五',2),
(4,'趙劉',1),
(5,'錢七',3);

select * from class;

select * from student;

CREATE PROCEDURE procedure_name ([parameters[,...]])
begin
-- SQL語(yǔ)句
end ;

create procedure test1()
begin
	select 'Hello';
end;

call test1();

-- 查看db01數(shù)據(jù)庫(kù)中的所有存儲(chǔ)過程
select name from mysql.proc where db='test';

-- 查看存儲(chǔ)過程的狀態(tài)信息
show procedure status;

-- 查看存儲(chǔ)過程的創(chuàng)建語(yǔ)句
show create procedure test1;

drop procedure test1;

CREATE PROCEDURE test2 ()
begin
	
	declare num int default 0;		-- 聲明變量,賦默認(rèn)值為0
	select num+10;
	
end ;

call test2();			-- 調(diào)用存儲(chǔ)過程

CREATE PROCEDURE test3 ()
begin
	
	declare num int default 0;
	set num =20;			-- 給num變量賦值
	select num;
	
end ;

call test3();

CREATE PROCEDURE test4 ()
begin
	
	declare num int default 0;			
	select count(1) into num from student;
	select num;
end ;

call test4();

CREATE PROCEDURE test5 ()
begin
	
	declare id int default 1;			
	declare class_name varchar(30);
	
	if id=1 then
		set class_name='哇塞，Java大佬！';
	elseif id=2 then
		set class_name='原來是UI的啊';
	else
		set class_name='不用想了，肯定是產(chǎn)品小樣';
	end if;
	
	select class_name;
end ;

call test5();

create procedure procedure_name([in/out/inout] 參數(shù)名  參數(shù)類型)

-- 定義一個(gè)輸入?yún)?shù)
CREATE PROCEDURE test6 (in id int)
begin
	
	declare class_name varchar(30);
	
	if id=1 then
		set class_name='哇塞，Java大佬！';
	elseif id=2 then
		set class_name='原來是UI的啊';
	else
		set class_name='不用想了，肯定是產(chǎn)品小樣';
	end if;
	
	select class_name;
end ;

call test6(3);

-- 定義一個(gè)輸入?yún)?shù)和一個(gè)輸出參數(shù)
CREATE PROCEDURE test7 (in id int,out class_name varchar(100))
begin
	if id=1 then
		set class_name='哇塞，Java大佬！';
	elseif id=2 then
		set class_name='原來是UI的啊';
	else
		set class_name='不用想了，肯定是產(chǎn)品小樣';
	end if;
	
end ;


call test7(1,@class_name);	-- 創(chuàng)建會(huì)話變量		

select @class_name;		-- 引用會(huì)話變量

CREATE PROCEDURE test8 (in month int,out season varchar(10))
begin
	
	case 
		when month >=1 and month<=3 then
			set season='spring';
		when month >=4 and month<=6 then
			set season='summer';
		when month >=7 and month<=9 then
			set season='autumn';
		when month >=10 and month<=12 then
			set season='winter';
	end case;
end ;

call test8(9,@season);			-- 定義會(huì)話變量來接收test8存儲(chǔ)過程返回的值

select @season;

CREATE PROCEDURE test10 (in count int)
begin
	declare total int default 0;
	declare i int default 1;
	
	while i<=count do
		set total=total+i;
		set i=i+1;
	end while;
	select total;
end ;

call test10(10);

CREATE PROCEDURE test11 (count int)		-- 默認(rèn)是輸入(in)參數(shù)
begin
	declare total int default 0;
	repeat 
		set total=total+count;
		set count=count-1;
		until count=0				-- 結(jié)束條件,注意不要打分號(hào)
	end repeat;
	select total;
end ;

call test11(10);

CREATE PROCEDURE test12 (count int)		-- 默認(rèn)是輸入(in)參數(shù)
begin
	declare total int default 0;	
	sum:loop							-- 定義循環(huán)標(biāo)識(shí)
		set total=total+count;
		set count=count-1;
		
		if count < 1 then
			leave sum;					-- 跳出循環(huán)
		end if;
	end loop sum;						-- 標(biāo)識(shí)循環(huán)結(jié)束
	select total;
	
end ;

call test12(10);

declare cursor_name cursor for statement;

open cursor_name;

close cursor_name;

CREATE PROCEDURE test13 ()		-- 默認(rèn)是輸入(in)參數(shù)
begin
	
	declare id int(11);
	declare `name` varchar(20);
	declare class_id int(11);
	-- 定義游標(biāo)結(jié)束標(biāo)識(shí)符
	declare has_data int default 1;
	
	declare stu_result cursor for select * from student;
	-- 監(jiān)測(cè)游標(biāo)結(jié)束
	declare exit handler for not FOUND set has_data=0;
	
	-- 打開游標(biāo)
	open stu_result;
	
	repeat 
		fetch stu_result into id,`name`,class_id;
		
		select concat('id: ',id,';name: ',`name`,';class_id',class_id);
		until has_data=0		-- 退出條件,注意不要打分號(hào)
	end repeat;
	
	-- 關(guān)閉游標(biāo)
	close stu_result;
	
end ;

call test13();

create temporary table temp_table(
	id int,
	name varchar(10)
);
insert into temp_table values (1,'1');

select * from temp_table ;

show tables;   -- 不會(huì)顯示臨時(shí)表的存在

create procedure pro1()
begin
	create temporary table temp_table(
		id int
	);
	
	insert into temp_table values(1);
	
	select * from temp_table;
end;

call pro1();

create function fun2()
returns int
begin

	declare id int ;
	create table temp_table(				
		id int
	);
	
	insert into temp_table values(1);
	
	select id from into id temp_table;	
	return id;
end;