存储过程游标

存储过程

查看现有的存储过程
Show procedure status G;

G 横着显示
delimiter ;或者$ 定义结尾标志;或者$

刪除存储过程
Drop procedure 存儲過程名字

参数
in 传入参数
out 传出参数
inout
set @currentAge = 10$             --先设置一个变量
call procedureName(@currentAge)$ --传入变量参数
select @currentAge$               --查询值

(1)
create procedure p1()
begin
select * from s_organization_type;
end$

调用存储过程
Call 存储过程名字();

(2)
create procedure p2(num int)
begin
    select * from s_organization_type a where a.sot_id>num;
end$

call p2(3) 取出主键大于3的信息

(3)
create procedure p3(num int,s char(4))
begin
    if s='true' then
        select * from s_organization_type a where a.sot_id>num;
    else
        select * from s_organization_type a where a.sot_id<num;
    end if;
end$

call p3(5,'true')
传入的参数s的值为‘true’時,查询主键大于5的语句,否则执行小于5的语句

(4)
create procedure p4(num smallint)
begin
   declare i int default 1;
   declare temp int default 0;
   while i<=num do
     set temp=temp+i;
     set i=i+1;
   end while;
   select temp;
end$

存储过程和函数
procedure function
存储过程没有返回值

函數
create function ..
begin
RETURN 0;
end$

===========================================

游标

declare continue handler for not found set nomore=0;
continue handler 触发后继续执行后面的代码
exit handler 触发后后面的代码不执行
undo 触发后后面的代码被撤消

(1)
create procedure p5()
begin
declare row_sotid int;
declare row_sotname varchar(20);
declare row_viewseq tinyint;

declare cnt int default 0;
declare i int default 0;

declare cursor_sot cursor for select sot_id,sot_name,view_seq from s_organization_type;   --申明游标

select count(*) into cnt from s_organization_type;     --給cnt赋值

open cursor_sot;                                      --打开游标

repeat                          --循环
    set i:=i+1;                       --每次循环增1
    fetch cursor_sot into row_sotid,row_sotname,row_viewseq;
    select row_sotid,row_sotname,row_viewseq;       --打印数据
until i>=cnt end repeat;               --停止循环当i等于count(*)时

close cursor_sot;                   --关闭游标

end$

(2)
create procedure p6()
begin
declare row_sotid int;
declare row_sotname varchar(20);
declare row_viewseq tinyint;

declare nomore int default 1;
declare cursor_sot cursor for select sot_id,sot_name,view_seq from s_organization_type;
declare continue handler for not found set nomore=0;       --当读取不到数据时把nomore设为0,然后停止循环

open cursor_sot;

while nomore != 0 do

    fetch cursor_sot into row_sotid,row_sotname,row_viewseq;
    select row_sotid,row_sotname;

end while;
close cursor_sot;

end$

(3)
CREATE PROCEDURE `p6`()
begin
declare row_sotid int;
declare row_sotname varchar(20);
declare row_viewseq tinyint;

declare nomore int default 1;
declare cursor_sot cursor for select sot_id,sot_name,view_seq from s_organization_type;
declare continue handler for not found set nomore=0;

open cursor_sot;

while nomore<>0 do

       fetch cursor_sot into row_sotid,row_sotname,row_viewseq;
    if nomore <> 0 then

      select row_sotid,row_sotname;

    end if;

end while;

close cursor_sot;

   select '1';


end

带参数的

(1)
CREATE PROCEDURE x(in num int,out total int)
BEGIN
   declare i int default 0;
   set total:=0;
   while i<num do
      set i:=i+1;
       set total:=total+i;
   end while;
END

call x(100,@total)
select @total$

(2)

CREATE PROCEDURE x(inout num int)
BEGIN
   set num:=num+20;
END

set @age:=0$
call x(@age)$
select @age$

存储过程 游标

存储过程游标