存储过程

最新推荐文章于 2022-09-24 08:41:04 发布

beidaol

最新推荐文章于 2022-09-24 08:41:04 发布

阅读量195

点赞数

本文链接：https://blog.csdn.net/beidaol/article/details/88565903

版权

1、定界符

DELIMITER 是MySQL的一个定界符，是告诉mysql解释器,该段命令是否已经结束了,mysql可以执行了。但是，在复合结构中，sql 语句用“；”结尾，但是我们并没有结束输入命令，所以通过
DELIMITER //
修改默认为 “；”的定界符为”//”, 以后输入 “//”代表命令输入结束，mysql 可以执行了。
DELIMITER ; 改回默认的定界符。

2、参数名不能和数据表字段名相同

这里写图片描述

参数名不能和数据表名相同，上面例子的 p_id不能写成 id。

3、存储过程必须重建，不能修改后重用。

不能更改已经保存的存储过程的函数体，必须先删除该存储过程，再创建新的存储过程。若在原存储过程上修改后重新运行会提示已有重名的存储过程存在，此时可用drop procedure if exists test(存储过程名);来删除，该语句要在第一行。

4、带out参数的存储过程

BEGIN
   DECLARE i int;
   SET i = 0;
   set result = 0;
   WHILE i <= n DO
       set result = result + i;
       set i = i + 1;
   END WHILE;

END]

执行的时候输入参数时，对于out参数，应该以@开头，@后面的名称可以随意

运行结果

@k是在进行查询操作的时候用的

比如先用navicat for mysql的函数/过程来创建存储过程，然后在查询/新建查询中使用CALL 存储过程名（参数）的方式来调用存储过程，执行后使用select @变量名;来查询out参数的值。

5、对于varchar类型要输入类型长度，不然保存时会提示异常

此时添加字符长度便可

在查询中调用存储过程

6、创建带有多个OUT类型的存储过程

根据Id来删除记录，并返回操作的记录数（a），剩余的记录数(b)

BEGIN
   DELETE FROM tb_base_information WHERE id = infoId;
   SELECT ROW_COUNT() INTO a;
   SELECT COUNT(id) FROM tb_base_information INTO b;

END

在查询中调用

注意：最好就不要用那些MySQL的图形化管理工具软件（例如，SQLYog）测试 FOUND_ROWS() 和 ROW_COUNT() 这两个函数。因为当你使用些工具软件执行某条SQL语句时，可能实际上并不仅仅是执行了这条SQL，这些软件同时会在后台自己执行一些其他SQL语句。所以有时你可能会发现这两个函数返回的结果和你预期的并不一样。所以呢，最好还是用 cmd 窗口来执行SQL进行测试。

补充知识：

ROW_COUNT() 函数：函数返回的是上一条SQL语句，对表数据进行修改操作后影响的记录数。

（1）如果上一条SQL语句不是修改操作语句（INSERT/UPDATE/DELETE 等），而是查询语句（SELECT/SHOW 等）则返回-1。如果是修改操作语句，则返回修改（增/删/该）影响的记录数。

注意，这里是上一条SQL语句（即执行该函数前的上一条SQL语句）。

（2）如果上一条SQL语句是UPDATE语句，但是UPDATE后所有数据的值并没有改变，则返回 0。

（3）如果上一条SQL语句是建表语句（创建表或临时表），但创建的是空表，则返回 0。

如果是删除表（DROP语句），则返回的还是 0。

（4）如果是创建临时表，但使用的是 AS 关键字直接将查询出来的值赋值给新建的临时表的话（其实就相当于新建了一个空表，紧接着使用了一条INSERT语句而已），则返回插入的记录数。

CREATE TEMPORARY TABLE tmp_sal AS
SELECT 
	*
FROM 
	employee 
WHERE 
	salary < 12000;

FOUND_ROWS() 函数：返回的是上一条 SELECT 语句（或 SHOW语句等）查询结果集的记录数。

（1） FOUND_ROWS()函数返回的是上一条 SELECT 语句（或 SHOW语句等）查询结果集的记录数。

注意，是上一条 SELECT 语句（即执行该函数前的最近一条SELECT语句），而不是上一条 SQL 语句；因为上一条SQL语句不一定是 SELECT 语句。

且，像 SELECT ROW_COUNT() 这种语句也是 SELECT 语句，它们的结果集也会被 FOUND_ROWS() 函数查出来。

（2）如果上一条 SELECT 语句查询结果为空，则返回 0。

（3）SHOW XXX（例如，show tables、show databases、show status）语句也会被 FOUND_ROWS() 函数查出来。

7、修改存储过程

ALTER PROCEDURE 存储过程名语句代码块；

8、删除存储过程

DROP PROCEDURE IF EXISTS存储过程名

eg:DROP PROCEDURE IF EXISTS proc_employee (proc_employee 存储过程名)

9、查看一个已存在的存储过程

SHOW CREATE PROCEDURE（类似于SHOW CREATE TABLE)
SHOW CREATE PROCEDURE 存储过程名;

10、列出所有的存储过程

SHOW PROCEDURE STATUS;

11、调用存储过程

CALL 存储过程名(参数列表)，如果没有参数，（）也不能省略。

12、存储过程中的变量

DECLARE 局部变量

DECLARE var_name[,...] type [DEFAULT value]

eg:DECLARE num int;

declare age int default 0;

这个语句被用来声明局部变量。要给变量提供一个默认值，请包含一个DEFAULT子句。值可以被指定为一个表达式，不需要为一个常数。
如果没有DEFAULT子句，初始值为NULL。局部变量的作用范围在它被声明的BEGIN ... END块内。它可以被用在嵌套的块中，除了那些用相同名字声明变量的块。

SET语句

SET var_name = expr [, var_name = expr]

-- 局部变量的赋值方式一

set age=18;

-- 局部变量的赋值方式二

select StuAge into age from demo.student where StuNo='A001';
在存储程序中的SET语句是一般SET语句的扩展版本。被参考变量可能是子程序内声明的变量，或者是全局服务器变量。
在存储程序中的SET语句作为预先存在的SET语法的一部分来实现。这允许SET a=x, b=y, ...这样的扩展语法。其中不同的变量类型（局域声明变量及全局和集体变量）可以被混合起来。这也允许把局部变量和一些只对系统变量有意义的选项合并起来。

/*添加菜单*/
DECLARE @pid NVARCHAR(50)/*权限ID*/
DECLARE @pPid NVARCHAR(50)/*权限父ID*/
DECLARE @rid NVARCHAR(50)/*角色表ID*/
SET @pPid = (SELECT ID FROM SysPermission WHERE Code ='ExpOrm')
SET @rid = (SELECT ID FROM SysRole WHERE Name ='开发人员')
 
IF NOT EXISTS (SELECT ID FROM SysPermission WHERE Code ='DemoTestYEXD')
BEGIN
	INSERT INTO SysPermission(ID,Type,ParentID,Code,Name,URL,Image,IsDeleted,IsDisplay,Level)
		VALUES(NEWID(),'4',@pPid,'DemoTestYEXD','我的demo测试','/Orm/DemoTestYEXD','/AppFiles/Images/32/418.png','0','0','3');
	SET @pid = (SELECT ID FROM SysPermission WHERE Code ='DemoTestYEXD');
	INSERT INTO SysRolePermission(ID,RoleID,PermissionID)
		VALUES(NEWID(),@rid,@pid)
END
GO

DECLARE：定义变量，变量第一个字母是“@”

SET：设置变量

IF NOT EXISTS：如果不存在数据，执行BEGIN-END模块中的语句

GO：批解释信号，告诉编辑器代码已结束。

13、局部变量和全局变量

局部变量以@开头，全局变量以@@开头。局部变量不能和全局变量重名。

14、SELECT ... INTO语句

SELECT col_name[,...] INTO var_name[,...] table_expr
这个SELECT语法把选定的列直接存储到变量。
因此，只有单一的行可以被取回。
SELECT id,data INTO x,y FROM test.t1 LIMIT 1;

into写到后面也行

SELECT COUNT(*) FROM tb_base_information INTO num;
注意，SQL变量名不能和列名一样。如果SELECT ... INTO这样的SQL语句包含一个对列的参考，并包含一个与列相同名字的局部变量，MySQL当前把参考解释为一个变量的名字。

15、自定义函数

函数体由合法的SQL语句构成
函数体可以是简单的SELECT或INSERT语句
函数体如果为复合结构则使用 BEGIN….END 语句
复合结构可以包含声明，循环，控制结构

插入一条新数据，user_name，返回该行的id。

BEGIN
	INSERT INTO tb_base_information(id) VALUES(infoId);

	RETURN LAST_INSERT_ID();
END

在查询/新建查询中运行

SELECT fun_insertByID('113d46c5-f532-4eaf-bad0-cddc00e569c9');

beidaol

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
存储过程

1、定界符 DELIMITER 是MySQL的一个定界符，是告诉mysql解释器,该段命令是否已经结束了,mysql可以执行了。但是，在复合结构中，sql 语句用“；”结尾，但是我们并没有结束输入命令，所以通过DELIMITER //修改默认为 “；”的定界符为”//”, 以后输入 “//”代表命令输入结束，mysql 可以执行了。DELIMITER ;改回默认的定界...
复制链接

扫一扫