forked from sisi397/HeendyFoody
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathHeendy_Foody_SQL.sql
1995 lines (1751 loc) · 70.1 KB
/
Heendy_Foody_SQL.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
--------------------------------------------------------
-- 파일이 생성됨 - 수요일-3월-16-2022
--------------------------------------------------------
--------------------------------------------------------
-- DDL for Type CARTIDSARRAY
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE TYPE CARTIDSARRAY AS VARRAY(100) OF NUMBER(20,0)
/
--------------------------------------------------------
-- DDL for Type CATEGORY_ARRAY
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE TYPE CATEGORY_ARRAY as table of varchar2(30);
/
--------------------------------------------------------
-- DDL for Type RECENTVIEWPRODUCTIDSARRAY
-- @Author 이지민
-- 최근 본 상품 아이디를 인자로 받을 array 타입 생성
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE TYPE RECENTVIEWPRODUCTIDSARRAY as varray (100) of number;
/
--------------------------------------------------------
-- DDL for Sequence MEMBER_SEQ
--------------------------------------------------------
CREATE SEQUENCE MEMBER_SEQ MINVALUE 1 MAXVALUE 9999999999999999999 INCREMENT BY 1 START WITH 121 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
--------------------------------------------------------
-- DDL for Sequence CART_SEQ
--------------------------------------------------------
CREATE SEQUENCE CART_SEQ MINVALUE 1 MAXVALUE 9999999999999999999 INCREMENT BY 1 START WITH 121 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
--------------------------------------------------------
-- DDL for Sequence CATEGORY_SEQ
--------------------------------------------------------
CREATE SEQUENCE CATEGORY_SEQ MINVALUE 1 MAXVALUE 9999999999999999999 INCREMENT BY 1 START WITH 81 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
--------------------------------------------------------
-- DDL for Sequence COMPANY_MEMBER_SEQ
--------------------------------------------------------
CREATE SEQUENCE COMPANY_MEMBER_SEQ MINVALUE 1 MAXVALUE 9999999999999999999 INCREMENT BY 1 START WITH 41 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
--------------------------------------------------------
-- DDL for Sequence LOGIN_ROLE_SEQ
--------------------------------------------------------
CREATE SEQUENCE LOGIN_ROLE_SEQ MINVALUE 1 MAXVALUE 9999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
--------------------------------------------------------
-- DDL for Sequence PRODUCT_ORDER_SEQ
--------------------------------------------------------
CREATE SEQUENCE PRODUCT_ORDER_SEQ MINVALUE 1 MAXVALUE 9999999999999999999 INCREMENT BY 1 START WITH 181 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
--------------------------------------------------------
-- DDL for Sequence PRODUCT_SEQ
--------------------------------------------------------
CREATE SEQUENCE PRODUCT_SEQ MINVALUE 1 MAXVALUE 9999999999999999999 INCREMENT BY 1 START WITH 201 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
--------------------------------------------------------
-- DDL for Sequence REVIEW_SEQ
--------------------------------------------------------
CREATE SEQUENCE REVIEW_SEQ MINVALUE 1 MAXVALUE 9999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
--------------------------------------------------------
-- DDL for Table CART
--------------------------------------------------------
CREATE TABLE CART
( CART_ID NUMBER(10,0) DEFAULT CART_SEQ.NEXTVAL,
CART_COUNT NUMBER(10,0),
PRODUCT_ID NUMBER(10,0),
COMPANY_ID NUMBER(10,0),
MEMBER_ID NUMBER(10,0)
);
--------------------------------------------------------
-- DDL for Table CATEGORY
--------------------------------------------------------
CREATE TABLE CATEGORY
( CATEGORY_ID NUMBER(10,0) DEFAULT CATEGORY_SEQ.NEXTVAL,
CATEGORY_NAME NVARCHAR2(30),
PARENT_CATEGORY_ID NUMBER(10,0)
);
--------------------------------------------------------
-- DDL for Table COMPANY_MEMBER
--------------------------------------------------------
CREATE TABLE COMPANY_MEMBER
( COMPANY_ID NUMBER(10,0) DEFAULT COMPANY_MEMBER_SEQ.NEXTVAL,
COMPANY_NAME NVARCHAR2(30),
COMPANY_PASSWORD NVARCHAR2(30),
COMPANY_TEL NVARCHAR2(30),
COMPANY_EMAIL NVARCHAR2(50),
ROLE_ID NUMBER(10,0)
);
--------------------------------------------------------
-- DDL for Table LOGIN_ROLE
--------------------------------------------------------
CREATE TABLE LOGIN_ROLE
( ROLE_ID NUMBER(10,0) DEFAULT LOGIN_ROLE_SEQ.NEXTVAL,
ROLE_NAME NVARCHAR2(30)
);
--------------------------------------------------------
-- DDL for Table MEMBER
--------------------------------------------------------
CREATE TABLE MEMBER
( MEMBER_ID NUMBER(10,0) DEFAULT MEMBER_SEQ.NEXTVAL,
MEMBER_NAME NVARCHAR2(30),
MEMBER_PASSWORD NVARCHAR2(30),
MEMBER_EMAIL NVARCHAR2(50),
ADDRESS NVARCHAR2(50),
POINT NUMBER(10,0) DEFAULT 300,
MEMBER_REG_DATE DATE DEFAULT SYSDATE,
ROLE_ID NUMBER(10,0),
BIRTH_DATE NVARCHAR2(30)
);
--------------------------------------------------------
-- DDL for Table MEMBER_LIKE_PRODUCT
--------------------------------------------------------
CREATE TABLE MEMBER_LIKE_PRODUCT
( MEMBER_ID NUMBER(10,0),
PRODUCT_ID NUMBER(10,0),
COMPANY_ID NUMBER(10,0)
);
--------------------------------------------------------
-- DDL for Table PRODUCT
--------------------------------------------------------
CREATE TABLE PRODUCT
( PRODUCT_ID NUMBER(10,0) DEFAULT PRODUCT_SEQ.NEXTVAL,
COMPANY_ID NUMBER(10,0),
PRODUCT_PRICE NUMBER(10,0),
PRODUCT_NAME NVARCHAR2(30),
IMAGE_URL NVARCHAR2(100),
PRODUCT_COUNT NUMBER(5,0),
PRODUCT_REG_DATE DATE DEFAULT SYSDATE,
DISCOUNT_RATE NUMBER(3,0) DEFAULT 0,
DELETED NUMBER(1,0) DEFAULT 0,
CATEGORY_ID NUMBER(10,0),
DISCOUNT_PRICE NUMBER(10,0) GENERATED ALWAYS AS ("PRODUCT_PRICE"-"PRODUCT_PRICE"*("DISCOUNT_RATE"/100)) VIRTUAL
);
--------------------------------------------------------
-- DDL for Table PRODUCT_ORDER
--------------------------------------------------------
CREATE TABLE PRODUCT_ORDER
( ORDER_ID NUMBER(10,0) DEFAULT PRODUCT_ORDER_SEQ.NEXTVAL,
MEMBER_ID NUMBER(10,0),
ORDER_TIME DATE DEFAULT SYSDATE,
ORDER_COUNT NUMBER(10,0),
ORDER_PRICE NUMBER(10,0),
PRODUCT_ID NUMBER(10,0),
COMPANY_ID NUMBER(10,0)
);
--------------------------------------------------------
-- DDL for View V_PRODUCT
--------------------------------------------------------
--------------------------------------------------------
-- @Author 김시은
-- 상품 정보를 담은 View
--------------------------------------------------------
create or replace view v_product
as
select p.product_id, p.company_id, company_name, product_price, discount_price, product_name,
image_url, product_count, product_reg_date, discount_rate, deleted, c.* , nvl(l.like_count,0) like_count
from product p
left join
(select mlp.product_id, count(*) as like_count from member_like_product mlp group by mlp.product_id) l
on p.product_id = l.product_id,
company_member cm,
(select c2.*, c1.category_name as parent_category_name from category c1, category c2 where c1.category_id = c2.parent_category_id) c
where p.company_id = cm.company_id and p.category_id = c.category_id;
--------------------------------------------------------
-- DDL for Index CART_MEID_IDX
--------------------------------------------------------
CREATE INDEX CART_MEID_IDX ON CART (MEMBER_ID);
--------------------------------------------------------
-- DDL for Index MLP_PID
--------------------------------------------------------
CREATE INDEX MLP_PID ON MEMBER_LIKE_PRODUCT (PRODUCT_ID);
--------------------------------------------------------
-- DDL for Index PK_CART
--------------------------------------------------------
CREATE UNIQUE INDEX PK_CART ON CART (CART_ID);
--------------------------------------------------------
-- DDL for Index PK_CATEGORY
--------------------------------------------------------
CREATE UNIQUE INDEX PK_CATEGORY ON CATEGORY (CATEGORY_ID);
--------------------------------------------------------
-- DDL for Index PK_COMPANY_MEMBER
--------------------------------------------------------
CREATE UNIQUE INDEX PK_COMPANY_MEMBER ON COMPANY_MEMBER (COMPANY_ID);
--------------------------------------------------------
-- DDL for Index PK_LIKE
--------------------------------------------------------
CREATE UNIQUE INDEX PK_LIKE ON MEMBER_LIKE_PRODUCT (MEMBER_ID, PRODUCT_ID, COMPANY_ID);
--------------------------------------------------------
-- DDL for Index PK_LOGIN_ROLE
--------------------------------------------------------
CREATE UNIQUE INDEX PK_LOGIN_ROLE ON LOGIN_ROLE (ROLE_ID);
--------------------------------------------------------
-- DDL for Index PK_MEMBER
--------------------------------------------------------
CREATE UNIQUE INDEX PK_MEMBER ON MEMBER (MEMBER_ID);
--------------------------------------------------------
-- DDL for Index PK_ORDER
--------------------------------------------------------
CREATE UNIQUE INDEX PK_ORDER ON PRODUCT_ORDER (ORDER_ID, MEMBER_ID);
--------------------------------------------------------
-- DDL for Index PK_PRODUCT
--------------------------------------------------------
CREATE UNIQUE INDEX PK_PRODUCT ON PRODUCT (PRODUCT_ID, COMPANY_ID);
--------------------------------------------------------
-- DDL for Index PRODUCT_CATEID_IDX
--------------------------------------------------------
CREATE INDEX PRODUCT_CATEID_IDX ON PRODUCT (CATEGORY_ID);
--------------------------------------------------------
-- DDL for Index UK_MEMBER_EMAIL
--------------------------------------------------------
CREATE UNIQUE INDEX UK_MEMBER_EMAIL ON MEMBER (MEMBER_EMAIL);
--------------------------------------------------------
-- DDL for Index UK_MEMBER_NAME
--------------------------------------------------------
CREATE UNIQUE INDEX UK_MEMBER_NAME ON MEMBER (MEMBER_NAME);
--------------------------------------------------------
-- DDL for Index CART_MEID_IDX
--------------------------------------------------------
CREATE INDEX CART_MEID_IDX ON CART (MEMBER_ID);
--------------------------------------------------------
-- DDL for Index PK_CART
--------------------------------------------------------
CREATE UNIQUE INDEX PK_CART ON CART (CART_ID);
--------------------------------------------------------
-- DDL for Index PK_CATEGORY
--------------------------------------------------------
CREATE UNIQUE INDEX PK_CATEGORY ON CATEGORY (CATEGORY_ID);
--------------------------------------------------------
-- DDL for Index PK_COMPANY_MEMBER
--------------------------------------------------------
CREATE UNIQUE INDEX PK_COMPANY_MEMBER ON COMPANY_MEMBER (COMPANY_ID) ;
--------------------------------------------------------
-- DDL for Index PK_LOGIN_ROLE
--------------------------------------------------------
CREATE UNIQUE INDEX PK_LOGIN_ROLE ON LOGIN_ROLE (ROLE_ID);
--------------------------------------------------------
-- DDL for Index UK_MEMBER_NAME
--------------------------------------------------------
CREATE UNIQUE INDEX UK_MEMBER_NAME ON MEMBER (MEMBER_NAME);
--------------------------------------------------------
-- DDL for Index UK_MEMBER_EMAIL
--------------------------------------------------------
CREATE UNIQUE INDEX UK_MEMBER_EMAIL ON MEMBER (MEMBER_EMAIL) ;
--------------------------------------------------------
-- DDL for Index PK_MEMBER
--------------------------------------------------------
CREATE UNIQUE INDEX PK_MEMBER ON MEMBER (MEMBER_ID);
--------------------------------------------------------
-- DDL for Index MLP_PID
--------------------------------------------------------
CREATE INDEX MLP_PID ON MEMBER_LIKE_PRODUCT (PRODUCT_ID);
--------------------------------------------------------
-- DDL for Index PK_LIKE
--------------------------------------------------------
CREATE UNIQUE INDEX PK_LIKE ON MEMBER_LIKE_PRODUCT (MEMBER_ID, PRODUCT_ID, COMPANY_ID);
--------------------------------------------------------
-- DDL for Index PRODUCT_CATEID_IDX
--------------------------------------------------------
CREATE INDEX PRODUCT_CATEID_IDX ON PRODUCT (CATEGORY_ID);
--------------------------------------------------------
-- DDL for Index PK_PRODUCT
--------------------------------------------------------
CREATE UNIQUE INDEX PK_PRODUCT ON PRODUCT (PRODUCT_ID, COMPANY_ID);
--------------------------------------------------------
-- DDL for Index PK_ORDER
--------------------------------------------------------
CREATE UNIQUE INDEX PK_ORDER ON PRODUCT_ORDER (ORDER_ID, MEMBER_ID);
--------------------------------------------------------
-- DDL for Trigger TR_CHECK_PRODUCT_INSERT_TIME
-- @Author 이지민
-- 상품 등록 전 시간을 체크하는 Trigger
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE TRIGGER TR_CHECK_PRODUCT_INSERT_TIME
before insert or update on product
declare
server_check_time exception;
pragma exception_init(server_check_time, -20013);
begin
/* 해당 시간대에 작업이 시행되면 에러를 발생시킨다 */
if(to_char(sysdate, 'day') in ('월요일') and
to_char(sysdate, 'hh24') >= 9 and to_char(sysdate, 'hh24') <= 10) then
raise server_check_time;
end if;
exception
when server_check_time then
dbms_output.put_line('ora'||sqlcode||': 서버 점검 시간입니다.' );
raise_application_error(sqlcode,'서버 점검 시간입니다.');
when others then
raise;
end;
/
ALTER TRIGGER TR_CHECK_PRODUCT_INSERT_TIME ENABLE;
--------------------------------------------------------
-- DDL for Procedure SP_LIST_CATEGORY
-- @Author 김시은, 문석호
-- 카테고리 전체 목록 조회
--------------------------------------------------------
set define off;
CREATE OR REPLACE EDITIONABLE PROCEDURE SP_LIST_CATEGORY
(p_category_id category.category_id%type,
p_parent_category_id category.parent_category_id%type,
p_category_name out varchar2,
p_category_info out SYS_REFCURSOR)
is
begin
/* 전체 카테고리 조회 (부모 카테고리 ID먼저 오름차순 정렬 후 자식 카테고리 ID를 오름차순 정렬한다.) */
if p_category_id = 0 and p_parent_category_id = 0 then
open p_category_info for
select *
from category
order by parent_category_id asc, category_id asc;
else
/* 현재 카테고리 명 조회 */
select category_name into p_category_name
from category
where parent_category_id = p_parent_category_id and category_id = p_category_id;
/* 카테고리 정보 조회 */
open p_category_info for
select *
from category
where parent_category_id = p_parent_category_id
order by category_id asc;
end if;
end;
/
--------------------------------------------------------
-- DDL for Package PACK_ENCRYPTION_DECRYPTION
-- @Author 문석호
-- 비밀번호 암호화 패키지
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE PACKAGE PACK_ENCRYPTION_DECRYPTION
IS
FUNCTION FUNC_ENCRYPT --암호화
(INPUT_STRING IN VARCHAR2)
RETURN RAW;
FUNCTION FUNC_DECRYPT -- 복호화
(INPUT_STRING IN VARCHAR2)
RETURN VARCHAR2;
END PACK_ENCRYPTION_DECRYPTION;
/
--------------------------------------------------------
-- DDL for Package PKG_CART
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE PACKAGE PKG_CART
is
/* 장바구니 리스트 */
PROCEDURE sp_select_cart_list (
p_member_id member.member_id%TYPE,
p_result OUT sys_refcursor
);
/* 장바구니 생성 */
PROCEDURE sp_create_cart (
p_productId cart.PRODUCT_ID%TYPE,
p_companyId cart.COMPANY_ID%TYPE,
p_memberID cart.MEMBER_ID%TYPE,
p_count cart.CART_COUNT%TYPE
);
/* 장바구니 삭제 */
PROCEDURE sp_delete_cart (
p_cart_id CART.CART_ID%TYPE,
p_member_id MEMBER.MEMBER_ID%TYPE
);
/* 장바구니 수량 추가 */
PROCEDURE sp_add_cart (
p_cart_id CART.CART_ID%TYPE,
p_cart_count CART.CART_COUNT%TYPE,
p_member_id MEMBER.MEMBER_ID%TYPE
);
/* 장바구니 수량 감소 */
PROCEDURE sp_minus_cart (
p_cart_id CART.CART_ID%TYPE,
p_cart_count CART.CART_COUNT%TYPE,
p_member_id MEMBER.MEMBER_ID%TYPE
);
end;
/
--------------------------------------------------------
-- DDL for Package PKG_COMPANY
-- @Author 김시은
-- 업체회원 페이지 차트 그리기 위한 데이터 : 구매자 연령층, 날짜별 상품 판매량
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE PACKAGE PKG_COMPANY
is
/* 구매자 연령층 */
PROCEDURE SP_MEMBER_AGEINFO (
p_company_id product_order.company_id%type,
p_member_ageinfo out sys_refcursor
);
/* 상품 판매량 */
PROCEDURE SP_PRODUCT_ORDERINFO (
p_company_id product_order.company_id%type,
p_product_id product_order.product_id%type,
p_date_sort varchar2,
p_product_orderinfo out sys_refcursor
);
end;
/
--------------------------------------------------------
-- DDL for Package PKG_MEMBER
-- @Author 문석호, 이지민
-- 회원 관련 패키지
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE PACKAGE PKG_MEMBER
is
--일반 회원 가입
PROCEDURE sp_add_member
(p_member_name MEMBER.MEMBER_NAME%TYPE,
p_member_password MEMBER.member_password%TYPE,
p_member_email MEMBER.member_email%TYPE,
p_address MEMBER.address%TYPE,
p_role_id MEMBER.role_id%TYPE,
p_birth_date MEMBER.birth_date%TYPE);
-- 일반 회원 로그인
PROCEDURE sp_member_IsExisted
(p_member_name MEMBER.MEMBER_NAME%TYPE,
p_member_password MEMBER.member_password%TYPE,
v_member_out out integer
);
--일반 회원 비밀번호 찾기
PROCEDURE sp_member_findPw
(p_member_name MEMBER.MEMBER_NAME%TYPE,
p_member_email MEMBER.member_email%TYPE,
v_member_pw_out out varchar2
);
--일반 회원 아이디 찾기
PROCEDURE sp_member_findId
(p_member_email MEMBER.member_email%TYPE,
v_member_id_out out varchar2
);
--일반 회원 아이디 중복체크
PROCEDURE sp_member_duplicatedId
(p_member_name MEMBER.member_name%TYPE,
v_member_idCheck out integer
);
-- 일반 회원 정보 불러오기
PROCEDURE sp_member_getmember
(p_member_name MEMBER.member_name%TYPE,
v_member_info_out out SYS_REFCURSOR
);
--------------------------------------------------------
-- @Author 이지민
-- 회원 포인트를 조회하는 Procedure
--------------------------------------------------------
-- 일반 회원 포인트 조회
procedure sp_select_member_point (
p_memberId member.member_id%type,
p_point out Number
);
--업체 회원 회원가입
procedure sp_add_company_member
(p_company_name company_member.company_name%type,
p_company_password company_member.company_password%type,
p_company_tel company_member.company_tel%type,
p_company_email company_member.company_email%type,
p_role_id company_member.role_id%type);
-- 업체 회원 아이디 중복 확인
PROCEDURE sp_company_duplicatedId
(p_company_name company_member.company_name%TYPE,
v_company_idCheck out Integer
);
--업체 회원 로그인
PROCEDURE sp_company_isExisted
(p_company_name company_member.company_name%TYPE,
p_company_password company_member.company_password%type,
v_company_out out Integer
);
--업체 회원 정보 가져오기
PROCEDURE sp_company_getcompany
(p_company_name company_member.company_name%TYPE,
v_company_info_out out sys_refcursor
);
end;
/
--------------------------------------------------------
-- DDL for Package PKG_ORDER
-- @Author 이승준, 이지민
-- 주문 내역 관련 패키지
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE PACKAGE PKG_ORDER
is
--------------------------------------------------------
-- @Author 이지민
-- 주문내역 리스트를 불러오는 Procedure
--------------------------------------------------------
/* 주문내역 리스트 */
procedure sp_list_order (
p_beginRow Number,
p_endRow Number,
p_memberId product_order.member_id%type,
p_list_order out sys_refcursor
);
/* 주문내역 생성(바로주문) */
procedure sp_create_order (
p_product_id product.product_id%TYPE,
p_company_id product.company_id%TYPE,
p_member_id MEMBER.MEMBER_ID%TYPE,
p_order_count PRODUCT_ORDER.ORDER_COUNT%TYPE
);
/* 주문내역 생성(장바구니 주문) */
procedure sp_create_order_from_cart (
p_member_id member.member_id%TYPE,
p_cart_array cartIdsArray
);
--------------------------------------------------------
-- @Author 이지민
-- 총 주문내역 개수를 가져오는 Procedure
--------------------------------------------------------
/* 총 주문내역 개수 */
procedure sp_totalcount_order (
p_memberId product_order.member_id%type,
p_totalCount out Number
);
end;
/
--------------------------------------------------------
-- DDL for Package PKG_PRODUCT
-- @Author 김시은, 이승준, 이지민
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE PACKAGE PKG_PRODUCT
is
--------------------------------------------------------
-- @Author 김시은
-- 상품 리스트를 불러오는 Procedure
--------------------------------------------------------
/* 상품 리스트 */
procedure sp_list_product
(p_beginRow Number,
p_endRow Number,
p_sort varchar2,
p_menu VARCHAR2,
p_cate v_product.category_id%type,
p_parent_cate v_product.parent_category_id%type,
p_list_product out sys_refcursor);
--------------------------------------------------------
-- @Author 김시은
-- 업체별 상품 목록을 불러오는 Procedure
--------------------------------------------------------
/* 업체별 상품 목록 */
PROCEDURE SP_COMPANY_PRODUCT (
p_company_id product_order.company_id%type,
p_company_product out sys_refcursor
);
--------------------------------------------------------
-- @Author 김시은
-- 상품 상세 페이지의 상품 정보를 불러오는 Procedure
--------------------------------------------------------
/* 상품 정보 */
procedure sp_select_product
(p_product_id v_product.product_id%type,
p_company_id v_product.company_id%type,
p_result out sys_refcursor);
--------------------------------------------------------
-- @Author 김시은
-- 상품 전체 개수를 불러오는 Procedure
--------------------------------------------------------
/* 상품 전체 개수 */
PROCEDURE SP_TOTALCOUNT_PRODUCT (
p_menu VARCHAR2,
p_cate v_product.category_id%type,
p_parent_cate v_product.parent_category_id%type,
p_totalcount out Number);
/* 상품 등록 */
PROCEDURE sp_create_product(
p_companyId company_member.company_id%TYPE,
p_product_name product.product_name%TYPE,
p_product_price product.product_price%TYPE,
p_discount_rate product.discount_rate%TYPE,
p_product_count product.product_count%TYPE,
p_image_url product.image_url%TYPE,
p_category_id category.category_id%TYPE
);
--------------------------------------------------------
-- @Author 이지민
-- 최근 본 상품 리스트를 불러오는 Procedure
--------------------------------------------------------
/* 최근 본 상품 목록 */
procedure sp_list_recent_view_product (
p_recent_view_array recentViewProductIdsArray,
p_list_recent_view_product out sys_refcursor
);
end;
/
--------------------------------------------------------
-- DDL for Package PKG_WISH
-- @Author 김시은, 이지민
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE PACKAGE PKG_WISH
is
--------------------------------------------------------
-- @Author 김시은
-- 좋아요 여부를 불러오는 Procedure
--------------------------------------------------------
/* 좋아요 여부 */
procedure sp_check_wish(
p_member_id member_like_product.member_id%type,
p_product_id member_like_product.product_id%type,
p_company_id member_like_product.company_id%type,
p_count out number
);
--------------------------------------------------------
-- @Author 김시은
-- 상품정보와 사용자 정보를 가지고 좋아요를 삽입하는 Procedure
--------------------------------------------------------
/* 좋아요 삽입 */
procedure sp_insert_wish(
p_member_id member_like_product.member_id%type,
p_product_id member_like_product.product_id%type,
p_company_id member_like_product.company_id%type
);
--------------------------------------------------------
-- @Author 김시은
-- 상품정보와 사용자 정보를 가지고 좋아요를 삭제하는 Procedure
--------------------------------------------------------
/* 좋아요 삭제 */
procedure sp_delete_wish(
p_member_id member_like_product.member_id%type,
p_product_id member_like_product.product_id%type,
p_company_id member_like_product.company_id%type
);
--------------------------------------------------------
-- @Author 이지민
-- 좋아요한 상품 내역을 불러오는 Procedure
--------------------------------------------------------
/* 좋아요 리스트 */
procedure sp_list_wish (
p_beginRow Number,
p_endRow Number,
p_memberId member_like_product.member_id%type,
p_list_wish out sys_refcursor
);
--------------------------------------------------------
-- @Author 이지민
-- 좋아요한 상품의 총 개수를 불러오는 Procedure
--------------------------------------------------------
/* 좋아요 총 개수 */
procedure sp_totalcount_wish (
p_memberId member_like_product.member_id%type,
p_totalCount out Number
);
end;
/
--------------------------------------------------------
-- DDL for Package Body PACK_ENCRYPTION_DECRYPTION
-- @Author 문석호
-- DBMS_CRYPTO의 비밀번호 암호화/복호화 함수
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE PACKAGE BODY PACK_ENCRYPTION_DECRYPTION
IS
FUNCTION FUNC_ENCRYPT -- 암호화 (암호화할 스트링을 인자로 받음)
(INPUT_STRING IN VARCHAR2)
RETURN RAW
IS
ORIGNAL_RAW RAW(64); -- RAW타입 변수 선언: 원본을 담을 변수(암호화할 데이터)
KEY_DATA_RAW RAW(64); -- 암호화키를 담을 변수
ENCRYTED_RAW RAW(64); -- 암호화된 데이터
BEGIN
-- INPUT_STRING을 아래와 같이 RAW 타입으로 변경.
ORIGNAL_RAW := UTL_I18N.STRING_TO_RAW(INPUT_STRING, 'AL32UTF8'); -- UTL_I18N :국가, 언어들 간의 다양한 호환 기능 제공 패키지
KEY_DATA_RAW := UTL_I18N.STRING_TO_RAW('HeendyUser01$', 'AL32UTF8');
-- 암호화해서 encrypted_raw에 저장
ENCRYTED_RAW := DBMS_CRYPTO.ENCRYPT(SRC => ORIGNAL_RAW,
TYP => DBMS_CRYPTO.DES_CBC_PKCS5,
KEY => KEY_DATA_RAW,
IV => NULL);
-- =>는 := 과 같은 의미로 본다.
RETURN ENCRYTED_RAW; -- 암호화 결과를 리턴.
END FUNC_ENCRYPT;
FUNCTION FUNC_DECRYPT -- 복호화 하는 함수
(INPUT_STRING IN VARCHAR2)
RETURN VARCHAR2
IS
KEY_DATA_RAW RAW(64);
DECRYPTED_RAW RAW(64);
CONVERTED_STRING VARCHAR2(64);
BEGIN
KEY_DATA_RAW := UTL_I18N.STRING_TO_RAW('HeendyUser01$', 'AL32UTF8');
DECRYPTED_RAW := DBMS_CRYPTO.DECRYPT(SRC => INPUT_STRING,
TYP => DBMS_CRYPTO.DES_CBC_PKCS5,
KEY => KEY_DATA_RAW,
IV => NULL);
-- RAW 타입을 STRING 타입으로 변경( 복호화한 결과를 varchar2로 변환해서 converted_string에 넣음)
CONVERTED_STRING := UTL_I18N.RAW_TO_CHAR(DECRYPTED_RAW, 'AL32UTF8');
RETURN CONVERTED_STRING; -- 복호화된 결과를 리턴
END FUNC_DECRYPT; -- 복호화 함수 종료
END PACK_ENCRYPTION_DECRYPTION;
/
--------------------------------------------------------
-- DDL for Package Body PKG_CART
-- @Author 이승준
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE PACKAGE BODY PKG_CART
is
/* 장바구니 리스트 */
--------------------------------------------------------
-- @Author 이승준
-- 장바구니 리스트 조회
--------------------------------------------------------
PROCEDURE sp_select_cart_list (
p_member_id member.member_id%TYPE,
p_result OUT sys_refcursor)
IS
BEGIN
OPEN p_result FOR
SELECT
c.cart_id cart_id,
c.product_id product_id,
c.company_id company_id,
c.cart_count cart_count,
p.product_count product_count,
p.product_name product_name,
p.image_url image_url,
p.product_price product_price,
p.discount_price discount_price,
p.deleted deleted
FROM cart c, product p
WHERE
c.member_id = p_member_id AND
c.product_id = p.product_id AND
c.company_id = p.company_id;
END;
/* 장바구니 생성 */
--------------------------------------------------------
-- @Author 이승준
-- 장바구니 생성
--------------------------------------------------------
PROCEDURE sp_create_cart
(p_productId cart.PRODUCT_ID%TYPE, p_companyId cart.COMPANY_ID%TYPE, p_memberID cart.MEMBER_ID%TYPE, p_count cart.CART_COUNT%TYPE)
IS
v_count NUMBER := 0;
v_product_count product.product_count%TYPE;
ALREADY_CART_EXIST EXCEPTION;
PRAGMA EXCEPTION_INIT(ALREADY_CART_EXIST,-20000);
LACK_OF_STOCK EXCEPTION;
PRAGMA EXCEPTION_INIT(LACK_OF_STOCK,-20001);
BEGIN
/*해당 사용자가 담을려는 상품이 이미 장바구니에 있는지 확인*/
SELECT count(*) INTO v_count FROM cart
WHERE MEMBER_ID = p_memberID AND PRODUCT_ID = p_productId AND COMPANY_ID = p_companyId;
/*이미 장바구니에 상품이 있으면 예외 발생*/
IF v_count >= 1 THEN
RAISE ALREADY_CART_EXIST;
END IF;
/*상품 재고 조회*/
SELECT PRODUCT_COUNT INTO v_product_count
FROM PRODUCT
WHERE
PRODUCT_ID = p_productId AND COMPANY_ID = p_companyId;
IF p_count > v_product_count THEN
RAISE LACK_OF_STOCK;
END IF;
/*장바구니에 상품이 없을시 상품 추가*/
INSERT INTO CART(PRODUCT_ID, COMPANY_ID, MEMBER_ID, CART_COUNT) VALUES(p_productId,p_companyId,p_memberID,p_count);
COMMIT;
EXCEPTION
WHEN ALREADY_CART_EXIST THEN
DBMS_OUTPUT.PUT_LINE('ORA'||SQLCODE||': 이미 장바구니에 같은 물품이 존재합니다.' );
RAISE_APPLICATION_ERROR(SQLCODE,'이미 장바구니에 같은 물품이 존재합니다.');
ROLLBACK;
WHEN LACK_OF_STOCK THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('ORA'||SQLCODE||': 상품 재고가 부족합니다.' );
RAISE_APPLICATION_ERROR(SQLCODE,'상품 재고가 부족합니다.');
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('ORA'||SQLCODE||': '||SQLERRM);
RAISE;
END;
/* 장바구니 삭제 */
--------------------------------------------------------
-- @Author 이승준
-- 장바구니 삭제
--------------------------------------------------------
PROCEDURE sp_delete_cart (
p_cart_id CART.CART_ID%TYPE,
p_member_id MEMBER.MEMBER_ID%TYPE)
IS
v_cart_member_id CART.MEMBER_ID%TYPE;
NOT_RESOURCE_OWNER EXCEPTION;
PRAGMA EXCEPTION_INIT(NOT_RESOURCE_OWNER,-20090);
BEGIN
SELECT MEMBER_ID INTO v_cart_member_id
FROM CART
WHERE CART_ID = p_cart_id FOR UPDATE;
/*삭제하려는 장바구니가 요청한 사용자와 생성한 사용자와 일치하는지 확인*/
IF v_cart_member_id != p_member_id THEN
RAISE NOT_RESOURCE_OWNER;
END IF;
DELETE FROM CART WHERE CART_ID = p_cart_id;
COMMIT;
EXCEPTION
WHEN NOT_RESOURCE_OWNER THEN
ROLLBACK ;
DBMS_OUTPUT.PUT_LINE('ORA'||SQLCODE||': 해당 리소스의 소유자가 아닙니다.' );
RAISE_APPLICATION_ERROR(SQLCODE,'해당 리소스의 소유자가 아닙니다.');
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/* 장바구니 수량 추가 */
--------------------------------------------------------
-- @Author 이승준
-- 장바구니 수량 증가
--------------------------------------------------------
PROCEDURE sp_add_cart
(p_cart_id CART.CART_ID%TYPE,
p_cart_count CART.CART_COUNT%TYPE,
p_member_id MEMBER.MEMBER_ID%TYPE
)
IS
TYPE cart_info_type IS RECORD (
cart_id cart.CART_ID%TYPE,
product_id cart.PRODUCT_ID%TYPE,
company_id cart.COMPANY_ID%TYPE,
cart_count cart.CART_COUNT%TYPE,
member_id cart.MEMBER_ID%TYPE
);
v_cart_info cart_info_type;
v_product_count PRODUCT.PRODUCT_ID%TYPE := 0;
v_cart_count CART.CART_COUNT%TYPE := 0;
LACK_OF_STOCK EXCEPTION;
PRAGMA EXCEPTION_INIT(LACK_OF_STOCK,-20001);
NOT_RESOURCE_OWNER EXCEPTION;
PRAGMA EXCEPTION_INIT(NOT_RESOURCE_OWNER,-20090);
BEGIN
SELECT CART_ID, PRODUCT_ID, COMPANY_ID, CART_COUNT, MEMBER_ID INTO v_cart_info
FROM CART WHERE CART_ID = p_cart_id FOR UPDATE;
/*장바구니 소유자와 변경을 요청한 사용자와 같은지 확인*/
IF v_cart_info.member_id != p_member_id THEN
RAISE NOT_RESOURCE_OWNER;
END IF;
v_cart_count := v_cart_info.cart_count;
/*상품 재고 조회*/
SELECT PRODUCT_COUNT INTO v_product_count
FROM PRODUCT
WHERE
PRODUCT_ID = v_cart_info.product_id AND COMPANY_ID = v_cart_info.company_id;
v_cart_count := v_cart_count + p_cart_count;
/*
* 1. 현재 상품재고와 장바구니에 담을려는 수량 비교
* 2. 현재 상품재고보다 장바구니에 담을려는 수량이 많을 시 예외 발생
* */
IF v_cart_count > v_product_count THEN
RAISE LACK_OF_STOCK;
END IF;
UPDATE CART SET CART_COUNT = v_cart_count WHERE CART_ID = p_cart_id;
COMMIT;
EXCEPTION
WHEN NOT_RESOURCE_OWNER THEN
ROLLBACK ;
DBMS_OUTPUT.PUT_LINE('ORA'||SQLCODE||': 해당 리소스의 소유자가 아닙니다.' );
RAISE_APPLICATION_ERROR(SQLCODE,'해당 리소스의 소유자가 아닙니다.');
WHEN LACK_OF_STOCK THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('ORA'||SQLCODE||': 상품 재고가 부족합니다.' );
RAISE_APPLICATION_ERROR(SQLCODE,'상품 재고가 부족합니다.');
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('ORA'||SQLCODE||': '||SQLERRM);
RAISE;
END;
/* 장바구니 수량 감소 */
--------------------------------------------------------
-- @Author 이승준
-- 장바구니 수량 감소
--------------------------------------------------------
PROCEDURE sp_minus_cart (
p_cart_id CART.CART_ID%TYPE,
p_cart_count CART.CART_COUNT%TYPE,
p_member_id MEMBER.MEMBER_ID%TYPE)
IS
TYPE cart_info_type IS RECORD (
cart_count CART.CART_COUNT%TYPE,
member_id CART.MEMBER_ID%TYPE