Mysql存储过程

存储过程（Stored Procedure）：

是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

 

存储过程和存储函数

1）一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。存储过程，功能强大，可以执行包括修改表等一系列数据库操作；用户定义函数不能用于执行一组修改全局数据库状态的操作。

2）对于存储过程来说可以返回参数，如记录集，而函数只能返回值或者表对象。函数只能返回一个变量或者表对象；而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型，而函数只能有IN类~~存储过程声明时不需要返回类型，而函数声明时需要描述返回类型，且函数体中必须包含一个有效的RETURN语句。

3）存储过程，可以使用非确定函数，不允许在用户定义函数主体中内置非确定函数。

4）存储过程一般是作为一个独立的部分来执行（ EXECUTE 语句执行），而函数可以作为查询语句的一个部分来调用（SELECT调用），由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。 SQL语句中不可用存储过程，而可以使用函数。

存储过程：

CREATE PROCEDURE num_from_employee (IN emp_id INT, OUT count_num INT )

存储函数：

CREATE FUNCTION name_from_employee (emp_id INT )

 

存储过程中有个DEFINER

(1) DEFINER的意思是“定义者”，也就是指明此存储过程有哪个用户定义的，它跟存储过程的使用权限无关；可以说，存储和过程是没有使用限制的，任何人都可以使用存储过程，那么为什么还要加上DEFINER指定存储过程的定义者呢，因为，存储过程虽然不限制任何人使用，但是它本身的行为必须受到权限限制，也就是存储过程本身内部具备哪些对数据库的访问权限，而这访问权限便是DEFINER用户对数据库的访问权限。

(2) DEFINER被定义为一个普通的username@hostname用户就不必说了，如果定义为root@%，有什么特别的吗？没有。只不过数据库中要存在root@%用户，否则创建的时候没事，调用的时候就提示root@% is not registered，也就是用户不存在。

(3) DEFINER也可以省略掉，这样存储过程默认的定义者是root@localhost，而一般数据库在安装的时候都会有一个root@localhost用户，所以，该存储过程也能正常在不同的机器上被使用。

5. 既然上述存储过程任何人都以访问，那么它本身运行时的访问权限怎么限制呢。可以通过SQL SECURITY INVOKER ，也就是，其本身的访问权限由调用者权限设置。

6.命令分隔符DELIMITER

MySQL默认是以分号作为两个命令的分割点的。但有些命令块中包含分号，比如存储过程或触发器的定义中。为了使MySQL不把命令块中的分号误认做命令的分割点，需要在执行这种命令块前临时改一下命令分隔符。尤其注意，SQLYog里面写存储过程的时候，必须自行定义命令分隔符。

原文链接：https://blog.csdn.net/dyzhen/article/details/6681438

案例一：DEFINER

    CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()

    BEGIN
        SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
    END;

    在这个案例中，不论哪个用户A调用存储过程，
    存储过程都会以'admin'@'localhost'的权限去执行，
    即使这个用户A没有查询mysql.user表的权限。

案例二：INVOKER

    CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()
        SQL SECURITY INVOKER
    BEGIN
        SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
    END;

    在这个案例中，虽然存储过程语句中仍然带有DEFINER参数，
    但是由于SQL SECURITY指定了INVOKER，所以在存储过程执行的时候，
    会以调用者的额身份去执行。此时这个存储过程是否能成功执行，
    取决于调用者是否有mysql.user表的查询权限。


如果想所有地方都可以使用那么：
CREATE DEFINER=`root`@`%` PROCEDURE account_count()
BEGIN
   SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
END;
 存储过程都会以'root'@'%'的权限去执行，
 'root'@'%'的意思是，密码正确的前提下任何人都可以使用MYSQL名为'root'用户的权限


也可以是：
CREATE DEFINER=CURRENT_USER PROCEDURE account_count() 
BEGIN
   SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
END;
存储过程以当前登录用户的权限执行该存储过程

 

存储过程的三种参数

（1）IN型参数：它是默认模式。在存储过程中定义IN参数时，调用程序必须将参数传递给存储过程。 另外，IN参数的值被保护。这意味着即使在存储过程中更改了IN参数的值，在存储过程结束后仍保留其原始值。换句话说，存储过程只使用IN参数的副本。

 

（2）OUT型参数：可以在存储过程中更改OUT参数的值，并将其更改后的新值传递回调用程序。

 

（3）INOUT型参数： INOUT参数是IN和OUT参数的组合。这意味着调用程序可以传递参数，且存储过程可以修改INOUT参数并将更改后的新值传递回调用程序。

 

例如：CREATE DEFINER=`root`@`%` PROCEDURE `名称`(IN `param1` bigint,OUT `param2` bigint,INOUT `param3` varchar)

 

定义参数的方法：先声明时什么型的参数，参数名称，参数的数据类型 （IN `param` varchar）;

 

mysql存储过程定义变量

1.declare
declare 变量名 变量类型  default  默认值；
declare a int DEFAULT 2;
declare定义的变量 相当于一个局部变量，  
在end之后失效，而且declare只能在begin，end中定义。

2.set
set @变量名=值
此处的变量不需要声明，mysql会自动根据值类型来确定类型，
这种变量要在变量名称前面加上“@”符号，叫做会话变量，
代表整个会话过程他都是有作用的，这个有点类似于全局变量一样。


3.select
同set

 

MySQL存储过程的控制语句

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

(2)可以利用if或者case语句控制存储过程中的执行流程。

CREATE DEFINER=`root`@`%` PROCEDURE account_count(IN age int,OUT descrise varchar) BEGIN if (age >18) then set descrise = '成年人'; elseif (age >60) then sset descrise = '老年人'; else set descrise = '未知群体'; end if; select descrise as user_descrise ; END;

CREATE DEFINER=`root`@`%` PROCEDURE account_count(IN age int,OUT descrise varchar) BEGIN cast (age >18) then set descrise = '成年人'; when(age >60) then sset descrise = '老年人'; else set descrise = '未知群体'; end cast; select descrise as user_descrise ; END;

(3)可以利用loop或者while或者repeat来循环

CREATE DEFINER=CURRENT_USER PROCEDURE account_count() 
begin 
    declare counter int default 0;
         test_loop:loop 
            if counter < 10 then set counter = counter + 1; 
            else 
                leave test_loop;
             end if; 
        end loop test_loop; 
    select counter; 
end

 

CREATE DEFINER=CURRENT_USER PROCEDURE account_count() 
begin
    declare counter int default 0;
    while counter < 10 do
        set counter = counter + 1;
    end while;
    select counter;
end

 

CREATE DEFINER=CURRENT_USER PROCEDURE account_count() 
begin
    declare counter int default 0;
    repeat
        set counter = counter + 1;
    until counter > 10
    end repeat;
    select counter;
end

MySQL存储过程的基本函数

参考：http://blog.sina.com.cn/s/blog_86fe5b440100wdyt.html

(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

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为小数位数]

(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中的游标

1.声明游标

DECLARE cursor_name CURSOR FOR select org_id,user_code from user_inf;      // 相当于所有数据在游标中

2.打开游标

OPEN cursor_name

3.使用游标       

 FETCH cursor_name INTO org_id,user_code;       // 将游标数据取出来用

4.关闭游标

CLOSE cursor_name