ToB企服应用市场:ToB评测及商务社交产业平台

标题: Oracle显式游标Explicit Cursor [打印本页]
作者: 北冰洋以北    时间: 2024-8-19 02:44
标题: Oracle显式游标Explicit Cursor
在Oracle数据库中，游标（Cursor）是一个数据库查询的效果集的引用。它答应你逐行地处理查询效果，而不是一次性地处理整个效果集。游标分为隐式游标和显式游标两种。
显式游标（Explicit Cursor）

显式游标是你在PL/SQL步调中明确声明和操作的游标。当你需要对查询效果进行更复杂的处理时，好比逐行处理查询效果或者需要获取查询的某些状态信息（如是否找到行），就需要使用显式游标。
显式游标的声明

显式游标在PL/SQL中通过CURSOR关键字进行声明，并通常指定一个游标名称和一个查询语句。例如：
  1. DECLARE
  2.   CURSOR employee_cur IS
  3.     SELECT employee_id, last_name FROM employees;
  4. BEGIN
  5.   -- 游标的使用将在这里进行
  6. END;
复制代码
显式游标的打开、获取和关闭

示例1 LOOP … EXIT WHEN … END LOOP

下面是一个使用显式游标逐行处理employees表中数据的例子：
  1. DECLARE
  2.   CURSOR employee_cur IS
  3.     SELECT employee_id, last_name FROM employees;
  4.   v_employee_id employees.employee_id%TYPE;
  5.   v_last_name employees.last_name%TYPE;
  6. BEGIN
  7.   OPEN employee_cur;
  8.   LOOP
  9.     FETCH employee_cur INTO v_employee_id, v_last_name;
  10.     EXIT WHEN employee_cur%NOTFOUND;
  11.     DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || ', Last Name: ' || v_last_name);
  12.   END LOOP;
  13.   CLOSE employee_cur;
  14. 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.
  1. BEGIN
  2.   FOR item IN (
  3.     SELECT last_name, job_id
  4.     FROM employees
  5.     WHERE job_id LIKE '%CLERK%'
  6.     AND manager_id > 120
  7.     ORDER BY last_name
  8.   )
  9.   LOOP
  10.     DBMS_OUTPUT.PUT_LINE
  11.       ('Name = ' || item.last_name || ', Job = ' || item.job_id);
  12.   END LOOP;
  13. END;
  14. /
  16. -- OR
  18. DECLARE
  19.   CURSOR c1 IS
  20.     SELECT last_name, job_id FROM employees
  21.     WHERE job_id LIKE '%CLERK%' AND manager_id > 120
  22.     ORDER BY last_name;
  23. BEGIN
  24.   FOR item IN c1
  25.   LOOP
  26.     DBMS_OUTPUT.PUT_LINE
  27.       ('Name = ' || item.last_name || ', Job = ' || item.job_id);
  28.   END LOOP;
  29. END;
  30. /
复制代码
Result:
  1. Name = Atkinson, Job = ST_CLERK
  2. Name = Bell, Job = SH_CLERK
  3. Name = Bissot, Job = ST_CLERK
  4. ...
  5. 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.
  1. DECLARE
  2.   CURSOR c1 (job VARCHAR2, max_wage NUMBER) IS
  3.     SELECT * FROM employees
  4.     WHERE job_id = job
  5.     AND salary > max_wage;
  6. BEGIN
  7.   FOR person IN c1('ST_CLERK', 3000)
  8.   LOOP
  9.      -- process data record
  10.     DBMS_OUTPUT.PUT_LINE (
  11.       'Name = ' || person.last_name || ', salary = ' ||
  12.       person.salary || ', Job Id = ' || person.job_id
  13.     );
  14.   END LOOP;
  15. END;
  16. /
复制代码
Result:
  1. Name = Nayer, salary = 3200, Job Id = ST_CLERK
  2. Name = Bissot, salary = 3300, Job Id = ST_CLERK
  3. Name = Mallin, salary = 3300, Job Id = ST_CLERK
  4. Name = Ladwig, salary = 3600, Job Id = ST_CLERK
  5. Name = Stiles, salary = 3200, Job Id = ST_CLERK
  6. Name = Rajs, salary = 3500, Job Id = ST_CLERK
  7. 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.
  1. BEGIN
  2.   FOR item IN (
  3.     SELECT first_name || ' ' || last_name AS full_name,
  4.            salary * 10                    AS dream_salary
  5.     FROM employees
  6.     WHERE ROWNUM <= 5
  7.     ORDER BY dream_salary DESC, last_name ASC
  8.   ) LOOP
  9.     DBMS_OUTPUT.PUT_LINE
  10.       (item.full_name || ' dreams of making ' || item.dream_salary);
  11.   END LOOP;
  12. END;
  13. /
复制代码
Result:
  1. Stephen King dreams of making 240000
  2. Lex Garcia dreams of making 170000
  3. Neena Yang dreams of making 170000
  4. Alexander James dreams of making 90000
  5. 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.
  1. DECLARE
  2.   CURSOR c1 IS
  3.     SELECT t1.department_id, department_name, staff
  4.     FROM departments t1,
  5.          ( SELECT department_id, COUNT(*) AS staff
  6.            FROM employees
  7.            GROUP BY department_id
  8.          ) t2
  9.     WHERE (t1.department_id = t2.department_id) AND staff >= 5
  10.     ORDER BY staff;
  12. BEGIN
  13.    FOR dept IN c1
  14.    LOOP
  15.      DBMS_OUTPUT.PUT_LINE ('Department = '
  16.        || dept.department_name || ', staff = ' || dept.staff);
  17.    END LOOP;
  18. END;
  19. /
复制代码
Result:
  1. Department = IT, staff = 5
  2. Department = Finance, staff = 6
  3. Department = Purchasing, staff = 6
  4. Department = Sales, staff = 34
  5. Department = Shipping, staff = 45
复制代码
示例6 FETCH Statements Inside LOOP Statements

  1. DECLARE
  2.   CURSOR c1 IS
  3.     SELECT last_name, job_id FROM employees
  4.     WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK')
  5.     ORDER BY last_name;
  7.   v_lastname  employees.last_name%TYPE;  -- variable for last_name
  8.   v_jobid     employees.job_id%TYPE;     -- variable for job_id
  10.   CURSOR c2 IS
  11.     SELECT * FROM employees
  12.     WHERE REGEXP_LIKE (job_id, '[ACADFIMKSA]_M[ANGR]')
  13.     ORDER BY job_id;
  15.   v_employees employees%ROWTYPE;  -- record variable for row of table
  17. BEGIN
  18.   OPEN c1;
  19.   LOOP  -- Fetches 2 columns into variables
  20.     FETCH c1 INTO v_lastname, v_jobid;
  21.     EXIT WHEN c1%NOTFOUND;
  22.     DBMS_OUTPUT.PUT_LINE( RPAD(v_lastname, 25, ' ') || v_jobid );
  23.   END LOOP;
  24.   CLOSE c1;
  25.   DBMS_OUTPUT.PUT_LINE( '-------------------------------------' );
  27.   OPEN c2;
  28.   LOOP  -- Fetches entire row into the v_employees record
  29.     FETCH c2 INTO v_employees;
  30.     EXIT WHEN c2%NOTFOUND;
  31.     DBMS_OUTPUT.PUT_LINE( RPAD(v_employees.last_name, 25, ' ') ||
  32.                                v_employees.job_id );
  33.   END LOOP;
  34.   CLOSE c2;
  35. END;
  36. /
复制代码
Result:
  1. Atkinson                 ST_CLERK
  2. Bell                     SH_CLERK
  3. Bissot                   ST_CLERK
  4. ...
  5. Walsh                    SH_CLERK
  6. -------------------------------------
  7. Higgins                  AC_MGR
  8. Gruenberg                FI_MGR
  9. Martinez                 MK_MAN
  10. ...
  11. 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.
  1. DECLARE
  2.   cv SYS_REFCURSOR;  -- cursor variable
  4.   v_lastname  employees.last_name%TYPE;  -- variable for last_name
  5.   v_jobid     employees.job_id%TYPE;     -- variable for job_id
  7.   query_2 VARCHAR2(200) :=
  8.     'SELECT * FROM employees
  9.     WHERE REGEXP_LIKE (job_id, ''[ACADFIMKSA]_M[ANGR]'')
  10.     ORDER BY job_id';
  12.   v_employees employees%ROWTYPE;  -- record variable row of table
  14. BEGIN
  15.   OPEN cv FOR
  16.     SELECT last_name, job_id FROM employees
  17.     WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK')
  18.     ORDER BY last_name;
  20.   LOOP  -- Fetches 2 columns into variables
  21.     FETCH cv INTO v_lastname, v_jobid;
  22.     EXIT WHEN cv%NOTFOUND;
  23.     DBMS_OUTPUT.PUT_LINE( RPAD(v_lastname, 25, ' ') || v_jobid );
  24.   END LOOP;
  26.   DBMS_OUTPUT.PUT_LINE( '-------------------------------------' );
  28.   OPEN cv FOR query_2;
  30.   LOOP  -- Fetches entire row into the v_employees record
  31.     FETCH cv INTO v_employees;
  32.     EXIT WHEN cv%NOTFOUND;
  33.     DBMS_OUTPUT.PUT_LINE( RPAD(v_employees.last_name, 25, ' ') ||
  34.                                v_employees.job_id );
  35.   END LOOP;
  37.   CLOSE cv;
  38. END;
  39. /
复制代码
Result:
  1. Atkinson                 ST_CLERK
  2. Bell                     SH_CLERK
  3. Bissot                   ST_CLERK
  4. ...
  5. Walsh                    SH_CLERK
  6. -------------------------------------
  7. Higgins                  AC_MGR
  8. Gruenberg                FI_MGR
  9. Martinez                 MK_MAN
  10. ...
  11. 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.
  1. DECLARE
  2.   TYPE empcurtyp IS REF CURSOR;
  3.   TYPE namelist IS TABLE OF employees.last_name%TYPE;
  4.   TYPE sallist IS TABLE OF employees.salary%TYPE;
  5.   emp_cv  empcurtyp;
  6.   names   namelist;
  7.   sals    sallist;
  8. BEGIN
  9.   OPEN emp_cv FOR
  10.     SELECT last_name, salary FROM employees
  11.     WHERE job_id = 'SA_REP'
  12.     ORDER BY salary DESC;
  14.   FETCH emp_cv BULK COLLECT INTO names, sals;
  15.   CLOSE emp_cv;
  16.   -- loop through the names and sals collections
  17.   FOR i IN names.FIRST .. names.LAST
  18.   LOOP
  19.     DBMS_OUTPUT.PUT_LINE
  20.       ('Name = ' || names(i) || ', salary = ' || sals(i));
  21.   END LOOP;
  22. END;
  23. /
复制代码
Result:
  1. Name = Ozer, salary = 11500
  2. Name = Abel, salary = 11000
  3. Name = Vishney, salary = 10500
  4. ...
  5. Name = Kumar, salary = 6100
复制代码
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4