程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 觸發器的應用(商店打折),觸發器打折

觸發器的應用(商店打折),觸發器打折

編輯:Oracle教程

觸發器的應用(商店打折),觸發器打折


--用戶信息表
create table useres
(
u_id number(13,0) primary key,
username varchar2(50) not null,
password varchar2(20) not null,
name varchar2(50),
sex char(4),
birthday date,
phonenum varchar2(11),
grade int default 0 --積分
);

--會員表
create table vip
(
vid varchar2(20) primary key,
u_id number(13,0),
discount number(3,2), --折扣

constraint fk_vip foreign key(u_id) references useres(u_id)
);

insert into useres values(2016061600001,'jacky','123456','張三','男','23-7月-88','13548643025',default);
insert into useres values(2016061600002,'mary','mary','王紅','女','20-7月-98','13748643025',default);
insert into useres values(2016061600003,'jason','123456','李四','男','23-7月-88','13948743025',default);
commit;

--觸發器
create or replace trigger trigger_vip
after
update of grade
on useres
for each row
when (new.grade >= 10000)
declare
vcount int;
v_vid varchar2(20) := 'V' || to_char(sysdate,'yyyyMMdd') || '00001';
v_discount number(3,2);
begin
select count(*) into vcount from vip;
if vcount > 0 then
v_vid := substr(v_vid,0,length(v_vid) - 5) || lpad(to_char((to_number(substr(v_vid,length(v_vid) - 4,5),'99999') + 1)),5,0);
end if;

case
when :new.grade >= 10000 and :new.grade <= 50000 then
v_discount := 0.95;
when :new.grade >= 60000 and :new.grade <= 100000 then
v_discount := 0.90;
when :new.grade >= 100000 then
v_discount := 0.80;
end case;

select count(*) into vcount from vip where u_id = :old.u_id;

if vcount > 0 then
select vid into v_vid from vip where u_id = :old.u_id;
update vip set discount = v_discount where vid = v_vid;
else
insert into vip values(v_vid,:old.u_id,v_discount);
end if;
end;


--測試
update useres set grade = grade + 50000 where u_id = 2016061600001;
commit;

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved