显示游标

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; 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值