章猛4.txt

来自「关于oracle和sql的书籍和ppt教程,非常好,本人珍藏品」· 文本 代码 · 共 41 行

create table variety (vid number,vname varchar2(20));
alter table variety add constraint pk_variety_id primary key(vid);



create table goodsz (gid number,gname varchar2(20),vid number);
alter table goodsz add constraint pk_goods_gid primary key (gid);
alter table goodsz add constraint fk_goods_vid_variety_vid foreign key(vid) references      variety(vid) on delete set null;
alter table goodsz add (price number);
alter table goodsz modify (price number not null);
alter table goodsz drop (price);
alter table goodsz add (area varchar2(20));
alter table goodsz add constraint ck_goodsz_area check (area in ('内地','香港','台湾'));

create or replace trigger aaaaa
 before 
 update of comm
 on emp
 for each row
declare
    procedure indept(qq number)
    is
      cursor aa
      is select comm from emp where empno=qq; 
      aaa emp.comm%type;
    begin
      open aa;
      loop
      fetch aa into aaa;
        exit when aa%notfound;
     -- if aaa>200000 then
      --raise_application_error(-20003,'员工工资不能高于二万！');
      --end if;
      end loop;
      close aa;   
    end;
begin
  if :new.comm>20000 then
 indept(:old.empno);
 end if;
end;