오라클 11g PL/SQL 로만 sqlplus 에서 영화예매시스템 만들어보기

DB설계는 대충 이런식 
https://pt.scribd.com/document/312084198/Multiplex-Booking-System-Oracle-2-pdf#download
요 링크에서 참고했다.

위에 ERD를 참고하면 아래처럼 테이블들과 시퀀스들을 생성해준다.

create table Movies
(MovieId number,
 MovieName varchar2(255),
 PRIMARY KEY(MovieId));

create table Hall
(HallID number,
 HallDesc varchar2(255),
 TotalCapacity number,
 PRIMARY KEY(HallID));

 create table Shows
 (ShowId number,
  HalId number,
  MovieId number,
  SlotNo number,
  FromDate date,
  ToDate date,
  PRIMARY KEY(ShowId),
  foreign key(MovieId) references Movies(MovieId),
  foreign key(HalId) references Hall(HallID));
  
  create table Users
  (UserId number,
  UserType char,
  UserName varchar2(45),
  MobileNo number,
  EmailId varchar2(255),
  primary key(UserId));
  

  create table Booking
  (BookingId number,
  ShowId number,
  UserId number,
  BookedDate date,
  ShowDate date,
  PRIMARY KEY(BookingId),
  foreign key(ShowId) references Shows(ShowId),
  foreign key(UserId) references Users(UserId));
  
  create table SeatType
  (SeatTypeId number,
  SeatTypeDesc varchar2(255),
  SeatFare number,
  primary key(SeatTypeId));
  
  create table BookingDetail
  (BookingId number,
  SeatTypeId number,
  NoofSeats number,
  foreign key(BookingId) references Booking(BookingId),
  foreign key(SeatTypeId) references SeatType(SeatTypeId));
  
  create table HallCapacity
  (HallID number,
  SeatTypeId number,
  SeatCount number,
  primary key(HallID,SeatTypeId),
  foreign key(HallID) references Hall(HallID),
  foreign key(SeatTypeId) references SeatType(SeatTypeId));
  
  create sequence user_id increment by 1 start with 1;
  create sequence show_id increment by 1 start with 1;
  create sequence book_id increment by 1 start with 1;


그리고 적당하게 아래 처럼 임의로 데이터를 insert해준다.

insert into Movies values(111,'신비한 동물사전');
insert into Movies values(222,'형');
insert into Movies values(333,'닥터 스트레인지');

insert into Hall values(101,'닥터 스트레인지 상영관',250);
insert into Hall values(102,'닥터 스트레인지 상영관',250);
insert into Hall values(103,'형 상영관',200);
insert into Hall values(104,'신비한 동물사전 상영관',300);

insert into Shows values(show_id.nextval,101,333,3331,to_date(201611251000,'RRRRMMDDHH24MI'),to_date(201611251200,'RRRRMMDDHH24MI'));
insert into Shows values(show_id.nextval,101,333,3332,to_date(201611251300,'RRRRMMDDHH24MI'),to_date(201611251500,'RRRRMMDDHH24MI'));
insert into Shows values(show_id.nextval,101,333,3333,to_date(201611251600,'RRRRMMDDHH24MI'),to_date(201611251800,'RRRRMMDDHH24MI'));
insert into Shows values(show_id.nextval,101,333,3334,to_date(201611251900,'RRRRMMDDHH24MI'),to_date(201611252100,'RRRRMMDDHH24MI'));
insert into Shows values(show_id.nextval,101,333,3335,to_date(201611252200,'RRRRMMDDHH24MI'),to_date(201611252359,'RRRRMMDDHH24MI'));
insert into Shows values(show_id.nextval,102,333,3336,to_date(201611251130,'RRRRMMDDHH24MI'),to_date(201611251330,'RRRRMMDDHH24MI'));
insert into Shows values(show_id.nextval,102,333,3337,to_date(201611251400,'RRRRMMDDHH24MI'),to_date(201611251600,'RRRRMMDDHH24MI'));
insert into Shows values(show_id.nextval,102,333,3338,to_date(201611251630,'RRRRMMDDHH24MI'),to_date(201611251830,'RRRRMMDDHH24MI'));
insert into Shows values(show_id.nextval,102,333,3339,to_date(201611252030,'RRRRMMDDHH24MI'),to_date(201611252230,'RRRRMMDDHH24MI'));
insert into Shows values(show_id.nextval,102,333,33310,to_date(201611252330,'RRRRMMDDHH24MI'),to_date(201611260130,'RRRRMMDDHH24MI'));
insert into Shows values(show_id.nextval,103,222,2221,to_date(201611250830,'RRRRMMDDHH24MI'),to_date(201611251030,'RRRRMMDDHH24MI'));
insert into Shows values(show_id.nextval,103,222,2222,to_date(201611251100,'RRRRMMDDHH24MI'),to_date(201611251300,'RRRRMMDDHH24MI'));
insert into Shows values(show_id.nextval,103,222,2223,to_date(201611251330,'RRRRMMDDHH24MI'),to_date(201611251530,'RRRRMMDDHH24MI'));
insert into Shows values(show_id.nextval,103,222,2224,to_date(201611251600,'RRRRMMDDHH24MI'),to_date(201611251800,'RRRRMMDDHH24MI'));
insert into Shows values(show_id.nextval,103,222,2225,to_date(201611251830,'RRRRMMDDHH24MI'),to_date(201611252030,'RRRRMMDDHH24MI'));
insert into Shows values(show_id.nextval,103,222,2226,to_date(201611252100,'RRRRMMDDHH24MI'),to_date(201611252300,'RRRRMMDDHH24MI'));
insert into Shows values(show_id.nextval,103,222,2227,to_date(201611252330,'RRRRMMDDHH24MI'),to_date(201611260130,'RRRRMMDDHH24MI'));
insert into Shows values(show_id.nextval,104,111,1111,to_date(201611250930,'RRRRMMDDHH24MI'),to_date(201611251130,'RRRRMMDDHH24MI'));
insert into Shows values(show_id.nextval,104,111,1112,to_date(201611251230,'RRRRMMDDHH24MI'),to_date(201611251430,'RRRRMMDDHH24MI'));
insert into Shows values(show_id.nextval,104,111,1113,to_date(201611251530,'RRRRMMDDHH24MI'),to_date(201611251730,'RRRRMMDDHH24MI'));
insert into Shows values(show_id.nextval,104,111,1114,to_date(201611251830,'RRRRMMDDHH24MI'),to_date(201611252030,'RRRRMMDDHH24MI'));
insert into Shows values(show_id.nextval,104,111,1115,to_date(201611252130,'RRRRMMDDHH24MI'),to_date(201611252330,'RRRRMMDDHH24MI'));
insert into Shows values(show_id.nextval,104,111,1116,to_date(201611252359,'RRRRMMDDHH24MI'),to_date(201611260200,'RRRRMMDDHH24MI'));

insert into Users values(user_id.nextval,'A','idiot',01072255198,'idiot@gmail.com');
insert into Users values(user_id.nextval,'C','최순실',01012345678,'siri@gmail.com');
insert into Users values(user_id.nextval,'C','박근혜',01087654321,'puppet@gmail.com');
insert into Users values(user_id.nextval,'C','김정은',01012121212,'dictator@gmail.com');

insert into SeatType values(1,'Standard',10000);
insert into SeatType values(2,'Royal',20000);
insert into SeatType values(3,'VIP',30000);

insert into HallCapacity values(101,1,150);
insert into HallCapacity values(101,2,50);
insert into HallCapacity values(101,3,50);
insert into HallCapacity values(102,1,150);
insert into HallCapacity values(102,2,50);
insert into HallCapacity values(102,3,50);
insert into HallCapacity values(103,1,140);
insert into HallCapacity values(103,2,30);
insert into HallCapacity values(103,3,30);
insert into HallCapacity values(104,1,200);
insert into HallCapacity values(104,2,50);
insert into HallCapacity values(104,3,50);

아!!! 그전에 alter session set nls_date_format = 'RRRR-MM-DD HH24:MI';
요걸 해줘야 상영시간표가 시간단위로 나온다 dateformat 설정을 해줘야 된다.
이제 테이블생성및 적당히 데이터들을 insert 했으니..
PL로만 짜보자.. 우선 첫번째 스크립트... 나는 first.sql로 저장했다.

prompt Are you our member?
prompt *------------------*
prompt |    1. YES!       |
prompt *------------------*
prompt *---------------------------*
prompt |    2. No! I want to exit  |
prompt *---------------------------*
prompt *--------------------------------*
prompt |    3. No! I want to join here  |
prompt *--------------------------------*
accept selections prompt "Select what you want :  "

column script new_value v_scripts

select case '&selections'
       when '1' then 'signin'
       when '2' then 'exit'
       when '3' then 'signup'
       else 'first'
       end as script
from dual;

@&v_scripts
실행하면 대충 이런식이다...
멤버냐고 물은다음에 1을 입력하고 엔터를 치면
로그인을 유도하고
2를 입력하고 엔터를 치면 종료를
3을 입력하고 엔터를 치면 회원가입을 유도한다.
로그인이라.. 회원가입이라.. 뭐 그렇게 표현하기 민망할 정도로 그냥 이름과 이메일?..
입력하게 만들어 놨다.

아래가 로그인을 유도하는 스크립트..
그냥 대충 뭐 입력하면 메인메뉴로 넘어가게끔 해놨다.. signin.sql 이다.

prompt what is your name?
prompt what is your email?

declare 
v_un users.username%type;
v_em users.emailid%type;
v_uname users.username%type;
v_email users.emailid%type;
begin
select '&v_name','&v_email' into v_uname,v_email
from dual;
select username,emailid into v_un, v_em
from users where username = v_uname and emailid = v_email;
if v_un = v_uname then
if v_em = v_email then
DBMS_OUTPUT.PUT_LINE('Match!');
DBMS_OUTPUT.PUT_LINE('Already You are our Member!');
end if;
end if;
end;
/
@mainmenu

그리고 그다음 아래스크립트가 회원가입을 유도하는 뭐..
대충 이름,폰번호,이메일 입력하면 Users 테이블에 insert하도록 PL을 짰다. signup.sql 이다.

prompt Please Enter your Information in our Form
accept name prompt 'what is your name?'
accept mobile prompt 'what is your number of mobile phone?'
accept email prompt 'what is your email?'

insert into users values(user_id.nextval,'C','&name',&mobile,'&email');
commit;

accept selectionss prompt 'if you want to go to back(mainmenu), push anykey and enter'
column script new_value v_scriptss 

select case '&selectionss' 
       when '1' then 'first' 
  else 'first' 
       end as script 
from dual;

@&v_scriptss

각 1, 3 선택에서 아무키나 누르면 첫화면으로 돌아가도록 했다.
이제 뭐 로그인이라할 것 까지는 쪽팔릴정도지만 아무튼 그걸 거치면
mainmenu로 간다.
아래가 mainmenu.sql 스크립트이다.

cle scr
prompt [Main Menu] Please make a selection: 
prompt *----------------------*
prompt | 1: Show screentime   |
prompt *----------------------*
prompt *---------------------------------*
prompt | 2: Make a reservation for movie |
prompt *---------------------------------*
prompt *-----------------------------*
prompt | 3: Confirm your reservation |
prompt *-----------------------------*
prompt *---------------------------*
prompt | 4: Cancel the reservation |
prompt *---------------------------*
prompt *----------*
prompt | 5: Exit  |
prompt *----------*
accept selection prompt "Enter option 1~5: " 
column script new_value v_script 
select case '&selection' 
       when '1' then 'screentime' 
       when '2' then 'reservation' 
       when '3' then 'conreserve'
  when '4' then 'cancel'
  when '5' then 'exit'
       else 'mainmenu' 
       end as script 
from dual;
@&v_script

1 은 상영시간표를
2 는 예약기능
3 은 예약확인 기능
4 는 예약취소 기능
5 는 프로그램 종료

뭐 대충 이런식이다..

순서대로 스크립트는 

먼저 1 screentime.sql

select moviename,FROMDATE,TODATE,h.hallid,HALLDESC,TOTALCAPACITY 
from movies m, shows s,hall h
where m.movieid=s.movieid and s.halid=h.hallid
order by fromdate,moviename;

accept selection2 prompt 'if you want to go to back(mainmenu), push anykey and enter'
column script new_value v_script2 
select case '&selection2' 
       when '1' then 'mainmenu' 
  else 'mainmenu' 
       end as script 
from dual;
@&v_script2


그 다음 2 예약기능 스크립트 reservation.sql

select moviename,FROMDATE,TODATE,h.hallid,HALLDESC,TOTALCAPACITY 
from movies m, shows s,hall h
where m.movieid=s.movieid and s.halid=h.hallid
order by fromdate,moviename;

prompt Enter each value for list
prompt *-------------------------------------------------------------------------*
prompt | v_hid is hallid                                                      |
prompt | v_fd is starting time of movie                                       |
prompt | v_td is ending time of movie                                         |
prompt | v_uname is your name(customer)                                       |
prompt | v_seattype is class of seat (ex. 1 = Standard 2 = Royal 3 = VIP )    |
prompt | v_thenumofseat is the number of seat that you will reserve           |
prompt *-------------------------------------------------------------------------*

declare
v_sid shows.showid%TYPE;
v_uid users.userid%type;
v_sd shows.fromdate%type;
begin
select showid,fromdate into v_sid, v_sd
from movies m, shows s,hall h
where m.movieid=s.movieid and s.halid=h.hallid and h.hallid=&v_hid and fromdate = to_date('&v_fd','RRRR-MM-DD HH24:MI') and todate = to_date('&v_td','RRRR-MM-DD HH24:MI')
order by fromdate,moviename;
select userid into v_uid
from users
where username = '&v_uname';
insert into booking values(book_id.nextval,v_sid,v_uid,sysdate,v_sd);
insert into bookingdetail values(book_id.currval,&v_seattype,&v_thenumofseat);
end;
/

accept selection3 prompt 'if you want to go to back(mainmenu), push anykey and enter'
column script new_value v_script3 
select case '&selection3' 
       when '1' then 'mainmenu' 
  else 'mainmenu' 
       end as script 
from dual;
@&v_script3

그 다음 3 예약 확인기능 스크립트 conreserve.sql

prompt "enter your name"

select b.bookingid,bookeddate as "Booked DATE",showdate as "Starting Time of Movie",noofseats as "Number of Seat",username as "Name of reserved",seattypedesc as "Class of Seat",seatfare*noofseats as "Total fare"
from booking b,bookingdetail bd,users u,seattype st 
where b.bookingid=bd.bookingid and bd.seattypeid=st.seattypeid and u.username ='&name';



accept selection4 prompt 'if you want to go to back(mainmenu), push anykey and enter'
column script new_value v_script4 
select case '&selection4' 
       when '1' then 'mainmenu' 
  else 'mainmenu' 
       end as script 
from dual;
@&v_script4

그 다음 4 예약 취소 기능 스크립트  cancel.sql

prompt "enter your name"

select b.bookingid,bookeddate as "Booked DATE",showdate as "Starting Time of Movie",noofseats as "Number of Seat",username as "Name of reserved",seattypedesc as "Class of Seat",seatfare*noofseats as "Total fare"
from booking b,bookingdetail bd,users u,seattype st 
where b.bookingid=bd.bookingid and bd.seattypeid=st.seattypeid and u.username ='&name';

prompt Enter your BookingID

delete from bookingdetail where bookingid = &v_id;
delete from booking where bookingid = &v_id;


accept selection5 prompt 'if you want to go to back(mainmenu), push anykey and enter'
column script new_value v_script5 
select case '&selection5' 
       when '1' then 'mainmenu' 
  else 'mainmenu' 
       end as script 
from dual;
@&v_script5

마지막 종료기능 5 스크립드 exit.sql

exit

댓글

이 블로그의 인기 게시물

About JVM Warm up

About idempotent

About Kafka Basic

About ZGC

sneak peek jitpack

Spring Boot Actuator readiness, liveness probes on k8s

About Websocket minimize data size and data transfer cost on cloud

About G1 GC

대학생 코딩 과제 대행 java, python, oracle 네 번째