在Oracle数据库中，游标（Cursor）是一个数据库查询的结果集的引用。它允许你逐行地处理查询结果，而不是一次性地处理整个结果集。游标分为隐式游标和显式游标两种。
显式游标（Explicit Cursor）
显式游标是你在PL/SQL程序中明确声明和操作的游标。当你需要对查询结果进行更复杂的处理时，比如逐行处理查询结果或者需要获取查询的某些状态信息（如是否找到行），就需要使用显式游标。
显式游标的声明
显式游标在PL/SQL中通过CURSOR关键字进行声明，并通常指定一个游标名称和一个查询语句。例如：
DECLARECURSOR employee_cur ISSELECT employee_id, last_name FROM employees;
BEGIN-- 游标的使用将在这里进行
END;
显式游标的打开、获取和关闭
-
打开游标：使用
OPEN语句打开游标，并执行其定义的查询。 -
获取游标数据：通常使用
FETCH INTO语句从游标中获取数据行，并将其放入PL/SQL变量中。这可以在循环中完成，以逐行处理查询结果。 -
关闭游标：使用
CLOSE语句关闭游标。关闭游标后，你将不能再从它那里获取数据。
示例1 LOOP … EXIT WHEN … END LOOP
下面是一个使用显式游标逐行处理employees表中数据的例子：
DECLARECURSOR employee_cur ISSELECT employee_id, last_name FROM employees;v_employee_id employees.employee_id%TYPE;v_last_name employees.last_name%TYPE;
BEGINOPEN employee_cur;LOOPFETCH employee_cur INTO v_employee_id, v_last_name;EXIT WHEN employee_cur%NOTFOUND;DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || ', Last Name: ' || v_last_name);END LOOP;CLOSE employee_cur;
END;
在这个例子中，employee_cur是一个显式游标，它遍历employees表中的所有行。对于表中的每一行，我们都将employee_id和last_name列的值提取到变量中，并使用DBMS_OUTPUT.PUT_LINE输出这些值。当游标没有更多行可以提取时（即employee_cur%NOTFOUND为TRUE），循环结束，并且游标被关闭。
示例2 Implicit Cursor FOR LOOP Statement
In this example, an implicit cursor FOR LOOP statement prints the last name and job ID of every clerk whose manager has an ID greater than 120.
BEGINFOR item IN (SELECT last_name, job_idFROM employeesWHERE job_id LIKE '%CLERK%'AND manager_id > 120ORDER BY last_name)LOOPDBMS_OUTPUT.PUT_LINE('Name = ' || item.last_name || ', Job = ' || item.job_id);END LOOP;
END;
/-- OR DECLARECURSOR c1 ISSELECT last_name, job_id FROM employeesWHERE job_id LIKE '%CLERK%' AND manager_id > 120ORDER BY last_name;
BEGINFOR item IN c1LOOPDBMS_OUTPUT.PUT_LINE('Name = ' || item.last_name || ', Job = ' || item.job_id);END LOOP;
END;
/Result:
Name = Atkinson, Job = ST_CLERK
Name = Bell, Job = SH_CLERK
Name = Bissot, Job = ST_CLERK
...
Name = Walsh, Job = SH_CLERK示例3 Passing Parameters to Explicit Cursor FOR LOOP Statement
This example declares and defines an explicit cursor that accepts two parameters, and then uses it in an explicit cursor FOR LOOP statement to display the wages paid to employees who earn more than a specified wage in a specified department.
DECLARECURSOR c1 (job VARCHAR2, max_wage NUMBER) ISSELECT * FROM employeesWHERE job_id = jobAND salary > max_wage;
BEGINFOR person IN c1('ST_CLERK', 3000)LOOP-- process data recordDBMS_OUTPUT.PUT_LINE ('Name = ' || person.last_name || ', salary = ' ||person.salary || ', Job Id = ' || person.job_id);END LOOP;
END;
/Result:
Name = Nayer, salary = 3200, Job Id = ST_CLERK
Name = Bissot, salary = 3300, Job Id = ST_CLERK
Name = Mallin, salary = 3300, Job Id = ST_CLERK
Name = Ladwig, salary = 3600, Job Id = ST_CLERK
Name = Stiles, salary = 3200, Job Id = ST_CLERK
Name = Rajs, salary = 3500, Job Id = ST_CLERK
Name = Davies, salary = 3100, Job Id = ST_CLERK
示例4 Cursor FOR Loop References Virtual Columns
In this example, the implicit cursor FOR LOOP references virtual columns by their aliases, full_name and dream_salary.
BEGINFOR item IN (SELECT first_name || ' ' || last_name AS full_name,salary * 10 AS dream_salary FROM employeesWHERE ROWNUM <= 5ORDER BY dream_salary DESC, last_name ASC) LOOPDBMS_OUTPUT.PUT_LINE(item.full_name || ' dreams of making ' || item.dream_salary);END LOOP;
END;
/Result:
Stephen King dreams of making 240000
Lex Garcia dreams of making 170000
Neena Yang dreams of making 170000
Alexander James dreams of making 90000
Bruce Miller dreams of making 60000
示例5 Subquery in FROM Clause of Parent Query
This example defines explicit cursor c1 with a query whose FROM clause contains a subquery.
DECLARECURSOR c1 ISSELECT t1.department_id, department_name, staffFROM departments t1,( SELECT department_id, COUNT(*) AS staffFROM employeesGROUP BY department_id) t2WHERE (t1.department_id = t2.department_id) AND staff >= 5ORDER BY staff;BEGINFOR dept IN c1LOOPDBMS_OUTPUT.PUT_LINE ('Department = '|| dept.department_name || ', staff = ' || dept.staff);END LOOP;
END;
/
Result:
Department = IT, staff = 5
Department = Finance, staff = 6
Department = Purchasing, staff = 6
Department = Sales, staff = 34
Department = Shipping, staff = 45
示例6 FETCH Statements Inside LOOP Statements
DECLARECURSOR c1 ISSELECT last_name, job_id FROM employeesWHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK')ORDER BY last_name;v_lastname employees.last_name%TYPE; -- variable for last_namev_jobid employees.job_id%TYPE; -- variable for job_idCURSOR c2 ISSELECT * FROM employeesWHERE REGEXP_LIKE (job_id, '[ACADFIMKSA]_M[ANGR]')ORDER BY job_id;v_employees employees%ROWTYPE; -- record variable for row of tableBEGINOPEN c1;LOOP -- Fetches 2 columns into variablesFETCH c1 INTO v_lastname, v_jobid;EXIT WHEN c1%NOTFOUND;DBMS_OUTPUT.PUT_LINE( RPAD(v_lastname, 25, ' ') || v_jobid );END LOOP;CLOSE c1;DBMS_OUTPUT.PUT_LINE( '-------------------------------------' );OPEN c2;LOOP -- Fetches entire row into the v_employees recordFETCH c2 INTO v_employees;EXIT WHEN c2%NOTFOUND;DBMS_OUTPUT.PUT_LINE( RPAD(v_employees.last_name, 25, ' ') ||v_employees.job_id );END LOOP;CLOSE c2;
END;
/Result:
Atkinson ST_CLERK
Bell SH_CLERK
Bissot ST_CLERK
...
Walsh SH_CLERK
-------------------------------------
Higgins AC_MGR
Gruenberg FI_MGR
Martinez MK_MAN
...
Errazuriz SA_MAN示例7 Fetching Data with Cursor Variables
This example uses one cursor variable to do what Example 6 does with two explicit cursors. The first OPEN FOR statement includes the query itself. The second OPEN FOR statement references a variable whose value is a query.
DECLAREcv SYS_REFCURSOR; -- cursor variablev_lastname employees.last_name%TYPE; -- variable for last_namev_jobid employees.job_id%TYPE; -- variable for job_idquery_2 VARCHAR2(200) :='SELECT * FROM employeesWHERE REGEXP_LIKE (job_id, ''[ACADFIMKSA]_M[ANGR]'')ORDER BY job_id';v_employees employees%ROWTYPE; -- record variable row of tableBEGINOPEN cv FORSELECT last_name, job_id FROM employeesWHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK')ORDER BY last_name;LOOP -- Fetches 2 columns into variablesFETCH cv INTO v_lastname, v_jobid;EXIT WHEN cv%NOTFOUND;DBMS_OUTPUT.PUT_LINE( RPAD(v_lastname, 25, ' ') || v_jobid );END LOOP;DBMS_OUTPUT.PUT_LINE( '-------------------------------------' );OPEN cv FOR query_2;LOOP -- Fetches entire row into the v_employees recordFETCH cv INTO v_employees;EXIT WHEN cv%NOTFOUND;DBMS_OUTPUT.PUT_LINE( RPAD(v_employees.last_name, 25, ' ') ||v_employees.job_id );END LOOP;CLOSE cv;
END;
/Result:
Atkinson ST_CLERK
Bell SH_CLERK
Bissot ST_CLERK
...
Walsh SH_CLERK
-------------------------------------
Higgins AC_MGR
Gruenberg FI_MGR
Martinez MK_MAN
...
Errazuriz SA_MAN示例8 Fetching from Cursor Variable into Collections
This example fetches from a cursor variable into two collections (nested tables), using the BULK COLLECT clause of the FETCH statement.
DECLARETYPE empcurtyp IS REF CURSOR;TYPE namelist IS TABLE OF employees.last_name%TYPE;TYPE sallist IS TABLE OF employees.salary%TYPE;emp_cv empcurtyp;names namelist;sals sallist;
BEGINOPEN emp_cv FORSELECT last_name, salary FROM employeesWHERE job_id = 'SA_REP'ORDER BY salary DESC;FETCH emp_cv BULK COLLECT INTO names, sals;CLOSE emp_cv;-- loop through the names and sals collectionsFOR i IN names.FIRST .. names.LASTLOOPDBMS_OUTPUT.PUT_LINE('Name = ' || names(i) || ', salary = ' || sals(i));END LOOP;
END;
/Result:
Name = Ozer, salary = 11500
Name = Abel, salary = 11000
Name = Vishney, salary = 10500
...
Name = Kumar, salary = 6100
