612.txt

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

/*create view stutea
as
select s.id sid,s.name sname,t.id tid,t.name tname
from student s,teacher t
where s.tid = t.id

-------------------------------------------------------------
create or replace trigger stuteatrigger
instead of insert
on stutea
for each row
declare
  shu number
begin
---------------------------------------------------------------------
  select count(*) into shu from teacher where id = :new.tid;
  
  if shu = 0 then
      insert into teacher values(:new.tid,:new.tname);
  end if;
--------------------------------------------------------------------
  select count(*) into shu from student where id = :new.sid;
  
  if shu = 0 then
      insert into student values(:new.sid,:new.sname,:new.tid);
  end if;
end;*/

/*
create view teaview 
as
select id ,name from teacher;*/

create or replace trigger teaviewtrigger
instead of insert
on teaview
for each row
declare
  shu  number;
  zuida number;
begin
  select max(suoyin) into zuida from teacher ;
  select count(*) into shu from teacher where id = :new.id;
  if shu = 0 then
     insert into teacher values(:new.id,:new.name,zuida + 1);
  end if;
end;



/*create or replace package oracle528
is
  procedure shuchu(id int);
  message varchar2(100);
end oracle528;*/.
/*
create or replace procedure shuchu(id int)
  is
    cursor curtea(tempid int)
    is
    select id ,name from teacher where id > tempid;
  
    mydata curtea%rowtype; 
  begin
    open curtea(id);
    
    loop
       fetch curtea into mydata ;
       dbms_output.put_line('老师的工号：'||mydata.id||' 老师的名字：'||mydata.name);
       exit when curtea%notfound;
      /* if curtea%found then
          dbms_output.put_line('老师的工号：'||mydata.id||' 老师的名字：'||mydata.name);
        else
          exit;
       end if;
    end loop;
    
    close curtea;
  end shuchu;*/

create or replace package body oracle528
is
  cursor curtea(tempid int)
  is
  select id ,name from teacher where id > tempid;
  
  mydata curtea%rowtype; 
  
  k int;
  procedure shuchu(id int)
  is
    
  begin
    open curtea(id);
    
    loop
      /* fetch curtea into mydata ;
       dbms_output.put_line('老师的工号：'||mydata.id||' 老师的名字：'||mydata.name);
       exit when curtea%notfound;*/
       fetch curtea into mydata ;
      if curtea%found then
          dbms_output.put_line('老师的工号：'||mydata.id||' 老师的名字：'||mydata.name);
        else
          exit;
       end if;
    end loop;
    
    close curtea;
  end shuchu;
begin
  message := 'dsafldsafdsafdsa';
  k := 5;
end oracle528;
/*create view stutea
as
select s.id sid,s.name sname,t.id tid,t.name tname
from student s,teacher t
where s.tid = t.id

-------------------------------------------------------------
create or replace trigger stuteatrigger
instead of insert
on stutea
for each row
declare
  shu number
begin
---------------------------------------------------------------------
  select count(*) into shu from teacher where id = :new.tid;
  
  if shu = 0 then
      insert into teacher values(:new.tid,:new.tname);
  end if;
--------------------------------------------------------------------
  select count(*) into shu from student where id = :new.sid;
  
  if shu = 0 then
      insert into student values(:new.sid,:new.sname,:new.tid);
  end if;
end;*/

/*
create view teaview 
as
select id ,name from teacher;

create or replace trigger teaviewtrigger
instead of insert
on teaview
for each row
declare
  shu  number;
  zuida number;
begin
  select max(suoyin) into zuida from teacher ;
  select count(*) into shu from teacher where id = :new.id;
  if shu = 0 then
     insert into teacher values(:new.id,:new.name,zuida + 1);
  end if;
end;*/