금일 수업에는 Oracle SQL에 대해서 배우는 첫 시간이 되겠다. 오늘부터 배우는 수업은 현업에서도 많이 쓰이는 부분이라고 하니 빡집중하며 최대한 필기해야지 ㅎㅎ

먼저 지난번 설치했던 Oracle 클라이언트를 실행시키면 위와 같이 첫 화면이 나오게 된다.

여기서 상단에 있는 도구 탭을 눌러 'SQL 워크시트'를 눌러준다.

주인장의 경우 지난주 금요일에 수업을 마치기 전 접속할 데이터베이스의 주소와 아이디, 암호 등을 미리 입력해둔 상태라 바로 접속이 가능하지만

새롭게 접속하려는 데이터베이스가 있다면 오른쪽의 초록색 +를 눌러서 세팅을 마쳐주면 되겠다.

Oracle의 경우 eclipse와 같이 프로그래밍을 위해 만들어진 프로그램이 아니기 때문에 사용하는 방법이 다르니 이 부분을 참고해야 한다.

(그냥 꾹 참고 하라는 뜻)

---

다음으로 나오는 내용들은 Oracle에서 사용하는 명령어들의 종류를 각각 풀어서 설명한것들이다.

오라클 명령어 종류

1. DCL

사용자를 생성하고, 권한을 부여하거나 삭제하는 명령어

GRANT : 특정 데이터베이스 사용자에게 특정 작업에 대한 수행 권한 부여를 한다.

REVOKE : 특정 데이터베이스 사용자에게 특정 작업에 대한 권한을 박탈, 회수한다.

COMMIT : 트랜잭션의 작업이 정상적으로 완료되었음을 관리자에게 알려준다.

ROLLBACK : 트랜잭션의 작업이 비정상적으로 종료되었을 때 원래의 상태로 복구한다.

​

2. DDL

자료를 저장하기 전에 설정하는 구조적으로 정의하는 명령어

CREATE : 데이터 베이스, 테이블 등을 생성하는 역할을 한다.

ALTER : 테이블을 수정하는 역할을 한다.

DROP : 데이터베이스, 테이블을 삭제하는 역할을 한다.

TRUNCATE : 테이블을 초기화 시키는 역할을 한다.

​

3. DML

데이터베이스에 들어 있는 데이터를 조회하거나 검색하고, 데이터를 변형하는 명령어

SELECT : 데이터를 조회하는 역할을 한다.

INSERT : 데이터를 삽입하는 역할을 한다.

UPDATE : 데이터를 수정하는 역할을 한다.

DELETE : 데이터를 삭제하는 역할을 한다.

​

4. TCL

논리적인 작업 단위를 묶어서 DML에 의해 조작된 결과를 작업 단위(트랜잭션) 별로 제어하는 명령어

COMMIT : 삭제 or 입력한 자료에 대해 트랜잭션을 완료하는 명령어.

ROLLBACK : 데이터의 변경사항이 취소되어 이전 상태로 되돌리는 명령어.

SAVEPOINT : 데이터의 특정 부분만을 저장하도록 정의하는 명령어.

​
출처

위의 용어들은 정처기 필기에도 나온다고 하니 꼭 기억해두도록 하자!

---

위와 같이 회원가입 테이블을 만든 뒤, 실행하고자 하는 부분의 세미콜론을 찍어준 이후 Ctrl + Enter를 누르면 딱 해당하는 부분의 명령어만 수행하게 된다!

( 바로 밑에 사용자 삭제 명령어 적어뒀었는데 다 같이 실행될까 살짝 쫄렸고..)

이후 삭제는

위의 사진과 같이 커맨드를 입력 후,

똑같이 세미콜론을 누른 뒤 Ctrl + Enter를 눌러 실행하면 되겠다.

---

기존에 생성된 테이블에 칼럼을 추가할 수도 있다.

위와 같이 추가하게 되면

이렇게 새로운 email 항목이 추가가 가능하겠다.

추가뿐만 아니라 삭제, 정의 등의 기능도 가능하다.

하지만 실제로 프로젝트를 수행하거나 현업에서 임의적으로 추가를 이행할 시 기존에 DB에 저장되어 있던 데이터의 손실이 발생할 수도 있으니 꼭 주의해야 하겠다.


그렇다면 이번엔 멤버를 추가하는 테이블을 만들어보자.

화살표 방향대로 회원의 아이디 데이터를 넣을 것이기 때문에 위처럼 테이블과 제약 조건문을 입력한다.

위의 코드를 실행을 시키고 난 이후

해당 경로로 들어 가서

접속하려는 데이터베이스를 선택하고, 아까 만들어둔 테이블 항목들을 선택하여 임포트를 실행하여 서로 병합을 시키게 되면..

위와 같이 두 테이블 간의 관계도가 보기 쉽게 표로 정리가 된 것을 확인할 수 있다.


원래는 Oracle이 아니라, VScode로 먼저 설계를 마친 이후에 Oracle로 작업을 진행하는 순이 올바르나, 강사님께서 이해를 돕기 위해 순서를 역순으로 강의를 진행하셨다고 한다.

그렇다면 원래 VScode는 어떤 식으로 설계를 진행해야 할까?

먼저 VScode를 실행해 보자.

실행시킨 이후, VScode를 실행시켜 새 파일 버튼을 눌러

위의 형식으로 새 파일을 만든다.

(반드시 '파일 이름'.vuerd.json 형식으로 만들어야 함!!!!)

이후 ERD 에디터를 불러온다.

불러오는 방법은 파일에서 우클릭 후 Open ERD Editor를 누르거나, 오른쪽 위의 버튼을 누르면 되겠다.

이후 이런 까만 화면이 뜨게 된다.

우리는 Oracle을 사용 중이니 우클릭하고 Database 항목에서 선택을 해준다.

그다음, 위의 사진 맨 위의 항목인 New Table을 눌러 테이블을 생성해 준다.

우리가 생성하려는 것은 기존의 membertbl이기 때문에, Oracle에 있는 항목들과 똑같이 구성해 준다.

이후 우클릭을 눌러 기본키를 설정해 준다.

이후 주소 테이블도 똑같이 설정을 해주도록 하자.
(외래 키는 따로 생성하는것이 아니다!)

이후 바탕화면에서 우클릭, Relationship -> One N 을 눌러서 회원 테이블에서 주소 테이블로 지정을 해주면..

위와 같이 외래키가 자동으로 설정이 이루어지게 된다..
여기서부터 ㄹㅇ 신기함의 향연이다..

헉

이게 제일 신기했다.. 지가 혼자서 java 클래스를 만들어주다니..

SQL 명령어까지? ㄷㄷ;

코드 창 위의 버튼들을 누르면 이렇게 보기 좋게 다른 언어로 바꿔준다.

(혹은 도표나!)

이번엔 위의 자동 생성된 SQL 명령어를 복사하여 Oracle 클라이언트로 복사를 해보자.

먼저 멤버 테이블,

그리고 주소 테이블까지.
위의 두 사진에 있는 명령어들은

방금 본 이 설계도를 따른 명령어들이다.

이번에는 물품을 추가하는 테이블을 만들어 보자.

여기서 내용 항목에 CLOB가 있는데

이는 기존의 VARCHAR2()의 문자열 길이 한계를 없애주는 명령어이다. 기억해두도록 하자.

이후 주문을 받을 테이블을 만든다.

상품 하나를 여러 사람에게 팔아야 하니 물품 테이블에서 주문 테이블로 One N을 이용하여 Relationship을 설정한다.

그러면 이렇게 설정이 완료되고,

전체적으로는 외부 키까지 설정이 되어 이러한 양상을 띄겠다.

(데이터는 절대로 중복되면 안 된다!)

이후 위의 SQL 소스코드를 Oracle에서 붙여넣기하여 한번 실행해 보도록 하자.

실행을 완료하면 콘솔창에 위처럼 완료되었다는 스크립트들이 쭉 실행되게 된다.

만약 기존에 있던 항목들과 중복되는 데이터들이 있다면 반드시 기존의 필요 없는 항목들을 삭제하거나 중복되는 데이터들의 이름을 바꿔주도록 하자.

- 테이블 삭제 커맨드

테이블을 지우는 커맨드는

DROP TABEL 테이블명 CASCADE CONSTRAINTS;

이다!
생각 안 난다면 복사 붙여 넣도록 하자.

(사실 "우클릭 > 삭제"해도 되긴한다)

---

생성이 모두 완료가 되었다면 위에서 언급했던 것처럼

Data Modeler - 임포트 - 데이터 딕셔너리

경로로 들어가서 해당하는 경로와 테이블들을 체크해 준 뒤 실행을 하게 되면,

VScode에서 미리 설계해둔 자료와 똑같이 위와 같은 결과가 도출되게 된다.

원래라면 VScode에서 Oracle로 연동이 이루어지게 되어 DB로 바로 데이터를 전송할 수도 있겠지만,

현재 우리가 실습을 진행하면서 쓰는 버전 같은 경우 무료 버전이기 때문에 그 정도까지의 기능을 사용할 순 없다고 하더라도

기존에 eclipse에서 lombok을 통해 하나하나 일일이 값을 넣어주던 시절에 비해서는 이 정도여도 감지덕지 & 선녀이다..

---

이번에는 DML을 수행할 차례이다.

Java로 수행을 하기 전 Oracle에서 한번 추가, 수정, 삭제를 수행해 보자.

-- 데이터를 추가한 이후에는 항상 아래 두가지를 결정해야 함.
COMMIT; -- 적용
ROLLBACK; -- 되돌리기

들어가기에 앞서 데이터베이스의 데이터를 넣거나 수정할 때는 반드시 위의 두 가지 명령어 중 하나를 수행하여야 한다.

COMMIT은 실행한 명령어에 따른 결괏값을 적용시키는 것, ROLLBACK은 이전 적용 지점까지 되돌리기 시키는 것이다.

-- 회원가입(추가)
-- INSERT INTO 테이블명 (컬럼명들...) 
    -- VALUES(실제추가할 값들...);
INSERT INTO member (userid, userpw, username, userage, userphone, usergender, userdate) 
    VALUES ( 'a', 'a', '가나다', 12, '010-0000-0000', 'F', CURRENT_DATE); 
-- CURRENT_DATE는 내장함수. 현재시간을 데이터베이스에 들어가는 시점으로 설정

먼저 회원가입이다. 미리 지정해둔 테이블의 칼럼들 양식에 맞게 항목을 적용해 준다.

이때, userid와 같은 경우 기본 키이기 때문에 중복 적용이 되지 않는다.

또한 유저의 성별도 앞서 M, F 두 가지로 만 설정이 가능하도록 제약 조건문을 걸어둔 상태이기 때문에 이점을 참고하고 넣도록 한다.

-- 전체 조회
-- SELECT 컬럼명 FROM 테이블명 별칭;
-- 모든 필드를 한번에 부를때는 *을 붙인다.
SELECT m.* FROM member m;

이후 데이터가 잘 들어갔는지 확인을 하기 위해서 전체 조회를 실행해 보았다.

필자는 위와 같이 여러 정보들을 입력했다. 잘 적용이 되는 모습이다.

(2번째의 성별이 C가 적용이 되었는데, 아까 임포트 과정에서 성별 제약문이 누락된 모양이다 ㅠㅠ 앞으로는 꼼꼼히 확인하도록 하자..)

이후 잘못 적용된 데이터가 있거나, 취소를 원할 경우 ROLLBACK을, 적용을 원할경우 COMMIT를 입력하여 실행시키면 되겠다.

이번에는 회원 주소이다.

-- 회원주소등록
INSERT INTO memberaddr ( userid, userdate, userposrcode, useraddr, userno) 
    VALUES ( 'a', CURRENT_DATE, 12345,'부산 남구', seq_memberaddr_no.NEXTVAL); -- 자동으로 증가하는 시퀀스
COMMIT;

회원가입의 경우에는 회원의 ID가 곧 기본 키값이었기에 시퀀스를 설정해 주지 않았지만,

위의 주소 테이블의 경우에는 아이디 한 개당 여러 가지의 주소가 입력될 수 있기 때문에 자동적으로 순차적인 기본 키를 부여하는 시퀀스를 설정하였다.

시퀀스는 아래와 같은 명령어로 부여하였다.

// 시퀀스 추가
CREATE SEQUENCE seq_memberaddr_no INCREMENT BY 1 START WITH 1001 NOMAXVALUE NOCACHE; 
  // 시퀀스 1001부터 1씩 증가. (멤버 주소)
CREATE SEQUENCE seq_item_code INCREMENT BY 2 START WITH 1 NOMAXVALUE NOCACHE;
  // 시퀀스 1부터 2씩 증가 (물품) 
CREATE SEQUENCE seq_purchase_no INCREMENT BY 1 START WITH 10001 NOMAXVALUE NOCACHE; 
  // 시퀀스 10001부터 1씩 증가 (주문수량)

COMMIT;

(교재 Pg.83쪽 참고)

미리 다른 테이블에서도 사용될 시퀀스들도 적용해두었다.

어쨌든 주소값이 잘 지정되어 있는지 확인해 보면..

잘 들어갔다.

설정해둔 1001부터 시퀀스 값의 증가가 제대로 이루어진 것을 확인할 수 있다.
(이후 COMMIT 적용!)



물품 등록도 마찬가지.

// 물품등록
INSERT INTO item(code,name,price,quantity, content, regdate)
    VALUES (seq_item_code.NEXTVAL, '바나나', 12980, 1000, '상큼', CURRENT_DATE); // 자동으로 증가하는 시퀀스
INSERT INTO item(code,name,price,quantity, content, regdate)
    VALUES (seq_item_code.NEXTVAL, '딸기', 12980, 1000, '상큼2', CURRENT_DATE);
INSERT INTO item(code,name,price,quantity, content, regdate)
    VALUES (seq_item_code.NEXTVAL, '포도', 12980, 1000, '상큼3', CURRENT_DATE);
COMMIT;

이번에는 어찌 보면 가장 위험할 수도 있는

'수정'과 '삭제'에 대해서도 한번 다뤄보도록 하겠다.

// 수정하기 ( 조건 )
// UPDATE 테이블명 SET 컬럼명=변경값, (+α 컬럼명=변경값) WHERE 조건;  
UPDATE member SET username = '바밤바' WHERE userid = 'a'; 
// 조건을 무조건 붙여야함! 조건을 걸지 않을시 해당 테이블의 모든 값이 변경됨. (값을 실수로 병경했을땐 무조건 rollback 할 것.)
UPDATE member SET username = '밤맛바밤바' WHERE userid = 'b';
COMMIT;// 수정하기 ( 조건 )
// UPDATE 테이블명 SET 컬럼명=변경값, (+α 컬럼명=변경값) WHERE 조건;  
UPDATE member SET username = '바밤바' WHERE userid = 'a'; 
// 조건을 무조건 붙여야함! 조건을 걸지 않을시 해당 테이블의 모든 값이 변경됨. (값을 실수로 병경했을땐 무조건 rollback 할 것.)
UPDATE member SET username = '밤맛바밤바' WHERE userid = 'b';
COMMIT;

수정과 삭제에 대해서는 '조건'이 붙게 된다.

아무래도 기존에 있던 데이터의 변경을 다루다 보니 잘못 지정할 경우 DB의 자료 전체가 변동되거나, 전체 삭제가 될 수도 있다.

(물론 롤백을 할 경우 되돌리기는 할 수 있지만, 항상 조심해야 한다.. 퇴사할수도있다.)

아무튼 위와 같이 id값이 'a'와 'b'의 유저 이름을 바꿔보겠다.

(참고로 아까는 보보보였다.)

오 ㅋ

이번에는 삭제이다.

// 삭제하기 (정말 주의해야함!!)
// DELETE FORM 테이블 WHERE 조건;
DELETE FROM item WHERE code = 15;
COMMIT; // 커밋하기전 반드시 확인할 것..!

아이템 테이블에 저장되어 있는 15번째 항목을 지워보도록 하겠다.

굿굿 잘 지워졌다.

회원 탈퇴

와 같은 중요한 정보들의 경우에는 데이터들을 통째로 날려버리는 것이 아니라,

모든 항목을 null로 채우는 방법을 사용한다.

(혹시라도 지웠다가 잘못되면 롤백도 못하는 상황이 생기면 안 되니..)

// 회원탈퇴 (중요한 정보는 지우는것이 아니라, null로 바꿔서 채우는것!)
UPDATE member SET userpw='', username='', userage=0, userphone='', usergender=null, userdate=null WHERE userid = 'a';

확인해 보면

바밤바씨가 회원 탈퇴한 것을 알 수 있다.

WHERE 조건문에는 여러 가지를 적용할 수 있기 때문에 상당히 복잡하다.

가령 아래와 같은 것도 가능하다.

// 재고수량이 3000이하인것만 재고수량을 5000개로 바꿀것
UPDATE item SET quantity = 5000 WHERE 3000 >= quantity;

재고수량(quantity)의 개수가 해당 값보다 작은 경우 해당하는 값들만 바꾸도록 지정할 수도 있다.

그리고 칼럼명과 지정 값의 위치가 서로 바뀌어도 적용이 되는 것을 확인하였다.

위의 코드를 적용하여 확인을 해보니

(아까는 위의 세 개 항목만 상품 테이블에 적용되어 있었다.)

5000개로 개수가 변경되었음을 확인할 수 있다.

---