(15) Oracle SQL 쿼리문

1. 회원 테이블

drop table members;
drop sequence seq_members;
commit;

create table members (
    mem_no number(8) not null,
    mem_id varchar2(100) not null,
    mem_pw varchar2(100) not null,
    mem_name varchar2(100) not null,
    mem_gender varchar2(30) not null,
    mem_email varchar2(150) not null,
    mem_phone varchar2(150) not null,
    mem_postcode varchar2(30) not null,
    mem_address1 varchar2(300) not null,
    mem_address2 varchar2(300) not null,
    mem_profileimg varchar2(4000) null,
    grade_code varchar2(5), -- NOR01, VIP01
    enabled varchar2(2) not null, -- 1: 활성화, 0: 회원 탈퇴, 2: 휴면 회원
    mem_category varchar2(2) not null, -- 01: 사용자, 02: 중간 관리자, 03: 관리자
    mem_regdate date not null,
    mem_agedate varchar2(10) not null,
    constraint pk_members primary key(mem_id)
);

create sequence seq_members 
start with 1 
increment by 1 
nomaxvalue nocache;

select 'private ' || 
        decode(lower(data_type), 'number', 'int ', 'String ') || 
        lower(column_name) || ';'
  from cols
 where lower(table_name) = 'members';
 
INSERT INTO members (mem_no, mem_id, mem_pw, mem_name, mem_gender, mem_email, mem_phone, mem_postcode, mem_address1, mem_address2, mem_profileimg, grade_code, enabled, mem_category, mem_regdate, mem_agedate)
VALUES (seq_members.nextval, 'user1', 'password1', '홍길동', 'M', 'hong.gil@example.com', '010-1234-5678', '12345', '서울시 강남구', '아파트 101호', null, 'NOR01', '1', '01', TO_DATE('2023-01-01', 'YYYY-MM-DD'), '2023-01-01');
INSERT INTO members (mem_no, mem_id, mem_pw, mem_name, mem_gender, mem_email, mem_phone, mem_postcode, mem_address1, mem_address2, mem_profileimg, grade_code, enabled, mem_category, mem_regdate, mem_agedate)
VALUES (seq_members.nextval, 'user2', 'password2', '이영희', 'F', 'lee.younghee@example.com', '010-9876-5432', '54321', '서울시 강동구', '아파트 202호', null, 'SIL01', '1', '02', TO_DATE('2023-02-01', 'YYYY-MM-DD'), '2023-02-01');
INSERT INTO members (mem_no, mem_id, mem_pw, mem_name, mem_gender, mem_email, mem_phone, mem_postcode, mem_address1, mem_address2, mem_profileimg, grade_code, enabled, mem_category, mem_regdate, mem_agedate)
VALUES (seq_members.nextval, 'user3', 'password3', '김철수', 'M', 'kim.chulsoo@example.com', '010-5555-7890', '67890', '서울시 강서구', '아파트 303호', null, 'GOL01', '1', '01', TO_DATE('2023-03-01', 'YYYY-MM-DD'), '2023-03-01');
INSERT INTO members (mem_no, mem_id, mem_pw, mem_name, mem_gender, mem_email, mem_phone, mem_postcode, mem_address1, mem_address2, mem_profileimg, grade_code, enabled, mem_category, mem_regdate, mem_agedate)
VALUES (seq_members.nextval, 'user4', 'password4', '박영희', 'F', 'park.younghee@example.com', '010-4444-0123', '98765', '서울시 관악구', '아파트 404호', null, 'PLT01', '1', '02', TO_DATE('2023-04-01', 'YYYY-MM-DD'), '2023-04-01');
INSERT INTO members (mem_no, mem_id, mem_pw, mem_name, mem_gender, mem_email, mem_phone, mem_postcode, mem_address1, mem_address2, mem_profileimg, grade_code, enabled, mem_category, mem_regdate, mem_agedate)
VALUES (seq_members.nextval, 'user5', 'password5', '이철수', 'M', 'lee.chulsoo@example.com', '010-1111-3333', '23456', '서울시 구로구', '아파트 505호', null, 'NOR01', '1', '01', TO_DATE('2023-05-01', 'YYYY-MM-DD'), '2023-05-01');
commit;

select * from members;

---

2. 공지사항 게시판 테이블

------------------------------------
-- 공지사항 게시판 SQL
drop table notice;
drop sequence seq_notice;
drop table noticefile;
drop sequence seq_noticefile;

create table notice(
    bo_no number(8) not null,
    bo_title VARCHAR2(300) not null,
    bo_content VARCHAR2(4000) not null,
    bo_writer VARCHAR2(150) not null,
    bo_date date not null,
    bo_hit number(8) DEFAULT 0 null,
    bo_impor VARCHAR2(2) null, -- Y, N
    CONSTRAINT pk_notice PRIMARY KEY(bo_no)
);

create sequence seq_notice increment by 1 start with 1 nocache;

create table noticefile(
    file_no number(8) not null,
    bo_no number(8) not null,
    file_name VARCHAR2(300) not null,
    file_size number(20) not null,
    file_fancysize VARCHAR2(100) not null,
    file_mime VARCHAR2(100) not null,
    file_savepath VARCHAR2(2000) not null,
    file_downcount number(8) not null,
    CONSTRAINT pk_noticefile PRIMARY KEY(file_no),
    CONSTRAINT fk_noticefile_bo_no foreign key(bo_no) references notice(bo_no)
);

create sequence seq_noticefile increment by 1 start with 1 nocache;

commit;

DECLARE
   v_user_id VARCHAR2(10) := '관리자';
BEGIN
   FOR i IN 1..100 LOOP
      INSERT INTO notice(bo_no, bo_title, bo_content, bo_writer, bo_date, bo_hit)
      VALUES (
         seq_notice.nextval,
         '제목' || TO_CHAR(i),
         '내용' || TO_CHAR(i),
         v_user_id,
         SYSDATE,
         0
      );
   END LOOP;
   COMMIT;
END;
/

-- 이전글/다음글 조회 쿼리
<select id="prevNextInfo" parameterType="int" resultType="noticeVO">
		select a.*
		from(
		    select bo_no, bo_title, bo_writer, bo_date, bo_hit, 'prev' as "prevnext_flag", row_number() over (order by bo_no desc) rnum from notice
		    <![CDATA[
		    	where bo_no < ${boNo}
		    ]]>
		) a
		where a.rnum = 1
		union
		select a.*
		from(
		    select bo_no, bo_title, bo_writer, bo_date, bo_hit, 'next' as "prevnext_flag", row_number() over (order by bo_no asc) rnum from notice
		    <![CDATA[
		    	where bo_no > ${boNo}
		    ]]>
		) a
		where a.rnum = 1
	</select>

---

3. 여행 정보 테이블

-- 여행 정보 테이블 생성
drop table journeyinfo;
drop sequence seq_journeyinfo;

create table journeyinfo (
    info_no number(8) not null, -- 테이블 번호, 자동 증가
    info_name varchar2(500) not null, -- 지역명(한글)
    info_engname varchar2(500) not null, -- 지역명(영어)
    info_description varchar2(4000) not null, -- 지역 설명
    info_flightyn varchar2(20) null, -- 항공 여부
    info_flight varchar2(20) null, -- 직항/왕복 기록
    info_flighttime varchar2(500) null, -- 항공 소요 시간
    info_visayn varchar2(20) null, -- 비자 여부
    info_visaexp varchar2(20) null, -- 비자/무비자 기록
    info_visatime varchar2(500) null, -- 비자 유효 기간
    info_voltage varchar2(20) not null, -- 전압 110V/220V 기록
    info_timedifer varchar2(500) not null, -- 한국 대비 시차 기록
    info_previewimg varchar2(4000) null, -- 여행 정보 미리보기 사진
    info_regdate date not null,
    constraint pk_journeyinfo primary key(info_no)
);

create sequence seq_journeyinfo increment by 1 start with 1 nocache;

select 'private ' || 
        decode(lower(data_type), 'number', 'int ', 'String ') || 
        lower(column_name) || ';'
  from cols
 where lower(table_name) = 'journeyinfo';
 
commit;

---

4. 포인트 결제 내역

-- 포인트 결제 내역
CREATE TABLE pointpayment (
   point_no   NUMBER      NOT NULL,
   poing_account   NUMBER      NULL,
   point_date   DATE      NOT NULL,
   poing_type   VARCHAR2(10)      NOT NULL,
   point_content   VARCHAR2(1000)      NULL,
   mem_id   VARCHAR2(20)      NOT NULL
);

create sequence seq_pointpayment increment by 1 start with 1 nocache;

---

5. qna 게시판 테이블

-- qna 게시판 메뉴 테이블
create table qnamenu (
    menu_id varchar2(30) not null, -- 메뉴 ID
    menu_lower varchar2(30) null, -- 하위 메뉴 ID
    menu_div varchar2(10) not null, -- 메뉴 구분 (사용자 : U, 관리자 : A)
    menu_order number(8) not null, -- 메뉴 순서
    menu_name varchar2(500) not null, -- 메뉴 명
    menu_description varchar2(4000) null, -- 메뉴 설명
    menu_url varchar2(4000) not null, -- 메뉴 URL
    menu_yn varchar2(10) not null, -- 메뉴 사용 여부
    constraint pk_qnamenu primary key(menu_id)
);

create table qnaboard (
    bo_no number(8) not null,
    bo_title VARCHAR2(300) not null,
    bo_content VARCHAR2(4000) not null,
    bo_writer VARCHAR2(150) not null,
    bo_date date not null,
    bo_hit number(8) DEFAULT 0 null,
    menu_id varchar2(30) not null, -- 메뉴 ID
    menu_lower varchar2(30) null, -- 하위 메뉴 ID
    CONSTRAINT pk_qnaboard PRIMARY KEY(bo_no)
);

---

6. 채팅방 테이블

-- 채팅방 테이블
select 'private ' || 
        decode(lower(data_type), 'number', 'int ', 'String ') || 
        lower(column_name) || ';'
  from cols
 where lower(table_name) = 'chat';

-- 1. 채팅방
drop table chatroom;
drop sequence seq_chatroom;

create table chatroom (
    room_no number not null, -- 채팅방 번호, 자동 증가
    room_regdate date not null, -- 채팅방 생성시간
    mg_no number not null, -- 동행 그룹 번호
    constraint pk_chatroom primary key(room_no),
    constraint fk_chatroom_mg_no foreign key(mg_no) references mategroup(mg_no)
);

create sequence seq_chatroom increment by 1 start with 1 nocache;

-- 2. 채팅
drop table chat;
drop sequence seq_chat;

create table chat (
    chat_no number not null, -- 채팅 순번, 자동 증가
    mem_id varchar2(100) not null, -- 멤버 테이블로부터 참조
    room_no number not null, -- 채팅방 참가 테이블로부터 참조
    chat_file varchar2(4000) null, -- 채팅방 파일
    chat_content varchar2(4000) not null, -- 채팅내용
    chat_ymd varchar2(500) not null, -- 채팅 년월일
    chat_hms varchar2(500) not null, -- 채팅 시분초
    chat_cnt varchar2(1000) not null, -- 채팅 카운트, 0: 입장/퇴장, 1: 채팅 시작, 1 이상: 계속해서 채팅함
    constraint pk_chat primary key(chat_no),
    constraint fk_chat_room_no foreign key(room_no) references chatroom(room_no)
);

create sequence seq_chat increment by 1 start with 1 nocache;

commit;

-- chat (chat_no, mem_id, room_no, chat_file, chat_content, chat_ymd, chat_hms, chat_cnt)

SELECT sub.*
, m.*
, cr.room_no
, cr.room_regdate
, c.chat_no
, c.chat_file
, c.chat_content
, c.chat_regdate
, c.chat_cnt
FROM (
    SELECT 
        a.*, 
        b.mategroup_no, 
        b.mategroup_id, 
        b.mategroup_agree, 
        b.mategroup_apply, 
        c.pl_private, 
        c.pl_title, 
        c.pl_theme, 
        c.pl_thumburl, 
        c.pl_msize, 
        c.pl_rdate
    FROM 
        mategroup a, 
        mategroup_member b, 
        planer c
    WHERE 
        a.mg_no = b.mg_no
        AND a.pl_no = c.pl_no
        AND a.pl_no = 1294
) sub
, chatroom cr
, members m
, chat c
WHERE 
    1 = 1
    AND cr.mg_no = sub.mg_no
    AND sub.mategroup_id = m.mem_id
    and sub.mategroup_id = c.mem_id
    AND sub.mategroup_apply = 'Y';

---

7. 채팅방 테스트 쿼리

-- 모집자 목록 들고오기
select d.*
     , e.*
  from (
    select a.*
         , b.mategroup_no
         , b.mategroup_id
         , b.mategroup_agree
         , b.mategroup_apply
         , c.pl_private
         , c.pl_title
         , c.pl_theme
         , c.pl_thumburl
         , c.pl_msize
         , c.pl_rdate
      from mategroup a
         , mategroup_member b
         , planer c
     where 1=1
       and a.mg_no = b.mg_no
       and a.pl_no = c.pl_no
       and a.pl_no = 1294
  ) d,
  members e
 where 1=1
   and d.mategroup_id = e.mem_id
;

UPDATE mategroup_member m
   SET m.mategroup_apply = 'W'
 WHERE m.mg_no = (
       SELECT a.mg_no
         FROM mategroup a, planer c
        WHERE a.mg_no = m.mg_no
          AND a.pl_no = c.pl_no
          AND a.pl_no = 1294
      )
   AND m.mategroup_id = 'user1';
commit;

---

8. 실시간 알림 테이블

-- 실시간 알림 테이블
drop table realtimealert;
drop sequence seq_realtimealert;

create table realtimealert(
    real_no number not null, -- 실시간 알림 순번, 자동 증가
    real_id varchar2(100) not null, -- 수신자 아이디
    mem_id varchar2(100) not null, -- 발신자 아이디
    mem_name varchar2(100) not null, -- 발신자 이름
    real_title varchar2(4000) not null, -- 실시간 알림 제목
    real_content varchar2(4000) not null, -- 실시간 알림 내용
    real_type varchar2(100) not null, -- 정보(info), 주의(warn), 경고(dang)
    real_readyn varchar2(1), -- 알림 읽음 여부
    real_url varchar2(4000), -- 해당 페이지 url 경로
    constraint pk_realtimealert primary key(real_no)
);

select count(*) from realtimealert where real_readyn = 'N' and real_id = 'a001'; -- 읽지 않은 갯수 카운트 
-- 읽고 난 이후 real_readyn 컬럼을 y로 바꿀때 where 조건에 걸릴거? real_id를 조건으로 일괄 update
select count(*) from realtimealert where real_readyn = 'Y' and real_id = 'a001';; -- 읽지 않은 갯수 카운트

create sequence seq_realtimealert increment by 1 start with 1 nocache;

------------------------------------------------

-- 발신자
drop table realtimesender;
drop sequence seq_realtimesender;

create table realtimesender (
    realsen_no number not null, -- 실시간 알림 순번, 자동 증가
    realsen_id varchar2(100) not null, -- 발신자 아이디(부모 테이블)
    realsen_name varchar2(100) not null, -- 발신자 이름
    realsen_title varchar2(4000) not null, -- 실시간 알림 제목
    realsen_content varchar2(4000) not null, -- 실시간 알림 내용
    realsen_type varchar2(100) not null, -- 정보(info), 주의(warn), 경고(dang)
    realsen_url varchar2(4000), -- 해당 페이지 url 경로
    constraint pk_realtimesender primary key(realsen_no)
);

create sequence seq_realtimesender increment by 1 start with 1 nocache;

select * from realtimesender order by realsen_no asc;
select count(*) from realtimesender order by realsen_no asc;

-- 수신자
drop table realtimereceiver;
drop sequence seq_realtimereceiver;

create table realtimereceiver(
    realrec_no number not null, -- 실시간 알림 순번, 자동 증가
    realrec_id varchar2(100) not null, -- 수신자 아이디
    realsen_readyn varchar2(1), -- 알림 읽음 여부
    realsen_no number not null, -- 발신자 로그 순번, 외래키
    constraint pk_realtimereceiver primary key(realrec_no),
    constraint fk_realtimereceiver_realsen_no foreign key(realsen_no) references realtimesender(realsen_no)
);

create sequence seq_realtimereceiver increment by 1 start with 1 nocache;

select * from realtimereceiver order by realrec_no asc;
select count(*) from realtimereceiver order by realrec_no asc;

---