plsql中游标使用

打开游标后的循环  

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;