'2015/06'에 해당되는 글 1건

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

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;