陈平新.txt

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

一,创建四个游标
declare
       cursor emp_c2
       is select ename,job,sal  from emp where sal>3000;
       
       type emp_record_type is record(
            empname emp.ename%type,
            empjob emp.job%type,
            empsal  emp.sal%type);
       emp_record emp_record_type;    
begin
  open emp_c2;
   loop
    fetch emp_c2 into emp_record;
     exit when emp_c2%notfound;
     dbms_output.put_line(emp_record.empname||emp_record.empjob|| emp_record.empsal);
   end loop;  
  close emp_c2;
end;

    1，从dept表打印出所有的部门名字
declare
 cursor dp_nm
 is
 select distinct dname from dept;
 bb dept.DNAME%type;
 
begin 
 open dP_nm;
 loop 
      fetch dp_nm into bb;
      exit when dp_nm%notfound;
      dbms_output.put_line(bb);
      
  end loop;
end;
    2，定义一个返回类型的游标，该游标返回dept表所有的数据 (用两种方式)
   --
declare
 type mybb is record (dno dept.deptno%type,
                      dna dept.dname%type,
                      dlc dept.loc%type );
 cc mybb;
    
  cursor dd
   --return dept%rowtype
   return mybb    
  is 
   select * from dept;
   
  --sss dept%rowtype;
   
   
begin
   open dd;
  loop
   fetch dd into cc;
  --dbms_output.put_line(sss.deptno||sss.dname||sss.loc);
  dbms_output.put_line(cc.dno||cc.dna||cc.dlc);
  exit when dd%notfound ;
  end loop;
close dd;
end;
---------------------------------------

declare 
  cursor de_all
  is select * from dept;
begin 
  for de in de_all loop
  dbms_output.put_line(de.deptno||de.dname||de.loc);
  end loop;
end;


二，建一个触发器，完成一个约束：emp表的薪水sal 不能大于20000
  create or replace trigger myempsal1
before  insert 
     or update
on myno
for each row
begin
  if :new.sal>20000 then
  raise_application_error(-20001,'DAYU 20000 ERROR');
  end if;
end;


     
三，以上午写的student,teacher 表为例子，写一个触发器，当删除teacher表的一行数据库时，在student表删除相应的数据（即：删除老师所教的学生）
  create or replace trigger cpx_d
after delete on zjteacher
for each row
begin
  delete from zjstudent where tid=:old.id;
end;

四，写一个触发器，来完成自增长列值的增加。如表temp（name varchar2(10),age int,id int）,当执行insert into temp(name,age) values('andy',24)时 会自动的插入id 的值，id 是递增的列