rem ****************************************************
rem ** 附录A 仓库管理数据库脚本
rem ** 脚本createTable1407.txt清单
rem **
rem ** 功能:创建仓库管理数据库
rem ** 作者:
rem ** 学号:
rem ** 首次创建时间:2010年4月14日
rem ** 最后修改时间:2010年5月27日
rem ****************************************************
rem *****************************************************
rem **删除表
rem *****************************************************
declare
mycount int;
sqlString1 varchar(100);
begin
mycount :=0;
sqlString1 :='drop table Amount1407';
select count(*) into mycount from user_tables where Table_Name='AMOUNT1407';
if mycount >0 then
EXECUTE IMMEDIATE sqlString1 ;
commit;
end if;
sqlString1 :='drop table Return1407';
select count(*) into mycount from user_tables where Table_Name='RETURN1407';
if mycount >0 then
EXECUTE IMMEDIATE sqlString1 ;
commit;
end if;
sqlString1 :='drop table OutStore1407';
select count(*) into mycount from user_tables where Table_Name='OUTSTORE1407';
if mycount >0 then
EXECUTE IMMEDIATE sqlString1 ;
commit;
end if;
sqlString1 :='drop table InStore21407';
select count(*) into mycount from user_tables where Table_Name='INSTORE21407';
if mycount >0 then
EXECUTE IMMEDIATE sqlString1 ;
commit;
end if;
sqlString1 :='drop table InStore1407';
select count(*) into mycount from user_tables where Table_Name='INSTORE1407';
if mycount >0 then
EXECUTE IMMEDIATE sqlString1 ;
commit;
end if;
sqlString1 :='drop table Indent21407';
select count(*) into mycount from user_tables where Table_Name='INDENT21407';
if mycount >0 then
EXECUTE IMMEDIATE sqlString1 ;
commit;
end if;
sqlString1 :='drop table Indent1407';
select count(*) into mycount from user_tables where Table_Name='INDENT1407';
if mycount >0 then
EXECUTE IMMEDIATE sqlString1 ;
commit;
end if;
sqlString1 :='drop table Supplier1407';
select count(*) into mycount from user_tables where Table_Name='SUPPLIER1407';
if mycount >0 then
EXECUTE IMMEDIATE sqlString1 ;
commit;
end if;
sqlString1 :='drop table Customer1407';
select count(*) into mycount from user_tables where Table_Name='CUSTOMER1407';
if mycount >0 then
EXECUTE IMMEDIATE sqlString1 ;
commit;
end if;
sqlString1 :='drop table Goods1407';
select count(*) into mycount from user_tables where Table_Name='GOODS1407';
if mycount >0 then
EXECUTE IMMEDIATE sqlString1 ;
commit;
end if;
sqlString1 :='drop table Type1407';
select count(*) into mycount from user_tables where Table_Name='TYPE1407';
if mycount >0 then
EXECUTE IMMEDIATE sqlString1 ;
commit;
end if;
sqlString1 :='drop table Employee1407';
select count(*) into mycount from user_tables where Table_Name='EMPLOYEE1407';
if mycount >0 then
EXECUTE IMMEDIATE sqlString1 ;
commit;
end if;
sqlString1 :='drop table Nation1407';
select count(*) into mycount from user_tables where Table_Name='NATION1407';
if mycount >0 then
EXECUTE IMMEDIATE sqlString1 ;
commit;
end if;
end;
/
rem ******************************************************
rem ** 创建民族信息表
rem ******************************************************
CREATE TABLE Nation1407(
NationNo1407 char(2) NOT NULL, /*民族编号*/
NationName1407 char(8) NOT NULL, /*民族名称*/
IfVoid Number(1) default 0
check (IfVoid between 0 and 1) , /*是否作废*/
constraint Ntion1407PK primary key(NationNo1407) /*主键约束*/
);
insert into Nation1407 values('01','汉族','0');
insert into Nation1407 values('02','回族','0');
insert into Nation1407 values('03','壮族','0');
insert into Nation1407 values('04','傣族','0');
rem ******************************************************
rem ** 创建职工信息表
rem ******************************************************
CREATE TABLE Employee1407(
EmployeeNo1407 char(8) NOT NULL, /*职工编号*/
EmployeeName char(10) NOT NULL, /*职工姓名*/
Sex char(10) , /*职工年龄*/
OutTime date , /*职工离职时间*/
ID char(20) NOT NULL, /*职工身份证号码*/
NationNo1407 char(2) NOT NULL, /*员工民族编号*/
constraint Employee1407PK primary key(EmployeeNo1407), /*主键约束*/
constraint EmployeeNation1407FK foreign key(NationNo1407) /*外键约束*/
references Nation1407(NationNo1407)
);
insert into Employee1407 values('1999-001','兰书琴','女','','35042419880223102','01');
insert into Employee1407 values('1999-002','赵孟','男','','36052419880704104','02');
insert into Employee1407 values('1999-003','李初珍','女','','35042419850203102','03');
insert into Employee1407 values('1999-004','金元青','男','','36052419860904123','04');
insert into Employee1407 values('2001-001','夏丹云','女','','35042419870101202','01');
insert into Employee1407 values('2001-002','赵原','男','','36052519880509002','01');
rem ******************************************************
rem ** 创建商品类型表
rem ******************************************************
CREATE TABLE Type1407 (
TypeNo1407 char(10) NOT NULL , /*商品类型编号*/
TypeName char(10) Not NULL , /*商品类型名称*/
IfVoid Number(1) default 0
check (IfVoid between 0 and 1) NOT NULL, /*是否作废*/
constraint Type1407PK primary key(TypeNo1407)
);
insert into Type1407(TypeNo1407,TypeName) values('001','食品');
insert into Type1407(TypeNo1407,TypeName) values('002','生活用品');
insert into Type1407(TypeNo1407,TypeName) values('003','办公用品');
insert into Type1407(TypeNo1407,TypeName) values('004','体育用品');
rem ******************************************************
rem ** 创建商品信息表
rem ******************************************************
CREATE TABLE Goods1407(
GoodsNo1407 char(10) NOT NULL, /*商品编号*/
GoodsName varchar2(30) NOT NULL, /*商品名称*/
TypeNo1407 char(10) NOT NULL, /*商品所属类型*/
IfVoid Number(1) default 0
check (IfVoid between 0 and 1) NOT NULL, /*是否作废*/
constraint Goods1407PK primary key(GoodsNo1407),
constraint GoodsType1407FK foreign key(TypeNo1407)
references Type1407(TypeNo1407)
);
insert into Goods1407 values('F001','矿泉水','001','0');
insert into Goods1407 values('F002','面包','001','0');
insert into Goods1407 values('D001','牙刷','002','0');
insert into Goods1407 values('O001','钢笔','003','0');
insert into Goods1407 values('S001','篮球','004','0');
rem ******************************************************
rem ** 创建客户信息表
rem ******************************************************
CREATE TABLE Customer1407 (
CustomerNo1407 char(3) NOT NULL , /*客户编号*/
Customer varchar2(30) NOT NULL , /*客户名称*/
Address varchar2(30), /*客户地址*/
Phone char(20), /*客户联系电话*/
IfVoid Number(1) default 0
check (IfVoid between 0 and 1) NOT NULL, /*是否作废*/
constraint Customer1407PK primary key(CustomerNo1407)
) ;
insert into Customer140