游标检索

declare
  cursor emp_cursor is select ename,salary from emp where empno = '2';
  v_name emp.ename%type;
  v_sal  emp.salary%type;
begin
  open emp_cursor;
  loop
    fetch emp_cursor into v_name,v_sal;
    exit when emp_cursor%notfound;
    dbms_output.put_line(v_name ||','||v_sal);
  end loop;
  close emp_cursor;
end; 

 

 

declare
  cursor emp_cursor is select ename from emp where empno = '2'
  type ename_table_type is table of emp.ename%type;
  ename_table ename_table_type;
begin
  open emp_cursor;
  fetch emp_cursor bulk collect into ename_table;     
  for i 1 .. ename_table.count loop
    dbms_output.put_line(ename_table(i));
  end loop;
  close emp_cursor;
end;

 

 

declare
  type name_array_type is varray(5) of emp.ename%type;
  name_array name_array_type;
  cursor emp_cursor is select ename from emp;
  rows int := 3;
  v_count int := 0;
begin
  open emp_cursor;
  loop
    fetch emp_cursor bulk collect into name_array limit rows;
    dbms_output.put_line('雇员名:');
    dbms_output.put_line('循环总数:'||to_char(emp_cursor%rowcount));
    dbms_output.put_line('v_count:'||to_char(v_count));
    for i in 1 .. (emp_cursor%rowcount - v_count) loop
      dbms_output.put(name_array(i) || '   ');
    end loop;
    dbms_output.new_line;
    v_count := emp_cursor%rowcount;
    exit when emp_cursor%notfound;
  end loop;
  close emp_cursor;
end;     
   

 

declare
  cursor emp_cursor is select ename from emp where empno = '2';
  type ename_table_type is table of varchar2(20);
  ename_table ename_table_type;
begin
  if not emp_cursor%isopen then
    open emp_cursor;
  end if;
  fetch emp_cursor bulk collect into ename_table;
  dbms_output.put_line(emp_cursor%rowcount);
end;     

 

 

declare
  cursor emp_cursor is select ename,salary from emp;
  emp_record emp_cursor%rowtype;
begin
  open emp_cursor;
  loop
    fetch emp_cursor into emp_record;
    exit when emp_cursor%notfound;
    dbms_output.put_line('雇员名'||emp_record.ename||','||'雇员薪水'||emp_record.salary);
  end loop;
  close emp_cursor;
end;   

 

 

 

declare
  cursor emp_cursor(no number) is select ename from emp where deptno = no;
  v_name emp.ename%type;
begin
  open emp_cursor(3);
  loop
    fetch emp_cursor into v_name;
    exit when emp_cursor%notfound;
    dbms_output.put_line(v_name);
  end loop;
  close emp_cursor;
end;     

 

declare
num number:=⊤
cursor c1 is select ename,salfrom emp order by saldesc;
emp_rec c1%rowtype;begin
for emp_recin c1 loop
  insert into top_dogs values emp_rec;
  exit when c1%rowcount=num;
end loop;
commit;
execute immediate'truncate table top_dogs'; --删除表内容

end;

/

 

 

动态游标

1.

Declare
      type rc is refcursor;  ---定义动态游标          
      l_cursor rc;
      v_emp emp%rowtype;
    begin
      open l_cursorfor 'select * from emp where deptno = :v_detpno'
      using &v_deptno;
      loop
         fetch l_cursorinto v_emp;
         exitwhen l_cursor%notfound;
          dbms_output.put_line(v_emp.empno);
      end loop;
      close l_cursor;     
    end;

declare
 cursor cur is select b.SID,b.SERIAL# 
    from V$LOCKED_OBJECT  a, V$SESSION b
    where a.SESSION_ID = b.SID order by b.LOGON_TIME;
  v_sid number;
  v_serial  number;
begin
  OPEN CUR;
loop
  fetch cur into v_sid , v_serial;
  EXIT WHEN CUR%NOTFOUND;
 DBMS_OUTPUT.PUT_LINE(v_sid || ' ' || v_serial);


END LOOP;
CLOSE CUR;


end;

 

declare
   cursor tmp_cursor is select usdpric from t_tmp;
  -- cursor fob_cursor is select cgicode from fobupricdetail for update;
   v_cgicode t_tmp.usdpric%type;
   i integer;
 begin
   i:=0;
   open tmp_cursor;
   loop
     fetch tmp_cursor into v_cgicode;
     exit when tmp_cursor%notfound;
     dbms_output.put_line(to_number('1000000021008179') + i);
    update fobupricdetail set fobpriceusd2 = v_cgicode where fobupricicode= '1000000000014929' and fobupricgicode = to_char(to_number('1000000021008179') + i);
     i := i+1;
   end loop;
   close tmp_cursor;
 end;