Product

product

create table product (
    idx 		number,
    name		varchar2(100),
    price		number,
    expiryDate	date,
    memo		varchar2(2000)
);

insert into product values (
    1, 
    '코카콜라(캔) 190ml', 
    900, 
    '24/04/01', 
    '코카콜라 캔 소형'
);
insert into product values (2, '코카콜라(PET) 500ml', 1500, '24/04/01', '코카콜라 PET 중형');
insert into product values (3, '코카콜라(PET) 1.5L', 2800, '24/04/01', '코카콜라 PET 대형');
insert into product values (4, '스프라이트 (캔) 300ml', 1200, '24/05/01', '스프라이트 캔 소형');
insert into product values (5, '페레로로쉐(3입)', 3000, '23/10/25', '페레로로쉐 매장 입구 진열 품목');

select * from product order by idx;
delete product;
commit;

product_Constraint

/*
    create table product (
        idx         number,
        name        varchar2(100),
        price       number,
        expiryDate  date,
        memo        varchar2(2000)
    );
*/

drop sequence product_seq;
drop table product;

create sequence product_seq
    start with 1
    maxvalue 999999999
    increment by 1
    nocache
    nocycle;

create table product (
    idx         number              default product_seq.nextval primary key,
    name        varchar2(100)       unique,
    price       number              check(price >= 100),
    --expiryDate  date                check(expiryDate between to_date('2023/01/01', 'YYYY/MM/DD') and to_date('9999/12/31', 'YYYY/MM/DD')),
    expiryDate  date                check(expiryDate > to_date('2023/01/01', 'YYYY/MM/DD')),
    memo        varchar2(2000)      not null
);

product_update_delete

select * from product
    order by idx asc;       -- ascendant (생략 가능, 기본값)
    -- order by idx desc;   -- descendant (내림차순)
    
update product
    set
        name='스프라이트 (캔) 250ML',
        price=1500
    where
        idx=4;
        
delete product where idx = 6;
                
commit;    
rollback;