显示游标

1.简单变量接收数据
declare
  cursor employees_cur is select first_name,last_name from employees;
  v_firstname employees.first_name%type;
  v_lastname employees.last_name%type;
begin
  open employees_cur;
  loop
    fetch employees_cur into v_firstname,v_lastname;
    exit when employees_cur%notfound;
    dbms_output.put_line('firstname-->'|| v_firstname || ';lastname-->'||v_lastname);
  end loop;
  close employees_cur;  
end;

2.记录接收数据

declare
  cursor employees_cur is select first_name,last_name from employees;
  v_employees employees_cur%rowtype;
begin
  open employees_cur;
  loop
    fetch employees_cur into v_employees;
    exit when employees_cur%notfound;
    dbms_output.put_line('firstname-->'|| v_employees.first_name || ';lastname-->'||v_employees.last_name);
  end loop;
  close employees_cur;  
end;

declare
  cursor employees_cur is select * from employees;
  v_employees employees%rowtype;
begin
  open employees_cur;
  loop
    fetch employees_cur into v_employees;
    exit when employees_cur%notfound;
    dbms_output.put_line('firstname-->'|| v_employees.first_name || ';lastname-->'||v_employees.last_name);
  end loop;
  close employees_cur;  
end;

3.for循环简化游标
 declare
  cursor employees_cur is select first_name,last_name from employees;  
begin
  for record_name in employees_cur loop
     dbms_output.put_line('firstname-->'|| record_name.first_name || ';lastname-->'||record_name.last_name);  
  end loop;
end;

4.带参数的游标
declare
  cursor employees_cur(deptno number) is select first_name,last_name from employees where department_id=deptno;  
  r employees_cur%rowtype;
begin
  for r in employees_cur(10) loop
    dbms_output.put_line(r.first_name);
  end loop;
  dbms_output.put_line('----------------------');
  for r in employees_cur(20) loop
    dbms_output.put_line(r.first_name);
  end loop;  
  
  dbms_output.put_line('----------------------');
  open employees_cur(30);
  loop
    fetch employees_cur into r;
    exit when employees_cur%notfound;
    dbms_output.put_line(r.first_name);
  end loop;
   close employees_cur; 
end;

5.批量导入数据到record或嵌套表
declare
  type emp_type is table of employees%rowtype index by pls_integer;
  emp_record emp_type;
  emp_row pls_integer;
begin
  select * bulk collect into emp_record from employees;
  dbms_output.put_line('The count is ' || emp_record.count);
  
  emp_row := emp_record.first;
  while (emp_row is not null) loop
    dbms_output.put_line('emp_row:' || emp_record(emp_row).employee_id || ':' || emp_record(emp_row).first_name);
    emp_row := emp_record.next(emp_row);
  end loop;
end;  

6.游标方式
declare
  cursor emp_cur is select * from employees;
  type emp_type is table of employees%rowtype index by pls_integer;
  emp_record emp_type;
  emp_row pls_integer;
  
begin
  open emp_cur;
  fetch emp_cur  bulk collect into emp_record;
  dbms_output.put_line('The count is ' || emp_record.count);
  
  emp_row := emp_record.first;
  while (emp_row is not null) loop
    dbms_output.put_line('emp_row:' || emp_record(emp_row).employee_id || ':' || emp_record(emp_row).first_name);
    emp_row := emp_record.next(emp_row);
  end loop;
  close emp_cur;
end;