mysql04--存储过程

  1. 云栖社区>
  2. 博客>
  3. 正文

mysql04--存储过程

范大脚脚 2017-11-21 10:16:00 浏览633
展开阅读全文
复制代码
过程:若干语句,调用时执行封装的体。没有返回值的函数。
函数:是一个有返回值的过程

存储过程:把若干条sql封装起来,起个名字(过程),并存储在数据库中。

也有不存储的过程,匿名过程,用完就扔(mysql不支持匿名过程)


create procedure p1()
begin
    select 2+3; 
end$

show procedure status;//查看现有的存储过程:

mysql> call p1();//调用存储过程,Call 存储过程名字();

//存储过程中,使用declare声明变量,declare n int [default 默认值]

create procedure p2(age int,hei int)
begin
    declare age smallint default 3;
    declare height int default 180;
    select concat('年龄是:', age, '身高是:' ,height);
end$

mysql> call p2(1,2)$
+---------------------------------------------+
| concat('年龄是:', age, '身高是:' ,height) |
+---------------------------------------------+
| 年龄是:3身高是:180                        |
+---------------------------------------------+

create procedure p3()
begin
    declare age smallint default 3;
    set age := (age +20);
    select concat('年龄是:', age);
end$

mysql> call p3()$
+-------------------------+
| concat('年龄是:', age) |
+-------------------------+
| 年龄是:23              |
+-------------------------+

create procedure p4(n int)
begin
    select * from orde where gid=n;
end$

mysql> call p4(3)$
+-----+-----+-----+
| oid | gid | num |
+-----+-----+-----+
|   1 |   3 | 100 |
|   1 |   3 | 100 |
|   1 |   3 | 100 |
|   1 |   3 | 100 |
+-----+-----+-----+

create procedure p5(n int)
begin
    declare age int default 18;
    if age>18 then
        select '已成年';
    else
        select '未成年';
    end if;
end$



create procedure p7(n int,m char(1))
begin
if m='t' then
   select * from orde where gid=3;
else
   select * from orde where gid=2;
end if;
end$

delimiter $
create procedure p8(width int,height int)
begin
    if width > height then
        select '';
    elseif width < height then
        select '';
    else
        select ''
    end if;
end$

//编程:顺序、选择、循环。语法格式不一样。
create procedure t8()
begin
    declare total int default 0;
    declare num int default 0;
    
    while num <= 100 do
        set total := total + num;
        set num = num +1;
    end while;
    
    select total;
end$

mysql> call t8()$
+-------+
| total |
+-------+
|  5050 |
+-------+


create procedure t8(in n int)//in表示传进去的参数,
begin
    declare total int default 0;
    declare num int default 0;
    
    while num <= n do
        set total := total + num;
        set num = num +1;
    end while;
    
    select total;
end$

mysql>  create procedure t8(in n int,out total int)//in表示传进去的参数,out是传出去的参数,
begin
    declare num int default 0;
    
    set total=0;
    while num <= n do
        set num = num +1;
        set total := total + num;
    end while;

end$

mysql> call t8(100,@tt)$ //输出的值给@tt
Query OK, 0 rows affected

mysql> select @tt$
+------+
| @tt  |
+------+
| 5151 |
+------+


mysql>  create procedure t12(inout io1 int)//inout既可以传进去也可以传出来
    begin
    declare num int default 0;
    
    while num <= io1 do
        set num = num +1;
        set io1 := io1 + num;
    end while;

    end$

mysql> set @total = 100$
Query OK, 0 rows affected

mysql> call t12(@total)$
1264 - Out of range value for column 'io' at row 1

mysql> select @total$


//case用法:
create procedure t13()
begin
    declare pos int default 0;
    
    set pos := floor(4*rand());  //不能用position是关键字
    
    case pos
        when 1 then select "在飞";
        when 2 then select "在海里";
        when 3 then select "在地上";
        else select "不知道";
    end case;
    
end$

mysql> call t13()$
+--------+
| 不知道 |
+--------+
| 不知道 |
+--------+


//repeat
create procedure t14()
begin

    declare total int default 0;
    declare i int default 0;
    
    repeat
        set i:=i+1;
        set total:=total+i;
        until i>=100
    end repeat;

    select total;
end$

mysql> call t14()$
+-------+
| total |
+-------+
|  5151 |
+-------+
复制代码

 


本文转自农夫山泉别墅博客园博客,原文链接:http://www.cnblogs.com/yaowen/p/8144770.html,如需转载请自行联系原作者

网友评论

登录后评论
0/500
评论
范大脚脚
+ 关注