611.txt

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

declare  
   CURSOR emp_fetch  ----声明游标
   is 
   select ename from emp ;
   
   cursor emp_fetch2 ----声明游标
   is 
   select ename,sal from emp;
   
   tempcursor emp_fetch2%rowtype;
   
   type myrecord is record(  ---- 声明类型
     tempname emp.ename%type,
     tempjob  emp.job%type,
     tempsal  emp.sal%type
   );
   
   temprecord myrecord;
   
   cursor emp_fetch3(tsal emp.sal%type default 1000)  -----声明游标
   return myrecord
   is 
   select ename,job,sal from emp where sal > tsal;
   
   
   cursor emp_update
   is
   select age from student  for update;
   
  -- myupdate emp_update%rowtype;
   
   tempname emp.ename%type;
   tempsal  emp.sal%type;
   tempage  student.age%type;
begin
    open emp_update;
    
    fetch emp_update into tempage ;
    loop
       update emp set sal = tempage + 1
        where current of emp_update;
       exit when emp_update%notfound;
    end loop;
-------------隐式游标----------------------------
    --insert into emp(empno) values (9876);
   -- dbms_output.put_line(sql%rowcount);
--------------------------------------------------------------
  ---open emp_fetch;  -----打开游标
   /*
     for tempfor in emp_fetch
     loop
        dbms_output.put_line(tempfor.ename);
     end loop;*/
     
     /*
     loop   
             -----提取游标
     fetch emp_fetch into tempname ;
     if emp_fetch%found then
       --fetch emp_fetch into tempname ;
       dbms_output.put_line(tempname);
     else
       dbms_output.put_line('shuju is over');
       exit;
     end if;
     */
     /*
     fetch emp_fetch into tempname ;
     dbms_output.put_line(tempname);
     exit when emp_fetch%notfound;  
     */
     
   --end loop;
 ---------------------------------------------------------------  
 /*
   open emp_fetch2;
  -- fetch emp_fetch2 into tempcursor;
  if(emp_fetch2%isopen) then
  dbms_output.put_line('ooooooooooooooooooo');
  end if;
  
   loop
     --dbms_output.put_line(emp_fetch2%isopen);
     fetch emp_fetch2 into tempcursor;
     dbms_output.put_line(tempcursor.ename||'de xinshui is '||tempcursor.sal);
     exit when emp_fetch2%notfound;
   end loop; */
   
   
   
------------------------------------------------------------------
/*
   open emp_fetch3(2000);
   
   loop
   
       fetch emp_fetch3 into temprecord;
       dbms_output.put_line(temprecord.tempname||'的工作为'||temprecord.tempjob||'的薪水为'||temprecord.tempsal);
       exit when emp_fetch3%notfound;
   end loop;
   close emp_fetch3;
         --------------------------
  dbms_output.put_line('---------------------------------------------');
  open emp_fetch3;
   
   loop
   
       fetch emp_fetch3 into temprecord;
       dbms_output.put_line(temprecord.tempname||'的工作为'||temprecord.tempjob||'的薪水为'||temprecord.tempsal);
       exit when emp_fetch3%notfound;
   end loop;*/
----------------------------------------------------------------------
  
 -- close emp_fetch; -----关闭游标
   --close emp_fetch2;
   --close emp_fetch3;
   close emp_update;
   
end;




declare 
  cursor emp_update
   is
   select id,name from student  for update;
   
   cursoremp emp_update%rowtype;
   --tempage student.id%type;
begin
   open emp_update;

    
    loop
       /*update student set name = tempage + 'oo'
        where current of emp_update;
       commit;*/
       fetch emp_update into cursoremp ;
        exit when emp_update%notfound;
        
        if (cursoremp.id = 1) then
         update student set name = 'jacky'
         where current of emp_update;
         --dbms_output.put_line(tempage);
        end if;
      
    end loop;
       commit;
    
    close emp_update;
end;

create or replace trigger teacherupdate
after
update 
on teacher
for each row
begin
  update student set tid = :new.id
  where tid = :old.id;
end;
/*create or replace trigger student11
before
insert 
on student
begin
     if (to_char(sysdate,'dd') between 1 and 20 ) then
        raise_application_error(-20001,'sorry.....bu neng insert');
     end if;
     dbms_output.put_line(to_char(sysdate,'dd'));
end;*/