cursor的简单写法和官方文档对比
在存储中使用游标是件很常见的事情，传统写法比较麻烦。
今天将化繁为简的写法总结一下：
www.2cto.com
格式如下
[sql]
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;
/*
优点： www.2cto.com
1、不用指定接收游标值的变量
2、不用显示的打开和关闭游标
缺点：
1、无法使用游标属性notfound,isopen,found,notfound
*/
以下摘录几个官方文档例子： www.2cto.com
[sql]
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
例子2：
[sql]
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语句块内部是，游标关闭在异常被处理前。因此，在语句块中显示游标的属性无效
www.2cto.com
传统方式：（摘录自官方文档）
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.
例子如下： www.2cto.com
[sql]
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;