销售管理系统全过程

/**********************销售管理系统*************************************/
/*1.创建表*/
/*--状态数据表--*/
create table nanstateInfo(
  id number(4) not null primary key,
  value varchar2(30),
  type number(2)
)
/
begin
insert into nanstateInfo values(1,'已订货',1);
insert into nanstateInfo values(2,'已发货',1);
insert into nanstateInfo values(3,'已收款',1);
insert into nanstateInfo values(4,'启用',2);
insert into nanstateInfo values(5,'停用',2);
insert into nanstateInfo values(6,'发货中',1);
insert into nanstateInfo values(7,'发货完',1);
end;
/
/*--用户表--*/
create table nanusers(
  id number(10) not null,
  loginName varchar2(30) not null,
  password varchar2(50),
  name varchar2(100),
  address varchar2(100),
  phone varchar2(50),
  createDate date not null,
  modifyDate date,
  status number(2),
  type number(2),
  primary key(id),
  foreign key(status) references nanstateInfo(id)
)
/
/*--产品表--*/
create table nanproduct(
  id number(10) not null,
  code varchar2(30) not null,
  name varchar2(30),
  unitPrice number(10),
  spec varchar2(300),
  units varchar2(10),
  state number(2),
  manufacturer varchar2(20),
  createDate date not null,
  modifyDate date,
  primary key(id),
  foreign key(state) references nanstateInfo(id)
)
/
/*--订货单表--*/
create  table nanDHD(
    id number(10) not null,
    handle varchar2(50) not null,
    customerId number(10) not null,
    state number(2) not null,
    modifyDate date not null,
    primary key (id),
    foreign key(state) references nanstateInfo(id),
    foreign key(customerId) references nanusers(id)
)
/
/*--出货表单--*/
create  table nanCHD(
  id number(10) not null,
  dhdId number(2) not null,
  handle varchar2(50) not null,
  customerId number(10) not null,
  createDate date not null,
  state number(2) not null,
  primary key(id),
  foreign key(dhdId) references nanDHD(id),
  foreign key(customerId) references  nanusers(id),
  foreign key(state) references nanstateInfo(id)
)
/
/*--订货单列表--*/
create table nanItem(
    id number(10) not null,
    dhdId number(10) not null,
    productId number(10) not null,
    dhl number(10,2),
    sendDate date not null,
    createDate date not null,
    modifyDate date ,
    state number(2) not null,
    primary key(id),
    foreign key(dhdId) references  nanDHD(id),
    foreign key(productId) references  nanproduct(id),
    foreign key(state) references  nanstateInfo(id)
)
/**2.创建序列**/
create sequence nanusers_seq;
create sequence nanproduct_seq;
create sequence nanDHD_seq;
create sequence nanCHD_seq;
create sequence nanItem_seq;
/
/***********3.创建存储过程************************/
/*包声明*/
create or replace package nanuserspack is
  type curUser is ref cursor;
  Success constant integer:=1;
  UserExist constant integer:=1;
  UserNotExist constant integer:=2;
  UserNotExistOrPasswordError constant integer:=-3;
  UnknowError constant integer:=-8;
  procedure registerUser(
    v_id out number,
    v_loginName in varchar2,
    v_password in varchar2,
    v_name varchar2,
    v_address in varchar2,
    v_phone in varchar2,
    v_type in  number,
    result out number
  );
  procedure getUserById(
    v_id in number,
    v_user out curUser,
    result out number
  );
  procedure getUserForLogin(
    v_loginName in varchar2,
    v_password in varchar2,
    v_user out curUser,
    result out number
  );
end nanuserspack;
/
/*包主体*/
create or replace package  body nanuserspack is
  procedure registerUser(
    v_id out number,
    v_loginName in varchar2,
    v_password in varchar2,
    v_name varchar2,
    v_address in varchar2,
    v_phone in varchar2,
    v_type in  number,
    result out number
  )as
  isExist integer:=0;
  userHasExist exception;
  begin
    select count(rownum) into isExist from nanusers
    where lower(loginName)=lower(v_loginName);
    if isExist>0 then
      raise userHasExist;
    else
      dbms_output.PUT_LINE('开始插入');
      insert into nanusers(id,loginName,password,name,address,phone,createDate,status,type)
      values(nanusers_seq.nextval,v_loginName,v_password,v_name,v_address,v_phone,sysdate,4,v_type);
      select id into v_id from nanusers where lower(loginName)=lower(v_loginName);
      commit;
      dbms_output.PUT_LINE('插入成功!');
      result:=Success;
    end if;
  exception
      when userHasExist then
        dbms_output.PUT_LINE('用户已经存在');
        v_id:=null;
        result:=UserExist;
      when others then
        dbms_output.PUT_LINE('插入出现错误');
        result:=UnknowError;  
  end  registerUser;
  procedure getUserById(
    v_id in number,
    v_user out curUser,
    result out number
  )as
    cnt number;
    notExist exception;
  begin
    select count(rownum) into cnt from nanusers where id=v_id;
    dbms_output.PUT_LINE(cnt);
    if cnt>0 then
      open v_user for select * from nanusers where id=v_id;
      result:=Success;
      dbms_output.PUT_LINE('成功得到数据!');
    else
      dbms_output.PUT_LINE('没有这个用户!');
      open v_user for select * from dual;
      raise notExist;
    end if;  
  exception
      when notExist then
        result:=UserNotExist;
      when others then
        result:=UnknowError;
  end getUserById;
 
  procedure getUserForLogin(
    v_loginName in varchar2,
    v_password in varchar2,
    v_user out curUser,
    result out number
  )as
    cnt number;
    NotExistOrPasswordError Exception;
  begin
    select count(rownum) into cnt from nanusers
      where lower(loginName)=lower(v_loginName)
        and password=v_password;
    if cnt>0 then
      open v_user for select * from nanusers where lower(loginName)=lower(v_loginName)
      and password=v_password;
      dbms_output.PUT_LINE('登录成功');
      result:=Success;
    else
      open v_user for select * from dual;
      dbms_output.PUT_LINE('登录失败，请检查用户名密码!');
      raise NotExistOrPasswordError;
    end if;
  exception
     when NotExistOrPasswordError then
        result:=UserNotExistOrPasswordError;
     when others then
        result:=UnknowError;
  end getUserForLogin;
end nanuserspack;
/********************4.进行测试*********************/
/*来个简单插入*/
declare
  v_no number;
  v_result number;
  type aa is ref cursor;
  v_cur aa;
begin 
   /*nanuserspack.REGISTERUSER(v_no,'lisi2','rrt4','fk4rr','f4rrr','1359568521',2,v_result);
   dbms_output.PUT_LINE(v_result);
   dbms_output.PUT_LINE(v_no);*/
   nanuserspack.GETUSERBYID(10,v_cur,v_result);
   dbms_output.PUT_LINE(v_result);
   dbms_output.PUT_LINE(v_no);
  
end;