章猛.txt

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

1:
declare
 cursor c_dept4 is select deptno,dname from dept order by deptno;
 v_dept dept%rowtype;
 cursor c_dept1 is select * from dept order by deptno;
 cursor c_dept2 is select * from dept order by deptno;
 v_dept2 c_dept2%rowtype;
 type t_dept3 is record(
         v_d_n dept.deptno%type,
         v_d_m dept.dname%type,
         v_d_l dept.loc%type);
 v_dept3 t_dept3;
 cursor c_dept3 return t_dept3
   is select * from dept order by deptno;
begin
---------------------------------------------------------------
 for c_c_dept4 in c_dept4 loop
    dbms_output.put_line('部门编号为:'||c_c_dept4.deptno||'的部门名字是:'||c_c_dept4.dname);
 end loop;
 
 ---------------------------------------------------------------
                 dbms_output.put_line('************************************************'); 
 open c_dept1;
 loop
   fetch c_dept1 into v_dept; 
   exit when c_dept1%notfound;
   dbms_output.put_line(v_dept.deptno||'的部门名字是'||v_dept.dname);
 end loop;
------------------------------------------------------------ 
         dbms_output.put_line('************************************************');
 open c_dept2;
 loop
   fetch c_dept2 into v_dept2;
   exit when c_dept2%notfound;
   dbms_output.put_line('部门编号为:'||v_dept2.deptno||'    的部门名字是:'||rpad(v_dept2.dname,15)||'其地址位于:'||v_dept2.loc);
  end loop; 
 --------------------------------------------------------------
      dbms_output.put_line('************************************************');
  open c_dept3;
  fetch c_dept3 into v_dept3;
  while c_dept3%found loop
     dbms_output.put_line('部门编号为:'||v_dept3.v_d_n||'    的部门名字是:'||rpad(v_dept3.v_d_m,15)||'其地址位于:'||v_dept3.v_d_l);
     fetch c_dept3 into v_dept3; 
  end loop;
 close c_dept1;
 close c_dept2;  
 close c_dept3; 
end;


2:

 create or replace trigger v_myemp
   before
   insert or update of sal on myemp
   for each row
begin
 if :new.sal > 20000 then
   raise_application_error(-20006,'工资必须小于20000');
 end if;
end;
  

3:

create or replace trigger tz_teacher
 after
 delete on zteacher
 for each row
begin
 delete from zstudent where tid=:old.id;
end;