Oracle 10g中游标的使用

时间：2011-01-17 来源：mxcjwl314

－－在显示游标中使用FETCH...INTO

代码

declare
  cursor tag_cursor is
    select trgt_name from t_b_phd_allunits;
  v_tag t_b_phd_allunits.trgt_name%type;
begin
  open tag_cursor;
  loop
    fetch tag_cursor
      into v_tag;
    exit when tag_cursor%notfound;
    dbms_output.put_line(v_tag);
  end loop;
  close tag_cursor;
end;

－－在显示游标中使用FETCH...BULK COLLECT INTO语句提取所有数据

代码

declare
  cursor tag_cursor is
    select trgt_name from t_b_phd_allunits;
  type tag_type_table is table of varchar2(50);
  tag_table tag_type_table;
begin
  open tag_cursor;
  fetch tag_cursor bulk collect
    into tag_table;
  for i in 1 .. tag_table.count loop
    dbms_output.put_line(tag_table(i));
  end loop;
  close tag_cursor;
end;

－－在显示游标中使用FETCH...BULK COLLECT INTO...LIMIT语句提取部分数据

代码

declare
  type tag_array_type is varray(5) of varchar2(50);
  tag_array tag_array_type;
  cursor tag_cursor is
    select trgt_name from t_b_phd_allunits;
  rows    int := 5;
  v_count int := 0;
begin
  open tag_cursor;
  loop
    fetch tag_cursor bulk collect
      into tag_array limit rows;
    dbms_output.put_line('<<<<<<<<----' || tag_cursor%rowcount);
    for i in 1 .. (tag_cursor%rowcount - v_count) loop
      dbms_output.put(tag_array(i) || ' ');
    end loop;
    dbms_output.new_line;
    v_count := tag_cursor%rowcount;
    exit when tag_cursor%notfound;
  end loop;
  close tag_cursor;
end;

－－在游标中定义记录变量

代码

declare
  cursor tag_cursor is
    select trgt_name from t_b_phd_allunits;
  tag_record tag_cursor%rowtype;
begin
  open tag_cursor;
  loop
    fetch tag_cursor
      into tag_record;
    exit when tag_cursor%notfound;
    dbms_output.put_line(tag_record.trgt_name);
  end loop;
  close tag_cursor;
end;

－－参数游标

代码

declare
  cursor tag_cursor(corpid varchar2) is
    select trgt_name from t_b_phd_allunits where corp_id = corpid;
  v_tag t_b_phd_allunits.trgt_name%type;
begin
  open tag_cursor('DQSH');
  loop
    fetch tag_cursor
      into v_tag;
    exit when tag_cursor%notfound;
    dbms_output.put_line(v_tag);
  end loop;
end;

－－使用游标更新数据

代码

declare
  cursor person_cursor is
    select person_name, telephone from t_b_phd_person for update;
  v_name t_b_phd_person.person_name%type;
  v_tel  t_b_phd_person.telephone%type;
begin
  open person_cursor;
  loop
    fetch person_cursor
      into v_name, v_tel;
    exit when person_cursor%notfound;
    if v_name = '张三' then
      update t_b_phd_person
         set telephone = '13545200123'
       where current of person_cursor;
    end if;
  end loop;
  close person_cursor;
end;

－－使用OF子句在特定表上加行共享锁

如果游标子查询涉及到多张表，那么在默认情况下会在所有修改表行上加行共享锁。为了只在特定表上加行共享锁，需要在FOR UPDATE子句后面带有OF子句。

－－使用NOWAIT子句

使用FOR UPDATE语句对被作用行加锁，如果其他会话已经在被作用行上加锁，那么在默认情况下当前会话要一直等待对方释放锁。当指定了NOWAIT子句后，如果其他会话已经在被作用行加锁，那么当前会话会显示错误提示消息。

－－使用游标FOR循环

declare
  cursor tag_cursor is
    select trgt_name from t_b_phd_allunits;
begin
  for tag_record in tag_cursor loop
    dbms_output.put_line(tag_record.trgt_name);
  end loop;
end;

－－游标FOR循环中使用子查询

begin
  for tag_record in (select trgt_name from t_b_phd_allunits) loop
    dbms_output.put_line(tag_record.trgt_name);
  end loop;
end;

－－在定义REF CURSOR类型时不指定return子句

代码

declare
  type tag_cursor_type is ref cursor;
  tag_cusor  tag_cursor_type;
  tag_record t_b_phd_allunits%rowtype;
begin
  open tag_cusor for
    select * from t_b_phd_allunits;
  loop
    fetch tag_cusor
      into tag_record;
    exit when tag_cusor%notfound;
    dbms_output.put_line(tag_record.trgt_name);
  end loop;
  close tag_cusor;
end;

－－在定义REF CURSOR类型时指定return子句

代码

declare
  type tag_record_type is record(
    unitid  varchar2(10),
    tagname varchar2(50));
  type tag_cursor_type is ref cursor return tag_record_type;
  tag_cusor  tag_cursor_type;
  tag_record tag_record_type;
begin
  open tag_cusor for
    select unit_id, trgt_name from t_b_phd_allunits;
  loop
    fetch tag_cusor
      into tag_record;
    exit when tag_cusor%notfound;
    dbms_output.put_line(tag_record.tagname);
  end loop;
  close tag_cusor;
end;

－－列出当前用户的子程序

select object_name, created, status
  from user_objects
 where object_type in ('PROCEDURE', 'FUNCTION');

－－列出子程序的源代码

select text from user_source where name = 'PKG_TEST' and type='PACKAGE'