存储过程（二）案例

(1)

Create or Replace Package myPackage 

 is

 begin

type stdInfo is record(stdInfo varchar(30), comment varchar(1));

type myArray is table of stdInfo index by binary_integer;

 

(2) 

Create or Replace Procedure get_comment(commentArray out myPackage.myArray)

 is

rs SYS_REFCURSOR;

record myPackage.stdInfo;

stdId varchar(30);

comment varchar(1);

i number;

 begin

open rs for select stdId,comment from out_school; 

i := 1;

 LOOP

fetch rs into stdId,comment; eixt when rs%NOTFOUND;

record.stdId := stdId;

record.comment := comment;

recommentArray(i) = record;

i := i+1;

 END LOOP;

END get_comment;

(3)

Create or Replace Procedure autocomputer(step in number)

 is 

rsCursor SYS_REFCURSOR;

commentArray myPackage.myArray;

math number;

article number;

language number;

music number;

sport number;

total number;

average number;

stdId varchar(30);

record myPackage.stdInfo;

i number;

 BEGIN

i := 1;

get_comment(commentArray);

    open rsCursor info stdId,math,article,language,music,sport from student t where t.step = step;

 LOOP

fetch rsCursor into stdId,math,article,language,music,sport; exit when rsCurcor%NOTFOUND;

total := math + article + language + music + sport;

for i in 1..commentArray.count LOOP

record : = commentArray(i);

if(stdId = record.stdId)

begin 

 if(record.comment = 'A') then

begin

total :=total + 20;

go to next;-- 跳出For循环

end;

 end if;

end

end if;

end LOOP;

average := total/5;

update student t set t.total = total and t.average = average where t.stdId = stdId;

 end LOOP;

 end;

end autocomputer;