mysql的高级特性-存储过程

定义：　

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

语法：

DELIMITER // 声明语句结束符，用于区分; 
    CEATE PROCEDURE demo_in_parameter(IN p_huhy int) 声明存储过程 
    BEGIN
        sql语句体;
    END 存储过程开始和结束符号 
    SET @p_huhy=1 变量赋值 
    DECLARE huhy_int int unsigned default 400; 变量定义

  注意：varchar类型数据必须加限定  varcahr(12)

　　存储过程(stored procedure)、存储例程(store routine)、存储函数区别 
　　Mysql存储例程实际包含了存储过程和存储函数，它们被统称为存储例程。 
　　其中存储过程主要完成在获取记录或插入记录或更新记录或删除记录，即完成select insert delete update等的工作。而存储函数只完成查询的工作，可接受输入参数并返回一个结果。

　　实例介绍：
　　　　mysql> DELIMITER ;; 
　　　　mysql> CREATE PROCEDURE huhy_count(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存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：

　　　　CREATE PROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形…])

　　IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
　　OUT 输出参数:该值可在存储过程内部被改变，并可返回
　　INOUT 输入输出参数:调用时指定，并且可被改变和返回

　　note:关于参数的使用我会在创建存储过程的时候再详细介绍

局部变量

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

　　　　DECLAREvariable_name [,variable_name…] datatype [DEFAULT value];
　　　　其中，datatype为MySQL的数据类型，如:int, float, date,varchar(length)
　　　　eg:
　　　　　　DECLARE huhy_int int unsigned default 12;
　　　　　　DECLARE huhy_numeric number(5,2) DEFAULT 9.01;
　　　　　　DECLARE huhy_date date DEFAULT '2007-12-31';
　　　　　　DECLARE huhy_datetime datetime DEFAULT '2008-12-31 23:59:59';
　　　　　　DECLARE huhy_varchar varchar(255) DEFAULT 'is huhy';

　　变量赋值：
　　　　SET 变量名 = 表达式值 [,variable_name = expression …]

用户变量：

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

　　1>在存储过程间传递全局范围的用户变量
　　　　mysql> CREATE PROCEDURE yang_1() SET @huhy_procedure='good';
　　　　mysql> CREATE PROCEDURE yang_2() SELECT CONCAT('huhy is ',@huhy_procedure);
　　　　mysql> CALL yang_1( );
　　　　mysql> CALL yang_2( );
　　　　　　+-----------------------------------------------+
　　　　　　| CONCAT('huhy is ',@huhy_procedure |
　　　　　　+-----------------------------------------------+
　　　　　　| huhy is good                                       |
　　　　　　+-----------------------------------------------+
　　2>在存储过程中使用用户变量
　　　　mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');
　　　　mysql > SET @greeting='Hello';
　　　　mysql > CALL GreetWorld( );
　　　　　　+----------------------------+
　　　　　　| CONCAT(@greeting,' World') |
　　　　　　+----------------------------+
　　　　　　| Hello World                  |
　　　　　　+----------------------------+
　　3>在MySQL客户端使用用户变量
　　　　mysql > SELECT 'Hello World' into @x;
　　　　mysql > SELECT @x;
　　　　　　+-------------+
　　　　　　| @x |
　　　　　　+-------------+
　　　　　　| Hello World |
　　　　　　+-------------+ 

注释:

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

　　双模杠：–- 该风格一般用于单行注释
　　c风格： 一般用于多行注释 /********************* 创建表 *****************************/

 

MySQL存储过程的调用

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

MySQL存储过程的查询

　　我们像知道一个数据库下面有那些表，我们一般采用showtables;进行查看。那么我们要查看某个数据库下面的存储过程，是否也可以采用呢？答案是，我们可以查看某个数据库下面的存储过程，但是是令一钟方式。 我们可以用 selectname from mysql.proc where db=’数据库名’; 或者 selectroutine_name from information_schema.routines where routine_schema=’数据库名’; 或者 showprocedure status where db=’数据库名’; 进行查询。 如果我们想知道，某个存储过程的详细，那我们又该怎么做呢？是不是也可以像操作表一样用describe 表名进行查看呢？ 答案是：我们可以查看存储过程的详细，但是需要用另一种方法： 

SHOWCREATE PROCEDURE 数据库.存储过程名;
就可以查看当前存储过程的详细。

MySQL存储过程的修改

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

 

MySQL存储过程的删除

　　删除一个存储过程比较简单，和删除表一样：
　　DROP PROCEDURE
　　从MySQL的表格中删除一个或多个存储过程。

 

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 
                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个空格
    数学类
        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 ) //
    日期时间类
        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) //开平方

mybatis操作mysql存储过程：

　　假设操作环境已经搭建完成。下面就来对其进行测试：

　　接口如下：

　　　　

　　xml配置如下：

　　　　

　　测试如下：

　　　　

---

总结：

　　以上就是mybatis对存储过程的简单操作：关于存储过程如下：　　

1> 查询表数据总共多少
    CREATE DEFINER=`huhy`@`%` PROCEDURE `huhy_count`(OUT `sum` int)
    BEGIN
        select count(*) INTO sum from user;
    END

    note: varchar类型数据必须加限定 eg varcahr(11),
    
2> 表名作为存储参数传入存储过程

DROP PROCEDURE IF EXISTS `pagePro`;
CREATE DEFINER = `root`@`localhost` PROCEDURE `pagePro`(in pageNo int,in pageSize int,in tableName varchar(50))
BEGIN
    DECLARE startIndex INT;
    set startIndex = pageSize * (pageNo-1);
    set @s = concat("select * from ",tableName," LIMIT ",startIndex,",",pageSize);
    prepare stmt from @s;
    execute stmt;
    DEALLOCATE prepare stmt;
END;

    mysql 是不支持表名,列名做参数的.只能采用动态拼接的方法来生成sql语句.

note：传入三个参数,pageNo 是页号,pageSize是一页显示的记录数量,tableName是表名.
  eg：call pagePro(2,3,'student');

　　大家要注意：存储过程的一些特性，比如原生的存储过程不允许传表名，列名，等，还有存储过程的语法，注意项。有兴趣的可以去试一下。合理的利用存储过程会极大的提高代码的性能。但并不是存储过程越多越好，大家合理掌握瓶颈问题。