Oracle 中的游标用法
程序员文章站
2024-02-10 12:59:46
...
循环将游标中的数据提取出来并放置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
上一篇: php读取access表报错
下一篇: Python 使用MySQLdb