Processing Query Result Sets

最新推荐文章于 2025-03-25 14:57:48 发布

数据架构

最新推荐文章于 2025-03-25 14:57:48 发布

阅读量272

点赞数

分类专栏： PL/SQL 文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/u011868279/article/details/129860996

版权

PL/SQL 专栏收录该内容

8 篇文章

订阅专栏

The following PL/SQL statements use implicit cursors that PL/SQL defines and manages for you:

SELECT INTO
Implicit cursor FOR LOOP

The following PL/SQL statements use explicit cursors:

Explicit cursor FOR LOOP

You define the explicit cursor, but PL/SQL manages it while the statement runs.
OPEN, FETCH, and CLOSE

You define and manage the explicit cursor.

1 Processing Query Result Sets With SELECT INTO Statements

Using an implicit cursor, the SELECT INTO statement retrieves values from one or more database tables (as the SQL SELECT statement does) and stores them in variables (which the SQL SELECT statement does not do).

1.1 Handling Single-Row Result Sets

If you expect the query to return only one row, then use the SELECT INTO statement to store values from that row in either one or more scalar variables, or one record variable.

If the query might return multiple rows, but you care about only the nth row, then restrict the result set to that row with the clause WHERE ROWNUM=n.

1.2 Handling Large Multiple-Row Result Sets

If you must assign a large quantity of table data to variables, Oracle recommends using the SELECT INTO statement with the BULK COLLECT clause.

2 Processing Query Result Sets With Cursor FOR LOOP Statements

The cursor FOR LOOP statement lets you run a SELECT statement and then immediately loop through the rows of the result set.

If you use the SELECT statement only in the cursor FOR LOOP statement, then specify the SELECT statement inside the cursor FOR LOOP statement, as in Example 6-18. This form of the cursor FOR LOOP statement uses an implicit cursor, and is called an implicit cursor FOR LOOP statement. Because the implicit cursor is internal to the statement, you cannot reference it with the name SQL.

If you use the SELECT statement multiple times in the same PL/SQL unit, then define an explicit cursor for it and specify that cursor in the cursor FOR LOOP statement, as in Example 6-19. This form of the cursor FOR LOOP statement is called an explicit cursor FOR LOOP statement. You can use the same explicit cursor elsewhere in the same PL/SQL unit.

Example 6-18 Implicit Cursor FOR LOOP Statement

[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 30 16:50:15 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show user;
USER is "SYS"
SQL> alter session set container=PDB1;

Session altered.

SQL> 
SQL> set pagesize 200 linesize 200
SQL> 
SQL> conn hr/hr@PDB1;
Connected.
SQL> set pagesize 200 linesize 200
SQL> 
SQL> 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  /

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> /
Name = Atkinson, Job = ST_CLERK
Name = Bell, Job = SH_CLERK
Name = Bissot, Job = ST_CLERK
Name = Bull, Job = SH_CLERK
Name = Cabrio, Job = SH_CLERK
Name = Chung, Job = SH_CLERK
Name = Davies, Job = ST_CLERK
Name = Dellinger, Job = SH_CLERK
Name = Dilly, Job = SH_CLERK
Name = Everett, Job = SH_CLERK
Name = Feeney, Job = SH_CLERK
Name = Gates, Job = SH_CLERK
Name = Gee, Job = ST_CLERK
Name = Grant, Job = SH_CLERK
Name = Jones, Job = SH_CLERK
Name = Ladwig, Job = ST_CLERK
Name = Mallin, Job = ST_CLERK
Name = Marlow, Job = ST_CLERK
Name = Matos, Job = ST_CLERK
Name = McCain, Job = SH_CLERK
Name = OConnell, Job = SH_CLERK
Name = Olson, Job = ST_CLERK
Name = Patel, Job = ST_CLERK
Name = Perkins, Job = SH_CLERK
Name = Philtanker, Job = ST_CLERK
Name = Rajs, Job = ST_CLERK
Name = Rogers, Job = ST_CLERK
Name = Sarchand, Job = SH_CLERK
Name = Seo, Job = ST_CLERK
Name = Stiles, Job = ST_CLERK
Name = Vargas, Job = ST_CLERK
Name = Walsh, Job = SH_CLERK

PL/SQL procedure successfully completed.

SQL>

Example 6-19 Explicit Cursor FOR LOOP Statement

SQL> 
SQL> DECLARE
  2    CURSOR c1 IS
  3      SELECT last_name, job_id FROM employees
  4      WHERE job_id LIKE '%CLERK%' AND manager_id > 120
  5      ORDER BY last_name;
  6  BEGIN
  7    FOR item IN c1
  8    LOOP
  9      DBMS_OUTPUT.PUT_LINE
 10        ('Name = ' || item.last_name || ', Job = ' || item.job_id);
 11    END LOOP;
 12  END;
 13  /
Name = Atkinson, Job = ST_CLERK
Name = Bell, Job = SH_CLERK
Name = Bissot, Job = ST_CLERK
Name = Bull, Job = SH_CLERK
Name = Cabrio, Job = SH_CLERK
Name = Chung, Job = SH_CLERK
Name = Davies, Job = ST_CLERK
Name = Dellinger, Job = SH_CLERK
Name = Dilly, Job = SH_CLERK
Name = Everett, Job = SH_CLERK
Name = Feeney, Job = SH_CLERK
Name = Gates, Job = SH_CLERK
Name = Gee, Job = ST_CLERK
Name = Grant, Job = SH_CLERK
Name = Jones, Job = SH_CLERK
Name = Ladwig, Job = ST_CLERK
Name = Mallin, Job = ST_CLERK
Name = Marlow, Job = ST_CLERK
Name = Matos, Job = ST_CLERK
Name = McCain, Job = SH_CLERK
Name = OConnell, Job = SH_CLERK
Name = Olson, Job = ST_CLERK
Name = Patel, Job = ST_CLERK
Name = Perkins, Job = SH_CLERK
Name = Philtanker, Job = ST_CLERK
Name = Rajs, Job = ST_CLERK
Name = Rogers, Job = ST_CLERK
Name = Sarchand, Job = SH_CLERK
Name = Seo, Job = ST_CLERK
Name = Stiles, Job = ST_CLERK
Name = Vargas, Job = ST_CLERK
Name = Walsh, Job = SH_CLERK

PL/SQL procedure successfully completed.

SQL>

Example 6-20 Passing Parameters to Explicit Cursor FOR LOOP Statement

SQL> 
SQL> 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  /
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

PL/SQL procedure successfully completed.

SQL>

Example 6-21 Cursor FOR Loop References Virtual Columns

the implicit cursor FOR LOOP references virtual columns by their aliases, full_name and dream_salary.

SQL> 
SQL> 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  /
Michael Hartstein dreams of making 130000
Pat Fay dreams of making 60000
Jennifer Whalen dreams of making 44000
Douglas Grant dreams of making 26000
Donald OConnell dreams of making 26000

PL/SQL procedure successfully completed.

SQL>

3 Processing Query Result Sets With Explicit Cursors, OPEN, FETCH, and CLOSE

For full control over query result set processing, declare explicit cursors and manage them with the statements OPEN, FETCH, and CLOSE.

Process multiple result sets in parallel, using multiple cursors.

Process multiple rows in a single loop iteration, skip rows, or split the processing into multiple loops.

Specify the query in one PL/SQL unit but retrieve the rows in another.

4 Processing Query Result Sets with Subqueries

Example 6-22 Subquery in FROM Clause of Parent Query

SQL> 
SQL> 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;
 11  
 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  /
Department = IT, staff = 5
Department = Finance, staff = 6
Department = Purchasing, staff = 6
Department = Sales, staff = 34
Department = Shipping, staff = 45

PL/SQL procedure successfully completed.

SQL>

Example 6-23 Correlated Subquery

SQL> 
SQL> DECLARE
  2    CURSOR c1 IS
  3      SELECT department_id, last_name, salary
  4      FROM employees t
  5      WHERE salary > ( SELECT AVG(salary)
  6                       FROM employees
  7                       WHERE t.department_id = department_id
  8                     )
  9      ORDER BY department_id, last_name;
 10  BEGIN
 11    FOR person IN c1
 12    LOOP
 13      DBMS_OUTPUT.PUT_LINE('Making above-average salary = ' || person.last_name);
 14    END LOOP;
 15  END;
 16  /
Making above-average salary = Hartstein
Making above-average salary = Raphaely
Making above-average salary = Bell
Making above-average salary = Bull
Making above-average salary = Chung
Making above-average salary = Dilly
Making above-average salary = Everett
Making above-average salary = Fripp
Making above-average salary = Kaufling
Making above-average salary = Ladwig
Making above-average salary = Mourgos
Making above-average salary = Rajs
Making above-average salary = Sarchand
Making above-average salary = Vollman
Making above-average salary = Weiss
Making above-average salary = Ernst
Making above-average salary = Hunold
Making above-average salary = Abel
Making above-average salary = Bernstein
Making above-average salary = Bloom
Making above-average salary = Cambrault
Making above-average salary = Errazuriz
Making above-average salary = Fox
Making above-average salary = Greene
Making above-average salary = Hall
Making above-average salary = King
Making above-average salary = McEwen
Making above-average salary = Ozer
Making above-average salary = Partners
Making above-average salary = Russell
Making above-average salary = Sully
Making above-average salary = Tucker
Making above-average salary = Vishney
Making above-average salary = Zlotkey
Making above-average salary = King
Making above-average salary = Faviet
Making above-average salary = Greenberg
Making above-average salary = Higgins

PL/SQL procedure successfully completed.

SQL>