MySQL 创建存储过程-百分百源码网

MySQL 创建存储过程

发布时间：01/15 来源：浏览：关键词：

MySQL 创建存储过程
“pr_add” 是个简单的 MySQL 存储过程，这个存储过程有两个 int 类型的输入参数

“a”、“b”，返回这两个参数的和。

drop procedure if exists pr_add;

-- 计算两个数之和

create procedure pr_add
(
   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;

/*
return c;- 不能在 MySQL 存储过程中使用。return 只能出现在函数中。
/
end;
二、调用 MySQL 存储过程
call pr_add(10, 20);
执行 MySQL 存储过程，存储过程参数为 MySQL 用户变量。

set @a = 10;
set @b = 20;

call pr_add(@a, @b);
三、MySQL 存储过程特点
创建 MySQL 存储过程的简单语法为：

create procedure 存储过程名字()
(
[in|out|inout] 参数 datatype
)
begin
MySQL 语句;
end;
MySQL 存储过程参数如果不显式指定“in”、“out”、“inout”，则默认为“in”。

习惯上，对于是“in” 的参数，我们都不会显式指定。

1. MySQL 存储过程名字后面的“()”是必须的，即使没有一个参数，也需要“()”

2. MySQL 存储过程参数，不能在参数名称前加“@”，如：“@a int”。下面的创建存

储过程语法在 MySQL 中是错误的（在 SQL Server 中是正确的）。 MySQL 存储过程中

的变量，不需要在变量名字前加“@”，虽然 MySQL 客户端用户变量要加个“@”。

create procedure pr_add
(
@a int,- 错误
b int - 正确
)
3. MySQL 存储过程的参数不能指定默认值。

4. MySQL 存储过程不需要在 procedure body 前面加 “as”。而 SQL Server 存储过

程必须加 “as” 关键字。

create procedure pr_add
(
   a int,
   b int
)
as             - 错误，MySQL 不需要 “as”
begin
   mysql statement ...;
end;
5. 如果 MySQL 存储过程中包含多条 MySQL 语句，则需要 begin end 关键字。

create procedure pr_add
(
   a int,
   b int
)
begin
   mysql statement 1 ...;
   mysql statement 2 ...;
end;
6. MySQL 存储过程中的每条语句的末尾，都要加上分号 “;”

...

declare c int;

   if a is null then
      set a = 0;
   end if;

...
end;
7. MySQL 存储过程中的注释。

   /*
     这是个
     多行 MySQL 注释。
/

declare c int; - 这是单行 MySQL 注释（注意- 后至少要有一个空格）

   if a is null then 这也是个单行 MySQL 注释
      set a = 0;
   end if;

...
end;
8. 不能在 MySQL 存储过程中使用 “return” 关键字。

set c = a + b;

select c as sum;

/*
return c;- 不能在 MySQL 存储过程中使用。return 只能出现在函数中。
/
end;
9. 调用 MySQL 存储过程时候，需要在过程名字后面加“()”，即使没有一个参数，也

需要“()”

call pr_no_param();
10. 因为 MySQL 存储过程参数没有默认值，所以在调用 MySQL 存储过程时候，不能省

略参数。可以用 null 来替代。

call pr_add(10, null);
mysql 5.0存储过程学习总结
一.创建存储过程

1.基本语法：

create procedure sp_name()
begin
………
end
2.参数传递

二.调用存储过程

1.基本语法：call sp_name()
注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递
三.删除存储过程

1.基本语法：
drop procedure sp_name//
2.注意事项
(1)不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程
四.区块，条件，循环

1.区块定义，常用
begin
……
end;
也可以给区块起别名，如：
lable:begin
………..
end lable;
可以用leave lable;跳出区块，执行区块以后的代码
2.条件语句

if 条件 then
statement
else
statement
end if;
3.循环语句
(1).while循环

[label:] WHILE expression DO

statements

END WHILE [label] ;

(2).loop循环

[label:] LOOP

statements

END LOOP [label];

(3).repeat until循环

[label:] REPEAT

statements

UNTIL expression

END REPEAT [label] ;

五.其他常用命令

1.show procedure status
显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时

间等
2.show create procedure sp_name
显示某一个存储过程的详细信息

mysql存储过程中要用到的运算符

mysql存储过程学习总结－操作符
算术运算符

+     加   SET var1=2+2;       4
-     减   SET var2=3-2;       1
*     乘   SET var3=3*2;       6
/     除   SET var4=10/3;      3.3333
DIV   整除 SET var5=10 DIV 3; 3
%     取模 SET var6=10%3 ;     1

比较运算符

>            大于 1>2 False
<            小于 2<1 False
<=           小于等于 2<=2 True
>=           大于等于 3>=2 True
BETWEEN      在两值之间 5 BETWEEN 1 AND 10 True
NOT BETWEEN 不在两值之间 5 NOT BETWEEN 1 AND 10 False
IN           在集合中 5 IN (1,2,3,4) False
NOT IN       不在集合中 5 NOT IN (1,2,3,4) True
=            等于 2=3 False
<>, !=       不等于 2<>3 False
<=>          严格比较两个NULL值是否相等 NULL<=>NULL True
LIKE         简单模式匹配 "Guy Harrison" LIKE "Guy%" True
REGEXP       正则式匹配 "Guy Harrison" REGEXP "[Gg]reg" False
IS NULL      为空 0 IS NULL False
IS NOT NULL 不为空 0 IS NOT NULL True
逻辑运算符

与(AND)

AND
TRUE
FALSE
NULL

TRUE
TRUE
FALSE
NULL

FALSE
FALSE
FALSE
NULL

NULL
NULL
NULL
NULL

或(OR)

OR
TRUE
FALSE
NULL

TRUE
TRUE
TRUE
TRUE

FALSE
TRUE
FALSE
NULL

NULL
TRUE
NULL
NULL

异或(XOR)

XOR
TRUE
FALSE
NULL

TRUE
FALSE
TRUE
NULL

FALSE
TRUE
FALSE
NULL

NULL
NULL
NULL
NULL

位运算符

|   位或
&   位与
<< 左移位
>> 右移位
~   位非(单目运算，按位取反)

mysq存储过程中常用的函数，字符串类型操作，数学类，日期时间类。

mysql存储过程基本函数
一.字符串类

CHARSET(str) //返回字串字符集
CONCAT (string2 [,... ]) //连接字串
INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返

回0
LCASE (string2 ) //转换成小写
LEFT (string2 ,length ) //从string2中的左边起取length个字符
LENGTH (string ) //string长度
LOAD_FILE (file_name ) //从文件读取内容
LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定开始位置
LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重复count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str
RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length
RTRIM (string2 ) //去除后端空格
STRCMP (string1 ,string2 ) //逐字符比较两字串大小,
SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,
注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1

mysql> select substring(’abcd’,0,2);
+———————–+
| substring(’abcd’,0,2) |
+———————–+
| |
+———————–+
1 row in set (0.00 sec)

符
UCASE (string2 ) //转换成大写
RIGHT(string2,length) //取string2最后length个字符
SPACE(count) //生成count个空格

二.数学类

ABS (number2 ) //绝对值
BIN (decimal_number ) //十进制转二进制
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //进制转换
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小数位数
HEX (DecimalNumber ) //转十六进制
注：HEX()中可传入字符串，则返回其ASC-11码，如HEX(’DEF’)返回4142143
也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19
LEAST (number , number2 [,..]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,power ) //求指数
RAND([seed]) //随机数
ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]

注：返回类型并非均为整数，如：
(1)默认变为整形值
mysql> select round(1.23);
+————-+
| round(1.23) |
+————-+
| 1 |
+————-+
1 row in set (0.00 sec)

mysql> select round(1.56);
+————-+
| round(1.56) |
+————-+
| 2 |
+————-+
1 row in set (0.00 sec)

(2)可以设定小数位数，返回浮点型数据
mysql> select round(1.567,2);
+—————-+
| round(1.567,2) |
+—————-+
| 1.57 |
+—————-+
1 row in set (0.00 sec)

SIGN (number2 ) //返回符号,正负或0
SQRT(number2) //开平方

三.日期时间类

ADDTIME (date2 ,time_interval ) //将time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区
CURRENT_DATE ( ) //当前日期
CURRENT_TIME ( ) //当前时间
CURRENT_TIMESTAMP ( ) //当前时间戳
DATE (datetime ) //返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间
DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间
DATEDIFF (date1 ,date2 ) //两个日期差
DAY (date ) //返回日期的天
DAYNAME (date ) //英文星期
DAYOFWEEK (date ) //星期(1-7) ,1为星期天
DAYOFYEAR (date ) //一年中的第几天
EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分
MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串
MAKETIME (hour ,minute ,second ) //生成时间串
MONTHNAME (date ) //英文月份名
NOW ( ) //当前时间
SEC_TO_TIME (seconds ) //秒数转成时间
STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示
TIMEDIFF (datetime1 ,datetime2 ) //两个时间差
TIME_TO_SEC (time ) //时间转秒数]
WEEK (date_time [,start_of_week ]) //第几周
YEAR (datetime ) //年份
DAYOFMONTH(datetime) //月的第几天
HOUR(datetime) //小时
LAST_DAY(date) //date的月的最后日期
MICROSECOND(datetime) //微秒
MONTH(datetime) //月
MINUTE(datetime) //分

附:可用在INTERVAL中的类型
DAY ,DAY_HOUR ,DAY_MINUTE ,DAY_SECOND ,HOUR ,HOUR_MINUTE ,HOUR_SECOND

MySQL存储过程例子，包含事务，参数，嵌套调用，游标，循环等

view plaincopy to clipboardprint?
drop procedure if exists pro_rep_shadow_rs;
delimiter |
----------------------------------
-- rep_shadow_rs
-- 用来处理信息的增加，更新和删除
-- 每次只更新上次以来没有做过的数据
-- 根据不同的标志位
-- 需要一个输出的参数，
-- 如果返回为0，则调用失败，事务回滚
-- 如果返回为1，调用成功，事务提交
--
-- 测试方法
-- call pro_rep_shadow_rs(@rtn);
-- select @rtn;
----------------------------------
create procedure pro_rep_shadow_rs(out rtn int)
begin
    -- 声明变量，所有的声明必须在非声明的语句前面
    declare iLast_rep_sync_id int default -1;
    declare iMax_rep_sync_id int default -1;
    -- 如果出现异常，或自动处理并rollback,但不再通知调用方了
    -- 如果希望应用获得异常，需要将下面这一句，以及启动事务和提交事务的语句

全部去掉
    declare exit handler for sqlexception rollback;
    -- 查找上一次的
    select eid into iLast_rep_sync_id from rep_de_proc_log where

tbl='rep_shadow_rs';
    -- 如果不存在，则增加一行
    if iLast_rep_sync_id=-1 then
      insert into rep_de_proc_log(rid,eid,tbl) values(0,0,'rep_shadow_rs');

      set iLast_rep_sync_id = 0;
    end if;

    -- 下一个数字
    set iLast_rep_sync_id=iLast_rep_sync_id+1;
    -- 设置默认的返回值为0：失败
    set rtn=0;

    -- 启动事务
    start transaction;
    -- 查找最大编号
    select max(rep_sync_id) into iMax_rep_sync_id from rep_shadow_rs;
    -- 有新数据
    if iMax_rep_sync_id>=iLast_rep_sync_id then
        -- 调用
        call pro_rep_shadow_rs_do(iLast_rep_sync_id,iMax_rep_sync_id);
        -- 更新日志
        update rep_de_proc_log set

rid=iLast_rep_sync_id,eid=iMax_rep_sync_id where tbl='rep_shadow_rs';
    end if;

    -- 运行没有异常，提交事务
    commit;
    -- 设置返回值为1
    set rtn=1;
end;
|
delimiter ;
drop procedure if exists pro_rep_shadow_rs_do;
delimiter |
---------------------------------
-- 处理指定编号范围内的数据
-- 需要输入2个参数
-- last_rep_sync_id 是编号的最小值
-- max_rep_sync_id 是编号的最大值
-- 无返回值
---------------------------------
create procedure pro_rep_shadow_rs_do(last_rep_sync_id int, max_rep_sync_id

int)
begin
    declare iRep_operationtype varchar(1);
    declare iRep_status varchar(1);
    declare iRep_Sync_id int;
    declare iId int;
    -- 这个用于处理游标到达最后一行的情况
    declare stop int default 0;
    -- 声明游标
    declare cur cursor for select

id,Rep_operationtype,iRep_status,rep_sync_id from rep_shadow_rs where

rep_sync_id between last_rep_sync_id and max_rep_sync_id;
    -- 声明游标的异常处理，设置一个终止标记
    declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;

    -- 打开游标
    open cur;

    -- 读取一行数据到变量
    fetch cur into iId,iRep_operationtype,iRep_status,iRep_Sync_id;
    -- 这个就是判断是否游标已经到达了最后
    while stop <> 1 do
        -- 各种判断
        if iRep_operationtype='I' then
            insert into rs0811 (id,fnbm) select id,fnbm from rep_shadow_rs

where rep_sync_id=iRep_sync_id;
        elseif iRep_operationtype='U' then
        begin
            if iRep_status='A' then
                insert into rs0811 (id,fnbm) select id,fnbm from

rep_shadow_rs where rep_sync_id=iRep_sync_id;
            elseif iRep_status='B' then
                delete from rs0811 where id=iId;
            end if;
        end;
        elseif iRep_operationtype='D' then
            delete from rs0811 where id=iId;
        end if;

        -- 读取下一行的数据
        fetch cur into iId,iRep_operationtype,iRep_status,iRep_Sync_id;
    end while; -- 循环结束
    close cur; -- 关闭游标
end;
|

打赏