数据库脚本.sql

LINUX下POS收银系统,可以对货物及工作人员进行管理.近本实现增删改查的功能

/*==============================================================*/
/* DBMS name:      ORACLE Version 8i2 (8.1.6)                   */
/* Created on:     2009-3-30 22:34:48                           */
/*==============================================================*/


alter table table_sell
   drop constraint FK_SALE_STAF;

alter table table_sell_list
   drop constraint FK_SALE_PROD;

alter table table_sell_list
   drop constraint FK_SALDET_SALE;

drop table table_commodity cascade constraints;

drop table table_employee cascade constraints;

drop table table_sell cascade constraints;

drop table table_sell_list cascade constraints;

/*==============================================================*/
/* Table: table_commodity                                       */
/*==============================================================*/
create table table_commodity  (
   num               INT,
   bar_code             VARCHAR2(8)                     not null,
   commodity_name       VARCHAR2(30),
   unti                 VARCHAR2(16),
   spec                 VARCHAR2(16),
   sell_price           NUMBER(8,2),
   stock_price          NUMBER(8,2),
   stock_number         INT,
   agio                 NUMBER(8,2),
   delete_sign          INT,
   constraint PK_TAB_GOODS primary key (bar_code)
);

/*==============================================================*/
/* Table: table_employee                                        */
/*==============================================================*/
create table table_employee  (
   employee_ID          VARCHAR2(6)                     not null,
   employee_name        VARCHAR2(15),
   employee_passwd      VARCHAR2(16),
   employee_style       INT,
   delete_sign          INT,
   remark               VARCHAR2(30),
   constraint PK_TAB_EMP primary key (employee_ID)
);

/*==============================================================*/
/* Table: table_sell                                            */
/*==============================================================*/
create table table_sell  (
   sell_ID              INT                             not null,
   employee_ID          VARCHAR2(6),
   list_num             VARCHAR2(18),
   sell_date            DATE,
   payment_money        NUMBER(8,2),
   must_pay_money       NUMBER(8,2),
   fact_money           NUMBER(8,2),
   give_change          NUMBER(8,2),
   sell_fettle          INT,
   constraint PK_TABLE_SELL primary key (sell_ID)
);

/*==============================================================*/
/* Table: table_sell_list                                       */
/*==============================================================*/
create table table_sell_list  (
   sell_list_ID         INT                             not null,
   bar_code             VARCHAR2(8),
   sell_ID              INT,
   counts               INT,
   unit_price           NUMBER(4,2),
   constraint PK_TAB_LIST primary key (sell_list_ID)
);

alter table table_sell
   add constraint FK_SALE_STAF foreign key (employee_ID)
      references table_employee (employee_ID);

alter table table_sell_list
   add constraint FK_SALE_PROD foreign key (bar_code)
      references table_commodity (bar_code);

alter table table_sell_list
   add constraint FK_SALDET_SALE foreign key (sell_ID)
      references table_sell (sell_ID);


create sequence seq_test 
  increment by 1 
  start with 1  
  maxvalue 99999 
  nocycle

create sequence seq_business
  increment by 1 
  start with 1000 
  maxvalue 99999 
  nocycle
  
  create sequence SEQ_GOODS_CODE
  increment by 1 
  start with 140 
  maxvalue 99999999 
  nocycle

    
  create sequence  SEQ_SELL_LIST_ID
  increment by 1 
  start with 1000 
  maxvalue 99999 
  nocycle
  
  create sequence SEQ_TUIHUO_ID 
  increment by 1 
  start with 140 
  maxvalue 99999999 
  nocycle

    
  create sequence SEQ_SELL_ID 
  increment by 1 
  start with 140 
  maxvalue 99999999 
  nocycle
  
    
  create sequence SEQ_PUTONG_ID 
  increment by 1 
  start with 140 
  maxvalue 99999999 
  nocycle



  SELECT SEQ_BUSINESS.NEXTVAL  FROM DUAL;
  
  
 insert into table_commodity values (seq_test.nextval,'CN000001', '雪碧', '瓶', '355ml', 2.5, 1.7, 9984, 0.8, 0);

insert into table_commodity values (seq_test.nextval,'CN000003', '百事可乐', '瓶', '355ml', 2.5, 1.8, 9996,1,0);
insert into table_commodity values (seq_test.nextval,'CN000002', '可口可乐', '瓶', '355ml', 2.5, 1.8, 9995, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000004', '美年达', '瓶', '355ml', 2.5, 1.8, 9998, 0.9, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000005', '七喜', '瓶', '355ml', 2.5, 1.7, 9996, 0.7, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000006', '农夫山泉', '瓶', '500ml', 1.5, 0.9, 9999, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000007', '娃哈哈矿泉水', '瓶', '500ml', 1, 0.7, 9994, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000008', '心相印', '条', '10包', 5, 3, 9998, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000009', '笔记本', '本', '16开', 2, 1, 9999, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000010', '康师傅牛肉面', '袋', '5包', 7.8, 4, 9999, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000011', '高露洁牙膏', '盒', '200g', 3, 1.8, 9999, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000012', '佳洁士牙刷', '支', '35g', 2, 1, 9999, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000013', '飘柔', '瓶', '400ml', 20, 12, 9999, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000014', '六神沐浴露', '瓶', '400ml', 18, 11, 9999, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000015', '雕牌洗衣粉', '袋', '200g', 3, 1, 9999, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000016', '快餐', '盒', '自助', 5, 3, 99, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000017', '成人纸尿片', '张', '10片', 1, 0.7, 9994, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000018', '安尔乐', '条', '10包', 5, 3, 9998, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000019', '高级笔记本', '本', '16开', 8, 1, 9999, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000020', '统一牛肉面', '袋', '5包', 7.8, 4, 9999, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000021', '开水', '瓶', '355ml', 2.5, 1.7, 9987, 0.8, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000023', '白开水', '瓶', '355ml', 2.5, 1.8, 9998, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000022', '咸开水', '瓶', '355ml', 2.5, 1.8, 9995, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000024', '甜开水', '瓶', '355ml', 2.5, 1.8, 9998, 0.9, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000025', '馊水', '瓶', '355ml', 2.5, 1.7, 9997, 0.7, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000026', '茶水', '瓶', '500ml', 1.5, 0.9, 9999, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000027', '咖啡', '瓶', '500ml', 1, 0.7, 9994, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000028', '绿帽子', '顶', '10次', 5, 3, 9998, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000029', '笔记本电脑', '台', '13寸', 6999, 1, 9999, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000030', '统一排骨面', '袋', '5包', 7.8, 4, 9999, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000031', '黑人牙膏', '盒', '200g', 3, 1.8, 9999, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000032', '白人牙刷', '支', '35g', 2, 1, 9999, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000033', '飘又柔', '瓶', '400ml', 20, 12, 9999, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000034', '黑又亮', '瓶', '400ml', 18, 11, 9999, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000035', '鸟牌洗衣粉', '袋', '200g', 3, 1, 9999, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000036', '便餐', '盒', '自助', 5, 3, 99, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000037', '婴儿纸尿片', '张', '10片', 1, 0.7, 9994, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000038', '护舒宝', '条', '10包', 5, 3, 9998, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000039', '低级笔记本', '本', '16开', 1, 1, 9999, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000040', '统一中国', '次', '2秒', 999, 4, 9999, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000041', '黄种人人牙膏', '盒', '200g', 3, 1.8, 9999, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000042', '印第安人牙刷', '支', '35g', 2, 1, 9999, 1, 0);

 
insert into table_commodity values (seq_test.nextval,'CN000043', '粗又大玉米', '支', '高级', 20, 12, 9999, 1, 0);