Oracle数据库销售系统.docx

1、Oracle数据库销售系统 销售系统 Oracle数据库 一、创建表（要求各个表有约束，能进行表和表连接）1、鲜花信息表 flower鲜花编号 fno number(6) primary key鲜花名称 fname varchar2(30) not null鲜花颜色 fcolor varchar2(10)鲜花单价 price float鲜花花语 fsay varchar2(30) create table flower( fno number(6) primary key, fname varchar2(30) not null, fcolor varchar2(10), price floa

2、t,fsay varchar2(30);2、顾客信息表 customer顾客编号 cuno number(5) primary key顾客名cuname varchar2(30) not null顾客类型 custatus varchar2(10)顾客住址 address varchar2(30) create table customer( cuno number(5) primary key, cuname varchar2(30) not null, custatus varchar2(10), address varchar2(30);3、销售信息表 sale鲜花编号 fno numb

3、er(6)顾客编号 cuno number(5)销售日期 saledate date是否有折扣 discount varchar2(10)销售价格 saleprice float create table sale( fno number(6), cuno number(5), saledate date, discount varchar2(10), saleprice float);增加约束（外键约束、检查约束）alter table sale add constraint fk_flower_sale_fno foreign key(fno) references flower (fno

4、);alter table sale add constraint fk_customer_sale_cuno foreign key(cuno) references customer(cuno);alter table sale add constraint ck_sale_discount check(discount=yes or discount=no);二、插入数据 (仅插入部分数据，其他数据见用替代变量插入数据)1.向flower表插入数据insert into flower values(000001,玫瑰,red,5, 我爱你);insert into flower valu

5、es(000002,百合,white, 5,健康快乐);insert into flower values(000003,康乃馨,pink, 4,无私的爱);insert into flower values(000004,满天星,blue, 3,甘愿做你的配角);insert into flower values(000005,雏菊,yellow, 6,隐藏在心中的爱);insert into flower values(000006,忘忧草,green, 4,忘记烦恼，快乐相随);2.向customer表插入数据insert into customer values(10001,大头儿子,

6、advanced,北京);insert into customer values(10002,喜洋洋,vip,天津);insert into customer values(10003,大耳朵图图,general,上海);insert into customer values(10004,蜡笔小新,advanced,韩国);insert into customer values(10005,樱桃小丸子,advanced,旧金山);insert into customer values(10006,李媛媛,general,中北大学); 3.向sale表插入数据insert into sale v

7、alues(000002,10001,02-10月-14,no,159);insert into sale values(000001,10002,09-9月-14,yes,239);insert into sale values(000003,10003,11-11月-14,no,189);insert into sale values(000002,10004,12-10月-14,yes,229);insert into sale values(000001,10005,20-8月-14,yes,199);insert into sale values(000003,10006,11-11

8、月-14,no,209);insert into sale values(000004,10002,20-11月-14,yes,339);三、建立索引1、sale表索引create index sale on sale(cno,cuno,saledate,saleprice);因为sale表时常要被使用select查询，所以为此建立索引2、flower表索引当花的种类繁多时，flower表被经常查询，为了方便起见则需建立索引create index flower on flower(fno, fname, fcolor, price, fsay);3、customer表索引 当拥有大量顾客时，

9、查询customer表会十分繁琐，建立索引会使查询简单快捷create index customer on customer(cuno, cuname, custatus,address);查看索引select index_type,index_name,table_name,uniqueness from user_indexes;四、建立视图(会根据各种需求建立视图，包括简单查询语句、表连接语句、子查询语句等建立视图)1、为使能查询已销售的鲜花信息固建此视图create view saledflowerinfo as select f.fno,f.fname,f.fcolor,s.sale

10、price from flower f,sale s where o=s.fno;2、为方便查看有折扣的客户的身份级别固建此视图create view customerdisinfo as select cu.cuno,cu.cuname,cu.custatus,s.discount from customer cu,sale s where cu.cuno=s.cuno and cu.cuno in (select cuno from sale where discount=yes);3、为了汇总订单，建立各个订单汇总视图create view dd as select f.fno,f.fn

11、ame,f.fcolor,s.saleprice from flower f,sale s where f.fno=s.fno;五、利用替代变量进行数据的插入1.flower表accept v_col1 prompt请输入鲜花编号：accept v_col2 prompt请输入鲜花名称：accept v_col3 prompt请输入鲜花颜色：accept v_col4 prompt请输入鲜花标价：accept v_col5 prompt请输入鲜花花语：insert into flower values(&v_col1,&v_col2,&v_col3,&v_col4, &v_col5);(存入D

12、：a.sql)执行语句：start d:a.sql2.customer表accept v_col1 prompt请输入顾客编号：accept v_col2 prompt请输入顾客姓名：accept v_col3 prompt请输入顾客类型：accept v_col4 prompt请输入顾客地址：insert into customer values(&v_col1,&v_col2,&v_col3,&v_col4);(存入D：b.sql)执行语句：start d:b.sql3、sale表accept v_col1 prompt请输入鲜花编号：accept v_col2 prompt请输入顾客编号

13、：accept v_col3 prompt请输入销售日期：accept v_col4 prompt是否有折扣：accept v_col4 prompt请输入销售价格：insert into sale values(&v_col1,&v_col2,&v_col3,&v_col4, &v_col5);(存入D：c.sql)执行语句： start d:c.sql六、.建立包(会利用游标、函数、包编写过程进行数据的查询)-包头create or replace package pk is procedure saleinfo(pfno number,pcuno number);function fun

14、_price(pcuno number) return number;end;-包体create or replace package body pk isprocedure saleinfo(pfno number,pcuno number) isv_fno number;v_cuno number;v_saledate date;v_saleprice float;-游标cursor c1(pfno number,pcuno number) is select fno,cuno,saledate,saleprice from sale where pfno=fno and pcuno=cu

15、no;beginopen c1(pfno,pcuno);loopfetch c1 into v_fno,v_cuno,v_saledate,v_saleprice;if c1%found thendbms_output.put_line(鲜花编号：|v_fno|客户编号：|v_cuno|销售时间：|v_saledate|销售价格：|v_saleprice);elsedbms_output.put_line(查询结束);exit;end if;end loop;close c1;end;-函数function fun_price(pcuno number) return number is su

16、mprice number(8,3);beginselect sum(saleprice) into sumprice from sale where pcuno=cuno;return sumprice;end;end;执行过程set serverout onexec pk.saleinfo(000002,10001);var r number;call pk.fun_price(10001)into:r;print r;七.触发器 create or replace trigger flower_trg_rai after insert on flower for each row dec

17、lare begin dbms_output.put_line(fno: | :new.fno); dbms_output.put_line(fname: | :new.fname); dbms_output.put_line(fcolor: | :new.fcolor); dbms_output.put_line(price|:new.price); dbms_output.put_line(fsay|:new.fsay); dbms_output.put_line(触发器已执行); end; 执行语句 insert into flower(fno,fname,fcolor,price) values(000012,郁金香,blue,15);