网站导航
> 文章列表 > Mysql——函数和存储过程

Mysql——函数和存储过程

网友: 文章列表 2024-03-22 04:57:40

Mysql——函数和存储过程

目录

  • 函数
    • 创建自定义函数
    • 删除函数
    • 调用自定义函数
    • 案例
      • 生成指定范围的随机整数
  • 储存过程
  • 存储过程和函数的区别

函数

MySQL函数（自定义函数），MySQL存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，函数一般用于计算和返回一个值，通常用来进行一些计算。

创建自定义函数

CREATE FUNCTION 函数名(参数1 参数类型， 参数2 参数类型)
RETURNS 返回值类型
BEGINDECLARE 变量 变量类型；SET 变量 = 变量值；SELECT...INTO 变量；RETURN 表达式/结果/查询；
END

如果一次输入的语句较多，并且语句中间有分号，这时需要新指定一个特殊的分隔符。
这个时候就需要用到DELIMITER

告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了。
默认情况下，delimiter是;

DELIMITER $$ 
CREATE FUNCTION 函数名(参数1 参数类型， 参数2 参数类型)
RETURNS 返回值类型
BEGINDECLARE 变量 变量类型；SET 变量 = 变量值；SELECT...INTO 变量；RETURN 表达式/结果/查询；
END$$ 
DELIMITER ;

删除函数

DROP FUNCTION 函数名

调用自定义函数

SELECT 函数名

案例

生成指定范围的随机整数

CREATE FUNCTION random_int_num(low INT,high INT)
RETURNS INT
BEGINRETURN 	ROUND(RAND()*(high-low)+low,0);
END;

如上通过输入范围的最小值和最大值进行返回范围内的随机整数。

如果希望函数进行根据大小自动判断范围，并返回范围内的随机整数。

DELIMITER $$
CREATE FUNCTION random_int_num(i INT,j INT)
RETURNS INT
BEGINDECLARE result INT;IF(i>j)THENSET result=ROUND(RAND()*(i-j)+j,0);ELSESET result=ROUND(RAND()*(j-i)+i,0);END IF;RETURN result;
END
$$

储存过程

就是一组经过预先编译的 SQL 语句的封装，需要执行的时候就可以把预先存储好的这一系列 SQL 语句全部执行

创建存储过程

DELIMITER //
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN存储过程体
END;
DELIMITER ;

删除存储过程

DROP PROCEDURE 存储过程名

调用存储过程

CALL 存储过程名(实参列表)
#如果你用的是其他数据库中的存储过程,哪就需要指定数据库名
CALL 库名.存储过程名(实参列表)

参数

MySQL存储过程的参数共有三种：IN \\ OUT \\ INOUT

  • IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
  • OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
  • INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

案例

向dept部门表批量插入随机数据

dept表结构

CREATE TABLE dept(  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,   dname VARCHAR(20) NOT NULL DEFAULT "",  loc VARCHAR(13) NOT NULL DEFAULT ""  ) ENGINE=INNODB DEFAULT CHARSET=UTF8 ;

随机产生字符串函数

DELIMITER $$
CREATE FUNCTION random_str(n INT)
RETURNS VARCHAR(255)
BEGINDECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ0123456789';DECLARE return_str VARCHAR(255) DEFAULT '';DECLARE i INT default 0;WHILE i < n DO  SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*62),1));
##concat 连接函数  ，substring(a,index,length) 从index处开始截取SET i = i + 1;END WHILE;RETURN  return_str;
END $$

插入部门数据的存储过程

DELIMITER $$CREATE PROCEDURE insert_dept (in deptno_prefix int,in max_num int(10))BEGIN DECLARE i INT DEFAULT 0;set autocommit = 0;REPEATSET 	i= i+1;INSERT INTO dept(deptno,dname,loc) value (deptno_prefix+i,random_str(10),random_str(8));UNTIL i = max_numEND REPEAT;COMMIT;END $$

优缺点

优点

  1. 存储过程可以一次编译多次使用。 存储过程只在创建时进行编译，之后的使用都不需要重新编译，这就提升了 SQL 的执行效率。
  2. 可以减少开发工作量。将代码封装成模块，实际上是编程的核心思想之一，这样可以把复杂的问题拆解成不同的模块，然后模块之间可以重复使用，在减少开发工作量的同时，还能保证代码的结构清晰。
  3. 存储过程的安全性强。我们在设定存储过程的时候可以设置对用户的使用权限，这样就和视图一样具有较强的安全性。
  4. 可以减少网络传输量。因为代码封装到存储过程中，每次使用只需要调用存储过程即可，这样就减少了网络传输量。
  5. 良好的封装性。在进行相对复杂的数据库操作时，原本需要使用一条一条的 SQL 语句，可能要连接多次数据库才能完成的操作，现在变成了一次存储过程，只需要连接一次即可。

缺点

  1. 可移植性差。存储过程不能跨数据库移植，比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程，在换成其他数据库时都需要重新编写。
  2. 调试困难。只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。虽然也有一些第三方工具可以对存储过程进行调试，但要收费。
  3. 存储过程的版本管理很困难。比如数据表索引发生变化了，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。
  4. 它不适合高并发的场景。

存储过程和函数的区别

  1. 存储过程的用户在数据库中完成特定的操作或者任务（例如插入、删除等），而函数则用于返回特定的数据；
  2. 存储过程的声明使用procedure关键字，而函数的声明则使用function关键字；
  3. 存储过程不需要返回类型，而函数则必须要返回类型；
  4. 存储过程可以作为独立的PL-SQL执行，而函数则不能作为独立的PL-SQL执行，必须作为表达式的一部分；
  5. 存储过程只能通过out和in/out来返回值，而函数除了可以使用out和in/out以外，还可以使用return来返回值，而且函数中必须要有return子句；
  6. SQL语句（DML或者SELECT）中不可用调用存储过程，而函数可以；
  7. 执行方式略有不同，存储过程的执行方式有两种（使用execute，使用begin和end），函数除了存储过程的两种方式外，还可以作为表达式使用（例如放在select中）
网络标签：

相关问题