Everything has an expiration date
[Oracle] 20240319 [FinalProject 완료] - 작성한 SQL 파일 전체 (교육센터 수료일) 본문
카테고리 없음
[Oracle] 20240319 [FinalProject 완료] - 작성한 SQL 파일 전체 (교육센터 수료일)
Jelly-fish 2024. 3. 19. 15:21finalProject_scott_1.sql
CREATE TABLE "MEMBER" (
"MEM_CODE" VARCHAR2(10) NOT NULL,
"ID" VARCHAR2(20) NULL,
"PW" VARCHAR2(20) NULL,
"NICKNAME" VARCHAR2(20) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"MBTI_CODE" VARCHAR2(10) NULL
);
CREATE TABLE "App_Opening" (
"AP_CODE" VARCHAR2(10) NOT NULL,
"TITLE" VARCHAR2(100) NULL,
"SUMMARY" VARCHAR2(5000) NULL,
"SDATE" DATE DEFAULT SYSDATE NULL,
"EDATE" DATE NULL,
"CONTENT" VARCHAR2(10000) NULL,
"KDATE" DATE NULL,
"NUMBER" NUMBER NULL,
"VIEW" NUMBER NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "CAREER" (
"C_CODE" VARCHAR2(10) NOT NULL,
"NAME" VARCHAR2(50) NULL,
"KDATE" DATE NULL,
"ISC_OPEN" NUMBER NULL,
"SDATE" DATE NULL,
"EDATE" DATE NULL,
"MI_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "B_REPORT" (
"BR_CODE" VARCHAR2(10) NOT NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"CONTENT" VARCHAR2(10000) NULL,
"SUMMARY" VARCHAR2(5000) NULL,
"TASK_CODE" VARCHAR2(10) NOT NULL,
"BR_CODE2" VARCHAR2(10) NOT NULL
);
CREATE TABLE "OPEN_TAG" (
"OT_CODE" VARCHAR2(10) NOT NULL,
"PL_CODE" VARCHAR2(10) NOT NULL,
"AP_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "PTAG_LIST" (
"PL_CODE" VARCHAR2(10) NOT NULL,
"NAME" VARCHAR2(100) NULL
);
CREATE TABLE "MEMBER_APPLY" (
"MA_CODE" VARCHAR2(10) NOT NULL,
"CONTENT" VARCHAR2(10000) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"DDATE" DATE NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL,
"RC_CODE" VARCHAR2(10) NOT NULL,
"AR_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "AO_COMMENT" (
"AC_CODE" VARCHAR2(10) NULL,
"CONTENT" VARCHAR2(5000) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"AP_CODE" VARCHAR2(10) NOT NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "AO_REPLY2" (
"A2_CODE" VARCHAR2(10) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"CONTENT" VARCHAR2(5000) NULL,
"AC_CODE" VARCHAR2(10) NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "MBTI" (
"MBTI_CODE" VARCHAR2(10) NULL,
"KMATE" VARCHAR2(10) NULL
);
CREATE TABLE "W_MEMBERLIST" (
"WM_CODE" VARCHAR2(10) NOT NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"MA_CODEA" VARCHAR2(10) NOT NULL,
"MA_CODEP" VARCHAR2(10) NOT NULL,
"WR_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "W_REASON" (
"WR_CODE" VARCHAR2(10) NOT NULL,
"REASON" VARCHAR2(100) NULL,
"PE_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "P_TARGET" (
"PTG_CODE" VARCHAR2(10) NULL,
"TARGET" VARCHAR2(50) NULL
);
CREATE TABLE "TECH_QnA" (
"TQ_CODE" VARCHAR2(10) NOT NULL,
"TITLE" VARCHAR2(100) NULL,
"CONTENT" VARCHAR2(10000) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"VIEW" NUMBER DEFAULT 0 NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "C_QnA" (
"CQ_CODE" VARCHAR2(10) NOT NULL,
"TITLE" VARCHAR2(100) NULL,
"CONTENT" VARCHAR2(10000) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"VIEW" NUMBER NULL,
"R_CODE" VARCHAR2(10) NOT NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "PORTFOLIO" (
"P_CODE" VARCHAR2(10) NOT NULL,
"TITLE" VARCHAR2(100) NOT NULL,
"CONTENT" VARCHAR2(10000) NOT NULL,
"KDATE" DATE DEFAULT SYSDATE NOT NULL,
"VIEW" NUMBER DEFAULT 0 NOT NULL,
"MEMBER_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "CVOTE" (
"V_CODE" VARCHAR2(10) NOT NULL,
"TITLE" VARCHAR2(1000) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"CQ_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "TQ_COMMENT" (
"TC_CODE" VARCHAR(255) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"COMMENT" VARCHAR2(5000) NULL,
"TQ_CODE" VARCHAR2(10) NOT NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "TQ_REPLY2" (
"T2_CODE" VARCHAR2(10) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"COMMENT" VARCHAR2(5000) NULL,
"TC_CODE" VARCHAR2(10) NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "CQ_COMMENT" (
"CQC_CODE" VARCHAR2(10) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"COMMENT" VARCHAR2(5000) NULL,
"CQ_CODE" VARCHAR2(10) NOT NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "CQ_REPLY2" (
"CQ2_CODE" VARCHAR2(10) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"COMMENT" VARCHAR2(5000) NULL,
"CQC_CODE" VARCHAR2(10) NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "CVOTE_P" (
"VP_CODE" VARCHAR2(10) NOT NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"VS_CODE" VARCHAR2(10) NOT NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "CVOTE_SEL" (
"VS_CODE" VARCHAR2(10) NOT NULL,
"CONTENT" VARCHAR2(500) NULL,
"V_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "RESUME" (
"R_CODE" VARCHAR2(10) NOT NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL,
"P_CODE" VARCHAR2(10) NOT NULL,
"INTRO" VARCHAR2(1000) NULL
);
CREATE TABLE "ST_LIST" (
"SL_CODE" VARCHAR2(10) NULL,
"NAME" VARCHAR2(100) NULL
);
CREATE TABLE "P_TAG" (
"PT_CODE" VARCHAR2(10) NOT NULL,
"P_CODE" VARCHAR2(10) NOT NULL,
"PTL_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "P_COMMENT" (
"PC_CODE" VARCHAR2(10) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"CONTENT" VARCHAR2(5000) NULL,
"P_CODE" VARCHAR2(10) NOT NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "P_REPLY2" (
"P2_CODE" VARCHAR2(10) NULL,
"KDATE" DATE NULL,
"COMMENT" VARCHAR2(5000) NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL,
"PC_CODE" VARCHAR2(10) NULL
);
CREATE TABLE "ADDITIONAL_OPEN" (
"AO_CODE" VARCHAR2(10) NOT NULL,
"TITLE" VARCHAR2(100) NULL,
"SUMMARY" VARCHAR2(5000) NULL,
"CONTENT" VARCHAR2(10000) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"VIEW" NUMBER NULL,
"AP_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "SKIL_TAG" (
"KT_CODE" VARCHAR2(10) NULL,
"SL_CODE" VARCHAR2(10) NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "INQUIRY" (
"IQ_CODE" VARCHAR2(10) NOT NULL,
"TITLE" VARCHAR2(1000) NULL,
"CONTENT" VARCHAR2(5000) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL,
"IT_CODE" VARCHAR2(10) NULL
);
CREATE TABLE "FAQ" (
"FAQ_CODE" VARCHAR2(10) NOT NULL,
"TILTE" VARCHAR2(100) NULL,
"CONTENT" VARCHAR2(10000) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"ADMIN_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "PROTEST" (
"PROTEST_CODE" VARCHAR2(10) NOT NULL,
"TITLE" VARCHAR2(100) NULL,
"CONTENT" VARCHAR2(10000) NULL,
"KDATE" DATE NULL
);
CREATE TABLE "INQUIRY_TYPE" (
"IT_CODE" VARCHAR2(10) NULL,
"TYPE" VARCHAR2(100) NULL
);
CREATE TABLE "I_REPLY" (
"IR_CODE" VARCHAR2(10) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"CONTENT" VARCHAR2(10000) NULL,
"IQ_CODE" VARCHAR2(10) NOT NULL,
"ADMIN_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "PROTEST_REPLY" (
"PROC_CODE" VARCHAR2(10) NOT NULL,
"KDATE" DATE NULL,
"CONTENT" VARCHAR2(5000) NULL,
"PROTEST_CODE" VARCHAR2(10) NOT NULL,
"ADMIN_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "MEM_INFO" (
"MI_CODE" VARCHAR2(10) NOT NULL,
"EMAIL" VARCHAR2(50) NULL,
"BDAY" DATE NULL,
"ISG_OPEN" NUMBER NULL,
"ISB_OPEN" NUMBER NULL,
"ISE_OPEN" NUMBER NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL,
"GENDER_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "P_REPORT" (
"PR_CODE" VARCHAR2(10) NOT NULL,
"CONTENT" VARCHAR2(10000) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"P_CODE" VARCHAR2(10) NOT NULL,
"RR_CODE" VARCHAR2(10) NOT NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "REPORT_REASON" (
"RR_CODE" VARCHAR2(10) NOT NULL,
"REASON" VARCHAR2(100) NULL
);
CREATE TABLE "PC_REPOERT" (
"PCR_CODE" VARCHAR(255) NULL,
"CONTENT" VARCHAR2(5000) NULL,
"KDATE" DATE NULL,
"PC_CODE" VARCHAR2(10) NULL,
"RR_CODE" VARCHAR2(10) NOT NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "P2_REPORT" (
"P2R_CODE" VARCHAR2(10) NULL,
"CONTENT" VARCHAR2(5000) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"RR_CODE" VARCHAR2(10) NOT NULL,
"P2_CODE" VARCHAR2(10) NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "CQ_REPORT" (
"QR_CODE" VARCHAR2(10) NULL,
"CONTENT" VARCHAR2(10000) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"CQ_CODE" VARCHAR2(10) NOT NULL,
"RR_CODE" VARCHAR2(10) NOT NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "TQ_REPORT" (
"TQR_CODE" VARCHAR2(10) NULL,
"CONTENT" VARCHAR2(5000) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"TQ_CODE" VARCHAR2(10) NOT NULL,
"RR_CODE" VARCHAR2(10) NOT NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "CQC_REPORT" (
"CR_CODE" VARCHAR2(10) NOT NULL,
"CONTENT" VARCHAR2(5000) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"RR_CODE" VARCHAR2(10) NOT NULL,
"CQC_CODE" VARCHAR2(10) NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "TQC_REPORT" (
"QCR_CODE" VARCHAR2(10) NOT NULL,
"CONTENT" VARCHAR2(5000) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"TC_CODE" VARCHAR2(10) NULL,
"RR_CODE" VARCHAR2(10) NOT NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "TQ2_REPORT" (
"Q2R_CODE" VARCHAR2(10) NOT NULL,
"CONTENT" VARCHAR2(5000) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"T2_CODE" VARCHAR2(10) NULL,
"RR_CODE" VARCHAR2(10) NOT NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "CQ2_REPORT" (
"C2_CODE" VARCHAR2(10) NOT NULL,
"CONTENT" VARCHAR2(5000) NULL,
"KDATE" DATE NULL,
"CQ2_CODE" VARCHAR2(10) NULL,
"RR_CODE" VARCHAR2(10) NOT NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "ADMIN" (
"ADMIN_CODE" VARCHAR2(10) NOT NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "P_REASON" (
"PRE_CODE" VARCHAR2(10) NOT NULL,
"REASON" VARCHAR2(50) NULL
);
CREATE TABLE "P_FILE" (
"PF_CODE" VARCHAR2(10) NOT NULL,
"PATH" VARCHAR2(1000) NULL,
"P_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "CAREER_FILE" (
"CF_CODE" VARCHAR2(10) NULL,
"PATH" VARCHAR2(1000) NOT NULL,
"CQ_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "TQ_FILE" (
"TQF_CODE" VARCHAR2(10) NOT NULL,
"PATH" VARCHAR2(1000) NULL,
"TQ_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "CQnA_PIC" (
"CQP_CODE" VARCHAR2(10) NOT NULL,
"PATH" VARCHAR2(1000) NULL,
"TQ_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "BR_FILE" (
"BRF_CODE" VARCHAR2(10) NOT NULL,
"PATH" VARCHAR2(1000) NULL,
"BR_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "OPEN_PICTURE" (
"OP_CODE" VARCHAR2(10) NOT NULL,
"PATH" VARCHAR2(1000) NULL,
"AP_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "TASK" (
"TASK_CODE" VARCHAR2(10) NOT NULL,
"TITLE" VARCHAR2(100) NULL,
"CONTENT" VARCHAR2(10000) NULL,
"SDATE" DATE DEFAULT SYSDATE NULL,
"EDATE" DATE NULL,
"KDATE" DATE NULL,
"MA_CODEA" VARCHAR2(10) NOT NULL,
"MA_CODEP" VARCHAR2(10) NOT NULL,
"DP_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "TASK_TAG" (
"TT_CODE" VARCHAR2(10) NOT NULL,
"TTL_CODE" VARCHAR2(10) NOT NULL,
"TASK_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "TT_LIST" (
"TTL_CODE" VARCHAR2(10) NOT NULL,
"NAME" VARCHAR2(100) NULL
);
CREATE TABLE "AO_REPORT" (
"AOR_CODE" VARCHAR2(10) NULL,
"CONTENT" VARCHAR2(10000) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"RR_CODE" VARCHAR2(10) NOT NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL,
"AP_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "AOC_REPORT" (
"ACR_CODE" VARCHAR2(10) NOT NULL,
"CONTENT" VARCHAR2(10000) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"RR_CODE" VARCHAR2(10) NOT NULL,
"Field2" VARCHAR2(10) NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "AO2_REPORT" (
"A2R_CODE" VARCHAR2(10) NOT NULL,
"CONTENT" VARCHAR2(10000) NULL,
"KDATE" SYSDATE DEFAULT SYSDATE NULL,
"RR_CODE" VARCHAR2(10) NOT NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL,
"A2_CODE" VARCHAR2(10) NULL
);
CREATE TABLE "OPENTAG_LIST" (
"OL_CODE" VARCHAR2(10) NOT NULL,
"NAME" VARCHAR2(100) NULL,
"OT_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "D_VOTE" (
"DV_CODE" VARCHAR2(10) NOT NULL,
"SDATE" DATE DEFAULT SYSDATE NULL,
"CONTENT" VARCHAR2(500) NULL,
"EDATE" DATE NULL,
"CP_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "AG_LIST" (
"AL_CODE" VARCHAR2(10) NOT NULL,
"MA_CODE" VARCHAR2(10) NOT NULL,
"DV_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "GENDER" (
"GENDER_CODE" VARCHAR2(10) NOT NULL,
"GENDER" VARCHAR2(50) NULL
);
CREATE TABLE "GRADE" (
"GRADE_CODE" VARCHAR2(10) NOT NULL,
"GRADE_NAME" VARCHAR2(50) NULL,
"GRADE_MIN" NUMBER NULL,
"GRADE_MAX" NUMBER NULL
);
CREATE TABLE "MEMBER_ROLE" (
"MR_CODE" VARCHAR2(10) NOT NULL,
"ROLE" VARCHAR2(50) NULL
);
CREATE TABLE "G_FILTER" (
"GF_CODE" VARCHAR2(10) NOT NULL,
"GRADE_CODE" VARCHAR2(10) NOT NULL,
"MR_CODE" VARCHAR2(10) NOT NULL,
"AP_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "S_FILTER" (
"SF_CODE" VARCHAR2(10) NOT NULL,
"GENDER_CODE" VARCHAR2(10) NOT NULL,
"AP_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "A_FILTER" (
"AF_CODE" VARCHAR2(10) NOT NULL,
"AP_CODE" VARCHAR2(10) NOT NULL,
"AA_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "MS_FILTER" (
"MSF_CODE" VARCHAR2(10) NOT NULL,
"MIN" NUMBER NULL,
"AP_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "AGE_RANGE" (
"AA_CODE" VARCHAR2(10) NOT NULL,
"RANGE" NUMBER NULL
);
CREATE TABLE "ROLE_COMP" (
"RC_CODE" VARCHAR2(10) NOT NULL,
"RC_NUMBER" NUMBER NULL,
"MR_CODE" VARCHAR2(10) NOT NULL,
"AP_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "A_RESULT" (
"AR_CODE" VARCHAR2(10) NOT NULL,
"RESULT" VARCHAR2(50) NULL
);
CREATE TABLE "AO_PROCESS" (
"Key" VARCHAR2(10) NOT NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"AOR_CODE" VARCHAR2(10) NULL,
"ADMIN_CODE" VARCHAR2(10) NOT NULL,
"PE_CODE" VARCHAR2(10) NOT NULL,
"PRE_CODE" VARCHAR2(10) NOT NULL,
"PTG_CODE" VARCHAR2(10) NULL
);
CREATE TABLE "P_PROCESS" (
"PP_CODE" VARCHAR2(10) NOT NULL,
"KDATE" DATE NULL,
"PR_CODE" VARCHAR2(10) NULL,
"ADMIN_CODE" VARCHAR2(10) NOT NULL,
"PRE_CODE" VARCHAR2(10) NOT NULL,
"PE_CODE" VARCHAR2(10) NOT NULL,
"PTG_CODE" VARCHAR2(10) NULL
);
CREATE TABLE "PC_PROCESS" (
"PRR_CODE" VARCHAR2(10) NOT NULL,
"KDATE" DATE NULL,
"PCR_CODE" VARCHAR2(10) NULL,
"ADMIN_CODE" VARCHAR2(10) NOT NULL,
"PRE_CODE" VARCHAR2(10) NOT NULL,
"PE_CODE" VARCHAR2(10) NOT NULL,
"Field3" VARCHAR2(10) NULL
);
CREATE TABLE "AOC_PROCESS" (
"ACP_CODE" VARCHAR2(10) NOT NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"ADMIN_CODE" VARCHAR2(10) NOT NULL,
"PRE_CODE" VARCHAR2(10) NOT NULL,
"ACR_CODE" VARCHAR2(10) NOT NULL,
"PE_CODE" VARCHAR2(10) NOT NULL,
"PTG_CODE" VARCHAR2(10) NULL
);
CREATE TABLE "AO2_PROCESS" (
"A2P_CODE" VARCHAR2(10) NOT NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"ADMIN_CODE" VARCHAR2(10) NOT NULL,
"PRE_CODE" VARCHAR2(10) NOT NULL,
"A2R_CODE" VARCHAR2(10) NOT NULL,
"PE_CODE" VARCHAR2(10) NOT NULL,
"PTG_CODE" VARCHAR2(10) NULL
);
CREATE TABLE "CQ2_PROCESS" (
"Q2P_CODE" VARCHAR2(10) NOT NULL,
"KDATE" DATE NULL,
"ADMIN_CODE" VARCHAR2(10) NOT NULL,
"PRE_CODE" VARCHAR2(10) NOT NULL,
"C2_CODE" VARCHAR2(10) NOT NULL,
"PE_CODE" VARCHAR2(10) NOT NULL,
"PTG_CODE" VARCHAR2(10) NULL
);
CREATE TABLE "CQC_PROCESS" (
"QCP_CODE" VARCHAR2(10) NOT NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"ADMIN_CODE" VARCHAR2(10) NOT NULL,
"PRE_CODE" VARCHAR2(10) NOT NULL,
"CR_CODE" VARCHAR2(10) NOT NULL,
"PE_CODE" VARCHAR2(10) NOT NULL,
"PTG_CODE" VARCHAR2(10) NULL
);
CREATE TABLE "CQ_PROCESS" (
"QPR_CODE" VARCHAR2(10) NOT NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"ADMIN_CODE" VARCHAR2(10) NOT NULL,
"PRE_CODE" VARCHAR2(10) NOT NULL,
"QR_CODE" VARCHAR2(10) NULL,
"PE_CODE" VARCHAR2(10) NOT NULL,
"PTG_CODE" VARCHAR2(10) NULL
);
CREATE TABLE "TQ2_PROCESS" (
"T2P_CDE" VARCHAR2(10) NOT NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"Q2R_CODE" VARCHAR2(10) NOT NULL,
"ADMIN_CODE" VARCHAR2(10) NOT NULL,
"PRE_CODE" VARCHAR2(10) NOT NULL,
"PE_CODE" VARCHAR2(10) NOT NULL,
"PTG_CODE" VARCHAR2(10) NULL
);
CREATE TABLE "TQC_PROCESS" (
"TCP_CODE" VARCHAR2(10) NOT NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"ADMIN_CODE" VARCHAR2(10) NOT NULL,
"PRE_CODE" VARCHAR2(10) NOT NULL,
"QCR_CODE" VARCHAR2(10) NOT NULL,
"PE_CODE" VARCHAR2(10) NOT NULL,
"PTG_CODE" VARCHAR2(10) NULL
);
CREATE TABLE "TQ_PROCESS" (
"TQP_CODE" VARCHAR2(10) NOT NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"ADMIN_CODE" VARCHAR2(10) NOT NULL,
"PRE_CODE" VARCHAR2(10) NOT NULL,
"PE_CODE" VARCHAR2(10) NOT NULL,
"PTG_CODE" VARCHAR2(10) NULL,
"TQR_CODE" VARCHAR2(10) NULL
);
CREATE TABLE "PENALTY" (
"PE_CODE" VARCHAR2(10) NOT NULL,
"NAME" VARCHAR2(50) NULL
);
CREATE TABLE "P2_PROCESS" (
"P2P_CODE" VARCHAR2(10) NOT NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"P2R_CODE" VARCHAR2(10) NULL,
"PRE_CODE" VARCHAR2(10) NOT NULL,
"PE_CODE" VARCHAR2(10) NOT NULL,
"ADMIN_CODE" VARCHAR2(10) NOT NULL,
"PTG_CODE" VARCHAR2(10) NULL
);
CREATE TABLE "CLOSE_MEMBER" (
"CM_CODE" VARCHAR2(10) NOT NULL,
"KMATE" DATE DEFAULT SYSDATE NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "MILESTONE" (
"MS_CODE" VARCHAR2(10) NOT NULL,
"DP_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "STEP" (
"ST_CODE" VARCHAR2(10) NOT NULL,
"STEP" VARCHAR2(50) NULL
);
CREATE TABLE "CHECKLIST" (
"CK_CODE" VARCHAR2(10) NOT NULL,
"CONTENT" VARCHAR2(10000) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"ST_CODE" VARCHAR2(10) NOT NULL,
"MS_CODE" VARCHAR2(10) NOT NULL,
"CC_CODE" VARCHAR2(10) NOT NULL,
"MA_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "CHECKLIST_CHEK" (
"CC_CODE" VARCHAR2(10) NOT NULL,
"CHECK" VARCHAR2(50) NULL
);
CREATE TABLE "FINAL_REPORT" (
"FR_CODE" VARCHAR2(10) NOT NULL,
"TITLE" VARCHAR2(100) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"CONTENT" VARCHAR2(10000) NULL
);
CREATE TABLE "FR_FILE" (
"FRF_CODE" VARCHAR2(10) NOT NULL,
"PATH" VARCHAR2(1000) NULL,
"FR_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "ERD" (
"ERD_CODE" VARCHAR2(10) NOT NULL,
"PATH" VARCHAR2(1000) NULL,
"FR_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "FLOWCHART" (
"FC_CODE" VARCHAR2(10) NULL,
"PATH" VARCHAR2(1000) NULL,
"FR_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "TEAM_FEEDBACK" (
"TF_CODE" VARCHAR2(10) NOT NULL,
"CONTENT" VARCHAR2(10000) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"MA_CODE" VARCHAR2(10) NOT NULL,
"FR_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "EVALUATION" (
"EV_CODE" VARCHAR2(10) NOT NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"SCORE" NUMBER NULL,
"ED_CODE" VARCHAR2(10) NOT NULL,
"MA_CODEA" VARCHAR2(10) NOT NULL,
"MA_CODEP" VARCHAR2(10) NOT NULL
);
CREATE TABLE "EV_DEVISION" (
"ED_CODE" VARCHAR2(10) NOT NULL,
"DIVISION" VARCHAR2(50) NULL
);
CREATE TABLE "CAUTION" (
"CT_CODE" VARCHAR2(10) NOT NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"TASK_CODE" VARCHAR2(10) NOT NULL,
"MA_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "TEAM_REPORT" (
"TR_CODE" VARCHAR2(10) NOT NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"CONTENT" VARCHAR2(10000) NULL,
"MA_CODE" VARCHAR2(10) NOT NULL,
"DP_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "D_LEADER" (
"DL_CODE" VARCHAR2(10) NOT NULL,
"KDATE" SYSDATE NULL,
"MA_CODE" VARCHAR2(10) NOT NULL,
"RE_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "C_PROJECT" (
"CP_CODE" VARCHAR2(10) NOT NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"App_OpenCode" VARCHAR2(10) NOT NULL
);
CREATE TABLE "RELEASE" (
"RE_CODE" VARCHAR2(10) NOT NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"TASK_CODE" VARCHAR2(10) NOT NULL,
"DP_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "D_PROJECT" (
"DP_CODE" VARCHAR2(10) NOT NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"CP_CODE" VARCHAR2(10) NOT NULL,
"DR_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "D_REASON" (
"DR_CODE" VARCHAR2(10) NOT NULL,
"REASON" VARCHAR2(100) NULL
);
CREATE TABLE "TR_PROCESS" (
"TP_CODE" VARCHAR2(10) NOT NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"TR_CODE" VARCHAR2(10) NOT NULL,
"ADMIN_CODE" VARCHAR2(10) NOT NULL,
"PRE_CODE" VARCHAR2(10) NOT NULL,
"PE_CODE" VARCHAR2(10) NOT NULL,
"PTG_CODE" VARCHAR2(10) NULL
);
CREATE TABLE "TECH_UP" (
"TU_CODE" VARCHAR2(10) NOT NULL,
"KDATE" SYSDATE NULL,
"TC_CODE" VARCHAR2(10) NULL
);
CREATE TABLE "CAREER_UP" (
"CU_CODE" VARCHAR2(10) NOT NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"CQC_CODE" VARCHAR2(10) NULL
);
CREATE TABLE "MINUTES" (
"MN_CODE" VARCHAR2(10) NOT NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"TITLE" VARCHAR2(100) NULL,
"CONTENT" VARCHAR2(10000) NULL,
"MA_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "MI_COMMENT" (
"MNC_CODE" VARCHAR2(10) NOT NULL,
"COMMENT" VARCHAR2(5000) NULL,
"KDATE" DATE DEFAULT SYSDATE NULL,
"MA_CODE" VARCHAR2(10) NOT NULL,
"MN_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "BR_RESULT" (
"BRR_CODE" VARCHAR2(10) NOT NULL,
"KDATE" DATE NULL,
"BR_CODE" VARCHAR2(10) NOT NULL,
"STATE_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "STATE" (
"STATE_CODE" VARCHAR2(10) NOT NULL,
"STATE" VARCHAR2(50) NULL
);
CREATE TABLE "LINK" (
"LINK_CODE" VARCHAR2(10) NOT NULL,
"LINK" VARCHAR2(1000) NULL,
"MEMBER_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "PROFILE" (
"PI_CODE" VARCHAR2(10) NOT NULL,
"PATH" VARCHAR2(1000) NULL,
"MEM_CODE" VARCHAR2(10) NOT NULL
);
CREATE TABLE "PT_LIST" (
"PTL_CODE" VARCHAR2(10) NOT NULL,
"NAME" VARCHAR2(100) NULL
);
ALTER TABLE "MEMBER" ADD CONSTRAINT "PK_MEMBER" PRIMARY KEY (
"MEM_CODE"
);
ALTER TABLE "App_Opening" ADD CONSTRAINT "PK_APP_OPENING" PRIMARY KEY (
"AP_CODE"
);
ALTER TABLE "CAREER" ADD CONSTRAINT "PK_CAREER" PRIMARY KEY (
"C_CODE"
);
ALTER TABLE "B_REPORT" ADD CONSTRAINT "PK_B_REPORT" PRIMARY KEY (
"BR_CODE"
);
ALTER TABLE "OPEN_TAG" ADD CONSTRAINT "PK_OPEN_TAG" PRIMARY KEY (
"OT_CODE"
);
ALTER TABLE "PTAG_LIST" ADD CONSTRAINT "PK_PTAG_LIST" PRIMARY KEY (
"PL_CODE"
);
ALTER TABLE "MEMBER_APPLY" ADD CONSTRAINT "PK_MEMBER_APPLY" PRIMARY KEY (
"MA_CODE"
);
ALTER TABLE "AO_COMMENT" ADD CONSTRAINT "PK_AO_COMMENT" PRIMARY KEY (
"AC_CODE"
);
ALTER TABLE "AO_REPLY2" ADD CONSTRAINT "PK_AO_REPLY2" PRIMARY KEY (
"A2_CODE"
);
ALTER TABLE "MBTI" ADD CONSTRAINT "PK_MBTI" PRIMARY KEY (
"MBTI_CODE"
);
ALTER TABLE "W_MEMBERLIST" ADD CONSTRAINT "PK_W_MEMBERLIST" PRIMARY KEY (
"WM_CODE"
);
ALTER TABLE "W_REASON" ADD CONSTRAINT "PK_W_REASON" PRIMARY KEY (
"WR_CODE"
);
ALTER TABLE "P_TARGET" ADD CONSTRAINT "PK_P_TARGET" PRIMARY KEY (
"PTG_CODE"
);
ALTER TABLE "TECH_QnA" ADD CONSTRAINT "PK_TECH_QNA" PRIMARY KEY (
"TQ_CODE"
);
ALTER TABLE "C_QnA" ADD CONSTRAINT "PK_C_QNA" PRIMARY KEY (
"CQ_CODE"
);
ALTER TABLE "PORTFOLIO" ADD CONSTRAINT "PK_PORTFOLIO" PRIMARY KEY (
"P_CODE"
);
ALTER TABLE "CVOTE" ADD CONSTRAINT "PK_CVOTE" PRIMARY KEY (
"V_CODE"
);
ALTER TABLE "TQ_COMMENT" ADD CONSTRAINT "PK_TQ_COMMENT" PRIMARY KEY (
"TC_CODE"
);
ALTER TABLE "TQ_REPLY2" ADD CONSTRAINT "PK_TQ_REPLY2" PRIMARY KEY (
"T2_CODE"
);
ALTER TABLE "CQ_COMMENT" ADD CONSTRAINT "PK_CQ_COMMENT" PRIMARY KEY (
"CQC_CODE"
);
ALTER TABLE "CQ_REPLY2" ADD CONSTRAINT "PK_CQ_REPLY2" PRIMARY KEY (
"CQ2_CODE"
);
ALTER TABLE "CVOTE_P" ADD CONSTRAINT "PK_CVOTE_P" PRIMARY KEY (
"VP_CODE"
);
ALTER TABLE "CVOTE_SEL" ADD CONSTRAINT "PK_CVOTE_SEL" PRIMARY KEY (
"VS_CODE"
);
ALTER TABLE "RESUME" ADD CONSTRAINT "PK_RESUME" PRIMARY KEY (
"R_CODE"
);
ALTER TABLE "ST_LIST" ADD CONSTRAINT "PK_ST_LIST" PRIMARY KEY (
"SL_CODE"
);
ALTER TABLE "P_TAG" ADD CONSTRAINT "PK_P_TAG" PRIMARY KEY (
"PT_CODE"
);
ALTER TABLE "P_COMMENT" ADD CONSTRAINT "PK_P_COMMENT" PRIMARY KEY (
"PC_CODE"
);
ALTER TABLE "P_REPLY2" ADD CONSTRAINT "PK_P_REPLY2" PRIMARY KEY (
"P2_CODE"
);
ALTER TABLE "ADDITIONAL_OPEN" ADD CONSTRAINT "PK_ADDITIONAL_OPEN" PRIMARY KEY (
"AO_CODE"
);
ALTER TABLE "SKIL_TAG" ADD CONSTRAINT "PK_SKIL_TAG" PRIMARY KEY (
"KT_CODE"
);
ALTER TABLE "INQUIRY" ADD CONSTRAINT "PK_INQUIRY" PRIMARY KEY (
"IQ_CODE"
);
ALTER TABLE "FAQ" ADD CONSTRAINT "PK_FAQ" PRIMARY KEY (
"FAQ_CODE"
);
ALTER TABLE "PROTEST" ADD CONSTRAINT "PK_PROTEST" PRIMARY KEY (
"PROTEST_CODE"
);
ALTER TABLE "INQUIRY_TYPE" ADD CONSTRAINT "PK_INQUIRY_TYPE" PRIMARY KEY (
"IT_CODE"
);
ALTER TABLE "I_REPLY" ADD CONSTRAINT "PK_I_REPLY" PRIMARY KEY (
"IR_CODE"
);
ALTER TABLE "PROTEST_REPLY" ADD CONSTRAINT "PK_PROTEST_REPLY" PRIMARY KEY (
"PROC_CODE"
);
ALTER TABLE "MEM_INFO" ADD CONSTRAINT "PK_MEM_INFO" PRIMARY KEY (
"MI_CODE"
);
ALTER TABLE "P_REPORT" ADD CONSTRAINT "PK_P_REPORT" PRIMARY KEY (
"PR_CODE"
);
ALTER TABLE "REPORT_REASON" ADD CONSTRAINT "PK_REPORT_REASON" PRIMARY KEY (
"RR_CODE"
);
ALTER TABLE "PC_REPOERT" ADD CONSTRAINT "PK_PC_REPOERT" PRIMARY KEY (
"PCR_CODE"
);
ALTER TABLE "P2_REPORT" ADD CONSTRAINT "PK_P2_REPORT" PRIMARY KEY (
"P2R_CODE"
);
ALTER TABLE "CQ_REPORT" ADD CONSTRAINT "PK_CQ_REPORT" PRIMARY KEY (
"QR_CODE"
);
ALTER TABLE "TQ_REPORT" ADD CONSTRAINT "PK_TQ_REPORT" PRIMARY KEY (
"TQR_CODE"
);
ALTER TABLE "CQC_REPORT" ADD CONSTRAINT "PK_CQC_REPORT" PRIMARY KEY (
"CR_CODE"
);
ALTER TABLE "TQC_REPORT" ADD CONSTRAINT "PK_TQC_REPORT" PRIMARY KEY (
"QCR_CODE"
);
ALTER TABLE "TQ2_REPORT" ADD CONSTRAINT "PK_TQ2_REPORT" PRIMARY KEY (
"Q2R_CODE"
);
ALTER TABLE "CQ2_REPORT" ADD CONSTRAINT "PK_CQ2_REPORT" PRIMARY KEY (
"C2_CODE"
);
ALTER TABLE "ADMIN" ADD CONSTRAINT "PK_ADMIN" PRIMARY KEY (
"ADMIN_CODE"
);
ALTER TABLE "P_REASON" ADD CONSTRAINT "PK_P_REASON" PRIMARY KEY (
"PRE_CODE"
);
ALTER TABLE "P_FILE" ADD CONSTRAINT "PK_P_FILE" PRIMARY KEY (
"PF_CODE"
);
ALTER TABLE "CAREER_FILE" ADD CONSTRAINT "PK_CAREER_FILE" PRIMARY KEY (
"CF_CODE"
);
ALTER TABLE "TQ_FILE" ADD CONSTRAINT "PK_TQ_FILE" PRIMARY KEY (
"TQF_CODE"
);
ALTER TABLE "CQnA_PIC" ADD CONSTRAINT "PK_CQNA_PIC" PRIMARY KEY (
"CQP_CODE"
);
ALTER TABLE "BR_FILE" ADD CONSTRAINT "PK_BR_FILE" PRIMARY KEY (
"BRF_CODE"
);
ALTER TABLE "OPEN_PICTURE" ADD CONSTRAINT "PK_OPEN_PICTURE" PRIMARY KEY (
"OP_CODE"
);
ALTER TABLE "TASK" ADD CONSTRAINT "PK_TASK" PRIMARY KEY (
"TASK_CODE"
);
ALTER TABLE "TASK_TAG" ADD CONSTRAINT "PK_TASK_TAG" PRIMARY KEY (
"TT_CODE"
);
ALTER TABLE "TT_LIST" ADD CONSTRAINT "PK_TT_LIST" PRIMARY KEY (
"TTL_CODE"
);
ALTER TABLE "AO_REPORT" ADD CONSTRAINT "PK_AO_REPORT" PRIMARY KEY (
"AOR_CODE"
);
ALTER TABLE "AOC_REPORT" ADD CONSTRAINT "PK_AOC_REPORT" PRIMARY KEY (
"ACR_CODE"
);
ALTER TABLE "AO2_REPORT" ADD CONSTRAINT "PK_AO2_REPORT" PRIMARY KEY (
"A2R_CODE"
);
ALTER TABLE "OPENTAG_LIST" ADD CONSTRAINT "PK_OPENTAG_LIST" PRIMARY KEY (
"OL_CODE"
);
ALTER TABLE "D_VOTE" ADD CONSTRAINT "PK_D_VOTE" PRIMARY KEY (
"DV_CODE"
);
ALTER TABLE "AG_LIST" ADD CONSTRAINT "PK_AG_LIST" PRIMARY KEY (
"AL_CODE"
);
ALTER TABLE "GENDER" ADD CONSTRAINT "PK_GENDER" PRIMARY KEY (
"GENDER_CODE"
);
ALTER TABLE "GRADE" ADD CONSTRAINT "PK_GRADE" PRIMARY KEY (
"GRADE_CODE"
);
ALTER TABLE "MEMBER_ROLE" ADD CONSTRAINT "PK_MEMBER_ROLE" PRIMARY KEY (
"MR_CODE"
);
ALTER TABLE "G_FILTER" ADD CONSTRAINT "PK_G_FILTER" PRIMARY KEY (
"GF_CODE"
);
ALTER TABLE "S_FILTER" ADD CONSTRAINT "PK_S_FILTER" PRIMARY KEY (
"SF_CODE"
);
ALTER TABLE "A_FILTER" ADD CONSTRAINT "PK_A_FILTER" PRIMARY KEY (
"AF_CODE"
);
ALTER TABLE "MS_FILTER" ADD CONSTRAINT "PK_MS_FILTER" PRIMARY KEY (
"MSF_CODE"
);
ALTER TABLE "AGE_RANGE" ADD CONSTRAINT "PK_AGE_RANGE" PRIMARY KEY (
"AA_CODE"
);
ALTER TABLE "ROLE_COMP" ADD CONSTRAINT "PK_ROLE_COMP" PRIMARY KEY (
"RC_CODE"
);
ALTER TABLE "A_RESULT" ADD CONSTRAINT "PK_A_RESULT" PRIMARY KEY (
"AR_CODE"
);
ALTER TABLE "AO_PROCESS" ADD CONSTRAINT "PK_AO_PROCESS" PRIMARY KEY (
"Key"
);
ALTER TABLE "P_PROCESS" ADD CONSTRAINT "PK_P_PROCESS" PRIMARY KEY (
"PP_CODE"
);
ALTER TABLE "PC_PROCESS" ADD CONSTRAINT "PK_PC_PROCESS" PRIMARY KEY (
"PRR_CODE"
);
ALTER TABLE "AOC_PROCESS" ADD CONSTRAINT "PK_AOC_PROCESS" PRIMARY KEY (
"ACP_CODE"
);
ALTER TABLE "AO2_PROCESS" ADD CONSTRAINT "PK_AO2_PROCESS" PRIMARY KEY (
"A2P_CODE"
);
ALTER TABLE "CQ2_PROCESS" ADD CONSTRAINT "PK_CQ2_PROCESS" PRIMARY KEY (
"Q2P_CODE"
);
ALTER TABLE "CQC_PROCESS" ADD CONSTRAINT "PK_CQC_PROCESS" PRIMARY KEY (
"QCP_CODE"
);
ALTER TABLE "CQ_PROCESS" ADD CONSTRAINT "PK_CQ_PROCESS" PRIMARY KEY (
"QPR_CODE"
);
ALTER TABLE "TQ2_PROCESS" ADD CONSTRAINT "PK_TQ2_PROCESS" PRIMARY KEY (
"T2P_CDE"
);
ALTER TABLE "TQC_PROCESS" ADD CONSTRAINT "PK_TQC_PROCESS" PRIMARY KEY (
"TCP_CODE"
);
ALTER TABLE "TQ_PROCESS" ADD CONSTRAINT "PK_TQ_PROCESS" PRIMARY KEY (
"TQP_CODE"
);
ALTER TABLE "PENALTY" ADD CONSTRAINT "PK_PENALTY" PRIMARY KEY (
"PE_CODE"
);
ALTER TABLE "P2_PROCESS" ADD CONSTRAINT "PK_P2_PROCESS" PRIMARY KEY (
"P2P_CODE"
);
ALTER TABLE "CLOSE_MEMBER" ADD CONSTRAINT "PK_CLOSE_MEMBER" PRIMARY KEY (
"CM_CODE"
);
ALTER TABLE "MILESTONE" ADD CONSTRAINT "PK_MILESTONE" PRIMARY KEY (
"MS_CODE"
);
ALTER TABLE "STEP" ADD CONSTRAINT "PK_STEP" PRIMARY KEY (
"ST_CODE"
);
ALTER TABLE "CHECKLIST" ADD CONSTRAINT "PK_CHECKLIST" PRIMARY KEY (
"CK_CODE"
);
ALTER TABLE "CHECKLIST_CHEK" ADD CONSTRAINT "PK_CHECKLIST_CHEK" PRIMARY KEY (
"CC_CODE"
);
ALTER TABLE "FINAL_REPORT" ADD CONSTRAINT "PK_FINAL_REPORT" PRIMARY KEY (
"FR_CODE"
);
ALTER TABLE "FR_FILE" ADD CONSTRAINT "PK_FR_FILE" PRIMARY KEY (
"FRF_CODE"
);
ALTER TABLE "ERD" ADD CONSTRAINT "PK_ERD" PRIMARY KEY (
"ERD_CODE"
);
ALTER TABLE "FLOWCHART" ADD CONSTRAINT "PK_FLOWCHART" PRIMARY KEY (
"FC_CODE"
);
ALTER TABLE "TEAM_FEEDBACK" ADD CONSTRAINT "PK_TEAM_FEEDBACK" PRIMARY KEY (
"TF_CODE"
);
ALTER TABLE "EVALUATION" ADD CONSTRAINT "PK_EVALUATION" PRIMARY KEY (
"EV_CODE"
);
ALTER TABLE "EV_DEVISION" ADD CONSTRAINT "PK_EV_DEVISION" PRIMARY KEY (
"ED_CODE"
);
ALTER TABLE "CAUTION" ADD CONSTRAINT "PK_CAUTION" PRIMARY KEY (
"CT_CODE"
);
ALTER TABLE "TEAM_REPORT" ADD CONSTRAINT "PK_TEAM_REPORT" PRIMARY KEY (
"TR_CODE"
);
ALTER TABLE "D_LEADER" ADD CONSTRAINT "PK_D_LEADER" PRIMARY KEY (
"DL_CODE"
);
ALTER TABLE "C_PROJECT" ADD CONSTRAINT "PK_C_PROJECT" PRIMARY KEY (
"CP_CODE"
);
ALTER TABLE "RELEASE" ADD CONSTRAINT "PK_RELEASE" PRIMARY KEY (
"RE_CODE"
);
ALTER TABLE "D_PROJECT" ADD CONSTRAINT "PK_D_PROJECT" PRIMARY KEY (
"DP_CODE"
);
ALTER TABLE "D_REASON" ADD CONSTRAINT "PK_D_REASON" PRIMARY KEY (
"DR_CODE"
);
ALTER TABLE "TR_PROCESS" ADD CONSTRAINT "PK_TR_PROCESS" PRIMARY KEY (
"TP_CODE"
);
ALTER TABLE "TECH_UP" ADD CONSTRAINT "PK_TECH_UP" PRIMARY KEY (
"TU_CODE"
);
ALTER TABLE "CAREER_UP" ADD CONSTRAINT "PK_CAREER_UP" PRIMARY KEY (
"CU_CODE"
);
ALTER TABLE "MINUTES" ADD CONSTRAINT "PK_MINUTES" PRIMARY KEY (
"MN_CODE"
);
ALTER TABLE "MI_COMMENT" ADD CONSTRAINT "PK_MI_COMMENT" PRIMARY KEY (
"MNC_CODE"
);
ALTER TABLE "BR_RESULT" ADD CONSTRAINT "PK_BR_RESULT" PRIMARY KEY (
"BRR_CODE"
);
ALTER TABLE "STATE" ADD CONSTRAINT "PK_STATE" PRIMARY KEY (
"STATE_CODE"
);
ALTER TABLE "LINK" ADD CONSTRAINT "PK_LINK" PRIMARY KEY (
"LINK_CODE"
);
ALTER TABLE "PROFILE" ADD CONSTRAINT "PK_PROFILE" PRIMARY KEY (
"PI_CODE"
);
ALTER TABLE "PT_LIST" ADD CONSTRAINT "PK_PT_LIST" PRIMARY KEY (
"PTL_CODE"
);
김지민.sql (팀 업무 분담 이후, 테이블, 시퀀스 생성, 제약조건 생성)
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--▩▩▩▩▩▩▩▩▩▩▩『테이블 생성』▩▩▩▩▩▩▩▩▩▩▩
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--[개설 신청 게시판 댓글 신고]
--[11 - AOC_REPORT]==========================================
CREATE TABLE "AOC_REPORT"
( "ACR_CODE" VARCHAR2(10)
, "CONTENT" VARCHAR2(4000)
, "KDATE" DATE DEFAULT SYSDATE
, "RR_CODE" VARCHAR2(10)
, "Field2" VARCHAR2(10)
, "MEM_CODE" VARCHAR2(10)
, CONSTRAINT ACR_PK PRIMARY KEY(ACR_CODE)
);
--[참조키 추가 - AOC_REPORT]*******************************
-- 댓글 코드[AC_CODE], 신고 사유 코드[RR_CODE]
-- 회원 코드[MEM_CODE]
-- AC_CODE : 개설 신청 게시판 댓글(AO_COMMENT)
ALTER TABLE AOC_REPORT
ADD CONSTRAINT ACR_AC_CODE_FK FOREIGN KEY(AC_CODE)
REFERENCES AO_COMMENT(AC_CODE);
-- RR_CODE : 콘텐츠 신고 사유(REPORT_REASON)
ALTER TABLE AOC_REPORT
ADD CONSTRAINT ACR_RR_CODE_FK FOREIGN KEY(RR_CODE)
REFERENCES REPORT_REASON(RR_CODE);
-- MEM_CODE : 일반 회원(MEMBER)
ALTER TABLE AOC_REPORT
ADD CONSTRAINT ACR_MEM_CODE_FK FOREIGN KEY(MEM_CODE)
REFERENCES MEMBER(MEM_CODE);
--*******************************************************
--[개설 신청 게시판 댓글 신고 처리]
--[12 - AOC_PROCESS]==========================================
CREATE TABLE "AOC_PROCESS"
( "ACP_CODE" VARCHAR2(10)
, "KDATE" DATE DEFAULT SYSDATE
, "ADMIN_CODE" VARCHAR2(10)
, "PRE_CODE" VARCHAR2(10)
, "ACR_CODE" VARCHAR2(10)
, "PE_CODE" VARCHAR2(10)
, "PTG_CODE" VARCHAR2(10)
, CONSTRAINT ACP_PK PRIMARY KEY(ACP_CODE)
);
--[참조키 추가 - AOC_PROCESS]*******************************
-- 관리자 코드[ADMIN_CODE], 처리 사유 코드[PRE_CODE]
-- 패널티 코드[PE_CODE], 처리 코드[PTG_CODE]
-- 댓글 신고 코드[ACR_CODE]
-- ADMIN_CODE : 관리자(ADMIN)
ALTER TABLE AOC_PROCESS
ADD CONSTRAINT ACP_ADMIN_CODE_FK FOREIGN KEY(ADMIN_CODE)
REFERENCES ADMIN(ADMIN_CODE);
-- PRE_CODE : 처리 사유(P_REASON)
ALTER TABLE AOC_PROCESS
ADD CONSTRAINT ACP_PRE_CODE_FK FOREIGN KEY(PRE_CODE)
REFERENCES P_REASON(PRE_CODE);
-- PE_CODE : 패널티(PENALTY)
ALTER TABLE AOC_PROCESS
ADD CONSTRAINT ACP_PE_CODE_FK FOREIGN KEY(PE_CODE)
REFERENCES PENALTY(PE_CODE);
-- PTG_CODE : 처리 대상(P_TARGET)
ALTER TABLE AOC_PROCESS
ADD CONSTRAINT ACP_PTG_CODE_FK FOREIGN KEY(PTG_CODE)
REFERENCES P_TARGET(PTG_CODE);
-- ACR_CODE : 개설 신청 게시판 댓글 신고(AOC_REPORT)
ALTER TABLE AOC_PROCESS
ADD CONSTRAINT ACP_Q2R_CODE_FK FOREIGN KEY(ACR_CODE)
REFERENCES AOC_REPORT(ACR_CODE);
--*******************************************************
--[개설신청글 태그]
--[13 - OPEN_TAG]==========================================
CREATE TABLE "OPEN_TAG"
( "OT_CODE" VARCHAR2(10)
, "PL_CODE" VARCHAR2(10)
, "AP_CODE" VARCHAR2(10)
, CONSTRAINT OT_PK PRIMARY KEY(OT_CODE)
);
--[참조키 추가 - OPEN_TAG]*******************************
-- 태그 코드(PL_CODE), 개설 신청 코드(AP_CODE)
-- PL_CODE : 프로젝트 주제 태그 목록(PTAG_LIST)
ALTER TABLE OPEN_TAG
ADD CONSTRAINT OT_PL_CODE_FK FOREIGN KEY(PL_CODE)
REFERENCES PTAG_LIST(PL_CODE);
-- AP_CODE : 개설 신청(APP_OPENING)
ALTER TABLE OPEN_TAG
ADD CONSTRAINT OT_AP_CODE_FK FOREIGN KEY(AP_CODE)
REFERENCES APP_OPENING(AP_CODE);
--*******************************************************
--[개설신청글 태그 목록]
--[14 - OPENTAG_LIST]==========================================
CREATE TABLE "OPENTAG_LIST"
( "OL_CODE" VARCHAR2(10)
, "NAME" VARCHAR2(100)
, "OT_CODE" VARCHAR2(10)
, CONSTRAINT OL_PK PRIMARY KEY(OL_CODE)
);
--[참조키 추가 - OPENTAG_LIST]*******************************
-- 주제 태그 코드[OT_CODE]
-- OT_CODE : 개설신청글 태그(OPEN_TAG)
ALTER TABLE OPENTAG_LIST
ADD CONSTRAINT OL_PL_CODE_FK FOREIGN KEY(OT_CODE)
REFERENCES OPEN_TAG(OT_CODE);
--*******************************************************
--[고객센터-FAQ 게시판]
--[20 - FAQ]==========================================
CREATE TABLE "FAQ"
( "FAQ_CODE" VARCHAR2(10)
, "TILTE" VARCHAR2(100)
, "CONTENT" VARCHAR2(4000)
, "KDATE" DATE DEFAULT SYSDATE
, "ADMIN_CODE" VARCHAR2(10)
, CONSTRAINT FAQ_PK PRIMARY KEY(FAQ_CODE)
);
--[참조키 추가 - FAQ]*******************************
-- 관리자 코드[ADMIN_CODE]
-- ADMIN_CODE : 관리자(ADMIN)
ALTER TABLE FAQ
ADD CONSTRAINT FAQ_ADMIN_CODE_FK FOREIGN KEY(ADMIN_CODE)
REFERENCES ADMIN(ADMIN_CODE);
--****************************************************
--[관리자]
--[21 - ADMIN]==========================================
CREATE TABLE ADMIN
( ADMIN_CODE VARCHAR2(10)
, KDATE DATE DEFAULT SYSDATE
, MEM_CODE VARCHAR2(10)
, CONSTRAINT ADMIN_PK PRIMARY KEY(ADMIN_CODE)
);
--==>> Table ADMIN이(가) 생성되었습니다.
--[참조키 추가 - ADMIN]*******************************
-- 회원코드 [MEM_CODE]
-- MEM_CODE : 일반회원(MEMBER)
ALTER TABLE ADMIN
ADD CONSTRAINT AD_MEM_CODE_FK FOREIGN KEY(MEM_CODE)
REFERENCES MEMBER(MEM_CODE);
--****************************************************
--[기술 Q&A 게시판]
--[22 - TECH_QNA]========================================
CREATE TABLE "TECH_QNA"
( "TQ_CODE" VARCHAR2(10)
, "TITLE" VARCHAR2(100)
, "CONTENT" VARCHAR2(4000)
, "KDATE" DATE DEFAULT SYSDATE
, "VIEWS" NUMBER DEFAULT 0
, "MEM_CODE" VARCHAR2(10)
, CONSTRAINT TQ_PK PRIMARY KEY(TQ_CODE)
);
--==>> Table "TECH_QNA"이(가) 생성되었습니다.
--[참조키 추가 - TECH_QNA]*******************************
-- 회원코드 [MEM_CODE]
-- MEM_CODE : 일반회원(MEMBER)
ALTER TABLE TECH_QNA
ADD CONSTRAINT AD_MEM_CODE_FK FOREIGN KEY(MEM_CODE)
REFERENCES MEMBER(MEM_CODE);
--*******************************************************
--[기술 Q&A 게시판 대댓글]
--[23 - TQ_REPLY2]========================================
CREATE TABLE "TQ_REPLY2"
( "T2_CODE" VARCHAR2(10)
, "KDATE" DATE DEFAULT SYSDATE
, "COMMENTS" VARCHAR2(4000)
, "TC_CODE" VARCHAR2(10)
, "MEM_CODE" VARCHAR2(10)
, CONSTRAINT T2_PK PRIMARY KEY(T2_CODE)
);
--==>> Table "TQ_REPLY2"이(가) 생성되었습니다.
--[참조키 추가 - TQ_REPLY2]*******************************
-- 댓글코드 [TC_CODE], 회원코드 [MEM_CODE]
-- TC_CODE : 기술Q&A게시판 댓글(TQ_COMMENT)
ALTER TABLE TQ_REPLY2
ADD CONSTRAINT T2_TC_CODE_FK FOREIGN KEY(TC_CODE)
REFERENCES TQ_COMMENT(TC_CODE);
-- MEM_CODE : 일반회원(MEMBER)
ALTER TABLE TQ_REPLY2
ADD CONSTRAINT T2_MEM_CODE_FK FOREIGN KEY(MEM_CODE)
REFERENCES MEMBER(MEM_CODE);
--*******************************************************
--[기술 Q&A 게시판 대댓글 신고]
--[24 - TQ2_REPORT]========================================
CREATE TABLE "TQ2_REPORT"
( "Q2R_CODE" VARCHAR2(10)
, "CONTENT" VARCHAR2(4000)
, "KDATE" DATE DEFAULT SYSDATE
, "T2_CODE" VARCHAR2(10)
, "RR_CODE" VARCHAR2(10)
, "MEM_CODE" VARCHAR2(10)
, CONSTRAINT Q2R_PK PRIMARY KEY(Q2R_CODE)
);
--==>> Table "TQ2_REPORT"이(가) 생성되었습니다.
--[참조키 추가 - TQ2_REPORT]*******************************
-- 대댓글 코드[T2_CODE], 신고 사유 코드[RR_CODE], 회원 코드[MEM_CODE]
-- T2_CODE : 기술Q&A 게시판 대댓글(TQ_REPLY2)
ALTER TABLE TQ2_REPORT
ADD CONSTRAINT Q2R_T2_CODE_FK FOREIGN KEY(T2_CODE)
REFERENCES TQ_REPLY2(T2_CODE);
-- RR_CODE : 콘텐츠 신고사유(REPORT_REASON)
ALTER TABLE TQ2_REPORT
ADD CONSTRAINT Q2R_RR_CODE_FK FOREIGN KEY(RR_CODE)
REFERENCES REPORT_REASON(RR_CODE);
-- MEM_CODE : 일반회원(MEMBER)
ALTER TABLE TQ2_REPORT
ADD CONSTRAINT Q2R_MEM_CODE_FK FOREIGN KEY(MEM_CODE)
REFERENCES MEMBER(MEM_CODE);
--*******************************************************
--[기술 Q&A 사진]
--[25 - CQnA_PIC]========================================
CREATE TABLE "CQnA_PIC"
( "CQP_CODE" VARCHAR2(10)
, "PATH" VARCHAR2(1000)
, "TQ_CODE" VARCHAR2(10)
, CONSTRAINT CQP_PK PRIMARY KEY(CQP_CODE)
);
--==>> Table "CQnA_PIC"이(가) 생성되었습니다.
--[참조키 추가 - CQnA_PIC]*******************************
-- 기술 게시물 코드[TQ_CODE]
-- TQ_CODE : 기술 Q&A 게시판(TECH_QNA)
ALTER TABLE CQnA_PIC
ADD CONSTRAINT CQP_TQ_CODE_FK FOREIGN KEY(TQ_CODE)
REFERENCES TECH_QNA(TQ_CODE);
--*******************************************************
--[기술 Q&A 게시판 신고]
--[26 - TQ_REPORT]========================================
CREATE TABLE "TQ_REPORT"
( "TQR_CODE" VARCHAR2(10)
, "CONTENT" VARCHAR2(4000)
, "KDATE" DATE DEFAULT SYSDATE
, "TQ_CODE" VARCHAR2(10)
, "RR_CODE" VARCHAR2(10)
, "MEM_CODE" VARCHAR2(10)
, CONSTRAINT TQR_PK PRIMARY KEY(TQR_CODE)
);
--==>> Table "TQ_REPORT"이(가) 생성되었습니다.
--[참조키 추가 - TQ_REPORT]*******************************
-- 기술 게시물 코드[TQ_CODE], 신고 사유 코드[RR_CODE]
-- 회원코드[MEM_CODE]
-- TQ_CODE : 기술 Q&A 게시판(TECH_QNA)
ALTER TABLE TQ_REPORT
ADD CONSTRAINT TQR_TQ_CODE_FK FOREIGN KEY(TQ_CODE)
REFERENCES TECH_QNA(TQ_CODE);
-- RR_CODE : 콘텐츠 신고 사유(REPORT_REASON)
ALTER TABLE TQ_REPORT
ADD CONSTRAINT TQR_RR_CODE_FK FOREIGN KEY(RR_CODE)
REFERENCES REPORT_REASON(RR_CODE);
-- MEM_CODE : 일반 회원(MEMBER)
ALTER TABLE TQ_REPORT
ADD CONSTRAINT TQR_MEM_CODE_FK FOREIGN KEY(MEM_CODE)
REFERENCES MEMBER(MEM_CODE);
--*******************************************************
--[기술 Q&A 게시판 첨부 파일]
--[27 - TQ_FILE]========================================
CREATE TABLE "TQ_FILE"
( "TQF_CODE" VARCHAR2(10)
, "PATH" VARCHAR2(1000)
, "TQ_CODE" VARCHAR2(10)
, CONSTRAINT TQF_PK PRIMARY KEY(TQF_CODE)
);
--==>> Table "TQ_FILE"이(가) 생성되었습니다.
--[참조키 추가 - TQ_FILE]*******************************
-- 기술 게시물 코드[TQ_CODE]
-- TQ_CODE : 기술 Q&A 게시판(TECH_QNA)
ALTER TABLE TQ_FILE
ADD CONSTRAINT TQF_TQ_CODE_FK FOREIGN KEY(TQ_CODE)
REFERENCES TECH_QNA(TQ_CODE);
--*******************************************************
--[기술 Q&A 게시판 신고 처리]
--[28 - TQ_PROCESS]=====================================
CREATE TABLE "TQ_PROCESS"
( "TQP_CODE" VARCHAR2(10)
, "KDATE" DATE DEFAULT SYSDATE
, "ADMIN_CODE" VARCHAR2(10)
, "PRE_CODE" VARCHAR2(10)
, "PE_CODE" VARCHAR2(10)
, "PTG_CODE" VARCHAR2(10)
, "TQR_CODE" VARCHAR2(10)
, CONSTRAINT TQP_PK PRIMARY KEY(TQP_CODE)
);
--==>> Table "TQ_PROCESS"이(가) 생성되었습니다.
--[참조키 추가 - TQ_PROCESS]*******************************
-- 관리자 코드[ADMIN_CODE], 처리 사유 코드[PRE_CODE]
-- 패널티 코드[PE_CODE], 처리 코드[PTG_CODE]
-- 신고 코드[TQR_CODE]
-- ADMIN_CODE : 관리자(ADMIN)
ALTER TABLE TQ_PROCESS
ADD CONSTRAINT TQP_ADMIN_CODE_FK FOREIGN KEY(ADMIN_CODE)
REFERENCES ADMIN(ADMIN_CODE);
-- PRE_CODE : 처리 사유(P_REASON)
ALTER TABLE TQ_PROCESS
ADD CONSTRAINT TQP_PRE_CODE_FK FOREIGN KEY(PRE_CODE)
REFERENCES P_REASON(PRE_CODE);
-- PE_CODE : 패널티(PENALTY)
ALTER TABLE TQ_PROCESS
ADD CONSTRAINT TQP_PE_CODE_FK FOREIGN KEY(PE_CODE)
REFERENCES PENALTY(PE_CODE);
-- PTG_CODE : 처리 대상(P_TARGET)
ALTER TABLE TQ_PROCESS
ADD CONSTRAINT TQP_PTG_CODE_FK FOREIGN KEY(PTG_CODE)
REFERENCES P_TARGET(PTG_CODE);
-- TQR_CODE : 기술 Q&A 게시판 신고(TQ_REPORT)
ALTER TABLE TQ_PROCESS
ADD CONSTRAINT TQP_TQR_CODE_FK FOREIGN KEY(TQR_CODE)
REFERENCES TQ_REPORT(TQR_CODE);
--*******************************************************
--[기술 대댓글 신고 처리]
--[29 - TQ2_PROCESS]========================================
CREATE TABLE "TQ2_PROCESS"
( "T2P_CODE" VARCHAR2(10)
, "KDATE" DATE DEFAULT SYSDATE
, "Q2R_CODE" VARCHAR2(10)
, "ADMIN_CODE" VARCHAR2(10)
, "PRE_CODE" VARCHAR2(10)
, "PE_CODE" VARCHAR2(10)
, "PTG_CODE" VARCHAR2(10)
, CONSTRAINT T2P_PK PRIMARY KEY(T2P_CODE)
);
--==>> Table "TQ2_PROCESS"이(가) 생성되었습니다.
--[참조키 추가 - TQ2_PROCESS]*******************************
-- 관리자 코드[ADMIN_CODE], 처리 사유 코드[PRE_CODE]
-- 패널티 코드[PE_CODE], 처리 코드[PTG_CODE]
-- 신고 코드[Q2R_CODE]
-- ADMIN_CODE : 관리자(ADMIN)
ALTER TABLE TQ2_PROCESS
ADD CONSTRAINT T2P_ADMIN_CODE_FK FOREIGN KEY(ADMIN_CODE)
REFERENCES ADMIN(ADMIN_CODE);
-- PRE_CODE : 처리 사유(P_REASON)
ALTER TABLE TQ2_PROCESS
ADD CONSTRAINT T2P_PRE_CODE_FK FOREIGN KEY(PRE_CODE)
REFERENCES P_REASON(PRE_CODE);
-- PE_CODE : 패널티(PENALTY)
ALTER TABLE TQ2_PROCESS
ADD CONSTRAINT T2P_PE_CODE_FK FOREIGN KEY(PE_CODE)
REFERENCES PENALTY(PE_CODE);
-- PTG_CODE : 처리 대상(P_TARGET)
ALTER TABLE TQ2_PROCESS
ADD CONSTRAINT T2P_PTG_CODE_FK FOREIGN KEY(PTG_CODE)
REFERENCES P_TARGET(PTG_CODE);
-- Q2R_CODE : 기술 Q&A 게시판 대댓글 신고(TQ2_REPORT)
ALTER TABLE TQ2_PROCESS
ADD CONSTRAINT T2P_Q2R_CODE_FK FOREIGN KEY(Q2R_CODE)
REFERENCES TQ_REPORT(Q2R_CODE);
--*******************************************************
--[기술 따봉]
--[30 - TECH_UP]========================================
CREATE TABLE "TECH_UP"
( "TU_CODE" VARCHAR2(10)
, "KDATE" DATE DEFAULT SYSDATE
, "TC_CODE" VARCHAR2(10)
, CONSTRAINT TU_PK PRIMARY KEY(TU_CODE)
);
--==>> Table "TECH_UP"이(가) 생성되었습니다.
--[참조키 추가 - TECH_UP]*******************************
-- 댓글 코드[TC_CODE]
-- TC_CODE : 기술 Q&A 게시판 댓글(TQ_COMMENT)
ALTER TABLE TECH_UP
ADD CONSTRAINT TU_TC_CODE_FK FOREIGN KEY(TC_CODE)
REFERENCES TQ_COMMENT(TC_CODE);
--*******************************************************
--[기술 Q&A 게시판 댓글]
--[31 - TQ_COMMENT]======================================
CREATE TABLE "TQ_COMMENT"
( "TC_CODE" VARCHAR(255)
, "KDATE" DATE DEFAULT SYSDATE
, "COMMENTS" VARCHAR2(4000)
, "TQ_CODE" VARCHAR2(10)
, "MEM_CODE" VARCHAR2(10)
, CONSTRAINT TC_PK PRIMARY KEY(TC_CODE)
);
--==>> Table "TQ_COMMENT"이(가) 생성되었습니다.
--[참조키 추가 - TQ_COMMENT]*******************************
-- 기술 게시물 코드[TQ_CODE], 회원 코드[MEM_CODE]
-- TQ_CODE : 기술 Q&A 게시판(TECH_QNA)
ALTER TABLE TQ_COMMENT
ADD CONSTRAINT TC_TQ_CODE_FK FOREIGN KEY(TQ_CODE)
REFERENCES TECH_QNA(TQ_CODE);
-- MEM_CODE : 일반 회원(MEMBER)
ALTER TABLE TQ_COMMENT
ADD CONSTRAINT TC_MEM_CODE_FK FOREIGN KEY(MEM_CODE)
REFERENCES MEMBER(MEM_CODE);
--*******************************************************
--[기술 Q&A 게시판 댓글 신고]
--[32 - TQC_REPORT]=======================================
CREATE TABLE "TQC_REPORT"
( "QCR_CODE" VARCHAR2(10)
, "CONTENT" VARCHAR2(4000)
, "KDATE" DATE DEFAULT SYSDATE
, "TC_CODE" VARCHAR2(10)
, "RR_CODE" VARCHAR2(10)
, "MEM_CODE" VARCHAR2(10)
, CONSTRAINT QCR_PK PRIMARY KEY(QCR_CODE)
);
--==>> Table "TQC_REPORT"이(가) 생성되었습니다.
--[참조키 추가 - TQC_REPORT]*******************************
-- 댓글 코드[TC_CODE], 신고 사유 코드[RR_CODE]
-- 회원 코드[MEM_CODE]
-- TC_CODE : 기술 Q&A 게시판 댓글(TQ_COMMENT)
ALTER TABLE TQC_REPORT
ADD CONSTRAINT QCR_TQ_CODE_FK FOREIGN KEY(TQ_CODE)
REFERENCES TECH_QNA(TQ_CODE);
-- RR_CODE : 콘텐츠 신고 사유(REPORT_REASON)
ALTER TABLE TQC_REPORT
ADD CONSTRAINT QCR_RR_CODE_FK FOREIGN KEY(RR_CODE)
REFERENCES REPORT_REASON(RR_CODE);
-- MEM_CODE : 일반 회원(MEMBER)
ALTER TABLE TQC_REPORT
ADD CONSTRAINT QCR_MEM_CODE_FK FOREIGN KEY(MEM_CODE)
REFERENCES MEMBER(MEM_CODE);
--*******************************************************
--[기술 게시판 댓글 신고 처리]
--[33 - TQC_PROCESS]=======================================
CREATE TABLE "TQC_PROCESS"
( "TCP_CODE" VARCHAR2(10)
, "KDATE" DATE DEFAULT SYSDATE
, "ADMIN_CODE" VARCHAR2(10)
, "PRE_CODE" VARCHAR2(10)
, "QCR_CODE" VARCHAR2(10)
, "PE_CODE" VARCHAR2(10)
, "PTG_CODE" VARCHAR2(10)
, CONSTRAINT TCP_PK PRIMARY KEY(TCP_CODE)
);
--==>> Table "TQC_PROCESS"이(가) 생성되었습니다.
--[참조키 추가 - TQC_PROCESS]*******************************
-- 관리자 코드[ADMIN_CODE], 처리 사유 코드[PRE_CODE]
-- 패널티 코드[PE_CODE], 처리 코드[PTG_CODE]
-- 신고 코드[QCR_CODE]
-- ADMIN_CODE : 관리자(ADMIN)
ALTER TABLE TQC_PROCESS
ADD CONSTRAINT TCP_ADMIN_CODE_FK FOREIGN KEY(ADMIN_CODE)
REFERENCES ADMIN(ADMIN_CODE);
-- PRE_CODE : 처리 사유(P_REASON)
ALTER TABLE TQC_PROCESS
ADD CONSTRAINT TCP_PRE_CODE_FK FOREIGN KEY(PRE_CODE)
REFERENCES P_REASON(PRE_CODE);
-- PE_CODE : 패널티(PENALTY)
ALTER TABLE TQC_PROCESS
ADD CONSTRAINT TCP_PE_CODE_FK FOREIGN KEY(PE_CODE)
REFERENCES PENALTY(PE_CODE);
-- PTG_CODE : 처리 대상(P_TARGET)
ALTER TABLE TQC_PROCESS
ADD CONSTRAINT TCP_PTG_CODE_FK FOREIGN KEY(PTG_CODE)
REFERENCES P_TARGET(PTG_CODE);
-- QCR_CODE : 기술 Q&A 게시판 댓글 신고(TQC_REPORT)
ALTER TABLE TQC_PROCESS
ADD CONSTRAINT TCP_QCR_CODE_FK FOREIGN KEY(QCR_CODE)
REFERENCES TQC_REPORT(QCR_CODE);
--*******************************************************
--[단계]
--[34 - STEP]==============================================
CREATE TABLE "STEP"
( "ST_CODE" VARCHAR2(10)
, "STEP" VARCHAR2(50)
, CONSTRAINT ST_PK PRIMARY KEY(ST_CODE)
);
--==>> Table "STEP"이(가) 생성되었습니다.
--[등급]
--[35 - GRADE]============================================
CREATE TABLE "GRADE"
("GRADE_CODE" VARCHAR2(10)
, "GRADE_NAME" VARCHAR2(50)
, "GRADE_MIN" NUMBER
, "GRADE_MAX" NUMBER
, CONSTRAINT GRADE_PK PRIMARY KEY(GRADE_CODE)
);
--==>> Table "GRADE"이(가) 생성되었습니다.
--[등급 필터링]
--[36 - G_FILTER]=========================================
CREATE TABLE "G_FILTER"
( "GF_CODE" VARCHAR2(10)
, "GRADE_CODE" VARCHAR2(10)
, "MR_CODE" VARCHAR2(10)
, "AP_CODE" VARCHAR2(10)
, CONSTRAINT GF_PK PRIMARY KEY(GF_CODE)
);
--==>> Table "G_FILTER"이(가) 생성되었습니다.
--[참조키 추가 - G_FILTER]*******************************
-- 등급 코드[GRADE_CODE], 직무 코드[MR_CODE]
-- 개설 신청 코드[AP_CODE]
-- GRADE_CODE : 등급(GRADE)
ALTER TABLE G_FILTER
ADD CONSTRAINT GF_GRADE_CODE_FK FOREIGN KEY(GRADE_CODE)
REFERENCES GRADE(GRADE_CODE);
-- MR_CODE : 직무(MEMBER_ROLE)
ALTER TABLE G_FILTER
ADD CONSTRAINT GF_MR_CODE_FK FOREIGN KEY(MR_CODE)
REFERENCES MEMBER_ROLE(MR_CODE);
-- AP_CODE : 개설 신청(APP_OPENING)
ALTER TABLE G_FILTER
ADD CONSTRAINT GF_AP_CODE_FK FOREIGN KEY(AP_CODE)
REFERENCES APP_OPENING(AP_CODE);
--*******************************************************
--[링크]
--[37 - LINK]==================================================
CREATE TABLE "LINK"
( "LINK_CODE" VARCHAR2(10)
, "LINK" VARCHAR2(1000)
, "MEMBER_CODE" VARCHAR2(10)
, CONSTRAINT LINK_PK PRIMARY KEY(LINK_CODE)
);
--==>> Table "LINK"이(가) 생성되었습니다.
--[참조키 추가 - LINK]***********************************
-- 회원코드[MEM_CODE]
-- MEM_CODE : 일반회원(MEMBER)
ALTER TABLE LINK
ADD CONSTRAINT LINK_MEM_CODE_FK FOREIGN KEY(MEM_CODE)
REFERENCES MEMBER(MEM_CODE);
--*******************************************************
--[마일스톤]
--[38 - MILESTONE]=============================================
CREATE TABLE "MILESTONE"
( "MS_CODE" VARCHAR2(10)
, "DP_CODE" VARCHAR2(10)
, CONSTRAINT MS_PK PRIMARY KEY(MS_CODE)
);
--==>> Table "MILESTONE"이(가) 생성되었습니다.
--[참조키 추가 - MILESTONE]*******************************
-- 프로젝트 코드[CP_CODE]
-- CP_CODE : 프로젝트(개설확정) (C_PROJECT)
ALTER TABLE MILESTONE
ADD CONSTRAINT MS_CP_CODE_FK FOREIGN KEY(CP_CODE)
REFERENCES MEMBER(CP_CODE);
--*******************************************************
--[매너 점수 필터링]
--[39 - MS_FILTER]===============================================
CREATE TABLE "MS_FILTER"
( "MSF_CODE" VARCHAR2(10)
, "MIN" NUMBER
, "AP_CODE" VARCHAR2(10)
, CONSTRAINT MSF_PK PRIMARY KEY(MSF_CODE)
);
--==>> Table "MS_FILTER"이(가) 생성되었습니다.
--[참조키 추가 - MS_FILTER]*******************************
-- 개설 신청 코드[AP_CODE]
-- AP_CODE : 개설 신청 (APP_OPENING)
ALTER TABLE MS_FILTER
ADD CONSTRAINT MSF_CP_CODE_FK FOREIGN KEY(APP_OPENING)
REFERENCES APP_OPENING(APP_OPENING);
--*******************************************************
--[멤버 지원]
--[40 - MEMBER_APPLY]=========================================
CREATE TABLE "MEMBER_APPLY"
( "MA_CODE" VARCHAR2(10)
, "CONTENT" VARCHAR2(4000)
, "KDATE" DATE DEFAULT SYSDATE
, "DDATE" DATE
, "MEM_CODE" VARCHAR2(10)
, "RC_CODE" VARCHAR2(10)
, "AR_CODE" VARCHAR2(10)
, CONSTRAINT MA_PK PRIMARY KEY(MA_CODE)
);
--==>> Table "MEMBER_APPLY"이(가) 생성되었습니다.
--[참조키 추가 - MEMBER_APPLY]*******************************
-- 회원코드[MEM_CODE], 직무 구성 코드[RC_CODE]
-- 결과 코드[AR_CODE]
-- MEM_CODE : 일반 회원(MEMBER)
ALTER TABLE MEMBER_APPLY
ADD CONSTRAINT MA_MEM_CODE_FK FOREIGN KEY(MEM_CODE)
REFERENCES MEMBER(MEM_CODE);
-- RC_CODE : 팀원별 직무 구성(ROLE_COMP)
ALTER TABLE MEMBER_APPLY
ADD CONSTRAINT MA_RC_CODE_FK FOREIGN KEY(RC_CODE)
REFERENCES ROLE_COMP(RC_CODE);
-- AR_CODE : 지원 결과(A_RESULT)
ALTER TABLE MEMBER_APPLY
ADD CONSTRAINT MA_AR_CODE_FK FOREIGN KEY(AR_CODE)
REFERENCES A_RESULT(AR_CODE);
--*******************************************************
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--▩▩▩▩▩▩▩▩▩▩▩『시퀀스 생성』▩▩▩▩▩▩▩▩▩▩▩
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--[AO_REPLY2 시퀀스]
--[7 - A2_SEQ]
CREATE SEQUENCE A2_SEQ
START WITH 1
NOCACHE;
--==>> Sequence A2_SEQ이(가) 생성되었습니다.
--[AO2_REPORT 시퀀스]
--[8 - A2R_SEQ]
CREATE SEQUENCE A2R_SEQ
START WITH 1
NOCACHE;
--==>> Sequence A2R_SEQ이(가) 생성되었습니다.
--[AO2_PROCESS 시퀀스]
--[9 - A2P_SEQ]
CREATE SEQUENCE A2P_SEQ
START WITH 1
NOCACHE;
--==>> Sequence A2P_SEQ이(가) 생성되었습니다.
--[AOC_REPORT 시퀀스]
--[11 - ACR_SEQ]
CREATE SEQUENCE ACR_SEQ
START WITH 1
NOCACHE;
--==>> Sequence ACR_SEQ이(가) 생성되었습니다.
--[AOC_PROCESS 시퀀스]
--[12 - ACP_SEQ]
CREATE SEQUENCE ACP_SEQ
START WITH 1
NOCACHE;
--==>> Sequence ACP_SEQ이(가) 생성되었습니다.
--[OPEN_TAG 시퀀스]
--[13 - OT_SEQ]
CREATE SEQUENCE OT_SEQ
START WITH 1
NOCACHE;
--==>> Sequence OT_SEQ이(가) 생성되었습니다.
--[OPENTAG_LIST 시퀀스]
--[14 - OL_SEQ]
CREATE SEQUENCE OL_SEQ
START WITH 1
NOCACHE;
--==>> Sequence OL_SEQ이(가) 생성되었습니다.
--[FINAL_REPORT 시퀀스]
--[15 - FR_SEQ]
CREATE SEQUENCE FR_SEQ
START WITH 1
NOCACHE;
--==>> Sequence FR_SEQ이(가) 생성되었습니다.
--[CAUTION 시퀀스]
--[16 - CT_SEQ]
CREATE SEQUENCE CT_SEQ
START WITH 1
NOCACHE;
--==>> Sequence CT_SEQ이(가) 생성되었습니다.
--[CAREER 시퀀스]
--[17 - C_SEQ]
CREATE SEQUENCE C_SEQ
START WITH 1
NOCACHE;
--==>> Sequence C_SEQ이(가) 생성되었습니다.
--[INQUIRY 시퀀스]
--[18 - IQ_SEQ]
CREATE SEQUENCE IQ_SEQ
START WITH 1
NOCACHE;
--==>> Sequence IQ_SEQ이(가) 생성되었습니다.
--[PROTEST 시퀀스]
--[19 - PROTEST_SEQ]
CREATE SEQUENCE PROTEST_SEQ
START WITH 1
NOCACHE;
--==>> Sequence PROTEST_SEQ이(가) 생성되었습니다.
--[FAQ 시퀀스]
--[20 - FAQ_SEQ]
CREATE SEQUENCE FAQ_SEQ
START WITH 1
NOCACHE;
--==>> Sequence FAQ_SEQ이(가) 생성되었습니다.
--[ADMIN 시퀀스]
--[21 - ADMIN_SEQ]
CREATE SEQUENCE ADMIN_SEQ
START WITH 1
NOCACHE;
--==>> Sequence ADMIN_SEQ이(가) 생성되었습니다.
--[TECH_QnA 시퀀스]
--[22 - TQ_SEQ]
CREATE SEQUENCE TQ_SEQ
START WITH 1
NOCACHE;
--==>> Sequence TQ_SEQ이(가) 생성되었습니다.
--[TQ_REPLY2 시퀀스]
--[23 - T2_SEQ]
CREATE SEQUENCE T2_SEQ
START WITH 1
NOCACHE;
--==>> Sequence T2_SEQ이(가) 생성되었습니다.
--[TQ2_REPORT 시퀀스]
--[24 - Q2R_SEQ]
CREATE SEQUENCE Q2R_SEQ
START WITH 1
NOCACHE;
--==>> Sequence Q2R_SEQ이(가) 생성되었습니다.
--[CQnA_PIC 시퀀스]
--[25 - CQP_SEQ]
CREATE SEQUENCE CQP_SEQ
START WITH 1
NOCACHE;
--==>> Sequence CQP_SEQ이(가) 생성되었습니다.
--[TQ_REPORT 시퀀스]
--[26 - TQR_SEQ]
CREATE SEQUENCE TQR_SEQ
START WITH 1
NOCACHE;
--==>> Sequence TQR_SEQ이(가) 생성되었습니다.
--[TQ_FILE 시퀀스]
--[27 - TQF_SEQ]
CREATE SEQUENCE TQF_SEQ
START WITH 1
NOCACHE;
--==>> Sequence TQF_SEQ이(가) 생성되었습니다.
--[TQ_PROCESS 시퀀스]
--[28 - TQP_SEQ]
CREATE SEQUENCE TQP_SEQ
START WITH 1
NOCACHE;
--==>> Sequence TQP_SEQ이(가) 생성되었습니다.
--[TQ2_PROCESS 시퀀스]
--[29 - T2P_SEQ]
CREATE SEQUENCE T2P_SEQ
START WITH 1
NOCACHE;
--==>> Sequence T2P_SEQ이(가) 생성되었습니다.
--[TECH_UP 시퀀스]
--[30 - TU_SEQ]
CREATE SEQUENCE TU_SEQ
START WITH 1
NOCACHE;
--==>> Sequence TU_SEQ이(가) 생성되었습니다.
--[TQ_COMMENT 시퀀스]
--[31 - TC_SEQ]
CREATE SEQUENCE TC_SEQ
START WITH 1
NOCACHE;
--==>> Sequence TC_SEQ이(가) 생성되었습니다.
--[TQC_REPORT 시퀀스]
--[32 - QCR_SEQ]
CREATE SEQUENCE QCR_SEQ
START WITH 1
NOCACHE;
--==>> Sequence QCR_SEQ이(가) 생성되었습니다.
--[TQC_PROCESS 시퀀스]
--[33 - TCP_SEQ]
CREATE SEQUENCE TCP_SEQ
START WITH 1
NOCACHE;
--==>> Sequence TCP_SEQ이(가) 생성되었습니다.
--[STEP 시퀀스]
--[34 - ST_SEQ]
CREATE SEQUENCE ST_SEQ
START WITH 1
NOCACHE;
--==>> Sequence ST_SEQ이(가) 생성되었습니다.
--[GRADE 시퀀스]
--[35 - GRADE_SEQ]
CREATE SEQUENCE GRADE_SEQ
START WITH 1
NOCACHE;
--==>> Sequence GRADE_SEQ이(가) 생성되었습니다.
--[G_FILTER 시퀀스]
--[36 - GF_SEQ]
CREATE SEQUENCE GF_SEQ
START WITH 1
NOCACHE;
--==>> Sequence GF_SEQ이(가) 생성되었습니다.
--[LINK 시퀀스]
--[37 - LINK_SEQ]
CREATE SEQUENCE LINK_SEQ
START WITH 1
NOCACHE;
--==>> Sequence LINK_SEQ이(가) 생성되었습니다.
--[MILESTONE 시퀀스]
--[38 - MS_SEQ]
CREATE SEQUENCE MS_SEQ
START WITH 1
NOCACHE;
--==>> Sequence MS_SEQ이(가) 생성되었습니다.
--[MS_FILTER 시퀀스]
--[39 - MSF_SEQ]
CREATE SEQUENCE MSF_SEQ
START WITH 1
NOCACHE;
--==>> Sequence MSF_SEQ이(가) 생성되었습니다.
--[MEMBER_APPLY 시퀀스]
--[40 - MA_SEQ]
CREATE SEQUENCE MA_SEQ
START WITH 1
NOCACHE;
--==>> Sequence MA_SEQ이(가) 생성되었습니다.
20240221_PMOA_KJM_FINAL_1.sql
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--▩▩▩▩▩▩▩▩▩▩▩『조회 쿼리문』▩▩▩▩▩▩▩▩▩▩▩
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--============================
--[전체 테이블 조회]
--============================
SELECT *
FROM ALL_TABLES
WHERE OWNER = 'PMOA';
--============================
--============================
--[전체 시퀀스 조회]
--============================
SELECT *
FROM ALL_SEQUENCES
WHERE SEQUENCE_OWNER = 'PMOA';
--============================
--============================
--[전체 제약조건 조회]
--============================
SELECT *
FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = '테이블명';
--============================
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--▩▩▩▩▩▩▩▩▩▩▩『테이블 생성』▩▩▩▩▩▩▩▩▩▩▩
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--[개설 신청 게시판 댓글 신고]
--[11 - AOC_REPORT]==========================================
CREATE TABLE "AOC_REPORT"
( "ACR_CODE" VARCHAR2(10)
, "CONTENT" VARCHAR2(4000)
, "KDATE" DATE DEFAULT SYSDATE
, "RR_CODE" VARCHAR2(10)
, "AC_CODE" VARCHAR2(10)
, "MEM_CODE" VARCHAR2(10)
, CONSTRAINT ACR_PK PRIMARY KEY(ACR_CODE)
);
--[참조키 추가 - AOC_REPORT]*******************************
-- 댓글 코드[AC_CODE], 신고 사유 코드[RR_CODE]
-- 회원 코드[MEM_CODE]
-- AC_CODE : 개설 신청 게시판 댓글(AO_COMMENT)
ALTER TABLE AOC_REPORT
ADD CONSTRAINT ACR_AC_CODE_FK FOREIGN KEY(AC_CODE)
REFERENCES AO_COMMENT(AC_CODE);
-- RR_CODE : 콘텐츠 신고 사유(REPORT_REASON)
ALTER TABLE AOC_REPORT
ADD CONSTRAINT ACR_RR_CODE_FK FOREIGN KEY(RR_CODE)
REFERENCES REPORT_REASON(RR_CODE);
-- MEM_CODE : 일반 회원(MEMBER)
ALTER TABLE AOC_REPORT
ADD CONSTRAINT ACR_MEM_CODE_FK FOREIGN KEY(MEM_CODE)
REFERENCES MEMBER(MEM_CODE);
--*******************************************************
--[개설 신청 게시판 댓글 신고 처리]
--[12 - AOC_PROCESS]==========================================
CREATE TABLE "AOC_PROCESS"
( "ACP_CODE" VARCHAR2(10)
, "KDATE" DATE DEFAULT SYSDATE
, "ADMIN_CODE" VARCHAR2(10)
, "PRE_CODE" VARCHAR2(10)
, "ACR_CODE" VARCHAR2(10)
, "PE_CODE" VARCHAR2(10)
, "PTG_CODE" VARCHAR2(10)
, CONSTRAINT ACP_PK PRIMARY KEY(ACP_CODE)
);
--[참조키 추가 - AOC_PROCESS]*******************************
-- 관리자 코드[ADMIN_CODE], 처리 사유 코드[PRE_CODE]
-- 패널티 코드[PE_CODE], 처리 코드[PTG_CODE]
-- 댓글 신고 코드[ACR_CODE]
-- ADMIN_CODE : 관리자(ADMIN)
ALTER TABLE AOC_PROCESS
ADD CONSTRAINT ACP_ADMIN_CODE_FK FOREIGN KEY(ADMIN_CODE)
REFERENCES ADMIN(ADMIN_CODE);
-- PRE_CODE : 처리 사유(P_REASON)
ALTER TABLE AOC_PROCESS
ADD CONSTRAINT ACP_PRE_CODE_FK FOREIGN KEY(PRE_CODE)
REFERENCES P_REASON(PRE_CODE);
-- PE_CODE : 패널티(PENALTY)
ALTER TABLE AOC_PROCESS
ADD CONSTRAINT ACP_PE_CODE_FK FOREIGN KEY(PE_CODE)
REFERENCES PENALTY(PE_CODE);
-- PTG_CODE : 처리 대상(P_TARGET)
ALTER TABLE AOC_PROCESS
ADD CONSTRAINT ACP_PTG_CODE_FK FOREIGN KEY(PTG_CODE)
REFERENCES P_TARGET(PTG_CODE);
-- ACR_CODE : 개설 신청 게시판 댓글 신고(AOC_REPORT)
ALTER TABLE AOC_PROCESS
ADD CONSTRAINT ACP_Q2R_CODE_FK FOREIGN KEY(ACR_CODE)
REFERENCES AOC_REPORT(ACR_CODE);
--*******************************************************
--[개설신청글 태그]
--[13 - OPEN_TAG]==========================================
CREATE TABLE "OPEN_TAG"
( "OT_CODE" VARCHAR2(10)
, "PL_CODE" VARCHAR2(10)
, "AP_CODE" VARCHAR2(10)
, CONSTRAINT OT_PK PRIMARY KEY(OT_CODE)
);
--[참조키 추가 - OPEN_TAG]*******************************
-- 태그 코드(PL_CODE), 개설 신청 코드(AP_CODE)
-- PL_CODE : 프로젝트 주제 태그 목록(PTAG_LIST)
ALTER TABLE OPEN_TAG
ADD CONSTRAINT OT_PL_CODE_FK FOREIGN KEY(PL_CODE)
REFERENCES PTAG_LIST(PL_CODE);
-- AP_CODE : 개설 신청(APP_OPENING)
ALTER TABLE OPEN_TAG
ADD CONSTRAINT OT_AP_CODE_FK FOREIGN KEY(AP_CODE)
REFERENCES APP_OPENING(AP_CODE);
--*******************************************************
--[개설신청글 태그 목록]
--[14 - OPENTAG_LIST]==========================================
CREATE TABLE "OPENTAG_LIST"
( "OL_CODE" VARCHAR2(10)
, "NAME" VARCHAR2(100)
, "OT_CODE" VARCHAR2(10)
, CONSTRAINT OL_PK PRIMARY KEY(OL_CODE)
);
--[참조키 추가 - OPENTAG_LIST]*******************************
-- 주제 태그 코드[OT_CODE]
-- OT_CODE : 개설신청글 태그(OPEN_TAG)
ALTER TABLE OPENTAG_LIST
ADD CONSTRAINT OL_PL_CODE_FK FOREIGN KEY(OT_CODE)
REFERENCES OPEN_TAG(OT_CODE);
--*******************************************************
--[고객센터-FAQ 게시판]
--[20 - FAQ]==========================================
CREATE TABLE "FAQ"
( "FAQ_CODE" VARCHAR2(10)
, "TILTE" VARCHAR2(100)
, "CONTENT" VARCHAR2(4000)
, "KDATE" DATE DEFAULT SYSDATE
, "ADMIN_CODE" VARCHAR2(10)
, CONSTRAINT FAQ_PK PRIMARY KEY(FAQ_CODE)
);
--[참조키 추가 - FAQ]*******************************
-- 관리자 코드[ADMIN_CODE]
-- ADMIN_CODE : 관리자(ADMIN)
ALTER TABLE FAQ
ADD CONSTRAINT FAQ_ADMIN_CODE_FK FOREIGN KEY(ADMIN_CODE)
REFERENCES ADMIN(ADMIN_CODE);
--****************************************************
--[관리자]
--[21 - ADMIN]==========================================
CREATE TABLE ADMIN
( ADMIN_CODE VARCHAR2(10)
, KDATE DATE DEFAULT SYSDATE
, MEM_CODE VARCHAR2(10)
, CONSTRAINT ADMIN_PK PRIMARY KEY(ADMIN_CODE)
);
--==>> Table ADMIN이(가) 생성되었습니다.
--[참조키 추가 - ADMIN]*******************************
-- 회원코드 [MEM_CODE]
-- MEM_CODE : 일반회원(MEMBER)
ALTER TABLE ADMIN
ADD CONSTRAINT AD_MEM_CODE_FK FOREIGN KEY(MEM_CODE)
REFERENCES MEMBER(MEM_CODE);
--****************************************************
--[기술 Q&A 게시판]
--[22 - TECH_QNA]========================================
CREATE TABLE "TECH_QNA"
( "TQ_CODE" VARCHAR2(10)
, "TITLE" VARCHAR2(100)
, "CONTENT" VARCHAR2(4000)
, "KDATE" DATE DEFAULT SYSDATE
, "VIEWS" NUMBER DEFAULT 0
, "MEM_CODE" VARCHAR2(10)
, CONSTRAINT TQ_PK PRIMARY KEY(TQ_CODE)
);
--==>> Table "TECH_QNA"이(가) 생성되었습니다.
--[참조키 추가 - TECH_QNA]*******************************
-- 회원코드 [MEM_CODE]
-- MEM_CODE : 일반회원(MEMBER)
ALTER TABLE TECH_QNA
ADD CONSTRAINT TQ_MEM_CODE_FK FOREIGN KEY(MEM_CODE)
REFERENCES MEMBER(MEM_CODE);
--*******************************************************
--[기술 Q&A 게시판 대댓글]
--[23 - TQ_REPLY2]========================================
CREATE TABLE "TQ_REPLY2"
( "T2_CODE" VARCHAR2(10)
, "KDATE" DATE DEFAULT SYSDATE
, "COMMENTS" VARCHAR2(4000)
, "TC_CODE" VARCHAR2(10)
, "MEM_CODE" VARCHAR2(10)
, CONSTRAINT T2_PK PRIMARY KEY(T2_CODE)
);
--==>> Table "TQ_REPLY2"이(가) 생성되었습니다.
--[참조키 추가 - TQ_REPLY2]*******************************
-- 댓글코드 [TC_CODE], 회원코드 [MEM_CODE]
-- TC_CODE : 기술Q&A게시판 댓글(TQ_COMMENT)
ALTER TABLE TQ_REPLY2
ADD CONSTRAINT T2_TC_CODE_FK FOREIGN KEY(TC_CODE)
REFERENCES TQ_COMMENT(TC_CODE);
-- MEM_CODE : 일반회원(MEMBER)
ALTER TABLE TQ_REPLY2
ADD CONSTRAINT T2_MEM_CODE_FK FOREIGN KEY(MEM_CODE)
REFERENCES MEMBER(MEM_CODE);
--*******************************************************
--[기술 Q&A 게시판 대댓글 신고]
--[24 - TQ2_REPORT]========================================
CREATE TABLE "TQ2_REPORT"
( "Q2R_CODE" VARCHAR2(10)
, "CONTENT" VARCHAR2(4000)
, "KDATE" DATE DEFAULT SYSDATE
, "T2_CODE" VARCHAR2(10)
, "RR_CODE" VARCHAR2(10)
, "MEM_CODE" VARCHAR2(10)
, CONSTRAINT Q2R_PK PRIMARY KEY(Q2R_CODE)
);
--==>> Table "TQ2_REPORT"이(가) 생성되었습니다.
--[참조키 추가 - TQ2_REPORT]*******************************
-- 대댓글 코드[T2_CODE], 신고 사유 코드[RR_CODE], 회원 코드[MEM_CODE]
-- T2_CODE : 기술Q&A 게시판 대댓글(TQ_REPLY2)
ALTER TABLE TQ2_REPORT
ADD CONSTRAINT Q2R_T2_CODE_FK FOREIGN KEY(T2_CODE)
REFERENCES TQ_REPLY2(T2_CODE);
-- RR_CODE : 콘텐츠 신고사유(REPORT_REASON)
ALTER TABLE TQ2_REPORT
ADD CONSTRAINT Q2R_RR_CODE_FK FOREIGN KEY(RR_CODE)
REFERENCES REPORT_REASON(RR_CODE);
-- MEM_CODE : 일반회원(MEMBER)
ALTER TABLE TQ2_REPORT
ADD CONSTRAINT Q2R_MEM_CODE_FK FOREIGN KEY(MEM_CODE)
REFERENCES MEMBER(MEM_CODE);
--*******************************************************
--[기술 Q&A 사진]
--[25 - CQNA_PIC]========================================
CREATE TABLE "CQNA_PIC"
( "CQP_CODE" VARCHAR2(10)
, "PATH" VARCHAR2(1000)
, "TQ_CODE" VARCHAR2(10)
, CONSTRAINT CQP_PK PRIMARY KEY(CQP_CODE)
);
--==>> Table "CQnA_PIC"이(가) 생성되었습니다.
--[참조키 추가 - CQnA_PIC]*******************************
-- 기술 게시물 코드[TQ_CODE]
-- TQ_CODE : 기술 Q&A 게시판(TECH_QNA)
ALTER TABLE CQnA_PIC
ADD CONSTRAINT CQP_TQ_CODE_FK FOREIGN KEY(TQ_CODE)
REFERENCES TECH_QNA(TQ_CODE);
--*******************************************************
--[기술 Q&A 게시판 신고]
--[26 - TQ_REPORT]========================================
CREATE TABLE "TQ_REPORT"
( "TQR_CODE" VARCHAR2(10)
, "CONTENT" VARCHAR2(4000)
, "KDATE" DATE DEFAULT SYSDATE
, "TQ_CODE" VARCHAR2(10)
, "RR_CODE" VARCHAR2(10)
, "MEM_CODE" VARCHAR2(10)
, CONSTRAINT TQR_PK PRIMARY KEY(TQR_CODE)
);
--==>> Table "TQ_REPORT"이(가) 생성되었습니다.
--[참조키 추가 - TQ_REPORT]*******************************
-- 기술 게시물 코드[TQ_CODE], 신고 사유 코드[RR_CODE]
-- 회원코드[MEM_CODE]
-- TQ_CODE : 기술 Q&A 게시판(TECH_QNA)
ALTER TABLE TQ_REPORT
ADD CONSTRAINT TQR_TQ_CODE_FK FOREIGN KEY(TQ_CODE)
REFERENCES TECH_QNA(TQ_CODE);
-- RR_CODE : 콘텐츠 신고 사유(REPORT_REASON)
ALTER TABLE TQ_REPORT
ADD CONSTRAINT TQR_RR_CODE_FK FOREIGN KEY(RR_CODE)
REFERENCES REPORT_REASON(RR_CODE);
-- MEM_CODE : 일반 회원(MEMBER)
ALTER TABLE TQ_REPORT
ADD CONSTRAINT TQR_MEM_CODE_FK FOREIGN KEY(MEM_CODE)
REFERENCES MEMBER(MEM_CODE);
--*******************************************************
--[기술 Q&A 게시판 첨부 파일]
--[27 - TQ_FILE]========================================
CREATE TABLE "TQ_FILE"
( "TQF_CODE" VARCHAR2(10)
, "PATH" VARCHAR2(1000)
, "TQ_CODE" VARCHAR2(10)
, CONSTRAINT TQF_PK PRIMARY KEY(TQF_CODE)
);
--==>> Table "TQ_FILE"이(가) 생성되었습니다.
--[참조키 추가 - TQ_FILE]*******************************
-- 기술 게시물 코드[TQ_CODE]
-- TQ_CODE : 기술 Q&A 게시판(TECH_QNA)
ALTER TABLE TQ_FILE
ADD CONSTRAINT TQF_TQ_CODE_FK FOREIGN KEY(TQ_CODE)
REFERENCES TECH_QNA(TQ_CODE);
--*******************************************************
--[기술 Q&A 게시판 신고 처리]
--[28 - TQ_PROCESS]=====================================
CREATE TABLE "TQ_PROCESS"
( "TQP_CODE" VARCHAR2(10)
, "KDATE" DATE DEFAULT SYSDATE
, "ADMIN_CODE" VARCHAR2(10)
, "PRE_CODE" VARCHAR2(10)
, "PE_CODE" VARCHAR2(10)
, "PTG_CODE" VARCHAR2(10)
, "TQR_CODE" VARCHAR2(10)
, CONSTRAINT TQP_PK PRIMARY KEY(TQP_CODE)
);
--==>> Table "TQ_PROCESS"이(가) 생성되었습니다.
--[참조키 추가 - TQ_PROCESS]*******************************
-- 관리자 코드[ADMIN_CODE], 처리 사유 코드[PRE_CODE]
-- 패널티 코드[PE_CODE], 처리 코드[PTG_CODE]
-- 신고 코드[TQR_CODE]
-- ADMIN_CODE : 관리자(ADMIN)
ALTER TABLE TQ_PROCESS
ADD CONSTRAINT TQP_ADMIN_CODE_FK FOREIGN KEY(ADMIN_CODE)
REFERENCES ADMIN(ADMIN_CODE);
-- PRE_CODE : 처리 사유(P_REASON)
ALTER TABLE TQ_PROCESS
ADD CONSTRAINT TQP_PRE_CODE_FK FOREIGN KEY(PRE_CODE)
REFERENCES P_REASON(PRE_CODE);
-- PE_CODE : 패널티(PENALTY)
ALTER TABLE TQ_PROCESS
ADD CONSTRAINT TQP_PE_CODE_FK FOREIGN KEY(PE_CODE)
REFERENCES PENALTY(PE_CODE);
-- PTG_CODE : 처리 대상(P_TARGET)
ALTER TABLE TQ_PROCESS
ADD CONSTRAINT TQP_PTG_CODE_FK FOREIGN KEY(PTG_CODE)
REFERENCES P_TARGET(PTG_CODE);
-- TQR_CODE : 기술 Q&A 게시판 신고(TQ_REPORT)
ALTER TABLE TQ_PROCESS
ADD CONSTRAINT TQP_TQR_CODE_FK FOREIGN KEY(TQR_CODE)
REFERENCES TQ_REPORT(TQR_CODE);
--*******************************************************
--[기술 대댓글 신고 처리]
--[29 - TQ2_PROCESS]========================================
CREATE TABLE "TQ2_PROCESS"
( "T2P_CODE" VARCHAR2(10)
, "KDATE" DATE DEFAULT SYSDATE
, "Q2R_CODE" VARCHAR2(10)
, "ADMIN_CODE" VARCHAR2(10)
, "PRE_CODE" VARCHAR2(10)
, "PE_CODE" VARCHAR2(10)
, "PTG_CODE" VARCHAR2(10)
, CONSTRAINT T2P_PK PRIMARY KEY(T2P_CODE)
);
--==>> Table "TQ2_PROCESS"이(가) 생성되었습니다.
--[참조키 추가 - TQ2_PROCESS]*******************************
-- 관리자 코드[ADMIN_CODE], 처리 사유 코드[PRE_CODE]
-- 패널티 코드[PE_CODE], 처리 코드[PTG_CODE]
-- 신고 코드[Q2R_CODE]
-- ADMIN_CODE : 관리자(ADMIN)
ALTER TABLE TQ2_PROCESS
ADD CONSTRAINT T2P_ADMIN_CODE_FK FOREIGN KEY(ADMIN_CODE)
REFERENCES ADMIN(ADMIN_CODE);
-- PRE_CODE : 처리 사유(P_REASON)
ALTER TABLE TQ2_PROCESS
ADD CONSTRAINT T2P_PRE_CODE_FK FOREIGN KEY(PRE_CODE)
REFERENCES P_REASON(PRE_CODE);
-- PE_CODE : 패널티(PENALTY)
ALTER TABLE TQ2_PROCESS
ADD CONSTRAINT T2P_PE_CODE_FK FOREIGN KEY(PE_CODE)
REFERENCES PENALTY(PE_CODE);
-- PTG_CODE : 처리 대상(P_TARGET)
ALTER TABLE TQ2_PROCESS
ADD CONSTRAINT T2P_PTG_CODE_FK FOREIGN KEY(PTG_CODE)
REFERENCES P_TARGET(PTG_CODE);
-- Q2R_CODE : 기술 Q&A 게시판 대댓글 신고(TQ2_REPORT)
ALTER TABLE TQ2_PROCESS
ADD CONSTRAINT T2P_Q2R_CODE_FK FOREIGN KEY(Q2R_CODE)
REFERENCES TQ2_REPORT(Q2R_CODE);
--*******************************************************
--[기술 따봉]
--[30 - TECH_UP]========================================
CREATE TABLE "TECH_UP"
( "TU_CODE" VARCHAR2(10)
, "KDATE" DATE DEFAULT SYSDATE
, "TC_CODE" VARCHAR2(10)
, CONSTRAINT TU_PK PRIMARY KEY(TU_CODE)
);
--==>> Table "TECH_UP"이(가) 생성되었습니다.
SELECT *
FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = 'TQ2_PROCESS';
--[참조키 추가 - TECH_UP]*******************************
-- 댓글 코드[TC_CODE]
-- TC_CODE : 기술 Q&A 게시판 댓글(TQ_COMMENT)
ALTER TABLE TECH_UP
ADD CONSTRAINT TU_TC_CODE_FK FOREIGN KEY(TC_CODE)
REFERENCES TQ_COMMENT(TC_CODE);
--*******************************************************
--[기술 Q&A 게시판 댓글]
--[31 - TQ_COMMENT]======================================
CREATE TABLE "TQ_COMMENT"
( "TC_CODE" VARCHAR(255)
, "KDATE" DATE DEFAULT SYSDATE
, "COMMENTS" VARCHAR2(4000)
, "TQ_CODE" VARCHAR2(10)
, "MEM_CODE" VARCHAR2(10)
, CONSTRAINT TC_PK PRIMARY KEY(TC_CODE)
);
--==>> Table "TQ_COMMENT"이(가) 생성되었습니다.
--[참조키 추가 - TQ_COMMENT]*******************************
-- 기술 게시물 코드[TQ_CODE], 회원 코드[MEM_CODE]
-- TQ_CODE : 기술 Q&A 게시판(TECH_QNA)
ALTER TABLE TQ_COMMENT
ADD CONSTRAINT TC_TQ_CODE_FK FOREIGN KEY(TQ_CODE)
REFERENCES TECH_QNA(TQ_CODE);
-- MEM_CODE : 일반 회원(MEMBER)
ALTER TABLE TQ_COMMENT
ADD CONSTRAINT TC_MEM_CODE_FK FOREIGN KEY(MEM_CODE)
REFERENCES MEMBER(MEM_CODE);
--*******************************************************
--[기술 Q&A 게시판 댓글 신고]
--[32 - TQC_REPORT]=======================================
CREATE TABLE "TQC_REPORT"
( "QCR_CODE" VARCHAR2(10)
, "CONTENT" VARCHAR2(4000)
, "KDATE" DATE DEFAULT SYSDATE
, "TC_CODE" VARCHAR2(10)
, "RR_CODE" VARCHAR2(10)
, "MEM_CODE" VARCHAR2(10)
, CONSTRAINT QCR_PK PRIMARY KEY(QCR_CODE)
);
--==>> Table "TQC_REPORT"이(가) 생성되었습니다.
--[참조키 추가 - TQC_REPORT]*******************************
-- 댓글 코드[TC_CODE], 신고 사유 코드[RR_CODE]
-- 회원 코드[MEM_CODE]
-- TC_CODE : 기술 Q&A 게시판 댓글(TQ_COMMENT)
ALTER TABLE TQC_REPORT
ADD CONSTRAINT QCR_TC_CODE_FK FOREIGN KEY(TC_CODE)
REFERENCES TQ_COMMENT(TC_CODE);
-- RR_CODE : 콘텐츠 신고 사유(REPORT_REASON)
ALTER TABLE TQC_REPORT
ADD CONSTRAINT QCR_RR_CODE_FK FOREIGN KEY(RR_CODE)
REFERENCES REPORT_REASON(RR_CODE);
-- MEM_CODE : 일반 회원(MEMBER)
ALTER TABLE TQC_REPORT
ADD CONSTRAINT QCR_MEM_CODE_FK FOREIGN KEY(MEM_CODE)
REFERENCES MEMBER(MEM_CODE);
--*******************************************************
--[기술 게시판 댓글 신고 처리]
--[33 - TQC_PROCESS]=======================================
CREATE TABLE "TQC_PROCESS"
( "TCP_CODE" VARCHAR2(10)
, "KDATE" DATE DEFAULT SYSDATE
, "ADMIN_CODE" VARCHAR2(10)
, "PRE_CODE" VARCHAR2(10)
, "QCR_CODE" VARCHAR2(10)
, "PE_CODE" VARCHAR2(10)
, "PTG_CODE" VARCHAR2(10)
, CONSTRAINT TCP_PK PRIMARY KEY(TCP_CODE)
);
--==>> Table "TQC_PROCESS"이(가) 생성되었습니다.
--[참조키 추가 - TQC_PROCESS]*******************************
-- 관리자 코드[ADMIN_CODE], 처리 사유 코드[PRE_CODE]
-- 패널티 코드[PE_CODE], 처리 코드[PTG_CODE]
-- 신고 코드[QCR_CODE]
-- ADMIN_CODE : 관리자(ADMIN)
ALTER TABLE TQC_PROCESS
ADD CONSTRAINT TCP_ADMIN_CODE_FK FOREIGN KEY(ADMIN_CODE)
REFERENCES ADMIN(ADMIN_CODE);
-- PRE_CODE : 처리 사유(P_REASON)
ALTER TABLE TQC_PROCESS
ADD CONSTRAINT TCP_PRE_CODE_FK FOREIGN KEY(PRE_CODE)
REFERENCES P_REASON(PRE_CODE);
-- PE_CODE : 패널티(PENALTY)
ALTER TABLE TQC_PROCESS
ADD CONSTRAINT TCP_PE_CODE_FK FOREIGN KEY(PE_CODE)
REFERENCES PENALTY(PE_CODE);
-- PTG_CODE : 처리 대상(P_TARGET)
ALTER TABLE TQC_PROCESS
ADD CONSTRAINT TCP_PTG_CODE_FK FOREIGN KEY(PTG_CODE)
REFERENCES P_TARGET(PTG_CODE);
-- QCR_CODE : 기술 Q&A 게시판 댓글 신고(TQC_REPORT)
ALTER TABLE TQC_PROCESS
ADD CONSTRAINT TCP_QCR_CODE_FK FOREIGN KEY(QCR_CODE)
REFERENCES TQC_REPORT(QCR_CODE);
--*******************************************************
--[단계]
--[34 - STEP]==============================================
CREATE TABLE "STEP"
( "ST_CODE" VARCHAR2(10)
, "STEP" VARCHAR2(50)
, CONSTRAINT ST_PK PRIMARY KEY(ST_CODE)
);
--==>> Table "STEP"이(가) 생성되었습니다.
--[등급]
--[35 - GRADE]============================================
CREATE TABLE "GRADE"
("GRADE_CODE" VARCHAR2(10)
, "NAME" VARCHAR2(50)
, "MIN" NUMBER
, "MAX" NUMBER
, "MAX_PROJECT" NUMBER
, CONSTRAINT GRADE_PK PRIMARY KEY(GRADE_CODE)
);
--==>> Table "GRADE"이(가) 생성되었습니다.
--[등급 필터링]
--[36 - G_FILTER]=========================================
CREATE TABLE "G_FILTER"
( "GF_CODE" VARCHAR2(10)
, "GRADE_CODE" VARCHAR2(10)
, "MR_CODE" VARCHAR2(10)
, "AP_CODE" VARCHAR2(10)
, CONSTRAINT GF_PK PRIMARY KEY(GF_CODE)
);
--==>> Table "G_FILTER"이(가) 생성되었습니다.
--[참조키 추가 - G_FILTER]*******************************
-- 등급 코드[GRADE_CODE], 직무 코드[MR_CODE]
-- 개설 신청 코드[AP_CODE]
-- GRADE_CODE : 등급(GRADE)
ALTER TABLE G_FILTER
ADD CONSTRAINT GF_GRADE_CODE_FK FOREIGN KEY(GRADE_CODE)
REFERENCES GRADE(GRADE_CODE);
-- MR_CODE : 직무(MEMBER_ROLE)
ALTER TABLE G_FILTER
ADD CONSTRAINT GF_MR_CODE_FK FOREIGN KEY(MR_CODE)
REFERENCES MEMBER_ROLE(MR_CODE);
-- AP_CODE : 개설 신청(APP_OPENING)
ALTER TABLE G_FILTER
ADD CONSTRAINT GF_AP_CODE_FK FOREIGN KEY(AP_CODE)
REFERENCES APP_OPENING(AP_CODE);
--*******************************************************
--[링크]
--[37 - LINK]==================================================
CREATE TABLE "LINK"
( "LINK_CODE" VARCHAR2(10)
, "LINK" VARCHAR2(1000)
, "MEM_CODE" VARCHAR2(10)
, CONSTRAINT LINK_PK PRIMARY KEY(LINK_CODE)
);
--==>> Table "LINK"이(가) 생성되었습니다.
--[참조키 추가 - LINK]***********************************
-- 회원코드[MEM_CODE]
-- MEM_CODE : 일반회원(MEMBER)
ALTER TABLE LINK
ADD CONSTRAINT LINK_MEM_CODE_FK FOREIGN KEY(MEM_CODE)
REFERENCES MEMBER(MEM_CODE);
--*******************************************************
--[마일스톤]
--[38 - MILESTONE]=============================================
CREATE TABLE "MILESTONE"
( "MS_CODE" VARCHAR2(10)
, "CP_CODE" VARCHAR2(10)
, CONSTRAINT MS_PK PRIMARY KEY(MS_CODE)
);
--==>> Table "MILESTONE"이(가) 생성되었습니다.
--[참조키 추가 - MILESTONE]*******************************
-- 프로젝트 코드[CP_CODE]
-- CP_CODE : 프로젝트(개설확정) (C_PROJECT)
ALTER TABLE MILESTONE
ADD CONSTRAINT MS_CP_CODE_FK FOREIGN KEY(CP_CODE)
REFERENCES C_PROJECT(CP_CODE);
--*******************************************************
--[매너 점수 필터링]
--[39 - MS_FILTER]===============================================
CREATE TABLE "MS_FILTER"
( "MSF_CODE" VARCHAR2(10)
, "MIN" NUMBER
, "AP_CODE" VARCHAR2(10)
, CONSTRAINT MSF_PK PRIMARY KEY(MSF_CODE)
);
--==>> Table "MS_FILTER"이(가) 생성되었습니다.
--[참조키 추가 - MS_FILTER]*******************************
-- 개설 신청 코드[AP_CODE]
-- AP_CODE : 개설 신청 (APP_OPENING)
ALTER TABLE MS_FILTER
ADD CONSTRAINT MSF_CP_CODE_FK FOREIGN KEY(AP_CODE)
REFERENCES APP_OPENING(AP_CODE);
--*******************************************************
--[멤버 지원]
--[40 - MEMBER_APPLY]=========================================
CREATE TABLE "MEMBER_APPLY"
( "MA_CODE" VARCHAR2(10)
, "CONTENT" VARCHAR2(4000)
, "KDATE" DATE DEFAULT SYSDATE
, "DDATE" DATE
, "MEM_CODE" VARCHAR2(10)
, "RC_CODE" VARCHAR2(10)
, "AR_CODE" VARCHAR2(10)
, CONSTRAINT MA_PK PRIMARY KEY(MA_CODE)
);
--==>> Table "MEMBER_APPLY"이(가) 생성되었습니다.
--[참조키 추가 - MEMBER_APPLY]*******************************
-- 회원코드[MEM_CODE], 직무 구성 코드[RC_CODE]
-- 결과 코드[AR_CODE]
-- MEM_CODE : 일반 회원(MEMBER)
ALTER TABLE MEMBER_APPLY
ADD CONSTRAINT MA_MEM_CODE_FK FOREIGN KEY(MEM_CODE)
REFERENCES MEMBER(MEM_CODE);
-- RC_CODE : 팀원별 직무 구성(ROLE_COMP)
ALTER TABLE MEMBER_APPLY
ADD CONSTRAINT MA_RC_CODE_FK FOREIGN KEY(RC_CODE)
REFERENCES ROLE_COMP(RC_CODE);
-- AR_CODE : 지원 결과(A_RESULT)
ALTER TABLE MEMBER_APPLY
ADD CONSTRAINT MA_AR_CODE_FK FOREIGN KEY(AR_CODE)
REFERENCES A_RESULT(AR_CODE);
--*******************************************************
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--▩▩▩▩▩▩▩▩▩▩▩『시퀀스 생성』▩▩▩▩▩▩▩▩▩▩▩
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--*************************************************
--*****************[추가한 시퀀스]*****************
--*************************************************
--[AO_REPLY2 시퀀스]
--[7 - A2_SEQ]
CREATE SEQUENCE A2_SEQ
START WITH 1
NOCACHE;
--==>> Sequence A2_SEQ이(가) 생성되었습니다.
--[AO2_REPORT 시퀀스]
--[8 - A2R_SEQ]
CREATE SEQUENCE A2R_SEQ
START WITH 1
NOCACHE;
--==>> Sequence A2R_SEQ이(가) 생성되었습니다.
--[AO2_PROCESS 시퀀스]
--[9 - A2P_SEQ]
CREATE SEQUENCE A2P_SEQ
START WITH 1
NOCACHE;
--==>> Sequence A2P_SEQ이(가) 생성되었습니다.
--[AOC_REPORT 시퀀스]
--[11 - ACR_SEQ]
CREATE SEQUENCE ACR_SEQ
START WITH 1
NOCACHE;
--==>> Sequence ACR_SEQ이(가) 생성되었습니다.
--[AOC_PROCESS 시퀀스]
--[12 - ACP_SEQ]
CREATE SEQUENCE ACP_SEQ
START WITH 1
NOCACHE;
--==>> Sequence ACP_SEQ이(가) 생성되었습니다.
--[OPEN_TAG 시퀀스]
--[13 - OT_SEQ]
CREATE SEQUENCE OT_SEQ
START WITH 1
NOCACHE;
--==>> Sequence OT_SEQ이(가) 생성되었습니다.
--[OPENTAG_LIST 시퀀스]
--[14 - OL_SEQ]
CREATE SEQUENCE OL_SEQ
START WITH 1
NOCACHE;
--==>> Sequence OL_SEQ이(가) 생성되었습니다.
--[FINAL_REPORT 시퀀스]
--[15 - FR_SEQ]
CREATE SEQUENCE FR_SEQ
START WITH 1
NOCACHE;
--==>> Sequence FR_SEQ이(가) 생성되었습니다.
--[CAUTION 시퀀스]
--[16 - CT_SEQ]
CREATE SEQUENCE CT_SEQ
START WITH 1
NOCACHE;
--==>> Sequence CT_SEQ이(가) 생성되었습니다.
--[CAREER 시퀀스]
--[17 - C_SEQ]
CREATE SEQUENCE C_SEQ
START WITH 1
NOCACHE;
--==>> Sequence C_SEQ이(가) 생성되었습니다.
--[INQUIRY 시퀀스]
--[18 - IQ_SEQ]
CREATE SEQUENCE IQ_SEQ
START WITH 1
NOCACHE;
--==>> Sequence IQ_SEQ이(가) 생성되었습니다.
--[PROTEST 시퀀스]
--[19 - PROTEST_SEQ]
CREATE SEQUENCE PROTEST_SEQ
START WITH 1
NOCACHE;
--==>> Sequence PROTEST_SEQ이(가) 생성되었습니다.
--[FAQ 시퀀스]
--[20 - FAQ_SEQ]
CREATE SEQUENCE FAQ_SEQ
START WITH 1
NOCACHE;
--==>> Sequence FAQ_SEQ이(가) 생성되었습니다.
--[ADMIN 시퀀스]
--[21 - ADMIN_SEQ]
CREATE SEQUENCE ADMIN_SEQ
START WITH 1
NOCACHE;
--==>> Sequence ADMIN_SEQ이(가) 생성되었습니다.
--[TECH_QnA 시퀀스]
--[22 - TQ_SEQ]
CREATE SEQUENCE TQ_SEQ
START WITH 1
NOCACHE;
--==>> Sequence TQ_SEQ이(가) 생성되었습니다.
--[TQ_REPLY2 시퀀스]
--[23 - T2_SEQ]
CREATE SEQUENCE T2_SEQ
START WITH 1
NOCACHE;
--==>> Sequence T2_SEQ이(가) 생성되었습니다.
--[TQ2_REPORT 시퀀스]
--[24 - Q2R_SEQ]
CREATE SEQUENCE Q2R_SEQ
START WITH 1
NOCACHE;
--==>> Sequence Q2R_SEQ이(가) 생성되었습니다.
--[CQnA_PIC 시퀀스]
--[25 - CQP_SEQ]
CREATE SEQUENCE CQP_SEQ
START WITH 1
NOCACHE;
--==>> Sequence CQP_SEQ이(가) 생성되었습니다.
--[TQ_REPORT 시퀀스]
--[26 - TQR_SEQ]
CREATE SEQUENCE TQR_SEQ
START WITH 1
NOCACHE;
--==>> Sequence TQR_SEQ이(가) 생성되었습니다.
--[TQ_FILE 시퀀스]
--[27 - TQF_SEQ]
CREATE SEQUENCE TQF_SEQ
START WITH 1
NOCACHE;
--==>> Sequence TQF_SEQ이(가) 생성되었습니다.
--[TQ_PROCESS 시퀀스]
--[28 - TQP_SEQ]
CREATE SEQUENCE TQP_SEQ
START WITH 1
NOCACHE;
--==>> Sequence TQP_SEQ이(가) 생성되었습니다.
--[TQ2_PROCESS 시퀀스]
--[29 - T2P_SEQ]
CREATE SEQUENCE T2P_SEQ
START WITH 1
NOCACHE;
--==>> Sequence T2P_SEQ이(가) 생성되었습니다.
--[TECH_UP 시퀀스]
--[30 - TU_SEQ]
CREATE SEQUENCE TU_SEQ
START WITH 1
NOCACHE;
--==>> Sequence TU_SEQ이(가) 생성되었습니다.
--[TQ_COMMENT 시퀀스]
--[31 - TC_SEQ]
CREATE SEQUENCE TC_SEQ
START WITH 1
NOCACHE;
--==>> Sequence TC_SEQ이(가) 생성되었습니다.
--[TQC_REPORT 시퀀스]
--[32 - QCR_SEQ]
CREATE SEQUENCE QCR_SEQ
START WITH 1
NOCACHE;
--==>> Sequence QCR_SEQ이(가) 생성되었습니다.
--[TQC_PROCESS 시퀀스]
--[33 - TCP_SEQ]
CREATE SEQUENCE TCP_SEQ
START WITH 1
NOCACHE;
--==>> Sequence TCP_SEQ이(가) 생성되었습니다.
--[STEP 시퀀스]
--[34 - ST_SEQ]
CREATE SEQUENCE ST_SEQ
START WITH 1
NOCACHE;
--==>> Sequence ST_SEQ이(가) 생성되었습니다.
--[GRADE 시퀀스]
--[35 - GRADE_SEQ]
CREATE SEQUENCE GRADE_SEQ
START WITH 1
NOCACHE;
--==>> Sequence GRADE_SEQ이(가) 생성되었습니다.
--[G_FILTER 시퀀스]
--[36 - GF_SEQ]
CREATE SEQUENCE GF_SEQ
START WITH 1
NOCACHE;
--==>> Sequence GF_SEQ이(가) 생성되었습니다.
--[LINK 시퀀스]
--[37 - LINK_SEQ]
CREATE SEQUENCE LINK_SEQ
START WITH 1
NOCACHE;
--==>> Sequence LINK_SEQ이(가) 생성되었습니다.
--[MILESTONE 시퀀스]
--[38 - MS_SEQ]
CREATE SEQUENCE MS_SEQ
START WITH 1
NOCACHE;
--==>> Sequence MS_SEQ이(가) 생성되었습니다.
--[MS_FILTER 시퀀스]
--[39 - MSF_SEQ]
CREATE SEQUENCE MSF_SEQ
START WITH 1
NOCACHE;
--==>> Sequence MSF_SEQ이(가) 생성되었습니다.
--[MEMBER_APPLY 시퀀스]
--[40 - MA_SEQ]
CREATE SEQUENCE MA_SEQ
START WITH 1
NOCACHE;
--==>> Sequence MA_SEQ이(가) 생성되었습니다.20240223_PMOA_KJM_FINAL_2.sql
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--▩▩▩▩▩▩▩『가데이터 넣기』▩▩▩▩▩▩▩▩
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
SELECT USER
FROM DUAL;
--==>> PMOA
--====================================
--[시퀀스 조회]
--====================================
SELECT *
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = 'MA_SEQ';
--==>>
/*
MA_SEQ 1 9999999999999999999999999999 1 N N 0 1
*/
--====================================
--======================================
--[CODE 에 넣을 코드 형식]
--======================================
SELECT TO_CHAR('MA' || LPAD(MA_SEQ.NEXTVAL, 4, '0')) AS "MA_CODE"
FROM DUAL;
--======================================
--=======================================================
--[테이블 구조 조회]
--=======================================================
DESC MEMBER_APPLY;
--==>>
/*
이름 널? 유형
-------- -------- --------------
MA_CODE NOT NULL VARCHAR2(10) -- 멤버 지원 코드
CONTENT VARCHAR2(4000) -- 포부
KDATE DATE -- 지원일
DDATE DATE -- 결정일
MEM_CODE VARCHAR2(10) -- 회원 코드
RC_CODE VARCHAR2(10) -- 직무 구성 코드
AR_CODE VARCHAR2(10) -- 결과 코드
*/
--=======================================================
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--===================================
--[멤버 지원 테이블 - MEMBER_APPLY]
-- 가데이터 입력
--===================================
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒
--【개설 신청 코드 - 1번】
--▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒
--[① 팀장 입력] - QWE 이윤수
INSERT INTO MEMBER_APPLY
(MA_CODE, CONTENT, KDATE, DDATE
, MEM_CODE
, RC_CODE
, AR_CODE)
VALUES (TO_CHAR('MA' || LPAD(MA_SEQ.NEXTVAL, 4, '0'))
, '팀장이 되었다 ㅎㅎ'
, SYSDATE
, NULL
, TO_CHAR('MEM' || LPAD(1, 4, '0'))
, TO_CHAR('RC' || LPAD(1, 4, '0'))
, TO_CHAR('AR' || LPAD(0, 4, '0')));
--[② 백엔드 1 입력] - ASD 제갈윤수
INSERT INTO MEMBER_APPLY
(MA_CODE, CONTENT, KDATE, DDATE
, MEM_CODE
, RC_CODE
, AR_CODE)
VALUES (TO_CHAR('MA' || LPAD(MA_SEQ.NEXTVAL, 4, '0'))
, '열심히 하겠습니다.'
, SYSDATE
, NULL
, TO_CHAR('MEM' || LPAD(2, 4, '0'))
, TO_CHAR('RC' || LPAD(2, 4, '0'))
, TO_CHAR('AR' || LPAD(0, 4, '0')));
--[② 백엔드 2 입력] - ZXC 사공윤수
INSERT INTO MEMBER_APPLY
(MA_CODE, CONTENT, KDATE, DDATE
, MEM_CODE
, RC_CODE
, AR_CODE)
VALUES (TO_CHAR('MA' || LPAD(MA_SEQ.NEXTVAL, 4, '0'))
, '백엔드 최정상을 향해~'
, SYSDATE
, NULL
, TO_CHAR('MEM' || LPAD(3, 4, '0'))
, TO_CHAR('RC' || LPAD(2, 4, '0'))
, TO_CHAR('AR' || LPAD(0, 4, '0')));
--[③ 프론트엔드 1 입력] - RTY 김윤수
INSERT INTO MEMBER_APPLY
(MA_CODE, CONTENT, KDATE, DDATE
, MEM_CODE
, RC_CODE
, AR_CODE)
VALUES (TO_CHAR('MA' || LPAD(MA_SEQ.NEXTVAL, 4, '0'))
, '저는 CSS 마스터입니다!'
, SYSDATE
, NULL
, TO_CHAR('MEM' || LPAD(4, 4, '0'))
, TO_CHAR('RC' || LPAD(3, 4, '0'))
, TO_CHAR('AR' || LPAD(0, 4, '0')));
--[③ 프론트엔드 2 입력] - CVB 박윤수
INSERT INTO MEMBER_APPLY
(MA_CODE, CONTENT, KDATE, DDATE
, MEM_CODE
, RC_CODE
, AR_CODE)
VALUES (TO_CHAR('MA' || LPAD(MA_SEQ.NEXTVAL, 4, '0'))
, '웹 디자인은 내게 맡겨!'
, SYSDATE
, NULL
, TO_CHAR('MEM' || LPAD(5, 4, '0'))
, TO_CHAR('RC' || LPAD(3, 4, '0'))
, TO_CHAR('AR' || LPAD(0, 4, '0')));
--==>> 1 행 이(가) 삽입되었습니다. * 5
--▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒
--【개설 신청 코드 - 2번】
--▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒
-- 회원, 직무, 결과
--[① 팀장 입력] - FGH 최윤수
INSERT INTO MEMBER_APPLY
(MA_CODE, CONTENT, KDATE, DDATE
, MEM_CODE
, RC_CODE
, AR_CODE)
VALUES (TO_CHAR('MA' || LPAD(MA_SEQ.NEXTVAL, 4, '0'))
, '내가 팀장이노? ㅎㅎ~'
, SYSDATE
, NULL
, TO_CHAR('MEM' || LPAD(6, 4, '0'))
, TO_CHAR('RC' || LPAD(4, 4, '0'))
, TO_CHAR('AR' || LPAD(0, 4, '0')));
--[② 백엔드 입력] - VBN 남궁윤수
INSERT INTO MEMBER_APPLY
(MA_CODE, CONTENT, KDATE, DDATE
, MEM_CODE
, RC_CODE
, AR_CODE)
VALUES (TO_CHAR('MA' || LPAD(MA_SEQ.NEXTVAL, 4, '0'))
, '백백백~ 컴백~'
, SYSDATE
, NULL
, TO_CHAR('MEM' || LPAD(7, 4, '0'))
, TO_CHAR('RC' || LPAD(5, 4, '0'))
, TO_CHAR('AR' || LPAD(0, 4, '0')));
--[② 백엔드 입력] - UIO 정윤수
INSERT INTO MEMBER_APPLY
(MA_CODE, CONTENT, KDATE, DDATE
, MEM_CODE
, RC_CODE
, AR_CODE)
VALUES (TO_CHAR('MA' || LPAD(MA_SEQ.NEXTVAL, 4, '0'))
, '백엔드 대통령'
, SYSDATE
, NULL
, TO_CHAR('MEM' || LPAD(8, 4, '0'))
, TO_CHAR('RC' || LPAD(5, 4, '0'))
, TO_CHAR('AR' || LPAD(0, 4, '0')));
--[③ 프론트엔드 입력] - QWER 장윤수
INSERT INTO MEMBER_APPLY
(MA_CODE, CONTENT, KDATE, DDATE
, MEM_CODE, RC_CODE, AR_CODE)
VALUES (TO_CHAR('MA' || LPAD(MA_SEQ.NEXTVAL, 4, '0'))
, '프로로로론 프로로론'
, SYSDATE
, NULL
, TO_CHAR('MEM' || LPAD(9, 4, '0'))
, TO_CHAR('RC' || LPAD(6, 4, '0'))
, TO_CHAR('AR' || LPAD(0, 4, '0')));
--****************************************************
--[멤버 지원 & 팀원별 직무 구성 & 직무 테이블 JOIN]
--****************************************************
SELECT *
FROM MEMBER_APPLY MA
INNER JOIN ROLE_COMP RC
ON MA.RC_CODE = RC.RC_CODE
INNER JOIN MEMBER_ROLE MR
ON RC.MR_CODE = MR.MR_CODE;
--****************************************************
--**************************************
--[일반 회원 테이블(MEMBER) 조회]
--**************************************
SELECT *
FROM MEMBER;
--**************************************
--**************************************
--[개설 신청 테이블(MEMBER) 조회]
--**************************************
SELECT *
FROM APP_OPENING;
--**************************************
--**********************************************
--[팀원별 직무 구성 테이블(ROLE_COMP) 조회]
--**********************************************
SELECT *
FROM ROLE_COMP
ORDER BY RC_CODE;
--**********************************************
--**********************************************
--[직무 테이블(MEMBER_ROLE) 조회]
--**********************************************
SELECT *
FROM MEMBER_ROLE
ORDER BY MR_CODE;
--**********************************************
--**************************************
--[지원 결과 테이블(A_RESULT) 조회]
--**************************************
SELECT *
FROM A_RESULT;
--**************************************
--**************************************
--[개설 신청 테이블(APP_OPENING) 조회]
--**************************************
SELECT *
FROM APP_OPENING;
--**************************************
SELECT *
FROM PJ_MILE_VIEW;
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--▩▩▩▩『트리거 생성 - 마일스톤 자동 INSERT』▩▩▩▩
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
CREATE OR REPLACE TRIGGER TRG_MILES
AFTER
INSERT ON C_PROJECT
BEGIN
INSERT INTO MILESTONE(MS_CODE, CP_CODE)
VALUES(TO_CHAR('MS' || LPAD(MS_SEQ.NEXTVAL, 4, '0'))
, TO_CHAR('CP' || LPAD(CP_SEQ.CURRVAL, 4, '0')));
END;
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--===================================
--[업무 보고서 테이블 - B_REPORT]
-- 가데이터 입력
--===================================
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--=======================================================
--[테이블 구조 조회]
--=======================================================
DESC B_REPORT;
--==>>
/*
이름 널? 유형
--------- -------- --------------
BR_CODE NOT NULL VARCHAR2(10) -- 업무 보고서 코드
KDATE DATE -- 작성 일자
CONTENT VARCHAR2(4000) -- 진행 내용
SUMMARY VARCHAR2(4000) -- 요약
TASK_CODE VARCHAR2(10) -- 태스크 코드
BR_CODE2 VARCHAR2(10) -- 회신 코드 참조
*/
--=======================================================
INSERT INTO B_REPORT(BR_CODE, KDATE, CONTENT
, SUMMARY
, TASK_CODE
, BR_CODE2)
VALUES (TO_CHAR('BR' || LPAD(BR_SEQ.NEXTVAL, 4, '0'))
, TO_DATE('2024-06-14', 'YYYY-MM-DD')
, '저는 정말정말 열심히 했습니다.'
, '짱열심히함!'
, TO_CHAR('TASK' || LPAD(1, 4, '0'))
, NULL);
INSERT INTO B_REPORT(BR_CODE, KDATE, CONTENT
, SUMMARY
, TASK_CODE
, BR_CODE2)
VALUES (TO_CHAR('BR' || LPAD(BR_SEQ.NEXTVAL, 4, '0'))
, TO_DATE('2024-06-01', 'YYYY-MM-DD')
, 'ERD 왜케 어렵눙 ㅠㅠ'
, '너무 어려워요'
, TO_CHAR('TASK' || LPAD(2, 4, '0'))
, NULL);
INSERT INTO B_REPORT(BR_CODE, KDATE, CONTENT
, SUMMARY, TASK_CODE, BR_CODE2)
VALUES (TO_CHAR('BR' || LPAD(BR_SEQ.NEXTVAL, 4, '0'))
, TO_DATE('2024-06-10', 'YYYY-MM-DD')
, '완벽한 뷰페이지 작업 『완. 료.★』'
, '완벽ㅎㅎ'
, TO_CHAR('TASK' || LPAD(1, 4, '0'))
, NULL);
--**************************************
--[태스크 테이블(TASK) 조회]
--**************************************
SELECT *
FROM TASK;
--**************************************
SELECT *
FROM B_REPORT;
SELECT *
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'C_PROJECT';
SELECT *
FROM MEMBER;
SELECT *
FROM C_PROJECT;20240229_PMOA_KJM.sql
SELECT USER
FROM DUAL;
--==>> PMOA
--=========================================================
--[혜성 오빠거 프로시저 고치기]============================
--=========================================================
--
--CREATE OR REPLACE PROCEDURE MEMBER_JOIN
--(
-- V_ID IN VARCHAR2,
-- V_PW IN VARCHAR2,
-- V_EMAIL IN VARCHAR2
--)
--IS
--BEGIN
-- INSERT INTO MEMBER (MEM_CODE, ID, PW, NICKNAME, MBTI_CODE) VALUES(TO_CHAR('MEM' || LPAD(MEM_SEQ.NEXTVAL, 4, '0')), V_ID, V_PW, V_ID, 'MBTI0008');
-- INSERT INTO MEM_INFO (MI_CODE, EMAIL, MEM_CODE, GENDER_CODE) VALUES(TO_CHAR('MI' || LPAD(MI_SEQ.NEXTVAL, 4, '0')), V_EMAIL, TO_CHAR('MEM' || LPAD(MEM_SEQ.CURRVAL, 4, '0')), 'GENDER0001');
--
--END;
--
--EXEC MEMBER_JOIN('TEST001', '1234', 'HELLO@GMAIL.COM');
--=========================================================
--=========================================================
--=========================================================
--=========================================================
--[길님의 댓글 개수 구하기 쿼리문 도와주기 ㅎㅎ]===========
--=========================================================
--SELECT CT.TC_NUM + CT.TT_NUM AS 총댓글
-- , CT.MEM_CODE AS 멤버코드
--FROM
--(
-- SELECT (
-- SELECT COUNT(TC.TC_CODE)
-- FROM MEMBER m1 LEFT JOIN TQ_COMMENT tc ON m1.MEM_CODE = tc.MEM_CODE
-- WHERE M1.MEM_CODE = M.MEM_CODE
-- GROUP BY m1.MEM_CODE
--
-- ) AS TC_NUM
-- ,
-- (
-- SELECT COUNT(tr.T2_CODE) AS TT_COUNT
-- FROM MEMBER m2 LEFT JOIN TQ_REPLY2 tr ON m2.MEM_CODE = tr.MEM_CODE
-- WHERE M2.MEM_CODE = M.MEM_CODE
-- GROUP BY m2.MEM_CODE
-- ) AS TT_NUM
-- , M.MEM_CODE AS MEM_CODE
-- FROM MEMBER M
--) CT;
--=========================================================
--=========================================================
--=========================================================
--[필요한 데이터 정리]
--『공개여부 : 0 - 공개, 1 - 비공개』
--○ 프로필 사진
--○ 닉네임
--○ 이메일 / 이메일 블라인드 여부
--○ 백엔드 스킬 점수, 프론트엔드 스킬 점수, 매너 점수
--○ 성별 / 성별 블라인드 여부
--○ 생년월일 / 생년월일 블라인드 여부
--○ MBTI
--○ Skill 태그
--『CareerDTO』
--○ 경력 - 회사명, 직종, 근무기간, 총 근무기간 / 경력 블라인드 여부
--『PjHistoryDTO』
--○ 참여한 프로젝트
-- - 프로젝트명, 담당직무, 프로젝트 요약, 프로젝트 기간, 완료 여부
-- - 완료 / 미완료 프로젝트 비율
--***[미완료]***
--▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒
--▒▒▒▒▒▒[프로필 사진 가데이터 입력]▒▒▒▒▒▒
--▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒
INSERT INTO PROFILE(PI_CODE, PATH, MEM_CODE)
VALUES (TO_CHAR('PI' || LPAD(PI_SEQ.NEXTVAL, 4, '0')), '???', 'MEM0001');
--▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒
--▒▒▒▒▒▒[스킬 태그 가데이터 입력]▒▒▒▒▒▒▒
--▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒
INSERT INTO SKILL_TAG(KT_CODE, MEM_CODE, TAG_NAME)
VALUES (TO_CHAR('KT' || LPAD(KT_SEQ.NEXTVAL, 4, '0')), 'MEM0001', 'Java');
INSERT INTO SKILL_TAG(KT_CODE, MEM_CODE, TAG_NAME)
VALUES (TO_CHAR('KT' || LPAD(KT_SEQ.NEXTVAL, 4, '0')), 'MEM0001', 'Oracle');
INSERT INTO SKILL_TAG(KT_CODE, MEM_CODE, TAG_NAME)
VALUES (TO_CHAR('KT' || LPAD(KT_SEQ.NEXTVAL, 4, '0')), 'MEM0001', 'Mybatis');
INSERT INTO SKILL_TAG(KT_CODE, MEM_CODE, TAG_NAME)
VALUES (TO_CHAR('KT' || LPAD(KT_SEQ.NEXTVAL, 4, '0')), 'MEM0001', 'HTML');
INSERT INTO SKILL_TAG(KT_CODE, MEM_CODE, TAG_NAME)
VALUES (TO_CHAR('KT' || LPAD(KT_SEQ.NEXTVAL, 4, '0')), 'MEM0001', 'CSS');
--[스킬태그 조회]======
SELECT *
FROM SKILL_TAG;
--=====================
--▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒
--▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒
--▒▒▒▒▒▒▒▒[경력 가데이터 입력]▒▒▒▒▒▒▒▒
--▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒
SELECT *
FROM CAREER
WHERE MI_CODE = 'MI0001';
INSERT INTO CAREER(C_CODE, NAME
, KDATE, ISC_OPEN
, SDATE, EDATE, MI_CODE)
VALUES (TO_CHAR('C' || LPAD(C_SEQ.NEXTVAL, 4, '0')), '애플힙디자인 헬스장'
, SYSDATE, 0
, TO_DATE('2022-08-15', 'YYYY-MM-DD'), TO_DATE('2023-02-19', 'YYYY-MM-DD'), 'MI0001');
--▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒
--▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒
--▒▒▒▒▒▒▒▒[기술 Q&A 가데이터 입력]▒▒▒▒▒▒▒▒
--▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒
INSERT INTO C_QNA(CQ_CODE, TITLE
, CONTENT
, KDATE, VIEWS, MEM_CODE)
VALUES (TO_CHAR('CQ' || LPAD(CQ_SEQ.NEXTVAL, 4, '0')), '아...삼일절에도 코딩한다꼬?!'
, '하...바람 오질라게부네~! 근데... 이거 프로젝트 게시판인데... 배치 어떻노? 텍스트 크기랑 공백 신경써봤는디~ㅎㅎ ο(=?ω＜=)ρ⌒☆'
, TO_DATE('2024-03-01', 'YYYY-MM-DD'), 7, 'MEM0001');
--▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒
--***[미완료]***
--[프로필 사진]========================================
SELECT PI.*
FROM MEMBER M
INNER JOIN PROFILE PI
ON M.MEM_CODE = PI.MEM_CODE
WHERE M.MEM_CODE = 'MEM0001';
--=====================================================
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--○ 닉네임
--○ 이메일 / 이메일 블라인드 여부
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--[닉네임]=====================================
SELECT NICKNAME
FROM MEMBER
WHERE MEM_CODE = 'MEM0001';
--==>> 이윤수
--[이메일 / 이메일 공개여부 = 0(공개)]===================
SELECT MI.EMAIL AS EMAIL
FROM MEMBER M
INNER JOIN MEM_INFO MI
ON M.MEM_CODE = MI.MEM_CODE
WHERE M.MEM_CODE = 'MEM0001';
--==>> QWE@TEST.COM
SELECT MI.ISE_OPEN AS EMAIL_OPEN
FROM MEMBER M
INNER JOIN MEM_INFO MI
ON M.MEM_CODE = MI.MEM_CODE
WHERE M.MEM_CODE = 'MEM0001';
--==>> 0 (공개)
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--○ 백엔드 스킬 점수, 프론트엔드 스킬 점수, 매너 점수
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--[백엔드 스킬 점수]==========================
SELECT 1000 + NVL(SUM(EV.SCORE), 0) AS BACK_SCORE
FROM MEMBER M
INNER JOIN MEMBER_APPLY MA
ON M.MEM_CODE = MA.MEM_CODE
INNER JOIN ROLE_COMP RC
ON MA.RC_CODE = RC.RC_CODE
INNER JOIN MEMBER_ROLE MR
ON RC.MR_CODE = MR.MR_CODE
INNER JOIN EVALUATION EV
ON MA.MA_CODE = EV.MA_CODEP
INNER JOIN EV_DEVISION ED
ON EV.ED_CODE = ED.ED_CODE
WHERE M.MEM_CODE = 'MEM0001'
AND MR.MR_CODE = 'MR0002'
AND ED.ED_CODE = 'ED0001';
--==>> 1006
--[백엔드 점수 상위 30명인지 확인 - 『숲』 등급]
--[백엔드 스킬 점수]==========================
SELECT 1000 + NVL(SUM(EV.SCORE), 0) AS BACK_SCORE
FROM MEMBER M
INNER JOIN MEMBER_APPLY MA
ON M.MEM_CODE = MA.MEM_CODE
INNER JOIN ROLE_COMP RC
ON MA.RC_CODE = RC.RC_CODE
INNER JOIN MEMBER_ROLE MR
ON RC.MR_CODE = MR.MR_CODE
INNER JOIN EVALUATION EV
ON MA.MA_CODE = EV.MA_CODEP
INNER JOIN EV_DEVISION ED
ON EV.ED_CODE = ED.ED_CODE
WHERE MR.MR_CODE = 'MR0002'
AND ED.ED_CODE = 'ED0001';
--[백엔드로 프로젝트에 참여한 경험이 있는 회원의 점수]
SELECT M.MEM_CODE AS 회원코드
, M.NICKNAME AS 닉네임
, MR.MR_CODE AS 직무코드
, MR.ROLE AS 직무
, ED.ED_CODE AS 점수구별코드
, ED.DIVISION AS 점수구별
, 1000 + NVL(SUM(EV.SCORE), 0) AS 점수
FROM MEMBER M
LEFT OUTER JOIN MEMBER_APPLY MA
ON M.MEM_CODE = MA.MEM_CODE
LEFT OUTER JOIN ROLE_COMP RC
ON MA.RC_CODE = RC.RC_CODE
LEFT OUTER JOIN MEMBER_ROLE MR
ON RC.MR_CODE = MR.MR_CODE
LEFT OUTER JOIN EVALUATION EV
ON MA.MA_CODE = EV.MA_CODEP
LEFT OUTER JOIN EV_DEVISION ED
ON EV.ED_CODE = ED.ED_CODE
WHERE MR.MR_CODE = 'MR0002'
GROUP BY M.MEM_CODE, M.NICKNAME, MR.MR_CODE, MR.ROLE, ED.ED_CODE, ED.DIVISION
ORDER BY M.MEM_CODE;
--[백엔드로 프로젝트에 참여한 경험이 없는 회원의 점수 - 기본값]
SELECT M.MEM_CODE AS 회원코드
, M.NICKNAME AS 닉네임
, MR.MR_CODE AS 직무코드
, MR.ROLE AS 직무
FROM MEMBER M
LEFT OUTER JOIN MEMBER_APPLY MA
ON M.MEM_CODE = MA.MEM_CODE
LEFT OUTER JOIN ROLE_COMP RC
ON MA.RC_CODE = RC.RC_CODE
LEFT OUTER JOIN MEMBER_ROLE MR
ON RC.MR_CODE = MR.MR_CODE
LEFT OUTER JOIN EVALUATION EV
ON MA.MA_CODE = EV.MA_CODEP
LEFT JOIN EV_DEVISION ED
ON EV.ED_CODE = ED.ED_CODE
AND ED.ED_CODE = 'ED0001'
WHERE MR.MR_CODE != 'MR0002'
OR MR.MR_CODE IS NULL
GROUP BY M.MEM_CODE, M.NICKNAME, MR.MR_CODE, MR.ROLE
ORDER BY M.MEM_CODE;
--[인원수 안 맞아서 백엔드 참여 인원 찾기]
SELECT M.NICKNAME, MR.ROLE
FROM MEMBER M
LEFT OUTER JOIN MEMBER_APPLY MA
ON M.MEM_CODE = MA.MEM_CODE
LEFT OUTER JOIN ROLE_COMP RC
ON MA.RC_CODE = RC.RC_CODE
LEFT OUTER JOIN MEMBER_ROLE MR
ON RC.MR_CODE = MR.MR_CODE
LEFT OUTER JOIN EVALUATION EV
ON MA.MA_CODE = EV.MA_CODEP
LEFT JOIN EV_DEVISION ED
ON EV.ED_CODE = ED.ED_CODE
WHERE MR.ROLE = '백엔드'
GROUP BY M.NICKNAME, MR.ROLE;
--[프론트엔드 스킬 점수]======================
SELECT 1000 + NVL(SUM(EV.SCORE), 0) AS FRONT_SCORE
FROM MEMBER M
INNER JOIN MEMBER_APPLY MA
ON M.MEM_CODE = MA.MEM_CODE
INNER JOIN ROLE_COMP RC
ON MA.RC_CODE = RC.RC_CODE
INNER JOIN MEMBER_ROLE MR
ON RC.MR_CODE = MR.MR_CODE
INNER JOIN EVALUATION EV
ON MA.MA_CODE = EV.MA_CODEP
INNER JOIN EV_DEVISION ED
ON EV.ED_CODE = ED.ED_CODE
WHERE M.MEM_CODE = 'MEM0001'
AND MR.MR_CODE = 'MR0003'
AND ED.ED_CODE = 'ED0001';
--==>> 1000
--[매너 점수]==================================
SELECT 36.5 + NVL(SUM(DECODE(EV.SCORE
, 5, 0.2
, 4, 0.1
, 3, 0
, 2, -0.1
, 1, -0.2)), 0) AS MANNER_SCORE
FROM MEMBER M
INNER JOIN MEMBER_APPLY MA
ON M.MEM_CODE = MA.MEM_CODE
INNER JOIN ROLE_COMP RC
ON MA.RC_CODE = RC.RC_CODE
INNER JOIN MEMBER_ROLE MR
ON RC.MR_CODE = MR.MR_CODE
INNER JOIN EVALUATION EV
ON MA.MA_CODE = EV.MA_CODEP
INNER JOIN EV_DEVISION ED
ON EV.ED_CODE = ED.ED_CODE
WHERE M.MEM_CODE = 'MEM0030'
AND ED.ED_CODE = 'ED0002';
--==>> 36.4
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--○ 성별 / 성별 블라인드 여부
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--[성별 / 성별 공개여부 = 0(공개)]========================
SELECT G.GENDER AS GENDER
FROM MEMBER M
INNER JOIN MEM_INFO MI
ON M.MEM_CODE = MI.MEM_CODE
INNER JOIN GENDER G
ON MI.GENDER_CODE = G.GENDER_CODE
WHERE M.MEM_CODE = 'MEM0001';
--==>> 남성
SELECT MI.ISG_OPEN AS GENDER_OPEN
FROM MEMBER M
INNER JOIN MEM_INFO MI
ON M.MEM_CODE = MI.MEM_CODE
INNER JOIN GENDER G
ON MI.GENDER_CODE = G.GENDER_CODE
WHERE M.MEM_CODE = 'MEM0001';
--==>> 0 (공개)
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--○ 생년월일 / 생년월일 블라인드 여부
--○ MBTI
--○ SKILL
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--[생년월일 / 생년월일 공개여부 = 0(공개)]===============
SELECT TO_CHAR(MI.BDAY, 'YYYY"년" FMMM"월" DD"일"') AS BIRTH_DAY
FROM MEMBER M
INNER JOIN MEM_INFO MI
ON M.MEM_CODE = MI.MEM_CODE
WHERE M.MEM_CODE = 'MEM0001';
--==>> 1995년 1월 6일
SELECT MI.ISB_OPEN AS BIRTH_OPEN
FROM MEMBER M
INNER JOIN MEM_INFO MI
ON M.MEM_CODE = MI.MEM_CODE
WHERE M.MEM_CODE = 'MEM0001';
--==>> 0 (공개)
--[MBTI]======================================
SELECT MBTI.MBTI AS MBTI
FROM MEMBER M
INNER JOIN MBTI MBTI
ON M.MBTI_CODE = MBTI.MBTI_CODE
WHERE M.MEM_CODE = 'MEM0001';
--==>> INFP
--[Skill-Tag]=====================================
SELECT TAG_NAME AS SKILL_TAG
FROM MEMBER M
INNER JOIN SKILL_TAG KT
ON M.MEM_CODE = KT.MEM_CODE
WHERE M.MEM_CODE = 'MEM0001';
--==>>
/*
Java
Oracle
Mybatis
HTML
CSS
*/
--[별도의 『MyPage_CareerDTO』 로 구성]
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--○ 경력 - 회사명, 입사일, 퇴사일, 총 근무기간 / 경력 블라인드 여부
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--[경력 / 경력 공개여부 = 0(공개)]================================
--① [회사명, 입사일, 퇴사일, 근속년월 구하기]==================================
SELECT CT.회사명 AS COMPANY_NAME
, CT.입사일 AS JOIN_DATE
, CT.퇴사일 AS LEAVE_DATE
, CT.근속년 AS WORKED_YEAR
, CT.근속월 AS WORKED_MONTH
FROM
(
SELECT C.NAME AS 회사명
, TO_CHAR(C.SDATE, 'YYYY.FMMM') AS 입사일
, TO_CHAR(C.EDATE, 'YYYY.FMMM') AS 퇴사일
, TRUNC((C.EDATE - C.SDATE) / 365) AS 근속년
, TRUNC(MOD(MONTHS_BETWEEN(C.EDATE, C.SDATE), 12)) AS 근속월
FROM MEMBER M
INNER JOIN MEM_INFO MI
ON M.MEM_CODE = MI.MEM_CODE
INNER JOIN CAREER C
ON MI.MI_CODE = C.MI_CODE
WHERE M.MEM_CODE = 'MEM0001'
AND C.ISC_OPEN = 0
) CT;
--==>>
/*
농심 2022.1 2024.3 2 2
애플힙디자인 헬스장 2022.8 2023.2 0 6
옥장판세일즈 2023.7 2023.12 0 4
*/
--② [총 근속일수 구하기]=======================================================
SELECT NVL(SUM(CT.근속년), 0)
, NVL(SUM(CT.근속월), 0)
FROM
(
SELECT C.NAME AS 회사명
, TO_CHAR(C.SDATE, 'YYYY.FMMM') AS 입사일
, TO_CHAR(C.EDATE, 'YYYY.FMMM') AS 퇴사일
, TRUNC(MONTHS_BETWEEN(C.EDATE, C.SDATE) / 12) AS 근속년
, TRUNC(MOD(MONTHS_BETWEEN(C.EDATE, C.SDATE), 12)) AS 근속월
, M.MEM_CODE AS 회원코드
FROM MEMBER M
INNER JOIN MEM_INFO MI
ON M.MEM_CODE = MI.MEM_CODE
INNER JOIN CAREER C
ON MI.MI_CODE = C.MI_CODE
WHERE M.MEM_CODE = 'MEM0063'
AND C.ISC_OPEN = 0
) CT;
--==============================================================================
--★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
--③ [하나의 DTO로 구성하기 위해 통합]==========================================
--★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
SELECT C.NAME AS COMPANY_NAME
, TO_CHAR(C.SDATE, 'YYYY.FMMM') AS JOIN_DATE
, TO_CHAR(C.EDATE, 'YYYY.FMMM') AS LEAVE_DATE
, TRUNC(MONTHS_BETWEEN(C.EDATE, C.SDATE) / 12) AS WORKED_YEAR
, TRUNC(MOD(MONTHS_BETWEEN(C.EDATE, C.SDATE), 12)) AS WORKED_MONTH
,
(
SELECT SUM(CT.근속년)
FROM
(
SELECT TRUNC(MONTHS_BETWEEN(C.EDATE, C.SDATE) / 12) AS 근속년
FROM MEMBER M
INNER JOIN MEM_INFO MI
ON M.MEM_CODE = MI.MEM_CODE
INNER JOIN CAREER C
ON MI.MI_CODE = C.MI_CODE
WHERE M.MEM_CODE = 'MEM0063'
AND C.ISC_OPEN = 0
) CT
) AS TOT_WORKED_YEAR
,
(
SELECT SUM(CT.근속월)
FROM
(
SELECT TRUNC(MOD(MONTHS_BETWEEN(C.EDATE, C.SDATE), 12)) AS 근속월
FROM MEMBER M
INNER JOIN MEM_INFO MI
ON M.MEM_CODE = MI.MEM_CODE
INNER JOIN CAREER C
ON MI.MI_CODE = C.MI_CODE
WHERE M.MEM_CODE = 'MEM0063'
AND C.ISC_OPEN = 0
) CT
) AS TOT_WORKED_MONTH
FROM MEMBER M
INNER JOIN MEM_INFO MI
ON M.MEM_CODE = MI.MEM_CODE
INNER JOIN CAREER C
ON MI.MI_CODE = C.MI_CODE
WHERE M.MEM_CODE = 'MEM0063'
AND C.ISC_OPEN = 0;
--[별도의 『MyPage_PjHistoryDTO』 로 구성]
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--○ 참여한 프로젝트
-- - 프로젝트명, 담당직무, 프로젝트 요약, 프로젝트 기간, 완료 여부
-- - 완료 / 미완료 프로젝트 비율
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
SELECT AP.PRJ_NAME AS PRJ_NAME
, MR.ROLE AS PRJ_ROLE
, AP.SUMMARY AS PRJ_SUMMARY
, TO_CHAR(AP.SDATE, 'YYYY.MM.DD') AS PRJ_START_DATE
, TO_CHAR(AP.EDATE, 'YYYY.MM.DD') AS PRJ_END_DATE
, DECODE(FR.TITLE, NULL, '미완료', '완료') AS PRJ_COMPLETE
FROM MEMBER M
INNER JOIN MEMBER_APPLY MA
ON M.MEM_CODE = MA.MEM_CODE
INNER JOIN ROLE_COMP RC
ON MA.RC_CODE = RC.RC_CODE
INNER JOIN MEMBER_ROLE MR
ON RC.MR_CODE = MR.MR_CODE
INNER JOIN APP_OPENING AP
ON RC.AP_CODE = AP.AP_CODE
INNER JOIN C_PROJECT CP
ON AP.AP_CODE = CP.AP_CODE
INNER JOIN FINAL_REPORT FR
ON CP.CP_CODE = FR.CP_CODE
WHERE M.MEM_CODE = 'MEM0001'
AND AR_CODE = 'AR0000';
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
-- 마이페이지 - 2페이지
-- ▶ 본인이 작성한 게시글
-- =====================
-- [ 기술 Q&A 게시판 ]
-- [ 커리어 Q&A 게시판 ]
-- =====================
-- → 최신순 정렬
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--[뷰페이지에 보여줄 항목 SELECT 연습]==========================================
SELECT '커리어' AS POST_CLASS
, CQ.CQ_CODE AS POST_NUMBER
, CQ.TITLE AS POST_TITLE
, CQ.KDATE AS POST_CREATION_DATE
, TO_CHAR(CQ.KDATE, 'YYYY-MM-DD HH24:MI:SS') AS REAL_POST_DATE
, TRUNC(MONTHS_BETWEEN(SYSDATE, CQ.KDATE)) AS POST_PREV_MONTH
, TRUNC(SYSDATE - CQ.KDATE) AS POST_PREV_DAY
, TRUNC(MOD((SYSDATE - CQ.KDATE) * 24, 24)) AS POST_PREV_HOUR
, TRUNC(MOD(MOD((SYSDATE - CQ.KDATE) * 24, 24) * 60, 60)) AS POST_PREV_MINUTE
, CQ.VIEWS AS POST_VIEWS
, M.NICKNAME AS NICKNAME
FROM MEMBER M
INNER JOIN C_QNA CQ
ON M.MEM_CODE = CQ.MEM_CODE
WHERE M.MEM_CODE = 'MEM0001';
--[게시판에 달린 댓글 개수 가져오기]============================================
--『커리어 게시판』===========================
SELECT CQ.CQ_CODE
FROM MEMBER M
INNER JOIN C_QNA CQ
ON M.MEM_CODE = CQ.MEM_CODE
INNER JOIN CQ_COMMENT CQC
ON CQ.CQ_CODE = CQC.CQ_CODE
WHERE M.MEM_CODE = 'MEM0001';
--『기술 게시판』===========================
SELECT TQ.TQ_CODE
FROM MEMBER M
INNER JOIN TECH_QNA TQ
ON M.MEM_CODE = TQ.MEM_CODE
INNER JOIN TQ_COMMENT TC
ON TQ.TQ_CODE = TC.TQ_CODE
WHERE M.MEM_CODE = 'MEM0001';
--[게시글 코멘트 개수 서브 상단쿼리 구성 테스트]================================
SELECT '커리어' AS POST_CLASS
, CQ.CQ_CODE AS POST_NUMBER
, CQ.TITLE AS POST_TITLE
, CQ.KDATE AS POST_CREATION_DATE
, TO_CHAR(CQ.KDATE, 'YYYY-MM-DD HH24:MI:SS') AS POST_REAL_WRITE_DATE
, TRUNC(MONTHS_BETWEEN(SYSDATE, CQ.KDATE)) AS POST_PREV_MONTH
, TRUNC(SYSDATE - CQ.KDATE) AS POST_PREV_DAY
, TRUNC(MOD((SYSDATE - CQ.KDATE) * 24, 24)) AS POST_PREV_HOUR
, TRUNC(MOD(MOD((SYSDATE - CQ.KDATE) * 24, 24) * 60, 60)) AS POST_PREV_MINUTE
, CQ.VIEWS AS POST_VIEWS
, M.NICKNAME AS NICKNAME
, M.MEM_CODE AS MEM_CODE
,
(
SELECT COUNT(CQ2.CQ_CODE)
FROM MEMBER M
INNER JOIN C_QNA CQ2
ON M.MEM_CODE = CQ2.MEM_CODE
INNER JOIN CQ_COMMENT CQC
ON CQ2.CQ_CODE = CQC.CQ_CODE
WHERE M.MEM_CODE = 'MEM0001'
AND CQ2.CQ_CODE = CQ.CQ_CODE
) AS POST_COMMENT_COUNT
FROM MEMBER M
INNER JOIN C_QNA CQ
ON M.MEM_CODE = CQ.MEM_CODE
WHERE M.MEM_CODE = 'MEM0001';
--[기술 QNA, 커리어 QNA 게시글 통합]============================================
SELECT MY_POST.POST_CLASS AS POST_CLASS
, MY_POST.POST_NUMBER AS POST_NUMBER
, MY_POST.POST_TITLE AS POST_TITLE
, MY_POST.POST_CREATION_DATE AS POST_CREATION_DATE
, MY_POST.POST_REAL_WRITE_DATE AS POST_REAL_WRITE_DATE
, MY_POST.POST_PREV_MONTH AS POST_PREV_MONTH
, MY_POST.POST_PREV_DAY AS POST_PREV_DAY
, MY_POST.POST_PREV_HOUR AS POST_PREV_HOUR
, MY_POST.POST_PREV_MINUTE AS POST_PREV_MINUTE
, MY_POST.POST_VIEWS AS POST_VIEWS
, MY_POST.NICKNAME AS NICKNAME
, MY_POST.MEM_CODE AS MEM_CODE
, MY_POST.POST_COMMENT_COUNT AS POST_COMMENT_COUNT
FROM
(
SELECT '커리어' AS POST_CLASS
, CQ.CQ_CODE AS POST_NUMBER
, CQ.TITLE AS POST_TITLE
, CQ.KDATE AS POST_CREATION_DATE
, TO_CHAR(CQ.KDATE, 'YYYY-MM-DD HH24:MI:SS') AS POST_REAL_WRITE_DATE
, TRUNC(MONTHS_BETWEEN(SYSDATE, CQ.KDATE)) AS POST_PREV_MONTH
, TRUNC(SYSDATE - CQ.KDATE) AS POST_PREV_DAY
, TRUNC(MOD((SYSDATE - CQ.KDATE) * 24, 24)) AS POST_PREV_HOUR
, TRUNC(MOD(MOD((SYSDATE - CQ.KDATE) * 24, 24) * 60, 60)) AS POST_PREV_MINUTE
, CQ.VIEWS AS POST_VIEWS
, M.NICKNAME AS NICKNAME
, M.MEM_CODE AS MEM_CODE
,
(
SELECT COUNT(CQ2.CQ_CODE)
FROM MEMBER M
INNER JOIN C_QNA CQ2
ON M.MEM_CODE = CQ2.MEM_CODE
INNER JOIN CQ_COMMENT CQC
ON CQ2.CQ_CODE = CQC.CQ_CODE
WHERE M.MEM_CODE = 'MEM0001'
AND CQ2.CQ_CODE = CQ.CQ_CODE
) AS POST_COMMENT_COUNT
FROM MEMBER M
INNER JOIN C_QNA CQ
ON M.MEM_CODE = CQ.MEM_CODE
UNION ALL
SELECT '기술'
, TQ.TQ_CODE
, TQ.TITLE
, TQ.KDATE
, TO_CHAR(TQ.KDATE, 'YYYY-MM-DD HH24:MI:SS')
, TRUNC(MONTHS_BETWEEN(SYSDATE, TQ.KDATE))
, TRUNC(SYSDATE - TQ.KDATE)
, TRUNC(MOD((SYSDATE - TQ.KDATE) * 24, 24))
, TRUNC(MOD(MOD((SYSDATE - TQ.KDATE) * 24, 24) * 60, 60))
, TQ.VIEWS
, M.NICKNAME
, M.MEM_CODE
,
(
SELECT COUNT(TQ2.TQ_CODE)
FROM MEMBER M
INNER JOIN TECH_QNA TQ2
ON M.MEM_CODE = TQ2.MEM_CODE
INNER JOIN TQ_COMMENT TC
ON TQ2.TQ_CODE = TC.TQ_CODE
WHERE M.MEM_CODE = 'MEM0001'
AND TQ2.TQ_CODE = TQ.TQ_CODE
) AS POST_COMMENT_COUNT
FROM MEMBER M
INNER JOIN TECH_QNA TQ
ON M.MEM_CODE = TQ.MEM_CODE
) MY_POST
WHERE MY_POST.MEM_CODE = 'MEM0001'
ORDER BY MY_POST.POST_CREATION_DATE;
--==============================================================================
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
-- 마이페이지 - 3페이지
-- ▶ 본인이 작성한 댓글
-- =====================
-- [ 기술 Q&A 게시판 ]
-- [ 커리어 Q&A 게시판 ]
-- =====================
-- → 최신순 정렬
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--[커리어 댓글 조회 테스트]=====================================================
SELECT '커리어' AS COMMENT_CLASS
, CQ.TITLE AS COMMENT_POST_TITLE
, CQC.COMMENTS AS COMMENT_CONTENT
, CU.CU_CODE AS COMMENT_UP_CODE
, M.NICKNAME AS NICKNAME
, TO_CHAR(CQC.KDATE, 'YYYY-MM-DD HH24:MI:SS') AS POST_REAL_WRITE_DATE
, TRUNC(MONTHS_BETWEEN(SYSDATE, CQC.KDATE)) AS POST_PREV_MONTH
, TRUNC(SYSDATE - CQC.KDATE) AS POST_PREV_DAY
, TRUNC(MOD((SYSDATE - CQC.KDATE) * 24, 24)) AS POST_PREV_HOUR
, TRUNC(MOD(MOD((SYSDATE - CQC.KDATE) * 24, 24) * 60, 60)) AS POST_PREV_MINUTE
, M.MEM_CODE AS MEM_CODE
FROM MEMBER M
INNER JOIN CQ_COMMENT CQC
ON M.MEM_CODE = CQC.MEM_CODE
INNER JOIN C_QNA CQ
ON CQC.CQ_CODE = CQ.CQ_CODE
LEFT OUTER JOIN CAREER_UP CU
ON CQC.CQC_CODE = CU.CQC_CODE;
SELECT ALL_CMT.COMMENT_CLASS AS COMMENT_CLASS
, ALL_CMT.COMMENT_POST_TITLE AS COMMENT_POST_TITLE
, ALL_CMT.COMMENT_CONTENT AS COMMENT_CONTENT
, ALL_CMT.NICKNAME AS NICKNAME
, ALL_CMT.COMMENT_UP_COUNT AS COMMENT_UP_COUNT
, ALL_CMT.COMMENT_REAL_WRITE_DATE AS COMMENT_REAL_WRITE_DATE
, ALL_CMT.COMMENT_PREV_MONTH AS COMMENT_PREV_MONTH
, ALL_CMT.COMMENT_PREV_DAY AS COMMENT_PREV_DAY
, ALL_CMT.COMMENT_PREV_HOUR AS COMMENT_PREV_HOUR
, ALL_CMT.COMMENT_PREV_MINUTE AS COMMENT_PREV_MINUTE
FROM
(
SELECT CMT.COMMENT_CLASS AS COMMENT_CLASS
, CMT.COMMENT_POST_TITLE AS COMMENT_POST_TITLE
, CMT.COMMENT_CONTENT AS COMMENT_CONTENT
, CMT.NICKNAME AS NICKNAME
, CMT.MEM_CODE AS MEM_CODE
, COUNT(CMT.COMMENT_UP_CODE) AS COMMENT_UP_COUNT
, CMT.COMMENT_REAL_WRITE_DATE AS COMMENT_REAL_WRITE_DATE
, CMT.COMMENT_PREV_MONTH AS COMMENT_PREV_MONTH
, CMT.COMMENT_PREV_DAY AS COMMENT_PREV_DAY
, CMT.COMMENT_PREV_HOUR AS COMMENT_PREV_HOUR
, CMT.COMMENT_PREV_MINUTE AS COMMENT_PREV_MINUTE
FROM
(
SELECT '커리어' AS COMMENT_CLASS
, CQ.TITLE AS COMMENT_POST_TITLE
, CQC.COMMENTS AS COMMENT_CONTENT
, CU.CU_CODE AS COMMENT_UP_CODE
, M.NICKNAME AS NICKNAME
, TO_CHAR(CQC.KDATE, 'YYYY-MM-DD HH24:MI:SS') AS COMMENT_REAL_WRITE_DATE
, TRUNC(MONTHS_BETWEEN(SYSDATE, CQC.KDATE)) AS COMMENT_PREV_MONTH
, TRUNC(SYSDATE - CQC.KDATE) AS COMMENT_PREV_DAY
, TRUNC(MOD((SYSDATE - CQC.KDATE) * 24, 24)) AS COMMENT_PREV_HOUR
, TRUNC(MOD(MOD((SYSDATE - CQC.KDATE) * 24, 24) * 60, 60)) AS COMMENT_PREV_MINUTE
, M.MEM_CODE AS MEM_CODE
FROM MEMBER M
INNER JOIN CQ_COMMENT CQC
ON M.MEM_CODE = CQC.MEM_CODE
INNER JOIN C_QNA CQ
ON CQC.CQ_CODE = CQ.CQ_CODE
LEFT OUTER JOIN CAREER_UP CU
ON CQC.CQC_CODE = CU.CQC_CODE
) CMT
GROUP BY CMT.COMMENT_CLASS, CMT.COMMENT_POST_TITLE, CMT.COMMENT_CONTENT, CMT.NICKNAME
, CMT.MEM_CODE
, CMT.COMMENT_REAL_WRITE_DATE
, CMT.COMMENT_PREV_MONTH
, CMT.COMMENT_PREV_DAY
, CMT.COMMENT_PREV_HOUR
, CMT.COMMENT_PREV_MINUTE
UNION ALL
SELECT CMT.COMMENT_CLASS
, CMT.COMMENT_POST_TITLE
, CMT.COMMENT_CONTENT
, CMT.NICKNAME
, CMT.MEM_CODE
, COUNT(CMT.COMMENT_UP_CODE)
, CMT.COMMENT_REAL_WRITE_DATE
, CMT.COMMENT_PREV_MONTH
, CMT.COMMENT_PREV_DAY
, CMT.COMMENT_PREV_HOUR
, CMT.COMMENT_PREV_MINUTE
FROM
(
SELECT '기술' AS COMMENT_CLASS
, TQ.TITLE AS COMMENT_POST_TITLE
, TC.COMMENTS AS COMMENT_CONTENT
, TU.TU_CODE AS COMMENT_UP_CODE
, M.NICKNAME AS NICKNAME
, M.MEM_CODE AS MEM_CODE
, TO_CHAR(TC.KDATE, 'YYYY-MM-DD HH24:MI:SS') AS COMMENT_REAL_WRITE_DATE
, TRUNC(MONTHS_BETWEEN(SYSDATE, TC.KDATE)) AS COMMENT_PREV_MONTH
, TRUNC(SYSDATE - TC.KDATE) AS COMMENT_PREV_DAY
, TRUNC(MOD((SYSDATE - TC.KDATE) * 24, 24)) AS COMMENT_PREV_HOUR
, TRUNC(MOD(MOD((SYSDATE - TC.KDATE) * 24, 24) * 60, 60)) AS COMMENT_PREV_MINUTE
FROM MEMBER M
INNER JOIN TQ_COMMENT TC
ON M.MEM_CODE = TC.MEM_CODE
INNER JOIN TECH_QNA TQ
ON TC.TQ_CODE = TQ.TQ_CODE
LEFT OUTER JOIN TECH_UP TU
ON TC.TC_CODE = TU.TC_CODE
) CMT
GROUP BY CMT.COMMENT_CLASS, CMT.COMMENT_POST_TITLE, CMT.COMMENT_CONTENT, CMT.NICKNAME
, CMT.MEM_CODE
, CMT.COMMENT_REAL_WRITE_DATE
, CMT.COMMENT_PREV_MONTH
, CMT.COMMENT_PREV_DAY
, CMT.COMMENT_PREV_HOUR
, CMT.COMMENT_PREV_MINUTE
) ALL_CMT
WHERE ALL_CMT.MEM_CODE = 'MEM0001';
--
SELECT *
FROM CHECKLIST CK
INNER JOIN STEP ST
ON CK.ST_CODE = ST.ST_CODE
INNER JOIN CHECKLIST_CHECK CC
ON CK.CC_CODE = CC.CC_CODE
INNER JOIN MEMBER_APPLY MA
ON CK.MA_CODE = MA.MA_CODE
INNER JOIN MEMBER M
ON MA.MEM_CODE = M.MEM_CODE;20240308_PMOA_KJM.sql
SELECT USER
FROM DUAL;
--==>> PMOA;
--[한울님꺼 좋아요 해보깅 ㅎㅎ]=================================================
SELECT P.P_CODE AS P_CODE, A.PRJ_NAME AS PRJ_NAME, P.TITLE AS TITLE, P.CONTENT AS CONTENT, P.VIEWS AS VIEWS
,
(
SELECT NVL(COUNT(*), 0) AS PORTFOLIO_LIKE
FROM POFOL_UP PU2
INNER JOIN PORTFOLIO PF2
ON PU2.P_CODE = PF2.P_CODE
WHERE PF2.P_CODE = P.P_CODE
) AS PORTFOLIO_LIKE
FROM PORTFOLIO P, C_PROJECT C, APP_OPENING A, POFOL_UP U
WHERE P.CP_CODE = C.CP_CODE
AND C.AP_CODE = A.AP_CODE
AND P.P_CODE = U.P_CODE(+)
GROUP BY P.P_CODE, A.PRJ_NAME, P.TITLE, P.CONTENT, P.VIEWS;
--==============================================================================
--▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒
--▒▒▒▒▒▒[체크리스트 가데이터 입력]▒▒▒▒▒▒
--▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒
SELECT *
FROM CHECKLIST;
INSERT INTO CHECKLIST(CK_CODE, CONTENT
, KDATE, ST_CODE, MS_CODE
, CC_CODE, MA_CODE)
VALUES (TO_CHAR('CK' || LPAD(CK_SEQ.NEXTVAL, 4, '0')), '테스트용 마일스톤 체크리스트 가데이터 입력'
, SYSDATE, 'ST0004', 'MS0001'
, 'CC0001', 'MA0001');
--▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒
--○ 프로젝트 참여 인원 조회 뷰
SELECT *
FROM OPEN_PRJ_MEMBERS_VIEW
WHERE 개설확정코드 = 'CP0001';
--○ 프로젝트 마일스톤 체크리스트 조회 뷰
SELECT *
FROM PJ_MILE_VIEW
WHERE PRJ_CP_CODE = 'CP0001';
--================================================
--『데이터 INSERT시 참고할 뷰 생성』
--==>> 【OPEN_PRJ_MEMBERS_VIEW】
-- : 개설확정된 프로젝트의 참여 인원들을 조회
--================================================
CREATE OR REPLACE VIEW OPEN_PRJ_MEMBERS_VIEW
AS
SELECT M.MEM_CODE AS "회원코드"
, M.NICKNAME AS "닉네임"
, MA.MA_CODE AS "멤버지원코드"
, AR.RESULT AS "합격여부"
, MR.ROLE AS "담당직무"
, AP.PRJ_NAME AS "프로젝트명"
, AP.SDATE AS "프로젝트시작일"
, AP.EDATE AS "프로젝트종료일"
, AP.NUMBERS AS "프로젝트참여인원수"
, AP.AP_CODE AS "개설신청코드"
, CP.CP_CODE AS "개설확정코드"
FROM MEMBER M
INNER JOIN MEMBER_APPLY MA
ON M.MEM_CODE = MA.MEM_CODE
INNER JOIN ROLE_COMP RC
ON MA.RC_CODE = RC.RC_CODE
INNER JOIN A_RESULT AR
ON MA.AR_CODE = AR.AR_CODE
INNER JOIN MEMBER_ROLE MR
ON RC.MR_CODE = MR.MR_CODE
INNER JOIN APP_OPENING AP
ON RC.AP_CODE = AP.AP_CODE
INNER JOIN C_PROJECT CP
ON AP.AP_CODE = CP.AP_CODE;
--================================================
--『데이터 INSERT시 참고할 뷰 생성』
--==>> 【PJ_MILE_VIEW】
--================================================
SELECT *
FROM PJ_MILE_VIEW;
CREATE OR REPLACE VIEW PJ_MILE_VIEW
AS
SELECT M.MEM_CODE AS "MEM_CODE"
, MA.MA_CODE AS "MA_CODE"
, M.NICKNAME AS "NICKNAME"
, MR.ROLE AS "MEMBER_ROLE"
, MR.MR_CODE AS "MEMBER_ROLE_CODE"
, MS.MS_CODE AS "MILESTONE_CODE"
, ST.STEP AS "STEP"
, ST.ST_CODE AS "STEP_CODE"
, CK.CK_CODE AS "CHECKLIST_CODE"
, CK.CONTENT AS "CHECKLIST_CONTENT"
, CC.CHECKS AS "IS_CHECKED"
, CC.CC_CODE AS "IS_CHECKED_CODE"
, AP.PRJ_NAME AS "PRJ_NAME"
, CP.CP_CODE AS "PRJ_CP_CODE"
FROM MEMBER M
INNER JOIN MEMBER_APPLY MA
ON M.MEM_CODE = MA.MEM_CODE
INNER JOIN ROLE_COMP RC
ON MA.RC_CODE = RC.RC_CODE
INNER JOIN APP_OPENING AP
ON RC.AP_CODE = AP.AP_CODE
INNER JOIN C_PROJECT CP
ON AP.AP_CODE = CP.AP_CODE
INNER JOIN MILESTONE MS
ON CP.CP_CODE = MS.CP_CODE
INNER JOIN CHECKLIST CK
ON MS.MS_CODE = CK.MS_CODE
AND MA.MA_CODE = CK.MA_CODE
INNER JOIN CHECKLIST_CHECK CC
ON CK.CC_CODE = CC.CC_CODE
INNER JOIN STEP ST
ON CK.ST_CODE = ST.ST_CODE
INNER JOIN MEMBER_ROLE MR
ON RC.MR_CODE = MR.MR_CODE
ORDER BY CHECKLIST_CODE ASC;
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--▩▩▩▩▩▩▩『마일스톤 체크리스트』▩▩▩▩▩▩▩
--▩▩▩▩▩▩【Milestone_CheckListDTO】▩▩▩▩▩▩▩
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--================================================
--위에서 구성한 PJ_MILE_VIEW 조회 구문을
--【Milestone_CheckListDTO】 DTO 로 구성
--================================================
SELECT M.MEM_CODE AS "MEM_CODE"
, MA.MA_CODE AS "MA_CODE"
, M.NICKNAME AS "NICKNAME"
, MR.ROLE AS "MEMBER_ROLE"
, MR.MR_CODE AS "MEMBER_ROLE_CODE"
, MS.MS_CODE AS "MILESTONE_CODE"
, ST.STEP AS "STEP"
, ST.ST_CODE AS "STEP_CODE"
, CK.CK_CODE AS "CHECKLIST_CODE"
, CK.CONTENT AS "CHECKLIST_CONTENT"
, CC.CHECKS AS "IS_CHECKED"
, CC.CC_CODE AS "IS_CHECKED_CODE"
, AP.PRJ_NAME AS "PRJ_NAME"
, CP.CP_CODE AS "PRJ_CP_CODE"
FROM MEMBER M
INNER JOIN MEMBER_APPLY MA
ON M.MEM_CODE = MA.MEM_CODE
INNER JOIN ROLE_COMP RC
ON MA.RC_CODE = RC.RC_CODE
INNER JOIN APP_OPENING AP
ON RC.AP_CODE = AP.AP_CODE
INNER JOIN C_PROJECT CP
ON AP.AP_CODE = CP.AP_CODE
INNER JOIN MILESTONE MS
ON CP.CP_CODE = MS.CP_CODE
INNER JOIN CHECKLIST CK
ON MS.MS_CODE = CK.MS_CODE
AND MA.MA_CODE = CK.MA_CODE
INNER JOIN CHECKLIST_CHECK CC
ON CK.CC_CODE = CC.CC_CODE
INNER JOIN STEP ST
ON CK.ST_CODE = ST.ST_CODE
INNER JOIN MEMBER_ROLE MR
ON RC.MR_CODE = MR.MR_CODE
WHERE CP.CP_CODE = 'CP0001'
AND ST.STEP = '구현'
ORDER BY CHECKLIST_CODE ASC;
--○ [선택항목 조회]============================
--SELECT CK.CK_CODE AS CK_CODE
-- , M.MEM_CODE AS MEM_CODE
-- , M.NICKNAME AS NICKNAME
-- , CK.KDATE AS CHECK_ADD_DATE
-- , CP.CP_CODE AS C_PROJECT_CODE
-- , CC.CHECKS AS IS_CHECKED
-- , CK.CONTENT AS CHECK_CONTENT
-- , ST.STEP AS CHECK_STEP
--FROM MEMBER M
-- INNER JOIN MEMBER_APPLY MA
-- ON M.MEM_CODE = MA.MEM_CODE
-- INNER JOIN ROLE_COMP RC
-- ON MA.RC_CODE = RC.RC_CODE
-- INNER JOIN APP_OPENING AP
-- ON RC.AP_CODE = AP.AP_CODE
-- INNER JOIN C_PROJECT CP
-- ON AP.AP_CODE = CP.AP_CODE
-- INNER JOIN MILESTONE MS
-- ON CP.CP_CODE = MS.CP_CODE
-- INNER JOIN CHECKLIST CK
-- ON MS.MS_CODE = CK.MS_CODE
-- AND MA.MA_CODE = CK.MA_CODE
-- INNER JOIN STEP ST
-- ON CK.ST_CODE = ST.ST_CODE
-- INNER JOIN CHECKLIST_CHECK CC
-- ON CK.CC_CODE = CC.CC_CODE
--WHERE CP.CP_CODE = 'CP0001';
--==============================================
--○ [전체 조회]===============================
SELECT *
FROM MEMBER M
INNER JOIN MEMBER_APPLY MA
ON M.MEM_CODE = MA.MEM_CODE
INNER JOIN ROLE_COMP RC
ON MA.RC_CODE = RC.RC_CODE
INNER JOIN APP_OPENING AP
ON RC.AP_CODE = AP.AP_CODE
INNER JOIN C_PROJECT CP
ON AP.AP_CODE = CP.AP_CODE
INNER JOIN MILESTONE MS
ON CP.CP_CODE = MS.CP_CODE
INNER JOIN CHECKLIST CK
ON MS.MS_CODE = CK.MS_CODE
AND MA.MA_CODE = CK.MA_CODE
INNER JOIN STEP ST
ON CK.ST_CODE = ST.ST_CODE
INNER JOIN CHECKLIST_CHECK CC
ON CK.CC_CODE = CC.CC_CODE;
--==============================================
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--▩▩▩[마일스톤 체크리스트 체크 처리]▩▩▩
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--○ [체크리스트 체크 항목의 체크 여부 조회]========
SELECT IS_CHECKED
FROM PJ_MILE_VIEW
WHERE CHECKLIST_CODE = 'CK0001';
--==================================================
--○ [체크리스트 체크 업데이트]=====================
UPDATE
(
SELECT CK.CC_CODE, CK.CK_CODE
FROM CHECKLIST CK
INNER JOIN CHECKLIST_CHECK CC
ON CK.CC_CODE = CC.CC_CODE
)
SET CC_CODE = 'CC0002'
WHERE CK_CODE = 'CK0009';
--=================================================
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--▩▩▩[마일스톤 체크리스트 항목 추가]▩▩▩
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
INSERT INTO CHECKLIST(CK_CODE, CONTENT
, KDATE, ST_CODE, MS_CODE
, CC_CODE, MA_CODE)
VALUES (TO_CHAR('CK' || LPAD(CK_SEQ.NEXTVAL, 4, '0')), '테스트용 마일스톤 체크리스트 가데이터 입력'
, SYSDATE, 'ST0004', 'MS0001'
, 'CC0001', 'MA0001');
--○ [개설확정 코드를 이용한 마일스톤 코드 검색]==========
SELECT MS_CODE
FROM MILESTONE
WHERE CP_CODE = 'CP0001';
-- =======================================================
--○ [단계 이름을 이용한 스텝 코드 검색]==================
SELECT ST_CODE
FROM STEP
WHERE STEP = '기획';
-- =======================================================
select *
from pj_mile_view;
--【마일스톤 항목을 추가하는 프로시저 생성】================================
--○ 『PRC_MILE_INSERTING(개설확정코드, 멤버지원코드, 단계 이름, 내용)』
--○ [실행문]*******************************************************************
EXECUTE PRC_MILE_INSERTING('CP0001', 'MA0001', '구현', '체크리스트 추가 항목 내용');
--******************************************************************************
CREATE OR REPLACE PROCEDURE PRC_MILE_INSERTING
(
V_CP_CODE IN C_PROJECT.CP_CODE%TYPE
, V_MA_CODE IN MEMBER_APPLY.MA_CODE%TYPE
, V_STEP IN STEP.STEP%TYPE
, V_CONTENT IN CHECKLIST.CONTENT%TYPE
)
IS
V_MS_CODE MILESTONE.MS_CODE%TYPE;
V_ST_CODE STEP.ST_CODE%TYPE;
BEGIN
SELECT MS_CODE INTO V_MS_CODE
FROM MILESTONE
WHERE CP_CODE = V_CP_CODE;
SELECT ST_CODE INTO V_ST_CODE
FROM STEP
WHERE STEP = V_STEP;
INSERT INTO CHECKLIST(CK_CODE, CONTENT
, KDATE, ST_CODE, MS_CODE
, CC_CODE, MA_CODE)
VALUES (TO_CHAR('CK' || LPAD(CK_SEQ.NEXTVAL, 4, '0')), V_CONTENT
, SYSDATE, V_ST_CODE, V_MS_CODE
, 'CC0001', V_MA_CODE);
END;
--=========================================================================20240311_PMOA_KJM.sql
SELECT USER
FROM DUAL;
--==>> PMOA
--[길님거 연습 ㅎㅎ]===========================================================
SELECT APP.MEM_CODE
, APP.AP_CODE AS CODE
, TO_CHAR(APP.SDATE, 'YYYY-MM-DD') AS KDATE
, APP.NUMBERS
, APP.VIEWS
, NVL(COUNT.AC_COUNT, 0) AS COMMENT_COUNT
, NVL(A2_COUNT.RECOMMENT_COUNT, 0) AS RECOMMENT_COUNT
FROM
APP_OPENING APP
LEFT JOIN
(
SELECT AP_CODE, COUNT(*) AS AC_COUNT
FROM AO_COMMENT
GROUP BY AP_CODE
) COUNT
ON COUNT.AP_CODE = APP.AP_CODE
LEFT OUTER JOIN
(
SELECT AP2.AP_CODE AS AP_CODE
, COUNT(A2.A2_CODE) AS RECOMMENT_COUNT
FROM APP_OPENING AP2
INNER JOIN AO_COMMENT AC
ON AP2.AP_CODE = AC.AP_CODE
INNER JOIN AO_REPLY2 A2
ON AC.AC_CODE = A2.AC_CODE
GROUP BY AP2.AP_CODE
) A2_COUNT
ON A2_COUNT.AP_CODE = APP.AP_CODE
ORDER BY CODE ASC;
--==============================================================================
--○ [CK_CODE(checklist_code)에 해당하는 체크리스트 내용을 조회하는 쿼리문]====
-- [수정버튼 클릭시, 입력창에 뿌려주기 위함.]
SELECT CONTENT
FROM CHECKLIST
WHERE CK_CODE = 'CK0001';
--=============================================================================
SELECT *
FROM CHECKLIST
ORDER BY CK_CODE ASC;
--○ [개설 확정된 프로젝트 코드와, 멤버 코드로 멤버지원 코드 조회] =============
SELECT MA_CODE
FROM PJ_MILE_VIEW
WHERE PRJ_CP_CODE = 'CP0001'
AND MEM_CODE = 'MEM0001'
GROUP BY MEM_CODE, MA_CODE;
--==============================================================================
--○ [체크리스트 항목 수정 쿼리문]===============
UPDATE CHECKLIST
SET CONTENT = '변경할 체크항목 사항'
, MA_CODE = 'MA0002'
, CC_CODE = 'CC0001'
, KDATE = SYSDATE
WHERE CK_CODE = 'CK0001';
--===============================================
--○ [기획 체크리스트 완료 항목 조회]============
--◎ [전체 항목 조회]
SELECT PRJ_CP_CODE, CHECKLIST_CODE, STEP, IS_CHECKED, CHECKLIST_CONTENT
FROM PJ_MILE_VIEW
WHERE PRJ_CP_CODE = 'CP0001'
AND STEP = '기획';
--◎ [전체 항목 카운트]
SELECT PRJ_CP_CODE
, COUNT(*) AS PLAN_CHECK_TOT_COUNT
FROM PJ_MILE_VIEW
WHERE STEP = '기획'
GROUP BY PRJ_CP_CODE;
--◎ [완료 항목 카운트]
SELECT PRJ_CP_CODE
, COUNT(*) AS PLAN_CHECK_SUCCESS_COUNT
FROM PJ_MILE_VIEW
WHERE STEP = '기획'
AND IS_CHECKED = 1
GROUP BY PRJ_CP_CODE;
--◎ [체크리스트 코드 CHECKLIST_CODE 를 통해,
-- 『단계명(STEP), 개설확정코드(PRJ_CP_CODE)』 문자열 결합 후 얻어내기] =====
SELECT STEP || '/' || PRJ_CP_CODE AS STEP_AND_CP_CODE
FROM PJ_MILE_VIEW
WHERE CHECKLIST_CODE = 'CK0003';
--◎ [완료 항목 / 전체 항목 비율 구하기]===================================
SELECT NVL( TRUNC( SUCCESS.PLAN_CHECK_SUCCESS_COUNT
/ TOT_COUNT.PLAN_CHECK_TOT_COUNT , 2), 0 ) * 100
AS MILE_SUCCESS_PERCENT
FROM
(
SELECT PRJ_CP_CODE
, STEP
, COUNT(*) AS PLAN_CHECK_TOT_COUNT
FROM PJ_MILE_VIEW
GROUP BY PRJ_CP_CODE, STEP
) TOT_COUNT
LEFT OUTER JOIN
(
SELECT PRJ_CP_CODE
, STEP
, COUNT(*) AS PLAN_CHECK_SUCCESS_COUNT
FROM PJ_MILE_VIEW
WHERE IS_CHECKED = 1
GROUP BY PRJ_CP_CODE, STEP
) SUCCESS
ON TOT_COUNT.PRJ_CP_CODE = SUCCESS.PRJ_CP_CODE
AND TOT_COUNT.STEP = SUCCESS.STEP
WHERE TOT_COUNT.PRJ_CP_CODE = 'CP0001'
AND TOT_COUNT.STEP = '기획';
--===============================================20240314_PMOA_KJM.sql
SELECT USER
FROM DUAL;
--==>> PMOA
--○ [멤버의 모든 프로필 이미지 경로 초기화 프로시저]===========================
EXECUTE PRC_DEFAULT_PROFILE_IMG;
--
--CREATE OR REPLACE PROCEDURE PRC_DEFAULT_PROFILE_IMG
--IS
-- V_MEM_CODE MEMBER.MEM_CODE%TYPE;
--
-- CURSOR MEM_CODE_SELECT
-- IS
-- SELECT MEM_CODE
-- FROM MEMBER;
--
--BEGIN
--
-- OPEN MEM_CODE_SELECT;
--
-- LOOP
--
-- FETCH MEM_CODE_SELECT INTO V_MEM_CODE;
--
-- EXIT WHEN MEM_CODE_SELECT%NOTFOUND;
--
--
-- INSERT INTO PROFILE(PI_CODE, MEM_CODE)
-- VALUES(TO_CHAR('PI' || LPAD(PI_SEQ.NEXTVAL, 4, '0')), V_MEM_CODE);
--
-- END LOOP;
--
-- CLOSE MEM_CODE_SELECT;
--
-- EXCEPTION
-- WHEN OTHERS THEN ROLLBACK;
--
--END;
--==============================================================================
--○ [마일스톤 체크리스트 삭제]============
DELETE
FROM CHECKLIST
WHERE CK_CODE = 'CK0001';
--=========================================
--○ [프로젝트의 참여 멤버 조회]===========
-- 멤버코드, 닉네임, 멤버지원코드, 담당직무
--○[피평가자 점수 조회]▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--===================================================
-- ▶ 멤버코드, 닉네임, 멤버지원코드, 담당직무,
-- ▶ 개설신청코드, 개설확정코드
-- ▶ 점수 구분 코드, 점수 구분명, 점수 코드, 점수
--===================================================
SELECT M.MEM_CODE AS "P_MEM_CODE"
, M.NICKNAME AS "P_NICKNAME"
, MA.MA_CODE AS "P_MA_CODE"
, MR.ROLE AS "P_MEMBER_ROLE"
, AP.AP_CODE AS "AP_CODE"
, CP.CP_CODE AS "CP_CODE"
, ED.ED_CODE AS "EVALU_TYPE_ED_CODE"
, ED.DIVISION AS "EVALU_DIVISION"
, EV.EV_CODE AS "EVALU_CODE"
, EV.SCORE AS "EVALU_SCORE"
FROM MEMBER M
INNER JOIN MEMBER_APPLY MA
ON M.MEM_CODE = MA.MEM_CODE
INNER JOIN ROLE_COMP RC
ON MA.RC_CODE = RC.RC_CODE
INNER JOIN MEMBER_ROLE MR
ON RC.MR_CODE = MR.MR_CODE
INNER JOIN APP_OPENING AP
ON RC.AP_CODE = AP.AP_CODE
INNER JOIN C_PROJECT CP
ON AP.AP_CODE = CP.AP_CODE
INNER JOIN EVALUATION EV
ON MA.MA_CODE = EV.MA_CODEP
INNER JOIN EV_DEVISION ED
ON EV.ED_CODE = ED.ED_CODE;
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--○[평가자 정보 조회]▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--===================================================
-- ▶ 멤버코드, 닉네임, 멤버지원코드, 담당직무
-- ▶ 개설확정코드
-- ▶ 점수코드
--===================================================
SELECT M.MEM_CODE AS "A_MEM_CODE"
, M.NICKNAME AS "A_NICKNAME"
, MA.MA_CODE AS "A_MA_CODE"
, MR.ROLE AS "A_MEMBER_ROLE"
, CP.CP_CODE AS "CP_CODE"
, EV.EV_CODE AS "EVALU_CODE"
FROM MEMBER M
INNER JOIN MEMBER_APPLY MA
ON M.MEM_CODE = MA.MEM_CODE
INNER JOIN ROLE_COMP RC
ON MA.RC_CODE = RC.RC_CODE
INNER JOIN MEMBER_ROLE MR
ON RC.MR_CODE = MR.MR_CODE
INNER JOIN APP_OPENING AP
ON RC.AP_CODE = AP.AP_CODE
INNER JOIN C_PROJECT CP
ON AP.AP_CODE = CP.AP_CODE
INNER JOIN EVALUATION EV
ON MA.MA_CODE = EV.MA_CODEA
INNER JOIN EV_DEVISION ED
ON EV.ED_CODE = ED.ED_CODE;
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--==============================================================================
--ⓐ [프로젝트 정보]
-- ▶ 개설신청코드, 개설확정코드
--==============================================================================
--ⓑ [평가자 정보]
-- ▶ 평가자 멤버코드, 평가자 멤버 지원 코드, 평가자 닉네임, 담당 직무
--==============================================================================
--ⓒ [피평가자 정보]
-- ▶ 피평가자 멤버코드, 피평가자 멤버 지원 코드, 피평가자 닉네임, 담당 직무
--==============================================================================
--ⓓ [점수 정보]
-- ▶ 점수 구분 코드, 점수 구분, 점수 코드, 점수
--==============================================================================
--○[평가자 + 피평가자 정보 통합]▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--=====================================
-- ▶ 뷰로 구성 : 『PJ_EVALU_VIEW』
--=====================================
SELECT *
FROM PJ_EVALU_VIEW
WHERE P_MEM_CODE = 'MEM0004';
SELECT 1000 + NVL(SUM(EV.SCORE), 0) AS FRONT_SCORE
FROM MEMBER M
INNER JOIN MEMBER_APPLY MA
ON M.MEM_CODE = MA.MEM_CODE
INNER JOIN ROLE_COMP RC
ON MA.RC_CODE = RC.RC_CODE
INNER JOIN MEMBER_ROLE MR
ON RC.MR_CODE = MR.MR_CODE
INNER JOIN EVALUATION EV
ON MA.MA_CODE = EV.MA_CODEP
INNER JOIN EV_DEVISION ED
ON EV.ED_CODE = ED.ED_CODE
WHERE M.MEM_CODE = 'MEM0005'
AND MR.MR_CODE = 'MR0003'
AND ED.ED_CODE = 'ED0001';
CREATE OR REPLACE VIEW PJ_EVALU_VIEW
AS
SELECT AP.AP_CODE AS "AP_CODE"
, CP.CP_CODE AS "CP_CODE"
, AR.RESULT AS "RESULT"
, RATER.A_MEM_CODE AS "A_MEM_CODE"
, RATER.A_NICKNAME AS "A_NICKNAME"
, RATER.A_MA_CODE AS "A_MA_CODE"
, RATER.A_MEMBER_ROLE AS "A_MEMBER_ROLE"
, M.MEM_CODE AS "P_MEM_CODE"
, M.NICKNAME AS "P_NICKNAME"
, MA.MA_CODE AS "P_MA_CODE"
, MR.ROLE AS "P_MEMBER_ROLE"
, ED.ED_CODE AS "EVALU_TYPE_ED_CODE"
, ED.DIVISION AS "EVALU_DIVISION"
, EV.EV_CODE AS "EVALU_CODE"
, EV.SCORE AS "EVALU_SCORE"
FROM MEMBER M
LEFT OUTER JOIN MEMBER_APPLY MA
ON M.MEM_CODE = MA.MEM_CODE
LEFT OUTER JOIN A_RESULT AR
ON MA.AR_CODE = AR.AR_CODE
LEFT OUTER JOIN ROLE_COMP RC
ON MA.RC_CODE = RC.RC_CODE
LEFT OUTER JOIN MEMBER_ROLE MR
ON RC.MR_CODE = MR.MR_CODE
LEFT OUTER JOIN APP_OPENING AP
ON RC.AP_CODE = AP.AP_CODE
LEFT OUTER JOIN C_PROJECT CP
ON AP.AP_CODE = CP.AP_CODE
LEFT OUTER JOIN EVALUATION EV
ON MA.MA_CODE = EV.MA_CODEP
LEFT OUTER JOIN EV_DEVISION ED
ON EV.ED_CODE = ED.ED_CODE
LEFT OUTER JOIN
(
SELECT M.MEM_CODE AS "A_MEM_CODE"
, M.NICKNAME AS "A_NICKNAME"
, MA.MA_CODE AS "A_MA_CODE"
, MR.ROLE AS "A_MEMBER_ROLE"
, CP.CP_CODE AS "CP_CODE"
, EV.EV_CODE AS "EVALU_CODE"
FROM MEMBER M
INNER JOIN MEMBER_APPLY MA
ON M.MEM_CODE = MA.MEM_CODE
INNER JOIN ROLE_COMP RC
ON MA.RC_CODE = RC.RC_CODE
INNER JOIN MEMBER_ROLE MR
ON RC.MR_CODE = MR.MR_CODE
INNER JOIN APP_OPENING AP
ON RC.AP_CODE = AP.AP_CODE
INNER JOIN C_PROJECT CP
ON AP.AP_CODE = CP.AP_CODE
INNER JOIN EVALUATION EV
ON MA.MA_CODE = EV.MA_CODEA
INNER JOIN EV_DEVISION ED
ON EV.ED_CODE = ED.ED_CODE
) RATER
ON EV.EV_CODE = RATER.EVALU_CODE;
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--★『PJ_MEMBER_SCORE_INFO_VIEW』
-- →【Milestone_MemberEvaluDTO】 로 구성
-- [점수 평가 입력시 보여질 사항을 PJ_EVALU_VIEW 를 이용해 다시 VIEW 로 구성]
--============================================================================
SELECT *
FROM MY_ALL_SCORE_VIEW;
CREATE OR REPLACE VIEW MY_ALL_SCORE_VIEW
AS
SELECT P_MEM_CODE AS "MEM_CODE"
, P_NICKNAME AS "NICKNAME"
, AVG(BACKEND_SCORE) AS "BACKEND_SCORE"
, AVG(FRONTEND_SCORE) AS "FRONTEND_SCORE"
, AVG(MANNER_SCORE) AS "MANNER_SCORE"
FROM PJ_MEMBER_SCORE_INFO_VIEW
WHERE P_MEM_CODE = 'MEM0002'
GROUP BY P_MEM_CODE, P_NICKNAME, BACKEND_SCORE, FRONTEND_SCORE, MANNER_SCORE;
--ⓐ [ VIEW 생성 : PJ_MEMBER_SCORE_INFO_VIEW ]**********************
CREATE OR REPLACE VIEW PJ_MEMBER_SCORE_INFO_VIEW
AS
SELECT AP_CODE, CP_CODE
, P_MEM_CODE, P_MA_CODE, P_NICKNAME, P_MEMBER_ROLE
,
(
SELECT 1000 + NVL(SUM(EVALU_SCORE), 0) AS BACK_SCORE
FROM PJ_EVALU_VIEW PJEV2
WHERE PJEV2.P_MEM_CODE = PJEV.P_MEM_CODE
AND EVALU_DIVISION = '실력'
AND P_MEMBER_ROLE = '백엔드'
) BACKEND_SCORE
,
(
SELECT 1000 + NVL(SUM(EVALU_SCORE), 0) AS FRONT_SCORE
FROM PJ_EVALU_VIEW PJEV3
WHERE PJEV3.P_MEM_CODE = PJEV.P_MEM_CODE
AND EVALU_DIVISION = '실력'
AND P_MEMBER_ROLE = '프론트 엔드'
) FRONTEND_SCORE
,
(
SELECT 36.5 + NVL(SUM(DECODE(EVALU_SCORE
, 5, 0.2
, 4, 0.1
, 3, 0
, 2, -0.1
, 1, -0.2)), 0) AS MANNER_SCORE
FROM PJ_EVALU_VIEW PJEV4
WHERE PJEV4.P_MEM_CODE = PJEV.P_MEM_CODE
AND EVALU_DIVISION = '매너'
) MANNER_SCORE
FROM PJ_EVALU_VIEW PJEV
GROUP BY AP_CODE, CP_CODE
, P_MEM_CODE, P_MA_CODE, P_NICKNAME, P_MEMBER_ROLE;
--★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
--ⓑ [ Milestone_MemberEvaluDTO 의 조회 컬럼 구성 ]***********************
-- ※ 『팀장은 점수 입력이 불가능하므로, WHERE 조건절에서 제외.』
--▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤
--★[평가를 받았는지, 안 받았는지에 대한 여부 구하기]=======
SELECT DECODE(COUNT(*), 0, '평가안함', '평가함')
FROM PJ_EVALU_VIEW
WHERE CP_CODE = 'CP0001'
AND A_MEM_CODE = 'MEM0001'
AND P_MEM_CODE = 'MEM0004';
--===========================================================
--▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤▤
SELECT PSI.AP_CODE AS "AP_CODE"
, PSI.CP_CODE AS "CP_CODE"
, PSI.P_MEM_CODE AS "P_MEM_CODE"
, PSI.P_MA_CODE AS "P_MA_CODE"
, PSI.P_NICKNAME AS "P_NICKNAME"
, PSI.P_MEMBER_ROLE AS "P_MEMBER_ROLE"
, PSI.BACKEND_SCORE AS "BACKEND_SCORE"
, PSI.FRONTEND_SCORE AS "FRONTEND_SCORE"
, PSI.MANNER_SCORE AS "MANNER_SCORE"
,
(
SELECT DECODE(COUNT(PEV.P_MEM_CODE), 0, '평가미완료', '평가완료')
FROM PJ_EVALU_VIEW PEV
WHERE CP_CODE = PSI.CP_CODE
AND A_MEM_CODE = 'MEM0001'
AND P_MEM_CODE = PSI.P_MEM_CODE
) AS "EVALU_OK"
FROM PJ_MEMBER_SCORE_INFO_VIEW PSI
WHERE CP_CODE = 'CP0001'
AND P_MEMBER_ROLE NOT IN ('팀장')
AND P_MEM_CODE != 'MEM0001'
ORDER BY P_MEM_CODE ASC;
--★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
--○[개설 확정 코드를 이용해서 개설 신청 코드를 얻어내기]===========
SELECT 개설신청코드 AS "AP_CODE"
FROM OPEN_PRJ_MEMBERS_VIEW
WHERE 개설확정코드 = 'CP0002'
GROUP BY 개설신청코드;
--==================================================================
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--◎[실력 점수 가데이터 입력]▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
--○[평가 가능한 회원 조회]==================================
SELECT AP_CODE, CP_CODE
, P_MEM_CODE, P_MA_CODE, P_NICKNAME, P_MEMBER_ROLE
, BACKEND_SCORE, FRONTEND_SCORE, MANNER_SCORE
FROM PJ_MEMBER_SCORE_INFO_VIEW
WHERE CP_CODE = 'CP0001'
AND P_MEMBER_ROLE NOT IN ('팀장')
-- AND P_MEM_CODE
-- NOT IN
-- (
-- SELECT P_MEM_CODE
-- FROM PJ_EVALU_VIEW
-- WHERE CP_CODE = 'CP0001'
-- AND A_MEM_CODE = 'MEM0001'
-- GROUP BY P_MEM_CODE
-- )
ORDER BY P_MEM_CODE ASC;
--===========================================================
--★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
--○[전체 팀원 평가 조회]====================================
SELECT *
FROM EVALUATION;
--===========================================================
--◎『실력 점수(ED_CODE) : 'ED0001'』
INSERT INTO EVALUATION(EV_CODE, KDATE
, SCORE, ED_CODE
, MA_CODEA, MA_CODEP)
VALUES ( TO_CHAR('EV' || LPAD(EV_SEQ.NEXTVAL, 4, '0')), SYSDATE
, 5, 'ED0001'
, 'MA0005', 'MA0004');
--==============================================================================
--◎『매너 점수(ED_CODE) : 'ED0002'』
INSERT INTO EVALUATION(EV_CODE, KDATE
, SCORE, ED_CODE
, MA_CODEA, MA_CODEP)
VALUES ( TO_CHAR('EV' || LPAD(EV_SEQ.NEXTVAL, 4, '0')), SYSDATE
, 3, 'ED0002'
, 'MA0005', 'MA0004');
--==============================================================================
--● [전체 점수 데이터 삭제]
DELETE
FROM EVALUATION;
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--=============================================================================
--○ 개설확정코드(CP_CODE)와 멤버코드를 이용하여, 직위(MEMBER_ROLE) 얻기.
--=============================================================================
SELECT P_MEMBER_ROLE AS "MEMBER_ROLE"
FROM PJ_MEMBER_SCORE_INFO_VIEW
WHERE CP_CODE = 'CP0001'
AND P_MEM_CODE = 'MEM0001';
--=============================================================================
--○[개설확정 코드 [CP0001]인 프로젝트의 모든 체크리스트 해제]===============
UPDATE
(
SELECT CK.CC_CODE
FROM CHECKLIST CK
INNER JOIN MILESTONE MS
ON CK.MS_CODE = MS.MS_CODE
INNER JOIN C_PROJECT CP
ON MS.CP_CODE = CP.CP_CODE
WHERE CP.CP_CODE = 'CP0001'
)
SET CC_CODE = 'CC0001';
--==========================================================================
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--◎ 마일스톤 대분류 체크리스트 항목의 완성 퍼센트 총합 구하기
-- ▶【400이면, 체크리스트 모두 완료! → 팀원 평가가 열린다~】
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
SELECT SUM(NVL( TRUNC( SUCCESS.PLAN_CHECK_SUCCESS_COUNT
/ TOT_COUNT.PLAN_CHECK_TOT_COUNT , 2), 0 ) * 100)
AS TOT_MILE_SUCCESS_PERCENT
FROM
(
SELECT PRJ_CP_CODE
, STEP
, COUNT(*) AS PLAN_CHECK_TOT_COUNT
FROM PJ_MILE_VIEW
GROUP BY PRJ_CP_CODE, STEP
) TOT_COUNT
LEFT OUTER JOIN
(
SELECT PRJ_CP_CODE
, STEP
, COUNT(*) AS PLAN_CHECK_SUCCESS_COUNT
FROM PJ_MILE_VIEW
WHERE IS_CHECKED = 1
GROUP BY PRJ_CP_CODE, STEP
) SUCCESS
ON TOT_COUNT.PRJ_CP_CODE = SUCCESS.PRJ_CP_CODE
AND TOT_COUNT.STEP = SUCCESS.STEP
WHERE TOT_COUNT.PRJ_CP_CODE = 'CP0001';
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--◎▩▩▩▩▩▩▩▩▩[결산 보고서 항목 INSERT 쿼리문]▩▩▩▩▩▩▩▩▩▩▩▩
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
--○ [결산 보고서 조회]==================
SELECT *
FROM FINAL_REPORT;
--=======================================
--==============================================================================
-- 1. 결산 보고서 테이블 (FINAL_REPORT) insert
-- 2. 포트폴리오 게시판 (PORTFOLIO) insert (같은 내용)
-- 3. 포트폴리오 코드 구하기
-- 4. 포트폴리오 코드 foreign key 로 갖는 포트폴리오 태그 (P_TAG) 에 태그 넣기
--==============================================================================
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
-- 【 1. 결산 보고서 테이블 INSERT 】
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
DESC FINAL_REPORT;
--=========================================================================
INSERT INTO FINAL_REPORT(FR_CODE, TITLE
, CONTENT, KDATE
, CP_CODE)
VALUES (TO_CHAR('FR' || LPAD(FR_SEQ.NEXTVAL, 4, '0')), '결산보고서 제목'
, '결산 보고서 내용입니다. 랄랄라', SYSDATE
, 'CP0000');
--=========================================================================
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
-- 【 2. 포트폴리오 테이블 INSERT 】
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
DESC PORTFOLIO;
--★[팀장만 INSERT 가능해야한다.]
--=========================================================================
INSERT INTO PORTFOLIO(P_CODE, TITLE
, CONTENT, KDATE
, VIEWS, MEM_CODE, CP_CODE)
VALUES('포트폴리오 코드', '결산보고서 제목'
, '결산 보고서 내용입니다. 랄랄라', SYSDATE
, 0, 'MEM0000', 'CP0000');
--=========================================================================
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
-- 【 3. 포트폴리오 태그 테이블 INSERT 】
--▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩▩
DESC P_TAG;
--=========================================================================
INSERT INTO P_TAG(PT_CODE, P_CODE, TAG_NAME)
VALUES (TO_CHAR('PT' || LPAD(PT_SEQ.NEXTVAL, 4, '0'))
, '포트폴리오 코드', 'JAVA');
--=========================================================================
--▩▩▩▩▩▩▩▩▩▩【별도의 메소드로 구성】▩▩▩▩▩▩▩▩▩▩▩▩
--◎ [CP_CODE 에 해당하는 프로젝트 제목 얻어내기]===========================
SELECT 프로젝트명 AS "PROJECT_NAME"
FROM OPEN_PRJ_MEMBERS_VIEW
WHERE 개설확정코드 = 'CP0001'
GROUP BY 프로젝트명;
--◎ [이번 포트폴리오 코드 얻어내기]========================================
--SELECT TO_CHAR('P' || LPAD(COUNT(P_CODE)+1, 4, '0')) AS NOW_P_CODE
--FROM PORTFOLIO;
SELECT TO_CHAR('P' || LPAD(NVL(MAX(TO_NUMBER(SUBSTR(P_CODE, 2))), 0)+1, 4, '0')) AS NOW_P_CODE
FROM PORTFOLIO;
--==========================================================================
--◎ [개설 확정 코드, 멤버 코드로 직위 반환받기]============================
SELECT P_MEMBER_ROLE AS "MEMBER_ROLE"
FROM PJ_MEMBER_SCORE_INFO_VIEW
WHERE CP_CODE = 'CP0001'
AND P_MEM_CODE = 'MEM0006';
--==========================================================================
--◎ [개설확정코드에 해당하는, 결산 보고서가 존재하는지 확인(COUNT(*))]======
SELECT DECODE(COUNT(*), 0, '미작성', '작성완료') AS "EXIST_FINAL_REPORT"
FROM FINAL_REPORT
WHERE CP_CODE = 'CP0001';
--===========================================================================
--★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
--▩▩▩▩▩▩▩▩▩▩【별도의 DTO로 구성】▩▩▩▩▩▩▩▩▩▩▩▩
--▶【FinalReport_ReportDTO】
-- 결산 보고서 코드(FR.FR_CODE), 포트폴리오 코드(P.P_CODE)
-- 개설신청코드(OPMV.개설신청코드), 개설확정코드(OPMV.개설확정코드)
-- 멤버코드(OPMV.회원코드), 멤버지원코드(OPMV.멤버지원코드)
-- 프로젝트명(OPMV.프로젝트명)
-- 제목(FR.TITLE), 내용(FR.CONTENT)
--SELECT FR.*, OPMV.*, P.*
SELECT FR.FR_CODE AS "FR_CODE"
, P.P_CODE AS "P_CODE"
, OPMV.개설신청코드 AS "AP_CODE"
, OPMV.개설확정코드 AS "CP_CODE"
, OPMV.회원코드 AS "LEADER_MEM_CODE"
, OPMV.멤버지원코드 AS "LEADER_MA_CODE"
, OPMV.프로젝트명 AS "PROJECT_NAME"
, FR.TITLE AS "REPORT_TITLE"
, FR.CONTENT AS "REPORT_CONTENT"
FROM FINAL_REPORT FR
RIGHT OUTER JOIN OPEN_PRJ_MEMBERS_VIEW OPMV
ON FR.CP_CODE = OPMV.개설확정코드
LEFT OUTER JOIN PORTFOLIO P
ON P.MEM_CODE = OPMV.회원코드
WHERE OPMV."담당직무" = '팀장'
AND OPMV."개설확정코드" = 'CP0001';
--★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
--▩▩▩▩▩▩▩▩▩▩【별도의 DTO로 구성】▩▩▩▩▩▩▩▩▩▩▩▩
--▶【FinalReport_PTagDTO】
-- 포트폴리오 코드(P.P_CODE), 포트폴리오 태그 코드(PT.PT_CODE)
-- 포트폴리오 태그명(PT.TAG_NAME)
SELECT P.P_CODE AS "P_CODE"
, PT.PT_CODE AS "PT_CODE"
, PT.TAG_NAME AS "TAG_NAME"
FROM FINAL_REPORT FR
RIGHT OUTER JOIN OPEN_PRJ_MEMBERS_VIEW OPMV
ON FR.CP_CODE = OPMV.개설확정코드
LEFT OUTER JOIN PORTFOLIO P
ON P.MEM_CODE = OPMV.회원코드
LEFT OUTER JOIN P_TAG PT
ON P.P_CODE = PT.P_CODE
WHERE 담당직무 = '팀장'
AND OPMV."개설확정코드" = 'CP0001';
select *
from member;
--● [결산 보고서 조회 / 삭제]
SELECT *
FROM FINAL_REPORT;
--************************
DELETE
FROM FINAL_REPORT
WHERE CP_CODE = 'CP0001';
--● [포트폴리오 태그 조회 / 삭제]================
SELECT *
FROM P_TAG;
--************************
DELETE
FROM P_TAG
WHERE P_CODE = 'P0001';
--● [포트폴리오 조회 / 삭제]================
SELECT *
FROM PORTFOLIO;
--************************
DELETE
FROM PORTFOLIO
WHERE P_CODE IN ('P0001');