cursor.md

# 游标

Joe 需要开发一个存储过程 make_trade，从 orders 表

```sql
create table orders (
    id int primary key auto_increment,
    price decimal(12, 4),
    item_id int,
    amount int
)
```

中，按 id 从小到大加载订单数据，生成交易单，写入 trade 表 

```sql
create table trade (
    id int primary key auto_increment,
    total decimal(12, 4)
    -- ...
)
```

其中 trade 的 total字段保存了交易单的总金额，这个金额是每一张交易单的所有 orders 的 price 总和，其数值不会超过 20000。

因为撮合交易的逻辑是串行的，Joe决定用游标实现这部分逻辑。下列哪一个选项是对的(选项中仅包含撮合逻辑，不包括完整的存储过程)？

为简单起见，我们忽略交易的详细逻辑，假设 orders 表仅包含 id, price, item_id, amount 四项。

<hr/>

点击进入[MySQL实战练习环境](https://mydev.csdn.net/product/pod/new?image=cimg-centos7-skilltreemysql&connect=auto&create=auto&utm_source=skill){target="_blank"}。

* `show databases;` 列出所有数据库
* `show tables;` 列出所有表

## 答案

```sql
create procedure make_trade()
begin
    DECLARE done INT DEFAULT FALSE;
    declare cur_orders cursor for select id, price, item_id, amount from orders order by id limit 1000;
    declare order_id, item_id, amount INT;
    declare price decimal(12, 4);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    open cur_orders;
    trade: LOOP
        fetch cur_orders into order_id, price, item_id, amount;
        if done then
            LEAVE trade;
        end if;
    if @total + @price > 2000 then
        leave trade;
    else
        -- 省略交易逻辑        
    end if;
    END LOOP trade;
    CLOSE cur_orders;
    start transaction;
    -- save trade
    delete from orders where id < order_id;
    commit ;

end;
```

## 选项

### A

```sql
create procedure make_trade()
begin
    DECLARE done INT DEFAULT FALSE;
    declare cur_orders cursor for select id, price, item_id, amount from orders order by id limit 1000;
    declare order_id, item_id, amount INT;
    declare price decimal(12, 4);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    open cur_orders;
    trade: LOOP
        fetch cur_orders into order_id, price, item_id, amount;
        if done then
            LEAVE trade;
        end if;
    -- 省略交易逻辑
    if @total + @price > 2000 then
        leave trade;
    else
        NEXT cur_orders;
    end if;
    END LOOP trade;
end;
```

### B

```sql
create procedure make_trade()
begin
    declare cur_orders cursor for select id, price, item_id, amount from orders order by id limit 1000;
    declare order_id, item_id, amount INT;
    declare price decimal(12, 4);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    trade: LOOP
        fetch cur_orders into order_id, price, item_id, amount;
    -- 省略交易逻辑
    END LOOP trade;
    start transaction;
    -- save trade
    delete from orders where id < order_id;
    commit ;
end;
```

### C

```sql
create procedure make_trade()
begin
    DECLARE done INT DEFAULT FALSE;
    declare cur_orders cursor for select id, price, item_id, amount from orders order by id limit 1000;
    declare order_id, item_id, amount INT;
    declare price decimal(12, 4);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    open cur_orders;
    fetch cur_orders into order_id, price, item_id, amount;
    if done then
        LEAVE cur_orders;
    end if;
    -- 省略交易逻辑
    
end;
```

### D

```sql
create procedure make_trade()
begin
    DECLARE done INT DEFAULT FALSE;
    declare cur_orders cursor for select id, price, item_id, amount from orders order by id limit 1000;
    declare order_id, item_id, amount INT;
    declare price decimal(12, 4);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    open cur_orders;
    trade: LOOP
        fetch cur_orders into order_id, price, item_id, amount;
        if done then
            LEAVE trade;
        end if;
    -- 省略交易逻辑
        
    END LOOP trade;
    CLOSE cur_orders;
    start transaction;
    -- save trade
    delete from orders where id < order_id;
    commit ;
end;
```