Mysql 存储过程简介--实现存储过程

最新推荐文章于 2024-08-21 00:17:51 发布

阅读量10w+

点赞数 60

分类专栏： Mysql 文章标签： Mysql

本文链接：https://blog.csdn.net/weixin_44520739/article/details/86608618

版权

Mysql 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

什么是存储引擎：

Mysql中的数据用各种不同的技术存储在文件（或者内存）中，每一种技术都使用不同的存储机制，
索引技巧锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的额技术你能过够获得额外的速度或者功能从而改善你的应用整体功能。不同的存储引擎性能是不一的你能够获得额外的速度或者功能，从而改善你的应用的整体功能。查看Mysql可用的引擎可以用：show engines查询出来后边有 yes 的是支持的 no 是不支持的。

2.Mysql 的常见引擎：

1.MEMORY：

memory使用存在内存中的内容来创建表，每个MEMORY表实际对应一个磁盘文件，格式是.frm（就是表里的结构）。memory类型的表访问速度非常快，因为它的数据放在内存中并且默认使用 HASH索引，但是一旦服务器关闭表中的数据就会丢失，但是表还会继续存在。

2.MylSAM:

拥有较高的插入（如 insert into 新增），查询速度，但不支持事物,也不支持外键。每个MylSAM在磁盘上存放3个文件，其中文件名和表名相同，但是扩展名分别为 .frm 存储表的一个定义 . MYD：存储真是的数据 .MYI：存储索引 MyISAM适合： (1)做很多count 的计算； (2)插入不频繁，查询非常频繁； (3)没有事务。

3.InnoDB:

5.5版本后Mysql的默认数据库，事物型数据库的首选引擎，支持ACID事物，支持行级锁定 innoDB写的处理效率差，他会占用空间来保存数据和索引。 InnoDB适合： (1)可靠性要求比较高，或者要求事务； (2)表更新和查询都相当的频繁，并且表锁定的机会比较大的情况指定数据引擎的创建让所有的灵活性成为可能的开关是提供给ANSI SQL的MySQL扩展——TYPE参数。 MySQL能够让你在表格这一层指定数据库引擎，所以它们有时候也指的是table formats。下面的示例代码表明了如何创建分别使用MyISAM、ISAM和HEAP引擎的表格。下面的代码显示了如何使用ALTER TABLE把MyISAM表格移动到InnoDB的引擎：代码：ALTER TABLE tblMyISAM CHANGE TYPE=InnoDB 3. Memory:所有数据置于内存的存储引擎，拥有的极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在 Mysql 重新启动时丢失。 4.Merge：将一定数量的 MyISAM 表联合而成一个整体，在超大规模数据存储时很有用。 5.Archive：非常适合存储大量的独立的，作为历史纪录的数据。因为它们不经常被读取。 Archive 拥有高效的插入速度，但其对查询的支持相对较差。 6.CSV：逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个 .CSV文件这是一个普通的文本文件，每个数据行占用一个文本行。CSV存储引擎不支持索引。7.BlackHole:黑洞引擎，写入的任何数据都会消失，一般用于记录 binlog 做复制的中继。

ACID:

A 事务的原子性(Atomicity)：指一个事务要么全部执行,要么不执行.也就是说一个事务不可能只执行了一半就停止了.比如你从取款机取钱,这个事务可以分成两个步骤:1划卡,2出钱.不可能划了卡,而钱却没出来.这两步必须同时完成.要么就不完成. C 事务的一致性(Consistency)：指事务的运行并不改变数据库中数据的一致性.例如,完整性约束了 a+b=10,一个事务改变了a, 那么b也应该随之改变. I 独立性(Isolation）:事务的独立性也有称作隔离性,是指两个以上的事务不会出现交错执行的状态. 因为这样可能会导致数据不一致. D 持久性(Durability）:事务的持久性是指事务执行成功以后,该事务所对数据库所作的更改便是持久的保存在数据库之中，不会无缘无故的回滚.

Innodb介绍：

innoDB是一种可靠性高的高性能 Mysql 数据库存储引擎，它有如下优点：
1.它遵循ACID模式设计，具有与事物（Transactions），回滚和保护用户数据的崩溃恢复能力。 2.提供行锁。 3.InnoDB表基于主键在磁盘上安排数据，有优化的常见查询功能。每一个InnoDB表都有一个叫做聚集索引的主键索引，能尽可能减少数据查询次数。 4.为了维护数据完整性，InnoDB还支持外键完整性约束。 5.可以与其它MySQL存储引擎混合使用InnoDB表。列如，能用一个结合操作在一个单独查询中综合源自 InnoDB和InnoDB表的数据 6.InnoDB是针对提高 CPU效率而设计的，并且在处理大数据时表现最佳。MyISAM和InnoDB的区别；

什么是存储过程：

一组可编程的函数，是为完成特定功能的SQL语句存储过程是具有名字的代码段，来完成一些特定的功能。创建的存储过程保存在数据库的字典中。

为什么用存储过程：

将重复性高的一些操作，封装到一个存储过程中，简化对Sql的调用。批量处理统一接口，确保数据的安全相对与oracle数据来说，Mysql的存储过程功能较弱使用较少

存储过程的创建和调用：

delimiter $ $: 他与存储过程无关 delimiter 语句将标准分割符–分号（；）更改 $ $ , 因为我们想将存储过程作为一个整体传递给服务器，而不是让mysql 工具一次解析每个语句，告诉mysql解析器，该段命令是否已经结束了，mysql是否可以执行了，默认情况下delimiter是分号；。在命令行客户端中，如果有一行以分号结束，那么回车后执行改命令，但是有的时候，不希望mysql这么做。在为可能输入较多的语句，且语句包含有分号，使用delimiterr$ $ 这样只有$ $出现之后mysql解析器解析执行这段语句

案例：

创建存储过程：

delimiter $$  -- 定义好开始  符号 可以 为$$ 或者//
 CREATE PROCEDURE user_tt()  -- 给存储过程取一个名字
BEGIN -- 开始
SELECT * from t_user; --sql语句执行 注意sql 语句结束有分号
END $$  --结束  注意 符号 和定义开始一致

在这里插入图片描述
调用存储过程：

delimiter；-- 把分号改回来

CALL user_tt;

在这里插入图片描述

查看存储过程：
查看所有的存储过程：SHOW PROCEDURE STATUS;
在这里插入图片描述

##查看指定数据库的存储过程：SHOW PROCEDURE STATUS WHERE db=“存储过程名”;

在这里插入图片描述
查看指定存储过程源代码： SHOW CREATE PROCEDURE + 存储过程名

在这里插入图片描述
删除存储过程：drop PROCEDURE +存储过程名；

在这里插入图片描述
存储过程变量：

delimiter $$  -- 定义好开始  符号 可以 为$$ 或者//
 CREATE PROCEDURE text_user()  -- 给存储过程取一个名字
BEGIN -- 开始

  -- 声明变量  关键 字declare   `DEFAULT ''  默认值 空字符串



DECLARE  jie  VARCHAR(255) default ''; 

DECLARE   x,y INT   default 0;


SELECT * from t_user; -- sql语句执行 注意sql 语句结束有分号


END $$  -- 结束  注意 符号 和定义开始一致

在这里插入图片描述
给变量赋值：

变量的范围：如果在存储过程中声明变量，那么当达到存储过程的 END 语句时它超出了范围。因此在其他代码块无法访问

存储过程参数：

三种类型：

                               in  输入类型
                                
                                --  根据传入的名称， 获取对应的信息、
delimiter $$
CREATE PROCEDURE getName(in name VARCHAR(225))-- 传入参数  in 关键字  VARCHAR类型
BEGIN 

 SELECT * from t_user where  user_code =name; -- 给要查的 赋值


END $$

delimiter;

在这里插入图片描述

        out类型的 输出



delimiter $$
CREATE PROCEDURE getMima(in name VARCHAR(225),out mima int )-- 传入参数 out  关键字  输出数据
BEGIN 

 SELECT  user_pass into mima  from t_user where  user_code = name; -- 给要查的 user_pass into mima  赋值  name 


END $$



查询结果
CALL  getMima('鲁班',@m);  -- @m 就时out 的参数  首先执行这句话
SELECT @m; -- 这里的@m 就是上面所定义的 在这可以直接输出 这只是简写

SELECT @m  from  DUAL 这个是mysql 数据库 提供的一张假表  可以加上参数调用

在这里插入图片描述

 inout 类型是又输入又是输出
  delimiter $$
CREATE PROCEDURE test1808(inout num  int ,in abc  int  )-- 传入参数 out  关键字  输出数据
BEGIN 


set num = num + abc;
 
END $$

delimiter;

查询结果
set @num1=220;
CALL test1808(@num1,10);
SELECT @num1;

在这里插入图片描述

存储过程语句：

if语句： IF expression THEN
statements；
END IF;
IF expression THEN
statements;
ELSE
else-statements;
END IF;
CASE语句：
CASE case_expression
WHEN when_expression_2 THEN commands …
ELSE commands
END CASE;
循环语句:
WHILE expression DO
statements
END WHILE
2)REPEAT
stalemeents;
UNTIL expression
END REPEAT

自定义函数

案例：


  定义 一个自定义函数  在定义函数 和存储过程有区别  定义函数function
delimiter $$
CREATE  FUNCTION rand_str(n int) returns  varchar(225)
BEGIN
-- 生明一个str 52字母
DECLARE str VARCHAR(100)  DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; 
-- 记录 当前是第几个
DECLARE i INT DEFAULT 0;
-- 生成的结果
DECLARE  res_str  VARCHAR(255)   DEFAULT '';
-- 循环  
WHILE i < n DO
-- 随机生成一个指定的字符串 b bc bce 
--   1+ RAND()*52  floor（1+rand ()*52）;
--  SUBSTR(str FROM (1+rand()*52),1)
set res_str=CONCAT(res_str,SUBSTR(str,FLOOR(1+RAND()*52),1));
SET i= i+1;
END WHILE;
RETURN res_str;
END $$
delimiter ;


结果
SELECT rand_str(5);

在这里插入图片描述

案例批量新增：

这里调用了以上的随机数 方法 函数

delimiter $$
CREATE  PROCEDURE insert_emp10(in  startNum int, in  max_num int) 
                        --  startNum 从几开始
                        --   max_num 要插入多少条数据
begin 
-- 声明 一个变量叫记录当前是第几条数据
 DECLARE  i INT default 0;
-- 默认情况是自动 提交数据
  set autocommit = 0; -- 不让它自动 提交
repeat 
set i= i+1; -- 每循环一次 让i+1
-- 插入数据
INSERT into  emp VALUES (startNum+i,rand_str(5),FLOOR(10+RAND()*30));
                             --  startNum代表是id ,+i是每次循环+1
                             -- rand_str(5) 调用 数据 数 对name 每一次循环
                               --   都去生成一个5个字母的名字
                               --  FLOOR(10+RAND()*30) 年龄 也是随机的 +10
                               --  是应为随机数是从0开始 *30 是到30岁
until i =max_num  -- 当i=max_num 就让它退出循环

end  repeat;
commit;  -- 整体提交所有数据  提高效率 
end $$

delimiter ;

结果：
在这里插入图片描述