set serveriutput on declare v_ename emp.ename%type; v_salary emp.salary%type; cursor c_emp is select ename,salary from emp; begin open c_emp; fetch c_emp into v_ename,v_salary; dbms_output.put_line(''salary of employee''|| v_ename ||''is''|| v_salary); fetch c_emp into v_ename,v_salary; dbms_output.put_line(''salary of employee''|| v_ename ||''is''|| v_salary); fetch c_emp into v_ename,v_salary; dbms_output.put_line(''salary of employee''|| v_ename ||''is''|| v_salary); close c_emp; end
set serveriutput on declare v_ename emp.ename%type; v_salary emp.salary%type; cursor c_emp is select ename,salary from emp; begin open c_emp; loop fetch c_emp into v_ename,v_salary; exit when c_emp%notfound; dbms_output.put_line(''salary of employee''|| v_ename ||''is''|| v_salary); end
set serveriutput on declare r_emp emp%rowtype; cursor c_emp is select * from emp; begin open c_emp; loop fetch c_emp into r_emp; exit when c_emp%notfound; dbms_out.put.put_line(''salary of employee''||r_emp.ename||''is''|| r_emp.salary); end loop; close c_emp; end;
%rowtype也可以用游标名来定义,这样的话就必须要首先声明游标:
set serveriutput on declare cursor c_emp is select ename,salary from emp; r_emp c_emp%rowtype; begin open c_emp; loop fetch c_emp into r_emp; exit when c_emp%notfound; dbms_out.put.put_line(''salary of employee''||r_emp.ename||''is''|| r_emp.salary); end loop; close c_emp; end;
cursor c_dept is select * from dept order by deptno; cursor c_emp (p_dept varachar2) is select ename,salary from emp where deptno=p_dept order by ename r_dept dept%rowtype; v_ename emp.ename%type; v_salary emp.salary%type; v_tot_salary emp.salary%type;
begin
open c_dept; loop fetch c_dept into r_dept; exit when c_dept%notfound; dbms_output.put_line(''department:''|| r_dept.deptno||''-''||r_dept.dname); v_tot_salary:=0; open c_emp(r_dept.deptno); loop fetch c_emp into v_ename,v_salary; exit when c_emp%notfound; dbms_output.put_line(''name:''|| v_ename||'' salary:''||v_salary); v_tot_salary:=v_tot_salary+v_salary; end loop; close c_emp; dbms_output.put_line(''toltal salary for dept:''|| v_tot_salary); end loop; close c_dept; end;
for record_name in (corsor_name[(parameter[,parameter]...)] | (query_difinition) loop statements end loop;
下面我们用for循环重写上面的例子:
decalre
cursor c_dept is select deptno,dname from dept order by deptno; cursor c_emp (p_dept varachar2) is select ename,salary from emp where deptno=p_dept order by ename
v_tot_salary emp.salary%type;
begin
for r_dept in c_dept loop dbms_output.put_line(''department:''|| r_dept.deptno||''-''||r_dept.dname); v_tot_salary:=0; for r_emp in c_emp(r_dept.deptno) loop dbms_output.put_line(''name:''|| v_ename||'' salary:''||v_salary); v_tot_salary:=v_tot_salary+v_salary; end loop; dbms_output.put_line(''toltal salary for dept:''|| v_tot_salary); end loop;
end;
在游标for循环中使用查询
在游标for循环中可以定义查询,由于没有显式声明所以游标没有名字,记录名通过游标查询来定义。
decalre
v_tot_salary emp.salary%type;
begin
for r_dept in (select deptno,dname from dept order by deptno) loop dbms_output.put_line(''department:''|| r_dept.deptno||''-''||r_dept.dname); v_tot_salary:=0; for r_emp in (select ename,salary from emp where deptno=p_dept order by ename) loop dbms_output.put_line(''name:''|| v_ename||'' salary:''||v_salary); v_tot_salary:=v_tot_salary+v_salary; end loop; dbms_output.put_line(''toltal salary for dept:''|| v_tot_salary); end loop;
end;
游标中的子查询
语法如下:
cursor c1 is select * from emp where deptno not in (select deptno from dept where dname!=''accounting'');
cursor c1 is select empno,salary from emp where comm is null for update of comm;
v_comm number(10,2);
begin
for r1 in c1 loop
if r1.salary<500 then v_comm:=r1.salary*0.25; elseif r1.salary<1000 then v_comm:=r1.salary*0.20; elseif r1.salary<3000 then v_comm:=r1.salary*0.15; else v_comm:=r1.salary*0.12; end if;