Oracle 命令操作之 游标

%type类型

SQL> set serveroutput on;
SQL> declare
  2  cursor c_1 is select xh from xs;
  3  v_1 xs.xh%type;
  4  begin
  5  open c_1;
  6  fetch c_1 into v_1;
  7  dbms_output.put_line(v_1||' '||c_1%rowcount);
  8  fetch c_1 into v_1;
  9  dbms_output.put_line(v_1);
 10  end;
 11  /
061101 1
101112

PL/SQL procedure successfully completed

%rowtype类型

SQL> set serveroutput on;
SQL> declare
  2  cursor c_1 is select xh,xm from xs;
  3  v_1 c_1%rowtype;
  4  begin
  5  open c_1;
  6  fetch c_1 into v_1;
  7  dbms_output.put_line(v_1.xm||' '||c_1%rowcount);
  8  fetch c_1 into v_1;
  9  dbms_output.put_line(v_1.xm||' '||c_1%rowcount);
 10  end;
 11  /
王林 1
李明 2

PL/SQL procedure successfully completed

带参数游标

SQL> set serveroutput on;
SQL> declare
  2  cursor c_1(v_xb xs.xb%type) is select xh,xm from xs where xb=v_xb;
  3  v_1 c_1%rowtype;
  4  begin
  5  open c_1('男');
  6  fetch c_1 into v_1;
  7  dbms_output.put_line(v_1.xm||' '||c_1%rowcount);
  8  fetch c_1 into v_1;
  9  dbms_output.put_line(v_1.xm||' '||c_1%rowcount);
 10  end;
 11  /
王林 1
李明 2

PL/SQL procedure successfully completed

while检索

SQL> set serveroutput on;
SQL> declare
  2  cursor c_dept_stat is select deptno,avg(sal) avgsal from scott.emp group by deptno;
  3  v_dept c_dept_stat %rowtype;
  4  begin
  5  open c_dept_stat;
  6  fetch c_dept_stat into v_dept;
  7  while c_dept_stat%found loop
  8  dbms_output.put_line('部门号'||v_dept.deptno||' '||'平均工资为'||trunc(v_dept.avgsal,1));
  9  fetch c_dept_stat into v_dept;
 10  end loop;
 11  close c_dept_stat;
 12  end;
 13  /
部门号30 平均工资为1566.6
部门号20 平均工资为2175
部门号10 平均工资为2916.6

PL/SQL procedure successfully completed

for检索

SQL> set serveroutput on;
SQL> DECLARE
  2      CURSOR c_1 IS SELECT deptno,avg(sal) avgsal FROM scott.emp GROUP BY deptno;
  3      V_dept  c_1%ROWTYPE;
  4  BEGIN
  5      FOR   v_dept    IN c_1
  6  LOOP
  7      DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||v_dept.avgsal);
  8    END LOOP;
  9  END;
 10  /
30 1566.666666666666666666666666666666666667
20 2175
10 2916.666666666666666666666666666666666667

PL/SQL procedure successfully completed