Oracle之游标

游标：提供了一种保证每一次只处理结果集中的一行的机制[即逐行的控制]。

游标的使用基础：

隐式游标：系统自动定义游标（当结果集为单行）

显式游标：用户定义的游标（当结果集为多行）

1. 游标使用步骤：

声明->打开 -> 读取 ->关闭游标

2. 游标属性:

%ISOPEN ,%FOUND,%NOTFOUND,%ROWCOUNT

隐式使用游标：在属性前加SQL,如：SQL%FOUND

显式使用游标：在属性前加游标名  如：student_cur%ROWCOUNT

3. 游标的应用：

使用游标可以逐行检索结果集中的记录，还可以更新或删除当前游标行的数据（加上for  update）

（1） 浏览数据

Set serverout on

Declare

V_specialty  student.specialty%TYPE;

V_sname   student.sname%TYPE;

V_dob  students.dob%TYPE;

Cursor student_cur                          --声明

Is

   Select name,dob  from students 

where  Specialty=v_spcialty;

begin

  v_specialty :=’&specialty’;

  open student_cur;                        --打开

  dbms_output.put_line(‘姓名 生日’);

loop

  fetch  student_cur  into  v_sname,v_dob;      --读取

exit when students_cur%notfound;

dbms_output.put_line(v_sname||‘ ’||v_dob);

end loop;

close student_cur;                          --关闭

end;

/

     

（2） 更新数据

Set serverout on

Declare

V_specialty  student.specialty%TYPE;

V_id   student.id%TYPE;

V_dob  students.dob%TYPE;

Cursor student_cur                          --声明

Is

   Select id,dob  from students 

where  Specialty=v_spcialty

for update;

begin

  v_specialty :=’&specialty’;

  open student_cur;                        --打开

  --dbms_output.put_line(‘姓名 生日’);

loop

  fetch  student_cur  into  v_id,v_dob;      --读取

exit when students_cur%notfound;

 case

 when v_id=101 then

update student  set score=1.1*score  

where current  of student_cur;

else

update student  set score=1.01 *score  

where current  of student_cur;

end loop;

close student_cur;                          --关闭

end;

/

（3） 删除数据

Set serverout on

Declare

V_specialty  student.specialty%TYPE;

V_id   student.id%TYPE;

V_dob  students.dob%TYPE;

Cursor student_cur                          --声明

Is

   Select id,dob  from students 

for update;

begin

  open student_cur;                        --打开

  fetch  student_cur  into  v_id,v_dob;      --读取

while students_cur%found

loop

 

if v_id=101 then

delete from student where current  of student_cur;

end if

end loop;

close student_cur;                          --关闭

end;

/

(4)游标for循环：

1.语句格式一：

先在定义部分定义游标，然后在游标for循环中引用该游标

例1：定义游标student_cur ,通过使用for循环，逐渐显示学生姓名和生日并在每个学生姓名前加序号。

Declare 

V_specialty  students.specialty%TYPE;

Cursor   student_cur

Is

Select name,dob  from  students  where  specialty = v_specialty;

Begin

V_specialty : =’&specialty’;

Dbms_output.put_line(‘序号  学生姓名  生日日期’);

for  students_record  in  students_cur  loop

Dbms_output.put_line(students_cur%rowcount||’  ’||students_record.name||’ ’||students_record.dob);

End loop;

End;

/

2.语句格式二：

用子查询替换原来的游标名：

在for循环中直接使用子查询，隐式定义游标，不能显式使用游标属性

Declare 

V_specialty  students.specialty%TYPE;

Cursor   student_cur

Is

Select name,dob  from  students  where  specialty = v_specialty;

Begin

V_specialty : =’&specialty’;

Dbms_output.put_line(‘序号  学生姓名  生日日期’);

for  students_record  in

 (Select name,dob  from students  where  specialty = v_specialty )loop

Dbms_output.put_line(students_cur%rowcount||’  ’||students_record.name||’ ’||students_record.dob);

End loop;

End;

/

隐式游标：

begin

for cur in(select job from emp) loop

dbms_output.put_line(cur.job||'');

end loop;

end;

/

(4)游标的复杂应用：

参数游标（定义使用游标时，带有参数）

1. 使用专业作为游标参数

Declare

V_dob  students.dob%TYPE;

V_sname students.name%TYPE;

Cursor  students_cur(v_specialty students.specialty%TYPE)

Is

Select  name,dob  from students where  specialty = v_specialty;

Begin

Open students_cur(‘计算机’);

Fetch students_cur  into  v_sname,v_dob;

While students_cur%found  loop

Dbms_output.put_line(v_sname||’   ’||v_dob);

Fetch  students_cur  into v_sname,v_dob;        --注意了

End loop;

Close students_cur;

End;

/

2.使用专业,成绩两个作为游标参数

Declare

V_dob  students.dob%TYPE;

V_sname students.name%TYPE;

Cursor  students_cur(v_specialty varchar2，v_score  varchar2)

Is

Select  name,dob  from students where  specialty = v_specialty and

Score>v_score;

Begin

Open students_cur(‘计算机’,80);

Fetch students_cur  into  v_sname,v_dob;

While students_cur%found  loop

Dbms_output.put_line(v_sname||’   ’||v_dob);

Fetch  students_cur  into v_sname,v_dob;

End loop;

Close students_cur;

End;

/

3.游标变量：

游标是静态的，游标变量是动态的

使用游标步骤：

1. 定义游标变量

2. 打开游标变量

3. 读取游标变量

4. 关闭游标变量

如：定义游标变量stucursor,通过使用游标变量stucursor,完成显示学生姓名和出生日期，不使用return子句(即打开游标变量stucursor可以与任何select语句相关联)

Declare 

Type students_cur  is ref  cursor;

studentsor  students_cur;

student_record  students%rowtype;

begin

if not stucursor%isopen  then

open  stucursor  into  students_record;

end if;

Dbms_output.put_line(‘ 学生姓名  生日日期’);

Loop

Fetch stucursor  into  students_record;

exit when  stucursor%notfound;

dbms_output.put_line(students_record.name||’  ’||

students_record.dob);

end loop;

close stucursor;

end;

/

如：定义游标变量stucursor,通过使用游标变量stucursor,完成显示学生姓名和出生日期，使用return子句（即打开游标变量stucursor时， select语句返回结果必须与return子句中students_record指定变量相匹配)

Declare 

Type  students_record  is record(

Stuname varchar2(10),

studob  date);

Sturecord  students_record;

Type students_cur  is ref  cursor return  students_record;

studentsor  students_cur;

begin

if not stucursor%isopen  then

open  stucursor  for select name,dob from students;

end if;

Dbms_output.put_line(‘ 学生姓名  生日日期’);

Loop

Fetch stucursor  into  sturecord;

exit when  stucursor%notfound;

dbms_output.put_line(sturecord.stuname||’  ’||

sturecord.studob);

end loop;

close stucursor;

end;

/

4.游标表达式

游标表达式

在select语句内部使用，构成嵌套游标，其返回类型为ref cursor,使用游标表达式可以处理多表间的关联数据，但只能用于显式游标

Set serverout on

Declare  

V_tname  teachers.name%TYPE;

V_title  teachers.title%TYPE;

V_dname  teachers.department_name%TYPE;

Type cursor_type  is ref  cursor;

Cursor  departments_cur(dept_id  number) is

Select d.departmetn_name,cursor(select name,title  from departments  d  where department_id = d.department_id)

From department d  where  d.deparment_id=dept_id;

Teachers_cur  cursor_type;

Begin

Open departments_cur  into v_dname,teachers_cur;

Exit when departments_cur%notfound;

Dbms_output.put_line(‘系部名称：’||v_dname);

Dbms_output.put_line(‘教师名称  职称’);

Loop

Fetch teachers_cur  into  v_tname,v_title;

Exit when teachers_cur%notfound;

Dbms_output.put_line(v_tname||‘ ’||v_dname);

End loop;

End;

/