Oracle 中的游标用法

 

循环将游标中的数据提取出来并放置2个变量中输出

SQL> declare
  2    cursor dept_cur is select d.deptno,d.dname from scott.dept d;
  3    dno scott.dept.deptno%type;
  4    dnm scott.dept.dname%type;
  5  begin
  6    open dept_cur;
  7    loop
  8      fetch dept_cur into dno,dnm;
  9      exit when dept_cur%notfound;
 10      dbms_output.put_line(dno||' '||dnm);
 11    end loop;
 12    close dept_cur;
 13  end;
 14  /
 
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS

循环将游标中的数据提取出来并放置 PL/SQL 记录中输出

declare
  cursor dept_cur is select d.deptno,d.dname from scott.dept d;
  type dept_record_type is record(
    dno scott.dept.deptno%type,
    dnm scott.dept.dname%type
  );
  dept_record dept_record_type;
begin
  open dept_cur;
  loop
    fetch dept_cur into dept_record;
    exit when dept_cur%notfound;
    dbms_output.put_line(dept_record.dno||' '||dept_record.dnm);
  end loop;
  close dept_cur;
end;
 
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS

 

将游标中的所有数据一次性提取出来并放置 PL/SQL 记录表中循环输出

SQL> declare
  2    cursor dept_cur is select d.deptno,d.dname from scott.dept d;
  3    type dept_record_type is record(
  4      dno scott.dept.deptno%type,
  5      dnm scott.dept.dname%type
  6    );
  7    dept_record dept_record_type;
  8  begin
  9    open dept_cur;
 10    loop
 11      fetch dept_cur into dept_record;
 12      exit when dept_cur%notfound;
 13      dbms_output.put_line(dept_record.dno||' '||dept_record.dnm);
 14    end loop;
 15    close dept_cur;
 16  end;
 17  /
 
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS

 

每次从游标中提取指定行记录放置 PL/SQL 记录表中并循环输出

SQL> declare
  2    cursor my_cur is select e.empno,e.ename from scott.emp e;
  3    type emp_record_type is record(
  4      eno scott.emp.empno%type,
  5      enm scott.emp.ename%type
  6    );
  7    type emp_table_type is table of emp_record_type;
  8    emp_table emp_table_type;
  9    r int:=5;
 10  begin
 11    open my_cur;
 12    loop
 13      fetch my_cur bulk collect into emp_table limit r;    --批量处理不需要初始化变量大小 
 14      for i in 1..emp_table.count loop
 15        dbms_output.put_line(emp_table(i).eno||' '||emp_table(i).enm);
 16      end loop;
 17      exit when my_cur%notfound;
 18    end loop;
 19    close my_cur;
 20  end;
 21  /
 
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER

 

将游标作为数据类型赋值后循环输出

SQL> declare
  2    cursor my_cur is select e.empno,e.ename from scott.emp e;
  3    type emp_table_type is table of my_cur%rowtype;
  4    emp_table emp_table_type;
  5    r int:=5;
  6  begin
  7    open my_cur;
  8    loop
  9      fetch my_cur bulk collect into emp_table limit r;
 10      for i in 1..emp_table.count loop
 11        dbms_output.put_line(emp_table(i).empno||' '||emp_table(i).ename);
 12      end loop;
 13      exit when my_cur%notfound;
 14    end loop;
 15    close my_cur;
 16  end;
 17  /
 
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER

 

 

带参游标的使用

SQL> declare
  2    cursor my_cur(eno int) is select e.empno,e.ename from scott.emp e where empno=eno;
  3    emp_table my_cur%rowtype;
  4  begin
  5    open my_cur(7788);
  6    fetch my_cur into emp_table;
  7    dbms_output.put_line(emp_table.empno||' '||emp_table.ename);
  8    close my_cur;
  9  end;
 10  /
 
7788 SCOTT

SQL> declare
  2    cursor my_cur(eno int defalut 7788) is select e.empno,e.ename from scott.emp e where empno=eno;
  3    emp_table my_cur%rowtype;
  4  begin
  5    open my_cur;
  6    fetch my_cur into emp_table;
  7    dbms_output.put_line(emp_table.empno||' '||emp_table.ename);
  8    close my_cur;
  9  end;
 10  /
 
7788 SCOTT

 

 

使用游标更新数据

declare
  cursor my_cur is select e.sal from emp e for update;
  v_oldsal emp.sal%type;
begin
  open my_cur;
  loop
    fetch my_cur into v_oldsal;
    exit when my_cur%notfound;
    if v_oldsal < 2000 then
    null;
      update emp set sal=sal+100 where current of my_cur;
    end if;
  end loop;
  close my_cur;
end;

 

 

使用游标删除数据

declare
  cursor my_cur is select deptno from emp2 for update;
  dno dept.deptno%type;
begin
  open my_cur;
  loop
    fetch my_cur into dno;
    exit when my_cur%notfound;
    if dno = 20 then
      delete from emp2 where current of my_cur;
    end if;
  end loop;
  close my_cur;
end;

 

 

使用游标 FOR 循环

SQL> set serveroutput on
SQL> declare
  2    cursor my_cur is select e.empno,e.ename from scott.emp e;
  3    myemp my_cur%rowtype;
  4  begin
  5    for myemp in my_cur loop
  6      dbms_output.put_line('第'||my_cur%rowcount||'个员工：'||myemp.ename);
  7    end loop;
  8  end;
  9  /
 
第1个员工：SMITH
第2个员工：ALLEN
第3个员工：WARD
第4个员工：JONES
第5个员工：MARTIN
第6个员工：BLAKE
第7个员工：CLARK
第8个员工：SCOTT
第9个员工：KING
第10个员工：TURNER
第11个员工：ADAMS
第12个员工：JAMES
第13个员工：FORD
第14个员工：MILLER
 
PL/SQL procedure successfully completed

 

在 FOR 循环中使用子查询

SQL> declare
  2  begin
  3    for emp_record in (select ename,sal from emp) loop
  4      dbms_output.put_line(emp_record.ename);
  5    end loop;
  6  end;
  7  /
 
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
 
PL/SQL procedure successfully completed

使用游标变量

 

declare
  type ref_type_myc is ref cursor return emp%rowtype;
  my_cur ref_type_myc;
  type emp_table_type is table of emp%rowtype;
  emp_table emp_table_type;
begin
  open my_cur for select * from emp;
  fetch my_cur bulk collect into emp_table;
  for i in 1..emp_table.count loop
    dbms_output.put_line(emp_table(i).ename);
  end loop;
end;

嵌套游标的使用

 

SQL> set serverout on;
SQL> declare
  2    type my_refcur_type is ref cursor;
  3    my_refcur my_refcur_type;
  4    cursor my_cur is
  5      select d.dname,cursor(select ename,sal from emp where deptno=d.deptno) from dept d;
  6    dnm dept.dname%type;
  7    enm emp.ename%type;
  8    sal emp.sal%type;
  9  begin
 10    open my_cur;
 11    loop
 12      fetch my_cur into dnm,my_refcur;
 13      exit when my_cur%notfound;
 14      dbms_output.put_line(dnm);
 15      loop
 16        fetch my_refcur into enm,sal;
 17        exit when my_refcur%notfound;
 18        dbms_output.put_line(enm||' '||sal);
 19      end loop;
 20    end loop;
 21  end;
 22  /
 
ACCOUNTING
CLARK 2695
KING 5500
MILLER 1530
RESEARCH
SMITH 900
JONES 2975
SCOTT 3000
ADAMS 1200
FORD 3000
SALES
ALLEN 1860
WARD 1475
MARTIN 1475
BLAKE 3135
TURNER 1750
JAMES 1145
OPERATIONS
 
PL/SQL procedure successfully completed