mysql 存储进程ITeye - 乐橙lc8

mysql 存储进程ITeye

2019-01-11 20:00:49 | 作者: 佑运 | 标签: 进程,存储,参数 | 浏览: 3002

mysql存储进程详解

1.  存储进程简介



咱们常用的操作数据库言语SQL句子在履行的时分需求要先编译,然后履行,而存储进程(Stored Procedure)是一组为了完结特定功用的SQL句子集,经编译后存储在数据库中,用户经过指定存储进程的姓名并给定参数(假如该存储进程带有参数)来调用履行它。

一个存储进程是一个可编程的函数,它在数据库中创立并保存。它能够有SQL句子和一些特别的操控结构组成。当期望在不同的应用程序或平台上履行相同的函数,或许封装特定功用时,存储进程对错常有用的。数据库中的存储进程能够看做是对编程中面向目标办法的模仿。它答应操控数据的拜访办法。

存储进程一般有以下长处:

(1).存储进程增强了SQL言语的功用和灵活性。存储进程能够用流操控句子编写,有很强的灵活性,能够完结杂乱的判别和较杂乱的运算。

(2).存储进程答应规范组件是编程。存储进程被创立后,能够在程序中被屡次调用,而不用从头编写该存储进程的SQL句子。而且数据库专业人员能够随时对存储进程进行修正,对应用程序源代码毫无影响。

(3).存储进程能完成较快的履行速度。假如某一操作包含许多的Transaction-SQL代码或分别被屡次履行,那么存储进程要比批处理的履行速度快许多。因为存储进程是预编译的。在初次运转一个存储进程时查询,优化器对其进行剖析优化,而且给出终究被存储在体系表中的履行计划。而批处理的Transaction-SQL句子在每次运转时都要进行编译和优化,速度相对要慢一些。

(4).存储进程能过削减网络流量。针对同一个数据库目标的操作(如查询、修正),假如这一操作所触及的Transaction-SQL句子被安排程存储进程,那么当在客户核算机上调用该存储进程时,网络中传送的仅仅该调用句子,然后大大增加了网络流量并降低了网络负载。

(5).存储进程可被作为一种安全机制来充分利用。体系办理员经过履行某一存储进程的权限进行约束,能够完成对相应的数据的拜访权限的约束,避免了非授权用户对数据的拜访,确保了数据的安全。



2.  关于MySQL的存储进程

存储进程是数据库存储的一个重要的功用,可是MySQL在5.0曾经并不支撑存储进程,这使得MySQL在应用上大打折扣。好在MySQL 5.0总算开端现已支撑存储进程,这样即能够大大进步数据库的处理速度,一起也能够进步数据库编程的灵活性。

3.  MySQL存储进程的创立



(1). 格局

MySQL存储进程创立的格局:CREATE PROCEDURE 进程名 ([进程参数[,...]])
[特性 ...] 进程体

这儿先举个比如:
 

  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,方法如:

CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名 数据类形...])

IN 输入参数:表明该参数的值有必要在调用存储进程时指定,在存储进程中修正该参数的值不能被回来,为默许值

OUT 输出参数:该值可在存储进程内部被改动,并可回来

INOUT 输入输出参数:调用时指定,而且可被改动和回来

Ⅰ. 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 sp_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  | 
  +----------+


(4). 变量

Ⅰ. 变量界说

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

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

例如:

  DECLARE l_int int unsigned default 4000000; 
  DECLARE l_numeric number(8,2) DEFAULT 9.95; 
  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  | 
  +----------------------------+ 


ⅲ. 在存储进程间传递大局规模的用户变量

  mysql CREATE PROCEDURE p1()  SET @last_procedure=p1; 
  mysql CREATE PROCEDURE p2() SELECT CONCAT(Last procedure was ,@last_proc); 
  mysql CALL p1( ); 
  mysql CALL p2( ); 
  +-----------------------------------------------+ 
  | CONCAT(Last procedure was ,@last_proc  | 
  +-----------------------------------------------+ 
  | Last procedure was p1  | 
  +-----------------------------------------------+ 




留意:

①用户变量名一般以@最初

②乱用用户变量会导致程序难以了解及办理



(5). 注释



MySQL存储进程可运用两种风格的注释

双模杠:--

该风格一般用于单行注释

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

例如:



  mysql DELIMITER // 
  mysql CREATE PROCEDURE proc1 --name存储进程名 
  - (IN parameter1 INTEGER) 
  - BEGIN 
  - DECLARE variable1 CHAR(10); 
  - IF parameter1 = 17 THEN 
  - SET variable1 = birds; 
  - ELSE
  - SET variable1 = beasts; 
  - END IF; 
  - INSERT INTO table1 VALUES (variable1); 
  - END 
  - // 
  mysql DELIMITER ; 



4.  MySQL存储进程的调用

用call和你进程名以及一个括号,括号里边根据需求,参加参数,参数包含输入参数、输出参数、输入输出参数。具体的调用办法能够参看上面的比如。

5.  MySQL存储进程的查询

咱们像知道一个数据库下面有那些表,咱们一般选用show tables;进行查看。那么咱们要查看某个数据库下面的存储进程,是否也能够选用呢?答案是,咱们能够查看某个数据库下面的存储进程,可是是令一钟办法。

咱们能够用

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

或许

select routine_name from information_schema.routines where routine_schema=数据库名;

或许

show procedure status where db=数据库名;

进行查询。

假如咱们想知道,某个存储进程的具体,那咱们又该怎么做呢?是不是也能够像操作表相同用describe 表名进行查看呢?

答案是:咱们能够查看存储进程的具体,可是需求用另一种办法:

SHOW CREATE PROCEDURE 数据库.存储进程名;

就能够查看当时存储进程的具体。



6.  MySQL存储进程的修正

ALTER PROCEDURE

更改用CREATE PROCEDURE 树立的预先指定的存储进程,其不会影响相关存储进程或存储功用。



7.  MySQL存储进程的删去

删去一个存储进程比较简单,和删去表相同:

DROP PROCEDURE

从MySQL的表格中删去一个或多个存储进程。



8.  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). 条件句子

Ⅰ. if-then -else句子







  mysql DELIMITER // 
  mysql CREATE PROCEDURE proc2(IN parameter int) 
  - begin
  - declare var int; 
  - set var=parameter+1; 
  - if var=0 then
  - insert into t values(17); 
  - end if; 
  - if parameter=0 then
  - update t set s1=s1+1; 
  - else
  - update t set s1=s1+2; 
  - end if; 
  - end; 
  - // 
  mysql DELIMITER ; 


Ⅱ. case句子:

  mysql DELIMITER // 
  mysql CREATE PROCEDURE proc3 (in parameter int) 
  - begin
  - declare var int; 
  - set var=parameter+1; 
  - case var 
  - when 0 then 
  - insert into t values(17); 
  - when 1 then 
  - insert into t values(18); 
  - else 
  - insert into t values(19); 
  - end case; 
  - end; 
  - // 
  mysql DELIMITER ;



(3). 循环句子

Ⅰ. while ···· end while:

  mysql DELIMITER // 
  mysql CREATE PROCEDURE proc4() 
  - begin
  - declare var int; 
  - set var=0; 
  - while var 6 do 
  - insert into t values(var); 
  - set var=var+1; 
  - end while; 
  - end; 
  - // 
  mysql DELIMITER ;





Ⅱ. repeat···· end repeat:

它在履行操作后查看成果,而while则是履行前进行查看。

  mysql DELIMITER // 
  mysql CREATE PROCEDURE proc5 () 
  - begin 
  - declare v int; 
  - set v=0; 
  - repeat 
  - insert into t values(v); 
  - set v=v+1; 
  - until v =5 
  - end repeat; 
  - end; 
  - // 
  mysql DELIMITER ; 




Ⅲ. loop ·····end loop:

loop循环不需求初始条件,这点和while 循环类似,一起和repeat循环相同不需求完毕条件, leave句子的含义是脱离循环。

  mysql DELIMITER // 
  mysql CREATE PROCEDURE proc6 () 
  - begin
  - declare v int; 
  - set v=0; 
  - LOOP_LABLE:loop 
  - insert into t values(v); 
  - set v=v+1; 
  - if v =5 then
  - leave LOOP_LABLE; 
  - end if; 
  - end loop; 
  - end; 
  - // 
  mysql DELIMITER ; 





Ⅳ. LABLES 标号:

标号能够用在begin repeat while 或许loop 句子前,句子标号只能在合法的句子前面运用。能够跳出循环,使运转指令到达复合句子的最终一步。



(4). ITERATE迭代

Ⅰ. ITERATE:

经过引证复合句子的标号,来重新开端复合句子

  mysql DELIMITER // 
  mysql CREATE PROCEDURE proc10 () 
  - begin
  - declare v int; 
  - set v=0; 
  - LOOP_LABLE:loop 
  - if v=3 then 
  - set v=v+1; 
  - ITERATE LOOP_LABLE; 
  - end if; 
  - insert into t values(v); 
  - set v=v+1; 
  - if v =5 then
  - leave LOOP_LABLE; 
  - end if; 
  - end loop; 
  - end; 
  - // 
  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



  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).数学类

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

  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)



(2)能够设定小数位数,回来浮点型数据

  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) //开平方

存储进程的缺陷:
  若果 存储进程的参数包含回来参数修正,那修正量就比较大了
  可移植性差
  因为存储进程将应用程序绑定到 SQL Server,因而运用存储进程封装事务逻辑将约束应用程序的可移植性。假如应用程序的可移植性在您的环境中非常重要,则将事务逻辑封装在不特定于 RDBMS 的中间层中或许是一个更佳的挑选。
  用的较少 

存储进程一方面进步了sql编译功率,另一方面还能够在这儿处理杂乱事务逻辑。
高负载,高并发网站应该用不到,高并发一般都用主从、LVS、中间件、笔直分库、水平分表。或许运用 nosql


参阅: http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html
  http://database.51cto.com/art/201006/203159.htm
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表乐橙lc8立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章

阅读排行

  • 1
  • 2

    Redis协议ITeye

    协议,一个,指令
  • 3
  • 4
  • 5

    Oracle Index 的三个问题ITeye

    索引,运用,数据
  • 6

    检查sqlserver的端口号ITeye

    端口号,能够,看到
  • 7
  • 8

    oracle中userITeye

    字段,运用,数据
  • 9
  • 10