打开游标后的循环
declare
i_total integer:=1000;cursor emp_cur is select * from emp order by salary asc;
i_emp emp%rowtype;
begin
open emp_cur;
loop
fetch emp_cur into i_emp;
exit when emp_cur%notfound;
i_total:=i_total+i_emp.salary;
end loop;
close emp_cur;
dbms_output.put_line(i_total);
end;
select into
create or replace procedure t_p(emp_id number) is
v_name varchar2(1000);
begin
select first_name into v_name from emp where employee_id=emp_id;
dbms_output.put_line(v_name);
end;
动态查询
create or replace procedure p1(employee_id number, department_id number) is
i_name varchar2(100);
begin
execute immediate 'select first_name from emp where employee_id='||employee_id ||' and department_id='||department_id into i_name;
dbms_output.put_line(i_name);
end;
动态查询,结果放到集合中
create or replace procedure p2(department_id number) is
type names is table of varchar2(100);
i_name names;
begin
execute immediate 'select first_name from emp where department_id=' ||
department_id bulk collect
into i_name;
for idx in 1 .. i_name.count loop
dbms_output.put_line(i_name(idx));
end loop;
end;
游标变量
游标变量的使用范围
-
Pass a cursor variable back to the host environment that called the program unit—the result set can be “consumed” for display or other processing.
-
Construct a result set inside a function, and return a cursor variable to that set. This is especially handy when you need to use PL/SQL, in addition to SQL, to build the result set.
-
Pass a cursor variable to a pipelined table function—a powerful but quite advanced optimization technique. A full explanation of cursor variables, including the differences between strong and weak REF CURSOR types, is beyond the scope of this article.
-
Instead, I will show the basic syntax for working with cursor variables and identify situations in which you might consider using this feature
CREATE OR REPLACE FUNCTION names_for(name_type_in IN VARCHAR2)
RETURN SYS_REFCURSOR IS
l_return SYS_REFCURSOR;
BEGIN
CASE name_type_in
WHEN 'EMP' THEN
OPEN l_return FOR
SELECT last_name FROM emp ORDER BY employee_id;
WHEN 'DEPT' THEN
OPEN l_return FOR
SELECT name FROM departments ORDER BY id;
END CASE;
RETURN l_return;
END names_for;
使用游标变量
DECLARE
l_names SYS_REFCURSOR;
l_name VARCHAR2 (32767);
BEGIN
l_names := names_for ('DEPT');
LOOP
FETCH l_names INTO l_name;
EXIT WHEN l_names%NOTFOUND;
DBMS_OUTPUT.put_line (l_name);
END LOOP;
CLOSE l_names;
END;
没有显示声明的游标是隐式游标,隐式游标有被称为sql游标,可以使用游标属性查看游标的信息。
update emp set email ='test'where employee_id = 123;
dbms_output.put_line(sql%rowcount || 'rows been updated');
ifsql%notfoundthen
dbms_output.put_line('no record');
endif;
commit;
exception
whenothersthen
dbms_output.put_line(SQLERRM);
end;
显示游标提供了比隐式游标更多的功能,通常使用显示游标处理select语句返回的多行数据。
在游标中指定参数
declare
cursor emp_cursor(p_deptno innumber) is
select * from emp where deptno = p_deptno;
begin
open emp_cursor(20);
end;
指定游标的返回类型
declare
cursor emp_cursor(p_deptno innumber) return emp%rowtypeis
select * from emp wehre deptno = p_deptno;
begin
open emp_cursor(20);
end;
使用bulk collect语句批量提取游标数据
declare
type depttab_type istableof dept%rowtype;
depttab depttab_type;
cursor deptcur is
select * from dept;
begin
open deptcur;
fetch deptcur bulkcollect
into depttab;
for i in1 .. depttab.count loop
dbms_output.put_line(depttab(i)
.deptno || ' ' || depttab(i).dname|| ' ' || depttab(i).loc);
endloop;
close deptcur;
end;
使用bulk collect limit语句批量提取游标数据,用于游标中的数据量很大的情况下
declare
type t9_type isvarray(4) of t9%rowtype;
t9tab t9_type;
cursor t9_cursor is
select * from t9;
v_rows int := 4;
v_count int := 0;
begin
open t9_cursor;
loop
fetch t9_cursor bulkcollect
into t9tab limit v_rows;
exitwhen t9_cursor%notfound;
dbms_output.put('名字');
for i in1 .. (t9_cursor%rowcount - v_count) loop
dbms_output.put(t9tab(i).object_name || ' ');
endloop;
dbms_output.new_line;
v_count := t9_cursor%rowcount;
dbms_output.put_line(v_count);
endloop;
close t9_cursor;
end;
修改游标数据
declare
cursor t9_cursor(p_id innumber) is
select * from t9 where object_id = p_id forupdate;
begin
for t9_row in t9_cursor(20) loop
update t9 set object_name = 'test'wherecurrentof t9_cursor;
endloop;
commit;
end;
delete也是一样的
delete from t9 where current of t9_cursor;
之前声明的游标,在定义的时候就绑定了一个查询,这种的游标称为静态游标,游标变量是在定义的时候不绑定到具体的查询,可以打开任何类型兼容的查询。
declare
type emp_type isrefcursorreturn emp%rowtype;
emp_cur emp_type;
emp_row emp%rowtype;
begin
open emp_cur for
select * from emp;
loop
fetch emp_cur
into emp_row;
exitwhen emp_cur%notfound;
dbms_output.put_line('name' || emp_row.ename);
endloop;
end;
可以使用sys_refcursor类型的游标来定义游标变量下面的2个语句是等效的
Type emp_type is ref cursor;
Emp_cur emp_type;
与下面的语句是等效的
emp_cursor sys_refcursor;