mysql存储过程详细教程

记录MYSQL存储过程中的关键语法：
DELIMITER //  声明语句结束符，用于区分;
CREATE PROCEDURE demo_in_parameter(IN p_in int)  声明存储过程
BEGIN .... END  存储过程开始和结束符号
SET @p_in=1   变量赋值

DECLARE l_int int unsigned default 4000000;  变量定义

什么是mysql存储例程?

存储例程是存储在数据库服务器中的一组sql语句，通过在查询中调用一个指定的名称来执行这些sql语句命令。

为什么要使用mysql存储过程？

我们都知道应用程序分为两种，一种是基于web，一种是基于桌面，他们都和数据库进行交互来完成数据的存取工作。假设现在有一种应用程序包含了这两 种，现在要修改其中的一个查询sql语句，那么我们可能要同时修改他们中对应的查询sql语句，当我们的应用程序很庞大很复杂的时候问题就出现这，不易维 护！另外把sql查询语句放在我们的web程序或桌面中很容易遭到sql注入的破坏。而存储例程正好可以帮我们解决这些问题。

存储过程(stored procedure)、存储例程(store routine)、存储函数区别

Mysql存储例程实际包含了存储过程和存储函数，它们被统称为存储例程。

其中存储过程主要完成在获取记录或插入记录或更新记录或删除记录，即完成select insert delete update等的工作。而存储函数只完成查询的工作，可接受输入参数并返回一个结果。

创建mysql存储过程、存储函数

create procedure 存储过程名(参数)

存储过程体

create function 存储函数名(参数)

下面是存储过程的例子：
mysql> DELIMITER // 
mysql> CREATE PROCEDURE proc1(OUT s int) 
   ->
BEGIN
   ->
SELECT COUNT(*) INTO s FROM user; 
   ->
END
   ->
// 
mysql> DELIMITER ;

注：

（1）这里需要注意的是DELIMITER//和DELIMITER;两句，DELIMITER是分割符的意思，因为MySQL默认以";"为分隔 符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当 前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。

（2）存储过程根据需要可能会有输入、输出、输入输出参数，这里有一个输出参数s，类型是int型，如果有多个参数用","分割开。

（3）过程体的开始与结束使用BEGIN与END进行标识。

这样，我们的一个MySQL存储过程就完成了，是不是很容易呢?看不懂也没关系，接下来，我们详细的讲解。

(2). 声明分割符

 

其实，关于声明分割符，上面的注解已经写得很清楚，不需要多说，只是稍微要注意一点的是：如果是用MySQL的Administrator管理工具时，可以直接创建，不再需要声明。

(3). 参数

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：

CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])

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

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

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

Ⅰ. IN参数例子

创建: 

1.  mysql > DELIMITER //  
2.  mysql > CREATE PROCEDURE demo_in_parameter(IN p_in int)  
3.       -> BEGIN   
4.       -> SELECT p_in;   
5.       -> SET p_in=2;   
6.       -> SELECT p_in;   
7.       -> END;   
8.       -> //  
9.  mysql > DELIMITER ; 

执行结果:

1.  mysql > SET @p_in=1;  
2.  mysql > CALL demo_in_parameter(@p_in);  
3.  +------+  
4.  | p_in |  
5.  +------+  
6.  |   1  |   
7.  +------+  
8.   
9.  +------+  
10.| p_in |  
11.+------+  
12.|   2  |   
13.+------+  
14. 
15.mysql> SELECT @p_in;  
16.+-------+  
17.| @p_in |  
18.+-------+  
19.|  1    |  
20.+-------+  

以上可以看出，p_in虽然在存储过程中被修改，但并不影响@p_id的值

 

Ⅱ.OUT参数例子

创建:

1.  mysql > DELIMITER //  
2.  mysql > CREATE PROCEDURE demo_out_parameter(OUT p_out int)  
3.       -> BEGIN 
4.       -> SELECT p_out;  
5.       -> SET p_out=2;  
6.       -> SELECT p_out;  
7.       -> END;  
8.       -> //  
9.  mysql > DELIMITER ; 

执行结果:

1.  mysql > SET @p_out=1;  
2.  mysql > CALL sp_demo_out_parameter(@p_out);  
3.  +-------+  
4.  | p_out |   
5.  +-------+  
6.  | NULL  |   
7.  +-------+  
8.   
9.  +-------+  
10.| p_out |  
11.+-------+  
12.|   2   |   
13.+-------+  
14. 
15.mysql> SELECT @p_out;  
16.+-------+  
17.| p_out |  
18.+-------+  
19.|   2   |  
20.+-------+  

Ⅲ. INOUT参数例子

创建:

1.  mysql > DELIMITER //   
2.  mysql > CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)   
3.       -> BEGIN 
4.       -> SELECT p_inout;  
5.       -> SET p_inout=2;  
6.       -> SELECT p_inout;   
7.       -> END;  
8.       -> //   
9.  mysql > DELIMITER ; 

 

 

执行结果:

1.  mysql > SET @p_inout=1;  
2.  mysql > CALL demo_inout_parameter(@p_inout) ;  
3.  +---------+  
4.  | p_inout |  
5.  +---------+  
6.  |    1    |  
7.  +---------+  
8.   
9.  +---------+  
10.| p_inout |   
11.+---------+  
12.|    2    |  
13.+---------+  
14. 
15.mysql > SELECT @p_inout;  
16.+----------+  
17.| @p_inout |   
18.+----------+  
19.|    2     |  
20.+----------+ 

 

(4). 变量

Ⅰ. 变量定义

局部变量声明一定要放在存储过程体的开始

DECLAREvariable_name [,variable_name...] datatype [DEFAULT value];

其中，datatype为MySQL的数据类型，如:int, float, date,varchar(length)

例如:

1.  DECLARE l_int int unsigned default 4000000;  
2.  DECLARE l_numeric number(8,2) DEFAULT 9.95;  
3.  DECLARE l_date date DEFAULT '1999-12-31';  
4.  DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';  
5.  DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';   

 

 

Ⅱ. 变量赋值

 SET 变量名 = 表达式值 [,variable_name = expression ...]

 

Ⅲ. 用户变量

 

ⅰ. 在MySQL客户端使用用户变量

1.  mysql > SELECT 'Hello World' into @x;  
2.  mysql > SELECT @x;  
3.  +-------------+  
4.  |   @x        |  
5.  +-------------+  
6.  | Hello World |  
7.  +-------------+  
8.  mysql > SET @y='Goodbye Cruel World';  
9.  mysql > SELECT @y;  
10.+---------------------+  
11.|     @y              |  
12.+---------------------+  
13.| Goodbye Cruel World |  
14.+---------------------+  
15. 
16.mysql > SET @z=1+2+3;  
17.mysql > SELECT @z;  
18.+------+  
19.| @z   |  
20.+------+  
21.|  6   |  
22.+------+  

ⅱ. 在存储过程中使用用户变量

1.  mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');  
2.  mysql > SET @greeting='Hello';  
3.  mysql > CALL GreetWorld( );  
4.  +----------------------------+  
5.  | CONCAT(@greeting,' World') |  
6.  +----------------------------+  
7.  |  Hello World               |  
8.  +----------------------------+  

ⅲ. 在存储过程间传递全局范围的用户变量

1.  mysql> CREATE PROCEDURE p1()   SET @last_procedure='p1';  
2.  mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_procedure);  
3.  mysql> CALL p1( );  
4.  mysql> CALL p2( );  
5.  +-----------------------------------------------+  
6.  | CONCAT('Last procedure was ',@last_proc       |  
7.  +-----------------------------------------------+  
8.  | Last procedure was p1                         |  
9.  +-----------------------------------------------+  

 

 

注意:

①用户变量名一般以@开头

②滥用用户变量会导致程序难以理解及管理

 

(5). 注释

 

MySQL存储过程可使用两种风格的注释

双模杠：--

该风格一般用于单行注释

c风格： 一般用于多行注释

例如：

 

1.  mysql > DELIMITER //  
2.  mysql > CREATE PROCEDURE proc1 --name存储过程名  
3.       -> (IN parameter1 INTEGER)   
4.       -> BEGIN   
5.       -> DECLARE variable1 CHAR(10);   
6.       -> IF parameter1 = 17 THEN   
7.       -> SET variable1 = 'birds';   
8.       -> ELSE 
9.       -> SET variable1 = 'beasts';   
10.     -> END IF;   
11.     -> INSERT INTO table1 VALUES (variable1);  
12.     -> END   
13.     -> //  
14.mysql > DELIMITER ;  

 

4.      MySQL存储过程的调用

用call和你过程名以及一个括号，括号里面根据需要，加入参数，参数包括输入参数、输出参数、输入输出参数。具体的调用方法可以参看上面的例子。

5.      MySQL存储过程的查询

我们像知道一个数据库下面有那些表，我们一般采用showtables;进行查看。那么我们要查看某个数据库下面的存储过程，是否也可以采用呢？答案是，我们可以查看某个数据库下面的存储过程，但是是令一钟方式。

我们可以用

selectname from mysql.proc where db=’数据库名’;

或者

selectroutine_name from information_schema.routines where routine_schema='数据库名';

或者

showprocedure status where db='数据库名';

进行查询。

如果我们想知道，某个存储过程的详细，那我们又该怎么做呢？是不是也可以像操作表一样用describe 表名进行查看呢？

答案是：我们可以查看存储过程的详细，但是需要用另一种方法：

SHOWCREATE PROCEDURE 数据库.存储过程名;

就可以查看当前存储过程的详细。

 

6.      MySQL存储过程的修改

ALTER PROCEDURE

更改用CREATE PROCEDURE 建立的预先指定的存储过程，其不会影响相关存储过程或存储功能。

 

7.      MySQL存储过程的删除

删除一个存储过程比较简单，和删除表一样：

DROPPROCEDURE

从MySQL的表格中删除一个或多个存储过程。

 

8.      MySQL存储过程的控制语句

(1). 变量作用域

内部的变量在其作用域范围内享有更高的优先权，当执行到end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储
过程外再也不能找到这个申明的变量，但是你可以通过out参数或者将其值指派
给会话变量来保存其值。

 

 

1.  mysql > DELIMITER //  
2.  mysql > CREATE PROCEDURE proc3()  
3.       -> begin 
4.       -> declare x1 varchar(5) default 'outer';  
5.       -> begin 
6.       -> declare x1 varchar(5) default 'inner';  
7.       -> select x1;  
8.       -> end;  
9.       -> select x1;  
10.     -> end;  
11.     -> //  
12.mysql > DELIMITER ;  

 

 (2). 条件语句

Ⅰ. if-then -else语句 

1.  mysql > DELIMITER //  
2.  mysql > CREATE PROCEDURE proc2(IN parameter int)  
3.       -> begin 
4.       -> declare var int;  
5.       -> set var=parameter+1;  
6.       -> if var=0 then 
7.       -> insert into t values(17);  
8.       -> end if;  
9.       -> if parameter=0 then 
10.     -> update t set s1=s1+1;  
11.     -> else 
12.     -> update t set s1=s1+2;  
13.     -> end if;  
14.     -> end;  
15.     -> //  
16.mysql > DELIMITER ;  

Ⅱ. case语句： 

1.  mysql > DELIMITER //  
2.  mysql > CREATE PROCEDURE proc3 (in parameter int)  
3.       -> begin 
4.       -> declare var int;  
5.       -> set var=parameter+1;  
6.       -> case var  
7.       -> when 0 then   
8.       -> insert into t values(17);  
9.       -> when 1 then   
10.     -> insert into t values(18);  
11.     -> else   
12.     -> insert into t values(19);  
13.     -> end case;  
14.     -> end;  
15.     -> //  
16.mysql > DELIMITER ; 
case
        when var=0 then
               insert into t values(30);
        when var>0 then
        when var<0 then
        else
              
end case
 
 

 

(3). 循环语句

Ⅰ. while ···· end while：

1.  mysql > DELIMITER //  
2.  mysql > CREATE PROCEDURE proc4()  
3.       -> begin 
4.       -> declare var int;  
5.       -> set var=0;  
6.       -> while var<6 do  
7.       -> insert into t values(var);  
8.       -> set var=var+1;  
9.       -> end while;  
10.     -> end;  
11.     -> //  
12.mysql > DELIMITER ; 

 while条件 do

--循环体

endwhile

 

 

Ⅱ. repeat···· end repeat：

它在执行操作后检查结果，而while则是执行前进行检查。

1.  mysql > DELIMITER //  
2.  mysql > CREATE PROCEDURE proc5 ()  
3.       -> begin   
4.       -> declare v int;  
5.       -> set v=0;  
6.       -> repeat  
7.       -> insert into t values(v);  
8.       -> set v=v+1;  
9.       -> until v>=5  
10.     -> end repeat;  
11.     -> end;  
12.     -> //  
13.mysql > DELIMITER ;  

 repeat

--循环体

until循环条件     

endrepeat;

 

Ⅲ. loop ·····endloop:

loop循环不需要初始条件，这点和while 循环相似，同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环。

1.  mysql > DELIMITER //  
2.  mysql > CREATE PROCEDURE proc6 ()  
3.       -> begin 
4.       -> declare v int;  
5.       -> set v=0;  
6.       -> LOOP_LABLE:loop  
7.       -> insert into t values(v);  
8.       -> set v=v+1;  
9.       -> if v >=5 then 
10.     -> leave LOOP_LABLE;  
11.     -> end if;  
12.     -> end loop;  
13.     -> end;  
14.     -> //  
15.mysql > DELIMITER ;  

 

 

Ⅳ. LABLES 标号：

标号可以用在begin repeat while 或者loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。

 

(4). ITERATE迭代

Ⅰ. ITERATE:

1.    通过引用复合语句的标号,来从新开始复合语句
2.  mysql > DELIMITER //  
3.  mysql > CREATE PROCEDURE proc10 ()  
4.       -> begin 
5.       -> declare v int;  
6.       -> set v=0;  
7.       -> LOOP_LABLE:loop  
8.      -> if v=3 then   
9.      -> set v=v+1;  
10.        -> ITERATE LOOP_LABLE;  
11.        -> end if;  
12.     -> insert into t values(v);  
13.     -> set v=v+1;  
14.        -> if v>=5 then 
15.        -> leave LOOP_LABLE;  
16.     -> end if;  
17.     -> end loop;  
18.     -> end;  
19.     -> //  
20.mysql > DELIMITER ; 

 

 

9.      MySQL存储过程的基本函数

 

(1).字符串类

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 

1.  mysql> select substring('abcd',0,2);  
2.  +-----------------------+  
3.  | substring('abcd',0,2) |  
4.  +-----------------------+  
5.  |                       |  
6.  +-----------------------+  
7.  1 row in set (0.00 sec)  
8.   
9.  mysql> select substring('abcd',1,2);  
10.+-----------------------+  
11.| substring('abcd',1,2) |  
12.+-----------------------+  
13.|     ab                |  
14.+-----------------------+  
15.1 row in set (0.02 sec)  

TRIM([[BOTH|LEADING|TRAILING][padding] FROM]string2) //去除指定位置的指定字符
UCASE (string2 ) //转换成大写
RIGHT(string2,length) //取string2最后length个字符
SPACE(count) //生成count个空格

(2).数学类

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)默认变为整形值

1.  mysql> select round(1.23);  
2.  +-------------+  
3.  | round(1.23) |  
4.  +-------------+  
5.  |           1 |  
6.  +-------------+  
7.  1 row in set (0.00 sec)  
8.   
9.  mysql> select round(1.56);  
10.+-------------+  
11.| round(1.56) |  
12.+-------------+  
13.|           2 |  
14.+-------------+  
15.1 row in set (0.00 sec) 

(2)可以设定小数位数，返回浮点型数据

1.  mysql> select round(1.567,2);  
2.  +----------------+  
3.  | round(1.567,2) |  
4.  +----------------+  
5.  |           1.57 |  
6.  +----------------+  
7.  1 row in set (0.00 sec) 

SIGN (number2 ) //

 

(3).日期时间类

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) //分返回符号,正负或0
SQRT(number2) //开平方

 

 

 

 

 

MySql分页存储过程

MySql测试版本：5.0.41-community-nt

DROP PROCEDURE IF EXISTS pr_pager;
CREATE PROCEDURE pr_pager(

    IN    p_table_name        VARCHAR(1024),        
    IN    p_fields            VARCHAR(1024),        
    IN    p_page_size            INT,                
    IN    p_page_now            INT,                
    IN    p_order_string        VARCHAR(128),        
    IN    p_where_string        VARCHAR(1024),        
     OUT    p_out_rows            INT                    
    
)
    NOT DETERMINISTIC
     SQL SECURITY DEFINER
     COMMENT '分页存储过程'
    
BEGIN

    
    DECLARE m_begin_row INT DEFAULT 0;
    DECLARE m_limit_string CHAR(64);

        
    SET m_begin_row = (p_page_now - 1) * p_page_size;
    SET m_limit_string = CONCAT(' LIMIT ', m_begin_row, ', ', p_page_size);
    
    SET @COUNT_STRING = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ', p_table_name, ' ', p_where_string);
    SET @MAIN_STRING = CONCAT('SELECT ', p_fields, ' FROM ', p_table_name, ' ', p_where_string, ' ', p_order_string,m_limit_string);

    
    PREPARE count_stmt FROM @COUNT_STRING;
    EXECUTE count_stmt;
    DEALLOCATE PREPARE count_stmt;
    SET p_out_rows = @ROWS_TOTAL;

    PREPARE main_stmt FROM @MAIN_STRING;
    EXECUTE main_stmt;
    DEALLOCATE PREPARE main_stmt;
    
END;

 

 

调用

mysql> call pr_pager("t","var",3,3,"","",@result);

mysql> call pr_pager("t","var",3,2,"","",@result);

  MySQL5 中添加了存储过程的支持。

    大多数SQL语句都是针对一个或多个表的单条语句。并非所有的操作都怎么简单。经常会有一个完整的操作需要多条才能完成

    存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件。虽然他们的作用不仅限于批处理。

    为什么要使用存储过程：优点

        1 通过吧处理封装在容易使用的单元中，简化复杂的操作

        2 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果开发人员和应用程序都使用了同一存储过程，则所使用的代码是相同的。还有就是防止错误，需要执行的步骤越多，出错的可能性越大。防止错误保证了数据的一致性。

        3 简化对变动的管理。如果表名、列名或业务逻辑有变化。只需要更改存储过程的代码，使用它的人员不会改自己的代码了都。

        4 提高性能，因为使用存储过程比使用单条SQL语句要快

        5 存在一些职能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码

        换句话说3个主要好处简单、安全、高性能

    缺点

        1 一般来说，存储过程的编写要比基本的SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。

        2 你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建，允许用户使用存储过程，但不允许创建存储过程

    存储过程是非常有用的，应该尽可能的使用它们

    执行存储过程

        MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL        .CALL接受存储过程的名字以及需要传递给它的任意参数

            CALL
productpricing(@pricelow , @pricehigh , @priceaverage);

            //执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格

    创建存储过程

        CREATE  PROCEDURE 存储过程名()

           一个例子说明：一个返回产品平均价格的存储过程如下代码：

           CREATE  PROCEDURE  productpricing()

           BEGIN

            SELECT
Avg(prod_price)  AS priceaverage

           FROM products;

           END;

        //创建存储过程名为productpricing，如果存储过程需要接受参数，可以在（）中列举出来。即使没有参数后面仍然要跟（）。BEGIN和END语句用来限定存储过程体，过程体本身是个简单的SELECT语句

        在MYSQL处理这段代码时会创建一个新的存储过程productpricing。没有返回数据。因为这段代码时创建而不是使用存储过程。

 

    Mysql命令行客户机的分隔符

        默认的MySQL语句分隔符为分号 ; 。Mysql命令行实用程序也是 ; 作为语句分隔符。如果命令行实用程序要解释存储过程自身的 ; 字符，则他们最终不会成为存储过程的成分，这会使存储过程中的SQL出现句法错误

        解决方法是临时更改命令实用程序的语句分隔符

            DELIMITER
//    //定义新的语句分隔符为//

            CREATE
PROCEDURE productpricing()

            BEGIN

            SELECT
Avg(prod_price) AS priceaverage

            FROM products;

            END //

            DELIMITER
;    //改回原来的语句分隔符为 ;

            除\符号外，任何字符都可以作为语句分隔符

        CALL
productpricing();  //使用productpricing存储过程

        执行刚创建的存储过程并显示返回的结果。因为存储过程实际上是一种函数，所以存储过程名后面要有()符号

    删除存储过程

        DROP
PROCEDURE productpricing ;     //删除存储过程后面不需要跟(),只给出存储过程名

        为了删除存储过程不存在时删除产生错误，可以判断仅存储过程存在时删除

        DROP
PROCEDURE IF EXISTS

        使用参数

        Productpricing只是一个简单的存储过程，他简单地显示SELECT语句的结果。

        一般存储过程并不显示结果，而是把结果返回给你指定的变量

            CREATE
PROCEDURE productpricing(

            OUT p1 DECIMAL(8,2),

            OUT ph DECIMAL(8,2),

            OUT pa DECIMAL(8,2),

            )

            BEGIN

            SELECT
Min(prod_price)

            INTO p1

            FROM products;

            SELECT
Max(prod_price)

            INTO ph

            FROM products;

            SELECT Avg(prod_price)

            INTO pa

            FROM
products;

            END;

            此存储过程接受3个参数，p1存储产品最低价格，ph存储产品最高价格，pa存储产品平均价格。每个参数必须指定类型，这里使用十进制值。关键字OUT指出相应的参数用来从存储过程传给一个值（返回给调用者）。MySQL支持IN（传递给存储过程）、OUT（从存储过程中传出、如这里所用）和INOUT（对存储过程传入和传出）类型的参数。存储过程的代码位于BEGIN和END语句内，如前所见，它们是一些列SELECT语句，用来检索值，然后保存到相应的变量（通过INTO关键字）

        调用修改过的存储过程必须指定3个变量名：

        CALL
productpricing(@pricelow , @pricehigh , @priceaverage);

        这条CALL语句给出3个参数，它们是存储过程将保存结果的3个变量的名字

    变量名  所有的MySQL变量都必须以@开始

    使用变量

        SELECT
@priceaverage ;

        SELECT
@pricelow , @pricehigh , @priceaverage ;   //获得3给变量的值

        下面是另一个例子，这次使用IN和OUT参数。ordertotal接受订单号，并返回该订单的合计

            CREATE
PROCEDURE ordertotal(

           IN
onumber INT,

           OUT
ototal DECIMAL(8,2)

            )

            BEGIN

            SELECT
Sum(item_price*quantity)

            FROM orderitems

            WHERE order_num
= onumber

            INTO
ototal;

            END;

            //onumber定义为IN，因为订单号时被传入存储过程，ototal定义为OUT，因为要从存储过程中返回合计，SELECT语句使用这两个参数，WHERE子句使用onumber选择正确的行，INTO使用ototal存储计算出来的合计

    为了调用这个新的过程，可以使用下列语句：

        CALL ordertotal(2005
, @total);   //这样查询其他的订单总计可直接改变订单号即可

        SELECT @total;

    建立智能的存储过程

        上面的存储过程基本都是封装MySQL简单的SELECT语句，但存储过程的威力在它包含业务逻辑和智能处理时才显示出来

        例如：你需要和以前一样的订单合计，但需要对合计增加营业税，不活只针对某些顾客（或许是你所在区的顾客）。那么需要做下面的事情：

            1 获得合计（与以前一样）

            2 吧营业税有条件地添加到合计

            3 返回合计（带或不带税）

        存储过程的完整工作如下：

            --
Name: ordertotal

            --
Parameters: onumber = 订单号

            --           taxable
= 1为有营业税 0 为没有

            --           ototal
= 合计

            CREATE  PROCEDURE ordertotal(

            IN onumber
INT,

            IN taxable
BOOLEAN,

            OUT ototal
DECIMAL(8,2)

            --
COMMENT()中的内容将在SHOW PROCEDURE STATUS ordertotal()中显示，其备注作用

            ) COMMENT 'Obtain
order total , optionally adding tax'

            BEGIN

            -- 定义total局部变量

            DECLARE total
DECIMAL(8,2)

            DECLARE taxrate
INT DEFAULT 6;

 

            -- 获得订单的合计，并将结果存储到局部变量total中

            SELECT
Sum(item_price*quantity)

            FROM orderitems

            WHERE order_num
= onumber

            INTO total;

 

            -- 判断是否需要增加营业税，如为真，这增加6%的营业税

            IF taxable THEN

            SELECT total+(total/100*taxrate) INTO total;

                  END
IF;

            -- 把局部变量total中才合计传给ototal中

            SELECT total INTO ototal;

            END;

            此存储过程有很大的变动，首先，增加了注释（前面放置--）。在存储过程复杂性增加时，这样很重要。在存储体中，用DECLARE语句定义了两个局部变量。DECLARE要求制定变量名和数据类型，它也支持可选的默认值（这个例子中taxrate的默认设置为6%），SELECT 语句已经改变，因此其结果存储到total局部变量中而不是ototal。IF语句检查taxable是否为真，如果为真，则用另一SELECT语句增加营业税到局部变量total，最后用另一SELECT语句将total（增加了或没有增加的）保存到ototal中。

    COMMENT关键字  本列中的存储过程在CREATE
PROCEDURE 语句中包含了一个COMMENT值，他不是必需的，但如果给出，将在SHOW
PROCEDURE STATUS的结果中显示

    IF语句   这个例子中给出了MySQL的IF语句的基本用法。IF语句还支持ELSEIF和ELSE子句（前者还使用THEN子句，后者不使用）

    检查存储过程

        为显示用来创建一个存储过程的CREATE语句，使用SHOW
CREATE PROCEDURE语句

            SHOW
CREATE PROCEDURE ordertotal;

        为了获得包括何时、有谁创建等详细信息的存储过程列表。使用SHOW
PROCEDURE STATUS.限制过程状态结果，为了限制其输出，可以使用LIKE指定一个过滤模式，例如：SHOW
PROCEDURE STATUS LIKE ''ordertotal;

第二十四章  使用游标

    MySQL5添加了对游标的支持

    只能用于存储过程

    由前几章可知，mysql检索操作返回一组称为结果集的行。都与mysql语句匹配的行（0行或多行），使用简单的SELECT语句，没有办法得到第一行、下一行或前10行，也不存在每次行地处理所有行的简单方法（相对于成批处理他们）

    有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。游标（cursor）是一个存储在MYSQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

    游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

    使用游标

        使用游标涉及几个明确的步骤：

            1 在能够使用游标前，必须声明（定义）它，这个过程实际上没有检索数据，它只是定义要使用的SELECT语句

            2 一旦声明后，必须打开游标以供使用。这个过程用钱吗定义的SELECT语句吧数据实际检索出来

            3 对于填有数据的游标，根据需要取出（检索）的各行

            4 在接受游标使用时，必须关闭它 如果不明确关闭游标，MySQL将会在到达END语句时自动关闭它

    创建游标

        游标可用DECLARE 语句创建。 DECLARE命名游标，并定义相应的SELECT语句。根据需要选择带有WHERE和其他子句。如：下面第一名为ordernumbers的游标，使用了检索所有订单的SELECT语句

            CREATE
PROCEDURE processorders()

            BEGIN

            DECLARE ordernumbers CURSOR

            FOR

            SELECT order_num FROM orders
;

            END;

            存储过程处理完成后，游标就消失，因为它局限于存储过程

    打开和关闭游标

            CREATE
PROCEDURE processorders()

            BEGIN

            DECLAREordernumbers CURSOR

            FOR

            SELECT order_num FROM orders
;

            Open
ordernumbers ;

            Close
ordernumbers ;  //CLOSE释放游标使用的所有内部内存和资源，因此，每个游标不需要时都应该关闭

            END;

    使用游标数据

        在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据（所需的要列），检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行，相当于PHP中的each()函数

循环检索数据，从第一行到最后一行

            CREATE
PROCEDURE processorders()

            BEGIN

            -- 声明局部变量

            DECLARE done BOOLEAN
DEFAULT 0;

            DECLARE o INT;

 

            DECLAREordernumbers CURSOR

            FOR

            SELECT order_num FROM orders
;

            -- 当SQLSTATE为02000时设置done值为1

            DECLARE
CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

            --打开游标

            Open ordernumbers
;

            -- 开始循环

            REPEAT

            -- 把当前行的值赋给声明的局部变量o中

            FETCH ordernumbers INTO o;

            -- 当done为真时停止循环

            UNTIL done END
REPEAT;

            --关闭游标

            Close ordernumbers
;  //CLOSE释放游标使用的所有内部内存和资源，因此，每个游标不需要时都应该关闭

            END;

        语句中定义了CONTINUE
HANDLER ,它是在条件出现时被执行的代码。这里，它指出当SQLSTATE '02000'出现时，SET
done=1。SQLSTATE '02000'是一个未找到条件，当REPEAT没有更多的行供循环时，出现这个条件。

    DECLARE 语句次序  用DECLARE语句定义局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标之后定义。不遵守此规则就会出错

重复和循环   除这里使用REPEAT语句外，MySQL还支持循环语句，它可用来重复执行代码，直到使用LEAVE语句手动退出为止。通常REPEAT语句的语法使它更适合于对游标进行的循环。

为了把这些内容组织起来，这次吧取出的数据进行某种实际的处理

        CREATE
PROCEDURE processorders()

        BEGIN

        -- 声明局部变量

        DECLARE done BOOLEAN
DEFAULT 0;

        DECLARE o INT;

        DECLARE t DECIMAL(8,2)

 

        DECLAREordernumbers CURSOR

        FOR

        SELECT order_num FROM orders
;

        -- 当SQLSTATE为02000时设置done值为1

        DECLARE
CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

        -- 创建一个ordertotals的表

        CREATE
TABLE IF NOT EXISTS ordertotals( order_num INT , total DECIMAL(8,2))

        --打开游标

        Open ordernumbers
;

        -- 开始循环

        REPEAT

        -- 把当前行的值赋给声明的局部变量o中

        FETCH ordernumbers INTO o;

        -- 用上文讲到的ordertotal存储过程并传入参数，返回营业税计算后的合计传给t变量

        CALL ordertotal(o
, 1 ,t)

        -- 把订单号和合计插入到新建的ordertotals表中

        INSERT
INTO ordertotals(order_num, total) VALUES(o , t);

        -- 当done为真时停止循环

        UNTIL done END
REPEAT;

        --关闭游标

        Close ordernumbers
;  //CLOSE释放游标使用的所有内部内存和资源，因此，每个游标不需要时都应该关闭

        END;

        最后SELECT
* FROM ordertotals就能查看结果了

第二十五章 使用触发器

    MySQL5版本后支持触发器

    只有表支持触发器，视图不支持触发器

    MySQL语句在需要的时被执行，存储过程也是如此，但是如果你想要某条语句（或某些语句）在事件发生时自动执行，那该怎么办呢：例如：

        1 每增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确，区的缩写是否为大写

        2 每当订购一个产品时，都从库存数量中减少订购的数量

        3 无论何时删除一行，都在某个存档中保留一个副本

    这写例子的共同之处是他们都需要在某个表发生更改时自动处理。这就是触发器。触发器是MySQL响应一下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）

    1 DELETE

    2 INSERT

    3 UPDATE

    其他的MySQL语句不支持触发器

    创建触发器

        创建触发器需要给出4条信息

        1 唯一的触发器名；  //保存每个数据库中的触发器名唯一

        2 触发器关联的表；

        3 触发器应该响应的活动（DELETE、INSERT或UPDATE）

        4 触发器何时执行（处理前还是后,前是BEFORE 后是AFTER）

        创建触发器用CREATE
TRIGGER

        CREATE
TRIGGER newproduct AFTER INSERT ON products

        FOR
EACH ROW SELECT'Product added'

       创建新触发器newproduct ，它将在INSERT语句成功执行后执行。这个触发器还镇定FOR
EACH ROW,因此代码对每个插入的行执行。这个例子作用是文本对每个插入的行显示一次product added

        FOR
EACH ROW 针对每个行都有作用，避免了INSERT一次插入多条语句

    触发器定义规则

        触发器按每个表每个事件每次地定义，每个表每个事件每次只允许定义一个触发器，因此，每个表最多定义6个触发器（每条INSERT
UPDATE 和DELETE的之前和之后）。单个触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT 和UPDATE存储执行的触发器，则应该定义两个触发器

    触发器失败  如果BEFORE(之前)触发器失败，则MySQL将不执行SQL语句的请求操作，此外，如果BEFORE触发器或语句本身失败，MySQL将不执行AFTER(之后)触发器

    删除触发器

        DROP
TRIGGER newproduct；

        触发器不能更新或覆盖，所以修改触发器只能先删除再创建

    使用触发器

        我们来看看每种触发器以及它们的差别

    INSERT 触发器

        INSERT触发器在INSERT语句执行之前或之后执行。需要知道以下几点：

    1 在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行

    2 在BEFORE
INSERT触发器中，NEW中的值也可以被更新（允许更改插入的值）

    3 对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值

        提示：通常BEFORE用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据）。本提示也适用于UPDATE触发器

    DELETE 触发器

        DELETE触发器在语句执行之前还是之后执行，需要知道以下几点：

    1 在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；

    2 OLD中的值全部是只读的，不能更新

        例子演示适用OLD保存将要除的行到一个存档表中

        CREATE
TRIGGERdeleteorder BEFORE DELETE ON orders

        FOR
EACH ROW

        BEGIN  

        INSERT
INTO archive_orders(order_num , order_date , cust_id)

        VALUES(OLD.order_num
, OLD.order_date , OLD.cust_id);

        END;

        //此处的BEGIN  END块是非必需的，可以没有

    在任何订单删除之前执行这个触发器，它适用一条INSERT语句将OLD中的值（将要删除的值）保存到一个名为archive_orders的存档表中

    BEFORE DELETE触发器的优点是（相对于AFTER
DELETE触发器），如果由于某种原因，订单不能被存档，DELETE本身将被放弃执行。

    多语言触发器  正如上面所见，触发器deleteorder 使用了BEGIN和END语句标记触发器体。这在此例中并不是必需的，不过也没有害处。使用BEGIN  END块的好处是触发器能容纳多条SQL语句。

    UPDATE触发器

        UPDATE触发器在语句执行之前还是之后执行，需要知道以下几点：

        1 在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问（UPDATE语句前）的值，引用一名为NEW的虚拟表访问新更新的值

        2 在BEFORE
UPDATE触发器中，NEW中的值可能被更新，（允许更改将要用于UPDATE语句中的值）

        3
OLD中的值全都是只读的，不能更新

            例子：保证州名的缩写总是大写（不管UPDATE语句给出的是大写还是小写）

            CREATE
TRIGGER updatevendor BEFORE UPDATE ON vendores FOR EACH
ROW SETNEW.vend_state = Upper(NEW.vend_state)

    触发器的进一步介绍

    1 与其他DBMS相比，MySQL5中支持的触发器相当初级。以后可能会增强

    2 创建触发器可能需要特殊的安全访问权限，但是触发器的执行时自动的.如果INSERT
UPDATE DELETE能执行，触发器就能执行

    3 应该用触发器来保证数据的一致性（大小写、格式等）。在触发器中执行这种类型的处理的优点是它总是进行这个处理，而且是透明地进行，与客户机应用无关

    4 触发器的一种非常有意义的使用创建审计跟踪。使用触发器把更改（如果需要，甚至还有之前和之后的状态）记录到另一表非常容易

    5 遗憾的是，MySQL触发器中不支持CALL语句，这表示不能从触发器中调用存储过程。所需要的存储过程代码需要复制到触发器内

时间： 2024-09-13 08:16:47

mysql存储过程详细教程的相关文章

MySQL存储过程实例教程

MySQL存储过程实例教程 MySQL 5.0以后的版本开始支持存储过程,存储过程具有一致性.高效性.安全性和体系结构等特点,本节将通过具体的实例讲解PHP是如何操纵MySQL存储过程的. 实例261:存储过程的创建 这是一个创建存储过程的实例 录像位置:光盘mingrisoft9?lt;/p> 实例说明 为了保证数据的完整性.一致性,提高应用的性能,常采用存储过程技术.MySQL 5.0之前的版本并不支持存储过程,随着MySQL技术的日趋完善,存储过程将在以后的项目中得到广泛的应用.本实例将介

windows7配置Nginx+php+mysql的详细教程_php实例

最近在学习php,想把自己的学习经历记录下来,并写一些经验,仅供参考交流.此文适合那些刚刚接触php,想要学习并想要自己搭建Nginx+php+mysql环境的同学. 当然,你也可以选择集成好的安装包,比如 wamp等,不过我推荐大家还是自己手动搭建一下环境比较好,这样能够更好的了解php及其运行流程.下面直接进入正题. 步骤: 1.准备安装包等 (1)nginx-1.10.1.zip,下载链接为http://nginx.org/en/download.html .我个人选择了稳定版. (2)p

windows7配置Nginx+php+mysql的详细教程

最近在学习php,想把自己的学习经历记录下来,并写一些经验,仅供参考交流.此文适合那些刚刚接触php,想要学习并想要自己搭建Nginx+php+mysql环境的同学. 当然,你也可以选择集成好的安装包,比如 wamp等,不过我推荐大家还是自己手动搭建一下环境比较好,这样能够更好的了解php及其运行流程.下面直接进入正题. 步骤: 1.准备安装包等 (1)nginx-1.10.1.zip,下载链接为http://nginx.org/en/download.html .我个人选择了稳定版. (2)p

OS X Mountain Lion 系统配置 Apache+Mysql+PHP 详细教程

  如果你是一名 Web 开发者,很多时候都需要在本地搭建服务器测试环境,比如 Apache+Mysql+PHP 这样的环境.事实上 Mac OS X 中想要搭建这样的环境很简单,本文我们就会将详细的教程分享给大家. 首先需要说明的是,Mac OS X 系统其实已经集成了 Apache+PHP环境,用户手动开启即可.在之前的 OS X 系统中,只需要进入「系统偏好设置--共享」,然后开启「Web 共享」就可以打开 Apache.不过在最新的 Mountain Lion 中苹果取消了这个共享功能的

linux上mysql安装详细教程

所有平台的MySQL下载地址为: MySQL 下载. 挑选你需要的 MySQL Community Server 版本及对应的平台. MySQL - MySQL服务器.你需要该选项,除非你只想连接运行在另一台机器上的MySQL服务器. MySQL-client - MySQL 客户端程序,用于连接并操作Mysql服务器. MySQL-devel - 库和包含文件,如果你想要编译其它MySQL客户端,例如Perl模块,则需要安装该RPM包. MySQL-shared - 该软件包包含某些语言和应用

在MySQL中实现二分查找的详细教程_Mysql

给定一个升序排列的自然数数组,数组中包含重复数字,例如:[1,2,2,3,4,4,4,5,6,7,7].问题:给定任意自然数,对数组进行二分查找,返回数组正确的位置,给出函数实现.注:连续相同的数字,返回第一个匹配位置还是最后一个匹配位置,由函数传入参数决定. 我为什么会出这道题目?     二分查找在数据库内核实现中非常重要     在数据库的内核实现中,二分查找是一个非常重要的逻辑,几乎99%以上的SQL语句(所有索引上的范围扫描/等值查询/Unique查询等),都会使用到二分查找进行数据的

将数据插入到MySQL表中的详细教程_Mysql

将数据插入到MySQL表,需要使用SQL INSERT INTO命令.可以将数据插入到MySQL表使用mysql>提示符下或使用任何脚本,如PHP.语法: 下面是通用的SQL语法INSERT INTO命令来插入数据到MySQL表: INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN ); 要插入的字符串数据类型,所有的值需要双或单引号,例如: - "value"

sysbench对mysql压力测试的详细教程_Mysql

前言 在对网站整体性能进行benchmark时,可以使用多种工具,比如大名鼎鼎的ab(Apache bench),http_load等工具.这里我们不关注他们的使用,如果你想了解,可以自行在网上找到答案. 重点来说MySQL的基准测试如何进行,也有很多种工具来供我们选择,比如mysqlslap.sysbench.Super Smack等,其中mysqlslap的使用MySQL官网给出了介绍,Super Smack是服务器压力测试强有力的工具,那么sysbench便是我们进行MySQL基准测试的很

使用sysbench来测试MySQL性能的详细教程_Mysql

sysbench是一个模块化的.跨平台.多线程基准测试工具,主要用于评估测试各种不同系统参数下的数据库负载情况. 目前sysbench代码托管在launchpad上,项目地址:https://launchpad.net/sysbench(原来的官网 http://sysbench.sourceforge.net 已经不可用),源码采用bazaar管理. 一. 下载源码包安装epel包后以便安装bzr客户端: rpm -Uvh http://dl.fedoraproject.org/pub/epe