[JAVA] 23-1. 이클립스와 SQL 연결 및 DB 관리를 위한 JDBC 사용법 - 기본 테이블 구축

1. 기본 테이블 생성

일단 이클립스에 JDBC를 설계하기 위해서는 데이터를 받아올 테이블을 생성해야 하는데요. 오라클과 클라이언트 툴이 설치되어 있다고 가정했을 때 다음과 같은 구문으로 테이블을 생성합니다.

참고로 다음 예제 코드들은 해당 강의를 참조하여 작성했습니다.

-- 유저 테이블
create table users (
	userid          varchar2(50)	primary key, 
	username		    varchar2(50)	not null,
	userpassword	  varchar2(100)	not null,
    userage				number(3)	not null,
	useremail		    varchar2(50)	not null
);

-- 게시판 테이블
create table boards (
    bno             number          primary key,
    btitle          varchar2(100)   not null,
    bcontent        clob            not null,
    bwriter         varchar2(50)    not null,
    bdate           date            default sysdate,
    bfilename       varchar2(50)    null,
    bfiledata		blob			null
);

다음은 CREATE SEQUENCE SEQ_BNO NOCACHE; 구문을 실행하는데, 이 구문은 boards 테이블의 bno가 추가될 때마다 일렬번호(sequence)를 붙여준다고 하네요.

그 다음 어카운트 테이블을 생성해 줍니다.

create table accounts (
  ano     varchar(20) primary key,
  owner   varchar(20) not null,
  balance number      not null
);

-- 테이블을 생성한 뒤 어카운트 두 개를 생성해 줍니다.
insert into accounts (ano, owner, balance) 
values ('111-111-1111', 'Re_Go', 5000000);

insert into accounts (ano, owner, balance) 
values ('222-222-2222', '박종민', 20000000);

commit;

2. 사용자 정보 생성 목적의 프로시저 구축

이 다음 새로운 사용자의 정보를 저장하는 user-create 프로시저를 생산해 주는데요. 여기서 짧게 프로시저에 대해 설명을 해보자면

프로시저란 DB(data server)에 저장되어있는, 일련의 작업을 수행하는 sql 코드의 집합을 의미하는데요. 한마디로 표현하자면 sql의 함수 같은 개념인데요.

이러한 프로시저는 오라클에서 다음과 같이 표현되며, 각각 구문의 뜻은 다음과 같습니다.

1. CREATE PROCEDURE : 프로시저 생성 구문
2. OR : '또는' 을 의미하는 구문
3. REPLACE : 프로시저나 테이블을 대체하는 구문
4. IN : 해당 테이블의 속성의 데이터타입을 토대로 매개변수에 전달할 때 사용됩니다. 즉 해당 프로시저로 데이터를 생성할 때 어떤 테이블의 속성 타입으로 입력할지를 의미합니다.
5. OUT : IN과 반대로 생성이 끝날 때 출력(OUT)되는 문구를 지정합니다.
6. IS : 프로시저와 함수가 구분되는 기준점이라고 보시면 됩니다.
7. BEGIN : 프로시저나 함수가 시작되는 기준점 입니다.
8. END : 프로시저나 함수가 종료되는 기준점 입니다.

CREATE OR REPLACE PROCEDURE procedure_name
(
    parameter1 IN datatype,
    parameter2 OUT datatype
)
IS
BEGIN
    -- 프로시저 내부에서 수행할 SQL 문장들;
END procedure_name;

그럼 위의 기본 프로시저 구문을 토대로 아래의 프로시저 정의를 해석해 보겠습니다.

-- user_create 프로시저를 대체하거나 없을 경우 새로 생성
CREATE OR REPLACE PROCEDURE user_create (
-- 각각의 매개변수에는 users의 다음 속성들의 타입을 전달하도록 함.
    a_userid        IN  users.userid%TYPE, 
    a_username      IN  users.username%TYPE,
    a_userpassword  IN  users.userpassword%TYPE,
    a_userage       IN  users.userage%TYPE,
    a_useremail     IN  users.useremail%TYPE,
    -- (pls는) 오라클의 정수형 데이터 타입
    a_rows          OUT PLS_INTEGER
) 
IS
-- 함수 실행점
BEGIN
-- users 테이블의 다음 속성에 값을 넣음
    INSERT INTO users (userid, username, userpassword, userage, useremail)
-- 넣을 값들은 프로시저에서 설정한 매개변수의 값들로 구성)
    VALUES (a_userid, a_username, a_userpassword,  a_userage, a_useremail);
-- a_rows(출력값)으로 실행된 sql의 가로 구문수를 출력
    a_rows := SQL%ROWCOUNT;
    COMMIT;
END;

참고로 프로시저는 다음 이미지와 같이 앞서 설명한대로 변수 선언 (DECLARE)와 함수 정의 (BEGIN과 END 사이)로 구성됩니다.

(자료 출처 : https://shlee1990.tistory.com/836)

3. 로그인 확인을 위한 메서드 구축

다음은 user_login 함수를 생성하는데요. 여기서도 마찬가지로 sql의 함수 생성법에 대해 짧게 설명해 보겠습니다. 구문은 다음과 같습니다.

1. FUNCTION : 함수를 생성할 때 사용되는 키워드 입니다. (이하 CREATE 및 REPLACE문은 생략)
2. 매개변수 | 데이터타입 : 프로시저의 변수 타입 지정과 다르게 IN, OUT 키워드를 사용하지 않고 지정합니다.
3. RETURN 반환타입 : 함수의 반환값 타입을 지정합니다.
4. IS : 프로시저와 함수의 영역을 나누는 기준점으로 사용되며, AS 키워드 또한 같은 역할을 수행합니다.
5. 변수 | 데이터타입 : 함수 내에서 사용할 지역 변수들을 지정할 때 사용합니다.
6. BEGIN, END : sql 내에서 특정 함수나 프로시저가 sql 구문으로 작성되는 시작점과 종료점을 의미합니다.
7. RETURN 반환값 : 반환할 함수의 실행 결과값을 지정합니다.

CREATE OR REPLACE FUNCTION 함수이름 (
    매개변수1 데이터타입,
    매개변수2 데이터타입,
    ...
)
RETURN 반환타입
IS
    변수1 데이터타입;
    변수2 데이터타입;
BEGIN
    -- 함수 본문: 수행할 작업을 여기에 작성합니다.

    RETURN 반환값; -- 반환값은 반환타입과 일치해야 합니다.
END;

참고로 함수 또한 프로시저와 같이 다음 이미지와 같이 앞서 설명한대로 변수 선언 (DECLARE)와 함수 정의 (BEGIN과 END 사이)로 구성됩니다.

(자료 출처 : https://m.blog.naver.com/handuelly/222055981866)

이제 위의 기본 함수 구문을 토대로 아래의 함수 정의를 해석해 보겠습니다.

-- user_login 함수 정의 (유저 아이디와 패스워드, 리턴값의 타입 지정)
CREATE OR REPLACE FUNCTION user_login (
    a_userid        users.userid%TYPE, 
    a_userpassword  users.userpassword%TYPE
) RETURN PLS_INTEGER
IS
	-- 함수 내 사용할 지역 변수인 유저 패스워드와 결과(result) 지정
    v_userpassword users.userpassword%TYPE;
    v_result PLS_INTEGER;
    --함수 구문 시작
BEGIN
	-- users 테이블에서 사용자가 전달한 매개변수 id(a_userid)와 테이블의 userid의 값이 같은 열의 userpassword를 검색 후 함수의 지역 변수 패스워드(userpassword)에 할당합니다.
    SELECT userpassword INTO v_userpassword
    FROM users
    WHERE userid = a_userid;

	-- 만약 지역 변수인 v_userpassword의 값과 a_userpassword (사용자가 넘겨준 매개 변수 패스워드) 가 일치하면 0 (일치함)을 반환, 틀리면 1 (불일치)를 반환
    IF v_userpassword = a_userpassword THEN
        RETURN 0;
    ELSE
        RETURN 1;
    -- IF문을 끝낼때는 END IF; 구문을 꼭 삽입해야 합니다.
    END IF;
    -- 예외 발생 구문으로 NO_DATA_FOUND는 SELECT INTO 구문을 실행하다 데이터 검색에 실패했을때 발동되며, 2 (예외 발생)를 반환합니다.
EXCEPTION
    WHEN NO_DATA_FOUND THEN 
        RETURN 2;
-- 함수 종료 지점
END;