ORACLE

https://opentutorials.org/module/4192 

• 가격정책
• 설치
• 사용자와 스키마
• 테이블
• 행추가
• 행읽기
• 행 수정&삭제
• primary key
• sequence
• SQL Developer
• 표의 분해
• 표의 조립
• SQL Developer 테이블관리
• JOIN
• 다음 단계 소개

커버정책

   Graph, Document, Object, Hierarchical, Key-Value, Relational

   그래프형, 문서형, 객체형, 계층형, key-value형, 관계형[압도적으로 많이 쓰임]

   https://db-engines.com/en/ranking

 

맨 위로

가격정책  

Express 는 무료, Named User plus 사용자 수에 따른, Processor cpu의 성능에 따른

검색어: oracle price table

https://www.oracle.com/assets/technology-price-list-070617.pdf

 

 

 

 

맨 위로

설치

검색어 : oracle express edition 18c

공짜, 오라클의 대부분의 기능이 있음. 다른 오라클 제품들과 호환됨. 학습용으로는 더없이 좋음. 작은 규모의 비지니스

 

Window, Linux 사용가능, Mac 은 사용불가. 설치할 때 설정이 매우 까다로움, 실제 상용에서는 어마어마한 시간 소요. Express는 간단. password 중요[관리자권한, super user 의 권한]

실행: SQL Plus  -> Enter user-name:  sys AS SYSDBA [Enter][Enter]  [비밀번호 불필요]

SQL 프럼프트가 나오면 설치 성공

 

ORACLE 서비스 정지/사용 하기

제어판 Control Panel/System and Security/관리도구 Administrative Tools/Services  에서 오라클을 시작 또는 정지할 수 있다.

Control Panel/System and Security/Adminstrative Tools/Services

우클릭/Stop 중지 하면 Running 이 사라짐

 

 

'나 이거 할 줄 알아'를 외치는 최소 단위

SETUP,  CRUD: Create, Read [이 두 가지가 제일 중요], Update, Delete

GROUP,  RUN

맨 위로

사용자와 스키마

스키마: 서로 연관된 표들을 그루핑하는 일종의 디렉토리. 스키마에 속한 표들을 설명하는 표의 형태, 어떤 정보를 갖는지 등을 설명하는 

오라클은 1억 넘고, 고성능컴퓨팅을 요함. 여러 사용자들이 사용하게 되고, 사용자를 만들면 그 사용자에 속한 스키마가 만들어짐.

사용자생성

맨 처음 SQL Plus를 실행시킬 때 Enter user-name: 메세지에 대한 입력을 sys as SYSDBA라고 입력하면 최고권한으로 접근하게 됨. 

검색어: oracle sql reference create user    https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8003.htm

CREATE USER

네모: 그대로 써야 함    둥근 모서리 띠: 입력해야 하는 값   가지: 그 중에서 선택   

CREATECREATE USER user_name IDENTIFIED BY password;    으로 사용자를 생성하려고 하면 오류가 발생한다.

ORA-65096: invalid common user or role name       라는 오류메시지. [콘솔에서는 오른쪽클릭이 복사/붙여넣기]

구글링해보면 https://wookoa.tistory.com/239 에서 설명하는 내용.

두가지 방법을 제시하고 있는데 그 중 2 번째 방법은 위의 명령 실행 전에 먼저 수행해야 할 명령이 있다.

       ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE;

ALTER 실행 후 CREATE USER 가 정상 수행됨

이 경우에는 saltfun 이라는 사용자의 생성과 함께 saltfun이라는 스키마도 함께 생성된 것임.[쏠트펀8]

하지만 위에서 생성된 사용자 saltfun은 콘솔로 접근할 권한이 없어 로그인이 거부됨

이렇게 생성된 사용자는 권한이 하나도 없어 사실 아무것도 할 수 없는 상태

사용자 권한 부여

SQL> GRANT DBA TO saltfun;     saltfun에게 DBA  관리자 권한 부여

실제상황에서는 데이터베이스를 관리하는 관리자와 이용하는 사용자를 구분하여 2원화 관리하는 것이 이상적.

권한이 부여된 후에는 콘솔로 접근이 됨

 

 

 

맨 위로

테이블

검색어: "create table sql references oracle"     Oracle Help Center 공식문서

 

도식의 형태로 명령에 대한 사용법이 상세히 설명되어 있음.

표를 만들기 전에 엑셀 등을 이용하여 미리 만들어 본다.

테이블생성

CREATE TABLE topic (
    id NUMBER NOT NULL,              데이터 형식(data type): NUMBER.   NOT NULL: 빈칸 허용하지 않음
    title VARCHAR2(50) NOT NULL,    data type: VARCHAR2 문자열. 길이는 50
    description VARCHAR2(4000),       문자열 길이는 최대 4000임.  4000이 넘는 데이터라면 다른 형식 선택
    created DATE NOT NULL,            data type: DATE   
);

SELECT owner, table_name FROM dba_tables WHERE table_name='TOPIC';   topic 테이블의 소유자 확인

TOPIC 테이블의 소유자 확인

검색어: oracle table list    https://hermeslog.tistory.com/4

TOPIC이라는 테이블이 saltfun 이라는 유저 또는 스키마에 속해있음을 알려줌

사용자이름을 소문자로 쓰면 안됨. 

맨 위로

행추가

INSERT INTO topic          ;이 나오기 전까지는 명령이 모두 입력된 것으로 보지 않으므로 줄바꾸기 해도 하나의 명령문.

             (id, title, description, created)

            VALUES

          (1, 'ORACLE', 'ORACLE is ...', SYSDATE);            SYSDATE  현재 시간

 1row created이 나오면 성공. 그러고 나서 반드시 commit; 명령 수행.

이런 식으로 다른 행의 자료들도 추가할 수 있다. commit; 을 수행해야만 실제로 반영된다는 사실에 유의.

 

SQL이란?

Structured Query Language

 

맨 위로

행 읽기

추가, 삭제는 그리 어렵지 않으나 읽기는 어렵다. 중급이냐 아니냐를 가르는 기준.

SELECT * FROM topic;    topic 테이블에서 모든 내용 가져오기

 

물론 Oracle SQL Developer 와 같은 다른 도구들을 쓰면 훨씬 직관적으로 다룰 수 있긴 하다. 하지만 여기서는 기본에 대해서만 본다.

행과 컬럼 제한하기

SELECT * FROM topic;      topic 테이블의 모든 컬럼의 값[ * ]

SELECT id, title, created FROM topic;   topic 테이블의 id, title, created 컬럼만 선택

SELECT * FROM topic WHERE id=1 ;  topic 테이블의 id=1 행만 선택

SELECT * FROM topic WHERE id>1 ;  topic 테이블의 id>1 행만 선택

SELECT id, title FROM topic WHERE id=1 ;  topic 테이블의 id=1 행의 id, title 컬럼만 선택

 

정렬과 페이징

SELECT * FROM topic ORDERED BY id DESC; id를 내림차순(작아지는)으로 정렬하여 결과를 보여줌: descendent

SELECT * FROM topic ORDERED BY id ASC; id를 오름차순(커지는)으로 정렬하여 결과를 보여줌: ascendent

SELECT * FROM topic ORDERED BY title ASC; title이 오름차순(커지는)으로 정렬되면서 결과를 보여줌: ascendent

데이터가 방대한 경우(1억개) SELECT * FROM topic; 과 같은 명령은 재앙을 초래할 수 있음. 마비에 이를 수 있다.

SELECT * FROM topic OFFSET 1 ROWS;     행번호가 0번 부터 시작한다고 보고 1번 행부터 결과를 보여줌

SELECT * FROM topic OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;  1번행부터 1개 행만 표시

SELECT * FROM topic OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY;  0번행부터 2개 행만 표시

 

 

 

맨 위로

행 수정 & 삭제

위와 같은 내용의 테이블이 있다 치면 3행의 'SQL server'를 'MSSQL'로 바꿔보자

확인하기 위해 SELECT * FROM topic;

마지막에는 반드시 전송명령 commit;    을 실행시켜야만 변경사항이 반영됨

삭제 명령들은 파괴적인 결과를 초래하기 때문에 매우 조심히 다뤄야 함.

DELETE FROM topic:    모든 행이 다 삭제됨. 따라서 반드시 백업파일을 마련하는 습관이 필요. 아래와 같이 정확한 위치를 지정해줘야 함.

DELETE FROM topic WHERE id=3;       id 열값이 3인 행을 삭제   [뒤에는 반드시 commit; 실행]

 

 

맨 위로

primary key  기본키, 주키

primary key는 테이블을 생성할 때 혹은 이후에라도 지정이 가능하지만, 처음에 추가하는 것이 바람직. 데이터가 많아지면 primary key 추가가 부담스러운 작업이 됨. 

테이블 지우기   DROP TABLE 테이블명;     이때 commit; 명령 불필요

테이블 삭제

CONSTRAINT  제약조건

CREATE TABLE topic (
    id NUMBER NOT NULL,
    title VARCHAR2(50) NOT NULL,
    description VARCHAR2(4000),
    created DATE NOT NULL,
    CONSTRAINT PK_TOPIC PRIMARY KEY(id)
);

id 열을 primary key 로 하여 표 생성

 

id를 중복해서 적용하려면 오류가 발생

primary key를 지정한 데이터와 지정하지 않은 데이터의 처리속도는 엄청난 차이가 있음.

필수사항은 아니지만 경제성 & 로직 면에서 반드시 지정해줘야 함.

맨 위로

sequence  

sequence를 생성해보자. sequence 는 차례대로 값을 증가시켜 나간다.

SQL> CREATE SEQUENCE SEQ_TOPIC;    SEQ_TOPIC : 생성하려는 sequence 의 이름을 달아줌

sequence에 대해 알아보기 위해 일단 이미 있는 topic의 내용을 다 지우겠다. DELETE 명령은 매우 신중해야 하게 사용해야 함.

DELETE 는 신중히 사용, 실행 후에는 COMMIT; 을 해줘야 함. sequence를 사용하여 행 추가

id 가 1로 자동 생성됨

sequence.NETVAL 로 생성한 id가 순서대로 커지지는 않는 것 같다. 암튼 중복할까봐 걱정할 필요는 없다

현재의 sequence 값을 알려면  SELECT SEQ_TOPIC.CURRVAL FROM topic;  그런데 결과가 이상하다.

sequence의 값이 행의 개수 만큼이나 출력된다

topic 대신에 DUAL 을 써주면 한 개의 값만 출력된다. 

sequence에 대한 추가 설명 http://www.gurubee.net/lecture/1037 

SELECT SEQ_TOPIC.CURRVAL FROM DUAL;

sequence는 생성할 때 증가 간격을 설정하는 등 많은 설정을 할 수 있으며, 생성된 후에도 ALTER 등을 이용하여 수정이 가능하다

시퀀스는 primary key 와 패밀리라고 생각해야 함. 같이 쓸 때 강력해져. unique한 값을 만들어주기 때문. 테이블과 시퀀스는 별도로 관리됨.

 

 

서버와 클라이언트 

SQL Plus는 클라이언트. 콘솔 형식이 아닌 그래피컬한 GUI환경에서 ORACLE을 제어할 수 있는 프로그램들이 매우 많다.

예를 들어 ORACLE에서 무료로 제공하는 SQL Developer 도 있고, 매우 비싸지만 기능이 워낙 강력하여 사용자 수가 제법 많은 TOAD라는 SQL ORACLE 클라이언트도 있다. 

 

맨 위로

SQL Developer 

검색어: sql developer oracle

https://www.oracle.com/kr/tools/downloads/sqldev-v192-downloads.html

설치 후 실행 시키면 connection을 만들어야 함.

Name은 임의로 달아줌. Username 에 따라 권한이 달라지므로 다른 유저의 db는 권한이 없을 수 있음. 주의 요망

 

표를 분해하고 조립하기 - JOIN

맨 위로

표 분해의 의미

관계형 데이터 베이스는 표로 된 데이터를 다룬다. 표가 비대해지면 필요에 따라 작게 쪼개거나, 이런 저런 필요에 따라 결합하면서 없었던 표를 만들어낼 수 도 있다. 

스프레드 시트에 표의 설계를 먼저 해본다. 기계는 사람이 수동으로 할 수 있는 것만 자동화할 수 있으며 첫 설계단계에서 매우 중요하다. 뭔가를 하기 전에 반드시 먼저 수동으로 해보아야 한다.

처음에 설계한 테이블이 아래 그림과 같다 치자. 그런데 자료를 더 추가 하고 싶다면? 

기존에 이런 테이블이 하나 있다 치자

이런 식으로 말이다.

saltfun의 prfile을 developer에서 manager로 바꾸려고 한다면 saltfun의 행이 많다면 [예를 들어 1억개라면] 거의 불가능에 가까울지도 모른다. 또, ypage라는 이름을 가진 사람이 있다고 하면 topic을 봐서는 ypage의 존재에 대해 알 수가 없다.  이제 표를 분해해보자. 이렇게 말이다.

표를 분해함으로써 얻을 수 있는 또다른 이점의 하나는 name과 profile 이 똑같은 다른 사람도 있을 수 있다는 것을 나타낼 수 있는 것이다

표를 어떻게 쪼갤지에 대해서는 데이터 모델링 등의 방법론들이 있으며 다른 수업에서 배울 수 있다.

맨 위로

표 조립의 의미

표를 쪼개므로써 나타나는 문제의 하나는 관리, 저장공간의 면에서 효율적이기는 하지만 보기가 매우 불편하다는 것이다.  이런 경우에 표들끼리 JOIN 하여 마치 원래부터 합쳐져 있는 표처럼 보이게 해줄 수 있다. 

SELECT * FROM topic LEFT JOIN author ON topic.author_id=author.id;    이런 식으로 topic 테이블을 왼쪽 LEFT 에 두고 author 테이블을 JOIN 하되 topic의 author_id와 author테이블의 id 같은 행을 author에서 찾아 붙이게 된다.

이 기능으로 인해 관계형DB가 DB시장을 오랫동안 지배해온 어마무시한 기능

맨 위로

SQL Developer 테이블관리

SQL Developer에서 테이블 추가. 일단 위의 예시를 실습을 통해 한 번 해보자. author라는 테이블을 먼저 만든다.

사용자이름/Tables(Filetered)/우클릭/New Table

사용자이름/Tables/우클릭/New Table

초록색 + 버튼으로 컬럼들을 추가한다

DDL탭은 명령어를 확인할 수 있게 해줌

기존테이블에 컬럼 추가. 테이블이름/오른쪽클릭/Edit/ 초록 +

상단의 Column탭의 연필도구로도 컬럼 수정이 가능

 

author 테이블의 id에 대한 sequence 생성

sequence Name: SEQ_AUTHOR

상단의 Data 탭으로 데이터 직접 입력/편집이 가능

SQL query를 입력할 수 있는 창이 뜬다

INSERT INTO author (id, name, profile) VALUES(SEQ_AUTHOR.nextval, 'saltfun', 'developer');

INSERT INTO author (id, name, profile) VALUES(SEQ_AUTHOR.nextval, 'mirgiana', 'Design');

INSERT INTO author (id, name, profile) VALUES(SEQ_AUTHOR.nextval, 'ypage', 'writer');

INSERT INTO author (id, name, profile) VALUES(SEQ_AUTHOR.nextval, 'mirgiana', 'Design');

매 행 별로 따로따로 play버튼을 클릭함. 네모안에 작은삼각은 Run Script(F5)인데 아마 한 번에 실행시킬 수 있는 듯

하단에 결과가 나타남

표의 내용을 수정하고 커밋버튼(F11)을 클릭해야 반영됨

commit 버튼을 클릭함과 동시에 하단에 메시지가 뜬다

 

 

맨 위로

JOIN

AUTHOR_ID와 같이 다른 테이블의 primary key 키값이 들어있는 열을 foreign key라고 함

SELECT * FROM topic LEFT JOIN author ON topic.author_id = author.id;

id 값이 순서대로 되지 않은 것은 초기 설정시 간격과 초기값 설정에서 문제가 있었던 것임. 이 실습과는 무관한 내용

SELECT id, title, name FROM topic LEFT JOIN author ON topic.author_id  = author.id;

위의 커리는 'column ambiguously 애매모호하게 defined'라는 오류를 발생한다. id 가 어느 테이블(topic 인지 author 인지)인지 확실치 않기 때문이다

따라서 이 경우는 이렇게 해야 한다.

SELECT topic.id, title, name FROM topic LEFT JOIN author ON topic.author_id = author.id;

또한 표시되는 컬럼의 이름도 달아줄 수 있다. 예를 들어 topic.id 컬럼명을 '토픽ID'라고 해보자. 그냥 topic.id 뒤에 한칸 건너 컬럼명을 써주면 된다. 이런 것을 별명이라고 한다.

SELECT topic.id 토픽ID, title, name FROM topic LEFT JOIN author ON topic.author_id = author.id;

표의 이름을 일일이 쓰기가 불편하다고 느껴질 수 있는데 표이름 역시 별명을 달아 처리할 수 있다.

역시 테이블명 뒤에 바로 한칸 건너 별명을 붙여주면 된다. 즉 FROM topic -> FROM topic T, JOIN author -> JOIN author A 와 같은 식이다.

SELECT T.id 토픽ID, title, name FROM topic T LEFT JOIN author A ON T.author_id=A.id;

topic.id가 1인 행만 가져오려면

SELECT T.id 토픽ID, title, name FROM topic T LEFT JOIN author A ON T.author_id=A.id WHERE T.id=1;

 

맨 위로

수업을 마치며

관계형데이터베이스 모델링

관계형데이터베이스를 사용하는 것보다는 현실의 복잡한 문제를 관계형데이터베이스에 잘 반영하는 것은 관계형데이터베이스 전문엔지니어들에게도 쉬운 일이 아니다.

훌륭한 테이블을 만들 수 있는 다양한 방법론이 고안되어 있다. 그러한 방법론들을 잘 모아서 정리해둔 것이 관계형 데이터 모델링이다. 좋은 표를 만드는 표준화된 방법론에 대해서 알려면 관계형 데이터베이스 모델링에 대해 학습해야 한다.

표에 대한 시야가 획기적으로 넓어진다.

처음 자료를 만들 때부터 index를 잘 해놓으면 1시간이 소요되는 작업을 1초도 안되는 사이에 해낼 수 있는 놀라운 매직을 경험할 수 있다.  

 

 

실제로 데이터베이스만을 직접적으로 사용하는 경우는 거의 없다. 웹, 앱 등 다양한 종류의 장치들이 사용자의 금융정보나 사용자가 작성한 글이나 또는 장비가 만든 데이터를 DB에 보관한다. 

이때 사용자가 사용하는 UI와 DB 사이에 존재하는 중간다리의 역할을 하는 기술을 Middleware 라고 한다.

ORACLE과 함께 자주 사용되는 미들웨어 기술로는 범 JAVA 진영의 JSP, 서블릿, Spring과 같은 것들이 있다. 이보다는 덜 이지만 그래도 많이 사용되는 PHP, Python, Node.js, Ruby와 같은 기술들고 ORACLE의 미들웨어로 활용될 수 있다.