数据库实习

create table usr
(
    usr_id varchar2(10),
    nackname varchar2(15) not null,
    name varchar2(15) not null,
    gender varchar2(10),
    age number,
    sno varchar2(15) unique,
    school varchar2(20),
    college varchar2(20),
    major varchar2(20),
    class varchar2(20),
    phone varchar2(15) unique,
    email varchar2(20) unique,
    credit number,
    pwd varchar2(20) not null,
    logtime date,
    constraint pk_usr primary key (usr_id),
    constraint ck_age check (age > 0 and age <100),
    constraint ck_credit check (credit >= 0 and credit <= 100),
    constraint ck_gender check (gender in ('ÄÐ', 'Å®'))
);

create table goods
(
    goods_id varchar2(10),
    name varchar2(15) not null,
    type varchar2(15),
    seller varchar2(10) not null,
    describe varchar2(100),
    buytime date,
    price number not null,
    clickrate number,
    state varchar2(10),
    constraint pk_goods primary key (goods_id),
    constraint fk_seller foreign key (seller) references usr(usr_id),
    constraint ck_price check (price >= 0),
    constraint ck_clickrate check (clickrate >= 0),
    constraint ck_state check (state in ('ÔÚÊÛ', 'ÊÛ³ö', 'δÉϼÜ'))
);

create table deal
(
    buyer_id varchar2(10),
    goods_id varchar2(10),
    ordertime date,
    arrivaltime date,
    score number,
    constraint pk_buyer_goods primary key(buyer_id, goods_id),
    constraint fk_buyer foreign key (buyer_id) references usr(usr_id),
    constraint fk_goods foreign key (goods_id) references goods(goods_id),
    constraint ck_score check (score >= 0 and score <= 10),
    constraint ck_arrivaltime check (arrivaltime >= ordertime)
);

create table login
(
    log_id varchar2(10),
    usr_id varchar2(10),
    logintime date,
    logouttime date,
    constraint pk_log primary key(log_id),
    constraint fk_usr foreign key (usr_id) references usr(usr_id),
    constraint ck_logout check (logouttime > logintime)
);

create table oos
(
    oos_id varchar2(10),
    oosname varchar2(15) not null,
    oostype varchar2(15) not null,
    oosprice number,
    oosbuyer varchar2(10) not null,
    constraint pk_oos primary key (oos_id),
    constraint fk_oosbuyer foreign key (oosbuyer) references usr(usr_id),
    constraint ck_oosprice check (oosprice > 0)
);

create table favorites
(
    fav_id varchar2(10),
    name varchar2(15) not null,
    createtime date,
    owner varchar2(10),
    constraint pk_fav primary key (fav_id),
    constraint fk_owner foreign key (owner) references usr(usr_id)
);

create table collection
(
    coll_id varchar2(10),
    fav_id varchar2(10) not null,
    goods varchar2(10) not null,
    colltime date,
    constraint pk_coll primary key (coll_id),
    constraint fk_fav foreign key (fav_id) references favorites(fav_id),
    constraint fk_fav_goods foreign key (goods) references goods(goods_id)
);

create table returnlist
(
    re_id varchar2(10),
    buyer varchar2(10),
    seller varchar2(10),
    reason varchar2(100),
    price number not null,
    constraint pk_re primary key (re_id) ,
    constraint fk_re_buyer foreign key(buyer) references usr(usr_id),
    constraint fk_re_seller foreign key (seller) references usr(usr_id),
    constraint ck_re_price check (price > 0) 
);

create table commlist
(
    comm_id varchar2(10),
    buyer varchar2(10),
    seller varchar2(10),
    result varchar2(50),
    commtime date,
    constraint pk_comm primary key (comm_id),
    constraint fk_comm_buyer foreign key(buyer) references usr(usr_id),
    constraint fk_comm_seller foreign key (seller) references usr(usr_id)
);

create table valuelist
(
    val_id varchar2(10),
    goods_id varchar2(10),
    price number not null,
    constraint pk_val primary key (val_id),
    constraint fk_val_goods foreign key (goods_id) references goods(goods_id),
    constraint ck_val_price check (price > 0)
);

create table pwdcglist
(
    pwd_id varchar2(10),
    usr_id varchar2(10),
    oldpwd varchar2(20),
    newpwd varchar2(20),
    cgtime date,
    constraint pk_pwd primary key (pwd_id),
    constraint fk_pwd_usr foreign key (usr_id) references usr(usr_id),
    constraint ck_newpwd check (newpwd != oldpwd)
);

create table sugglist
(
    sugg_id varchar2(10),
    usr_id varchar2(10),
    sugg_mess varchar2(100),
    dispose varchar2(50),
    constraint pk_sugg primary key (sugg_id),
    constraint fk_sugg_usr foreign key (usr_id) references usr(usr_id)
);

create table discountlist
(
    dis_id varchar2(10),
    goods varchar2(10),
    disprice number,
    bgtime date,
    edtime date,
    constraint pk_dis primary key (dis_id),
    constraint pk_disgoods foreign key (goods) references goods(goods_id),
    constraint ck_disprice check (disprice > 0),
    constraint ck_edtime check (edtime >= bgtime)
);

create table unshelve
(
    unshe_id varchar2(10),
    goods varchar2(10),
    unshetime date,
    constraint pk_unshe primary key (unshe_id),
    constraint fk_unshe_goods foreign key (goods) references goods(goods_id)
);

create table browselist
(
    usr varchar2(10),
    goods varchar2(10),
    browsetime date not null,
    constraint pk_browse primary key (usr, goods),
    constraint fk_browse_usr foreign key (usr) references usr(usr_id),
    constraint fk_browse_goods foreign key (goods) references goods(goods_id)
);

create table commentlist
(
    comment_id    varchar2(10),
    buyer varchar2(10),
    goods varchar2(10),
    message varchar2(100),
    commtime date,
    constraint pk_comment primary key (comment_id),
    constraint fk_comment_buyer foreign key (buyer) references usr(usr_id),
    constraint fk_comment_goods    foreign key (goods) references goods(goods_id)
);

create view deal_view(buyer_nackname, seller_nackname, goods_name, goods_type, goods_describe, deal_price, deal_time, score)
as
select A.nackname, B.nackname, goods.name, goods.type, goods.describe, goods.price, deal.ordertime, deal.score
from usr A, usr B, goods, deal
where A.usr_id = deal.buyer_id and
      goods.goods_id = deal.goods_id and
      B.usr_id = goods.seller;

create view goods_onsale_view(goods_name, goods_type, goods_describe, price, clickrate, seller_nackname, seller_credit, seller_buytime)
as
select goods.name, goods.type, goods.describe, goods.price, goods.clickrate, usr.nackname, usr.credit, goods.buytime
from usr, goods
where goods.seller = usr.usr_id and
      goods.state = 'ÔÚÊÛ';

create view discount_view(name, type, describe, discount_price, startTime, endTime,  clickrate, seller_nackname, seller_credit, seller_buytime)
as
select goods.name, goods.type, goods.describe, discountlist.disprice, discountlist.bgtime, discountlist.edtime, goods.clickrate, usr.nackname, usr.credit, goods.buytime
from usr, goods, discountlist
where discountlist.goods = goods.goods_id and
      goods.seller = usr.usr_id;

create view collection_view(favorites_name, goods_name, goods_type, goods_describe, price, collect_time)      
as
select favorites.name, goods.name, goods.type, goods.describe, goods.price, collection.colltime
from favorites, collection, goods
where favorites.fav_id = collection.fav_id and
      collection.goods = goods.goods_id;

create or replace trigger insert_deal
    before insert on deal 
    for each row
begin 
    update goods
    set goods.state = 'ÊÛ³ö'
    where goods.goods_id = :new.goods_id;    
end;

create or replace trigger insert_unshelve
    before insert on unshelve
    for each row
begin
    update goods
    set goods.state = 'δÉϼÜ'
    where goods.goods_id = :new.goods;
end;

create or replace trigger insert_discountlist
    before insert on discountlist
    for each row
begin
    update goods
    set goods.price = :new.disprice
    where goods.goods_id = :new.goods;
end;

create or replace trigger insert_pwdcglist
    before insert on pwdcglist
    for each row
begin
    update usr
    set usr.pwd = :new.newpwd
    where usr.usr_id = :new.usr_id;
end;

create or replace procedure query_onsale_byName(
    v_goods_name in goods_onsale_view.goods_name%type,
    out_return out sys_refcursor
)as
begin 
    open out_return for 'select *
    from goods_onsale_view
    where v_goods_name = goods_onsale_view.goods_name';
end;    

create or replace procedure query_onsale_byType(
    v_goods_type in goods_onsale_view.goods_type%type,
    out_return out sys_refcursor
)as
begin 
    open out_return for 'select *
    from goods_onsale_view
    where v_goods_type = goods_onsale_view.goods_type';
end;

--1)ÐÂÓû§×¢²áÒµÎñ
create or replace procedure new_usr(
    v_usr_id in usr.usr_id%type,
    v_nackname in usr.nackname%type,
    v_name in usr.name%type,
    v_gender in usr.gender%type,
    v_age in usr.age%type,
    v_sno in usr.sno%type,
    v_school in usr.school%type,
    v_college in usr.college%type,
    v_major in usr.major%type,
    v_class in usr.class%type,
    v_phone in usr.phone%type,
    v_email in usr.email%type,
    v_credit in usr.credit%type,
    v_pwd in usr.pwd%type,
    v_logtime in usr.logtime%type
)as
begin 
    insert into usr
    values(v_usr_id, v_nackname, v_name, v_gender, v_age, v_sno, v_school, v_college, v_major, v_class, v_phone, v_email, v_credit, v_pwd, v_logtime);
    commit;
end;    

create or replace procedure new_goods(
    v_goods_id in goods.goods_id%type,
    v_name in goods.name%type,
    v_type in goods.type%type,
    v_seller in goods.seller%type,
    v_describe in goods.describe%type,
    v_buytime in goods.buytime%type,
    v_price in goods.price%type,
    v_clickrate in goods.clickrate%type,
    v_state in goods.state%type
)as
begin 
    insert into goods
    values(v_goods_id, v_name, v_type, v_seller, v_describe, v_buytime, v_price, v_clickrate, v_state);
    commit;
end;

create or replace procedure new_deal(
    v_buyer_id in deal.buyer_id%type,
    v_goods_id in deal.goods_id%type,
    v_ordertime in deal.ordertime%type,
    v_arrivaltime in deal.arrivaltime%type,
    v_score in deal.score%type
)as
begin 
    insert into deal
    values(v_buyer_id, v_goods_id, v_ordertime, v_arrivaltime, v_score);
    commit;
end;

create or replace procedure new_login(
    v_log_id in login.log_id%type,
    v_usr_id in login.usr_id%type,
    v_logintime in login.logintime%type,
    v_logouttime in login.logouttime%type
)as
begin 
    insert into login
    values(v_log_id, v_usr_id, v_logintime, v_logouttime);
    commit;
end;

-- declare 
--  a login.log_id%type:='1';
--     b login.usr_id%type:='2';
--     c login.logintime%type:=to_date('2017/01/12', 'yyyy/mm/dd');
--     d login.logouttime%type:=to_date('2017/01/13', 'yyyy/mm/dd');
-- begin 
--   new_login(a, b, c, d);
--   dbms_output.put_line(a||b||c||d);
-- end;

create or replace procedure new_oos(
    v_oos_id in oos.oos_id%type,
    v_oosname in oos.oosname%type,
    v_oostype in oos.oostype%type,
    v_oosprice in oos.oosprice%type,
    v_oosbuyer in oos.oosbuyer%type
)as
begin 
    insert into oos(oos_id, oosname, oostype, oosprice, oosbuyer)
    values(v_oos_id, v_oosname, v_oostype, v_oosprice, v_oosbuyer);
    commit;
end;

create or replace procedure new_favorites(
    v_fav_id in favorites.fav_id%type,
    v_name in favorites.name%type,
    v_createtime in favorites.createtime%type,
    v_owner in favorites.owner%type
)as
begin 
    insert into favorites
    values(v_fav_id, v_name, v_createtime, v_owner);
    commit;
end;

-- declare 
--  a favorites.fav_id%type:='1';
--     b favorites.name%type:='СÃ×ÊÖ»·';
--     c favorites.createtime%type:=to_date('2017/01/12', 'yyyy/mm/dd');
--     d favorites.owner%type:='3';
-- begin 
--   new_favorites(a, b, c, d);
--   dbms_output.put_line(a||b||c||d);
-- end;

create or replace procedure new_collection(
    v_coll_id in collection.coll_id%type,
    v_fav_id in collection.fav_id%type,
    v_goods in collection.goods%type,
    v_colltime in collection.colltime%type
)as
begin 
    insert into collection
    values(v_coll_id, v_fav_id, v_goods, v_colltime);
    commit;
end;

create or replace procedure new_returnlist(
    v_re_id in returnlist.re_id%type,
    v_buyer in returnlist.buyer%type,
    v_seller in returnlist.seller%type,
    v_reason in returnlist.reason%type,
    v_price in returnlist.price%type
)as
begin 
    insert into returnlist
    values(v_re_id, v_buyer, v_seller, v_reason, v_price);
    commit;
end;

create or replace procedure new_commlist(
    v_comm_id in commlist.comm_id%type,
    v_buyer in commlist.buyer%type,
    v_seller in commlist.seller%type,
    v_result in commlist.result%type,
    v_commtime in commlist.commtime%type
)as
begin 
    insert into commlist
    values(v_comm_id, v_buyer, v_seller, v_result, v_commtime);
    commit;
end;

create or replace procedure new_valuelist(
    v_val_id in valuelist.val_id%type,
    v_goods_id in valuelist.goods_id%type,
    v_price in valuelist.price%type
)as
begin 
    insert into valuelist
    values(v_val_id, v_goods_id, v_price);
    commit;
end;

create or replace procedure new_pwdcglist(
    v_pwd_id in pwdcglist.pwd_id%type,
    v_usr_id in pwdcglist.usr_id%type,
    v_oldpwd in pwdcglist.oldpwd%type,
    v_newpwd in pwdcglist.newpwd%type,
    v_cgtime in pwdcglist.cgtime%type
)as
begin 
    insert into pwdcglist
    values(v_pwd_id, v_usr_id, v_oldpwd, v_newpwd, v_cgtime);
    commit;
end;

create or replace procedure new_sugglist(
    v_sugg_id in sugglist.sugg_id%type,
    v_usr_id in sugglist.usr_id%type,
    v_sugg_mess in sugglist.sugg_mess%type,
    v_dispose in sugglist.dispose%type
)as
begin 
    insert into sugglist
    values(v_sugg_id, v_usr_id, v_sugg_mess, v_dispose);
    commit;
end;

create or replace procedure new_discountlist(
    v_dis_id in discountlist.dis_id%type,
    v_goods in discountlist.goods%type,
    v_disprice in discountlist.disprice%type,
    v_bgtime in discountlist.bgtime%type,
    v_edtime in discountlist.edtime%type
)as
begin 
    insert into discountlist
    values(v_dis_id, v_goods, v_disprice, v_bgtime, v_edtime);
    commit;
end;

create or replace procedure new_unshelve(
    v_unshe_id in unshelve.unshe_id%type,
    v_goods in unshelve.goods%type,
    v_unshetime in unshelve.unshetime%type
)as
begin 
    insert into unshelve
    values(v_unshe_id, v_goods, v_unshetime);
    commit;
end;

create or replace procedure new_browselist(
    v_usr in browselist.usr%type,
    v_goods in browselist.goods%type,
    v_browsetime in browselist.browsetime%type
)as
begin 
    insert into browselist
    values(v_usr, v_goods, v_browsetime);
    commit;
end;

create or replace procedure new_commentlist(
    v_comment_id in commentlist.comment_id%type,
    v_buyer in commentlist.buyer%type,
    v_goods in commentlist.goods%type,
    v_message in commentlist.message%type,
    v_commtime in commentlist.commtime%type
)as
begin 
    insert into commentlist
    values(v_comment_id, v_buyer, v_goods, v_message, v_commtime);
    commit;
end;

--Óû§¸ÄÃÜÂëÒµÎñ
create or replace procedure change_pwd(
  v_usr in usr.nackname%type,
  v_oldpwd in usr.pwd%type,
  v_newpwd in usr.pwd%type
)is v_pwd usr.pwd%type;
  v_cnt number;
  v_usr_id usr.usr_id%type;
begin
  select usr_id, pwd into v_usr_id, v_pwd
  from usr
  where nackname = v_usr;
  if(v_pwd = v_oldpwd) then
    update usr
    set pwd = v_newpwd
    where nackname = v_usr;

    select count(pwd_id) into v_cnt
    from pwdcglist;

    new_pwdcglist(to_char(v_cnt+1, '00000'),  v_usr_id, v_oldpwd, v_newpwd, sysdate);
  end if;
end;

declare 
    a usr.nackname%type:='A';
    b usr.pwd%type:='000000';
    c usr.pwd%type:='123456';
begin 
  change_pwd(a, b, c);
  dbms_output.put_line(a||' '||b||' '||c);
end;

--Âò¼Òä¯ÀÀ²éѯҵÎñ
create or replace procedure browse_goods(
      v_usr in usr.nackname%type,
      v_goods in goods.name%type
)is v_cnt number;
      v_usr_id usr.usr_id%type;
      v_type goods.type%type;
      v_seller usr.name%type;
      v_describe goods.describe%type;
      v_buytime goods.buytime%type;
      v_price goods.price%type;
      v_clickrate goods.clickrate%type;
      v_goods_id goods.goods_id%type;
cursor c is
      select goods.goods_id, goods.type, usr.nackname, goods.describe, goods.price, goods.buytime, goods.clickrate
      from goods, usr
      where goods.name like '%'||v_goods||'%' and
            goods.state = 'ÔÚÊÛ' and
            goods.seller = usr.usr_id;
begin 
      select usr_id into v_usr_id 
      from usr
      where nackname = v_usr;
      open c;
      fetch c into v_goods_id, v_type, v_seller, v_describe, v_price, v_buytime, v_clickrate;
      if(c%notfound)then
           select count(oos_id) into v_cnt
        from oos;
        insert into oos values(to_char(v_cnt+1, '00000'), v_goods, 'δ֪', null, v_usr_id);
        dbms_output.put_line('out of store');
        --commit;
      else  
          dbms_output.put_line(v_goods||' '||v_type||' '||v_seller||' '||v_describe||' '||v_price||' '||v_buytime||' '||v_clickrate);
          new_browselist(v_usr_id, v_goods_id, sysdate);
          loop 
            fetch c into v_goods_id, v_type, v_seller, v_describe, v_price, v_buytime, v_clickrate;
            exit when c%notfound;
            dbms_output.put_line(v_goods||' '||v_type||' '||v_seller||' '||v_describe||' '||v_price||' '||v_buytime||' '||v_clickrate);
            new_browselist(v_usr_id, v_goods_id, sysdate);
          end loop;
    end if;
end;    


declare 
      a usr.nackname%type:='A';
    b goods.name%type:='Сѧ';
begin 
      browse_goods(a, b);
  --dbms_output.put_line(a||' '||b);
end;