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

 

 

---

 

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

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

 

 

 

---

 

 

이전 글을 보고 싶다면 클릭해 주세요!

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

 

 

 

 

DB설계 및 테이블 생성

 

-- Lodging 테이블 생성
CREATE TABLE Lodging(
lodgingUniqueNumber VARCHAR2(100) PRIMARY KEY,
lodgingName VARCHAR2(100),
lodgingTel VARCHAR2(16)
);

-- RoomInfo 테이블 생성
CREATE TABLE RoomInfo (
roomNumber NUMBER PRIMARY KEY,
lodgingUniqueNumber VARCHAR2(30),
roomName VARCHAR2(100),
roomPrice NUMBER(10),
lodgingType VARCHAR2(10) CHECK (lodgingType IN ('호텔','펜션','캠핑')),
lodgingAddr VARCHAR2(200),
FOREIGN KEY (lodgingUniqueNumber) REFERENCES Lodging(lodgingUniqueNumber)
);

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

-- Customer 테이블 생성
CREATE TABLE Customer (
bookingNumber NUMBER PRIMARY KEY,
customerName VARCHAR2(100),
customerPhone VARCHAR2(15),
password VARCHAR2(20),
paymentInfo VARCHAR2(20) CHECK (paymentInfo IN ('카드','모바일페이','무통장입금')),
checkIn DATE,
checkOut DATE
);

-- Booking 테이블 생성
CREATE TABLE BookingList(
roomNumber NUMBER,
bookingNumber NUMBER,
checkIN DATE,
checkOut DATE,
reservationDay NUMBER,
priceTotal NUMBER(10),
FOREIGN KEY (roomNumber) REFERENCES RoomInfo(roomNumber),
FOREIGN KEY (bookingNumber) REFERENCES Customer(bookingNumber) ON DELETE CASCADE
);

 

 

 

테이블 생성 후 

roomNumber와 bookingNumber를 시퀀스 처리하기로 하였다

시퀀스는 별도의 테이블처럼 Create 문을 통해 생성하며 , 자동 증가 칼럼이다 

 

-----------------------------------
SEQUENCE 문법
-----------------------------------
CREATE SEQUENCE [스미카명].[시퀀스명]
/* 옵션 */
INCREMENT BY --  증가값 설정 
START WITH -- 시작값 설정, MINVALUE와 같거나 커야함 
MINVALUE -- 최솟값 설정
MAXVALUE -- 최댓값 설정
NOCYCLE || CYCLE -- 최댓값 도달 시 시작 값 부터 다시 반복 여부 , 최솟 값 부터 다시 시작 
NOCACHE || CACHE -- CACHE 사용 여부, 캐시를 사용하여 미리 값을 할당한다, 속도가 빠르며 동시 사용자가 많을 경우 유리하다 
NOORDER || ORDER -- 요청 순서대로 값 생성 여부 , 발생 순서를 보장하지만 조금의 시스템 부화가 있을 수 있음

** CACHE를 사용할 경우 CACHE 또는 CACHE 값으로 지정할 수 있다.
값을 지정하지 않으면 기본값은 20이고, 지정 한 만큼 시퀀스를 미리 생성해 놓는다


-- Roominfo 테이블 roomNumber 시퀀스 생성
CREATE SEQUENCE roomPK_auto_sq
INCREMENT BY 1
START WITH 1
MAXVALUE 999
CYCLE;

Customer 테이블 bookingNumber 시퀀스 생성
CREATE SEQUENCE booking_auto_sq
INCREMENT BY 1
START WITH 1
MAXVALUE 999
CYCLE;

 

 

 

시퀀스 사용법

현재 내가 만들어 놓은 테이블을 기준으로 작성

INSERT INTO RoomInfo VALUES (roomPK_auto_sq.NEXTVAL, '값','값','값',,,,);

 

 

 

시퀀스 사용에 주의점

START WITH 외엔 모두 변경이 가능하다.

그리고 시퀀스는 실행할 때마다 값이 증가한다. 그리고 증가된 값을 다시 내릴 순 없다

( 해당 내용은 SELECT 문도 포함하게 된다)

 

 

그리고 시퀀스이름. CURRVAL 사용하면 현재 시퀀스의 순번을 가져올 수 있다

CURRVAL 은 여러 번 실행해도 순번은 증가하지 않고 현재 순번만 가져온다

CURRVAL은 NEXTVAL을 한번 실행한 세선에서만 사용 가능하다

 

 

 

 

 

 

PL/SQL

 

DB 설계를 하면서 필요한 기능이 있었다.

사용자가 원하는 업종과 지역, 그리고 체크인 체크아웃 날짜를 설정하게 되었을 때 예약 가능한 방을 찾아서 리턴해주는 것이 필요했다

이걸 View로 처리하자니 매번 요구하는 정보가 달라져서 고민이 되었다.

그리고 입력받는 데이터는 한정적이지만 반환해야 하는 데이터는 여러 가지였다

 

그러면서 프로시저를 다시 공부하게 되었다

처음 학원 수업 시작 시 데이터베이스에 대해서 공부하면서 

데이터베이스 수업이 끝날 때 프로시저를 한번 만들어봤었는데 

그때 이후론 잡아본 적이 없다 보니 당황스러웠다.

 

다시 처음부터 공부하자는 생각으로 책을 들여다봤다.

 

프로시저를 사용하기 전 알아야 하는 부분이 있었다.

바로 PL/SQL 문법이었다.

한차례 사용은 해봤으나 그땐 이해라기보다 이렇게 써야 된다라고 암기만 했던 터라 기억이 잘 나지 않았고 그러면서 다시 이해를 하기 위해 공부했다

 

 

PL/SQL 이란?

Procedural Langauge Structured Query Language의 약어로

오라클 데이터 베이스에서 사용되는 프로그래밍 언어이다 SQL문을 향상해 프로시저, 함수, 트리거 등을 작성하는 데 사용된다

 

 

변수 선언법

DECLARE
   변수명 데이터타입;
BEGIN
   -- 코드
END;

변수 값 할당

변수명 := 값 ;

오른쪽에 있는 값이 왼쪽에 있는 변수명에 대입된다
오라클은 대입연산자가 := 이다

 

 

스칼라 변수

SQL 자료형과 비슷하게 선언하는 것을 스칼라 변수라고 한다

자료형 : NUMBER , CHAR , VARCHAR2, DATE, TIMESTAMP

v_member VARCHAR2(10) := 'kims';

v_age NUMBER(2) = 10;

v_addr VARCHAR2(100) := '서울특별시 성동구';

 

 

레퍼런스 변수

DB테이블의 특정 칼럼으로 변수타입을 지정할 때 사용한다

선언법 : 변수이름 테이블이름. 칼럼이름 % TYPE;

테이블에서 칼럼의 자료형이 변경되더라도 PL/SQL에서의 변수 선언 부분을 수정할 필요가 없다는 장점이 있다.

 

/* 예시 */

customerName customer.name%TYPE;
customerPhone customer.phone%TYPE;
customerAge customer.age%TYPE;

 

 

해당 변수에 값을 할당하는 방법은

초기값을 설정하는 것 도 있지만. 사용자가 어떤 로직을 통해 값을 대입하고자 할 때도 있다.

그럴 때엔 INTO 절을 사용하면 되는데 보통 SELECT에 INTO를 포함시켜서 처리한다.

INTO절에는 SELECT문에서 조회한 데이터를 저장할 변수를 기술하는데 1:1로 일치시킨다.

하나의 행만 저장할 수 있고 결과는 메모리에 저장되어 사용할 수 있다.

 

vn_bookingNumber NUMBER;
vn_reservationDay NUMBER;
vn_priceTotal NUMBER;
vn_oneDayPrice NUMBER;


SELECT roomPrice INTO vn_oneDayPrice FROM RoomInfo WHERE roomNumber = p_roomNumber;
-- 방번호 기준으로 1일치의 숙박료를 변수에 대입함

 

 

 

조건문 & 반복문

 

조건문 IF 문 : 기본 문법 


IF 조건 THEN
   -- 코드
ELSIF 다른조건 THEN
   -- 코드
ELSE
   -- 코드
END IF;

 

반복문 FOR & WHILE 의 기본 문법 

FOR 변수 IN 범위 LOOP

-- 코드
    
END LOOP;

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

WHILE 조건 LOOP

-- 코드

END LOOP;

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

 

 

 

 

 

 

 

프로시저 기본 문법

 

CREATE OR REPLACE PROCEDURE 프로시저이름 IS 
    --프로시저 내에서 사용할 변수
    변수이름 데이터타입;
    변수이름 데이터타입;

 BEGIN
     기능 구현,처리 작성;
 END;

 

 

 

IF 문을 포함한 프로시저 기본 예제

CREATE OR REPLACE PROCEDURE 예제프로시저(입력값 NUMBER)
IS
BEGIN
   -- 입력값이 10보다 크면 메시지 출력
   IF 입력값 > 10 THEN
      DBMS_OUTPUT.PUT_LINE('입력값이 10보다 큽니다.');
   -- 입력값이 10과 같거나 작으면 다른 메시지 출력
   ELSE
      DBMS_OUTPUT.PUT_LINE('입력값이 10보다 작거나 같습니다.');
   END IF;
END 예제프로시저;

 

WHILE 문을 사용한 프로시저 예제 


CREATE OR REPLACE PROCEDURE CountdownProcedure(시작값 NUMBER)
IS
   현재값 NUMBER := 시작값;
BEGIN
   -- 현재값이 1보다 큰 동안 반복
   WHILE 현재값 > 0 LOOP
      DBMS_OUTPUT.PUT_LINE('현재값: ' || 현재값);
      현재값 := 현재값 - 1;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('Countdown이 종료되었습니다.');
END CountdownProcedure;

 

 

 

DBMS_OUTPUT.PUT_LINE 이란?

Oracle PL/SQL에서 결과를 출력하는 데 사용되는 프로시저입니다.

사용자의 별도의 정의 없이도 사용할 수 있습니다. PL/SQL 블록이나 프로시저 실행 중에 메시지를 출력하고 디버깅 하는데 도움을 줍니다

 

 

사용법

DBMS_OUTPUT.PUT_LINE(출력할_메시지);

예제

DECLARE
   변수명 NUMBER := 10;
BEGIN
   DBMS_OUTPUT.PUT_LINE('현재 변수값: ' || 변수명);
END;

 

 

DBMS_OUTPUT.PUT_LINE 은 괄호 안에 출력할 메세지를 받습니다

문자열 연결 연산자 '||'을 사용하여 문자열과 변수를 함께 출력할 수 있습니다

 

주의사항 

1. 출력 활성화를 먼저 시켜야 합니다.

실행 전에 SET SERVEROUTPUT ON;을 먼저 실행하여 활성화를 시켜줘야 합니다

2. 크기제한

출력되는 문자열은 특정 크기로 제한됩니다.

크기가 넘어가면 일부 메시지가 잘릴 수 있습니다

3. 클라이언트 도구에 따라 다름

주로 SQL*PLUS와 같은 클라이언트 도구에서 지원됩니다. 다른 도구에서  사용이 불가할 수 있습니다