mysql存储过程总结

程序员文章站 2022-06-03 23:53:11

...

N年没写过存储过程了，突然要写存储一下都忘记完了，现在回顾总结。
先废话的说下mysql中的变量有1.系统变量和 2.自定义变量

系统变量：变量由系统提供，不是用户定义，属于服务器层面
a.查看所有的系统变量: show global | session variables;
b.查看满足条件的部分系统变量: show global | 【session】 variables like '%char%';
c.查看指定的某哥系统变量的值:
select @@global | 【session】.系统变量名;
d.为某个系统变量赋值
方法一:
set global | 【session】系统变量名 = 值；
方法二:
set @@global | 【session】.系统变量名 = 值；
e.全局变量
1.查看所有的全局变量:
SHOW GLOBAL VARIABLES;
1. 查看部分的全局变量:
  SHOW GLOBAL VARIABLES LIKE '%char%';
  3.查看指定的全局变量的值:
  SELECT @@global.autocommit;
  SELECT @@tx_isolation;
  4.为某个指定的全局变量赋值:
  SET @@global.autocommit=0;
  f.会话变量
  1.查看所有的会话变量
  SHOW VARIABLES;
  SHOW SESSION VARIABLES;
  2.查看部分的会话变量
  SHOW VARIABLES LIKE '%char%';
  SHOW SESSION VARIABLES LIKE '%char%'；
  3.查看指定的某个会话变量
  SELECT @@tx_isolation;
  SELECT @@session.tx_isolation;
  4.为某个会话变量赋值
  方式一:
  SET @@tx_isolation='read-uncommitted';
  方式二:
  SET SESSION tx_isolation='read-committed';
自定义变量: 变量是用户自定义的，不是系统的
作用域: 针对于当前会话(连接)有效，同于会话变量的作用域。应用在任何地方，也就是begin end里面或begin end外面
2.1用户变量
赋值的操作符: = 或 :=
a.声明并初始化
SET @用户变量名=值；
或 SET @用户变量名:=值；
或 SELECT @用户变量名:=值
b.赋值(更新用户变量的值)
方式一: 通过SET 或 SELECT
SET @用户变量名=值；
或 SET @用户变量名:=值；
或 SELECT @用户变量名:=值；
方式二: 通过SELECT INTO
SELECT 字段 INTO @变量名 FROM 表；
c.使用(查看用户变量的值)
SELECT @用户变量名;

例子:
#声明并初始化
SET @name='john';
SET @name=100;
SET @count=1;
#赋值
SELECT COUNT( * ) INTO @count FROM 表名
#查看
SELECT @count;

2.2 局部变量
         作用域: 仅仅在定义它的begin end 中有效,应用在begin end中的第一句                      话
  a.声明
          DECLARE 变量名 类型；
            DECLARE 变量名 类型 DEFAULT 值；
      b.赋值
           方式一: 通过SET 或 SELECT
                         SET 局部变量名=值；
                   或  SET 局部变量名:=值；
                   或  SELECT @局部变量名:=值；
             方式二: 通过SELECT INTO
                  SELECT 字段 INTO 局部变量名 FROM 表
         c.使用
            SELECT 局部变量名；

对比用户变量和局部变量
用户变量：
作用域: 当前会话
定义和使用的位置: 会话中的任何地方
语法: 必须加@符合，不用限定类型
局部变量：
作用域: BEGIN END中
定义和使用的位置: 只能在BEGIN END中，且为第一句话
语法: 一般不用加@符合，需要限定类型

例如: 声明两个变量并赋初始值，求和，并打印
SET @m=1;
SET @n=2;
SET @sum = @m + @n;
SELECT @sum;

存储过程:
使用存储过程的好处: 1.提高代码的重用性； 2.简化操作； 3.减少了编译次数并减少了和数据库服务器的连接次数，提高了效率
含义: 一组预先编译好的SQL语句的集合，理解成批处理语句
一. 创建语法:
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体(一组合法的SQL语句)
END
注意: 1.参数列表包含三部分：参数模式(IN、OUT、INOUT) , 参数名，参数类型
例如: IN stuname VARCHAR(20)
参数模式:
IN:该参数可以作为输入，也就是该参数需要调用方法入值
OUT:该参数可以作为输出，也就是该参数可以作为返回值
INOUT:该参数即可以作为输入又可以作为输出，也就是该参数即需要传入值，又可以返回值
2.如果存储过程体仅仅只有一句话，BEGIN END可以省略
存储过程体中的每条SQL语句的结尾要求必须加分号。
存储过程的结尾可以使用DELMITER重新设置，语法:
DELIMITER 结束标记
例如: DELIMITER $
二.调用语句
CALL 存储过程名(实参列表)；

     例如:
        1.空参列表
           #例:插入到admin表中五个记录
                 DELIMITER $
                 CREATE PROCEDURE myp1()
                 BEGIN
                      INSERT INTO admin(username, password)
                            VALUES('name1','1111'),('name2','1111'),('name3','1111'),('name4','1111'),('name5','1111');
                 END $

                 调用: CALL myp1() $
    2.创建带IN模式参数的存储过程
     例如: 创建存储过程实现 根据名字查询对应的信息
        CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
            BEGIN
                 SELECT tb1.*
                     FROM table1 tb1
                     RIGHT JOIN table2 tb2 on tb1.id = tb2.nameId
                     WHERE tb1.name = tb2.name
            END $
            调用: CALL myp2('姓名') $

    例如:创建存储过程实现,用户是否登陆成功
    CREATE PROCEDURE myp3(IN username VARCHAR(20), IN password VARCHAR(20))
    BEGIN
         DECLARE result INT DEFAULT 0;  #声明并初始化
             SELECT COUNT(*) INTO result #赋值
             FROM admin
             WHERE admin.username = username
             AND admin.password = password;

             SELECT IF(result>0, '成功', '失败');  #使用
    END $
    调用:  CALL myp3('name','password') $

    3.带返回值的
    例如:根据gril姓名,返回对应的boy姓名
    CREATE PROCEDURE myp5(IN grilName VARCHAR(20), OUT boyName VARCHAR(20))
    BEGIN
         SELECT b.boyName INTO boyName
             FROM boys b
             INNER JOIN gril g ON b.id = g.boy_id
             WHERE g.name = grilName;
    END $
    调用: CALL myp5('name', @bName) $
    输出: SELECT @bName$

    例如: 根据gril姓名,返回对应的boy的姓名和魅力值
    CREATE PROCEDURE myp6(IN gName VARCHAR(20),  OUT bName VARCHAR(20), OUT bCp INT)
    BEGIN
        SELECT b.boyname, b.userCp INTO bName, bCp
            FROM boys b
            INNER JOIN gril g on b.id = g.boy_id
            WHERE g.name = gName
    END $
    调用: CALL myp6(‘name’, @bName, @usercp)$
    输出: SELECT @bName, @usercp

    4.创建带INOUT模式参数的存储过程
    例如: 传入a和b两个值，最终a和b都翻倍并返回
    CREATE PROCEDURE myp8(INOUT a INT, INOUT b INT )
    BEGIN
         SET a=a*2;
             SET b=b*2;
    END $
    调用:
        SET @m=10$
            SET @n=20$
            CALL myp8(@m, @n)$
            SELECT @m,@n

#一、创建存储过程实现传入用户名和密码,插入到admin表中
CREATE PROCEDURE test_pro1(IN username VARCHAR(20), IN loginPwd VARCHAR(20))
BEGIN
       INSERT INTO admin(admin.username, admin.password)
             VALUES(username, loginPwd);
END $
#二、创建存储过程实现传入女神编号,返回女神名称和女神电话
CREATE PROCEDURE test_pro2(IN id INT, OUT name VARCHAR(20), OUT phone VARCHAR(20))
BEGIN
        SELECT b.name, b.phone INTO name,phone
                FROM gril b
                WHERE b.id = id
END $
#三、创建存储过程实现传入两个生日，返回大小
 CREATE PROCEDURE test_pro3(IN birth1 DATETIME, IN birth2 DATETIME, OUT result INT)
 BEGIN
       SELECT DATEDIFF(birth1, birth2) INTO result;
 END $
    #四、创建存储过程实现传入一个日期，格式化成为YYYY年MM月DD日并返回
    CREATE PROCEDURE test_pro4(IN mydate DATETIME, OUT strDate VARCHAR(50))
    BEGIN
       SELECT DATE_FORMAT(mydate, '%y年%m月%d日') INTO strDate;
    END $
 CALL test_pro4(NOW(), @str)$
 SELECT @str $

    #五、创建存储过程实现分页的效果
    CREATE PROCEDURE test_pro6(IN startIndex INT, IN size INT)
    BEGIN
       SELECT * FROM admin LIMIT  startIndex, size;
    END $

 3.删除存储过程
 语法: DROP PROCEDURE 存储过程名
 ![](https://s1.51cto.com/images/blog/201912/11/0dd990e41b7c87c4d79badb99502788b.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)

 4.查看存储过程
 SHOW CREATE PROCEDURE 存储名称;

mysql存储过程总结函数:
函数和存储过程的区别:
存储过程:可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数:有且仅有1个返回，适合做处理数据后返回一个结果

一、创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
          函数体
END
注意: 
1.参数列表   包含两个部分:参数名     参数类型
2.函数体:肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错，不建议
return 值；
3.函数体中仅有一句话，则可以省略BEGIN END
4.使用DELIMITER语句设置结束标记

二、调用语法
SELECT 函数名(参数列表)

例如:
  #1.无参有返回
    CREATE FUNCTION my1() RETURNS INT
    BEGIN
              DECLARE c INT DEFAULT 0; #定义变量
                        SELECT COUNT(*) INTO c； #赋值
                        FROM employees;
                        RETURN c;
    END $
    调用: SELECT myf1()$

    #2.有参有返回
    CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
    BEGIN
          SET @sal=0; #定义用户变量
                SELECT salary INTO @sal #赋值
                FROM employees
                WHERE last_name = empName;
                RETURN @sal;
    END $

    SELECT myf2('k-ing') $

    CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
    BEGIN
        DECLARE sal DOUBLE;
            SELECT AVG(salary) INTO sal
            FROM employees e
            JOIN departments d ON e.department_id = d.department_id
            WHERE d.department_name = deptName;
            RETURN sal;
    END $

    SELECT myf3('IT')$

二、查看函数
SHOW CREATE FUNCTION 函数名；

三、删除函数
DROP FUNCTION 函数名；

mysql存储过程总结

SQLSERVER查询锁表，阻塞，使用表名查询存储过程，行数，表字段类型等常规查询实例讲解

MySQL 支持 emoji 图标存储

MySQL面试总结

mysql存储过程原理与使用方法详解

mysql存储过程之错误处理实例详解

MySQL千万级大数据SQL查询优化知识点总结

mysql存储过程之返回多个值的方法示例

sql获取存储过程返回数据过程解析

Mysql通过存储过程分割字符串为数组

解析:php调用MsSQL存储过程使用内置RETVAL获取过程中的return值