精华内容
下载资源
问答
  • MySql存储过程

    2018-11-06 22:33:28
    一、MySQL的主要适用场景 ...三、mysql存储过程优点和缺点  优点: 1.在生产环境下,可以通过直接修改存储过程的方式修改业务逻辑(或bug),而不用重启服务器。但这一点便利被许多人滥用了。有人直接就...

    一、MySQL的主要适用场景

    1、Web网站系统

    2、日志记录系统

    3、数据仓库系统

    4、嵌入式系统

    二、Innodb 存储引擎

    1、支持事务安装

    2、数据多版本读取

    3、锁定机制的改进

    4、实现外键

    三、mysql存储过程优点和缺点

     优点:

    1.在生产环境下,可以通过直接修改存储过程的方式修改业务逻辑(或bug),而不用重启服务器。但这一点便利被许多人滥用了。有人直接就在正式服务器上修改存储过程,而没有经过完整的测试,后果非常严重。

    2.执行速度快。存储过程经过编译之后会比单独一条一条执行要快。但这个效率真是没太大影响。如果是要做大数据量的导入、同步,我们可以用其它手段。

    3.减少网络传输。存储过程直接就在数据库服务器上跑,所有的数据访问都在服务器内部进行,不需要传输数据到其它终端。但我们的应付服务器通常与数据库是在同一内网,大数据的访问的瓶颈会是硬盘的速度,而不是网速。

    4.能够解决presentation与数据之间的差异,说得文艺青年点就是解决OO模型与二维数据持久化之间的阻抗。领域模型和数据模型的设计可能不是同一个人(一个是SA,另一个是DBA),两者的分歧可能会很大——这不奇怪,一个是以OO的思想来设计,一个是结构化的数据来设计,大家互不妥协——你说为了软件的弹性必须这么设计,他说为了效率必须那样设计,为了抹平鸿沟,就用存储过程来做数据存储的逻辑映射(把属性映射到字段)。好吧,台下已经有同学在叨咕ORM了。

    5.方便DBA优化。所有的SQL集中在一个地方,DBA(数据库管理员Database Administrator,简称DBA)会很高兴。这一点算是ORM的软肋。不过按照CQRS框架的思想,查询是用存储过程还是ORM,还真不是问题——DBA对数据库的优化,ORM一样会受益。况且放在ORM中还能用二级缓存,有些时候效率还会更高。

     缺点:

    1.SQL本身是一种结构化查询语言,加上了一些控制(赋值、循环和异常处理等),但不是OO的,本质上还是过程化的,面对复杂的业务逻辑,过程化的处理会很吃力。这一点算致命伤。

    2.不便于调试。基本上没有较好的调试器,很多时候是用print来调试,但用这种方法调试长达数百行的存储过程简直是噩梦。好吧,这一点不算啥,C#/java一样能写出噩梦般的代码。

    3.没办法应用缓存。虽然有全局临时表之类的方法可以做缓存,但同样加重了数据库的负担。如果缓存并发严重,经常要加锁,那效率实在堪忧。

    4.无法适应数据库的切割(水平或垂直切割)。数据库切割之后,存储过程并不清楚数据存储在哪个数据库中。

    四、mysql存储过程简介:

    SQL语句需要先编译然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。

    存储过程是可编程的函数,在数据库中创建并保存,可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟,它允许控制数据的访问方式。(存储过程是数据库的一个重要的功能,MySQL 5.0以前并不支持存储过程,这使得MySQL在应用上大打折扣。好在MySQL 5.0开始支持存储过程,这样即可以大大提高数据库的处理速度,同时也可以提高数据库编程的灵活性。)

    优化:

    1、存储过程pr_dealtestnum的主体是一条insert语句,但这条insert语句里面又包含了select语句,这样的编写是不规范的。因此,我们要把这条insert语句拆分成两条语句,即先把数据从tb_testnum_tmp表中查找出来,再插入到tb_testnum表中。

    2、在向tb_testnum表插入数据之前,要判断该条数据在表中是否已经存在了,如果存在,则不再插入数据。同理,在从tb_testnum_tmp表中查询数据之前,要先判断该条数据在表中是否存在,如果存在,才能从表中查找数据。

    3、不管向tb_testnum表插入数据的操作执行成功与否,都应该有一个标识值来表示执行的结果,这样也方便开发人员对程序流程的追踪和调试。也就是说,在每条leave语句之前,都应该有一个返回值,我们为此定义一个输出参数。

    4、我们注意到“insert into tb_testnum values(p_boxnumber,p_usertype);”语句中,tb_testnum表之后没有列出具体的字段名,这个也是不规范的。如果在以后的软件版本中,tb_testnum表中新增了字段,那么这条insert语句极有可能会报错。因此,规范的写法是无论tb_testnum表中有多少字段,在执行insert操作时,都要列出具体的字段名。
    5、在执行insert语句之后,要用MySQL中自带的@error_count参数来判断插入数据是否成功,方便开发人员跟踪执行结果。如果该参数的值不为0,表示插入失败,那么我们就用一个返回参数值来表示操作失败。

     

     

    展开全文
  • MySQL存储过程详解 mysql 存储过程

    千次阅读 2016-09-20 15:13:23
    MySQL存储过程详解mysql存储过程 mysql存储过程详解 1.存储过程简介 我们常用的操作数据库语言SQL语句在执行的时候需要要先编译,然后执行,而存储过程(StoredProcedure)是一组为了完成特定功能...

    MySQL存储过程详解  mysql 存储过程

      

    mysql存储过程详解

    1.     存储过程简介

     

    我们常用的操作数据库语言SQL语句在执行的时候需要要先编译,然后执行,而存储过程(StoredProcedure)是一组为了完成特定功能的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过程名 ([过程参数[,...]])
    [特性 ...]过程体

    这里先举个例子:
       

    1. mysql> DELIMITER // 
    2. mysql> CREATE PROCEDURE proc1(OUT s int) 
    3.     -> BEGIN 
    4.     -> SELECT COUNT(*) INTO s FROM user; 
    5.     -> END 
    6.     -> // 
    7. 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,形式如:

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

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

    OUT输出参数:该值可在存储过程内部被改变,并可返回

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

    Ⅰ.IN参数例子

    创建:

    1. mysql > DELIMITER // 
    2. mysql > CREATE PROCEDURE demo_in_parameter(IN p_in int) 
    3. -> BEGIN  
    4. -> SELECT p_in;  
    5. -> SET p_in=2;  
    6. -> SELECT p_in;  
    7. -> END;  
    8. -> //  
    9. mysql > DELIMITER ; 


    执行结果:

    1. mysql > SET @p_in=1; 
    2. mysql > CALL demo_in_parameter(@p_in); 
    3. +------+ 
    4. | p_in | 
    5. +------+ 
    6. |   1  |  
    7. +------+ 
    8.  
    9. +------+ 
    10. | p_in | 
    11. +------+ 
    12. |   2  |  
    13. +------+ 
    14.  
    15. mysql> SELECT @p_in; 
    16. +-------+ 
    17. | @p_in | 
    18. +-------+ 
    19. |  1    | 
    20. +-------+ 


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

     

    Ⅱ.OUT参数例子

    创建:

    1. mysql > DELIMITER // 
    2. mysql > CREATE PROCEDURE demo_out_parameter(OUT p_out int) 
    3. -> BEGIN 
    4. -> SELECT p_out; 
    5. -> SET p_out=2; 
    6. -> SELECT p_out; 
    7. -> END; 
    8. -> //  
    9. mysql > DELIMITER ; 


    执行结果:

    1. mysql > SET @p_out=1; 
    2. mysql > CALL sp_demo_out_parameter(@p_out); 
    3. +-------+ 
    4. | p_out |  
    5. +-------+ 
    6. | NULL  |  
    7. +-------+ 
    8.  
    9. +-------+ 
    10. | p_out | 
    11. +-------+ 
    12. |   2   |  
    13. +-------+ 
    14.  
    15. mysql> SELECT @p_out; 
    16. +-------+ 
    17. | p_out | 
    18. +-------+ 
    19. |   2   | 
    20. +-------+ 


    Ⅲ.INOUT参数例子

    创建:

    1. mysql > DELIMITER //  
    2. mysql > CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)  
    3. -> BEGIN 
    4. -> SELECT p_inout; 
    5. -> SET p_inout=2; 
    6. -> SELECT p_inout;  
    7. -> END; 
    8. -> //  
    9. mysql > DELIMITER ; 

     

     

    执行结果:
    1. mysql > SET @p_inout=1; 
    2. mysql > CALL demo_inout_parameter(@p_inout) ; 
    3. +---------+  
    4. | p_inout | 
    5. +---------+  
    6. |    1    | 
    7. +---------+  
    8.  
    9. +---------+  
    10. | p_inout |  
    11. +---------+  
    12. |    2    | 
    13. +---------+  
    14.  
    15. mysql > SELECT @p_inout; 
    16. +----------+  
    17. | @p_inout |  
    18. +----------+  
    19. |    2     | 
    20. +----------+ 
     

    (4).变量

    Ⅰ.变量定义

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

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

    例如:

    1. DECLARE l_int int unsigned default 4000000; 
    2. DECLARE l_numeric number(8,2) DEFAULT 9.95; 
    3. DECLARE l_date date DEFAULT '1999-12-31'; 
    4. DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59'; 
    5. DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';  

     

     

    Ⅱ.变量赋值

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

     

    Ⅲ.用户变量

     

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

    1. mysql > SELECT 'Hello World' into @x; 
    2. mysql > SELECT @x; 
    3. +-------------+ 
    4. |   @x        | 
    5. +-------------+ 
    6. Hello World | 
    7. +-------------+ 
    8. mysql > SET @y='Goodbye Cruel World'; 
    9. mysql > SELECT @y; 
    10. +---------------------+ 
    11. |     @y              | 
    12. +---------------------+ 
    13. | Goodbye Cruel World | 
    14. +---------------------+ 
    15.  
    16. mysql > SET @z=1+2+3; 
    17. mysql > SELECT @z; 
    18. +------+ 
    19. | @z   | 
    20. +------+ 
    21. |  6   | 
    22. +------+ 

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

    1. mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World'); 
    2. mysql > SET @greeting='Hello'; 
    3. mysql > CALL GreetWorld( ); 
    4. +----------------------------+ 
    5. | CONCAT(@greeting,' World') | 
    6. +----------------------------+ 
    7. |  Hello World               | 
    8. +----------------------------+ 

     

    ⅲ.在存储过程间传递全局范围的用户变量
    1. mysql> CREATE PROCEDURE p1()   SET @last_procedure='p1'; 
    2. mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_proc); 
    3. mysql> CALL p1( ); 
    4. mysql> CALL p2( ); 
    5. +-----------------------------------------------+ 
    6. | CONCAT('Last procedure was ',@last_proc |  
    7. +-----------------------------------------------+ 
    8. | Last procedure was p1                         | 
    9. +-----------------------------------------------+ 

     

     

    注意:

    ①用户变量名一般以@开头

    ②滥用用户变量会导致程序难以理解及管理

     

    (5).注释

     

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

    双模杠:--

    该风格一般用于单行注释

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

    例如:

     

    1. mysql > DELIMITER // 
    2. mysql > CREATE PROCEDURE proc1 --name存储过程名 
    3. -> (IN parameter1 INTEGER)  
    4. -> BEGIN  
    5. -> DECLARE variable1 CHAR(10);  
    6. -> IF parameter1 = 17 THEN  
    7. -> SET variable1 = 'birds';  
    8. -> ELSE 
    9. -> SET variable1 = 'beasts';  
    10. -> END IF;  
    11. -> INSERT INTO table1 VALUES (variable1); 
    12. -> END  
    13. -> //  
    14. mysql > DELIMITER ; 

     

    4.     MySQL存储过程的调用

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

    5.     MySQL存储过程的查询

    我们像知道一个数据库下面有那些表,我们一般采用showtables;进行查看。那么我们要查看某个数据库下面的存储过程,是否也可以采用呢?答案是,我们可以查看某个数据库下面的存储过程,但是是令一钟方式。

    我们可以用

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

    或者

    selectroutine_name from information_schema.routines whereroutine_schema='数据库名';

    或者

    show procedurestatus where db='数据库名';

    进行查询。

    如果我们想知道,某个存储过程的详细,那我们又该怎么做呢?是不是也可以像操作表一样用describe表名进行查看呢?

    答案是:我们可以查看存储过程的详细,但是需要用另一种方法:

    SHOW CREATEPROCEDURE数据库.存储过程名;

    就可以查看当前存储过程的详细。

     

    6.     MySQL存储过程的修改

    ALTER PROCEDURE

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

     

    7.     MySQL存储过程的删除

    删除一个存储过程比较简单,和删除表一样:

    DROPPROCEDURE

    从MySQL的表格中删除一个或多个存储过程。

     

    8.     MySQL存储过程的控制语句

    (1).变量作用域

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

     

     

    1. mysql > DELIMITER // 
    2. mysql > CREATE PROCEDURE proc3() 
    3.      -> begin 
    4.      -> declare x1 varchar(5) default 'outer'; 
    5.      -> begin 
    6.      -> declare x1 varchar(5) default 'inner'; 
    7.      -> select x1; 
    8.      -> end; 
    9.      -> select x1; 
    10.      -> end; 
    11.      -> // 
    12. mysql > DELIMITER ; 

     

     (2).条件语句

    Ⅰ. if-then-else语句

     

     

     

    1. mysql > DELIMITER // 
    2. mysql > CREATE PROCEDURE proc2(IN parameter int) 
    3.      -> begin 
    4.      -> declare var int; 
    5.      -> set var=parameter+1; 
    6.      -> if var=0 then 
    7.      -> insert into t values(17); 
    8.      -> end if; 
    9.      -> if parameter=0 then 
    10.      -> update t set s1=s1+1; 
    11.      -> else 
    12.      -> update t set s1=s1+2; 
    13.      -> end if; 
    14.      -> end; 
    15.      -> // 
    16. mysql > DELIMITER ; 


    Ⅱ. case语句:

    1. mysql > DELIMITER // 
    2. mysql > CREATE PROCEDURE proc3 (in parameter int) 
    3.      -> begin 
    4.      -> declare var int; 
    5.      -> set var=parameter+1; 
    6.      -> case var 
    7.      -> when 0 then  
    8.      -> insert into t values(17); 
    9.      -> when 1 then  
    10.      -> insert into t values(18); 
    11.      -> else  
    12.      -> insert into t values(19); 
    13.      -> end case; 
    14.      -> end; 
    15.      -> // 
    16. mysql > DELIMITER ; 

     

    (3).循环语句

    Ⅰ. while ···· endwhile:

    1. mysql > DELIMITER // 
    2. mysql > CREATE PROCEDURE proc4() 
    3.      -> begin 
    4.      -> declare var int; 
    5.      -> set var=0; 
    6.      -> while var<6 do 
    7.      -> insert into t values(var); 
    8.      -> set var=var+1; 
    9.      -> end while; 
    10.      -> end; 
    11.      -> // 
    12. mysql > DELIMITER ; 

     

     

    Ⅱ. repeat···· endrepeat:

    它在执行操作后检查结果,而while则是执行前进行检查。

    1. mysql > DELIMITER // 
    2. mysql > CREATE PROCEDURE proc5 () 
    3.      -> begin  
    4.      -> declare v int; 
    5.      -> set v=0; 
    6.      -> repeat 
    7.      -> insert into t values(v); 
    8.      -> set v=v+1; 
    9.      -> until v>=5 
    10.      -> end repeat; 
    11.      -> end; 
    12.      -> // 
    13. mysql > DELIMITER ; 

     


    Ⅲ. loop ·····endloop:

    loop循环不需要初始条件,这点和while循环相似,同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环。

    1. mysql > DELIMITER // 
    2. mysql > CREATE PROCEDURE proc6 () 
    3.      -> begin 
    4.      -> declare v int; 
    5.      -> set v=0; 
    6.      -> LOOP_LABLE:loop 
    7.      -> insert into t values(v); 
    8.      -> set v=v+1; 
    9.      -> if v >=5 then 
    10.      -> leave LOOP_LABLE; 
    11.      -> end if; 
    12.      -> end loop; 
    13.      -> end; 
    14.      -> // 
    15. mysql > DELIMITER ; 

     

     

    Ⅳ.LABLES标号:

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

     

    (4).ITERATE迭代

    Ⅰ.ITERATE:

    通过引用复合语句的标号,来从新开始复合语句

    1. mysql > DELIMITER // 
    2. mysql > CREATE PROCEDURE proc10 () 
    3.      -> begin 
    4.      -> declare v int; 
    5.      -> set v=0; 
    6.      -> LOOP_LABLE:loop 
    7.      -> if v=3 then  
    8.      -> set v=v+1; 
    9.      -> ITERATE LOOP_LABLE; 
    10.      -> end if; 
    11.      -> insert into t values(v); 
    12.      -> set v=v+1; 
    13.      -> if v>=5 then 
    14.      -> leave LOOP_LABLE; 
    15.      -> end if; 
    16.      -> end loop; 
    17.      -> end; 
    18.      -> // 
    19. 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 

     

    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个空格

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

    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 ) //

     

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

    转载 http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html   感谢博主分享
    展开全文
  • 一、存储过程优点 存储过程是一组预先创建并用指定的名称存储在数据库服务器上的 SQL 语句,将使用比较频繁或者比较复杂的操作,预先用 SQL 语句写好并存储起来,以后当需要数据库提供相同的服务时,只需再次执行该...

    总结下使用存储过程的优缺点
    一、存储过程优点
    存储过程是一组预先创建并用指定的名称存储在数据库服务器上的 SQL 语句,将使用比较频繁或者比较复杂的操作,预先用 SQL 语句写好并存储起来,以后当需要数据库提供相同的服务时,只需再次执行该存储过程。

    1.具有更好的性能

    存储过程是预编译的,只在创建时进行编译,以后每次执行存储过程都不需再重新编译,而一般 SQL 语句每执行一次就编译一次,因此使用存储过程可以提高数据库执行速度。

    2.功能实现更加灵活

    存储过程中可以应用条件判断和游标等语句,有很强的灵活性,可以直接调用数据库的一些内置函数,完成复杂的判断和较复杂的运算。

    3.减少网络传输

    复杂的业务逻辑需要多条 SQL 语句,当客户机和服务器之间的操作很多时,将产生大量的网络传输。如果将这些操作放在一个存储过程中,那么客户机和服务器之间的网络传输就会减少,降低了网络负载。

    4.具有更好的安全性

    (1)数据库管理人员可以更好的进行权限控制,存储过程可以屏蔽对底层数据库对象的直接访问,使用 EXECUTE 权限调用存储过程,无需拥有访问底层数据库对象的显式权限。

    (2)在通过网络调用过程时,只有对执行过程的调用是可见的。无法看到表和数据库对象名称,不能嵌入SQL 语句,有助于避免 SQL 注入攻击。

    二、存储过程的弊端

    1.架构不清晰,不够面向对象

    存储过程不太适合面向对象的设计,无法采用面向对象的方式将业务逻辑进行封装,业务逻辑在存储层实现,增加了业务和存储的耦合,代码的可读性也会降低,

    2.开发和维护要求比较高

    存储过程的编写直接依赖于开发人员,如果业务逻辑改动较多,需要频繁直接操作数据库,大量业务降维到数据库,很多异常不能在代码中捕获,出现问题较难排查,需要数据库管理人员的帮助。

    3.可移植性差

    过多的使用存储过程会降低系统的移植性。在对存储进行相关扩展时,可能会增加一些额外的工作。

    三、总结
    存储过程与SQL语句如何抉择?

    架构设计没有绝对,只有在当前的场景下最合适的。

    普通的项目开发中,不建议大量使用存储过程,对比SQL语句,存储过程适用于业务逻辑复杂,比较耗时,同时请求量较少的操作,例如后台大批量查询、定期更新等。

    (1)当一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时可以考虑应用存储过程

    (2)在一个事务的完成需要很复杂的商业逻辑时可以考虑应用存储过程

    (3)比较复杂的统计和汇总可以考虑应用后台存储过程

    展开全文
  • MySQL 存储过程

    千次阅读 多人点赞 2018-12-10 20:55:26
    MySQL 存储过程 存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象 存储过程就是具有名字的一段代码,用来完成一个特定的功能。 创建的存储过程保存在数据库的数据...

    MySQL 存储过程


    存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象

    存储过程就是具有名字的一段代码,用来完成一个特定的功能。
    创建的存储过程保存在数据库的数据字典中

    优点:
    存储过程可封装,并隐藏复杂的商业逻辑。
    存储过程可以回传值,并可以接受参数。
    存储过程无法使用 SELECT 指令来运行,因为它是子程序,与查看表,数据表或用户定义函数不同。
    存储过程可以用在数据检验,强制实行商业逻辑等。
    缺点:
    存储过程,往往定制化于特定的数据库上,因为支持的编程语言不同。当切换到其他厂商的数据库系统时,需要重写原有的存储过程。
    存储过程的性能调校与撰写,受限于各种数据库系统。

    存储过程的参数 :
    参数类型:
    IN输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)
    OUT输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
    INOUT输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)

    MYSQL 存储过程中的关键语法:

    delimiter//                            声明语句结束符,可以自定义(默认分号)
    create procedure 名称(声明参数1,...)                声明存储过程
    begin...end                            存储过程开始和结束符号
    declare 变量名 变量类型 (unsigned default 默认值)        定义变量
    set 变量名=变量值                        变量赋值
    参数类型(in|out|inout) 参数名 数据类型(int、varchar等)    声明参数


    例子:
    delimiter //                -- 将SQL语句的结束标记设置为//
    drop procedure if exists testprint;    -- 如果存在该名称的存储过程则删除
    create procedure testprint()        -- 创建一个名字为testprint的存储过程
    begin                     -- 代码部分开始
    select '您好!' as infos;        -- 存储过程的代码部分
    end;                     -- 代码部分结束
    //                    -- 结束标记
        
    delimiter ;                -- 编写变异结束后将SQL语句的结束标记还原
    call testprint;                -- 调用存储过程

    in输入参数:
    多个参数用逗号分开,SQL语句最后需加分号
    delimiter //
    create procedure addteacher(in p_tname varchar(20),in p_tsex varchar(4))
    begin 
    insert into teacher (tname,tsex) values(p_tname,p_tsex);
    end;//
    --调用存储过程使用call
    --@pp_tname临时变量:@+变量名
    delimiter ;
    set @pp_tname='王五';
    set @pp_tsex='男';
    call addteacher(@pp_tname,@pp_tsex);

    OUT输出参数:
    delimiter //
    drop procedure if exists selecttname;
    create PROCEDURE selecttname(in p_sname varchar(20),out p_tname varchar(20))
    begin 
    --使用select ... into ...,将查询结果赋值给参数
    select tname into p_tname from teacher 
    inner join class on ctid=tid
    inner join student on scid=cid
    where sname=p_sname;
    end;
    //
    delimiter ;
    set @sname='一一';
    --调用带有out输出参数的存储过程,作为输出参数的变量需要被赋值
    set @tname='';
    call selecttname(@sname,@tname);
    select @tname;

    条件语句

    if-then-else 语句
    以if开始 + 条件 + then + 操作 (+ else + 操作)+ 以end if结尾

    delimiter //
    create procedure proc(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;  
    //

    case语句
    case + 变量|参数 + when + 值1 + then + 操作 + when + 值2 + then + 操作... + else + 操作 + end case

    delimiter //
    create procedure proc(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;  
    //  

    循环语句
    一:
    while 条件 do  循环体 end while

    delimiter //
    create procedure proc()  
    begin 
    declare var int;  
    set var=0;  
    while var<6 do  
    insert into t values(var);  
    set var=var+1;  
    end while;  
    end;  
    //  

    二:
    repeat 循环体 until 循环条件  end repeat;

    delimiter //
    create procedure proc()  
    begin 
    declare v int;  
    set v=0;  
    repeat  
    insert into t values(v);  
    set v=v+1;  
    until v>=5  
    end repeat;  
    end;  
    //
      
    三:
    名称(自定义):loop 循环体 if 条件 leave 名称 end if end loop

    delimiter //
    create procedure proc()  
    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;  
    //  

    JDBC调用存储过程

    不带输出参数:

    //加载驱动
    Class.forName("com.mysql.jdbc.Driver");
    //获得连接
    String url = "jdbc:mysql://127.0.0.1:3306/lianxi1";
    String user = "root";
    String userPass = "123456";
    Connection con = DriverManager.getConnection(url, user, userPass);
    //创建存储过程的对象  
    java.sql.CallableStatement  cs = con.prepareCall("{call addstu(?,?)}");
    //给存储过程的参数设置值
    cs.setString(1, "张三");
    cs.setInt(2, "男");
    //执行存储过程 
    n = cs.executeUpdate();

    带输出参数:
    //加载驱动
    Class.forName("com.mysql.jdbc.Driver");
    //获得连接
    String url = "jdbc:mysql://127.0.0.1:3306/lianxi1";
    String user = "root";
    String userPass = "123456";
    Connection con = DriverManager.getConnection(url, user, userPass);
    //创建存储过程的对象  ,querystu根据id查姓名
    java.sql.CallableStatement  cs = con.prepareCall("{call querystu(?,?)}");
    //给存储过程的参数设置值,第一个参数为id
    cs.setInt(1, 2);
    //第二个参数,为out name varchar(20)
    //注册存储过程的第二个参数registerOutParameter(),
    cs.registerOutParameter(2, java.sql.Types.VARCHAR);
    //执行存储过程
    cs.execute();
    //得到存储过程的输出参数值,如果输出为int类型则使用getInt();
    String name = cs.getString(2);


    Mybatis调用存储过程

    有输出参数时:
    XML映射文件内书写语句需要加属性statementType="CALLABLE"
    注释书写SQL语句需要声明:@Options(statementType=StatementType.CALLABLE)
    输出参数写法:#{name,mode=OUT,jdbcType=VARCHAR} ,OUT、VARCHAR需要大写

    XML文件内书写
    不带输出参数:statementType="CALLABLE"可以省略    
    <insert id="addclass" parameterType="map" statementType="CALLABLE">
        call addstu(#{name},#{sex})
    </insert>

    带输出参数:
    注意!传入的参数类型直接为:parameterType="String"无法获得输出参数
    <select id="selecttname" statementType="CALLABLE" parameterType="map">
        call selectname(#{id},#{name,mode=OUT,jdbcType=VARCHAR})
    </select>
    带输出参数的调用:
    String str = "config/mybatis-config.xml";
    InputStream is = Resources.getResourceAsStream(str);
    SqlSessionFactory ss = new SqlSessionFactoryBuilder().build(is);
    SqlSession s = ss.openSession();
    Map<Object,String> map = new HashMap<>();
    map.put("id", 1);
    s.selectOne("selectname",map);
    System.out.println(map.get("name"));

    注解模式,带输出参数写法:
    @Select("call selectname(#{id},#{name,mode=OUT,jdbcType=VARCHAR})")
    @Options(statementType=StatementType.CALLABLE)
    public void selecttname1(Map<Object,String> map);
     

    展开全文
  • MySQL存储过程

    千次阅读 2016-06-26 19:42:40
    MySQL存储过程存储过程是SQL语句和控制语句的预编译集合,以一个名称存储并作为一个单元处理。1、MySQL执行流程,通过存储过程,可以避免重复语法分和编译,提高执行效率2、存储过程: (1)是SQL语句与控制语句的...
  • mysql存储过程详解 1. 存储过程简介   我们常用的操作数据库语言SQL语句在执行的时候需要要先编译,然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在...
  •  存储过程优点如下: 重复使用。存储过程可以重复使用,从而可以减少数据库开发人员的工作量。提高性能。存储过程在创建的时候在进行了编译,将来使用的时候不再重新翻译。一般的SQL语句每执行一次就需要编译一次,...
  • MYSQL存储过程

    2014-07-18 16:16:48
    MYSQL存储过程 存储过程通常有以下优点: (1).存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。 (2).存储过程允许标准组件是编程。...
  • 存储过程 优点: 提高开发效率,提高应用程序的性能,简化程序开发人员的操作,减少应用程序和是数据库服务器之间的流量。 缺点: 难以调试,开发和维护难度高,增加数据库服务器开销。 创建存储过程及其一些相关...
  • mysql存储过程详解

    2020-12-15 19:36:59
    1.存储过程简介 我们常用的操作数据库语言SQL语句在执行的时候需要要先编译,然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译... 存储过程通常有以下优点: (1).存储过程增强了SQL
  • 我们大家都知道MySQL 存储过程是从 MySQL 5.0 开始逐渐增加新的功能。存储过程在实际应用中也是优点大于缺点。不过最主要的还是执行效率和SQL 代码封装。特别是 SQL 代码封装功能,如果没有存储过程
  • Mysql存储过程

    千次阅读 2021-07-03 14:48:30
    存储过程优点 提高执行性能; 减轻网络负担; 可将数据库数据的处理黑匣子化; 创建存储过程 //没有返回值的存储过程 create procedure pro01(name varchar(10)) begin if name is null or name = " " then select...
  • MySql存储过程优点与缺点

    千次阅读 2019-05-08 14:10:59
    MySql的存储过程的优点与缺点一、存储过程优点二、存储过程的弊端三、总结 总结下使用存储过程的优缺点 一、存储过程优点 存储过程是一组预先创建并用指定的名称存储在数据库服务器上的 SQL 语句,将使用比较频繁...
  • mysql 存储过程

    千次阅读 2016-04-05 17:09:22
    查询存储过程 show procedure status; select name from mysql.proc where db = ‘your_db_name’ and type = ...创建存储过程mysql> DELIMITER // mysql> CREATE PROCEDURE proc1(OUT s int) -> BEGIN ->
  • MySQL存储过程详解 mysql 存储过程,if,while常用语句

    万次阅读 多人点赞 2014-03-03 20:38:01
    mysql存储过程详解 1. 存储过程简介   我们常用的操作数据库语言SQL语句在执行的时候需要要先编译,然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,...
  • mysql存储过程详解实例

    万次阅读 多人点赞 2016-12-23 10:23:17
    mysql存储过程详解实例
  • MySql 存储过程

    2009-10-23 22:52:00
    存储过程优点有一箩筐。不过最主要的还是执行效率和SQL 代码封装。特别是 SQL 代码封装功能,如果没有存储过程,在外部程序访问数据库时(例如 PHP),要组织很多 SQL 语句。特别是业务逻辑复杂的时候,一大堆的 ...
  • 初识MySQL存储过程

    千次阅读 多人点赞 2014-10-27 08:14:58
    MySQL 存储过程是从 MySQL 5.0 开始增加的新功能。大大提高数据库的处理速度,同时也可以提高数据库编程的灵活性。 过程:封装起来的若干条语句,调用时这些封装体执行。把此过程存储到数据库中即存储过程
  • mysql存储过程详解 1. 存储过程简介   我们常用的操作数据库语言SQL语句在执行的时候需要要先编译,然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在...
  • 本文实例讲述了mysql存储过程原理与使用方法。分享给大家供大家参考,具体如下: 存储过程包含了一系列可执行的sql语句,存储过程存放于MySQL中,通过调用它的名字可以执行其内部的一堆sql 存储过程的优点 #1. 用于...
  • mysql存储过程详解 1. 存储过程简介   我们常用的操作数据库语言SQL语句在执行的时候需要要先编译,然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在...
  • MySQL 存储过程创建

    千次阅读 2018-11-26 21:09:52
    优点(为什么要用存储过程?): ①将重复性很高的一些操作,封装到一个存储过程中,简化了对这些SQL的调用 ②批量处理:SQL+循环,减少流量,也就是“跑批” ③统一接口,确保数据的安全 相对于oracle数据库...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 75,901
精华内容 30,360
关键字:

mysql存储过程优点

mysql 订阅