테이블

=========================================[장바구니]

 

/*카테고리 테이블*/
create table jsp_category(
     cnum number(8) primary key,
     code varchar2(20) unique,
     cname varchar2(50) not null
);

create sequence jsp_category_cnum_seq
start with 1
increment by 1
nocache;

/*상품 테이블*/
create table jsp_product(
     pnum number(8) primary key,
     pname varchar2(100) not null,
     pcategory_fk varchar2(20) --카테고리 코드
                        references jsp_category (code),
     pcompany varchar2(50), --제조회사 
     pimage1 varchar2(30), --상품이미지파일명
     pimage2 varchar2(30),
     pqty number(8) default 0, --상품수량
     price number(8) default 0, --상품 정가
     saleprice number(8) default 0,--상품 판매가
     pspec varchar2(20),--HIT,BEST,NEW 등의 값을 가짐
     pcontents varchar2(300),--상품설명
     point number(8) default 0, --포인트점수
     pinputdate date   --입고일
);

create sequence jsp_product_pnum_seq
start with 1
increment by 1
nocache;

 

 

=====================================[주문관련 테이블]

- jsp_order       --주문관련 테이블
- jsp_odetail     --주문 상세내역 테이블
- jsp_customer --상품 수령자 테이블

 

create table jsp_order(
     onum varchar2(20) primary key, --주문번호
     userid varchar2(20) not null, --주문자 아이디
     ototalPrice number(8), --주문 총액
     ototalPoint number(8), --총 포인트
     opaystate varchar2(12) default '미결제', --지불 상태
     odeliver varchar2(20) default '미배송',
     orderdate date --주문일
);

create table jsp_odetail(
     num number(8) primary key,
     onum varchar2(20) not null,
     pnum number(8) not null,
     pname varchar2(50) not null,
     pimage varchar2(30),
     price number(8),
     pqty number(8),
     point number(8)
);

create sequence jsp_odetail_num_seq
start with 1
increment by 1
nocache;

 

create table jsp_customer(
     num number(8) primary key,
     onum varchar2(20) not null,
     name varchar2(30) not null,
     email varchar2(50),
     hp1 varchar2(3) not null,
     hp2 varchar2(4) not null,
     hp3 varchar2(4) not null,
     zipcode varchar2(10) not null,
     addr1 varchar2(100) not null,
     addr2 varchar2(100) not null
);

create sequence jsp_customer_num_seq
start with 1
increment by 1
nocache;

select * from spring_order;
select * from spring_odetail;
select * from spring_customer;

 

select a.onum,a.osumprice, a.osumpoint, a.userid, a.opaystate, a.odeliver

         c.name, c.hp1, c.hp2, c.hp3, c.addr,
         b.pnum, b.pname, b.pimage,b.price,b.point,
from spring_order a join spring_odetail b
on a.onum=b.onum join spring_customer c
on b.onum=c.onum
where onum=?

 

select a.onum,a.osumprice, a.osumpoint, a.userid, a.opaystate, a.odeliver

          c.name, c.hp1, c.hp2, c.hp3, c.addr,
          b.pnum, b.pname, b.pimage,b.price,b.point,
from spring_order a
join spring_odetail b on a.onum=b.onum
join spring_customer c  on b.onum=c.onum;