mysql存储过程示例

因工作需要,需要完成这样一个功能:编写一条SQL语句,能够条件查询A表中数据,同时能都查询B表中的数据,并且能够同时修改A表中的某条数据,删除B表中的某条数据.

一条SQL: 查询t1,t2. 向t2中添加一条数据,删除t1中某条数据

delimiter //
    create procedure caozuo()
    begin
        select * from t1;# 查询t1
        select * from t2; # 查询t2
        insert into t2 values(1,'市场部',5000); #插入数据
        delete from t1 where id=5; #删除数据
    end //
delimiter ;

调用存储过程

语法：call 过程名（参数1，参数2）;

mysql> call my_p1();

|-----存储过程是什么?作用?

MySQL 5.0版开始才支持存储过程。

存储过程（StoredProcedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

MySQL存储过程的语句块以begin开始，以end结束。语句体中可以包含变量的声明、控制语句、SQL查询语句等。由于存储过程内部语句要以分号结束，所以在定义存储过程前应将语

句结束标志“;”更改为其他字符，并且该字符在存储过程中出现的几率也应该较低，可以用关键字delimiter更改。分隔符是通知MySQL客户端已经输入完成的符号。

|-----编写存储过程的格式? 编写一个简单的存储过程

格式:

delimiter 新的结束符

create procedure 过程名()

begin

# 过程体:SQL,变量,表达式,控制语句,循环语句等等

end 结束符

delimiter ;

说明:SQL中默认的命令结束符为分号";",当mysql碰到此符号,就认为命令输入完成,就开始执行命令.

新的的结束符的要求: 不要太特殊 "__"  "." ,一般使用"//" .

第一步:进行结束符的更改

第二步:编写存储过程

第三部:结束符改为默认

delimiter //
create procedure my_p1()
begin
select * from t1;
end //
delimiter ;

|----编写含有参数的存储过程?使用该存储过

参数的类型:in / out /inout

参数的默认类型:in ---->相对存储过程而言

delimiter //

create procedure my_p1()

begin

end //

delimiter ;

含有输入参数的存储过程:

参数格式: in/out/inout 参数名 类型

delimiter //
create procedure my_p2(in num int)
begin
select num;#查询参数的值
set num=23; #重新赋值参数的值为23;
select num;
end //
delimiter ;

调用含有参数的存储过成:

set @n=2;  #设置一个全局的变量

call my_p2(@n);

含有输出参数的存储过程:

delimiter //
create procedure my_p3(out num int)
begin
select num;#查询参数的值
set num=23; #重新赋值参数的值为23;
select num;
end //
delimiter ;

mysql> set @n1=1;

Query OK, 0 rows affected (0.00 sec)

mysql> call my_p3(@n1);

+------+

| num  |

+------+

| NULL |

+------+

1 row in set (0.00 sec)

+------+

| num  |

+------+

|   23 |

+------+

1 row in set (0.01 sec)

Query OK, 0 rows affected (0.14 sec)

mysql> select @n1;

+------+

| @n1  |

+------+

|   23 |

+------+

1 row in set (0.00 sec)

总结: 对于参数的理解:相对存储工程判断是输入或输出,不管是输出或输入,都是单向的数据传递.

inout:数据既可以传进去,也可以传出来

|----在存储过程中编写变量,并赋值

格式: 声明变量:  declare 变量名 变量类型 [默认值];

变量赋值:  set 变量名=值;  编写含有变量的存储过程:

delimiter //
create procedure my_p4()
begin
declare num int default 23;
select num; 
end //
delimiter ;
delimiter //
create procedure my_p5()
begin
declare sex char(2)  ;
select sex;
set sex='男';
select sex;
end //
delimiter ;

mysql> call my_p5();

+------+

| sex  |

+------+

| NULL |

+------+

1 row in set (0.00 sec)

+------+

| sex  |

+------+

| 男   |

+------+

1 row in set (0.06 sec)

Query OK, 0 rows affected (0.08 sec)

|-----存储过程的删除和修改?怎么查看数据库中含有哪些存储过程?

修改视图:  alter view 视图名 as  新的select语句;

对于存储过程的修改: 一般不去修改,因为存储过程的修改,只能修改一些属性参数,不能修改过程体.

删除存储过程: drop procedure if exists 过程名;  注意不能加括号

查看数据库中含有什么存储过程?

Show procedure status;

|-----条件语句的使用

if:
delimiter //
create procedure my_p1()
begin
declare num int ;
set num=23;
if(num=23) then
set num=-23;
select num;
end if;
end //
delimiter ;
if else if else:
delimiter //
create procedure my_p2(in num int)
begin
if(num=23) then
set num=-23;
select num;
else if(num=2)then
set num=-2;
select num;
else
select '完蛋了';
end if ;
end if;
end //
delimiter ;
case:
delimiter //
create procedure my_p3(in num int)
begin
case num
when 1 then select num; #不需要break
when 2 then select num;
else select '没有值'; # 相当于default
end case;
end //
delimiter ;

mysql> call my_p3(3);

ERROR 1339 (20000): Case not found for CASE statement

mysql>

|-----循环语句的使用

loop循环:

delimiter //
create procedure my_p4(in num int)
begin
loop_a:   #自定义了一个标签
loop
set num=num+1;
select num;
if(num>6) then
leave loop_a;
end if;
end loop;
end //
delimiter ;

repeat循环:

delimiter //
create procedure my_p5(in num int)
begin
repeat
select num;
set num=num+1;
until num>5 
end repeat;
end //
delimiter ;

while循环:

delimiter //
create procedure my_p6(in num int)
begin
while num<5
do
select num;
set num=num+1;
end while;
end //
delimiter ;