아직 도메인과 필수,선택의 사항을 반영하지 않았음.
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;
'활동사항 > db설계(2015.03~2015.06)' 카테고리의 다른 글
최종ERD와 프로시저 단위의 트랜잭션들 (0) | 2015.06.01 |
---|---|
예약 프로시저 생성시 오류 (0) | 2015.05.17 |
oracle 영화관련 테이블 작업하면서...(2) (0) | 2015.05.17 |
oracle 영화관련 테이블 작업하면서..(1) (0) | 2015.03.31 |
node.js oracle 연동 총정리 (3) | 2015.03.27 |
댓글을 달아 주세요