数据库课程设计论文

2009级计算机科学与技术2班数据库应用系统课程设计课程论文

create table Sendback2(

num varchar(10) primary key,

Sum varchar(10) references SGoods(SNum), Num1 varchar(10) references Customer(Num1), KNum varchar(10) references Employee(KNum), Qty number not null, tip number not null, datetime date not null )

---------建立存放关系表 create table storel (

CNum varchar(10) references Ctorage(CNum), SNum varchar(10) references SGoods(SNum), Qty number not null, datetime date not null, primary key(CNum,SNum) );

3.2建立触发器

创建购买触发器

create or replace trigger ruku before insert on buy for each row --declare

-- local variables here begin

if:new.qty*:new.sprice-:new.tip>0 then rollback; end if; end ruku;

3.3建立视图

-----管理员查询会员信息的视图

create view 会员信息(会员号,姓名,地址,联系方式)as select HNum,Hname,Hadress,Hphone from Customer where HNum is not null; ----营业员、会员查询商品信息的视图

create view 商品(商品编号,商品名称,商品价格,商品数量,生产日期,保质期,类型)as select SGoods.SNum ,SName ,Sprice ,Qty ,

Dat ,SBZQ ,SLeiX from SGoods,Storel where SGoods.Snum=Storel.Snum; ------管理员查看供应商信息的视图

- 22-

2009级计算机科学与技术2班数据库应用系统课程设计课程论文

create view 供应商(供应商编号,姓名,地址,联系方式) as select * from psupplier;

--------超市经理查询员工信息的视图

create view 员工信息(员工号,姓名,年龄,性别,职位)as select KNum,Kname,Kage,Csex,Kpositon from ------管理员、采购员查看库存的视图

create view 库存(库存编号,仓库名称,商品编号,商品名称,商品数量,库存最大容量) as select ctorage.cnum,ctorage.cname,storel.snum,sgoods.sname,qty,maxnum from storel,ctorage,sgoods where storel.cnum=ctorage.cnum and sgoods.snum=storel.snum;

Employee where Kpositon!='超市经理'; --------查询顾客退货的视图

create view 顾客退货(退货编号,商品编号,顾客编号,员工编号, 商品名称,退货数量,退货金额,退货日期) as select num,snum,num1,knum,sname,qty,tip,datetime from sendback2,sgoods where sgoods.snum=sendback2.sum; -------查询采购退货的视图

create view 采购退货(退货编号,商品编号,供应商编号,员工编号, 商品名称,退货数量,退货金额,退货日期) as select num,sgoods.snum,pum,knum,sname,qty,tip,datetime from sendback1,sgoods where

sgoods.snum=sendback1.snum; -------查询库存状态的视图 create view 存放(仓库编号,仓库名称,商品编号,商品名称,入库日期,出库日期,库存数量) as select ctorage.cnum,cname,storel.snum,sname,storel.datetime,buy.datetime,storel.qty from ctorage,storel,sgoods,buy where ctorage.cnum=storel.cnum and sgoods.snum=storel.snum and buy.snum=storel.snum;

3.4建立查询存储过程

-------定义查询会员信息的存储过程

create or replace procedure H_select is v_Num Customer.Num1%type; v_Num1 Customer.Hnum%type; v_Name Customer.Hname%type; v_adress Customer.Hadress%type; v_phone Customer.Hphone%type;

cursor CL is select Num1,HNum,Hname,Hadress,Hphone from Customer where HNum is not null; begin

dbms_output.put_line('顾客号'||' '||'会员号'||' '||'姓名'||' '||'地址'||' '||'联系方式');

- 23-

2009级计算机科学与技术2班数据库应用系统课程设计课程论文

open CL; loop

fetch CL into v_Num,v_Num1,v_Name,v_adress,v_phone; exit when CL%notfound;

dbms_output.put_line(v_Num||' '||v_Num1||' '||v_Name||' '||v_adress||' '||v_phone); end loop; close CL; end;

--------查询员工信息的存储过程

create or replace procedure K_select is v_Num Employee.KNum%type; v_Name Employee.Kname%type; v_age Employee.Kage%type; v_sex Employee.Csex%type;

v_position Employee.Kpositon%type;

cursor CL is select KNum,Kname,Kage,Csex,Kpositon from Employee where

Kpositon!='超市经理'; begin

dbms_output.put_line('员工号'||' '||'员工姓名'||' '||'年龄'||' '||'性别'||' '||'职位'); open CL; loop

fetch CL into v_Num,v_Name,v_age,v_sex,v_position; exit when CL%notfound;

dbms_output.put_line(v_Num||' '||v_Name||' '||v_age||' '||v_sex||' '||v_position); end loop; close CL; end;

------查询供应商信息的存储过程

create or replace procedure P_select is

v_num PSupplier.Pnum%type; v_name PSupplier.Pname%type; v_adress PSupplier.Padress%type; v_phone PSupplier.Pphone%type;

cursor CL is select PNum,Pname,Padress,Pphone from PSupplier; begin

dbms_output.put_line('供应商编号'||' '||'姓名 '||' '||'地址'||' '||'联系方式'); open CL; loop

- 24-

2009级计算机科学与技术2班数据库应用系统课程设计课程论文

fetch CL into v_num,v_name,v_adress,v_phone ; exit when CL%notfound;

dbms_output.put_line(v_num||' '||v_name||' '||v_adress||' '||v_phone); end loop ; close CL; end P_select;

3.5建立更新存储过程 --------销售修改库存

create or replace procedure sale_update(Amount in out buy.qty%type, snum1 in out buy.snum%type,datetime storel.datetime%type) is --declare --tip1 number; --tip2 number;

qty1 storel.qty%type; begin

select qty into qty1 from storel where storel.snum=snum1; if qty1 is null then rollback; return; end if;

if qty1

update storel set qty=qty1-Amount,datetime=datetime where snum=snum1; commit; end sale;

----入库修改库存

create or replace procedure into_update(Amount in out storel.qty%type, snum1 in out storel.snum%type,datetime storel.datetime%type) is --declare --tip1 number; --tip2 number;

qty1 storel.qty%type; begin

select qty into qty1 from storel where storel.snum=snum1; if qty1 is null then rollback; return; end if;

if qty1

- 25-