'oracle procedure'에 해당되는 글 3건





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



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 항상 겸손하자.

출처 - http://www.gurubee.net/lecture/1041


프로시저(Procedure)란?

  특정 작업을 수행 하는, 이름이 있는 PL/SQL BLOCK 이다.

  매개 변수를 받을 수 있고, 반복적으로 사용 할 수 있는 BLOCK 이다.

  보통 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는 PL/SQL BLOCK을 데이터베이스에 저장하기 위해 생성 한다.

프로시저 문법

 
CREATE OR REPLACE procedure name 
   IN argument 
   OUT argument 
   IN OUT argument 

IS 

   [변수의 선언]

BEGIN  --> 필수 

   [PL/SQL Block] 
   -- SQL문장, PL/SQL제어 문장 

   [EXCEPTION]  --> 선택
  -- error가 발생할 때 수행하는 문장

END;  --> 필수 
    
  • CREATE OR REPLACE 구문을 사용하여 생성 한다.
  • IS 로 PL/SQL의 블록을 시작 한다.
  • LOCAL 변수는 IS 와 BEGIN 사이에 선언 한다.

프로시저 작성 예제

-- 프로시저의 이름은 update_sal이다 
-- update_sal 프로시저는 사번을 입력받아 급여를 인상 한다. 
-- 프로시저를 끝마칠 때에는 항상 "/"를 지정 한다.
SQL> CREATE OR REPLACE PROCEDURE update_sal 
     /* IN  Parameter */
     (v_empno    IN    NUMBER) 
         
     IS 

     BEGIN 

       UPDATE emp 
       SET sal = sal  * 1.1 
       WHERE empno = v_empno; 

       COMMIT; 

     END update_sal; 
     /     
    

프로시저 실행 예제

EXECUTE 문을 이용해 프로시저를 실행 한다.

SQL> EXECUTE update_sal(7369);
PL/SQL 처리가 정상적으로 완료되었습니다.

-- 7369번 사원의 급여가 10% 인상 되었는지 확인해 보기 바란다.
    


블로그 이미지

종환 Revolutionist-JongHwan

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