SQL存储过程

SQL存储过程

​ 存储过程就是一条或多条SQL语句的集合，可以视为批处理文件。它可以定义批量插入的语句，也可以定义一个接收不同条件的SQL。

• create procedure 创建存储过程
• create function 创建存储函数
• 调用存储过程的语句为CALL。
• 调用存储函数的形式就像调用MYSQL内部函数一样。

1. MySQL中delimiter

   • 告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行。
   • 默认情况下，delimiter是分号。
   • 在命令行客户端中，如果有一行以分号结束，那么回车后，mysql将会执行该命令。

2. SQL代码实例

   • 创建存储函数

     DROP FUNCTION IF EXISTS getStuNameById;
     
     DELIMITER //
     CREATE FUNCTION getStuNameById(sid INT)
     RETURNS VARCHAR(255)
     RETURN (SELECT NAME FROM USER WHERE id=sid);//
     
     DELIMITER ;
     
     SELECT getStuNameById(5) AS sname;
     

   • 变量操作

     用declare可以声明SQL语句变量，作用范围为BEGIN…END中，没有default子句，初始值为NULL。

     DECLARE name,address VARCHAR;  -- 发现了吗，SQL中一般都喜欢先定义变量再定义类型，与Java是相反的。
     DECLARE age INT DEFAULT 20; -- 指定默认值。若没有DEFAULT子句，初始值为NULL。
     

     变量赋值

     SET name = 'jay';  -- 为name变量设置值
     

   • 创建存储过程

     DROP PROCEDURE IF EXISTS conStById;
     
     DELIMITER //
     CREATE PROCEDURE conStById(IN sid INT(11),OUT result INT(11))
     BEGIN
     DECLARE sCount INT DEFAULT 0;
     SELECT COUNT(*) INTO sCount FROM USER WHERE id>sid;
     SET result=sCount;
     END
     //
     
     DELIMITER ;
     
     CALL conStById(2,@result);
     SELECT @result AS res;
     

     存储过程在创建之后会一直存留在mysql服务器，可以直接重复调用。

     注意：
     1）在函数传递到存储过程的时候，in定义传入参数，out定义传出结果值参数。

     ​ 2）在传入返回值结果值时，需要使用@result表示，取值也需要这样用，但是在过程内部不需要这样使用。

   • 智能存储过程

     场景：需要获得和以前一样的订单合计，但是需要对合计增加营业税

     要求：

     ​ a.获得合计（和以前一样）

     ​ b.把营业税有条件的添加到合计

     ​ c.返回合计

     CREATE     PROCEDURE ordertotal (
     IN onumber INT,
     IN taxable BOOLEAN,
     OUT ototal DECIMAL(8,2)
     ) COMMENT 'Obtain order total, optionally adding tax'
     BEGIN
         
             -- Declare variable for total
             DECLARE total DECIMAL(8,2);     //   声明变量   
             -- Declare tax percentage
             DECLARE taxrate INT DEFAULT 6;
             
             -- Get the order total
             SELECT Sum(item_price*quantity)
             FROM orderitems
             WHERE order_num = onumber
             INTO total;
             
             -- Is this taxable?
             IF taxable THEN
                 -- yes,so add taxrate to the total
                 SELECT total+(total/100*taxrate) INTO total;
             END IF;
             --  And finally, save to out variable
             SELECT total INTO ototal;
     END