java使用游标案例_一个游标小例子

//学生表

create table student(id varchar(10),

name varchar(10),

sex  char(2));

//成绩表

create table course(cid varchar(10),

cname varchar(10),

score number(4,1),

id varchar(10));

insert into course values ('c1','java',90,1);

insert into course values ('c2','oracle',80,1);

insert into course values ('c1','java',85,2);

insert into course values ('c1','oracle',88,2);

insert into course values ('c1','java',75,3);

insert into course values ('c1','java',80,4);

insert into student values ('1','张三','男');

insert into student values ('2','李四','男');

insert into student values ('3','王五','男');

insert into student values ('4','钱子','男');

insert into student values ('5','孙悟空','男');

学号  课程名  成绩

-------------------

如果有学号的学生没有考试，同样显示出学号，但是课程名和学号同样显示出为‘未知’

1

set

serveroutput

on

2

DECLARE

3

CURSOR

c_student

is

select

id

from

student;

4

CURSOR

c_course

is

select

ss.id,NVL(cname,

'

未知

'

),

5

      NVL(score,

0

)

from

course cc,student ss

where

cc.id (

+

)

=

ss.id;

6

7

  v_id_1 student.id

%

type;

8

  v_id_2 student.id

%

type;

9

  v_cname course.cname

%

type;

10

  v_score course.score

%

type;

11

BEGIN

12

13

OPEN

c_course;

14

  LOOP

15

    fetchc_courseintov_id_1,v_cname,v_score;16    EXITWHENc_course%NOTFOUND;--EXIT WHEN 应该和fetch同时写在一起这样才能保证不出错误1718    OPENc_student;19        LOOP20       fetchc_studentintov_id_2;21       EXITWHENc_student%NOTFOUND;--EXIT WHEN 应该和fetch同时写在一起这样才能保证退出有效2223       ifv_id_1=v_id_2then24          DBMS_OUTPUT.put_line(v_id_1||''||v_cname||''||v_score);25       endif;2627    ENDLOOP;28    CLOSEc_student;2930ENDLOOP;31CLOSEc_course;32END;3334

posted on 2006-09-09 16:08 JavaCoffe 阅读(834) 评论(1)  编辑  收藏 所属分类: Oralce&&PL/SQL