在存储中使用游标是件很常见的事情，传统写法比较麻烦。今天将化繁为简的写法总结一下：

格式如下 

create or replace procedure ff
as 
   v_column_name
begin
     for v_info in (
                select column_name from cols where table_name = 'EMP'
                ) loop
                --定义和循环游标
                dbms_output.put_line(v_info.column_name);
                --调用游标中的值 
                end loop; 
end;
/*
  优点：
   1、不用指定接收游标值的变量
  2、不用显示的打开和关闭游标
  缺点：
  1、无法使用游标属性notfound,isopen,found,notfound 
  
*/

以下摘录几个官方文档例子：

DECLARE
  CURSOR c1 (job VARCHAR2, max_wage NUMBER) IS
    SELECT * FROM employees
    WHERE job_id = job
    AND salary > max_wage;
BEGIN
  FOR person IN c1('ST_CLERK', 3000)
  LOOP
     -- process data record
    DBMS_OUTPUT.PUT_LINE (
      'Name = ' || person.last_name || ', salary = ' ||
      person.salary || ', Job Id = ' || person.job_id
    );
  END LOOP;
END;
/

Result:

Name = Nayer, salary = 4065.6, Job Id = ST_CLERK
Name = Mikkilineni, salary = 3430.35, Job Id = ST_CLERK
Name = Landry, salary = 3049.2, Job Id = ST_CLERK
...
Name = Vargas, salary = 3176.25, Job Id = ST_CLERK

BEGIN
  FOR item IN (
    SELECT first_name || ' ' || last_name AS full_name,
           salary * 10                    AS dream_salary 
    FROM employees
    WHERE ROWNUM <= 5
    ORDER BY dream_salary DESC, last_name ASC
  ) LOOP
    DBMS_OUTPUT.PUT_LINE
      (item.full_name || ' dreams of making ' || item.dream_salary);
  END LOOP;
END;
/

Result:

Michael Hartstein dreams of making 143325
Pat Fay dreams of making 66150
Jennifer Whalen dreams of making 48510
Douglas Grant dreams of making 31531.5
Donald OConnell dreams of making 31531.5

注意：

Note:

When an exception is raised inside a cursorFOR LOOP statement, the cursor closes before the exception handler runs. Therefore, the values of explicit cursor attributes are not available in the handler.

当一个异常发生在for loop语句块内部是，游标关闭在异常被处理前。因此，在语句块中显示游标的属性无效

传统方式：（摘录自官方文档）

You can either declare an explicit cursor first and then define it later in the same block, subprogram, or package, or declare and define it at the same time.

An explicit cursor declaration, which only declares a cursor, has this syntax:

CURSOR cursor_name [ parameter_list ] RETURN return_type;

An explicit cursor definition has this syntax:

CURSOR cursor_name [ parameter_list ] [ RETURN return_type ]
  IS select_statement;

If you declared the cursor earlier, then the explicit cursor definition defines it; otherwise, it both declares and defines it.

DECLARE
  CURSOR c1 RETURN departments%ROWTYPE;    -- 声明 c1
 
  CURSOR c1 RETURN departments%ROWTYPE IS  -- 定义 c1,
    SELECT * FROM departments              
    WHERE department_id = 110;
  /*
    在实际使用中主要采用c2的方式声明和定义游标
    c1 和 c3的方式至今没用到过。官方文档 就是标准....
  */
  CURSOR c2 IS                             -- 声明 and 定义 c2 
    SELECT employee_id, job_id, salary FROM employees
    WHERE salary > 2000; 
  /*
    以下方式也是常用写法。
    游标定义方式 OPEN C2 FOR 'SQL statements '
    好处在用可灵活使用绑定变量
  */
  TYPE cursor_type IS REF CURSOR;
  C2 cursor_type;
  
  CURSOR c3 RETURN locations%ROWTYPE;      -- 声明 c3 
 
  CURSOR c3 IS