아직 도메인과 필수,선택의 사항을 반영하지 않았음.



1. 예매 프로시저 

CREATE OR REPLACE PROCEDURE RESERVEPROC
( p_email in varchar2,
  p_class in varchar2,
  p_phonenumber in varchar2,
  p_name in varchar2,
  p_bookingcode in varchar2,
  p_timecode in varchar2,
  p_screencode in varchar2,
  p_moviecode in varchar2,
  p_totalprice in integer,
  p_seatcount in integer,
  p_seatcodes in SEATCODES
 )
 IS 
    I INTEGER;
 BEGIN
    --멤버 마일리지 업데이트
    
    IF p_email IS NOT NULL THEN
      UPDATE MEMBER
      SET MILEAGE = CASE 
                     WHEN class = 'D' THEN MILEAGE+p_totalprice*0.02
                     WHEN class = 'C' THEN MILEAGE+p_totalprice*0.04
                     WHEN class = 'B' THEN  MILEAGE+p_totalprice*0.06
                     WHEN class = 'A' THEN  MILEAGE+p_totalprice*0.08
                     WHEN class = 'S' THEN  MILEAGE+p_totalprice*0.1
                     END
      WHERE EMAIL = p_email;
    ELSE 
      INSERT INTO NONMEMBER
      VALUES (p_phonenumber,p_name);
    END IF;
                   
    --BOOKING테이블 예약레코드 삽입
      INSERT
      INTO BOOKING 
      VALUES (p_bookingcode,p_email,p_phonenumber,p_timecode,p_screencode,p_moviecode,p_totalprice,p_seatcount);

     
    --seatcodearr을 루프 돌면서 BOOKED_SEATS에 삽입하고 PERFORMANCE_SEAT의 사용여부 업데이트.  
    FOR I IN 1 .. p_seatcodes.COUNT
    LOOP
        --BOOKED_SEATS에 INSERT
        INSERT
        INTO BOOKED_SEATS 
        VALUES(p_bookingcode,p_screencode,p_seatcodes(I));
         
        --PERFORMANCE_SEAT UPDATE
        UPDATE
        PERFORMANCE_SEAT 
        SET SEATSTATUS=1 
        WHERE SEATCODE= p_seatcodes(I)
        and SCREENCODE= p_screencode
        and TIMECODE= p_timecode; 
    END LOOP;
    --커밋.
    commit;
END;

그런데 이러한 프로시저를 실행시키기 위해서 node.js에서 선택한 좌석들을 오라클 형식의 배열을 만들어 바인딩 시켜줘야 하는데 아직 node-oracledb에서 array binding을 제공하고 있지 않다.때문에 다음과 같은 블록문을 실행하여 스트링을 파싱하여 프로시저를 실행하도록 하였다.

--좌석 스트링을 파싱하여 예약프로시저 실행.
DECLARE
       p_seatcodes SEATCODES;
       s_seatcodes varchar2(300);
    BEGIN
       s_seatcodes := 'A1,B1,B2';
       SELECT REGEXP_SUBSTR(s_seatcodes, '[^,$]+', 1, LEVEL ) AS 검증항목
                            BULK collect into p_seatcodes
                            FROM DUAL
                            CONNECT BY REGEXP_SUBSTR(s_seatcodes, '[^,$]+', 1, LEVEL ) IS NOT NULL;
       RESERVEPROC('alciakng@uos.ac.kr','D','aa','SUPERPLEX12015-06-012','SUPERPLEX1','M1',60,6,p_seatcodes); 
  END;


2. 예매 취소 프로시저


--예약취소 프로시저
CREATE OR REPLACE PROCEDURE CANCELRESERVEPROC
( p_email in varchar2,
  p_class in varchar2,
  p_bookingcode in varchar2,
  p_totalprice in varchar2
 )
 IS 
    I INTEGER;
 BEGIN
    -- UPDATE PERFORMANCE_SEAT_TABLE
    UPDATE PERFORMANCE_SEAT 
    SET SEATSTATUS=0 
    where (screencode,seatcode,timecode) 
    in (select booked_seats.screencode,booked_seats.seatcode,booking.timecode 
        from booking, booked_seats 
        where booking.bookingcode = booked_seats.bookingcode 
        and booking.bookingcode=p_bookingcode);
    
    --멤버 마일리지 다시 깍아야함
    IF p_email IS NOT NULL THEN
      UPDATE MEMBER
      SET MILEAGE = CASE 
                     WHEN class = 'D' THEN MILEAGE-p_totalprice*0.02
                     WHEN class = 'C' THEN MILEAGE-p_totalprice*0.04
                     WHEN class = 'B' THEN  MILEAGE-p_totalprice*0.06
                     WHEN class = 'A' THEN  MILEAGE-p_totalprice*0.08
                     WHEN class = 'S' THEN  MILEAGE-p_totalprice*0.1
                     END
      WHERE EMAIL = p_email;
    END IF;
    
    --예약테이블에서 삭제(cascade로 booked_seat테이블도 알아서 삭제됨)
    delete 
    from booking 
    where bookingcode = p_bookingcode;
    commit;
END;  


3. 영화 등록 프로시저


CREATE OR REPLACE PROCEDURE ADDMOVIE
(
    name in varchar2,
    genre in varchar2,
    runningtime in varchar2,
    director in varchar2,
    rating in varchar2,
    company in varchar2,
    country in varchar2,
    actors in varchar2,
    image in varchar2,
    opendate in date,
    summary in varchar2
)
IS
    moviecode varchar2(10);
--영화등록 쿼리
begin 
    select 'M'||seq_movie.nextval into moviecode from dual;
    
    insert into movie 
    values(moviecode,name,genre,runningtime,director,rating,company,country,actors,image,opendate,summary);
    
    --이것만 실제에서 빼~
    insert into rating
    values(seq_rating.nextval,moviecode,0);
    commit;
end;

4. 일정등록 프로시저 


CREATE OR REPLACE PROCEDURE ADDTIMEPROC
(
  p_screencode in varchar2,
  p_moviecode in varchar2,
  p_moviedate in DATE,
  p_starttime in varchar2,
  p_endtime in varchar2
)
IS
  p_moviecount INTEGER;
BEGIN
  --상영회차를 count기반으로 계산
  select count(*)+1 
  into p_moviecount
  from time 
  where moviedate=p_MOVIEDATE and screencode=p_SCREENCODE;
  
  --time테이블에 insert
  insert 
  into time 
  values(p_SCREENCODE||to_char(p_MOVIEDATE,'yy-mm-dd')||p_MOVIECOUNT,p_STARTTIME,p_ENDTIME,p_MOVIEDATE,p_MOVIECOUNT,p_SCREENCODE,p_MOVIECODE);
 
  --PERFORMANCE_SEAT TABLE에 현재 등록된 좌석기반으로 insert
  INSERT INTO performance_seat (seatcode,screencode,timecode,seatstatus)
      SELECT
           seat.seatcode AS seatcode,
           seat.screencode AS screencode,
           p_SCREENCODE||to_char(p_MOVIEDATE,'yy-mm-dd')||p_MOVIECOUNT AS timecode,
           0 AS seatstatus
      FROM seat 
      WHERE screencode =p_SCREENCODE;
  
  commit;
END;


5. 워치리스트 추가 프로시저

--워치리스트 추가 프로시저 
CREATE OR REPLACE PROCEDURE WATCHLISTPROC
(p_email in varchar2,
 p_moviecode in varchar2
)
  IS
    IS_ADDED INTEGER;
  BEGIN
--이미 워치리스트에 추가되었는지 검사한다.
      select count(*) 
      into IS_ADDED
      from watchlist 
      where email=p_email and moviecode = p_moviecode;
      
      --추가 되어있지 않으면 추가한다.
      IF IS_ADDED=0 THEN
          insert into watchlist 
          values(p_email,p_moviecode); 
      END IF;

      commit;
END;


블로그 이미지

종환 Revolutionist-JongHwan

github.com/alciakng 항상 겸손하자.

1. 현재까지 작성한 예약 프로시저

--seatcodes라는 배열 타입 생성 CREATE OR REPLACE TYPE SEATCODES AS TABLE OF VARCHAR(3); --예약 프로시저 CREATE OR REPLACE PROCEDURE ReserveProc (bookingcode in varchar2, email in varchar2, timecode in varchar2, screencode in varchar2, moviecode in varchar2, totalprice in integer, seatcount in integer, --seatcodes라는 타입을 만들었다. seatcodearr in seatcodes, email in varchar2, class in varchar2 ) IS I BINARY_INTEGER; BEGIN --멤버 마일리지 업데이트 UPDATE MEMBER SET MILEAGE = CASE WHEN class = 'D' THEN MILEAGE+totalprice*0.02 WHEN class = 'C' THEN MILEAGE+totalprice*0.04 WHEN class = 'B' THEN MILEAGE+totalprice*0.06 WHEN class = 'A' THEN MILEAGE+totalprice*0.08 WHEN class = 'S' THEN MILEAGE+totalprice*0.1 END; --BOOKING테이블 예약레코드 삽입 INSERT INTO BOOKING VALUES (bookingcode,email,timecode,screencode,moviecode,totalprice,seatcount); --seatcodearr을 루프 돌면서 BOOKED_SEATS에 삽입하고 PERFORMANCE_SEAT의 사용여부 업데이트. FOR I IN 1 .. seatcodearr.COUNT LOOP --BOOKED_SEATS에 INSERT INSERT INTO BOOKED_SEATS VALUES(bookingcode,seatcodearr(I)); --PERFORMANCE_SEAT UPDATE UPDATE PERFORMANCE_SEAT SET SEATSTATUS=1 WHERE SEATCODE= seatcodearr(I) and TIMECODE= timecode; END LOOP; --커밋. commit; END;


오류타입

  • 오류: PL/SQL: Compilation unit analysis terminated
  • 오류(1,1): PLS-00410: duplicate fields in RECORD,TABLE or argument list are not permitted 



해결 ?


위와 같은오류는 아규먼트 선언부분에 중복변수가 있을 때 발생 

위의 코드를 보면 email이 중복이다. 

중복변수를 지워주면서 변수명도 바꿔주었다. 


CREATE OR REPLACE PROCEDURE RESERVEPROC
(p_bookingcode in varchar2,
 p_email in varchar2,
 p_timecode in varchar2,
 p_screencode in varchar2,
 p_moviecode in varchar2,
 p_totalprice in integer,
 p_seatcount in integer,
 p_seatcodes in seatcodes,
 p_class in varchar2
 )
 IS 
    I INTEGER;
 BEGIN
    --멤버 마일리지 업데이트
    UPDATE MEMBER
    SET MILEAGE = CASE 
                   WHEN class = 'D' THEN MILEAGE+p_totalprice*0.02
                   WHEN class = 'C' THEN MILEAGE+p_totalprice*0.04
                   WHEN class = 'B' THEN  MILEAGE+p_totalprice*0.06
                   WHEN class = 'A' THEN  MILEAGE+p_totalprice*0.08
                   WHEN class = 'S' THEN  MILEAGE+p_totalprice*0.1
                   END;
                   
    --BOOKING테이블 예약레코드 삽입               
    INSERT
    INTO BOOKING 
    VALUES (p_bookingcode,p_email,p_timecode,p_screencode,p_moviecode,p_totalprice,p_seatcount);               
     
    --seatcodearr을 루프 돌면서 BOOKED_SEATS에 삽입하고 PERFORMANCE_SEAT의 사용여부 업데이트.  
    FOR I IN 1 .. p_seatcodes.COUNT
    LOOP
        --BOOKED_SEATS에 INSERT
        INSERT
        INTO BOOKED_SEATS 
        VALUES(p_bookingcode,p_seatcodes(I));
         
        --PERFORMANCE_SEAT UPDATE
        UPDATE
        PERFORMANCE_SEAT 
        SET SEATSTATUS=1 
        WHERE SEATCODE= p_seatcodes(I)
        and TIMECODE= p_timecode;
    END LOOP;
    --커밋.
    commit;
END;



프로시저를 조회하니..


 select * from user_objects where object_type = 'PROCEDURE'; 

status가 valid하다고 나온다.성공!

블로그 이미지

종환 Revolutionist-JongHwan

github.com/alciakng 항상 겸손하자.

1. ERD 모델링



- 모델링에서 어려웠던 점


좌석을 등록하게 되면 SEAT테이블에 등록이 되는데 SEAT테이블에서는 일정에 따른 좌석의 사용여부를 관리할 수 없다.

SEAT에 사용여부 컬럼을 넣게 되면 TIME테이블에서 일정코드를 외래키로 받아서 따로 관리해야 되는데, 그렇게 되면 관리자가 좌석을 등록할 때 일정도 함께 등록해야 하는 모순이 생겨버린다. 설령 일정을 NULL허용을 해놓고 SEAT을 등록한다 하더라도 NULL값이 들어가게 되면 나중에 일정등록할 때 SEAT테이블에 UPDATE를 해주면서 다시 등록을 해야하는데 이 쿼리가 좀 만만치 않다.

그래서 PERFORMANCE_SEAT 테이블을 만들어 놓고 SEATSTATUS라는 사용여부 컬럼을 두었다. 관리자는 SEAT테이블에 좌석만 등록하고 일정을 등록할 시에 일정에 따른 좌석정보를 PERFORMANCE_SEAT테이블에 등록하는 데 이 때 다음과 같은 쿼리를 쓴다.




- 코딩시 어려웠던 점


예약할때 BOOKING테이블MEMBER테이블PERFORMANCE_SEAT 테이블, BOOKED_SEATS 테이블 이렇게 4개에 INSERT하고 UPDATE할 때 쿼리가 복잡하다. 이를 프로시저를 통해 해결하고자 한다(추후작성)


2. TEST ROWS


insert into movie values('M1','미쓰홍당무','코믹','200분','김종환','12세','서울시립대','한국','공효진','미쓰홍당무.jpg','2015/04/01','정말 끝내주게 웃깁니다.');

insert into movie values('M2','킹스맨','액션','150분','강주희','15세','서울시립대','미국','Dan Brown','킹스맨.jpg','2015/04/02','멋진액션!');

insert into movie values('M3','살인의추억','스릴러','300분','최윤주','19세','서울시립대','한국','송강호,김상경','살인의추억.jpg','2015/04/25','과연 누가 살인자일까?');

insert into movie values('M4','포화속으로','전쟁','150분','김종환','15세','서울시립대','한국','탑,권상우','포화속으로.jpg','2015/04/21','6.25 현장');

insert into movie values('M5','신세계','느와르','200분','김종환','19세','서울시립대','한국','이정재,황정민,최민식','신세계.jpg','2015/03/24','황정민 이정재 최민식 세남자의 느와르');

insert into movie values('M6','파파로티','드라마','150분','강주희','전체','서울시립대','한국','이제훈,한석규','파파로티.jpg','2015/03/21','테너의 감동적인 실화');

insert into movie values('M7','완득이','드라마','300분','최윤주','전체','서울시립대','한국','김윤석,유아인','완득이.jpg','2015/05/23','사제지간을 그린 드라마');

insert into movie values('M8','화이','스릴러','100분','김종환','19세','서울시립대','한국','여진구,김윤석','화이.jpg','2015/04/21','여진구를 다시보게 되는 작품');



insert into screen values('S1','1관');

insert into screen values('S2','2관');

insert into screen values('S3','3관');

insert into screen values('S4','4관');

insert into screen values('S5','5관');

insert into screen values('S6','6관');

insert into screen values('S7','7관');

insert into screen values('S8','8관');



insert into screen_movie values('S1','M1','2015-04-01','2015-05-29');

insert into screen_movie values('S1','M2','2015-03-28','2015-04-29');

insert into screen_movie values('S1','M3','2015-04-25','2015-06-02');

insert into screen_movie values('S1','M4','2015-03-17','2015-05-10');

insert into screen_movie values('S2','M1','2015-04-01','2015-05-29');

insert into screen_movie values('S2','M2','2015-03-28','2015-04-29');

insert into screen_movie values('S2','M3','2015-04-25','2015-06-02');

insert into screen_movie values('S2','M4','2015-03-17','2015-05-10');

insert into screen_movie values('S3','M1','2015-04-01','2015-05-29');

insert into screen_movie values('S3','M2','2015-03-28','2015-04-29');

insert into screen_movie values('S3','M3','2015-04-25','2015-06-02');

insert into screen_movie values('S3','M4','2015-03-17','2015-05-10');

insert into screen_movie values('S4','M1','2015-04-01','2015-05-29');

insert into screen_movie values('S4','M2','2015-03-28','2015-04-29');

insert into screen_movie values('S4','M3','2015-04-25','2015-06-02');

insert into screen_movie values('S4','M4','2015-03-17','2015-05-10');

insert into screen_movie values('S5','M1','2015-04-01','2015-05-29');

insert into screen_movie values('S5','M2','2015-03-28','2015-04-29');

insert into screen_movie values('S5','M3','2015-04-25','2015-06-02');

insert into screen_movie values('S5','M4','2015-03-17','2015-05-10');

insert into screen_movie values('S6','M1','2015-04-01','2015-05-29');

insert into screen_movie values('S6','M2','2015-03-28','2015-04-29');

insert into screen_movie values('S6','M3','2015-04-25','2015-06-02');

insert into screen_movie values('S6','M4','2015-03-17','2015-05-10');

insert into screen_movie values('S7','M1','2015-04-01','2015-05-29');

insert into screen_movie values('S7','M2','2015-03-28','2015-04-29');

insert into screen_movie values('S7','M3','2015-04-25','2015-06-02');

insert into screen_movie values('S7','M4','2015-03-17','2015-05-10');

insert into screen_movie values('S8','M1','2015-04-01','2015-05-29');

insert into screen_movie values('S8','M2','2015-03-28','2015-04-29');

insert into screen_movie values('S8','M3','2015-04-25','2015-06-02');

insert into screen_movie values('S8','M4','2015-03-17','2015-05-10');



insert into time values('S12015-05-011','07:20','09:20','2015-05-01',1,'S1','M1');

insert into time values('S12015-05-012','10:20','12:20','2015-05-01',2,'S1','M2');

insert into time values('S12015-05-013','13:20','15:20','2015-05-01',3,'S1','M3');

insert into time values('S12015-05-014','16:20','18:20','2015-05-01',4,'S1','M4');



insert into time values('S22015-05-011','07:20','09:20','2015-05-01',1,'S2','M1');

insert into time values('S22015-05-012','10:20','12:20','2015-05-01',2,'S2','M2');

insert into time values('S22015-05-013','13:20','15:20','2015-05-01',3,'S2','M3');

insert into time values('S22015-05-014','16:20','18:20','2015-05-01',4,'S2','M4');

insert into time values('S22015-05-015','18:40','20:20','2015-05-01',5,'S2','M1');



insert into time values('S32015-05-011','07:20','09:20','2015-05-01',1,'S3','M1');

insert into time values('S32015-05-012','10:20','12:20','2015-05-01',2,'S3','M2');

insert into time values('S32015-05-013','13:20','15:20','2015-05-01',3,'S3','M3');

insert into time values('S32015-05-014','16:20','18:20','2015-05-01',4,'S3','M4');



insert into time values('S42015-05-011','07:20','09:20','2015-05-01',1,'S4','M1');

insert into time values('S42015-05-012','10:20','12:20','2015-05-01',2,'S4','M2');

insert into time values('S42015-05-013','13:20','15:20','2015-05-01',3,'S4','M3');

insert into time values('S42015-05-014','16:20','18:20','2015-05-01',4,'S4','M4');



insert into seat values('A1','S1','A','1');

insert into seat values('A2','S1','A','2');

insert into seat values('A3','S1','A','3');

insert into seat values('A4','S1','A','4');

insert into seat values('A5','S1','A','5');

insert into seat values('A6','S1','A','6');

insert into seat values('A7','S1','A','7');

insert into seat values('A8','S1','A','8');

insert into seat values('A9','S1','A','9');

insert into seat values('A10','S1','A','10');

insert into seat values('A11','S1','A','11');

insert into seat values('A12','S1','A','12');

insert into seat values('A13','S1','A','13');

insert into seat values('A14','S1','A','14');

insert into seat values('A15','S1','A','15');

insert into seat values('A16','S1','A','16');

insert into seat values('A17','S1','A','17');

insert into seat values('A18','S1','A','18');

insert into seat values('B1','S1','B','1');

insert into seat values('B2','S1','B','2');

insert into seat values('B3','S1','B','3');

insert into seat values('B4','S1','B','4');

insert into seat values('B5','S1','B','5');

insert into seat values('B6','S1','B','6');

insert into seat values('B7','S1','B','7');

insert into seat values('B8','S1','B','8');

insert into seat values('B9','S1','B','9');

insert into seat values('B10','S1','B','10');

insert into seat values('B11','S1','B','11');

insert into seat values('B12','S1','B','12');

insert into seat values('B13','S1','B','13');

insert into seat values('B14','S1','B','14');

insert into seat values('B15','S1','B','15');

insert into seat values('B16','S1','B','16');

insert into seat values('B17','S1','B','17');

insert into seat values('B18','S1','B','18');



블로그 이미지

종환 Revolutionist-JongHwan

github.com/alciakng 항상 겸손하자.

 

 

영화예매 db를 짜고있다...

 

test row들..

 

insert into movie values('m1','미쓰홍당무','코믹','200분','김종환','정말 끝내주게 웃깁니다.','미쓰홍당무',0,'서울시립대');
insert into movie values('m2','킹스맨','액션','150분','강주희','액션 좋아요.','킹스맨',0,'서울시립대');
insert into movie values('m3','살인의추억','스릴러','300분','최윤주','정말 스릴있어요','미쓰홍당무',0,'서울시립대');
insert into movie values('m4','천재','드라마','100분','김종환','이사람 천잰가?','천재',0,'서울시립대');

insert into screen values('s1','1관');
insert into screen values('s2','2관');
insert into screen values('s3','3관');
insert into screen values('s4','4관');
insert into screen values('s5','5관');
insert into screen values('s6','6관');
insert into screen values('s7','7관');
insert into screen values('s8','8관');


insert into screen_movie values('s1','m1','2015-04-01','2015-05-29');
insert into screen_movie values('s1','m2','2015-03-28','2015-04-29');
insert into screen_movie values('s1','m3','2015-04-25','2015-06-02');
insert into screen_movie values('s1','m4','2015-03-17','2015-05-10');
insert into screen_movie values('s2','m1','2015-04-01','2015-05-29');
insert into screen_movie values('s2','m2','2015-03-28','2015-04-29');
insert into screen_movie values('s2','m3','2015-04-25','2015-06-02');
insert into screen_movie values('s2','m4','2015-03-17','2015-05-10');
insert into screen_movie values('s3','m1','2015-04-01','2015-05-29');
insert into screen_movie values('s3','m2','2015-03-28','2015-04-29');
insert into screen_movie values('s3','m3','2015-04-25','2015-06-02');
insert into screen_movie values('s3','m4','2015-03-17','2015-05-10');
insert into screen_movie values('s4','m1','2015-04-01','2015-05-29');
insert into screen_movie values('s4','m2','2015-03-28','2015-04-29');
insert into screen_movie values('s4','m3','2015-04-25','2015-06-02');
insert into screen_movie values('s4','m4','2015-03-17','2015-05-10');
insert into screen_movie values('s5','m1','2015-04-01','2015-05-29');
insert into screen_movie values('s5','m2','2015-03-28','2015-04-29');
insert into screen_movie values('s5','m3','2015-04-25','2015-06-02');
insert into screen_movie values('s5','m4','2015-03-17','2015-05-10');
insert into screen_movie values('s6','m1','2015-04-01','2015-05-29');
insert into screen_movie values('s6','m2','2015-03-28','2015-04-29');
insert into screen_movie values('s6','m3','2015-04-25','2015-06-02');
insert into screen_movie values('s6','m4','2015-03-17','2015-05-10');
insert into screen_movie values('s7','m1','2015-04-01','2015-05-29');
insert into screen_movie values('s7','m2','2015-03-28','2015-04-29');
insert into screen_movie values('s7','m3','2015-04-25','2015-06-02');
insert into screen_movie values('s7','m4','2015-03-17','2015-05-10');
insert into screen_movie values('s8','m1','2015-04-01','2015-05-29');
insert into screen_movie values('s8','m2','2015-03-28','2015-04-29');
insert into screen_movie values('s8','m3','2015-04-25','2015-06-02');
insert into screen_movie values('s8','m4','2015-03-17','2015-05-10');

insert into time values('s1','m1','07:20','09:20','2015-04-01',1);
insert into time values('s1','m2','10:20','12:20','2015-04-01',2);
insert into time values('s1','m3','13:20','15:20','2015-04-01',3);
insert into time values('s1','m4','16:20','18:20','2015-04-01',4);

insert into time values('s2','m1','07:20','09:20','2015-04-01',1);
insert into time values('s2','m2','10:20','12:20','2015-04-01',2);
insert into time values('s2','m3','13:20','15:20','2015-04-01',3);
insert into time values('s2','m4','16:20','18:20','2015-04-01',4);

insert into time values('s3','m1','07:20','09:20','2015-04-01',1);
insert into time values('s3','m2','10:20','12:20','2015-04-01',2);
insert into time values('s3','m3','13:20','15:20','2015-04-01',3);
insert into time values('s3','m4','16:20','18:20','2015-04-01',4);

insert into time values('s4','m1','07:20','09:20','2015-04-01',1);
insert into time values('s4','m2','10:20','12:20','2015-04-01',2);
insert into time values('s4','m3','13:20','15:20','2015-04-01',3);
insert into time values('s4','m4','16:20','18:20','2015-04-01',4);

 



insert into seat values('s1','a','1');

insert into seat values('s1','a','2');

insert into seat values('s1','a','3');

insert into seat values('s1','a','4');

insert into seat values('s1','a','5');

insert into seat values('s1','a','6');

insert into seat values('s1','a','7');

insert into seat values('s1','a','8');

insert into seat values('s1','a','9');

insert into seat values('s1','a','10');

insert into seat values('s1','a','11');

insert into seat values('s1','a','12');

insert into seat values('s1','a','13');

insert into seat values('s1','a','14');

insert into seat values('s1','a','15');

insert into seat values('s1','a','16');

insert into seat values('s1','a','17');

insert into seat values('s1','a','18');

insert into seat values('s1','b','1');

insert into seat values('s1','b','2');

insert into seat values('s1','b','3');

insert into seat values('s1','b','4');

insert into seat values('s1','b','5');

insert into seat values('s1','b','6');

insert into seat values('s1','b','7');

insert into seat values('s1','b','8');

insert into seat values('s1','b','9');

insert into seat values('s1','b','10');

insert into seat values('s1','b','11');

insert into seat values('s1','b','12');

insert into seat values('s1','b','13');

insert into seat values('s1','b','14');

insert into seat values('s1','b','15');

insert into seat values('s1','b','16');

insert into seat values('s1','b','17');

insert into seat values('s1','b','18');


commit;

 

부트스트랩 기반으로 영화 리스트 ui를 만들고 이에 필요한 query를 짜야한다.

 

(1) 특정 상영관과 날짜를 선택했을때 영화목록을 받아오고 (선호도, 개봉일자) 중 하나를 택하여 정렬할수 있는 쿼리

 

(2) (1)에 더하여 영화를 카테고리별로 받아오는 쿼리

 

 

ex)

//스크린코드가 s1인 스크린에 상영중인 영화를 출력하는 쿼리.
select * from movie where moviecode IN (select moviecode from screen_movie where SCREENCODE='s1');
//스크린코드가 s1이고 현재날짜가 해당영화의 시작일과 종료일 사이에 있을때 해당하는 영화를 출력하는 쿼리.
select * from movie where moviecode IN (select moviecode from screen_movie where SCREENCODE='s1' and '2015-05-20' > STARTDATE and '2015-05-20'< ENDDATE);
//스크린코드가 s1이고 현재날짜가 해당영화의 시작일과 종료일 사이에 있고 장르가 코믹인 영화를 출력하는 쿼리.
select * from movie where moviecode IN (select moviecode from screen_movie where SCREENCODE='s1' and '2015-05-20' > STARTDATE and '2015-05-20'< ENDDATE) and genre='코믹';
//스크린코드가 s1이고 현재날짜가 해당영화의 시작일과 종료일 사이에 있고 장르가 코믹인 영화를 선호도가 높은 영화부터 정렬하여 출력하는 쿼리.
select * from movie where moviecode IN (select moviecode from screen_movie where SCREENCODE='s1' and '2015-05-20' > STARTDATE and '2015-05-20'< ENDDATE) and genre='코믹' order by rating DESC;

블로그 이미지

종환 Revolutionist-JongHwan

github.com/alciakng 항상 겸손하자.

학교 텀프로젝트로 db설계 과목을 듣게 되었다....그런데 rdb를 무조건 oracle을 써야만 하니..

jsp,서블릿은 경험이 많이 부족하고 node.js 로 연동을 시도해 보았다.


1. oracledb 모듈설치(oracle 사에서 공식지원하는 모듈) 


우선 oracle 공식홈페이지의 node.js support를 참고하였다.

https://blogs.oracle.com/opal/entry/introducing_node_oracledb_a_node

git-hub 오픈소스 프로젝트로 나와있다. 윈도우 사용자 이기때문에 

https://github.com/oracle/node-oracledb/blob/master/INSTALL.md 을 참고하여 oracle에서 공식지원하는 node.js 모듈인 oracledb 모듈을 설치하는데 성공하였다.


2. node.js와의 연동 test


dbconfig.js


var oracledb = require('oracledb');
 
module.exports = {
		  user          : "JH",
		  password      : "JH",
		  connectString : "localhost/XE"
};



movieController.js

exports.addMovie = function(req,res){
	
	oracledb.getConnection(dbConfig,
		function(err,connection){
		
			 if (err) {
			      console.error(err.message);
			      return;
			 }
			 console.log(req.body);
			 connection.execute(
			 "insert into Movie VALUES(:CODE,:NAME,:CATEGORY,:RUNNINGTIME,:DIRECTOR,:OUTLINE,:IMAGE)"
		    ,[1,req.body.name,req.body.category,req.body.runningtime,req.body.director,req.body.outline,req.body.image]
		    ,
			 function(err){
				if(err){
					console.log(err.message);
					return;
				}
				connection.commit(function(err){
					if(err){
						console.log(err.message);
						return;
					}
					res.render('addMovie');
				 })
		     });
          });
}






위와 같은 방식으로 config 폴더에 db configure set을 하고 controller에서 간단한 insert를 해보았다..


아래는 간단하게 만든 html 폼이다. 



이렇게 폼까지 만들고 db에 insert를 해보았으나 한글깨짐 문제가 발생한다. 

select하고 콘솔에 출력해보았더니(빨간색 동그라미 친부분) 한글이 깨져서 나온다.


열심히 구글링 해서 (http://www.enjoydev.net/xe_new/index.php?mid=en_devboard&category=556&document_srl=289&listStyle=viewer)


sysdba로 접속 후..

update sys.props$ set value$='KO16MSWIN949' where name='NLS_CHARACTERSET'; 
update sys.props$ set value$='KO16MSWIN949' where name='NLS_NCHAR_CHARACTERSET'; 
update sys.props$ set value$='KOREAN_KOREA.KO16MSWIN949' where name='NLS_LANGUAGE';

위와 같이 캐릭터셋을 변경한 후 오라클 재가동.

commit;

shutdown immediate;

startup;


NLS_LANG 변경.

시작>실행>regedit을 실행 후 oracle>key_xe>nls_lang 값을

korean_korea.ut8로 변경하였다..

oracle character set을 변경하는 방법까지 적용하였으나, 결과는 마찬가지다.





3. oracle node.js 파트 담당자가 언어 서포트가 아직 안된다고 한다... (git-hub issue page 공식답변)


문제해결!

 

실행 -> regedit -> NLS_LANG을 AMERICAN_AMERICA.UTF8로 설정 후 한글이 정상적으로 출력되었다.


NLS_LANG이 KOREAN이면 오히려 한글 출력이 안되고 꼭 아메리칸 아메리카 UTF8로 설정해야 한다.


블로그 이미지

종환 Revolutionist-JongHwan

github.com/alciakng 항상 겸손하자.