SQL - 예매 데이터 출력

찌니·2022년 1월 14일
0

Oracle

목록 보기
1/1
post-thumbnail
  • Oracle

나의 역할?
-- 영화관 정보, 교통/주차 출력, 회원가입 여부

-- 테이블 생성

DROP TABLE PAY_CANCEL;
DROP TABLE COM_SEAT;
DROP TABLE MEMBER_PAY;
DROP TABLE GUEST_PAY;
DROP TABLE GUEST;
DROP TABLE SEAT;
DROP TABLE PEOPLE;
DROP TABLE PEOPLE_TYPE;
DROP TABLE TICKETING;
DROP TABLE TIMETABLE;
DROP TABLE QUESTION;
DROP TABLE THEATER;
DROP TABLE LOCATION;
DROP TABLE CINEMA;
DROP TABLE M_GRADE;
DROP TABLE M_VIDEO;
DROP TABLE MOVIE;
DROP TABLE COUPON;
DROP TABLE POINT;
DROP TABLE MEMBER_RANK;
DROP TABLE OFTEN_CGV;
DROP TABLE MEMBER;
DROP TABLE NOTICE;
/* 공지사항 */
CREATE TABLE NOTICE (
	NOTICE_CODE NUMBER(4) NOT NULL, /* 번호 */
	NSUBJECT NVARCHAR2(10) NOT NULL, /* 구분 */
	NTITLE NVARCHAR2(40) NOT NULL, /* 제목 */
	NDATE DATE NOT NULL, /* 등록일 */
	NVIEW NUMBER, /* 조회수 */
	NCONTENT NVARCHAR2(500) NOT NULL /* 내용 */
);
ALTER TABLE NOTICE ADD CONSTRAINT PK_NOTICE PRIMARY KEY ( NOTICE_CODE );
/* 회원 */
CREATE TABLE MEMBER (
	ID VARCHAR2(20) NOT NULL, /* 회원ID */
	NICKNAME NVARCHAR2(10) NOT NULL, /* 닉네임 */
	PASSWORD VARCHAR2(20) NOT NULL, /* 비밀번호 */
	NAME NVARCHAR2(10) NOT NULL, /* 이름 */
	SSN VARCHAR2(14) NOT NULL, /* 주민번호 */
	TEL VARCHAR2(14) NOT NULL, /* 핸드폰번호 */
	EMAIL VARCHAR2(30) NOT NULL, /* 이메일 */
	MGRADE NVARCHAR2(5), /* 등급 */
	MPOINT NUMBER /* 포인트 */
);
ALTER TABLE MEMBER ADD CONSTRAINT PK_MEMBER PRIMARY KEY ( ID );
/* 자주가는CGV */
CREATE TABLE OFTEN_CGV (
	CGV_NAME NVARCHAR2(10) NOT NULL, /* 자주가는CGV */
	ID VARCHAR2(20) /* 회원ID */
);
ALTER TABLE OFTEN_CGV ADD CONSTRAINT PK_OFTEN_CGV PRIMARY KEY ( CGV_NAME );
ALTER TABLE OFTEN_CGV ADD CONSTRAINT FK_OFTENCGV_ID FOREIGN KEY ( ID ) REFERENCES MEMBER ( ID );
/* 회원등급 */
CREATE TABLE MEMBER_RANK (
	RNAME NVARCHAR2(5) NOT NULL, /* 등급명 */
	LOPOINT NUMBER(5) NOT NULL, /* 최저 포인트 */
	HIPOINT NUMBER(5) NOT NULL /* 최고 포인트 */
);
/* 포인트 */
CREATE TABLE POINT (
	POINT_CODE VARCHAR2(10) NOT NULL, /* 포인트코드 */
	ID VARCHAR2(20), /* 회원ID */
	PNAME NVARCHAR2(20) NOT NULL, /* 포인트이름 */
	POINT NUMBER NOT NULL, /* 포인트 */
	PDATE DATE NOT NULL /* 적립일 */
);
ALTER TABLE POINT ADD CONSTRAINT PK_POINT PRIMARY KEY ( POINT_CODE );
ALTER TABLE POINT ADD CONSTRAINT FK_POINT_ID FOREIGN KEY ( ID ) REFERENCES MEMBER ( ID );
/* 관람권/할인쿠폰 관리 */
CREATE TABLE COUPON (
	COUPON_CODE VARCHAR2(10) NOT NULL, /* 관람/할인 쿠폰번호 */
	ID VARCHAR2(20), /* 회원ID */
	CDATE DATE NOT NULL, /* 등록일 */
	CPERIOD NVARCHAR2(20), /* 유효기간 */
	CUSEDATE NVARCHAR2(10) /* 사용일 */
);
ALTER TABLE COUPON ADD CONSTRAINT PK_COUPON PRIMARY KEY ( COUPON_CODE );
ALTER TABLE COUPON ADD CONSTRAINT FK_COUPON_ID FOREIGN KEY ( ID ) REFERENCES MEMBER ( ID );
/* 영화 */
CREATE TABLE MOVIE (
	MOVIE_CODE VARCHAR2(10) NOT NULL, /* 영화코드 */
	TITLE NVARCHAR2(50) NOT NULL, /* 영화제목 */
	DIRECTOR NVARCHAR2(20), /* 감독 */
	GRADE NVARCHAR2(2), /* 등급 */
	MDATE DATE, /* 개봉일자 */
	COUNTRY NVARCHAR2(11), /* 국가 */
	ACTOR NVARCHAR2(100), /* 주연배우 */
	GENRE NVARCHAR2(20), /* 장르 */
	RUNTIME NVARCHAR2(4), /* 상영시간 */
	STORY NVARCHAR2(1000) /* 줄거리 */
);
ALTER TABLE MOVIE ADD CONSTRAINT PK_MOVIE PRIMARY KEY ( MOVIE_CODE );
/* 예고편 */
CREATE TABLE M_VIDEO (
	VIDEO_CODE VARCHAR2(10) NOT NULL, /* 영상코드 */
	MOVIE_CODE VARCHAR2(10), /* 영화코드 */
	VDATE DATE NOT NULL, /* 영상등록일 */
	VEXPLAIN NVARCHAR2(500) /* 영상설명 */
);
ALTER TABLE M_VIDEO ADD CONSTRAINT PK_M_VIDEO PRIMARY KEY ( VIDEO_CODE );
ALTER TABLE M_VIDEO ADD CONSTRAINT FK_MVIDEO_MOVIE FOREIGN KEY ( MOVIE_CODE ) REFERENCES MOVIE ( MOVIE_CODE );
/* 평점 */
CREATE TABLE M_GRADE (
	GRADE_CODE VARCHAR2(10) NOT NULL, /* 평점코드 */
	MOVIE_CODE VARCHAR2(10), /* 영화코드 */
	ID VARCHAR2(20), /* 회원ID */
	REVIEW NVARCHAR2(500) NOT NULL, /* 관람평 */
	REVIEW_REG DATE NOT NULL, /* 관람평등록일 */
	MLIKE NUMBER /* 좋아요 */
);
ALTER TABLE M_GRADE	ADD	CONSTRAINT PK_M_GRADE PRIMARY KEY (	GRADE_CODE );
ALTER TABLE M_GRADE ADD CONSTRAINT FK_MGRADE_ID FOREIGN KEY ( ID ) REFERENCES MEMBER ( ID );
ALTER TABLE M_GRADE ADD CONSTRAINT FK_MGRADE_MOVIE FOREIGN KEY ( MOVIE_CODE ) REFERENCES MOVIE ( MOVIE_CODE );
/* 영화관 */
CREATE TABLE CINEMA (
	CINEMA_CODE NVARCHAR2(10) NOT NULL, /* 영화관코드 */
    	CNAME NVARCHAR2(10), /* 지점명 */
	CADDR NVARCHAR2(50) NOT NULL, /* 주소 */
	CTEL VARCHAR2(15) NOT NULL /* 전화번호 */
);
ALTER TABLE CINEMA ADD CONSTRAINT PK_CINEMA PRIMARY KEY ( CINEMA_CODE );
/* 위치/주차장 안내 */
CREATE TABLE LOCATION (
	LOC_CODE VARCHAR2(10) NOT NULL, /* 위치코드 */
	CINEMA_CODE NVARCHAR2(10), /* 영화관코드 */
	TRAFFIC NVARCHAR2(500) NOT NULL, /* 교통안내 */
	PARKING NVARCHAR2(500) NOT NULL /* 주차안내 */
);
ALTER TABLE LOCATION ADD CONSTRAINT PK_LOCATION_LOC PRIMARY KEY ( LOC_CODE );
ALTER TABLE LOCATION ADD CONSTRAINT FK_LOCATION_CINEMA FOREIGN KEY ( CINEMA_CODE ) REFERENCES CINEMA ( CINEMA_CODE );
/* 상영관 */
CREATE TABLE THEATER (
	THEATER_CODE NVARCHAR2(20) NOT NULL, /* 상영관코드 */
	CINEMA_CODE NVARCHAR2(10), /* 영화관코드 */
	TNAME NVARCHAR2(10) NOT NULL, /* 상영관명 */
	TLOC NVARCHAR2(50) NOT NULL, /* 위치 */
	TSEAT NUMBER(3) NOT NULL /* 좌석수 */
);
ALTER TABLE THEATER ADD CONSTRAINT PK_THEATER PRIMARY KEY ( THEATER_CODE );
ALTER TABLE THEATER ADD CONSTRAINT FK_THEATER_CINEMA FOREIGN KEY ( CINEMA_CODE ) REFERENCES CINEMA ( CINEMA_CODE );
/* 문의사항 */
CREATE TABLE QUESTION (
	QUESTION_CODE VARCHAR2(10) NOT NULL, /* 문의번호 */
	ID VARCHAR2(20), /* 회원ID */
	CINEMA_CODE NVARCHAR2(10), /* 영화관코드 */
	QTYPE NVARCHAR2(2) NOT NULL, /* 유형 */
	QTITLE NVARCHAR2(40) NOT NULL, /* 제목 */
	QCONTENT NVARCHAR2(500) NOT NULL, /* 내용 */
	QDATE DATE NOT NULL /* 등록일 */
);
ALTER TABLE QUESTION ADD CONSTRAINT PK_QUESTION PRIMARY KEY ( QUESTION_CODE );
ALTER TABLE QUESTION ADD CONSTRAINT FK_QUESTION_ID FOREIGN KEY ( ID ) REFERENCES MEMBER ( ID );
ALTER TABLE QUESTION ADD CONSTRAINT FK_QUESTION_CINEMA FOREIGN KEY ( CINEMA_CODE ) REFERENCES CINEMA ( CINEMA_CODE );
/* 상영시간표 */
CREATE TABLE TIMETABLE (
	MOVIE_CODE VARCHAR2(10) NOT NULL, /* 영화코드 */
	THEATER_CODE NVARCHAR2(20) NOT NULL, /* 상영관코드 */
	TDATE DATE NOT NULL, /* 날짜 */
	TIME VARCHAR2(15) NOT NULL, /* 시간 */
	DIMENSION VARCHAR2(10), /* 영상기술 */
	SUB NVARCHAR2(2) /* 자막/더빙 */
);
ALTER TABLE TIMETABLE ADD CONSTRAINT PK_TIMETABLE PRIMARY KEY ( MOVIE_CODE, THEATER_CODE, TDATE, TIME );
ALTER TABLE TIMETABLE ADD CONSTRAINT FK_TIMETABLE_MOVIE FOREIGN KEY ( MOVIE_CODE ) REFERENCES MOVIE ( MOVIE_CODE );
ALTER TABLE TIMETABLE ADD CONSTRAINT FK_TIMETABLE_THEATER FOREIGN KEY ( THEATER_CODE ) REFERENCES THEATER ( THEATER_CODE );
/* 예매 */
CREATE TABLE TICKETING (
	TICKETING_CODE VARCHAR2(10) NOT NULL, /* 예매번호 */
	MOVIE_CODE VARCHAR2(10), /* 영화코드 */
	THEATER_CODE NVARCHAR2(20), /* 상영관코드 */
    TDATE DATE, /* 날짜 */
	TIME VARCHAR2(15) /* 시간 */
);
ALTER TABLE TICKETING ADD CONSTRAINT PK_TICKETING PRIMARY KEY ( TICKETING_CODE );
ALTER TABLE TICKETING ADD CONSTRAINT FK_TICKETING_THEATER FOREIGN KEY ( MOVIE_CODE, THEATER_CODE, TDATE, TIME ) REFERENCES TIMETABLE ( MOVIE_CODE, THEATER_CODE, TDATE, TIME );
/* 인원 유형 */
CREATE TABLE PEOPLE_TYPE (
	P_TYPE VARCHAR2(10) NOT NULL, /* 유형 */
	PRICE NUMBER /* 가격 */
);
ALTER TABLE PEOPLE_TYPE ADD CONSTRAINT P_TYPE PRIMARY KEY ( P_TYPE );
/* 인원 */
CREATE TABLE PEOPLE (
    TICKETING_CODE VARCHAR2(10), /* 예매번호 */
	NOMAL NUMBER(1) default 0, /* 일반 */
	CHILD NUMBER(1) default 0, /* 청소년 */
	SPECIAL NUMBER(1) default 0, /* 우대 */
    SENIOR NUMBER(1) default 0 /* 경로 */
);
ALTER TABLE PEOPLE ADD CONSTRAINT PK_PEOPLE PRIMARY KEY ( TICKETING_CODE );
ALTER TABLE PEOPLE ADD CONSTRAINT FK_PEOPLE_TICKETING FOREIGN KEY ( TICKETING_CODE ) REFERENCES TICKETING ( TICKETING_CODE );
/* 좌석 */
CREATE TABLE SEAT (
	SEAT_CODE NVARCHAR2(10) NOT NULL, /* 좌석코드 */
	THEATER_CODE NVARCHAR2(20), /* 상영관코드 */
	DESTROY NUMBER(1), /* 파손여부 */
    SNAME VARCHAR2(5),
	SEAT_NAME NVARCHAR2(10) , /* 좌석명 */
	SPRICE NUMBER(4) /* 가격 */
);
ALTER TABLE SEAT ADD CONSTRAINT PK_SEAT PRIMARY KEY ( SEAT_CODE );
ALTER TABLE SEAT ADD CONSTRAINT FK_SEAT_THEATER FOREIGN KEY ( THEATER_CODE ) REFERENCES THEATER ( THEATER_CODE );
/* 예매완료좌석 */
CREATE TABLE COM_SEAT (
	SEAT_CODE NVARCHAR2(10) not null, /* 좌석코드 */
	TICKETING_CODE VARCHAR2(10) not null /* 예매번호 */
);
ALTER TABLE COM_SEAT ADD CONSTRAINT PK_COMSEAT PRIMARY KEY ( SEAT_CODE, TICKETING_CODE );   
/* 비회원 */
CREATE TABLE GUEST (
	GUEST_CODE VARCHAR2(10) NOT NULL, /* 비회원코드 */
	BIRTH_DATE NUMBER(8) NOT NULL, /* 생년월일 */
	TEL VARCHAR2(14) NOT NULL, /* 휴대폰번호 */
	PASSWORD NUMBER(4) NOT NULL, /* 비밀번호 */
	CONSENT NUMBER(1) NOT NULL /* 개인정보동의 */
);
ALTER TABLE GUEST ADD CONSTRAINT PK_GUEST PRIMARY KEY ( GUEST_CODE );
/* 비회원결제 */
CREATE TABLE GUEST_PAY (
	GUESTPAY_CODE VARCHAR2(10) NOT NULL, /* 결제코드 */
	TICKETING_CODE VARCHAR2(10), /* 예매번호 */
	NPRICE NUMBER NOT NULL, /* 결제금액 */
	NDATE DATE NOT NULL, /* 결제날짜 */
	GUEST_CODE VARCHAR2(10) /* 비회원코드 */
);
ALTER TABLE GUEST_PAY ADD CONSTRAINT PK_GUEST_PAY PRIMARY KEY ( GUESTPAY_CODE );
ALTER TABLE GUEST_PAY ADD CONSTRAINT FK_GUESTPAY_GUEST FOREIGN KEY ( GUEST_CODE ) REFERENCES GUEST ( GUEST_CODE );
ALTER TABLE GUEST_PAY ADD CONSTRAINT FK_GUESTPAY_TICKETING FOREIGN KEY ( TICKETING_CODE ) REFERENCES TICKETING ( TICKETING_CODE );
/* 회원결제 */
CREATE TABLE MEMBER_PAY (
	PAY_CODE VARCHAR2(10) NOT NULL, /* 결제코드 */
	TICKETING_CODE VARCHAR2(10), /* 예매번호 */
	PRICE NUMBER NOT NULL, /* 결제금액 */
	PAYWAY NVARCHAR2(10), /* 결제수단 */
	PAY_DATE DATE NOT NULL, /* 결제날짜 */
	ID VARCHAR2(20), /* 회원ID */
	POINT_CODE VARCHAR2(10), /* 포인트코드 */
	COUPON_CODE VARCHAR2(10) /* 관람/할인코드 */
);
ALTER TABLE MEMBER_PAY ADD CONSTRAINT PK_MEMBER_PAY PRIMARY KEY ( PAY_CODE );
ALTER TABLE MEMBER_PAY ADD CONSTRAINT FK_MEMBERPAY_ID FOREIGN KEY ( ID ) REFERENCES MEMBER ( ID );
ALTER TABLE MEMBER_PAY ADD CONSTRAINT FK_MEMBERPAY_POINT FOREIGN KEY ( POINT_CODE ) REFERENCES POINT ( POINT_CODE );
ALTER TABLE MEMBER_PAY ADD CONSTRAINT FK_MEMBERPAY_COUPON FOREIGN KEY ( COUPON_CODE ) REFERENCES COUPON ( COUPON_CODE );
ALTER TABLE MEMBER_PAY ADD CONSTRAINT FK_MEMBERPAY_TICKETING FOREIGN KEY ( TICKETING_CODE ) REFERENCES TICKETING ( TICKETING_CODE );
/* 예매취소 */
CREATE TABLE PAY_CANCEL (
	CANCEL_SEQ NUMBER NOT NULL, /* 취소시퀀스 */
	PAY_CODE VARCHAR2(10), /* 회원결제코드 */
    GUESTPAY_CODE VARCHAR2(10) NOT NULL, /* 비회원결제코드 */
	M_TICKET VARCHAR2(10), /* 예매번호 */
	CANCEL_DATE DATE /* 취소날짜 */
);
ALTER TABLE PAY_CANCEL ADD CONSTRAINT PK_PAYCANCEL PRIMARY KEY ( CANCEL_SEQ );
ALTER TABLE PAY_CANCEL ADD CONSTRAINT FK_PAYCANCEL_PAY FOREIGN KEY ( PAY_CODE ) REFERENCES MEMBER_PAY ( PAY_CODE );
ALTER TABLE PAY_CANCEL ADD CONSTRAINT FK_PAYCANCEL_GUESTPAY FOREIGN KEY ( GUESTPAY_CODE ) REFERENCES GUEST_PAY ( GUESTPAY_CODE );

-- 쿼리 생성

/* 예매페이지 출력 */

CREATE OR REPLACE PROCEDURE UP_PRINT_MOVIE
IS
        vrow movie%rowtype;
BEGIN
        FOR vrow in (SELECT DISTINCT grade, title FROM movie where mdate >= (sysdate-30) order by title)
        LOOP
                dbms_output.put_line(vrow.grade || ' || ' ||  vrow.title);
        END LOOP;
END;

CREATE OR REPLACE PROCEDURE UP_PRINT_CINEMA
(
        pname cinema.cname%type
)
IS
        vrow cinema%rowtype;
BEGIN
        dbms_output.put_line('=====' || pname || '=====');
        FOR vrow in (SELECT DISTINCT cname, cinema_code FROM cinema where cname=pname order by cinema_code)
        LOOP
                dbms_output.put_line(vrow.cinema_code);
        END LOOP;
END;

CREATE OR REPLACE PROCEDURE UP_SELECT_TIME
(
        ptitle movie.title%type,
        pcinema cinema.cinema_code%type,
        pdate date
)
IS
        vrow timetable%rowtype;
BEGIN
        dbms_output.put_line(pdate);
        dbms_output.put_line('=====' || ptitle || '=====');
        FOR vrow in (SELECT * FROM timetable 
        where REGEXP_LIKE(theater_code, pcinema) and movie_code=(select movie_code from movie where title=ptitle) and tdate=pdate
        order by time)
        LOOP
                dbms_output.put_line(vrow.theater_code || ' [' || vrow.dimension || '/' || vrow.sub || '] ' || vrow.time);
        END LOOP;
END;

select * from timetable;
select * from movie;

exec UP_PRINT_MOVIE;
exec UP_PRINT_CINEMA('서울');
exec UP_SELECT_TIME('더 스파이','강남', '2021-05-15');




/* 고른영화정보 */
CREATE OR REPLACE PROCEDURE UP_MOVIEINFO
( pticket TICKETING.TICKETING_CODE%type )
IS
        vdm timetable.dimension%type;
        vgrade movie.grade%type;
        vloc nvarchar2(20);
        vseat nvarchar2(20);
        vpeoplesum number;
        vnum number;
        vpeople people%rowtype;
        vseatsum nvarchar2(100);
        vtitle movie.title%type;
        vdate timetable.tdate%type;
        vcinema theater.cinema_code%type;
        vtime timetable.time%type;
        vsname seat.seat_name%type;
BEGIN
        SELECT distinct dimension into vdm FROM timetable WHERE movie_code=(select movie_code from TICKETING where TICKETING_CODE=pticket);
        SELECT grade into vgrade FROM movie WHERE movie_code=(select movie_code from TICKETING where TICKETING_CODE=pticket);
        SELECT title into vtitle FROM movie WHERE movie_code=(select movie_code from TICKETING where TICKETING_CODE=pticket);
        SELECT distinct tdate into vdate FROM timetable WHERE tdate=(select tdate from TICKETING where TICKETING_CODE=pticket);
        SELECT cinema_code into vcinema FROM theater WHERE theater_code=(select theater_code from TICKETING where TICKETING_CODE=pticket);
        SELECT distinct time into vtime FROM timetable WHERE time=(select time from TICKETING where TICKETING_CODE=pticket);
        SELECT tname || tloc into vloc FROM theater where theater_code=(select theater_code from TICKETING where TICKETING_CODE=pticket);
        select distinct seat_name into vsname from seat s join com_seat c on s.seat_code=c.seat_code where ticketing_code=pticket;
        select nvl(nomal,0)+nvl(child,0)+nvl(special,0)+nvl(senior,0) into vpeoplesum from people where TICKETING_CODE=pticket;
        FOR vnum IN 1 .. vpeoplesum
        LOOP
        select sname into vseat from(select rownum num, sname from seat s join com_seat c on s.seat_code=c.seat_code where ticketing_code=pticket) t
                    where t.num=vnum;
        vseatsum := vseatsum || vseat || ' ';
        END LOOP;
        FOR vpeople IN ( SELECT * FROM people where TICKETING_CODE=pticket )
        LOOP
        dbms_output.put_line( RPAD(vtitle, 15, ' ') || ' |  [극장]   CGV ' || RPAD(vcinema, 20, ' ') || '|  [좌석명]   ' || vsname );
        dbms_output.put_line( RPAD(' ', 15, ' ') || '|  [일시]   ' || RPAD(to_char(vdate, 'yyyy.mm.dd(dy)')
                                                    ||' '||vtime, 24, ' ') || '|  [좌석번호] ' || vseatsum );
        dbms_output.put_line( RPAD(vdm, 15, ' ') || '|  [상영관] ' || RPAD(vloc, 20, ' ') || '    |' );
        dbms_output.put( RPAD(vgrade || ' 관람가' , 15, ' ') || ' |  [인원]   ');
            IF vpeople.nomal is not null THEN dbms_output.put( '일반 ' || vpeople.nomal || '명 ');
            END IF;
            IF vpeople.child is not null THEN dbms_output.put( ', 청소년 ' || vpeople.child || '명 ' );
            END IF;
            IF vpeople.special is not null THEN dbms_output.put( ', 우대 ' || vpeople.special || '명 ' );
            END IF;
            IF vpeople.senior is not null THEN dbms_output.put( ', 경로 ' || vpeople.senior || '명 ' );
            END IF;
        dbms_output.put('   |');
        dbms_output.put_line(' ');
        END LOOP;
END;

exec UP_MOVIEINFO('TK319');


/* 상영시간표 출력 */

CREATE OR REPLACE PROCEDURE UP_PRINT_TIMETABLE
(
    pcinema cinema.cinema_code%type,
    pdate date
)
IS
        vrow timetable%rowtype;
        vtitle nvarchar2(50);
        vtime varchar2(100);
        vrow2 timetable%rowtype;
        vrow3 timetable%rowtype;
BEGIN
        dbms_output.put_line( pcinema || ' / ' || extract(day FROM pdate) || '일' );
        dbms_output.put_line( ' ' );
        dbms_output.put_line( '======================' );
        dbms_output.put_line( ' ' );
        FOR vrow in (SELECT DISTINCT movie_code, theater_code, tdate, dimension, sub 
                    FROM timetable where extract(day FROM tdate) = extract(day FROM pdate) and REGEXP_LIKE(theater_code, pcinema))
        LOOP
        SELECT title into vtitle FROM movie WHERE movie_code=vrow.movie_code;
                dbms_output.put_line('[ ' || vtitle || ' ] ');
                dbms_output.put_line(' ');
                dbms_output.put_line('  ' || vrow.theater_code || ' | ' || vrow.dimension || '(' || vrow.sub || ') ' );
                      FOR vrow2 IN (SELECT DISTINCT * FROM timetable where extract(day FROM tdate) = extract(day FROM pdate) 
                                    and REGEXP_LIKE(theater_code, pcinema) and movie_code = vrow.movie_code)
                      LOOP
                      dbms_output.put(' | ' || vrow2.time );
                      END LOOP;
                dbms_output.put_line(' |');
                dbms_output.put_line(' ' );
        END LOOP;
END;

exec UP_PRINT_TIMETABLE('강남','2015-05-15');




-- 출력 
CREATE OR REPLACE PROCEDURE UP_TICKETPRICE
( pticket TICKETING.TICKETING_CODE%type )
IS
        vpeople people%rowtype;
        vnprice number;
        vcprice number;
        vsprice number;
        vseprice number;
        vsum number;
BEGIN
        select price into vnprice from PEOPLE_TYPE where p_type='일반';
        select price into vcprice from PEOPLE_TYPE where p_type='청소년';
        select price into vsprice from PEOPLE_TYPE where p_type='경로';
        select price into vseprice from PEOPLE_TYPE where p_type='우대';
        FOR vpeople IN ( SELECT * FROM people WHERE TICKETING_CODE = pticket )
        LOOP
            IF vpeople.nomal is not null 
            THEN dbms_output.put_line( RPAD('일반', 10, ' ') || vnprice || '원 X ' || vpeople.nomal );
            END IF;
            IF vpeople.child is not null 
            THEN dbms_output.put_line( RPAD('청소년', 10, ' ') || vcprice || '원 X ' || vpeople.child  );
            END IF;
            IF vpeople.special is not null 
            THEN dbms_output.put_line( RPAD('우대', 10, ' ') || vsprice || '원 X ' || vpeople.special );
            END IF;
            IF vpeople.senior is not null 
            THEN dbms_output.put_line( RPAD('경로', 10, ' ') || vseprice || '원 X ' || vpeople.senior );
            END IF;
            vsum := (vnprice*vpeople.nomal)+(vcprice*vpeople.child)+(vsprice*vpeople.special)+(vseprice*vpeople.senior);
        END LOOP;
       dbms_output.put_line( RPAD('총금액', 15, ' ') || vsum || '원');
END;

exec UP_TICKETPRICE('TK319');

/* 영화관 정보 출력 */

create or replace procedure up_cinema
(
    p_cinema_code cinema.cinema_code%type   --code
)
is
     vcrow cinema%rowtype;  -- code     
begin
    select * into vcrow from cinema where cinema_code = p_cinema_code;
    dbms_output.put_line('극장이름 : CGV' || p_cinema_code);
    dbms_output.put_line('주소 : '|| vcrow.caddr);
    dbms_output.put_line('전화번호 : '|| vcrow.ctel);
end;

exec up_cinema('강남');

/* 영화관 위치/주차장 안내 */

create or replace procedure up_location
(
    p_cinema_code LOCATION.cinema_code%type   --code
)
is
     vlrow location%rowtype;  -- code     
begin
    select * into vlrow from LOCATION where cinema_code = p_cinema_code;
    dbms_output.put_line('극장이름 : CGV' || p_cinema_code);
    dbms_output.put_line('교통안내 : '|| vlrow.traffic);
    dbms_output.put_line('주차안내 : '|| vlrow.parking);
end;

exec up_location('강남');


/* 영화 예고편 안내 */

CREATE OR REPLACE PROCEDURE UP_MVIDEO(
    ptitle movie.title%type
)
IS
    TYPE vm_video IS RECORD(
        title movie.title%type ,
        vdate m_video.vdate%type ,
        vexplain m_video.vexplain%type     
    );
    vrow vm_video;
BEGIN
    FOR vrow IN (
        SELECT title, vdate, vexplain
        FROM movie m JOIN m_video v ON m.movie_code = v.movie_code
        WHERE REGEXP_LIKE(title, ptitle)
    )
    LOOP
        dbms_output.put_line(vrow.title || ' ' || vrow.vdate || ' ' || vrow.vexplain);
    END LOOP;
END;

EXEC UP_MVIDEO('스파이럴');


/* 로그인 */

CREATE OR REPLACE PROCEDURE CGV_UP_LOGON
(
        PID MEMBER.ID%TYPE
        ,PPASSWORD MEMBER.PASSWORD%TYPE
)
IS
    VISID NUMBER(1);
    VPASSWORD MEMBER.PASSWORD%TYPE;
BEGIN
    SELECT COUNT(*) INTO VISID 
    FROM MEMBER
    WHERE ID=PID;
    
    IF VISID = 0 THEN 
        DBMS_OUTPUT.PUT_LINE('ID를 입력하세요.');
    ELSIF VISID = 1 THEN
        SELECT PASSWORD INTO VPASSWORD
        FROM MEMBER
        WHERE ID = PID;
        CASE
        WHEN PPASSWORD = VPASSWORD THEN 
            DBMS_OUTPUT.PUT_LINE('로그인 성공'); 
            COMMIT;
        WHEN PPASSWORD <> VPASSWORD 
        THEN DBMS_OUTPUT.PUT_LINE('아이디 또는 패스워드가 맞지 않습니다. 확인 후 입력해주세요.'); 
        ELSE DBMS_OUTPUT.PUT_LINE('패스워드를 입력하세요.');
        END CASE;
    END IF;
END;


/* 비회원 예매내역 확인 */
CREATE OR REPLACE PROCEDURE CGV_UP_GUEST_LOGON
(
    P_GUEST_BIRTH GUEST.BIRTH_DATE%TYPE
    ,P_GUEST_TEL GUEST.TEL%TYPE
    ,P_GUEST_PASSWORD GUEST.PASSWORD%TYPE
)
IS
    VISGUEST NUMBER(1);
BEGIN
    SELECT COUNT(*) INTO VISGUEST 
    FROM GUEST
    WHERE BIRTH_DATE = P_GUEST_BIRTH AND TEL = P_GUEST_TEL AND PASSWORD = P_GUEST_PASSWORD;
    
    CASE
    WHEN P_GUEST_BIRTH IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('법정생년월일(8자리)을 입력해주세요.');
    WHEN P_GUEST_TEL IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('휴대폰 번호를 입력해주세요.');  
    WHEN P_GUEST_PASSWORD IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('비밀번호(4자리)를 입력해주세요.');
    ELSE
         IF VISGUEST = 1 THEN
            DBMS_OUTPUT.PUT_LINE('예매 내역 확인');
         ELSE
            DBMS_OUTPUT.PUT_LINE('입력하신 정보와 일치하는 예매내역이 없습니다.'); 
        END IF;
        
    END CASE;
END;


-- 회원가입
CREATE OR REPLACE PROCEDURE CGV_UP_SIGN
(
    PID MEMBER.ID%TYPE
    ,PNICKNAME MEMBER.NICKNAME%TYPE
    ,PPASSWORD MEMBER.PASSWORD%TYPE
    ,PNAME MEMBER.NAME%TYPE
    ,PSSN MEMBER.SSN%TYPE
    ,PTEL MEMBER.TEL%TYPE
    ,PEMAIL MEMBER.EMAIL%TYPE
)
IS
    VISID NUMBER(1);
    VID MEMBER.ID%TYPE;
BEGIN
    SELECT COUNT(*) INTO VISID FROM MEMBER WHERE ID=PID;
    SELECT ID INTO VID FROM MEMBER WHERE ID=PID;
    IF VISID = 1 THEN
        DBMS_OUTPUT.PUT_LINE(VID || ' 중복되는 ID 입니다.');   
    END IF;
exception
when no_data_found then
        INSERT INTO MEMBER(ID,NICKNAME,PASSWORD,NAME,SSN,TEL,EMAIL,MGRADE,MPOINT) 
        VALUES(PID,PNICKNAME,PPASSWORD,PNAME,PSSN,PTEL,PEMAIL,'일반',0);
        DBMS_OUTPUT.PUT_LINE('회원가입이 완료되었습니다.');
when others then
null;
END;



-- MY CGV 마이페이지

CREATE OR REPLACE PROCEDURE MY_PAGE(
     pID MEMBER.ID%type
)
IS
    vgrade MEMBER.MGRADE%type;
    vcoupon number(3);
    vpoint MEMBER.MPOINT%type;
    voftencgv OFTEN_CGV.CGV_NAME%type;   
BEGIN
    select MGRADE into vgrade from member where id = pid;
    select count(*) into vcoupon from coupon   where id = pid;
    select mpoint into vpoint from member where id = pid;
    
    dbms_output.put_line(pID || '님');   
    dbms_output.put_line('고객님은' || vgrade || '입니다');
    dbms_output.put_line('==============================================');    
    dbms_output.put('CGV 할인쿠폰' || vcoupon || '개' ||  '    |    ');
    dbms_output.put_line('CJ ONE 사용가능 포인트' || vpoint || '원'  );
    dbms_output.put_line('----------------------------------------------');     
    dbms_output.put_line('자주가는 CGV');
    FOR vrow IN (
    select CGV_NAME from OFTEN_CGV where id = pid
     )
    LOOP
    dbms_output.put_line('[' || vrow.CGV_NAME || ']' );
    END LOOP;
END;

EXEC MY_PAGE('AAA123 ');


-- 시퀀스-
CREATE SEQUENCE POINT_NUM
    INCREMENT BY 1
    START WITH 11
    NOCYCLE
    NOCACHE;

-- 회원 결제(포인트)
CREATE OR REPLACE PROCEDURE UP_PAY_POINT (
    pticketing_code ticketing.ticketing_code%type ,
    pid member.id%type  ,
    ppoint member.mpoint%type
)
IS
    TYPE v_pay IS RECORD(
        nomal number := 0,
        child number := 0,
        special number := 0,
        senior number := 0,
        mpoint number ,
        id member.id%type
    );
    vrow v_pay;
    vprice number;
    vsum number := 0;
    vsaleprice number;
    vid number(1) := 0;
    epoint number(10);
    cpoint number(10);
BEGIN
    SELECT COUNT(*) INTO vid
    FROM member
    WHERE id = pid;
    
    IF vid != 1
        THEN DBMS_OUTPUT.PUT_LINE('존재하지 않는 ID입니다.');
    ELSE
        SELECT nomal, child, special, senior, mpoint, m.id
            INTO vrow.nomal, vrow.child, vrow.special, vrow.senior, vrow.mpoint, vrow.id
        FROM member_pay m JOIN ticketing t ON m.ticketing_code = t.ticketing_code
                          JOIN people p ON m.ticketing_code = p.ticketing_code
                          JOIN member e ON m.id = e.id
        WHERE m.ticketing_code = pticketing_code;
    
        SELECT nvl(mpoint, 0) INTO epoint
        FROM member
        WHERE id = pid;
        
        IF (epoint - ppoint) < 0
            THEN DBMS_OUTPUT.PUT_LINE('보유한 포인트보다 많습니다. 다시 결제해주세요.');
        ELSE
            UPDATE member
            SET mpoint = epoint - ppoint
            WHERE id = pid;
            
            UPDATE member
            SET mpoint = mpoint + 1000
            WHERE id = pid;
            
            INSERT INTO point VALUES(point_num.NEXTVAL, pid, '영화 결제 포인트 적립', 1000, sysdate);
            COMMIT;
            
            DBMS_OUTPUT.PUT_LINE('===== 포인트결제 =====');
            DBMS_OUTPUT.PUT_LINE('일반 : ' || nvl(vrow.nomal, 0));
            DBMS_OUTPUT.PUT_LINE('청소년 : ' || nvl(vrow.child, 0));
            DBMS_OUTPUT.PUT_LINE('노인 : ' || nvl(vrow.special, 0));
            DBMS_OUTPUT.PUT_LINE('우대 : ' || nvl(vrow.senior, 0));
            DBMS_OUTPUT.PUT_LINE('====================');
            vprice := nvl(vrow.nomal, 0) * 13000 + nvl(vrow.child, 0) * 10000 + nvl(vrow.special, 0) * 6000 + nvl(vrow.senior, 0) * 5000;
            vsum := vsum + vprice;
    
            vsaleprice := vsum - ppoint;
            
            cpoint := epoint - ppoint;
            DBMS_OUTPUT.PUT_LINE('결제 ID = ' || pid);
            DBMS_OUTPUT.PUT_LINE('총 가격 = ' || vsum);
            DBMS_OUTPUT.PUT_LINE('사용할 포인트 = ' || ppoint);
            DBMS_OUTPUT.PUT_LINE('최종 결제금액 = ' || vsaleprice);
            DBMS_OUTPUT.PUT_LINE('보유 포인트 = ' || epoint);
            DBMS_OUTPUT.PUT_LINE('결제 후 남은 포인트 = ' || cpoint);
            DBMS_OUTPUT.PUT_LINE('========================');
            DBMS_OUTPUT.PUT_LINE('결제완료! 포인트 1000원 적립');
        END IF;
    END IF;
END;

UPDATE member SET mpoint = 2172 WHERE id = 'AAA123 ';
EXEC UP_PAY_POINT('TK1', 'AAA123 ', 1000);

SELECT * FROM member;
SELECT * FROM point;

UPDATE member SET mpoint = mpoint+1000 WHERE id = 'AAA123 ';

EXEC UP_PAY_POINT('TK319', 'AAA123 ', 1000);


 
 -- 회원결제(할인쿠폰)
CREATE OR REPLACE PROCEDURE UP_PAY_COUPON (
    pticketing_code ticketing.ticketing_code%type ,
    pid member.id%type  ,
    pcoupon coupon.coupon_code%type
)
IS
    TYPE v_pay IS RECORD(
        nomal number, 
        child number, 
        special number, 
        senior number, 
        coupon_code coupon.coupon_code%type , 
        coupon_price coupon.coupon_price%type , 
        id coupon.id%type ,
        cdate coupon.cdate%type ,
        cperiod coupon.cperiod%type ,
        cusedate coupon.cusedate%type
    );
    vrow v_pay;
    vprice number;
    vsum number := 0;
    vsaleprice number;
    vid number(1) := 0;
    vcoupon number;
BEGIN
    SELECT nomal, child, special, senior, c.coupon_code, coupon_price, c.id, cdate, cperiod, cusedate
        INTO vrow.nomal, vrow.child, vrow.special, vrow.senior, vrow.coupon_code, vrow.coupon_price, vrow.id, 
                                     vrow.cdate, vrow.cperiod, vrow.cusedate
    FROM member_pay e JOIN coupon c ON e.id = c.id
                      JOIN member m ON e.id = m.id
                      JOIN people p ON e.ticketing_code = p.ticketing_code
    WHERE e.ticketing_code = pticketing_code;

    SELECT COUNT(*) INTO vid
    FROM member
    WHERE id = pid;
    
    IF vid != 1
        THEN DBMS_OUTPUT.PUT_LINE('존재하지 않는 ID입니다.');
    ELSE
        SELECT COUNT(*) INTO vid
        FROM coupon
        WHERE coupon_code = pcoupon;
        vid := vid + 1;
        
        IF vid = 1
            THEN DBMS_OUTPUT.PUT_LINE('없는 쿠폰번호입니다.');
        ELSE
            IF TRUNC(sysdate) - (TRUNC(vrow.cdate) + SUBSTR(vrow.cperiod, 1, 2)) >= 0
                THEN DBMS_OUTPUT.PUT_LINE('사용기간이 지난 쿠폰입니다.');
            ELSE
                IF vrow.cusedate IS NULL THEN
                    DBMS_OUTPUT.PUT_LINE('===== 쿠폰결제 =====');
                    DBMS_OUTPUT.PUT_LINE('일반 : ' || nvl(vrow.nomal, 0));
                    DBMS_OUTPUT.PUT_LINE('청소년 : ' || nvl(vrow.child, 0));
                    DBMS_OUTPUT.PUT_LINE('노인 : ' || nvl(vrow.special, 0));
                    DBMS_OUTPUT.PUT_LINE('우대 : ' || nvl(vrow.senior, 0));
                    DBMS_OUTPUT.PUT_LINE('===================');
                    vprice := nvl(vrow.nomal, 0) * 13000 + nvl(vrow.child, 0) * 10000 
                              + nvl(vrow.special, 0) * 6000 + nvl(vrow.senior, 0) * 5000;
                    vsum := vsum + vprice;
                    
                    UPDATE coupon
                    SET CUSEDATE = sysdate
                    WHERE id = pid;
                    
                    UPDATE member
                    SET mpoint = mpoint + 1000
                    WHERE id = pid;
                    
                    INSERT INTO point VALUES(point_num.NEXTVAL, pid, '영화 결제 포인트 적립', 1000, sysdate);
                    COMMIT;
                    
                    vcoupon := vrow.coupon_price;
                    vsaleprice := vsum - vcoupon;
                    DBMS_OUTPUT.PUT_LINE('총 가격 = ' || vsum);
                    DBMS_OUTPUT.PUT_LINE('쿠폰할인 가격 = ' || vcoupon);
                    DBMS_OUTPUT.PUT_LINE('최종 결제금액 = ' || vsaleprice);
                    DBMS_OUTPUT.PUT_LINE('========================');
                    DBMS_OUTPUT.PUT_LINE('결제완료! 포인트 1000원 적립');
                ELSE
                    DBMS_OUTPUT.PUT_LINE('이미 사용한 쿠폰입니다.');
                END IF;
            END IF;
        END IF;
    END IF;
EXCEPTION
    WHEN no_data_found THEN DBMS_OUTPUT.PUT_LINE('없는 쿠폰번호입니다.');
END;



EXEC UP_PAY_COUPON('TK319', 'DBDBDEEP', 432);


SELECT * FROM member_pay;
SELECT * FROM coupon;
insert into member_pay (pay_code, ticketing_code, price, payway, pay_date, id)
values ('11', 'TK319', 36000, '신용카드', '2021-05-16', 'AAA123 ')



-- 회원 일반 결제
CREATE OR REPLACE PROCEDURE UP_PAY (
    pticketing_code ticketing.ticketing_code%type ,
    pid member.id%type
)
IS
    TYPE v_pay IS RECORD(
        nomal number := 0,
        child number := 0,
        special number := 0,
        senior number := 0,
        id member.id%type
    );
    vrow v_pay;
    vprice number;
    vsum number := 0;
    vid number(1) := 0;
BEGIN
    SELECT COUNT(*) INTO vid
    FROM member
    WHERE id = pid;
    
    IF vid != 1
        THEN DBMS_OUTPUT.PUT_LINE('존재하지 않는 ID입니다.');
    ELSE
        SELECT nomal, child, special, senior, m.id
            INTO vrow.nomal, vrow.child, vrow.special, vrow.senior,  vrow.id
        FROM member_pay m JOIN ticketing t ON m.ticketing_code = t.ticketing_code
                          JOIN people p ON m.ticketing_code = p.ticketing_code
                          JOIN member e ON m.id = e.id
        WHERE m.ticketing_code = pticketing_code;

            UPDATE member
            SET mpoint = mpoint + 1000
            WHERE id = pid;
            
            INSERT INTO point VALUES(point_num.NEXTVAL, pid, '영화 결제 포인트 적립', 1000, sysdate);
            COMMIT;
            
            DBMS_OUTPUT.PUT_LINE('===== 일반결제 =====');
            DBMS_OUTPUT.PUT_LINE('일반 : ' || nvl(vrow.nomal, 0));
            DBMS_OUTPUT.PUT_LINE('청소년 : ' || nvl(vrow.child, 0));
            DBMS_OUTPUT.PUT_LINE('노인 : ' || nvl(vrow.special, 0));
            DBMS_OUTPUT.PUT_LINE('우대 : ' || nvl(vrow.senior, 0));
            DBMS_OUTPUT.PUT_LINE('==================');
            vprice := nvl(vrow.nomal, 0) * 13000 + nvl(vrow.child, 0) * 10000 + nvl(vrow.special, 0) * 6000 + nvl(vrow.senior, 0) * 5000;
            vsum := vsum + vprice;
            
            DBMS_OUTPUT.PUT_LINE('결제 ID = ' || pid);
            DBMS_OUTPUT.PUT_LINE('총 가격 = ' || vsum);
            DBMS_OUTPUT.PUT_LINE('최종 결제금액 = ' || vsum);
            DBMS_OUTPUT.PUT_LINE('========================');
            DBMS_OUTPUT.PUT_LINE('결제완료! 포인트 1000원 적립');
    END IF;
END;

EXEC UP_PAY('TK319', 'DBDBDEEP');
SELECT * FROM member;
SELECT * FROM point;



-- 비회원 결제
CREATE OR REPLACE PROCEDURE UP_GUEST_PAY (
    pticketing_code ticketing.ticketing_code%type
)
IS
    TYPE v_pay IS RECORD(
        nomal number := 0,
        child number := 0,
        special number := 0,
        senior number := 0
    );
    vrow v_pay;
    vprice number;
    vsum number := 0;
BEGIN
    SELECT nomal, child, special, senior
        INTO vrow.nomal, vrow.child, vrow.special, vrow.senior
    FROM guest_pay g JOIN ticketing t ON g.ticketing_code = t.ticketing_code
                     JOIN people p ON g.ticketing_code = p.ticketing_code
    WHERE g.ticketing_code = pticketing_code;

    DBMS_OUTPUT.PUT_LINE('===== 비회원 결제 =====');
    DBMS_OUTPUT.PUT_LINE('일반 : ' || nvl(vrow.nomal, 0));
    DBMS_OUTPUT.PUT_LINE('청소년 : ' || nvl(vrow.child, 0));
    DBMS_OUTPUT.PUT_LINE('노인 : ' || nvl(vrow.special, 0));
    DBMS_OUTPUT.PUT_LINE('우대 : ' || nvl(vrow.senior, 0));
    DBMS_OUTPUT.PUT_LINE('=====================');
    vprice := nvl(vrow.nomal, 0) * 13000 + nvl(vrow.child, 0) * 10000 + nvl(vrow.special, 0) * 6000 + nvl(vrow.senior, 0) * 5000;
    vsum := vsum + vprice;

    DBMS_OUTPUT.PUT_LINE('총 가격 = ' || vsum);
END;

EXEC UP_GUEST_PAY('TK319');

SELECT * FROM ticketing;
SELECT * FROM guest_pay WHERE ticketing_code = 'TK11';





/* 게시판*/
CREATE OR REPLACE PROCEDURE UP_MGRADE
(
    ppage in number
)
IS
    vrow m_grade%rowtype;
BEGIN
    for vrow in
    (
        select substr(grade_code,6,9) num, id, review, review_reg, mlike
        from m_grade
        where floor(substr(grade_code,6,9)/7)+1 = ppage
    )
    LOOP
        dbms_output.put_line(vrow.num || vrow.id || ' ' || vrow.review || ' ' || vrow.review_reg || ' ' || vrow.mlike);
    END LOOP;
END;
exec UP_MGRADE(2);


/* 공지사항 */
CREATE OR REPLACE PROCEDURE UP_NOTICE
(
    ppage in number
)
IS
    vrow notice%rowtype;
BEGIN
    for vrow in
    (
        select *
        from notice
        where floor(notice_code/20)+1 = ppage
    )
    LOOP
        dbms_output.put_line(vrow.notice_code || ' ' || RPAD(vrow.NSUBJECT,10,' ')  || vrow.NTITLE || ' ' 
                            || vrow.NDATE || ' ' || vrow.NVIEW);
    END LOOP;
END;
exec UP_NOTICE(1);

/* 회원가입*/
CREATE OR REPLACE PROCEDURE CGV_UP_SIGN_CHECK
(
 PNAME MEMBER.NAME%TYPE
 , PBIRTH_DATE MEMBER.SSN%TYPE
 , PTEL MEMBER.TEL%TYPE
)
IS
    VCHECK NUMBER(1);
    VNAME MEMBER.NAME%TYPE;
BEGIN
    SELECT COUNT(NAME) INTO VCHECK
    FROM MEMBER
    WHERE NAME = PNAME AND SUBSTR(SSN,0,6) = PBIRTH_DATE AND SUBSTR(TEL,5,14) = PTEL;
    
    SELECT NAME INTO VNAME
    FROM MEMBER
    WHERE NAME = PNAME;
    
    IF VCHECK = 1 THEN
           DBMS_OUTPUT.PUT_LINE( VNAME || '님! 이미 CGV 회원으로 등록되어 있습니다.');            
       
    END IF;
 
exception
when no_data_found then
    CASE
    WHEN PNAME IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('이름을 입력하세요.');
    WHEN PBIRTH_DATE IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('법정생년월일을 정확히 입력해 주세요.');
    WHEN PTEL IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('국번제외한 휴대폰번호를 입력해주세요. (ex.010-123-5678 > 123-5678)');   
    ELSE             
        DBMS_OUTPUT.PUT_LINE('회원가입 창으로 이동합니다.');
    END CASE;    
when others then
null;
END;


/* 비회원예매내역확인 */
CREATE OR REPLACE PROCEDURE CGV_UP_GUEST_LOGON
(
    P_GUEST_BIRTH GUEST.BIRTH_DATE%TYPE
    ,P_GUEST_TEL GUEST.TEL%TYPE
    ,P_GUEST_PASSWORD GUEST.PASSWORD%TYPE
)
IS
    VISGUEST NUMBER(1);
BEGIN
    SELECT COUNT(*) INTO VISGUEST 
    FROM GUEST
    WHERE BIRTH_DATE = P_GUEST_BIRTH AND TEL = P_GUEST_TEL AND PASSWORD = P_GUEST_PASSWORD;
    
    CASE
    WHEN P_GUEST_BIRTH IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('법정생년월일(8자리)을 입력해주세요.');
    WHEN P_GUEST_TEL IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('휴대폰 번호를 입력해주세요.');  
    WHEN P_GUEST_PASSWORD IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('비밀번호(4자리)를 입력해주세요.');
    ELSE
         IF VISGUEST = 1 THEN
            DBMS_OUTPUT.PUT_LINE('예매 내역 확인');
         ELSE
            DBMS_OUTPUT.PUT_LINE('입력하신 정보와 일치하는 예매내역이 없습니다.'); 
        END IF;
    END CASE;
END;

-- 예매취소
CREATE OR REPLACE PROCEDURE UP_PAYCANCEL(
    pticketing_code ticketing.ticketing_code%type
)
IS
    vm_check number := 0;
    vpay_code member_pay.pay_code%type;
    vguest_code guest_pay.guestpay_code%type;
BEGIN
    
    SELECT COUNT(*)
        INTO vm_check
    FROM member_pay
    WHERE ticketing_code = pticketing_code;
    IF vm_check = 1
        THEN 
            SELECT pay_code
                INTO vpay_code
            FROM member_pay
            WHERE ticketing_code = pticketing_code;
            INSERT INTO PAY_CANCEL VALUES (cancel_num.NEXTVAL, vpay_code, NULL, pticketing_code, sysdate);
            DBMS_OUTPUT.PUT_LINE('취소가 완료되었습니다.');
            COMMIT;
    ELSE
        SELECT guestpay_code
            INTO vpay_code
        FROM guest_pay
        WHERE ticketing_code = pticketing_code;
        INSERT INTO PAY_CANCEL VALUES (cancel_num.NEXTVAL, NULL, vpay_code, pticketing_code, sysdate);
        DBMS_OUTPUT.PUT_LINE('취소가 완료되었습니다.');
        COMMIT;
    END IF;
END;

SELECT * FROM member_pay;
DELETE FROM pay_cancel;
SELECT * FROM guest_pay;

DELETE FROM pay_cancel WHERE cancel_seq = 2;
EXEC UP_PAY('TK4', 'EEE123');
EXEC UP_GUEST_PAY('TK14');

EXEC UP_PAYCANCEL('TK4');
EXEC UP_PAYCANCEL('TK14');


/* 예매취소 출력 */
CREATE OR REPLACE PROCEDURE UP_GUEST_PAY (
    pticketing_code ticketing.ticketing_code%type
)
IS
    TYPE v_pay IS RECORD(
        nomal number := 0,
        child number := 0,
        special number := 0,
        senior number := 0
    );
    vrow v_pay;
    vprice number;
    vsum number := 0;
BEGIN
    SELECT nomal, child, special, senior
        INTO vrow.nomal, vrow.child, vrow.special, vrow.senior
    FROM guest_pay g JOIN ticketing t ON g.ticketing_code = t.ticketing_code
                     JOIN people p ON g.ticketing_code = p.ticketing_code
    WHERE g.ticketing_code = pticketing_code;

    DBMS_OUTPUT.PUT_LINE('===== 비회원 결제 =====');
    DBMS_OUTPUT.PUT_LINE('일반 : ' || nvl(vrow.nomal, 0));
    DBMS_OUTPUT.PUT_LINE('청소년 : ' || nvl(vrow.child, 0));
    DBMS_OUTPUT.PUT_LINE('노인 : ' || nvl(vrow.special, 0));
    DBMS_OUTPUT.PUT_LINE('우대 : ' || nvl(vrow.senior, 0));
    DBMS_OUTPUT.PUT_LINE('=====================');
    vprice := nvl(vrow.nomal, 0) * 13000 + nvl(vrow.child, 0) * 10000 + nvl(vrow.special, 0) * 6000 + nvl(vrow.senior, 0) * 5000;
    vsum := vsum + vprice;

    DBMS_OUTPUT.PUT_LINE('총 가격 = ' || vsum);
    
    UPDATE guest_pay
    SET nprice = vsum
    WHERE ticketing_code = pticketing_code;
    
END;

EXEC UP_GUEST_PAY('TK11');

SELECT * FROM ticketing;
SELECT * FROM guest_pay WHERE ticketing_code = 'TK11';


-- 성별 선호도 쿼리

select title,decode(mod(substr(ssn,8,1),2),1,'남자','여자') 남녀
        , count(mod(substr(ssn,8,1),2)) / 
        ( select count(*)
          from MEMBER_PAY mp join Ticketing t on mp.ticketing_code = t.ticketing_code
          where movie_code = 'MV3') * 100 || '%' 퍼센트
from MEMBER_PAY mp join Ticketing t on mp.ticketing_code = t.ticketing_code
        join member m on mp.id = m.id
        join movie mv on t.movie_code = mv.movie_code
where mv.movie_code = 'MV3'
group by title, mod(substr(ssn,8,1),2) ;


-- 나이대별 선호도 쿼리


select t.*,count(age)/
        ( select count(*)
          from MEMBER_PAY mp join Ticketing t on mp.ticketing_code = t.ticketing_code
          where movie_code = 'MV3') * 100 || '%' 퍼센트
from
(
select t.title,
  case
  when 나이 between 10 and 19 then '10대'
  when 나이 between 20 and 29 then '20대'
  when 나이 between 30 and 39 then '30대'
  when 나이 between 40 and 49 then '40대'
  else '50대'
end age
from
(
select title,
case
when substr(ssn,0,2) > 40 then (to_char(sysdate,'yyyy') - (substr(ssn,0,2)+1900) +1) 
else to_char(sysdate,'yyyy') - (substr(ssn,0,2)+2000) +1
end 나이
from MEMBER_PAY mp join Ticketing t on mp.ticketing_code = t.ticketing_code
        join member m on mp.id = m.id
        join movie mv on t.movie_code = mv.movie_code
        where mv.movie_code = 'MV3'
        group by title,substr(ssn,0,2)
) t
) t
group by t.title,t.age;


CREATE OR REPLACE PROCEDURE CGV_UP_SIGN_CHECK
(
 PNAME MEMBER.NAME%TYPE
 , PBIRTH_DATE MEMBER.SSN%TYPE
 , PTEL MEMBER.TEL%TYPE
)
IS
    VCHECK NUMBER(1);
    VNAME MEMBER.NAME%TYPE;
BEGIN
    SELECT COUNT(NAME) INTO VCHECK
    FROM MEMBER
    WHERE NAME = PNAME AND SUBSTR(SSN,0,6) = PBIRTH_DATE AND SUBSTR(TEL,5,14) = PTEL;
    
    SELECT NAME INTO VNAME
    FROM MEMBER
    WHERE NAME = PNAME;
    
    IF VCHECK = 1 THEN
           DBMS_OUTPUT.PUT_LINE( VNAME || '님! 이미 CGV 회원으로 등록되어 있습니다.');            
       
    END IF;
 
exception
when no_data_found then
    CASE
    WHEN PNAME IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('이름을 입력하세요.');
    WHEN PBIRTH_DATE IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('법정생년월일을 정확히 입력해 주세요.');
    WHEN PTEL IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('국번제외한 휴대폰번호를 입력해주세요. (ex.010-123-5678 > 123-5678)');   
    ELSE             
        DBMS_OUTPUT.PUT_LINE('회원가입 창으로 이동합니다.');
    END CASE;    
when others then
null;
END;

0개의 댓글