mysql ocp题库解析-014

Examine this statement which executes successfully:

DELIMITER //
CREATE PROCEDURE test_cursor(OUT city_name VARCHAR(100))
BEGIN
DECLARE count INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT name FROM city;
DECLARE EXIT HANDLER FOR NOT FOUND SELECT count;
OPEN cur;
LOOP
FETCH cur INTO city_name;
SET count = count+1;
END LOOP;
END //
DELIMITER ;

You execute this statement:

CALL test_cursor(@city);

Which two are true about the execution?

A) The procedure displays the number of rows in the city table.
B) The memory allocated to the cursor is not released.
C) The loop runs infinitely.
D) @city contains the last city name fetched from the cursor.
E) The procedure exits if the city table is not found.

题目内容：

Examine this statement which executes successfully:

DELIMITER //
CREATE PROCEDURE test_cursor(OUT city_name VARCHAR(100))
BEGIN
    DECLARE count INT DEFAULT 0;
    DECLARE cur CURSOR FOR SELECT name FROM city;
    DECLARE EXIT HANDLER FOR NOT FOUND SELECT count;
    OPEN cur;
    LOOP
        FETCH cur INTO city_name;
        SET count = count+1;
    END LOOP;
END //
DELIMITER ;

You execute this statement:

CALL test_cursor(@city);

Which two are true about the execution?

A) The procedure displays the number of rows in the city table.
B) The memory allocated to the cursor is not released.
C) The loop runs infinitely.
D) @city contains the last city name fetched from the cursor.
E) The procedure exits if the city table is not found.

深度解析：

存储过程执行流程：

sequenceDiagram
    participant P as 存储过程
    participant C as 游标
    Note over P: 初始化 count=0
    P->>C: OPEN cur (打开游标)
    loop LOOP
        P->>C: FETCH city_name
        alt 有数据
            C-->>P: 返回城市名
            P->>P: count = count+1
        else 无数据 (NOT FOUND)
            C-->>P: 触发NOT FOUND
            P->>P: 执行EXIT HANDLER
            P->>P: SELECT count (显示行数)
            P->>P: 立即退出
        end
    end

正确答案分析：

A) 显示 city 表的行数
✅ 正确

• EXIT HANDLER FOR NOT FOUND 在游标无数据时激活
• SELECT count 显示累计的 count 值
• count 等于 FETCH 成功次数 = 行数

D) @city 包含最后获取的城市名
✅ 正确

• 每次 FETCH 更新 city_name OUT 参数
• 最后一次成功 FETCH 的值保留在 @city 中
• 示例：如果最后一行是 “Paris”，则 @city = 'Paris'

错误选项分析：

关键机制详解：

1. EXIT HANDLER 行为：

   • 当 FETCH 无数据时触发
   • 立即执行 SELECT count
   • 终止存储过程执行

2. OUT 参数特性：

   • 最后成功赋值的值保留
   • 不受后续错误影响

3. 游标生命周期：

   graph LR
       A[OPEN cur] --> B[FETCH]
       B -->|成功| C[处理数据]
       B -->|失败| D[触发HANDLER]
       D --> E[自动关闭游标]
   

执行结果验证：

假设 city 表有 3 行数据：

1. “New York”
2. “London”
3. “Paris”

执行 CALL test_cursor(@city) 后：

SELECT @city; -- 输出 'Paris'

同时客户端显示：

+-------+
| count |
+-------+
|     3 |
+-------+

生产应用警示：

1. 潜在问题：

   • 缺少 CLOSE cur 语句（但自动处理）
   • 循环后无操作（直接退出）

2. 优化建议：

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
   OPEN cur;
   read_loop: LOOP
       FETCH cur INTO city_name;
       IF done THEN LEAVE read_loop; END IF;
       SET count = count+1;
   END LOOP;
   CLOSE cur; -- 显式关闭
   

正确答案：

A) The procedure displays the number of rows in the city table.
D) @city contains the last city name fetched from the cursor.

💡 专家提示：
使用游标时始终添加显式结束条件：

DECLARE done INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur;
FETCH cur INTO ...;
WHILE done = 0 DO
  -- 处理数据
  FETCH cur INTO ...;
END WHILE;
CLOSE cur;