1. Declare
delimiter ||
crate procedure usePro()
begin
declare a, b varchar(20);
insert into TesFun2 values(null, a, b);
end ||
delimiter;
call usePro();
2. Set
delimiter ||
create procedure usePro2()
begin
declare a, b varchar(20);
set a='Linux C编程', b='123456';
insert into TesFun2 values(null, a, b);
end ||
delimiter;
call usePro2();
3. Select into
delimiter ||
create procedure usePro3()
begin
declare a, b varchar(20);
select useName2, password2 into a, b from TesFun3 where id2=1;
insert into TesFun2 values(null, a, b);
end ||
delimiter;
call usePro3();
4. Cursor
delimiter||
create procedure usePro4()
begin
declare a, b varchar(20);
declare cur4 cursor for select useName2, password2 from TesFun3;
open cur4;
fetch cur4 into a, b;
insert into TesFun2 values(null, a, b);
close cur4;
end ||
delimiter;
call usePro4();
5. If
delimiter ||
create procedure tesPro5(in bookId int)
begin
select count(*) into @num from TesFun2 where id=bookId;
if @num>0 then update TesFun2 set useNam='Java12345' where id=bookId;
else
insert into TesFun2 values(null, '禾博士', 'heboshi');
end if;
end ||
delimiter;
call tesPro5(4);
call tesPro5(5);
6. Case
delimiter ||
create procedure tesPro6(in bookId int)
begin
select count(*) into @num from TesFun2 where id=bookId;
case @num
when 1 then update TesFun2 set useNam='Java1234567' where id=bookId;
when 0 then insert into TesFun2 values(null, '禾博士2', 'heboshi2');
else insert into TesFun2 values(null, '禾博士3', 'heboshi3');
end case;
end ||
delimiter;
call tesPro6(3);
call tesPro6(8);
7. Loop
delimiter ||
create procedure tesPro7(in totNum int)
begin
loo: loop
set totNum=totNum-1;
if totNum=0 then leave loo;
else
insert into TesFun3 values(totNum, '禾博士', 'heboshi');
end if;
end loop loo;
end ||
delimiter;
call tesPro7(10);
8. Iterate
delimiter ||
create procedure tesPro8(in totNum int)
begin
loo: loop
set totNum=totNum-1;
if totNum=0 then leave loo;
elseif totNum=3 then iterate loo;
else
insert into TesFun3 values(totNum, '好玩的Java', 'mima');
end if;
end loop loo;
end ||
delimiter;
call tesPro8(11);
9. Repeat
delimiter ||
create procedure tesPro9(in totNum int)
begin
rea: repeat
set totNum=totNum-1;
insert into TesFun3 values(totNum, '加油', 'jiayou');
until totNum=1;
end repeat rea;
end ||
delimiter;
call tesPro9(11);
10. While
delimiter ||
create procedure tesPro10(in totNum int)
begin
whi: while totNum>0 do
insert into TesFun3 values(totNum, 'while', 'pwhile');
set totNum=totNum-1;
end while whi;
end ||
delimiter;
call tesPro10(5);