MySQL存储过程中的3种循环,存储过程的基本语法,ORACLE与MYSQL的存储过程/函数的使用区别，退出存储过程方法

墨蓝 2022-04-05 15:52 655阅读 0赞

[学无止境][Link 1]

[博客园][Link 2]   [首页][Link 1]   [新随笔][Link 3]   [联系][Link 4]   [订阅][Link 5][![订阅][xml.gif]][Link 5]  [管理][Link 6]

随笔-1968  评论-103  文章-4

# [MySQL存储过程中的3种循环,存储过程的基本语法,ORACLE与MYSQL的存储过程/函数的使用区别，退出存储过程方法][MySQL_3_ORACLE_MYSQL] #

在MySQL存储过程的语句中有三个标准的循环方式：WHILE循环，LOOP循环以及REPEAT循环。还有一种非标准的循环方式：GOTO，不过这种循环方式最好别用，很容易引起程序的混乱，在这里就不错具体介绍了。

这几个循环语句的格式如下：

*  WHILE……DO……END WHILE
 *  REPEAT……UNTIL END REPEAT
 *  LOOP……END LOOP
 *  GOTO

下面首先使用第一种循环编写一个例子。

![复制代码][copycode.gif]

mysql> create procedure pro10()
 -> begin
 -> declare i int;
 -> set i=0;
 -> while i<5 do
 -> insert into t1(filed) values(i);
 -> set i=i+1;
 -> end while;
 -> end;//

![复制代码][copycode.gif]

Query OK, 0 rows affected (0.00 sec)  
    在这个例子中，INSERT和SET语句在WHILE和END WHILE之间，当变量i大于等于5的时候就退出循环。使用set i=0；语句是为了防止一个常见的错误，如果没有初始化，i默认变量值为NULL，而NULL和任何值操作的结果都是NULL。  
    执行一下这个存储过程并产看一下执行结果：  
mysql> delete from t1//  
Query OK, 0 rows affected (0.00 sec)  
mysql> call pro10()//  
Query OK, 1 row affected (0.00 sec)  
mysql> select \* from t1//  
\+——-+  
| filed |  
\+——-+  
|     0 |  
|     1 |  
|     2 |  
|     3 |  
|     4 |  
\+——-+  
5 rows in set (0.00 sec)  
    以上就是执行结果，有5行数据插入到数据库中，证明存储过程编写正确无误^\_^。

再来看一下第二个循环控制指令 REPEAT……END REPEAT。使用REPEAT循环控制语句编写下面这个存储过程：

![复制代码][copycode.gif]

mysql> create procedure pro11()
        -> begin
        -> declare i int default 0;
        -> repeat
        ->     insert into t1(filed) values(i);
        ->     set i=i+1;
        ->     until i>=5
        -> end repeat;
        -> end;//

![复制代码][copycode.gif]

Query OK, 0 rows affected (0.00 sec) 
    这个REPEAT循环的功能和前面WHILE循环一样，区别在于它的执行后检查是否满足循环条件（until i>=5），而WHILE则是执行前检查（while i<5 do）。 
    不过要注意until i>=5后面不要加分号，如果加分号，就是提示语法错误。 
    编写完成后，调用一下这个存储过程，并查看结果： 
mysql> delete from t1// 
Query OK, 5 rows affected (0.00 sec)

mysql> call pro11()//  
Query OK, 1 row affected (0.00 sec) \#虽然在这里显示只有一行数据受到影响，但是下面选择数据的话，还是插入了5行数据。

mysql> select \* from t1//  
\+——-+  
| filed |  
\+——-+  
|     0 |  
|     1 |  
|     2 |  
|     3 |  
|     4 |  
\+——-+  
5 rows in set (0.00 sec)  
一行就是执行结果，实际的作用和使用while编写的存储过程一样，都是插入5行数据。

再来看一下第三个循环控制语句LOOP……END LOOP。编写一个存储过程程序如下：

![复制代码][copycode.gif]

mysql> create procedure pro12()
        -> begin
        -> declare i int default 0;
        -> loop_label: loop
        ->     insert into t1(filed) values(i);
        ->     set i=i+1;
        ->     if i>=5 then
        ->         leave loop_label;
        ->     end if;
        -> end loop;
        -> end;//

![复制代码][copycode.gif]

Query OK, 0 rows affected (0.00 sec)  
从上面这个例子可以看出，使用LOOP编写同样的循环控制语句要比使用while和repeat编写的要复杂一些：在循环内部加入了IF……END IF语句，在IF语句中又加入了LEAVE语句，LEAVE语句的意思是离开循环，LEAVE的格式是：LEAVE 循环标号。  
    编写完存储过程程序后，来执行并查看一下运行结果：  
mysql> delete from t1//  
Query OK, 5 rows affected (0.00 sec)

mysql> call pro12//  
Query OK, 1 row affected (0.00 sec) \#虽然说只有一行数据受影响，但是实际上是插入了5行数据。

mysql> select \* from t1//  
\+——-+  
| filed |  
\+——-+  
|     0 |  
|     1 |  
|     2 |  
|     3 |  
|     4 |  
\+——-+  
5 rows in set (0.00 sec)  
    执行结果和使用WHILE、LOOP编写的循环一样，都是往标中插入5行值。

Labels   标号和 END Labels 结束标号  
   在使用loop的时候，使用到的labels标号，对于labels可以用到while，loop，rrepeat等循环控制语句中。而且有必要好好认识一下lables!！  
mysql> create procedure pro13()  
    -> label\_1:begin  
    -> label\_2:while 0=1 do leave label\_2;end while;  
    -> label\_3:repeat leave label\_3;until 0=0 end repeat;  
    -> label\_4:loop leave label\_4;end loop;  
    -> end;//  
Query OK, 0 rows affected (0.00 sec)  
    上面这里例子显示了可以在BEGIN、WHILE、REPEAT或者LOOP语句前使用语句标号，语句标号只能在合法的语句前使用，所以LEAVE label\_3意味着离开语句标号名为label\_3的语句或符合语句。  
    其实，也可以使用END labels来表示标号结束符。  
mysql> create procedure pro14()  
    -> label\_1:begin  
    -> label\_2:while 0=1 do leave label\_2;end while label\_2;  
    -> label\_3:repeat leave label\_3;until 0=0 end repeat label\_3;  
    -> label\_4:loop leave label\_4;end loop label\_4;  
    -> end label\_1;//  
Query OK, 0 rows affected (0.00 sec)  
    上面就是使用了标号结束符，其实这个结束标号并不是十分有用，而且他必须和开始定义的标号名字一样，否则就会报错。如果要养成一个良好的编程习惯方便他人阅读的话，可以使用这个标号结束符。

ITERATE 迭代          
     如果是在ITERATE语句，即迭代语句中的话，就必须使用LEAVE语句。ITERATE只能出现在LOOP，REPEAT和WHILE语句中，它的意思是“再次循环”，例如：  
mysql> create procedure pro15()

-> begin  
    -> declare i int default 0;  
    -> loop\_label:loop  
    ->     if i=3 then  
    ->         set i=i+1;  
    ->         iterate loop\_label;  
    ->     end if;  
    ->     insert into t1(filed) values(i);  
    ->     set i=i+1;  
    ->     if i>=5 then  
    ->        leave loop\_label;  
    ->     end if;  
    ->   end loop;  
    -> end;//  
Query OK, 0 rows affected (0.00 sec)  
    iterate语句和leave语句一样，也是在循环内部使用，它有点类似于C/C++语言中的continue。  
    那么这个存储程序是怎么运行的的？首先i的值为0，条件判断语句if i=3 then判断为假，跳过if语段，向数据库中插入0，然后i+1，同样后面的if i>=5 then判断也为假，也跳过；继续循环，同样插入1和2；在i=3的时候条件判断语句if i=3 then判断为真，执行i=i+1，i值为4，然后执行迭代iterate loop\_label;，即语句执行到iterate loop\_label;后直接跳到if i=3 then判断语句，执行判断，这个时候由于i=4，if i=3 then判断为假，跳过IF语段，将4添加到表中，i变为5，条件判断if i>=5 then判断为真，执行leave loop\_label;跳出loop循环，然后执行end;//，结束整个存储过程。  
    综上所述，数据库中将插入数值：0,1,2,4。执行存储过程，并查看结果：|  
mysql> delete from t1//  
Query OK, 5 rows affected (0.00 sec)

mysql> call pro15//  
Query OK, 1 row affected (0.00 sec)

mysql> select \* from t1//  
\+——-+  
| filed |  
\+——-+  
|     0 |  
|     1 |  
|     2 |  
|     4 |  
\+——-+  
4 rows in set (0.00 sec)

和我们上面分析的结果一样，只插入了数值0,1,2,4。

存储过程如同一门程序设计语言，同样包含了数据类型、流程控制、输入和输出和它自己的函数库。

\--------------------基本语法--------------------

**一.创建存储过程**  
create procedure sp\_name()  
begin  
.........  
end

**二.调用存储过程**  
1.基本语法：call sp\_name()  
注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递

**三.删除存储过程**  
1.基本语法：  
drop procedure sp\_name//

2.注意事项  
(1)不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

**四.其他常用命令**

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

2.show create procedure sp\_name  
显示某一个[MySQL][Link 7]存储过程的详细信息

\--------------------数据类型及运算符--------------------  
**一、基本数据类型：**  
略

**二、变量：**

自定义变量：DECLARE   a INT ; SET a=100;    可用以下语句代替：DECLARE a INT DEFAULT 100;

变量分为*用户变量*和*系统变量*，系统变量又分为会话和全局级变量

用户变量：用户变量名一般以@开头，滥用用户变量会导致程序难以理解及管理

1、 在mysql客户端使用用户变量  
mysql> SELECT 'Hello World' into @x;  
mysql> SELECT @x;

mysql> SET @y='Goodbye Cruel World';  
mysql> select @y;

mysql> SET @z=1+2+3;  
mysql> select @z;

2、 在存储过程中使用用户变量

mysql> CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');  
mysql> SET @greeting='Hello';  
mysql> CALL GreetWorld( );

3、 在存储过程间传递全局范围的用户变量  
mysql> CREATE PROCEDURE p1( )   SET @last\_procedure='p1';  
mysql> CREATE PROCEDURE p2( ) SELECT CONCAT('Last procedure was ',@last\_procedure);  
mysql> CALL p1( );  
mysql> CALL p2( );

**三、运算符：**  
1.算术运算符  
\+     加   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

2.比较运算符 
>            大于 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

3.逻辑运算符

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

注释：

mysql存储过程可使用两种风格的注释  
双横杠：--

该风格一般用于单行注释  
c风格：/\* 注释内容 \*/ 一般用于多行注释

\--------------------流程控制--------------------  
**一、顺序结构  
二、分支结构**  
if  
case

**三、循环结构**  
for循环  
while循环  
loop循环  
repeat until循环

注：  
区块定义，常用  
begin  
......  
end;  
也可以给区块起别名，如：  
lable:begin  
...........  
end lable;  
可以用leave lable;跳出区块，执行区块以后的代码。

可以提前退出存储过程（函数直接return）。

begin和end如同C语言中的\{ 和 \}。

\--------------------输入和输出--------------------

**mysql存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT**  
Create procedure|function(\[\[IN |OUT |INOUT \] 参数名 数据类形...\])

**IN 输入参数**  
表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

**OUT 输出参数**  
该值可在存储过程内部被改变，并可返回

**INOUT 输入输出参数**  
调用时指定，并且可被改变和返回

IN参数例子：  
CREATE PROCEDURE sp\_demo\_in\_parameter(IN p\_in INT)  
BEGIN  
SELECT p\_in; --查询输入参数  
SET p\_in=2; --修改  
select p\_in;--查看修改后的值  
END;

执行结果:  
mysql> set @p\_in=1  
mysql> call sp\_demo\_in\_parameter(@p\_in)  
略  
mysql> select @p\_in;  
略  
以上可以看出，p\_in虽然在存储过程中被修改，但并不影响@p\_id的值

OUT参数例子  
创建:  
mysql> CREATE PROCEDURE sp\_demo\_out\_parameter(OUT p\_out INT)  
BEGIN  
SELECT p\_out;/\*查看输出参数\*/  
SET p\_out=2;/\*修改参数值\*/  
SELECT p\_out;/\*看看有否变化\*/  
END;

执行结果:  
mysql> SET @p\_out=1  
mysql> CALL sp\_demo\_out\_parameter(@p\_out)  
略

mysql> SELECT @p\_out;  
略

INOUT参数例子：  
mysql> CREATE PROCEDURE sp\_demo\_inout\_parameter(INOUT p\_inout INT)  
BEGIN  
SELECT p\_inout;  
SET p\_inout=2;  
SELECT p\_inout;  
END;

执行结果：  
set @p\_inout=1  
call sp\_demo\_inout\_parameter(@p\_inout) //  
略  
select @p\_inout;  
略

**附：函数库**  
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)

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

TRIM(\[\[BOTH|LEADING|TRAILING\] \[padding\] FROM\]string2) //去除指定位置的指定字符  
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 ,MINUTE ,MINUTE\_SECOND,MONTH ,SECOND ,YEAR  
DECLARE variable\_name \[,variable\_name...\] datatype \[DEFAULT value\];   
其中，datatype为mysql的数据类型，如:INT, FLOAT, DATE, VARCHAR(length)

例：

DECLARE l\_int INT unsigned default 4000000;   
DECLARE l\_numeric NUMERIC(8,2) DEFAULT 9.95;   
DECLARE l\_date DATE DEFAULT '1999-12-31';   
DECLARE l\_datetime DATETIME DEFAULT '1999-12-31 23:59:59';  
DECLARE l\_varchar VARCHAR(255) DEFAULT 'This will not be padded';

###  ORACLE与MYSQL的存储过程/函数的使用区别 ###

* <table> 
 <tbody> 
 <tr> 
 <td>编号</td> 
 <td>类别</td> 
 <td>ORACLE</td> 
 <td>MYSQL</td> 
 <td>注释</td> 
 </tr> 
 <tr> 
 <td>1</td> 
 <td>创建存储过程语句不同</td> 
 <td>create or replace procedure P_ADD_FAC( &nbsp;&nbsp;&nbsp;id_fac_cd&nbsp; IN ES_FAC_UNIT.FAC_CD%TYPE)&nbsp;is</td> 
 <td>DROP PROCEDURE IF EXISTS `SD_USER_P_ADD_USR`; create procedure P_ADD_FAC( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;id_fac_cd&nbsp; varchar(100))</td> 
 <td> 1.在创建存储过程时如果存在同名的存储过程,会删除老的存储过程.&nbsp; &nbsp; oracle使用create or replace. &nbsp; mysql使用先删除老的存储过程,然后再创建新的存储过程. 2. oracle 存储过程可以定义在package中,也可以定义在Procedures中. 如果定义在包中,一个包中可以包含多个存储过程和方法.如果定义在Procedures中,存储过程中不可以定义多个存储过程.&nbsp; &nbsp;&nbsp; Mysql&nbsp; 存储过程中不可以定义多个存储过程.&nbsp; 3. oracle中字符串类型可以使用varchar2.&nbsp;&nbsp; &nbsp;&nbsp; Mysql 需要使用varchar 4. Oracle中参数varchar长度不是必须的, &nbsp;&nbsp; Mysql中参数varchar长度是必须的, 比如varchar(100)&nbsp;</td> 
 </tr> 
 <tr> 
 <td>2</td> 
 <td>创建函数语句不同</td> 
 <td>CREATE OR REPLACEFUNCTION F_ROLE_FACS_GRP( &nbsp;&nbsp;&nbsp;&nbsp; ii_role_int_key IN SD_ROLE.ROLE_INT_KEY%TYPE &nbsp;&nbsp;&nbsp; )&nbsp;RETURN&nbsp;VARCHAR2</td> 
 <td>DROP FUNCTION IF EXISTS `SD_ROLE_F_ROLE_FACS_GRP`; CREATE&nbsp; FUNCTION `SD_ROLE_F_ROLE_FACS_GRP`( &nbsp;ii_role_int_key INTEGER(10) )&nbsp;RETURNS&nbsp;varchar(1000)&nbsp;</td> 
 <td>1.在创建函数时如果存在同名的函数,会删除老的函数.&nbsp;&nbsp; &nbsp; oracle使用create or replace. &nbsp; mysql使用先删除老的函数,然后再创建新的函数. 2. oracle 函数可以定义在package中,也可以定义在Functions中. 如果定义在包中,一个包中可以包含多个存储过程和函数.如果定义在Functions中,每个函数只能定义一个函数. &nbsp;&nbsp; Mysql&nbsp; Functions不可以定义多个函数.&nbsp; 3.&nbsp; oracle返回值用return.&nbsp; &nbsp;&nbsp;&nbsp; Mysql返回值用returns.&nbsp;</td> 
 </tr> 
 <tr> 
 <td>3</td> 
 <td>传入参数写法不同</td> 
 <td>procedure P_ADD_FAC( &nbsp;&nbsp;&nbsp;id_fac_cd&nbsp; IN ES_FAC_UNIT.FAC_CD%TYPE)</td> 
 <td>create procedure P_ADD_FAC( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(in) id_fac_cd&nbsp; varchar(100))</td> 
 <td> 1. oracle存储过程参数可以定义为表的字段类型. &nbsp;&nbsp; Mysql存储过程不支持这种定义方法.需要定义变量的实际类型和长度. 2. oracle 参数类型in/out/inout写在参数名后面.&nbsp; &nbsp;&nbsp; Mysql&nbsp; 参数类型in/out/inout写在参数名前面. 3. oracle 参数类型in/out/inout 都必须写. &nbsp;&nbsp; Mysql&nbsp; 参数类型如果是in,则可以省略. 如果是out或inout则不能省略. 注意: mysql中指定参数为IN, OUT, 或INOUT 只对PROCEDURE是合法的。（FUNCTION参数总是被认为是IN参数） RETURNS字句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。&nbsp;&nbsp; &nbsp;</td> 
 </tr> 
 <tr> 
 <td>function func_name( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;gw_id&nbsp; in(out)&nbsp; varchar2&nbsp;)</td> 
 <td>create function func_name( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;gw_id varchar（100))</td> 
 </tr> 
 <tr> 
 <td>4</td> 
 <td>包的声明方式</td> 
 <td>create or replace package/package body package name</td> 
 <td>拆分成多个存储过程或函数</td> 
 <td>oracle可以创建包,包中可以包含多个存储过程和方法.&nbsp; mysql没有没有包这个概念,可以分别创建存储过程和方法. 每个存储过程或方法都需要放在一个文件中.&nbsp; 例1: 方法命名&nbsp; oracle 中SD_FACILITY_PKG.F_SEARCH_FAC&nbsp; to mysql SD_FACILITY_F_SEARCH_FAC&nbsp; 例2: 过程命名 oracle 中SD_FACILITY_PKG.P_ADD_FAC to mysql SD_FACILITY_P_ADD_FAC&nbsp;</td> 
 </tr> 
 <tr> 
 <td>5</td> 
 <td>存储过程返回语句不一样</td> 
 <td>return;</td> 
 <td>LEAVE proc; (proc&nbsp;代表最外层的begin end)</td> 
 <td>oracle存储过程和方法都可以使用return退出当前过程和方法.&nbsp; Mysql存储过程中只能使用leave退出当前存储过程.不可以使用return.&nbsp; Mysql方法可以使用return退出当前方法.&nbsp;</td> 
 </tr> 
 <tr> 
 <td>6</td> 
 <td>存储过程异常处理不一样</td> 
 <td>EXCEPTION &nbsp;&nbsp;&nbsp; WHEN OTHERS THEN &nbsp;&nbsp;&nbsp; ROLLBACK ; &nbsp;&nbsp;&nbsp; ov_rtn_msg := c_sp_name||'('|| li_debug_pos ||'):'|| &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TO_CHAR(SQLCODE)||': '||SUBSTR(SQLERRM,1,100);</td> 
 <td>DECLARE EXIT HANDLER FOR&nbsp; SQLEXCEPTION&nbsp; &nbsp;BEGIN &nbsp;&nbsp;&nbsp; ROLLBACK ; &nbsp;&nbsp;&nbsp; set ov_rtn_msg = concat(c_sp_name,'(', li_debug_pos ,'):', &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TO_CHAR(SQLCODE),': ',SUBSTR(SQLERRM,1,100)); &nbsp;END;</td> 
 <td>oracle : 内部异常不需要定义,在存储过程或函数末尾写上EXCEPTION后,后面的部分即为异常处理的部分.&nbsp; oracle可以定义自定义异常,自定义异常需要使用raise关键字抛出异常后,才可以在EXCEPTION中捕获. mysql: mysql内部异常也需要先定义,在定义的同时也需要实现异常的功能.&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 目前mysql不支持自定义异常.&nbsp;</td> 
 </tr> 
 <tr> 
 <td>7</td> 
 <td>过程和函数的声明变量的位置不同</td> 
 <td>声明变量在begin…end体之前</td> 
 <td>声明变量在begin...end体内，begin之后其他任何内容之前</td> 
 <td>　</td> 
 </tr> 
 <tr> 
 <td>8</td> 
 <td>NO_DATA_FOUND异常处理</td> 
 <td>&nbsp;EXCEPTION &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHEN NO_DATA_FOUND THEN &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; oi_rtn_cd := 1; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ov_rtn_msg := SD_COMMON.P_GET_MSG('DP-CBM-01100a-016', &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; li_sub_rtn_cd, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; lv_sub_rtn_msg &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; );</td> 
 <td>使用FOUND_ROWS()代替NO_DATA_FOUND. 详见注释.</td> 
 <td>oracle中:&nbsp; NO_DATA_FOUND是游标的一个属性.&nbsp; 当select没有查到数据就会出现 no data found 的异常，程序不会向下执行. Mysql:&nbsp; 没有NO_DATA_FOUND这个属性.但可是使用FOUND_ROWS()方法得到select语句查询出来的数据.如果FOUND_ROWS()得到的值为0,就进入异常处理逻辑.&nbsp;</td> 
 </tr> 
 <tr> 
 <td>9</td> 
 <td>在存储过程中调用存储过程方式的不同</td> 
 <td>Procedure_Name(参数);</td> 
 <td>Call Procedure_Name(参数);</td> 
 <td>MYSQL存储过程调用存储过程，需要使用Call pro_name(参数).&nbsp;&nbsp; Oracle调用存储过程直接写存储过程名就可以了.&nbsp;</td> 
 </tr> 
 <tr> 
 <td>10</td> 
 <td>抛异常的方式不同</td> 
 <td>RAISE Exception_Name;</td> 
 <td>见备注</td> 
 <td>详见&lt;&lt;2009002-OTMPPS-Difficult Questions-0001.doc&gt;&gt;中2.5 Mysql异常处理部分</td> 
 </tr> 
 </tbody> 
 </table>
 
  
 * 异常处理
 * MySQL的GET DIAGNOSTICS语句 
 
 这是一个把我困扰已久的问题，今天偶然间解决了。 
 
 以前用Oracle时经常会用到的三个东西：sql%rowcount、SQLCODE、SQLERRM 
 sql%rowcount用于记录最近一条DML语句修改的记录条数，就如你在sqlplus下执行delete from之后提示已删除xx行一样。 
 SQLCODE和SQLERRM是Oracle的异常处理函数，常被用于得到完整错误提示信息，方便错误时处理。 
 
 那么问题来了，MySQL有没有相似的功能呢？以前查了很久也没有找到好的解决办法，然而在5.6.4以后，MySQL提供了GET DIAGNOSTICS语法，那么我的问题也随之迎刃而解。 
 
 简单讲GET DIAGNOSTICS能提供以下两种信息： 
 语句信息，例如错误信息号或者语句影响的行数。 
 错误信息，例如错误号和错误消息。 
  
 
 如果一条语句产生了三种错误，诊断区域包含的语句和错误信息类似这样：
 
  
 
 Statement information: row count ... other statement information items ... Condition area list: Condition area 1: error code for condition 1 error message for condition 1 ... other condition information items ... Condition area 2: error code for condition 2: error message for condition 2 ... other condition information items ... Condition area 3: error code for condition 3 error message for condition 3 ... other condition information items ...
 
 
 使用GET DIAGNOSTICS需要注意的是，它或者包含语句信息，或者包含错误信息，但一个GET DIAGNOSTICS不会同时包含语句信息和错误信息，所以需要用两个GET DIAGNOSTICS来获得语句信息和错误信息。 
 
 获得语句信息： 
 GET DIAGNOSTICS @p1 = NUMBER, @p2 = ROW\_COUNT; 
 
 获得错误信息： 
 GET DIAGNOSTICS CONDITION 1 @p3 = RETURNED\_SQLSTATE, @p4 = MESSAGE\_TEXT; 
 
 语句信息条目名称有： 
 NUMBER  
 | ROW\_COUNT 
 
 错误信息条目名称有： 
 CLASS\_ORIGIN  
 | SUBCLASS\_ORIGIN 
 | RETURNED\_SQLSTATE 
 | MESSAGE\_TEXT 
 | MYSQL\_ERRNO 
 | CONSTRAINT\_CATALOG 
 | CONSTRAINT\_SCHEMA 
 | CONSTRAINT\_NAME 
 | CATALOG\_NAME 
 | SCHEMA\_NAME 
 | TABLE\_NAME 
 | COLUMN\_NAME 
 | CURSOR\_NAME 
 
 为了确保获得正确的主错误信息，必须使用类似如下的语句： 
 GET DIAGNOSTICS @cno = NUMBER; 
 GET DIAGNOSTICS CONDITION @cno @errno = MYSQL\_ERRNO; 
 
 最后来看一个使用GET DIAGNOSTICS完整例子（摘自官方文档）
 * ![复制代码][copycode.gif]
 
 BEGIN 
 -- Declare variables to hold diagnostics area information 
 DECLARE code CHAR(5) DEFAULT '00000'; 
 DECLARE msg TEXT; 
 DECLARE rows INT; 
 DECLARE result TEXT; 
 
 -- Declare exception handler for failed insert 
 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
 BEGIN 
 GET DIAGNOSTICS CONDITION 1 code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT; 
 END; 
 -- Perform the insert 
 INSERT INTO t1 (int_col) VALUES(value); 
 
 -- Check whether the insert was successful 
 IF code = '00000' THEN 
 GET DIAGNOSTICS rows = ROW_COUNT; 
 SET result = CONCAT('insert succeeded, row count = ',rows); 
 ELSE 
 SET result = CONCAT('insert failed, error = ',code,', message = ',msg); 
 END IF; 
 -- Say what happened 
 SELECT result; 
 END
 
 ![复制代码][copycode.gif]
 
 结果：insert failed, error = 42S02, message = Table 'ud\_omcs.t1' doesn't exist

分类: [mysql][],[mysql存储过程函数事件][mysql 1]

标签: [mysql][mysql 2], [mysql存储过程函数事件][mysql 3]

好文要顶 关注我 收藏该文 ![icon_weibo_24.png][] ![wechat.png][]

[![u285763.jpg_id_28143750][]][u285763.jpg_id_28143750 1]

[duanxz][u285763.jpg_id_28143750 1]  
[关注 - 18][- 18]  
[粉丝 - 661][- 661]

\+加关注

[« ][Link 8]上一篇：[mysql事务之二：MySQL隔离级别演示][Link 8]  
[» ][Link 9]下一篇：[mysql分区表之二：MySQL的表的四种分区类型介绍][Link 9]

[https://www.cnblogs.com/duanxz/p/3936618.html][MySQL_3_ORACLE_MYSQL]

[Link 1]: https://www.cnblogs.com/duanxz/
[Link 2]: https://www.cnblogs.com/
[Link 3]: /images/20220405/9a295e7554bc45f6bda11d4ee452c411.pngEditPosts.aspx?opt=1
[Link 4]: https://msg.cnblogs.com/send/duanxz
[Link 5]: https://www.cnblogs.com/duanxz/rss
[xml.gif]: https://www.cnblogs.com/images/xml.gif
[Link 6]: /images/20220405/9a295e7554bc45f6bda11d4ee452c411.png
[MySQL_3_ORACLE_MYSQL]: https://www.cnblogs.com/duanxz/p/3936618.html
[copycode.gif]: /images/20220405/db6d14bfa5f746229a21a7ab9e87eae9.png
[Link 7]: http://lib.csdn.net/base/14
[mysql]: https://www.cnblogs.com/duanxz/category/383842.html
[mysql 1]: https://www.cnblogs.com/duanxz/category/901167.html
[mysql 2]: https://www.cnblogs.com/duanxz/tag/mysql/
[mysql 3]: https://www.cnblogs.com/duanxz/tag/mysql%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B%E5%87%BD%E6%95%B0%E4%BA%8B%E4%BB%B6/
[icon_weibo_24.png]: https://common.cnblogs.com/images/icon_weibo_24.png
[wechat.png]: /images/20220405/95537dff3f5f493486e6eb0d5db73cde.png
[u285763.jpg_id_28143750]: /images/20220405/a6c2148d0660424eaba31a14da02ef22.png
[u285763.jpg_id_28143750 1]: http://home.cnblogs.com/u/duanxz/
[- 18]: http://home.cnblogs.com/u/duanxz/followees
[- 661]: http://home.cnblogs.com/u/duanxz/followers
[Link 8]: https://www.cnblogs.com/duanxz/p/3851714.html
[Link 9]: https://www.cnblogs.com/duanxz/p/3825155.html