Mysql_sql存储过程

#存储过程

    存储过程和函数：类似于java中的方法

    好处：

        1.提高代码的重用性

        2.简化操作

        #存储过程  procedure

            含义：一组预先编译好的SQL语句的集合，理解成批处理语句

            1.提高代码的重用性

            2.简化操作

            3.减少了编译次数并且减少了和数据服务器的连接次数，提高了效率

 

 #一、创建语法 begin end类型于{}

            CREATE PROCEDURE 存储过程名(参数列表)

            BEGIN

                存储过程体(一组合法的SQL语句)

            END

        注意：

         1.参数列表包含三部分

            参数模式 参数名 参数类型

            举例：

                IN stuname VARCHAR(20)

         参数类型：

             IN ：该参数可以作为输入，也就是该参数需要调用方传入值

             OUT ：该参数可以作为输出，也就是该参数可以作为返回值

             INOUT ：该参数可以作为输入又可以作为输出，也就是参数既需要传入值，又可以返回值

        2.如果存储过程体仅仅只有一句话，begin end可以省略

            存储过程中的每条SQL语句的结尾必须加分号。

            存储过程的结尾可以使用 DELIMITER 重新设置 delimter $结束标记 再遇到$符号就执行

            语法：

                DELIMITER 结束标记

            案例： #

                DELIMITER $

 

#二、调用语法

            CALL 存储过程名(实参列表);

 

存储过程案例

    #1、空参列表

        #案例：插入到admin表中五条记录

        SELECT * FROM admin;

#delimter $ 声明这个$符号遇到 end$时 当遇到这个符号再执行整段语句 

  因为储存过程中的sql语句结尾分号 如果不加标记编译器认为到分号就结束了

 

        DELIMITER $    

        CREATE PROCEDURE myp1()

        BEGIN

            INSERT INTO admin(username,`password`)

            VALUE('tom',777),('lily',577),('luce',767),('jack',797),('sim',377);

        END $

复制到mysql自带客户端

        

 

 #调用存储结构 call

            CALL myp1()$  成功的的插入五条数据

前面声明了遇到$符号才执行 我在select查询时 用分号无效用$符回车才执行select语句

        

 

#2、创建带in模式参数的存储过程

      #案例1.创建存储过程实现 根据女神名，查询对应的男神名

        这里的女神名需要当作参数

        CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))

        BEGIN

                SELECT bo.*

                FROM boys bo

                RIGHT JOIN beauty gr ON gr.`boyfriend_id`=bo.`id`

                WHERE gr.name=beautyName;

        END $

      

 

    #调用

    CALL myp2('小昭')$  传入形参

    

 

    #案例2.创建存储过程实现，用户是否登录成功

        CREATE PROCEDURE myp3(IN username VARCHAR(20),IN `password` VARCHAR(20))

        BEGIN

            DECLARE result INT DEFAULT 0;#声明局部变量并赋值

    

            SELECT COUNT(*) INTO result #满足用户名密码与数据库中数据一致时 筛选条件满足count加1 赋值给result变量

            FROM admin a

            WHERE a.username=username

            AND a.password=`password`;

    

            SELECT IF(result>0,'成功','失败');  #查看局部变量 如果result>0则where筛选条件成功 count加1

        END $

        

传入的值满足筛选条件传入name与password与 admin表中一致的话count(*)就将满足的数据加1，赋值给result。如果result大于0返回成功，反之返回失败

        调用存储过程  

        CALL myp3('join','8888')$   admin 数据john 8888

        

 

    #3、创建带out模式的存储过程

        #案例1：根据女神名，返回对应的男神名

            CREATE PROCEDURE myp4(IN girlName VARCHAR(20),OUT boyname VARCHAR(20))

            BEGIN

                SELECT bo.boyname INTO boyname #满足传入的女神名=连接查询的gir.name into赋值给 输出模式boyname

                FROM boys bo

                INNER JOIN beauty gir ON bo.id=gir.`boyfriend_id`

                WHERE gir.name = girlName;

            END $

        

        #调用

        #定义一个用户变量

            SET @boyname=''$   #接收返回值的变量

        #调用myp4

            CALL myp4('热巴',@boyname)$

        #查看用户变量@boyname

            SELECT @boyname$

        

 

    #案例2：根据女神名，返回对应的男身名和魅力值

        多个out模式 bo.boyName,bo.userCP INTO boyName,userCP 满足筛选条件的 将男神名与CP值分别into到两个输出变量

            CREATE PROCEDURE mpy5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)

            BEGIN

                SELECT bo.boyName,bo.userCP INTO boyName,userCP

                FROM boys bo

                INNER JOIN beauty gir ON bo.id=gir.boyfriend_id

                WHERE gir.name=beautyName;

            END $

    /*set @boyname;

    set @usercp;

    也可不用定义*/

 

    #调用mpy5

        CALL mpy5('小昭',@boyname,@usercp)$

    #查看调用结果

        SELECT @boyname,@usercp$

        

        

 

    #4.创建带inout模式参数的存储过程

        #案例1：传入a和b两个值，最终a和b都翻倍并返回

            CREATE PROCEDURE myp6(INOUT a INT,INOUT b INT)

            BEGIN

                SET a=a*2;   #a和b都为局部变量 直接*2

                SET b=b*2;

            END $

        

        #定义用户变量赋值

            SET @a=8$

            SET @b=9$

        #调用 并传入参数

            CALL myp6(@a,@b)$

        #查看调用存储过程结果

            SELECT @a,@b$

        

 

#三、删除存储过程

        语法： DROP PROCEDURE 存储过程名

        DROP PROCEDURE test2;

        DROP PROCEDURE test1,test2;#错误的语法 报错

 

#四、查看存储过程的信息

        DESC myp2;#错误的存储信息

        SHOW CREATE PROCEDURE myp2;

        

 

#存储过程案例

    #一、创建存储过程或函数实现传入用户名和密码，插入到admin表中

        CREATE PROCEDURE test1(IN `name` VARCHAR(10),IN `passwords` VARCHAR(16))

        BEGIN

            INSERT INTO admin(username,`password`)

            VALUE(`name`,passwords);

        END $

        #调用test1

        CALL test1('jack','6666')$

    存储过程成功创建 与 调用成功插入

    

    SELECT * FROM admin$

    

 

    #二、创建存储过程或函数实现传入女神编号，返回女神名称和女神电话

        CREATE PROCEDURE test2(IN beautyId INT,OUT beautyName VARCHAR(20),OUT phone VARCHAR(11))

        BEGIN

            SELECT gir.name,gir.phone INTO beautyName,phone

            FROM beauty gir

            WHERE gir.id=beautyId;

        END $

    #调用test2 传入女神3 ID

        CALL test2(3,@beautyName,@phone)$

        SELECT @beautyName,@phone$

        

 

    #三、创建存储过程或函数实现传入两个女神生日，返回大小

        CREATE PROCEDURE test3(INT date1 DATETIME,INT date2 DATETIME,OUT result VARCHAR(2))

        BEGIN

            SELECT DATEDIFF(date1,date2) INTO result;

            SELECT result,

            CASE

                WHEN result>0 THEN '大'

                WHEN result<0 THEN '小'

                ELSE '相等'

            END;

        END $

        CALL test3(NOW(),'1996-1-6',@result)$