Mysql 存储过程

存储过程通常有以下优点
1) 增强了SQL语言的功能和灵活性。
2) 存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程SQL语句,而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响
3) 存储过程能实现较快的执行速度
4) 存储过程能减少网络流量。
5) 存储过程可被作为一种安全机制来充分利用，系统管理员通过执行某一存储过程的权限进行限制,能够实现对相应数据

的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。

Mysql存储过程
  Mysql5.0以前不支持存储过程
1) 格式 create procedure 过程名([过程参数[,...]])
   [特性...]过程体

   例子:
   delimiter // ----------------声明分隔符
   create procedure procl(OUT S int)
   begin
   select count(*) into s from user;
   end
   //
   delimiter;
注：(1) 这里需要注意的是delimiter//和delimiter;两句，delimiter是分隔符的意思,因为MYSQL默认以";"为分隔符,
       如果没有声明分隔符,那么编译器会把存储过程当成SQL语句进行处理,则存储过程的编译过程会报错,所以要事
       先用delimiter关键字声明当前段分隔符,这样MYSQL才会将";"当作存储过程中的代码,不会执行这谢代码,用完
       之后要把分隔符还原。
    (2) 存储过程根据需要可能会有输入,输出,输入输出参数,这里有一个输出参数s,类型是int型,如果有多个参数用
       ","分隔开。
    (3) 过程体的开始于结束使用begin与end进行标识。

2) 参数
 MYSQL存储过程的参数用在存储过程的定义,共有三种参数类型IN,OUT,INTOUT,形式如：
 create procedure([IN|OUT|INOUT] 参数名 数据类型...)

  Mysql存储过程参数如果不显式指定"in","out","inout",则默认为"in",习惯上,对于是"in"的参数,我们都不会显式指定

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

创建:
mysql > DELIMITER //  
mysql > CREATE PROCEDURE demo_in_parameter(IN p_in int)  
-> BEGIN   
-> SELECT p_in;   
-> SET p_in=2;   
-> SELECT p_in;   
-> END;   
-> //  
mysql > DELIMITER ;


执行结果:
mysql > SET @p_in=1;  
mysql > CALL demo_in_parameter(@p_in);  
+------+  
| p_in |  
+------+  
|   1  |   
+------+  

+------+  
| p_in |  
+------+  
|   2  |   
+------+  

mysql> SELECT @p_in;  
+-------+  
| @p_in |  
+-------+  
|  1    |  
+-------+  

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

 OUT 输出参数: 该值可在存储过程内部被改编,并可返回
 
创建:
mysql > DELIMITER //  
mysql > CREATE PROCEDURE demo_out_parameter(OUT p_out int)  
-> BEGIN
-> SELECT p_out;  
-> SET p_out=2;  
-> SELECT p_out;  
-> END;  
-> //  
mysql > DELIMITER ;


执行结果:
mysql > SET @p_out=1;  
mysql > CALL demo_out_parameter(@p_out);  
+-------+  
| p_out |   
+-------+  
| NULL  |   
+-------+  

+-------+  
| p_out |  
+-------+  
|   2   |   
+-------+  

mysql> SELECT @p_out;  
+-------+  
| p_out |  
+-------+  
|   2   |  
+-------+  

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

创建:
mysql > DELIMITER //   
mysql > CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)   
-> BEGIN
-> SELECT p_inout;  
-> SET p_inout=2;  
-> SELECT p_inout;   
-> END;  
-> //   
mysql > DELIMITER ;

执行结果:
mysql > SET @p_inout=1;  
mysql > CALL demo_inout_parameter(@p_inout) ;  
+---------+  
| p_inout |  
+---------+  
|    1    |  
+---------+  

+---------+  
| p_inout |   
+---------+  
|    2    |  
+---------+  

mysql > SELECT @p_inout;  
+----------+  
| @p_inout |   
+----------+  
|    2     |  
+----------+

3) 变量:
格式:
  declare 声明变量名(可以有多个,使用","分开声明) 声明变量类型 default 默认值;
  declare variable_name[，variable_name...] datatype [default value];

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

  Mysql数据类型是没有number类型的,对应的只有int(整型)、float(浮点型);

  Mysql存储过程名字后面的"()"是必须的,即使没有一个参数,也需要"()";

  Mysql存储过程参数,不能再参数名称前加"@",如“@a int”. 

  Mysql存储过程的参数不能指定默认值

  Mysql存储过程不需要在procedure body前面加"as".而sql_server存储过程必须加"as"关键字

  如果Mysql存储过程中包含多条Mysql语句,则需要begin end关键字

        BEGIN 和 END 语句用于将多个 Transact-SQL 语句组合为一个逻辑块。在控制流语句必须执行包含两条或多条 Transact-SQL 语句的语句块的任何地方，都可以使用 BEGIN 和 END 语句。

        BEGIN 和 END 语句必须成对使用：任何一个均不能单独使用。BEGIN 语句单独出现在一行中，后跟 Transact-SQL 语句块。最后，END 语句单独出现在一行中，指示语句块的结束。

  不能在 MySQL 存储过程中使用 "return" 关键字,return 只能出现在函数中

  调用 MySQL 存储过程时候，需要在过程名字后面加"()"，即使没有一个参数，也需要"()"

  例如：
  declare l_int int unsinged default 4000000;
  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';

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

用户变量

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

    mysql > SELECT 'Hello World' into @x;  
    mysql > SELECT @x;  
    +-------------+  
    |   @x        |  
    +-------------+  
    | Hello World |  
    +-------------+  
    mysql > SET @y='Goodbye Cruel World';  
    mysql > SELECT @y;  
    +---------------------+  
    |     @y              |  
    +---------------------+  
    | Goodbye Cruel World |  
    +---------------------+  
     
    mysql > SET @z=1+2+3;  
    mysql > SELECT @z;  
    +------+  
    | @z   |  
    +------+  
    |  6   |  
    +------+  

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

    mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');  
    mysql > SET @greeting='Hello';  
    mysql > CALL GreetWorld( );  
    +----------------------------+  
    | CONCAT(@greeting,' World') |  
    +----------------------------+  
    |  Hello World               |  
    +----------------------------+  
注意：
  1、用户变量名一般以@开头
  2、滥用用户变量会导致程序难以理解及管理

4) 注释
MySQL 存储过程可使用两种风格的注释
 双模杠: --
 该风格一般用于单行注释

 /**/: 一般用于多行注释(将需要注释的多行内容嵌套在/**/中间)

MySQL 存储过程的调用
用call 过程名(参数...)

MySQL 存储过程的查询
  想要知道数据库下面有哪些表,一般采用show tables;进行查看。
  我们查看某个数据库下面的存储过程方式如下：
  1、select name from mysql.proc where db='数据库名';
  2、select routine_name from information_schema routines where routines_shema = '数据库名';
  3、show procedure status where db='数据库名';
 
  如果想要了解某个存储过程的详细我们可以进行如下操作:
  show create procedure 数据库.存储过程名;

  MySQL 存储过程的修改
  alter procedure 存储过程名
 
  MySQL 存储过程的删除
  drop procedure  存储过程名

MySQL 存储过程的控制语句
1) 内部作用域
   内部的变量在其作用域内享有更高的优先权,当执行到end.变量时,内部变量消失,此时已经在其作用域外,变量不再
   可见了,因为在存储过程外再也不能找到这个申明的变量,但是你可以通过out参数或者将其值指派给会话变量来保存
   其值
mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc3()  
 -> begin
 -> declare x1 varchar(5) default 'outer';  
 -> begin
 -> declare x1 varchar(5) default 'inner';  
 -> select x1;  
 -> end;  
 -> select x1;  
 -> end;  
 -> //  
mysql > DELIMITER ;  

2) 条件语句
  1、if-then-else 语句
    DELIMITER $$

    CREATE DEFINER=`dbma`@`192.168.7.62` PROCEDURE `proc2`(parameter int)
    begin
    declare var int;
    set var = parameter+1;
    if var=0 then
    insert into wanyun values(17);
    end if;

    if parameter =0 then
    update wanyun set age = age+1;
    else
    update wanyun set age = age+2;
    end if;
    end 

  2、case 语句
        delimiter //
    create procedure proc3(parameter int)
    begin
    declare var int;
    set var = parameter+1;
    case car
    when  0 then
    insert into wanyun values(17);
    when 1 then
    insert into wanyun values(18);
    when 2 then
    insert into wanyun values(19);
    end case;
    end;

3) while...end while 循环语句
    delimiter //
    create procedure proc4(parameter int)
    begin
    declare var int;
    set var  = 0;
    while var < 6 do
    insert into wanyun values(var);
    set var = var+1;
    end while;
    end;

4) loop...end loop 循环
   loop循环不需要初始条件,这点和while循环相似;同时和repeat循环一样不需要结束条件
   leave 语句的意义是离开循环
   delimiter //
    create procedure proc5()
    begin
     declare var int;
    
    LOOP_LABEL:loop
    insert into wanyun values(var);
    set var = var +1;

    if(v>=5) then
    leave LOOP_LABEL;
    end if;
    end loop;
    end;

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

 ITERATE迭代
 1、iterate:
   通过引用复合语句的标号来重新开始复合语句
    delimiter //
    create procedure proc6()
    begin
    declare v int;
    set v = 0;
    LOOP_LABEL:loop
    if v = 3 then
    set v = v+1;
    iterate LOOP_LABEL;
    end if;
    insert into wanyun values(v);
    set v = v+1;
    if v>= 5 then
    leave LOOP_LABEL;
    end if;
    end loop;
    end;

MySQL 存储过程的基本函数
1) 字符串类
  charset(str)//返回字串字符集;
  concat(str1[,...])//连接字串;
  instr(str,substr)//返回substring首次在string中出现的位置,不存在返回0;
  lcase(str)//转换成小写;
  left(str,length)//从str中的左边起取length个字符;
  load_file(file_name)//从文件读取内容;
  locate(substr,string[,start_position])//同instr,但可指定开始位置;
  lpad(string,length,pad)//重复用pad加在string开头，直到字串长度为length;
  ltrim(string)//去除前端空格;
  repeat(string,count)//重复count次;
  replace(str,search_str,replace_str)//在str中用replace_str替换search_str;
  rpad(string,length,pad)//在str后用pad补充,直到长度为length;
  rtrim(string)//去除后端空格;
  stramp(string1,string2)//逐字符比较两字串大小;
  substring(str,position[,length])//从str的position开始取length个字符;

  注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1
  select substring('abcd',0,2);
  执行结果是:空

  select substring('abcd',1,2);
  执行结果是:ab
  trim([[both|leading|trailing][padding]from]string)//去除指定位置的指定字符;
  ucase(string)//转换成大写;
  right(string,length)//取string最后length个字符;
  space(count)//生成count个空格;

2) 数学类
  abs(number)//绝对值;
  bin(decimal_number)//十进制转二进制;
  ceiling(number)//向上取整;
  conv(number)//进制转换;
  floor(number)//向下取整;
  format(number,decimal_places)//保留小数位数;
  hex(DecimalNumber)//转十六进制;
注：hex()中科传入字符串,则返回其ASC-11码，如hex('def')返回4142143,也可以传入十进制整数,返回其十六进制
编码,如hex(25) 返回19；
  least(number,number...)//求最小值;
  mod(除数,被除数)//求余;
  power(number,power)//求指数;
  rand([seed])//随机数;
  round(number[,decimals])//四舍五入,decimals为小树位数;
注：返回类型并非均为整数;

 3) 日期时间类
 addtime(date,time_interval)//将time_interval加到date;
 convert_tz(datetime,fromTZ,toTZ)//转换时区;
 current_date()//当前日期;
 current_time()//当前时间;
 current_timestamp()//当前时间戳;
 date(datetime)//返回datetime的日期部分;
 date_add(date,interval d_value d_type)//在date中加上日期或时间;
 date_format(datetime,formatCodes)//使用formatcodes格式显示datetime;
 date_sub(date,interval d_value d_type)//在date上减去一个时间;
 datediff(date1,date1)//两个时间差;
 day(date)//返回date在当前date日期的月中是第多少天;例如：select day('2015-02-14');返回的结果是14;
 dayname(date)//英文星期;例如:select dayname('2015-02-14');返回的结果是Saturday;
 dayofweek(date)//星期(1-7),1为星期天;例如：select dayofweek('2015-02-14');返回结果是7，代表是星期六;
 dayofyear(date)//一年中的第几天;
 extract(interval_name from date)//从date中提取日期的指定部分;
 makedate(hour,minute,second)//生成时间串;
 monthname(date)//英文月份名
 now()//当前时间;
 sec_to_time(seconds)//秒数转成时间;例如：select sec_to_time(1290);返回结果是00:21:30;
 str_to_date(string,format)//字串转成时间,以format格式显示;
 timediff(datetime1,datetime2)//两个时间差;
 time_to_sec(time)//时间转秒数;
 week(date_time[,start_of_week])//第几周;
 year(datetime)//年份;
 dayofmonth(datetime)//当前datetime时间月的第几天;例如:select dayofmonth('2015-01-14');返沪结果是14;
 hour(datetime)//小时;例如:select hour('2015-01-14 16:41:00');返沪结果是16;
 last_date(date)//date的月的最后日期;
 microsecond(datetime)//微秒;
 month(datetime)//月;
 minute(datetime)//分返回符号,正负或0;
 sqrt(number)//开平方; 例如: select sqrt(144);返沪结果是12;

内容转自:http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html