维勒斯.txt

关于oracle和sql的书籍和ppt教程,非常好,本人珍藏品

1.1

declare
 type nametable is table of dept.dname%type index by binary_integer;
 nam nametable;
 i int;
cursor dptname
is
select dept.dname from dept;
begin
 open dptname;
 loop
  i := 0;
  fetch dptname into nam(i);
  dbms_output.put_line(nam(i));
  i := i+1;
  exit when dptname%NOTFOUND;
 end loop;
end; 




1.2

declare 
  type drecord is record (deptno dept.deptno%type,dname dept.dname%type,
  loc dept.loc%type);
   tmprecord drecord;
  
  cursor rec_cursor return drecord
  is
  select * from dept;
  cursor row_cursor --return row_cursor%rowtype
  is 
  select * from dept;
  r_cursor row_cursor%rowtype;
  begin
   open rec_cursor;
   loop
     fetch rec_cursor into tmprecord;
     exit when rec_cursor%NOTFOUND;
     dbms_output.put_line('编号'||tmprecord.deptno ||'名称'||tmprecord.dname ||'地址'||tmprecord.loc);
   end loop;  
   
   open row_cursor;
   loop
     fetch row_cursor into r_cursor;
     exit when row_cursor%NOTFOUND;
     dbms_output.put_line('编号'||r_cursor.deptno ||'名称'||r_cursor.dname ||'地址'||r_cursor.loc);
   end loop;
   close rec_cursor;  
   close row_cursor;
   end;

2.

create or replace trigger emp_sal
before insert 
or update
on emp
for each row
begin
  if (:new.sal>20000) then 
   raise_application_error(-20099,'薪水不能大于20000');
  end if; 
end;

3.

create or replace trigger del
after delete
on teacher
for each row
begin
  delete from student where tid= :old.id;
end;
   
4.

create or replace trigger autoid
after insert 
on temp_20 
for each row
--when (old.id=null)
begin  
  --update temp_20 set id =(select (max(id)+1) from temp_20);
  --where :old.id=null;
  dbms_output.put_line('触发器被激活');    
end;