2023/11/24 ~ 2023/11/26 ✌️ 프로시저 & 커서의 굴레

 

 

 

❤ 김춘장이의 위키백과 - 나만의 공부 기록 Tistory ❤ 

GitHub - gayulz/StudyHistory: 나의 공부 기록 commit

 

 

 

---

 

 

 

시작에 앞서 현재 하고 있는 프로젝트의 앞 단계 포스팅이 보고 싶으시다면 클릭해 주세요

-- 1. 프로젝트 분석과 설계 시작 --

2023/11/20 👌 JAVA 프로젝트 시작

-- 2. DB설계와 기능 구현 --

2023/11/21 ~ 2023/11/23☝️ JAVA 프로젝트 - DB설계 & DB 기술 적용

 

 

 

 

시작 전 이야기 

공부하기 전, 이야기

글을 작성하기 앞서 , 간단한 담소(?)를 하고자 한다.

사실 학부에서 오라클과 mssql을 해보았지만

그때에도 커서는 다루지 않았다.

'커서'라는 놈이 있는지도 지금까지 몰랐다. 🥲

DB관련 업무를 하는 내 남자친구는 나에게 '커서'란 존재를 알려준 적도 없었다.

커서란 것을 어떻게 알게 되었는가? 하면 이번 프로젝트에 프로시저를 도입하면서

데이터를 java로 어떻게 이동을 시키지?라는 궁금증과 함께 시작되어

프로시저를 더 공부하면서 알게 되었다.

그전까지 공부했던 프로시저는 그냥 겉핥기가 분명했다.

지금까지 커서를 몰랐다니 말이다.

학원 선생님도 면담을 할 때 놀래셨다.

어떻게 커서라는 애를 알게 되었냐며 말이다.

현업에선 알아야 하는 부분 중 한 가지이지만,

프로시저나 커서 까지 들어가게 되면 프로그래밍적인 부분이라 시간이 없고 본인 스스로가

찾아서 공부하길 바라셨던 것 같았다.

학원 선생님이 나에게 가장 염려스러워하던 부분 중 한 가지가

너무 일을 크게 벌이려는 경향이 있다는 점을 걱정하셨다

'처음에 조인문이 너무 길어 뷰를 보다 , 적합하지 않아서 프로시저를 파다 보니 커서가 있었다'라는 발언에

일을 너무 크게 벌리고 있는 게 아닌가 걱정된다 하셨다.

하지만 왜 커서까지 가게 되었는지 순차적으로 다시 설명드리고 나니

다행이라고 하셨다, 그런 이유가 있다면 다행히 공부를 잘하고 있는 부분이니 열심히 해보라고 하셨다.

힘을 얻고 다시 공부를 해본다

우선 프로시저란 놈을 더 설명해보고자 한다

 

 

 

 

 

 

 

프로시저

 

Procedure 은 PL/SQL에서 저장된 서버 측 프로그램이다

주로 특정 작업을 수행하거나, 일련의 SQL문을 실행하는 데 사용된다

간단히 생각해 보면 JAVA의 메서드 같은 느낌이라고 보면 된다

내가 어떤 인자를 받았을 때

어떤 기능을 할 수 있고 반환값을 설정하는 그런 기능을 말이다.

 

 

기본 정의 문법

CREATE [OR REPLACE] PROCEDURE 프로시저명(매개변수1 데이터타입, 매개변수2 데이터타입, ...)
IS
   -- 선언부: 변수, 상수 등을 선언할 수 있음
BEGIN
   -- 실행부: 실제로 수행할 코드를 기술
   -- (SQL 문, 제어문, 반복문, 예외 처리 등)
END 프로시저명;

CREATE PROCEDURE: 프로시저를 생성하는 키워드이다
OR REPLACE: 이미 동일한 이름의 프로시저가 존재하는 경우 덮어쓰기 옵션
프로시저명: 프로시저의 이름을 지정
매개변수: 필요에 따라 프로시저에 전달할 수 있는 입력 매개변수
IS: 선언부와 실행부를 구분하는 예약어
BEGIN...END: 프로시저의 실행 부분을 정의

 

활용법

1. 데이터 조작 : 프로시저는 데이터를 조회, 삽입, 수정, 삭제 등의 데이터 조작 작업을 수행할 수 있다

2. 로직의 모듈화 : 반복되는 로직을 프로시저로 정의하여 코드의 재사용성을 높일 수 있다

3. 트랜잭션 관리 : 여러 SQL문을 하나의 트랜잭션으로 묵어 처리할 수 있다

4. 예외 처리 : 예외가 발생할 경우 처리할 코드를 프로시저 안에 정의하여 안정성을 높일 수 있다

5. 보안 강화 : 특정 기능을 수행하는 프로시저에 대한 권한을 부여하여 보안을 강화할 수 있다.

 

 

사용법

프로시저 호출 방법

프로시저를 호출하기 위해서는 EXECUTE 또는 CALL 문을 사용합니다 

EXECUTE 프로시저명(매개변수1, 매개변수2, ...);
-- 또는
CALL 프로시저명(매개변수1, 매개변수2, ...);

매개변수 전달 : 프로시저가 매개변수를 가질 경우, 호출 시에 해당 매개변수에 값을 전달해야 합니다
결과 확인 : DBMS_OUTPUT.PUT_LINE을 이용하여 프로시저 내에서 중간 결과 확인 가능하다
예외 처리 : 프로시저가 예외를 처리하는 경우, 호출자는 EXCEPTION 섹션에서 예외를 처리한다

 

 

간단한 예제

 

CREATE OR REPLACE PROCEDURE GreetPerson(Name IN VARCHAR2)
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE('안녕하세요, ' || Name || '님!');
END GreetPerson;

이름을 입력 받아 인사말을 출력하는 기능의 프로시저

호출 방법

EXECUTE GreetPerson('홍길동');
-- 또는
CALL GreetPerson('홍길동');


이렇게 실행하면 안녕하세요 , 홍길동님 ! 이라는 메세지가 출력된다

 

이제 프로시저에 대해 기본적인 공부를 했다

이제 커서에 대해서 한번 알아보자

렛츠 기릿 

 

 

 

 

 

 

CUSOR 

 

 

내가 아는 커서의 의미

 

 

 

공부하기 전 나는 커서라고 하면 이런 느낌이었다

이게 아니면 C 언어의 Pointer 같은 느낌이랄까 , 딱 거기 까지였다.

이번에 공부하고 난 뒤 

커서에 대한 생각이 바뀌었다

ResultSet 같은 느낌으로 바뀌었는데 , 조금 내가 공부하는 게 바뀌고 있다는 의미겠지?

 

 

 

그래서 커서라는 게 뭐냐?

데이터베이스 쿼리의 결과 집합을 담는 장치라고 한다.

반복적으로 행을 처리하거나 조작할 때 사용하며

일반적으로 PL/SQL 블록 내에서 커서를 선언하고 열고(fetch) 닫는 단계를 거쳐

데이터베이스 결과를 처리한다.

 

 

나는 왜 커서가 필요했을까?

 

처음에 여러 조인을 사용하여 조건을 충족한 데이터들을 가져오는 게 목적이었다.

그러다 보면 java에 쿼리를 매핑할 때 너무 코드가 길었다.

긴 코드가 싫다 보니 간단히 쿼리를 전송하고자 하는 목표로 view를 생각했으나 적합하지 않았다.

그래서 프로시저를 만들었는데 

아뿔싸, 이 코드는 내가 DB에서 출력만 할 줄 알지 java로 가져올 수 없었다.

그래서 고민을 했고 책을 뒤져봤는데 커서라는 걸 알게 되었고

이 커서로 내가 java로 데이터를 가져올 수 있다는 걸 알게 되었다.

 

그렇다 

이 커서란 놈은 이래서 필요했다

 

간단한 커서의 예시이다

DECLARE
   -- 커서 선언
   CURSOR my_cursor IS
      SELECT column1, column2
      FROM your_table;

   -- 변수 선언
   v_column1 테이블명.column1%TYPE;
   v_column2 테이블명.column2%TYPE;

BEGIN
   -- 커서 열기
   OPEN my_cursor;

   -- 반복문을 통한 행 처리
   LOOP
      -- 커서로부터 데이터 가져오기
      FETCH my_cursor INTO v_column1, v_column2;

      -- 더 이상 행이 없으면 반복문 종료
      EXIT WHEN my_cursor%NOTFOUND;

      -- 가져온 데이터를 이용한 로직 수행
      -- 예: DBMS_OUTPUT.PUT_LINE(v_column1 || ', ' || v_column2);
   END LOOP;

   -- 커서 닫기
   CLOSE my_cursor;
END;

 

 

커서는 항상 열게 되면 닫아줘야 된다

java의 io관련 리소스를 닫는 것과 비슷하게 생각해 주면 된다.

자원을 썼으면 닫아줘야 된다

 

그리고 커서를 닫기가 귀찮다면

FOR 문을 같이 써주면 닫지 않아도 자동으로 닫히게 된다.

 

PL/SQL에 변수는 IN, OUT 변수를 설정할 수 있는데

커서를 이용하기 위해  OUTPUT.PUT.LINE을 써야 되는 건 아니다 

출력을 하지 않고 결과 데이터를 CURSOR를 통해 JAVA로 가져온다고 생각하면 된다

 

 

 

 

 

 

 

내가 만든 쿼리

 

지금까지 필요한 내용으로 적은 나의 소스쿼리이다.

보완하면 좋은 사항이 있다면 댓글부탁드립니다

 

 

 

-- 원하는 업종/지역/체크인 체크아웃 날자를 설정시 가능한 방을 찾아서 리턴해주는 프로시저

CREATE OR REPLACE PROCEDURE Search_room_proc (
p_lodgingType VARCHAR2,
p_lodgingAddr VARCHAR2,
p_checkIn DATE,
p_checkOut DATE,
p_results OUT SYS_REFCURSOR
) AS
BEGIN
OPEN p_results FOR
SELECT l.lodgingName, ri.*
FROM RoomInfo ri
JOIN Lodging l ON ri.lodgingUniqueNumber = l.lodgingUniqueNumber
WHERE ri.lodgingType = p_lodgingType
AND ri.lodgingAddr LIKE '%' || p_lodgingAddr || '%'
AND NOT EXISTS (
SELECT 1
FROM BookingList bl
WHERE bl.roomNumber = ri.roomNumber
AND (p_checkIn BETWEEN bl.checkIN AND bl.checkOut
OR p_checkOut BETWEEN bl.checkIN AND bl.checkOut))
ORDER BY roomNumber ASC;
END Search_room_proc;







-- 고객 예약 완료시 BookingList 와 Customer 테이블에 자동으로 insert 하는 프로시저 
CREATE OR REPLACE PROCEDURE Insert_customer_proc(
p_roomNumber RoomInfo.roomNumber%TYPE,
p_customerName VARCHAR2,
p_customerPhone VARCHAR2,
p_password VARCHAR2,
p_paymentInfo VARCHAR2,
p_checkIn DATE,
p_checkOut DATE)
AS
-- 변수선언
vn_bookingNumber NUMBER;
vn_reservationDay NUMBER;
vn_priceTotal NUMBER;
vn_oneDayPrice NUMBER;
BEGIN
-- 예약을 받는 내용으로 매개변수를 받으면 먼저 customer 테이블에 데이터 입력 
INSERT INTO Customer VALUES(booking_auto_sq.NEXTVAL,p_customerName,p_customerPhone,p_password,p_paymentInfo,p_checkIn,p_checkOut);
-- 입력받은 방번호기준으로 1일치의 숙박료를 변수에 대입 
SELECT roomPrice INTO vn_oneDayPrice FROM RoomInfo WHERE roomNumber = p_roomNumber;
-- 체크인과 체크아웃 기간차를 둔 숙박일수 계산 후 대입 
vn_reservationDay := p_checkOut-p_checkIn;
-- 해당 방번호의 금액을 곱한 총 금액계산 후 대입 
vn_priceTotal := vn_reservationDay*vn_oneDayPrice;
-- 시퀀스로 생성된 bookingNumber 찾은 뒤 변수에 대입
SELECT bookingNumber INTO vn_bookingNumber FROM Customer WHERE customerName = p_customerName AND customerPhone = p_customerPhone;
-- BookingList 테이블에 데이터 삽입 
INSERT INTO BookingList VALUES (p_roomNumber, vn_bookingNumber, p_checkIn, p_checkOut, vn_reservationDay, vn_priceTotal);
END Insert_customer_proc;







-- 입력된 회원정보를 가지고 예약된 리스트를 찾아서 리턴 
CREATE OR REPLACE PROCEDURE reservation_info_proc(
p_customerName VARCHAR2,
p_customerPhone VARCHAR2,
p_password VARCHAR2,
p_results OUT SYS_REFCURSOR
)AS
-- 변수 선언 및 초기화 
vs_bookingNumber NUMBER := 0;
vn_count NUMBER := 0;
BEGIN
OPEN p_results FOR
SELECT lg.lodgingName, lg.lodgingTel, ri.lodgingAddr, bl.bookingNumber , ri.roomName, bl.priceTotal, bl.reservationDay, bl.checkIn, bl.checkOut
FROM RoomInfo ri, BookingList bl, Lodging lg
WHERE ri.roomNumber = bl.roomNumber
AND lg.lodgingUniqueNumber = ri.lodgingUniqueNumber
AND bl.bookingNumber = (SELECT bookingNumber
FROM Customer
WHERE customerName = p_customerName
AND password = p_password
AND customerPhone = p_customerPhone)
ORDER BY bl.checkIn ASC;

END reservation_info_proc;


-----------------------------------------------------------------------------------------------------