PL/SQL学习笔记二

-- Created on 2009-9-16 by ADMINISTRATOR


/*当从成绩表里提取数据时,把成绩少于60的学生姓名,
课程,成绩插入到不及格学生表,大于60小于80的插入良好学生表,
大于80的插入优秀学生表.*/

/*declare
i integer :=1;
v_tt integer :=1;
sum integer ;
cursor v_empcur is select * from student ;
tt v_empcur%rowtype;
begin
open v_empcur;--打开游标

loop
FETCH v_empcur INTO tt;
Dbms_Output.put_line(tt.stuid);

exit when(v_empcur%notfound);
end loop;
end;*/

declare
i integer :=1;
--close v_empcur;--关闭游标
--不能用 × 。因为两个表内有重复的字段，查询时冲突
--cursor cur is select s.stuid,s.name,t.course,t.score from student s,score t where s.stuid=t.stuid;
cursor cur is select s.*,t.course,t.score from student s,score t where s.stuid=t.stuid;
begin

FOR v_tt IN cur LOOP
if(v_tt.score =33) then DBMS_OUTPUT.PUT_LINE(v_tt.name);
end if;
END LOOP;


end;


/* BEGIN
OPEN emp_cursor(10, 'CLERK');
LOOP
FETCH emp_cursor INTO v_empno,v_ename;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename);
END LOOP;
END;
*/