在Oracle数据库中，游标（Cursor）是一种数据库查询的工具，它允许用户在PL/SQL代码中存储和操作查询结果。游标的主要作用是处理SQL语句返回的多行数据，它通过一个指针来遍历这些数据，从而可以逐条处理查询结果。
隐式游标（Implicit CursorORACLE的游标
1.由 PL/SQL 引擎自动创建、打开、关闭，开发者看不见游标名。细分为：
① 单行 SELECT INTO 游标
② 多行 DML（INSERT/UPDATE/DELETE）游标
单行 SELECT INTO 游标场景：只返回 1 行，无 TOO_MANY_ROWS 风险
DECLAREl_salary employees.salary%TYPE;BEGINSELECT salary INTO l_salaryFROM employeesWHERE employee_id = 100;DBMS_OUTPUT.PUT_LINE('Salary='||l_salary);END;/
多行DML游标场景：INSERT/UPDATE/DELETE 后立即获取受影响行数
BEGINUPDATE employees SET salary = salary * 1.1 WHERE department_id = 30;DBMS_OUTPUT.PUT_LINE('Updated rows='||SQL%ROWCOUNT);END;/
2.显式游标（Explicit Cursor）
显式游标（Explicit Cursor），需要开发者自己 DECLARE、OPEN、FETCH、CLOSE操作。又细分为：
③ 静态游标（Static Explicit Cursor）
④ 参数化游标（Parameterized Cursor）
静态游标场景：结果集 >1 行，需逐行处理
DECLARECURSOR c_dept ISSELECT department_id, department_nameFROM departmentsWHERE location_id = 1700;r_dept c_dept%ROWTYPE;BEGINOPEN c_dept;LOOPFETCH c_dept INTO r_dept;EXIT WHEN c_dept%NOTFOUND;DBMS_OUTPUT.PUT_LINE(r_dept.department_id||':'||r_dept.department_name);END LOOP;CLOSE c_dept;END;/
参数化游标场景：复用同一游标结构，按不同参数多次打开
DECLARECURSOR c_emp(p_dept_id employees.department_id%TYPE) ISSELECT first_name, salaryFROM employeesWHERE department_id = p_dept_id;r_emp c_emp%ROWTYPE;BEGINFOR r IN c_emp(30) LOOPDBMS_OUTPUT.PUT_LINE(r.first_name);END LOOP;
-- 同一游标再开一次OPEN c_emp(100);LOOPFETCH c_emp INTO r_emp;EXIT WHEN c_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE(r_emp.salary);END LOOP;CLOSE c_emp;END;/
3.引用游标（Reference Cursor / Cursor Variable）
引用游标本质上是一个指向游标对象的指针，可在客户端与服务器之间、子程序之间传递，细分为：
⑤ 弱类型引用游标（Weak REF CURSOR）
⑥ 强类型引用游标（Strong REF CURSOR）
弱类型引用游标场景：列结构不固定，常用于返回给 JDBC/ODBC/.NET
DECLARErc SYS_REFCURSOR; -- 不声明列结构l_first employees.first_name%TYPE;l_sal employees.salary%TYPE;BEGINOPEN rc FOR 'SELECT first_name, salary FROM employees WHERE rownum <= 3';LOOPFETCH rc INTO l_first, l_sal;EXIT WHEN rc%NOTFOUND;DBMS_OUTPUT.PUT_LINE(l_first||' earns '||l_sal);END LOOP;CLOSE rc;END;/
强类型引用游标场景：编译期检查列结构，安全且可读
CREATE OR REPLACE PACKAGE emp_pkg ASTYPE emp_curtype IS REF CURSOR RETURN employees%ROWTYPE; -- 强类型PROCEDURE get_emps(p_dept_id IN NUMBER, p_rc OUT emp_curtype);END emp_pkg;/CREATE OR REPLACE PACKAGE BODY emp_pkg ASPROCEDURE get_emps(p_dept_id IN NUMBER, p_rc OUT emp_curtype) ISBEGINOPEN p_rc FORSELECT * FROM employees WHERE department_id = p_dept_id;END;END emp_pkg;/
-- 调用DECLAREl_rc emp_pkg.emp_curtype;l_rec employees%ROWTYPE;BEGINemp_pkg.get_emps(50, l_rc);LOOPFETCH l_rc INTO l_rec;EXIT WHEN l_rc%NOTFOUND;DBMS_OUTPUT.PUT_LINE(l_rec.employee_id||' '||l_rec.last_name);END LOOP;CLOSE l_rc;END;/
4.游标分页处理
DECLAREpage_size CONSTANT PLS_INTEGER := 5;v_offset PLS_INTEGER := 0;TYPE t_emp IS TABLE OF employees%ROWTYPE;a_emp t_emp;BEGINLOOPSELECT *BULK COLLECT INTO a_empFROM employeesORDER BY employee_idOFFSET v_offset ROWS FETCH NEXT page_size ROWS ONLY;EXIT WHEN a_emp.COUNT = 0;DBMS_OUTPUT.PUT_LINE('---- 第 '||(v_offset/page_size+1)||' 页 ----');FOR i IN 1..a_emp.COUNT LOOPDBMS_OUTPUT.PUT_LINE(a_emp(i).employee_id||' '||a_emp(i).last_name);END LOOP;v_offset := v_offset + page_size;END LOOP;END;/
5.总结
仔细的小伙伴有没有发现什么怪怪的地方？没错！上述所有功能均为羲和（Halo）数据库的执行结果！Halo已完整支持Oracle的显式游标、参数化游标、强/弱类型 REF CURSOR、游标属性（%NOTFOUND、%ROWCOUNT）及 FOR/FORALL 等功能，应用无需改写原有 PL/SQL 业务逻辑即可实现平滑迁移。喜欢本文的小伙伴们请点赞吧！
附录 案例表及数据
-- 地区表CREATE TABLE locations (location_id NUMBER(4) CONSTRAINT loc_pk PRIMARY KEY,street_address VARCHAR2(40),postal_code VARCHAR2(12),city VARCHAR2(30) CONSTRAINT loc_city_nn NOT NULL,state_province VARCHAR2(25),country_id CHAR(2));
-- 部门表CREATE TABLE departments (department_id NUMBER(4) CONSTRAINT dept_pk PRIMARY KEY,department_name VARCHAR2(30) CONSTRAINT dept_name_nn NOT NULL,manager_id NUMBER(6),location_id NUMBER(4),CONSTRAINT dept_loc_fk FOREIGN KEY (location_id) REFERENCES locations(location_id));
-- 员工表CREATE TABLE employees (employee_id NUMBER(6) CONSTRAINT emp_pk PRIMARY KEY,first_name VARCHAR2(20),last_name VARCHAR2(25) CONSTRAINT emp_lname_nn NOT NULL,email VARCHAR2(25) CONSTRAINT emp_email_nn NOT NULL,phone_number VARCHAR2(20),hire_date DATE CONSTRAINT emp_hire_dt_nn NOT NULL,job_id VARCHAR2(10) CONSTRAINT emp_job_nn NOT NULL,salary NUMBER(8,2),commission_pct NUMBER(2,2),manager_id NUMBER(6),department_id NUMBER(4),CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id),CONSTRAINT emp_mgr_fk FOREIGN KEY (manager_id) REFERENCES employees(employee_id));
-- 序列：员工编号 100 起跳CREATE SEQUENCE employees_seq START WITH 100 INCREMENT BY 1;
-- departments 序列（演示用）CREATE SEQUENCE departments_seq START WITH 10 INCREMENT BY 10;
-- 地区INSERT INTO locations VALUES (1700,'2004 Charade Rd','98199','Seattle' ,'Washington' ,'US');INSERT INTO locations VALUES (1400,'2014 Jabberwocky Rd','26192','Southlake' ,'Texas' ,'US');INSERT INTO locations VALUES (1500,'2011 Interiors Blvd','99236','South San Francisco','California' ,'US');INSERT INTO locations VALUES (1800,'460 Bloor St. W.','M5S 1Y8','Toronto' ,'Ontario' ,'CA');INSERT INTO locations VALUES (2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK');
-- 部门INSERT INTO departments VALUES (90,'Executive' ,NULL,1700); -- 稍后把 manager_id 改成 100INSERT INTO departments VALUES (10,'Administration',200,1700);INSERT INTO departments VALUES (30,'Purchasing' ,114,1700);INSERT INTO departments VALUES (100,'Finance' ,108,1700);INSERT INTO departments VALUES (50,'Shipping' ,121,1500);INSERT INTO departments VALUES (60,'IT' ,103,1400);INSERT INTO departments VALUES (80,'Sales' ,145,2500);INSERT INTO departments VALUES (110,'Accounting' ,205,1700);
-- 员工（100~206，覆盖前面游标示例所需）INSERT INTO employees VALUES (100,'Steven' ,'King' ,'SKING' ,'515.123.4567',DATE'1987-06-17','AD_PRES',24000,NULL,NULL,90);INSERT INTO employees VALUES (101,'Neena' ,'Kochhar' ,'NKOCHHAR','515.123.4568',DATE'1989-09-21','AD_VP',17000,NULL,100,90);INSERT INTO employees VALUES (102,'Lex' ,'De Haan','LDEHAAN','515.123.4569',DATE'1993-01-13','AD_VP',17000,NULL,100,90);INSERT INTO employees VALUES (103,'Alexander','Hunold' ,'AHUNOLD','590.423.4567',DATE'1990-01-03','IT_PROG',9000,NULL,102,60);INSERT INTO employees VALUES (104,'Bruce' ,'Ernst' ,'BERNST' ,'590.423.4568',DATE'1991-05-21','IT_PROG',6000,NULL,103,60);INSERT INTO employees VALUES (105,'David' ,'Austin' ,'DAUSTIN','590.423.4569',DATE'1997-06-25','IT_PROG',4800,NULL,103,60);INSERT INTO employees VALUES (106,'Valli' ,'Pataballa','VPATABAL','590.423.4560',DATE'1998-02-05','IT_PROG',4800,NULL,103,60);INSERT INTO employees VALUES (107,'Diana' ,'Lorentz','DLORENTZ','590.423.5567',DATE'1999-02-07','IT_PROG',4200,NULL,103,60);INSERT INTO employees VALUES (108,'Nancy' ,'Greenberg','NGREENBE','515.124.4569',DATE'1994-08-17','FI_MGR',12000,NULL,101,100);INSERT INTO employees VALUES (109,'Daniel' ,'Faviet','DFAVIET','515.124.4169',DATE'1994-08-16','FI_ACCOUNT',9000,NULL,108,100);INSERT INTO employees VALUES (114,'Den' ,'Raphaely','DRAPHEAL','515.127.4561',DATE'1994-12-07','PU_MAN',11000,NULL,100,30);INSERT INTO employees VALUES (115,'Alexander','Khoo' ,'AKHOO' ,'515.127.4562',DATE'1995-05-18','PU_CLERK',3100,NULL,114,30);INSERT INTO employees VALUES (116,'Shelli' ,'Baida' ,'SBAIDA' ,'515.127.4563',DATE'1997-12-24','PU_CLERK',2900,NULL,114,30);INSERT INTO employees VALUES (117,'Sigal' ,'Tobias' ,'STOBIAS','515.127.4564',DATE'1997-07-24','PU_CLERK',2800,NULL,114,30);INSERT INTO employees VALUES (118,'Guy' ,'Himuro' ,'GHIMURO','515.127.4565',DATE'1998-11-15','PU_CLERK',2600,NULL,114,30);INSERT INTO employees VALUES (121,'Adam' ,'Fripp' ,'AFRIPP' ,'650.123.2234',DATE'1997-04-10','ST_MAN',8200,NULL,100,50);INSERT INTO employees VALUES (145,'John' ,'Russell','JRUSSEL','011.44.1344.429268',DATE'1996-10-01','SA_MAN',14000,0.4,100,80);INSERT INTO employees VALUES (146,'Karen' ,'Partners','KPARTNER','011.44.1344.467268',DATE'1997-01-05','SA_MAN',13500,0.3,100,80);INSERT INTO employees VALUES (147,'Alberto' ,'Errazuriz','AERRAZUR','011.44.1344.429278',DATE'1997-03-10','SA_MAN',12000,0.3,100,80);INSERT INTO employees VALUES (200,'Jennifer' ,'Whalen' ,'JWHALEN','515.123.4444',DATE'1987-09-17','AD_ASST',4400,NULL,101,10);INSERT INTO employees VALUES (205,'Shelley' ,'Higgins','SHIGGINS','515.123.8080',DATE'1994-06-07','AC_MGR',12000,NULL,101,110);INSERT INTO employees VALUES (206,'William' ,'Gietz' ,'WGIETZ' ,'515.123.8181',DATE'1994-06-07','AC_ACCOUNT',8300,NULL,205,110);
-- 把 departments.manager_id 更新为真实员工号（可选，保持数据一致）UPDATE departments SET manager_id = 100 WHERE department_id = 90;UPDATE departments SET manager_id = 200 WHERE department_id = 10;UPDATE departments SET manager_id = 114 WHERE department_id = 30;UPDATE departments SET manager_id = 121 WHERE department_id = 50;UPDATE departments SET manager_id = 103 WHERE department_id = 60;UPDATE departments SET manager_id = 108 WHERE department_id = 100;UPDATE departments SET manager_id = 145 WHERE department_id = 80;UPDATE departments SET manager_id = 205 WHERE department_id = 110;COMMIT;
-- 验证SELECT 'locations' tbl, COUNT(*) cnt FROM locations UNION ALLSELECT 'departments', COUNT(*) FROM departments UNION ALLSELECT 'employees' , COUNT(*) FROM employees;
-- 清理环境DROP TABLE IF EXISTS employees;DROP TABLE IF EXISTS departments;DROP TABLE IF EXISTS locations;drop SEQUENCE employees_seq;drop SEQUENCE departments_seq;