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;
显示游标
最新推荐文章于 2024-03-08 10:15:47 发布