Defined cursor ;
cursor mycursor is select * from employees where employees.salary>tempsal;
Learning point on cursor;
declare
tempsal employees.salary%type;
cursor mycursor is select * from employees where employees.salary>tempsal;
currecord employees%rowtype;
begin
tempsal:=800;
open mycursor;
if mycursor%isopen then
fetch mycursor into cursorrecord;
dbms_output.put_line(to_char(cursorrecord.deptno));
else dbms_output.put_line('游标没有打开');
end if;
end;
currecord is record type(记录类型)
%isopen
if mycursor%isopen then
fetch mycursor into cursorrecord;
dbms_output.put_line(to_char(cursorrecord.deptno));
else dbms_output.put_line('游标没有打开');
end if;
%found
if mycursor%found then
fetch mycursor into cursorrecord;
dbms_output.put_line(to_char(cursorrecord.deptno));
else dbms_output.put_line('游标没有打开');
end if;
%rowcount
dbms_output.put_line(to_char(mycursor%rowcount));
loop exit when mycurcor%notfound;
fetch mycursor into cursorrecord;
end loop;
declare
tempsal employees.salary%type;
cursor mycursor is select * from employees where employees.salary>tempsal;
currecord employees%rowtype;
begin
tempsal:=800;
open mycursor;
loop exit
when mycursor%notfound;
fetch mycursor into currecord;
dbms_output.put_line(to_char(currecord.email)) ;
end loop;
end;
output:
empid=13
empid=10
empid=12
empid=12
corrected SQL code:
declare
temp_salary employee.salary%type;
temp_emp employee%rowtype;
cursor mycursor is
select * from employee where employee.salary>temp_salary;
begin
temp_salary:=2000;
open mycursor;
loop
fetch mycursor into temp_emp;
exit when mycursor%notfound;
dbms_output.put_line('empid='||temp_emp.empid);
end loop;
end;
output:
empid=13
empid=10
empid=12
declare中声明一个可以承载 mycursor的记录型数据
声明一个cursor
此处注意:loop exit之间的循环问题
在存储过程中使用游标
create or replace procedure emp_pro_cur
as
cursor cur is select * from employee where employee.empname like 'd%';
begin
for emp in cur loop --这里for循环,不需要使用fetch移动游标指针,也不需要open cursor&close cursor
dbms_output.put_line(emp.empname);
end loop;
end;
--调用存储过程
call emp_pro_cur();