黄松平.txt

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

declare
   v_dname dept.dname%type;
   dept_record dept%rowtype;
   cursor c1    --声明c1
   is 
   select dname from dept;
   
   cursor c2    --声明c2
   return dept%rowtype
   is select distinct * from dept;
   v_dept c2%rowtype;
   
   cursor c3   --声明c3
   return dept%rowtype
   is 
   select * from dept;
begin
   open c1;
   loop
     fetch c1 into v_dname;
     dbms_output.put_line(v_dname);
     exit when c1%notfound;
   end loop;
   close c1;
   open c2;
   loop 
   fetch c2 into v_dept;
   dbms_output.put_line(v_dept.deptno||' '||v_dept.dname||' '||v_dept.loc);
   exit when c2%notfound;
   end loop;
   close c2;
   open c3;
   loop
      fetch c3 into dept_record;
      dbms_output.put_line(dept_record.deptno||'  '||dept_record.dname||'  '||dept_record.loc);
      exit when c3%notfound;
   end loop;
   close c3;
end;

2
create or replace trigger tri_emp_sal
before 
update of sal
or insert
on emp
for each row
begin
 case
    when UPDATING ('sal')  then
    if :new.sal>20000 then
    RAISE_APPLICATION_ERROR(-20001,'工资不能高于20000');
    end if;
    when inserting then 
    if :new.sal>20000then
    RAISE_APPLICATION_ERROR(-20002,'工资不能高于20000');
    end if;
    END CASE;
    
end;
3
create or replace trigger tri_delete_tid
after 
delete 
on teacher
for each row
begin
delete from student where lid=:old.tid;
end;
4
create or replace trigger tri_sel_insert
after 
insert 
on temp
for each row
begin
update temp set id=1+(select max(id) from temp)where :new.id is null;
end;