mysql 游標,MySQL - Cursors | mysql Tutorial

深碍√TFBOYSˉ_ 2023-01-14 04:50 159阅读 0赞

Example

Cursors enable you to itterate results of query one by line. DECLARE

command is used to init cursor and associate it with a specific SQL query:

DECLARE student CURSOR FOR SELECT name FROM studend;

Let's say we sell products of some types. We want to count how many products of each type are exists.

Our data:

CREATE TABLE product

(

id INT(10) UNSIGNED NOT NULL AUTO\_INCREMENT PRIMARY KEY,

type VARCHAR(50) NOT NULL,

name VARCHAR(255) NOT NULL

);

CREATE TABLE product\_type

(

name VARCHAR(50) NOT NULL PRIMARY KEY

);

CREATE TABLE product\_type\_count

(

type VARCHAR(50) NOT NULL PRIMARY KEY,

count INT(10) UNSIGNED NOT NULL DEFAULT 0

);

INSERT INTO product\_type (name) VALUES

('dress'),

('food');

INSERT INTO product (type, name) VALUES

('dress', 'T-shirt'),

('dress', 'Trousers'),

('food', 'Apple'),

('food', 'Tomatoes'),

('food', 'Meat');

We may achieve the goal using stored procedure with using cursor:

DELIMITER //

DROP PROCEDURE IF EXISTS product\_count;

CREATE PROCEDURE product\_count()

BEGIN

DECLARE p\_type VARCHAR(255);

DECLARE p\_count INT(10) UNSIGNED;

DECLARE done INT DEFAULT 0;

DECLARE product CURSOR FOR

SELECT

type,

COUNT(\*)

FROM product

GROUP BY type;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

TRUNCATE product\_type;

OPEN product;

REPEAT

FETCH product

INTO p\_type, p\_count;

IF NOT done

THEN

INSERT INTO product\_type\_count

SET

type = p\_type,

count = p\_count;

END IF;

UNTIL done

END REPEAT;

CLOSE product;

END //

DELIMITER ;

When you may call procedure with:

CALL product\_count();

Result would be in product\_type\_count table:

type | count

\----------------

dress | 2

food | 3

While that is a good example of a CURSOR, notice how the entire body of the procedure can be replaced by just

INSERT INTO product\_type\_count

(type, count)

SELECT type, COUNT(\*)

FROM product

GROUP BY type;

This will run a lot faster.

mysql 游標,MySQL - Cursors | mysql Tutorial

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关【MySQL 】MySQL 创建数据库， MySQL 删除数据库，MySQL 选择数据库

相关 MYSQL、MySQL、MySql、mysql 批量更新SQL写法实践

相关 mysql 游標,MySQL - Cursors | mysql Tutorial

相关 php mysql cursor_Mysql创建存储过程，使用游标Cursor循环更新_MySQL

相关【MySQL】使用MySQL

相关【MySQL】MySQL简介

相关 MySQL存储过程游标（cursor）示例

相关 MySQL CURSOR游标

相关 MySQL-存储过程-游标 CURSOR FOR

相关 Mysql存储过程中使用cursor