Fellow me on GitHub

数据库实习

  1 create table usr
  2 (
  3     usr_id varchar2(10),
  4     nackname varchar2(15) not null,
  5     name varchar2(15) not null,
  6     gender varchar2(10),
  7     age number,
  8     sno varchar2(15) unique,
  9     school varchar2(20),
 10     college varchar2(20),
 11     major varchar2(20),
 12     class varchar2(20),
 13     phone varchar2(15) unique,
 14     email varchar2(20) unique,
 15     credit number,
 16     pwd varchar2(20) not null,
 17     logtime date,
 18     constraint pk_usr primary key (usr_id),
 19     constraint ck_age check (age > 0 and age <100),
 20     constraint ck_credit check (credit >= 0 and credit <= 100),
 21     constraint ck_gender check (gender in ('ÄÐ', 'Å®'))
 22 );
 23 
 24 create table goods
 25 (
 26     goods_id varchar2(10),
 27     name varchar2(15) not null,
 28     type varchar2(15),
 29     seller varchar2(10) not null,
 30     describe varchar2(100),
 31     buytime date,
 32     price number not null,
 33     clickrate number,
 34     state varchar2(10),
 35     constraint pk_goods primary key (goods_id),
 36     constraint fk_seller foreign key (seller) references usr(usr_id),
 37     constraint ck_price check (price >= 0),
 38     constraint ck_clickrate check (clickrate >= 0),
 39     constraint ck_state check (state in ('ÔÚÊÛ', 'ÊÛ³ö', 'δÉϼÜ'))
 40 );
 41 
 42 create table deal
 43 (
 44     buyer_id varchar2(10),
 45     goods_id varchar2(10),
 46     ordertime date,
 47     arrivaltime date,
 48     score number,
 49     constraint pk_buyer_goods primary key(buyer_id, goods_id),
 50     constraint fk_buyer foreign key (buyer_id) references usr(usr_id),
 51     constraint fk_goods foreign key (goods_id) references goods(goods_id),
 52     constraint ck_score check (score >= 0 and score <= 10),
 53     constraint ck_arrivaltime check (arrivaltime >= ordertime)
 54 );
 55 
 56 create table login
 57 (
 58     log_id varchar2(10),
 59     usr_id varchar2(10),
 60     logintime date,
 61     logouttime date,
 62     constraint pk_log primary key(log_id),
 63     constraint fk_usr foreign key (usr_id) references usr(usr_id),
 64     constraint ck_logout check (logouttime > logintime)
 65 );
 66 
 67 create table oos
 68 (
 69     oos_id varchar2(10),
 70     oosname varchar2(15) not null,
 71     oostype varchar2(15) not null,
 72     oosprice number,
 73     oosbuyer varchar2(10) not null,
 74     constraint pk_oos primary key (oos_id),
 75     constraint fk_oosbuyer foreign key (oosbuyer) references usr(usr_id),
 76     constraint ck_oosprice check (oosprice > 0)
 77 );
 78 
 79 create table favorites
 80 (
 81     fav_id varchar2(10),
 82     name varchar2(15) not null,
 83     createtime date,
 84     owner varchar2(10),
 85     constraint pk_fav primary key (fav_id),
 86     constraint fk_owner foreign key (owner) references usr(usr_id)
 87 );
 88 
 89 create table collection
 90 (
 91     coll_id varchar2(10),
 92     fav_id varchar2(10) not null,
 93     goods varchar2(10) not null,
 94     colltime date,
 95     constraint pk_coll primary key (coll_id),
 96     constraint fk_fav foreign key (fav_id) references favorites(fav_id),
 97     constraint fk_fav_goods foreign key (goods) references goods(goods_id)
 98 );
 99 
100 create table returnlist
101 (
102     re_id varchar2(10),
103     buyer varchar2(10),
104     seller varchar2(10),
105     reason varchar2(100),
106     price number not null,
107     constraint pk_re primary key (re_id) ,
108     constraint fk_re_buyer foreign key(buyer) references usr(usr_id),
109     constraint fk_re_seller foreign key (seller) references usr(usr_id),
110     constraint ck_re_price check (price > 0) 
111 );
112 
113 create table commlist
114 (
115     comm_id varchar2(10),
116     buyer varchar2(10),
117     seller varchar2(10),
118     result varchar2(50),
119     commtime date,
120     constraint pk_comm primary key (comm_id),
121     constraint fk_comm_buyer foreign key(buyer) references usr(usr_id),
122     constraint fk_comm_seller foreign key (seller) references usr(usr_id)
123 );
124 
125 create table valuelist
126 (
127     val_id varchar2(10),
128     goods_id varchar2(10),
129     price number not null,
130     constraint pk_val primary key (val_id),
131     constraint fk_val_goods foreign key (goods_id) references goods(goods_id),
132     constraint ck_val_price check (price > 0)
133 );
134 
135 create table pwdcglist
136 (
137     pwd_id varchar2(10),
138     usr_id varchar2(10),
139     oldpwd varchar2(20),
140     newpwd varchar2(20),
141     cgtime date,
142     constraint pk_pwd primary key (pwd_id),
143     constraint fk_pwd_usr foreign key (usr_id) references usr(usr_id),
144     constraint ck_newpwd check (newpwd != oldpwd)
145 );
146 
147 create table sugglist
148 (
149     sugg_id varchar2(10),
150     usr_id varchar2(10),
151     sugg_mess varchar2(100),
152     dispose varchar2(50),
153     constraint pk_sugg primary key (sugg_id),
154     constraint fk_sugg_usr foreign key (usr_id) references usr(usr_id)
155 );
156 
157 create table discountlist
158 (
159     dis_id varchar2(10),
160     goods varchar2(10),
161     disprice number,
162     bgtime date,
163     edtime date,
164     constraint pk_dis primary key (dis_id),
165     constraint pk_disgoods foreign key (goods) references goods(goods_id),
166     constraint ck_disprice check (disprice > 0),
167     constraint ck_edtime check (edtime >= bgtime)
168 );
169 
170 create table unshelve
171 (
172     unshe_id varchar2(10),
173     goods varchar2(10),
174     unshetime date,
175     constraint pk_unshe primary key (unshe_id),
176     constraint fk_unshe_goods foreign key (goods) references goods(goods_id)
177 );
178 
179 create table browselist
180 (
181     usr varchar2(10),
182     goods varchar2(10),
183     browsetime date not null,
184     constraint pk_browse primary key (usr, goods),
185     constraint fk_browse_usr foreign key (usr) references usr(usr_id),
186     constraint fk_browse_goods foreign key (goods) references goods(goods_id)
187 );
188 
189 create table commentlist
190 (
191     comment_id    varchar2(10),
192     buyer varchar2(10),
193     goods varchar2(10),
194     message varchar2(100),
195     commtime date,
196     constraint pk_comment primary key (comment_id),
197     constraint fk_comment_buyer foreign key (buyer) references usr(usr_id),
198     constraint fk_comment_goods    foreign key (goods) references goods(goods_id)
199 );
200 
201 create view deal_view(buyer_nackname, seller_nackname, goods_name, goods_type, goods_describe, deal_price, deal_time, score)
202 as
203 select A.nackname, B.nackname, goods.name, goods.type, goods.describe, goods.price, deal.ordertime, deal.score
204 from usr A, usr B, goods, deal
205 where A.usr_id = deal.buyer_id and
206       goods.goods_id = deal.goods_id and
207       B.usr_id = goods.seller;
208 
209 create view goods_onsale_view(goods_name, goods_type, goods_describe, price, clickrate, seller_nackname, seller_credit, seller_buytime)
210 as
211 select goods.name, goods.type, goods.describe, goods.price, goods.clickrate, usr.nackname, usr.credit, goods.buytime
212 from usr, goods
213 where goods.seller = usr.usr_id and
214       goods.state = 'ÔÚÊÛ';
215 
216 create view discount_view(name, type, describe, discount_price, startTime, endTime,  clickrate, seller_nackname, seller_credit, seller_buytime)
217 as
218 select goods.name, goods.type, goods.describe, discountlist.disprice, discountlist.bgtime, discountlist.edtime, goods.clickrate, usr.nackname, usr.credit, goods.buytime
219 from usr, goods, discountlist
220 where discountlist.goods = goods.goods_id and
221       goods.seller = usr.usr_id;
222 
223 create view collection_view(favorites_name, goods_name, goods_type, goods_describe, price, collect_time)      
224 as
225 select favorites.name, goods.name, goods.type, goods.describe, goods.price, collection.colltime
226 from favorites, collection, goods
227 where favorites.fav_id = collection.fav_id and
228       collection.goods = goods.goods_id;
229 
230 create or replace trigger insert_deal
231     before insert on deal 
232     for each row
233 begin 
234     update goods
235     set goods.state = 'ÊÛ³ö'
236     where goods.goods_id = :new.goods_id;    
237 end;
238 
239 create or replace trigger insert_unshelve
240     before insert on unshelve
241     for each row
242 begin
243     update goods
244     set goods.state = 'δÉϼÜ'
245     where goods.goods_id = :new.goods;
246 end;
247 
248 create or replace trigger insert_discountlist
249     before insert on discountlist
250     for each row
251 begin
252     update goods
253     set goods.price = :new.disprice
254     where goods.goods_id = :new.goods;
255 end;
256 
257 create or replace trigger insert_pwdcglist
258     before insert on pwdcglist
259     for each row
260 begin
261     update usr
262     set usr.pwd = :new.newpwd
263     where usr.usr_id = :new.usr_id;
264 end;
265 
266 create or replace procedure query_onsale_byName(
267     v_goods_name in goods_onsale_view.goods_name%type,
268     out_return out sys_refcursor
269 )as
270 begin 
271     open out_return for 'select *
272     from goods_onsale_view
273     where v_goods_name = goods_onsale_view.goods_name';
274 end;    
275 
276 create or replace procedure query_onsale_byType(
277     v_goods_type in goods_onsale_view.goods_type%type,
278     out_return out sys_refcursor
279 )as
280 begin 
281     open out_return for 'select *
282     from goods_onsale_view
283     where v_goods_type = goods_onsale_view.goods_type';
284 end;
285 
286 --1)ÐÂÓû§×¢²áÒµÎñ
287 create or replace procedure new_usr(
288     v_usr_id in usr.usr_id%type,
289     v_nackname in usr.nackname%type,
290     v_name in usr.name%type,
291     v_gender in usr.gender%type,
292     v_age in usr.age%type,
293     v_sno in usr.sno%type,
294     v_school in usr.school%type,
295     v_college in usr.college%type,
296     v_major in usr.major%type,
297     v_class in usr.class%type,
298     v_phone in usr.phone%type,
299     v_email in usr.email%type,
300     v_credit in usr.credit%type,
301     v_pwd in usr.pwd%type,
302     v_logtime in usr.logtime%type
303 )as
304 begin 
305     insert into usr
306     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);
307     commit;
308 end;    
309 
310 create or replace procedure new_goods(
311     v_goods_id in goods.goods_id%type,
312     v_name in goods.name%type,
313     v_type in goods.type%type,
314     v_seller in goods.seller%type,
315     v_describe in goods.describe%type,
316     v_buytime in goods.buytime%type,
317     v_price in goods.price%type,
318     v_clickrate in goods.clickrate%type,
319     v_state in goods.state%type
320 )as
321 begin 
322     insert into goods
323     values(v_goods_id, v_name, v_type, v_seller, v_describe, v_buytime, v_price, v_clickrate, v_state);
324     commit;
325 end;
326 
327 create or replace procedure new_deal(
328     v_buyer_id in deal.buyer_id%type,
329     v_goods_id in deal.goods_id%type,
330     v_ordertime in deal.ordertime%type,
331     v_arrivaltime in deal.arrivaltime%type,
332     v_score in deal.score%type
333 )as
334 begin 
335     insert into deal
336     values(v_buyer_id, v_goods_id, v_ordertime, v_arrivaltime, v_score);
337     commit;
338 end;
339 
340 create or replace procedure new_login(
341     v_log_id in login.log_id%type,
342     v_usr_id in login.usr_id%type,
343     v_logintime in login.logintime%type,
344     v_logouttime in login.logouttime%type
345 )as
346 begin 
347     insert into login
348     values(v_log_id, v_usr_id, v_logintime, v_logouttime);
349     commit;
350 end;
351 
352 -- declare 
353 --  a login.log_id%type:='1';
354 --     b login.usr_id%type:='2';
355 --     c login.logintime%type:=to_date('2017/01/12', 'yyyy/mm/dd');
356 --     d login.logouttime%type:=to_date('2017/01/13', 'yyyy/mm/dd');
357 -- begin 
358 --   new_login(a, b, c, d);
359 --   dbms_output.put_line(a||b||c||d);
360 -- end;
361 
362 create or replace procedure new_oos(
363     v_oos_id in oos.oos_id%type,
364     v_oosname in oos.oosname%type,
365     v_oostype in oos.oostype%type,
366     v_oosprice in oos.oosprice%type,
367     v_oosbuyer in oos.oosbuyer%type
368 )as
369 begin 
370     insert into oos(oos_id, oosname, oostype, oosprice, oosbuyer)
371     values(v_oos_id, v_oosname, v_oostype, v_oosprice, v_oosbuyer);
372     commit;
373 end;
374 
375 create or replace procedure new_favorites(
376     v_fav_id in favorites.fav_id%type,
377     v_name in favorites.name%type,
378     v_createtime in favorites.createtime%type,
379     v_owner in favorites.owner%type
380 )as
381 begin 
382     insert into favorites
383     values(v_fav_id, v_name, v_createtime, v_owner);
384     commit;
385 end;
386 
387 -- declare 
388 --  a favorites.fav_id%type:='1';
389 --     b favorites.name%type:='СÃ×ÊÖ»·';
390 --     c favorites.createtime%type:=to_date('2017/01/12', 'yyyy/mm/dd');
391 --     d favorites.owner%type:='3';
392 -- begin 
393 --   new_favorites(a, b, c, d);
394 --   dbms_output.put_line(a||b||c||d);
395 -- end;
396 
397 create or replace procedure new_collection(
398     v_coll_id in collection.coll_id%type,
399     v_fav_id in collection.fav_id%type,
400     v_goods in collection.goods%type,
401     v_colltime in collection.colltime%type
402 )as
403 begin 
404     insert into collection
405     values(v_coll_id, v_fav_id, v_goods, v_colltime);
406     commit;
407 end;
408 
409 create or replace procedure new_returnlist(
410     v_re_id in returnlist.re_id%type,
411     v_buyer in returnlist.buyer%type,
412     v_seller in returnlist.seller%type,
413     v_reason in returnlist.reason%type,
414     v_price in returnlist.price%type
415 )as
416 begin 
417     insert into returnlist
418     values(v_re_id, v_buyer, v_seller, v_reason, v_price);
419     commit;
420 end;
421 
422 create or replace procedure new_commlist(
423     v_comm_id in commlist.comm_id%type,
424     v_buyer in commlist.buyer%type,
425     v_seller in commlist.seller%type,
426     v_result in commlist.result%type,
427     v_commtime in commlist.commtime%type
428 )as
429 begin 
430     insert into commlist
431     values(v_comm_id, v_buyer, v_seller, v_result, v_commtime);
432     commit;
433 end;
434 
435 create or replace procedure new_valuelist(
436     v_val_id in valuelist.val_id%type,
437     v_goods_id in valuelist.goods_id%type,
438     v_price in valuelist.price%type
439 )as
440 begin 
441     insert into valuelist
442     values(v_val_id, v_goods_id, v_price);
443     commit;
444 end;
445 
446 create or replace procedure new_pwdcglist(
447     v_pwd_id in pwdcglist.pwd_id%type,
448     v_usr_id in pwdcglist.usr_id%type,
449     v_oldpwd in pwdcglist.oldpwd%type,
450     v_newpwd in pwdcglist.newpwd%type,
451     v_cgtime in pwdcglist.cgtime%type
452 )as
453 begin 
454     insert into pwdcglist
455     values(v_pwd_id, v_usr_id, v_oldpwd, v_newpwd, v_cgtime);
456     commit;
457 end;
458 
459 create or replace procedure new_sugglist(
460     v_sugg_id in sugglist.sugg_id%type,
461     v_usr_id in sugglist.usr_id%type,
462     v_sugg_mess in sugglist.sugg_mess%type,
463     v_dispose in sugglist.dispose%type
464 )as
465 begin 
466     insert into sugglist
467     values(v_sugg_id, v_usr_id, v_sugg_mess, v_dispose);
468     commit;
469 end;
470 
471 create or replace procedure new_discountlist(
472     v_dis_id in discountlist.dis_id%type,
473     v_goods in discountlist.goods%type,
474     v_disprice in discountlist.disprice%type,
475     v_bgtime in discountlist.bgtime%type,
476     v_edtime in discountlist.edtime%type
477 )as
478 begin 
479     insert into discountlist
480     values(v_dis_id, v_goods, v_disprice, v_bgtime, v_edtime);
481     commit;
482 end;
483 
484 create or replace procedure new_unshelve(
485     v_unshe_id in unshelve.unshe_id%type,
486     v_goods in unshelve.goods%type,
487     v_unshetime in unshelve.unshetime%type
488 )as
489 begin 
490     insert into unshelve
491     values(v_unshe_id, v_goods, v_unshetime);
492     commit;
493 end;
494 
495 create or replace procedure new_browselist(
496     v_usr in browselist.usr%type,
497     v_goods in browselist.goods%type,
498     v_browsetime in browselist.browsetime%type
499 )as
500 begin 
501     insert into browselist
502     values(v_usr, v_goods, v_browsetime);
503     commit;
504 end;
505 
506 create or replace procedure new_commentlist(
507     v_comment_id in commentlist.comment_id%type,
508     v_buyer in commentlist.buyer%type,
509     v_goods in commentlist.goods%type,
510     v_message in commentlist.message%type,
511     v_commtime in commentlist.commtime%type
512 )as
513 begin 
514     insert into commentlist
515     values(v_comment_id, v_buyer, v_goods, v_message, v_commtime);
516     commit;
517 end;
518 
519 --Óû§¸ÄÃÜÂëÒµÎñ
520 create or replace procedure change_pwd(
521   v_usr in usr.nackname%type,
522   v_oldpwd in usr.pwd%type,
523   v_newpwd in usr.pwd%type
524 )is v_pwd usr.pwd%type;
525   v_cnt number;
526   v_usr_id usr.usr_id%type;
527 begin
528   select usr_id, pwd into v_usr_id, v_pwd
529   from usr
530   where nackname = v_usr;
531   if(v_pwd = v_oldpwd) then
532     update usr
533     set pwd = v_newpwd
534     where nackname = v_usr;
535 
536     select count(pwd_id) into v_cnt
537     from pwdcglist;
538 
539     new_pwdcglist(to_char(v_cnt+1, '00000'),  v_usr_id, v_oldpwd, v_newpwd, sysdate);
540   end if;
541 end;
542 
543 declare 
544     a usr.nackname%type:='A';
545     b usr.pwd%type:='000000';
546     c usr.pwd%type:='123456';
547 begin 
548   change_pwd(a, b, c);
549   dbms_output.put_line(a||' '||b||' '||c);
550 end;
551 
552 --Âò¼Òä¯ÀÀ²éѯҵÎñ
553 create or replace procedure browse_goods(
554       v_usr in usr.nackname%type,
555       v_goods in goods.name%type
556 )is v_cnt number;
557       v_usr_id usr.usr_id%type;
558       v_type goods.type%type;
559       v_seller usr.name%type;
560       v_describe goods.describe%type;
561       v_buytime goods.buytime%type;
562       v_price goods.price%type;
563       v_clickrate goods.clickrate%type;
564       v_goods_id goods.goods_id%type;
565 cursor c is
566       select goods.goods_id, goods.type, usr.nackname, goods.describe, goods.price, goods.buytime, goods.clickrate
567       from goods, usr
568       where goods.name like '%'||v_goods||'%' and
569             goods.state = 'ÔÚÊÛ' and
570             goods.seller = usr.usr_id;
571 begin 
572       select usr_id into v_usr_id 
573       from usr
574       where nackname = v_usr;
575       open c;
576       fetch c into v_goods_id, v_type, v_seller, v_describe, v_price, v_buytime, v_clickrate;
577       if(c%notfound)then
578            select count(oos_id) into v_cnt
579         from oos;
580         insert into oos values(to_char(v_cnt+1, '00000'), v_goods, 'δ֪', null, v_usr_id);
581         dbms_output.put_line('out of store');
582         --commit;
583       else  
584           dbms_output.put_line(v_goods||' '||v_type||' '||v_seller||' '||v_describe||' '||v_price||' '||v_buytime||' '||v_clickrate);
585           new_browselist(v_usr_id, v_goods_id, sysdate);
586           loop 
587             fetch c into v_goods_id, v_type, v_seller, v_describe, v_price, v_buytime, v_clickrate;
588             exit when c%notfound;
589             dbms_output.put_line(v_goods||' '||v_type||' '||v_seller||' '||v_describe||' '||v_price||' '||v_buytime||' '||v_clickrate);
590             new_browselist(v_usr_id, v_goods_id, sysdate);
591           end loop;
592     end if;
593 end;    
594 
595 
596 declare 
597       a usr.nackname%type:='A';
598     b goods.name%type:='Сѧ';
599 begin 
600       browse_goods(a, b);
601   --dbms_output.put_line(a||' '||b);
602 end;

 

posted @ 2017-01-19 14:12  Penn000  阅读(286)  评论(0编辑  收藏  举报