6.11.txt

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

1.
declare
  type deptrecord is record
     (deptno dept.deptno%type,
     dname  dept.dname%type,
     loc   dept.loc%type);
     m_deptrecord deptrecord;
     m_deptname  dept.dname%type;
     m_loc  dept.loc%type;
     m_deptno   dept.deptno%type;
   cursor c1
is
  select deptno,dname from dept;

   cursor c2(m_deptno number)
   is
   select deptno,dname from dept where deptno<=m_deptno;
   cursor c3(m_deptno number)
     return deptrecord
   is
     select deptno,dname,loc from dept where deptno<=m_deptno;
   cursor c4(m_deptno number)
   is
     select deptno,dname,loc from dept where deptno<=m_deptno;
   m_deptrec c4%rowtype;
 begin
 open c1;
 loop
     fetch c1 into m_deptno,m_deptname;
     if c1%found then
       dbms_output.put_line('c1:'||m_deptno||' '||m_deptname);
     else
       dbms_output.put_line('c1:'||'已经处理完结果集了');
       exit;
     end if;
     end loop;
    open c2(70);
    loop 
       fetch c2 into m_deptno,m_deptname;
       exit when c2%notfound;
       dbms_output.put_line('c2:'||m_deptno||' '||m_deptname);
     end loop;
    open c3(m_deptno=>70);
    loop
       fetch c3 into m_deptrecord;
       exit when c3%notfound;
       dbms_output.put_line('c3:'||m_deptno||' '||m_deptname||' '||m_loc);
       end loop;
   open c4(70);
   loop
     fetch c4 into m_deptrec;
     exit when c4%notfound;
     dbms_output.put_line('c4:'||m_deptrec.deptno||' '||m_deptrec.dname||' '
     ||m_deptrec.loc);
     end loop;
      close c1;
      close c2;
      close c3;
      close c4;
end;    

2
create or replace trigger 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>20000 then
  raise_application_error (-20001,'薪水不能大于20000');
  end if;
end case;
end;

3
create or replace trigger delete_data
after delete 
on teacher11
for each row
begin
 delete student11 where tno=:old.tno;
end;

4
create or replace trigger addself
after insert 
on temp_1
for each row
when (old.id=null)
begin
  update temp_1 set id =(select (max(id)+1) from temp_1);
end;