Oracle 中的游标用法

苏5669

　　
　　循环将游标中的数据提取出来并放置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