利用navicat创建存储过程、触发器和使用游标的简单实例

ゝ一纸荒年。 2021-11-22 05:40 574阅读 0赞

创建存储过程和触发器

# 1、建表 #

首先先建两张表（users表和number表），具体设计如下图：

![SouthEast][]

![SouthEast 1][]

# 2、存储过程 #

写一个存储过程，往users表中插入数据，创建过程如下：

![SouthEast 2][]

![SouthEast 3][]

![SouthEast 4][]

代码如下：

BEGIN
    	#Routine body goes here...
    	declare n bigint;
    	set n = 201121029684;
    	while n <= 201121029694
    	do
    		insert into users(student_ID) values(n);
    		set n = n + 1;
    	end while;
    END

![SouthEast 5][]

执行存储过程后可以看到users表中的数据如下：

![SouthEast 6][]

整个存储过程的编写就完成了，当然这只是一个极为简单的例子，仅供入门参考。

# 3、触发器 #

在写触发器之前，我们先把users的数据清空

truncate table users;

现在我们有两个表，我要做的事情就是，当我往users中插入数据后，number中也相应变化：

例如，number表中有一条初始数据，id=1，num=0；

当我往users中插入一条数据后，就触发number表中的num字段就加1，也就是记录用户数。

下面我们来实现这个小小的功能。

右击users表，选择设计表

![SouthEast 7][]

选择触发器选项

![SouthEast 8][]

![SouthEast 9][]

![SouthEast 10][]

代码：

begin
        update number
        set num = (select count(*) from users) ;
    end

保存后，再往users表中添加新数据，再查看一下number中的数据，你会神奇的发现，number表中的数据也变了，自己动手试一下吧！

ps：存储过程需要程序员自己去执行，触发器，顾名思义，自动触发。

# 4、游标使用 #

首先我将users表中的数据清空（当然不清空也可以），然后再往里面填充数据，如下图所示：

![SouthEast 11][]

我现在想做的是将student\_ID字段都加上100，通过这个例子简单展示一下游标的使用。

创建一个存储过程，创建方式参考上面步骤。存储过程代码如下：

BEGIN
    	#Routine body goes here...
    
    	declare tmp bigint default 0;
    	declare cur CURSOR FOR SELECT student_ID FROM users;  -- 定义游标
    	/*
    	02000 发生下述异常之一： 
    	SELECT INTO 语句或 INSERT 语句的子查询的结果为空表。 
    	在搜索的 UPDATE 或 DELETE 语句内标识的行数为零。 
    	在 FETCH 语句中引用的游标位置处于结果表最后一行之后。
    
    
    	*/
    	declare CONTINUE HANDLER FOR SQLSTATE '02000' set tmp = 0; 
    
    
    	OPEN cur;  -- 打开游标
    	FETCH cur INTO tmp;  -- 游标向下走一步
    
    
    	WHILE(tmp != 0)
    	DO	
    		select tmp;  -- 将tmp打印出来，会发现tmp就像一个指针，一开始指向第一行，游标走一步，则指向下一行记录
    
    
    		UPDATE users
    		SET student_ID = tmp + 100
    		WHERE student_ID = tmp;
    		FETCH cur INTO tmp;
    	END WHILE;
    
    	CLOSE cur;  -- 关闭游标
    END

执行上面的存储过程，你会发现，users中的数据如你所愿的发生了变化。

![SouthEast 12][]

当然，这个功能直接用循环就可以解决，我这里只是简单展示一下游标的用法，利于对游标有个感性认识。

that‘s all~

[SouthEast]: /images/20211122/9036fa5e35ca4c7192ed7a41eab3141d.png
[SouthEast 1]: /images/20211122/c5c76eb8fb5f438da78cc544a19db07d.png
[SouthEast 2]: /images/20211122/11fd15671f984ff7963b58c0af5802bb.png
[SouthEast 3]: /images/20211122/30c5b7551a0f4d1fa1656098410abad7.png
[SouthEast 4]: /images/20211122/c39f30cfcdb24bfcb8a76ae74f1fcb52.png
[SouthEast 5]: /images/20211122/3cfa364e753f41c0b9e64a39b1e642f8.png
[SouthEast 6]: /images/20211122/835a22360c85479589b7a5c158ca873b.png
[SouthEast 7]: /images/20211122/4afa9e78fcda43a7a5b2070ea3510c88.png
[SouthEast 8]: /images/20211122/f69a75008b12458b9092c32c8113ceb2.png
[SouthEast 9]: /images/20211122/17a97cd00c854304983a51c27b6f95bc.png
[SouthEast 10]: /images/20211122/c96c53b01233411e8d55f0870967b524.png
[SouthEast 11]: /images/20211122/3f4520b7af584f31b822e8ff97f096fb.png
[SouthEast 12]: /images/20211122/05924ab4ac704125a3ef530f2343c456.png