（课堂笔记）游标与动态SQL：使用 CHR(39) 替代拼接单引号

本文系统介绍了Oracle数据库中的游标与动态SQL技术。

---

主要内容包括：

1. 游标概念与分类（隐式/显式游标）及其四大属性（ISOPEN/FOUND/NOTFOUND/ROWCOUNT）；
2. 显式游标的使用方法，包括定义、遍历和参数传递；
3. 动态SQL的EXECUTE IMMEDIATE语法，重点说明SELECT...INTO在动态SQL中的特殊写法；
4. 批量数据处理技巧和注意事项。

---

文章通过丰富示例演示了游标遍历、批量提交、动态表操作等典型场景，并对比了静态SQL与动态SQL的差异，为数据库开发提供了实用指导。

（课堂笔记）游标与动态SQL

🧩 一、游标（CURSOR）

1. 什么是游标？

• 游标是指向数据库表中每一行数据的指针。

• 用于逐行处理查询结果集。

2. 游标分类

3. 游标的四大属性（非常重要）

使用方式：

• 隐式游标：SQL%属性名

• 显式游标：游标名%属性名

🧪 二、隐式游标示例

sql

BEGIN UPDATE EMP SET SAL = SAL + 100 WHERE DEPTNO = 10; IF SQL%FOUND THEN DBMS_OUTPUT.put_line('有数据被更新！'); DBMS_OUTPUT.PUT_LINE('总共有：' || SQL%ROWCOUNT || ' 行数据被更新。'); END IF; COMMIT; END;

🛠 三、显式游标

创建语法（放在BEGIN之前）

sql

CURSOR 游标名(参数名 参数类型) IS SELECT 语句;

示例1：遍历 20 号部门员工

sql

CREATE OR REPLACE PROCEDURE P_005 IS CURSOR CUR_001 IS SELECT * FROM EMP WHERE DEPTNO = 20; BEGIN FOR I IN CUR_001 LOOP DBMS_OUTPUT.PUT_LINE(I.ENAME || '--' || I.SAL); END LOOP; END;

示例2：根据传入员工编号，打印其所在部门所有员工

sql

CREATE OR REPLACE PROCEDURE P_007(P_NUM NUMBER) IS VVVV NUMBER; CURSOR CUR_002(P_DEPTNO NUMBER) IS SELECT ENAME FROM EMP WHERE DEPTNO = P_DEPTNO; BEGIN SELECT DEPTNO INTO VVVV FROM EMP WHERE EMPNO = P_NUM; FOR I IN CUR_002(VVVV) LOOP DBMS_OUTPUT.PUT_LINE(I.ENAME); END LOOP; END;

示例3：返回员工所在部门的所有员工姓名（拼接）

sql

CREATE OR REPLACE FUNCTION KKK(P_EMPNO NUMBER) RETURN VARCHAR2 IS V_DEPTNO NUMBER; CURSOR CUR_001(P_DEPT NUMBER) IS SELECT ENAME FROM EMP WHERE DEPTNO = P_DEPT; V_RES VARCHAR2(1000) := ''; BEGIN SELECT DEPTNO INTO V_DEPTNO FROM EMP WHERE EMPNO = P_EMPNO; FOR I IN CUR_001(V_DEPTNO) LOOP V_RES := V_RES || I.ENAME || ','; END LOOP; V_RES := SUBSTR(V_RES, 1, LENGTH(V_RES) - 1); RETURN V_RES; END;

⚠️ 四、变量 vs 参数

sql

CREATE OR REPLACE PROCEDURE PPP(P_NUM NUMBER) IS BEGIN -- P_NUM := 2222; ❌ 错误，参数不能赋值 DBMS_OUTPUT.PUT_LINE(P_NUM); END;

📦 五、综合练习（游标 + 批量插入）

创建空表并插入数据

sql

-- 创建空表 CREATE TABLE EMP_0508 AS SELECT * FROM EMP WHERE 1=2; -- 存储过程：插入指定部门数据 CREATE OR REPLACE PROCEDURE P_000(P_DEPT NUMBER) IS CURSOR CUR_001(T_DEPTNO NUMBER) IS SELECT * FROM EMP WHERE DEPTNO = T_DEPTNO; BEGIN FOR I IN CUR_001(P_DEPT) LOOP INSERT INTO EMP_0508 VALUES (I.EMPNO, I.ENAME, I.JOB, I.MGR, I.HIREDATE, I.SAL, I.COMM, I.DEPTNO); END LOOP; COMMIT; END;

🔁 六、批量提交（避免 UNDO 表空间不足）

sql

CREATE OR REPLACE PROCEDURE P_012 IS CURSOR CUR_012 IS SELECT * FROM EMP; BEGIN FOR I IN CUR_012 LOOP INSERT INTO EMP_0508 VALUES (...); IF MOD(CUR_012%ROWCOUNT, 100000) = 0 THEN COMMIT; END IF; END LOOP; COMMIT; END;

✅每 10 万条提交一次，适合大数据量操作。

⚡ 七、动态 SQL

使用场景：

• SQL 语句结构不固定（如动态表名、条件）

• 执行 DDL 语句（CREATE / DROP / TRUNCATE）

核心语法：

sql

EXECUTE IMMEDIATE 'SQL 语句字符串';

示例1：批量创建 100 张表

sql

CREATE OR REPLACE PROCEDURE P_CRE_TABLE IS V_STR VARCHAR2(100); BEGIN FOR I IN 1..100 LOOP V_STR := 'CREATE TABLE EMP__' || LPAD(I, 3, 0) || ' AS SELECT * FROM EMP WHERE 1=2'; EXECUTE IMMEDIATE V_STR; END LOOP; END;

示例2：批量删除表

sql

CREATE OR REPLACE PROCEDURE P_013 IS V_SQL VARCHAR2(1000); BEGIN FOR I IN 1..100 LOOP V_SQL := 'DROP TABLE EMP__' || LPAD(I, 3, 0); EXECUTE IMMEDIATE V_SQL; END LOOP; END;

示例3：TRUNCATE 表

sql

CREATE OR REPLACE PROCEDURE P_014 IS BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE EMP_0508'; INSERT INTO EMP_0508 SELECT * FROM EMP; COMMIT; END;

示例4：动态 SELECT INTO

sql

EXECUTE IMMEDIATE 'SELECT ENAME FROM EMP WHERE EMPNO = 7369' INTO V_ENAME;

🧠 常见考点 & 面试题

Q：游标的使用场景？

• 批量提交

• 批量更新

• 逐行处理数据

Q：隐式游标和显式游标的区别？

Q：四大属性是什么？

• %ISOPEN

• %FOUND

• %NOTFOUND

• %ROWCOUNT

✅ 复习建议

1. 掌握隐式和显式游标的区别及使用方式

2. 熟练写出显式游标的定义 + FOR 循环遍历

3. 理解四大属性的含义和输出时机

4. 掌握动态 SQL 的写法，尤其是字符串拼接中的引号处理

5. 会写批量提交的逻辑

如果 动态执行的 是 SELECT .. INTO 语句 则需要改写成 EXECUTE IMMEDIATE SQL语句字符串 INTO 变量1,变量2... 是什么意思

详细解释：动态 SQL 中的SELECT ... INTO

你的笔记中这句话的意思是：

当你用动态SQL执行一个查询语句（SELECT）并且想把查询结果存储到变量中时，需要使用EXECUTE IMMEDIATE ... INTO ...语法

📌 为什么要这样写？

普通情况（静态SQL）

sql

DECLARE V_ENAME VARCHAR2(100); BEGIN -- 直接使用 SELECT INTO，结果存入变量 SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO = 7369; DBMS_OUTPUT.PUT_LINE(V_ENAME); -- 输出：SMITH END;

动态SQL情况

sql

DECLARE V_ENAME VARCHAR2(100); V_SQL VARCHAR2(1000); BEGIN -- SQL 语句被写成了字符串 V_SQL := 'SELECT ENAME FROM EMP WHERE EMPNO = 7369'; -- 必须用 EXECUTE IMMEDIATE ... INTO EXECUTE IMMEDIATE V_SQL INTO V_ENAME; DBMS_OUTPUT.PUT_LINE(V_ENAME); -- 输出：SMITH END;

🎯 核心语法

sql

EXECUTE IMMEDIATE 'SELECT 语句字符串' INTO 变量1, 变量2, ...;

示例1：查询单个值

sql

DECLARE V_ENAME VARCHAR2(100); V_SQL VARCHAR2(1000); BEGIN V_SQL := 'SELECT ENAME FROM EMP WHERE EMPNO = 7499'; EXECUTE IMMEDIATE V_SQL INTO V_ENAME; DBMS_OUTPUT.PUT_LINE(V_ENAME); -- 输出：ALLEN END;

示例2：查询多个值

sql

DECLARE V_ENAME VARCHAR2(100); V_SAL NUMBER; V_SQL VARCHAR2(1000); BEGIN V_SQL := 'SELECT ENAME, SAL FROM EMP WHERE EMPNO = 7499'; EXECUTE IMMEDIATE V_SQL INTO V_ENAME, V_SAL; DBMS_OUTPUT.PUT_LINE(V_ENAME || '的工资是：' || V_SAL); END;

💡 你笔记中的实际案例解析

sql

CREATE OR REPLACE PROCEDURE P_021(X VARCHAR2) IS V_CNT NUMBER; V_SQL VARCHAR2(1000); V_ENAME VARCHAR2(100); BEGIN -- 先统计有多少符合条件的员工 SELECT COUNT(1) INTO V_CNT FROM EMP WHERE INSTR(ENAME, X) > 0; IF V_CNT = 0 THEN DBMS_OUTPUT.PUT_LINE('没有符合条件的员工！'); ELSE -- 循环取出每个员工 FOR I IN 1..V_CNT LOOP -- 动态构造分页查询的SQL字符串 V_SQL := 'SELECT ENAME FROM (SELECT ENAME, ROW_NUMBER() OVER (ORDER BY 1) AS RN FROM EMP WHERE ENAME LIKE ' || CHR(39) || '%' || X || '%' || CHR(39) || ') WHERE RN = ' || I; -- ⭐ 关键：将动态查询的结果存入变量 V_ENAME EXECUTE IMMEDIATE V_SQL INTO V_ENAME; DBMS_OUTPUT.PUT_LINE(V_ENAME); END LOOP; END IF; END;

⚠️ 注意事项

1. 查询结果必须只有一行

• EXECUTE IMMEDIATE ... INTO要求查询结果只能返回一行

• 如果返回多行会报错ORA-01422: exact fetch returns more than requested number of rows

2. 变量数量必须匹配

sql

-- ❌ 错误：2个变量但只查询1列 EXECUTE IMMEDIATE 'SELECT ENAME FROM EMP WHERE EMPNO=7369' INTO V_ENAME, V_SAL; -- ✅ 正确：查询2列对应2个变量 EXECUTE IMMEDIATE 'SELECT ENAME, SAL FROM EMP WHERE EMPNO=7369' INTO V_ENAME, V_SAL;

3. 字符串中的引号处理

sql

-- 两种写法等价 -- 方法1：用两个单引号表示一个单引号 V_SQL := 'SELECT ENAME FROM EMP WHERE ENAME LIKE ''%S%'''; -- 方法2：用 CHR(39) 表示单引号（更清晰） V_SQL := 'SELECT ENAME FROM EMP WHERE ENAME LIKE ' || CHR(39) || '%S%' || CHR(39);

📊 对比总结

✅ 一句话记忆

动态SQL是字符串，要存结果加INTO，变量数量要匹配，查询必须单一行。