Oracle游标的简单应用

不带参数的游标
DECLARE
  cursor my_cursor
    is select xh from xs;
  v_xh xs.xh%type;
begin
  open my_cursor;
  fetch my_cursor into v_xh;
  dbms_output.put_line(v_xh);
  dbms_output.put_line(my_cursor%rowcount);
exception
  when others then
    dbms_output.put_line(sqlcode||sqlerrm);
end;

带参数的游标
DECLARE
  varId NUMBER;
  varName VARCHAR2(50);
  cursor my_cursor(v_xb xs.xb%type)
    is select xh,xm from xs where xb=xs.xb;
begin
  open my_cursor('男');
  fetch my_cursor into varId,varName;
  dbms_output.put_line('学生编号:'||varId||'学生姓名:'||varName);
  dbms_output.put_line(my_cursor%rowcount);
  close my_cursor;
exception
  when others then
    dbms_output.put_line(sqlcode||sqlerrm);
end;

DECLARE
  v_1 xs%rowtype;
  cursor my_cursor(v_xb xs.xb%type)
    is select * from xs where xb=xs.xb;
begin
  open my_cursor('男');
  fetch my_cursor into v_1;
  dbms_output.put_line('学生编号:'||v_1.xh);
  dbms_output.put_line(my_cursor%rowcount);
  close my_cursor;
exception
  when others then
    dbms_output.put_line(sqlcode||sqlerrm);
end;

游标属性
%isopen
declare
  cursor c_1 is select * from xs;
  v_1 c_1%rowtype;
begin
  if c_1%isopen=false then
    open c_1;
  end if;
  fetch c_1 into v_1;
  dbms_output.put_line(v_1.xh||v_1.xm||v_1.zxf);
  close c_1;
end;

%rowcount
declare
  cursor c_1 is select * from xs;
  v_1 c_1%rowtype;
begin
  open c_1;
  fetch c_1 into v_1;
  dbms_output.put_line(v_1.xh||v_1.xm||v_1.zxf);
  fetch c_1 into v_1;
  dbms_output.put_line('当前游标指向第'||c_1%rowcount);
  close c_1;
end;

游标的遍历
利用while循环检索遍历
declare
  v_xh char(6);
  v_zxf number(2);
  cursor xs_cur is select xh,zxf from xs;
begin 
  open xs_cur;
  fetch xs_cur into v_xh,v_zxf;
  while xs_cur%found
    loop
      dbms_output.put_line('学号:'||v_xh||' '||'总学分:'||v_zxf);
      fetch xs_cur into v_xh,v_zxf;
    end loop;
  close xs_cur;
end;

declare
  cursor xs_cur is select xh,zxf from xs;
  v_1 xs_cur%rowtype;
begin 
  open xs_cur;
  fetch xs_cur into v_1;
  while xs_cur%found
    loop
      dbms_output.put_line('学号:'||v_1.xh||' '||'总学分:'||v_1.zxf);
      fetch xs_cur into v_1;
    end loop;
  close xs_cur;
end;

declare
  v_1 xs%rowtype;
  cursor xs_cur is select * from xs;
begin 
  open xs_cur;
  fetch xs_cur into v_1;
  while xs_cur%found
    loop
      dbms_output.put_line('学号:'||v_1.xh||' '||'总学分:'||v_1.zxf);
      fetch xs_cur into v_1;
    end loop;
  close xs_cur;
end;

输出各个部门的平均工资,若平均工资大于2000,则输出该部门平均工资较高
declare
  cursor c_dept is select deptno,avg(sal) avgsal from scott.emp group by deptno;
  v_dept c_dept%rowtype;
begin
  open c_dept;
  fetch c_dept into v_dept;
  while c_dept%found
    loop
      dbms_output.put_line('部门号:'||v_dept.deptno||' '||'平均工资:'||v_dept.avgsal);
      if(v_dept.avgsal>2000)
        then
          dbms_output.put_line(v_dept.deptno||'平均工资较高');
      end if;
      fetch c_dept into v_dept;
    end loop;
  close c_dept;
end;


利用for循环检索游标
统计并输出各个部门的平均工资
declare
  cursor c_1 is select deptno,avg(sal) avgsal from scott.emp group by deptno;
  v_dept c_1%rowtype;
begin
  for v_dept in c_1
    loop
      dbms_output.put_line('部门号:'||v_dept.deptno||' '||'平均工资:'||v_dept.avgsal);
    end loop;
end;

展开阅读全文

没有更多推荐了,返回首页