杨勇6.11.txt

来自「关于oracle和sql的书籍和ppt教程,非常好,本人珍藏品」· 文本 代码 · 共 56 行

1.
declare 
cursor name_fetch
is
  select dname from dept;  
  tempname dept.dname%type;
begin
  open name_fetch;
  loop
      fetch name_fetch into tempname;
      if name_fetch%found then dbms_output.put_line(tempname);
      else exit;   
      end if;
  end loop;
  close name_fetch;
end;
2.
(1)
declare 
   type myrecord is record(
     y_deptno dept.deptno%type,
     y_dname  dept.dname%type,
     y_loc    dept.loc%type);  
   cursor y_fetch
   return myrecord
   is 
   select dept.deptno,dept.dname,dept.loc from dept;  
   y_record myrecord;  
begin
   open y_fetch;
   loop
      fetch y_fetch into y_record;
      if y_fetch%found then dbms_output.put_line(y_record.y_deptno||'  '||y_record.y_dname||'  '||y_record.y_loc);
      else exit;   
      end if;
   end loop;
   close y_fetch;
end;
(2)

3.
create or replace trigger y_trigger
before insert or update or delete on emp
for each row
begin
  if :new.sal>20000 then raise_application_error(-20001,'工资必须大于20000');
  end if;
end;
4.
create or replace trigger yy
after delete
on teacher
for each row
begin
  delete from student where tid= :old.id;
end;