Mysql存储过程-原理、语法、函数详细说明

  • 内容
  • 评论
  • 相关

mysql储存过程-原理、语法、函数详细说明
转载自 http://www.huweishen.net/weihu/1722.html
一、Mysql储存过程简介:
  储存过程是一个可编程的函数,它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。
存储过程通常有以下优点:
1)存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行,那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询,优化器对其进行分析优化,并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化,速度相对要慢一些。
2)存储过程允许标准组件是编程。存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响。
3)存储过程可以用流控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。
4)存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制,能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。
5)存储过程能过减少网络流量。针对同一个数据库对象的操作(如查询、修改),如果这一操作所涉及的Transaction-SQL语句被组织程存储过程,那么当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而大大增加了网络流量并降低了网络负载。
二、mysql储存过程的创建等语句:
1、CREATE PROCEDURE (创建储存过程)
   CREATE PROCEDURE 存储过程名 (参数列表)
   BEGIN
   SQL语句代码块
   END
注:由括号包围的参数列必须总是存在。如果没有参数,也该使用一个空参数列()。每个参数默认都是一个IN参数。要指定为其它参数,可在参数名之前使用关键词 OUT或INOUT在mysql客户端定义存储过程的时候使用delimiter命令来把语句定界符从;变为//。 当使用delimiter命令时,你应该避免使用反斜杠(‘/’)字符,因为那是MySQL的转义字符。
如:
    mysql> delimiter //
    mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    -> SELECT COUNT(*) INTO param1 FROM t;
    -> END
    -> //
   Query OK, 0 rows affected (0.00 sec)
2、ALTER  PROCEDURE(修改储存过程)
   ALTER PROCEDURE  存储过程名 SQL语句代码块
这个语句可以被用来改变一个存储程序的特征。
3、DROP  PROCEDURE (删除储存过程)
   DROP PROCEDURE IF EXISTS存储过程名
eg:DROP PROCEDURE IF EXISTS proc_employee (proc_employee 存储过程名)
注:这个语句被用来移除一个存储程序。不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程
4、SHOW  CREATE PROCEDURE(类似于SHOW CREATE TABLE,查看一个已存在的存储过程信息)
   SHOW CREATE PROCEDURE 存储过程名
5、SHOW PROCEDURE STATUS (列出所有的存储过程)
   SHOW PROCEDURE STATUS
6、CALL 语句(存储过程的调用)
   CALL 存储过程名(参数列表)
CALL语句调用一个先前用CREATE PROCEDURE创建的程序。
CALL语句可以用声明为OUT或INOUT参数的参数给它的调用者传回值。
哪怕该存储过程没有参数传递,存储过程名称后面也必须加括号。
7、BEGIN ... END(复合语句)
     [begin_label:]
   BEGIN
     [statement_list]
    END
     [end_label]
存储子程序可以使用BEGIN ... END复合语句来包含多个语句。 statement_list 代表一个或多个语句的列表。statement_list之内每个语句都必须用分号“;”来结尾。复合语句可以被标记。除非begin_label存在,否则end_label不能被给出,并且如果二者都存在,他们必须是同样的。
8、DECLARE语句(用来声明局部变量)
DECLARE语句被用来把不同项目局域到一个子程序:局部变量DECLARE仅被用在BEGIN ... END复合语句里,并且必须在复合语句的开头,在任何其它语句之前。
9、存储程序中的变量
1)DECLARE局部变量
   DECLARE var_name[,...] type [DEFAULT value]
这个语句被用来声明局部变量。
要给变量提供一个默认值,请包含一个DEFAULT子句。
值可以被指定为一个表达式,不需要为一个常数。
如果没有DEFAULT子句,初始值为NULL。
局部变量的作用范围在它被声明的BEGIN ... END块内。
它可以被用在嵌套的块中,除了那些用相同名字声明变量的块。
2)变量SET语句
   SET var_name = expr [, var_name = expr]
在存储程序中的SET语句是一般SET语句的扩展版本。
被参考变量可能是子程序内声明的变量,或者是全局服务器变量。
在存储程序中的SET语句作为预先存在的SET语法的一部分来实现。这允许SET a=x, b=y, ...这样的扩展语法。
其中不同的变量类型(局域声明变量及全局和集体变量)可以被混合起来。
这也允许把局部变量和一些只对系统变量有意义的选项合并起来。
3)SELECT ... INTO 语句
   SELECT col_name[,...] INTO var_name[,...] table_expr
这个SELECT语法把选定的列直接存储到变量。
因此,只有单一的行可以被取回。
SELECT id,data INTO x,y FROM test.t1 LIMIT 1;
注:用户变量名在MySQL 5.1中是对大小写不敏感的。
重要: SQL变量名不能和列名一样。如果SELECT ... INTO这样的SQL语句包含一个对列的参考,并包含一个与列相同名字的局部变量,MySQL当前把参考解释为一个变量的名字。
10、MySQL 存储过程参数类型(in、out、inout)
1)MySQL 存储过程参数(in)
MySQL 存储过程  “in” 参数:跟 C 语言的函数参数的值传递类似,MySQL 存储过程内部可能会修改此参数,但对in类型参数的修改,对调用者(caller)来说是不可见的(not visible)。
2)MySQL 存储过程参数(out)
MySQL 存储过程  “out” 参数:从存储过程内部传值给调用者。在存储过程内部,该参数初始值为 null,无论调用者是否给存储过程参数设置值。
3)MySQL 存储过程参数(inout)
MySQL 存储过程  inout 参数跟 out 类似,都可以从存储过程内部传值给调用者。不同的是:调用者还可以通过  inout 参数传递值给存储过程。
总结:如果仅仅想把数据传给 MySQL 存储过程,那就使用“in” 类型参数;如果仅仅从 MySQL 存储过程返回值,那就使用“out” 类型参数;如果需要把数据传给 MySQL 存储过程,还要经过一些计算后再传回,就要使用“inout” 类型参数。
三、MySQL存储函数-基本常用函数
1、字符串类
1)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
2)函数使用实例:
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个空格

2、数学类
1)mysql储存函数-数学函数:
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为小数位数]
注:返回类型并非均为整数,如下文:
2)函数使用实例:
a、默认变为整形值
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)
b、可以设定小数位数,返回浮点型数据
mysql> select round(1.567,2); 
+----------------+ 
| round(1.567,2) | 
+----------------+ 
|           1.57 | 
+----------------+ 
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存储过程-原理、语法、函数详细说明

微信支付二维码

微信 赏一包辣条吧~

支付宝支付二维码

支付宝 赏一听可乐吧~

评论

0条评论

发表评论

电子邮件地址不会被公开。 必填项已用*标注