日期:2014-05-16 浏览次数:20531 次
在存储中使用游标是件很常见的事情,传统写法比较麻烦。今天将化繁为简的写法总结一下:
格式如下
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例子2:
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