mysql存储过程学习笔记分享

# 对于MySQL 5.0.45版本，需要重新定义一个输入结束标记符,如:delimiter //
# 在所有的存储过程中使用";"作为每一条语句的结束标记
# 而每一条MySQL语句都必须使用"//"作为结束标记;
drop procedure if exists phelloworld //
create procedure phelloworld()
begin
select 'Hello,World!' as F;
end;
//
 
drop procedure if exists padd //
create procedure padd
(
a int,
b int
)
 
begin
declare c int;
if a is null then
set a = 0;
end if;
if b is null then
set b = 0;
end if;
set c = a + b;
select c as Sum;
end;
//
 
# 使用mysql变量调用存储过程
set @a = 10 //
set @b = 12 //
call padd(@a,@b) //
 
# 直接使用常量值调用存储过程
call padd(11,12) //
 
# 对数据库表进行操作
# 创建表
drop table if exists mapping //
create table mapping (
`cFieldID` smallint(5) unsigned not null,
`cFieldName` varchar(30) not null,
primary key(`cFieldID`)
)Engine=InnoDB default charset=utf8 //
 
insert into mapping values(1,'MarketValue') //
insert into mapping values(2,'P/L') //
insert into mapping values(3,'EName') //
insert into mapping values(4,'Nominal') //
insert into mapping values(5,'Chg') //
 
# 写一个存储过程,往mapping表中插入一条记录,并返回当前记录的总数
drop procedure if exists paddmapping //
create procedure paddmapping (
out cnt int
)
begin
declare maxid int;
select max(cFieldID)+1 into maxid from mapping;
insert into mapping values(maxid,'Hello');
select count(cFieldID) into cnt from mapping;
end //
 
# 定义一个包含输入输出参数的存储过程
drop procedure if exists pinoutmapping //
create procedure pinoutmapping (
in cfid int,
out cfnm varchar(30)
)
 
begin
select cFieldName into cfnm from mapping where cFieldID = cfid;
end;
//
 
# 在定义存储过程时使用输入参数:in inParam type
# 输入参数可以在存储过程中被修改,但是不能返回该输入参数的值
drop procedure if exists pinparam //
create procedure pinparam (
in inparam int
)
begin
select inparam;
# 在存储过程中改变输入参数的值
set inparam = 2;
select inparam;
end;
//
 
# 调用该存储过程之后再查看输入参数的值是否发生改变,虽然输入参数的值在存储过程中进行了修改
#
 
#在定义存储过程时使用输出参数:out outParam type
#输出参数可以在存储过程中进行修改,且能返回该输出参数的值
drop procedure if exists poutparam //
create procedure poutparam (
out outparam int
)
begin
select outparam;
# 改变输出参数的值
set outparam = 3;
select outparam;
end;
//
 
# 在定义存储过程时使用inout参数:inout inoutParam type
# 在调用存储过程时指定参数值,可在存储过程中改变该参数值且能返回
drop procedure if exists pinoutparam //
create procedure pinoutparam (
inout inoutparam int
)
begin
select inoutparam;
# 在存储过程中改变inout参数的值
set inoutparam = 3;
# 查看在存储过程中改变后的参数值
select inoutparam;
end;
//
 
# 在存储过程中定义变量:declare vname [,vname2 ...] vtype [default value]
# vtype是mysql中的数据类型:int,float,date,varchar(length)
# 变量赋值:set vname=value
drop procedure if exists pvarible //
create procedure pvarible ()
begin
declare uid int(8) default 0;
declare uname varchar(30) default 'root';
select uid as ID,uname as Name;
set uid = 1;
set uname = 'chench';
select uid as ID,uname as Name;
end;
//
 
# 用户变量
# (1)在MySQL客户端使用用户变量
select 'Hello,World!' into @hl //
select @hl //
 
set @gb = 'Good Bye!' //
select @gb //
 
set @gb = 1+2+3 //
select @gb //
 
# (2)在存储过程中使用用户变量
drop procedure if exists pgreetingworld //
create procedure pgreetingworld () select concat(@greeting,' World') //
set @greeting = 'Hello' //
call pgreetingworld() //
 
# (3)在存储过程间调用全局范围内的变量
set @vglobal="zhangsan" //
drop procedure if exists p1 //
create procedure p1 ()
begin
set @vglobal = 'wangwu'; -- 在存储过程中改变mysql全部变量的值
select @vglobal; -- 在存储过程中访问mysql全局变量的值
end;
//
 
drop procedure if exists p2 //
create procedure p2 ()
begin
select concat('variable last value is ',@vglobal);
end;
//
 
# 变量作用域
drop procedure if exists p3 //
create procedure p3()
begin
declare x1 varchar(15) default 'outter';
begin
declare x1 varchar(15) default 'inner';
select x1;
end;
select x1;
end;
//
 
# 存储过程中的条件语句
# 定义一个测试表
drop table if exists pt //
create table pt (
pid int unsigned auto_increment not null,
pname varchar(30) not null,
primary key(pid)
)Engine=InnoDB default charset = utf8 //
 
# (1)if-then -else语句:使用if var then else var end of结构
# 写一个根据传递的参数值是奇数还是偶数来插入数据库表相应的数据记录
drop procedure if exists p4 //
create procedure p4 (
in param int
)
begin
declare t int default 0;
set t = param % 2;
if t = 0 then
insert into pt values(null,'event number!');
else
insert into pt values(null,'odd number');
end if;
select * from pt;
end;
//
 
# (2)case语句:使用case var when-then ... end case结构
# 根据传递的参数值来插入相应的记录,往pt表中插入记录
drop procedure if exists p5 //
create procedure p5 (
in param int
)
begin
declare value int default -1;
set value = param % 2;
case value
when 0 then
insert into pt values(null,'even number');
else
insert into pt values(null,'odd number');
end case;
select * from pt;
end;
//
 
# 存储过程中的循环语句
# (1)while condition do(先条件再循环)
#...
#  end while
 
# 通过传递参数,连续插入新的记录到pt表中
delimiter //
drop procedure if exists p6 //
create procedure p6 (
in param int
)
begin
declare i int default 0;
if param > 0 then
while i < param do
insert into pt values(null,'param');
set i = i+1;
end while;
end if;
select * from pt;
end;
//
delimiter ;
 
# (2)repeat (先循环再条件)
#...
#until condition
#end repeat
 
# 通过传递参数,连续插入新的记录到pt表中
delimiter //
drop procedure if exists p7 //
create procedure p7 (
in param int
)
begin
declare i int default 0;
if param > 0 then
repeat
insert into pt values(null,'repeat');
set i = i+1;
until i >= param
end repeat;
end if;
select * from pt;
#echo 'successfully!'
end;
//
delimiter ;
 
# (3)loop(没有初始条件,也没有结束条件)
#LOOP_LABEL:loop
#...
#leave LOOP_LABEL --离开循环
#end loop --虽然使用leave离开了循环,但是仍然需要使用end loop作为loop的结束标记,这是语法固定结构
 
# 通过参数传递,往pt表中插入连续的记录
delimiter //
drop procedure if exists p8 //
create procedure p8 (
in param int
)
begin
declare i int default 0;
if param > 0 then
LOOP_LABEL:loop
insert into pt values(null,'loop');
set i = i+1;
if i >= param then
leave LOOP_LABEL; #满足条件就离开循环
end if;
end loop; #虽然已经使用leave离开了循环,但是必须要有loop结束标记
/*while i<param do
insert into pt values(null,'while');
set i=i+1;
end while;
*/
end if;
select * from pt;
end;
//
delimiter ;
 
#
# MySQL存储过程中使用查询结果的值来赋值给变量
# 如:select id into id_value from user where id = '';
# 当查询结果为空时不会进行赋值操作,即:id_value变量将保持原来的值
 
#
查询数据库中的存储过程
 
方法一：
       select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE'
 
方法二：
         show procedure status;
 
 
查看存储过程或函数的创建代码
 
show create procedure proc_name;
show create function func_name;
 
# 统计pt数据表中的记录总数,在Java程序中调用该存储过程
delimiter //
drop procedure if exists pcount //
create procedure pcount (
out count int
)
 
begin
#set autocommit = 0;
#start transaction;
select count(pid) into count from pt;
end;
//
delimiter ;