코린이

WHERE 

SELECT 문으로 데이터를 조회할때 조건을 추가하여 그 조건에 해당하는 데이터만 출력할때 사용합니다.

예를들어 모든 데이터 조회가 아닌 특정 컬럼 즉 '홍길동'이라는 이름을 가진 사람의 데이터만 조회하고 싶을때 사용합니다. (부서번호가 1번인 사원, 월급이 500 이상인 사원 등등) 

기본문법
SELECT [조회할 열이름] FROM [조회할 테이블 이름] WHERE [조건식];

아래는 EMP 테이블을 전체 조회했을때 결과이다.

이 결과에서 만약 ENAME 이 'KING'이라는 사원의 결과만 출력하고싶다면 아래 처럼 작성하면 된다.

SELECT * FROM EMP WHERE ENAME = 'KING';

AND, OR 연산자

WHERE절에서 여러개의 조건식을 지정하게 해줄 수 있는 논리연산자 입니다.

AND와 OR은 WHERE절에 추가적으로 조건을 추가한다는 개념으로 이해하시면 편하실꺼 같습니다.

EMP 테이블의 DEPTNO(부서번호)가 10번이고 직업이 'MANAGER'인 사원의 정보만 조회

SELECT * FROM EMP WHERE DEPTNO = 10 ADN JOB 'MANAGER';

이처럼 AND를 사용해 여러 개의 조건식을 사용할 수 있습니다. 

그리고 JOB 컬럼의 MANAGER, SALESMAN 등 데이터가 대문자로 들어가있기 때문에 조회하실때 대문자로 입력해주셔야 일치하는 행을 조회할 수 있습니다. 

비교연산자

비교연산자에는 대소 비교 연산자 즉 부등호와 같은 비교 연산자가 있습니다.

등가 비교 연산자

EMP 테이블의 사원중 1년 급여가 35000이상인 사원들만 조회

SELECT ENAME, SAL, SAL*12 AS 연봉 FROM EMP WHERE SAL*12 >= 35000;

논리 부정 연산자

논리 부정 연산자는 NOT입니다. 즉 단어 뜻 그대로 아닌 것. 을 생각하시면 됩니다.

EMP 테이블의 사원중  부서가 'MANAGER'가 아닌 사원들의 정보만 조회

SELECT * FROM EMP WHERE NOT JOB = 'MANAGER';

사실 NOT은 이러한 용도로는 자주 쓰이지 않습니다. WHERE 절의 NOT JOB 이 부분은 사실 WHERE JOB != 'MANAGER'와 같기 때문입니다. NOT은 보통 다음에 배울 BETWEEN, IN, IS NULL 과 같은 연산자에 추가적으로 사용되거나 OR, AND와 같이 조건식이 묶여있는 상황에서 반대값 결과를 얻고 싶을때 사용됩니다.

IN연산자

IN연산자를 사용하면 AND, OR을 여러번 사용하지 않아도 되는 장점이 있습니다. 아래 예제를 보시면 쉽게 이해하실 수 있습니다. 

EMP 테이블에서 사원의 부서중 CLERK, SALESMAN, MANAGER인 사원의 정보만 조회

SELECT * FROM EMP WHERE JOB = 'CLERK' OR JOB = 'SALESMAN' OR JOB = 'MANAGER';

SELECT * FROM EMP WHERE JOB IN('CLERK', 'SALESMAN', 'MANAGER');

두 쿼리는 같은 결과를 출력합니다.

IN 연산자는 추후 중첩 쿼리문을 사용할때 자주 쓰이는 부분으로 개념을 정확히 이해 하시는게 좋습니다.

BEETWEEN 연산자 

A 와 B 의 값 사이에 있는 정보를 조회할때 쓰이는 연산자 입니다. 

EMP 테이블의 사원 중 연봉이 20000만 이상 30000이하인 사원의 정보를 조회 

SELECT ENAME, SAL, SAL*12 AS 연봉  FROM EMP WHERE SAL*12 >= 20000 AND SAL*12 <= 30000;

SELECT ENAME, SAL, SAL*12 AS 연봉  FROM EMP WHERE SAL*12 BETWEEN 20000 AND 30000;

역시나 두 쿼리 모두 같은 결과를 출력합니다.

LIKE 연산자

이번 게시글의 마지막 연산자인 LIKE입니다. LIKE 연산자는 일부 문자열이 포함된 데이터를 조회할때 사용합니다.

포함된 문자열을 검색할때 와일드 카드를 사용하는데 와일드카드는 _ 와 %가 있습니다.

_는 한개의 문자를 의미하며 %는 길이와 상관없이 모든 문자를 의미합니다.

EMP테이블의 사원의 이름중 세번째 글자가 R인 사원의 정보를 조회

SELECT * FROM EMP WHERE ENAME LIKE '__R%';

__R% = 3번째 글자는 반드시 R이며 R 앞에는 2 문자가 반드시 와야한다. R 뒤에는 몇 문자가 와도 상관이 없다 라는 뜻입니다. 

EMP테이블의 사원 이름중 첫 글자가 A 로 시작하는 사원의 정보를 조회 

SELECT * FROM EMP WHERE ENAME LIKE 'A%';

이번에는 SELECT문에서 사용 가능한 연산자에 대해 알아보겠습니다. 

연산자의 종류 

SELECT ENAME, SAL+500 FROM EMP; // + : 더하기 연산자
SELECT ENAME, SAL-800 FROM EMP; // - : 빼기 연산자
SELECT ENAME, SAL*12 FROM EMP; // * : 곱하기 연산자
SELECT ENAME, SAL/2 FROM EMP; // / : 나누기 연산자

먼저 EMP 테이블의 사원의 연봉을 계산하는 방법입니다.

SELECT ENAME AS 이름, SAL AS 월급, SAL*12 AS 연봉 FROM EMP;

위와 같이 사용자가 계산식을 만들어 새로운 열을 확인할 수 있습니다. 하지만 위의 연봉이라는 열은 실제 테이블에 존재하지 않는 열입니다.

데이터의 정렬

위의 결과값에서 연봉의 오름차순대로 정렬 혹은 내림차순으로 정렬하려면 아래와 같은 방법을 사용합니다.

오름차순

SELECT ENAME AS 이름, SAL AS 월급, SAL*12 AS 연봉 FROM EMP ORDER BY 연봉 ASC; (ASC는 생략가능)

내림차순

SELECT ENAME AS 이름, SAL AS 월급, SAL*12 AS 연봉 FROM EMP ORDER BY 연봉 DESC;

※ 정렬할 데이터가 많아지면 컴퓨터 자원을 많이 사용하기 때문에 꼭 필요한 경우가 아니면 사용하지 않는 게 좋습니다.

안녕하세요. 최근에 오라클 DB를 접하며 공부하고 이해한 정보를 토대로 요약하여 작성했습니다. 

오라클 DB를 처음 접하시는 분 들에게 조금이나마 도움이 되길 바라면서 이 글을 작성합니다. 

실습환경 = Toad for Oracle

1. DML의 구조 

- 데이터 조회 목적인 SELECT 

- 데이터 삽입 목적인 INSERT

- 데이터 삭제 목적인 DELETE

- 데이터 수정 목적인 UPDATE

이번 게시글에서는 제일 자주 사용되며 제일 중요하다고 볼 수 있는 자료 조회 목적인 SELECT입니다.

데이터 조회에는 반드시 함께 동반되어야 하는 구문은 SELECT ~ FROM 절이며 

SELECT은 어떤 컬럼을 선택하느냐에 대한 필수사항이고 FROM은 어디에서 즉 어떠한 테이블에서 데이터를 읽어 올 것인가 하느냐에 대한 구문입니다.  SELECT ~ FROM 뒤에 추가적인 옵션과도 같은 부분이 있습니다. 

1-1. 결과에 대해 GROUP함수를 사용하여 자료를 GROUP 지을 때 사용하는 GROUP BY.

1-2. GROUP지은 결과에 대해 조건을 추가적으로 부여할 수 있는 HAVING절 (HAVING은 GROUP BY 절이 있을 때만 사용이 가능)

1-3. 결과에 대해 정렬을 할 수 있는 ORDER BY.

1-4. 자료 조회에 추가적인 조건을 부여할 수 있는 WHERE절이 있습니다. 

이러한 4가지 추가적인 사항은 필수적인 옵션은 아니며 사용자의 필요사항에 따라 추가적으로 기술될 수도 아닐 수 도 있습니다. 하지만 이러한 옵션들을 잘 사용하게 된다면 DB에 저장되어 있는 데이터를 사용자의 입맛에 맞게 꺼내올 수 있습니다.

ㅡ실습ㅡ

- 실습 환경은 Toad for Oracle에서 진행하며 Scott 계정의 EMP 테이블을 사용합니다 -

DESC EMP;

DESC 명령을 사용하면 해당 테이블의 컬럼명과 컬럼에 대한 정보들이 표시됩니다. 

EMPNO(사원번호)는 기본키 즉 PK이며 4자리의 숫자로 이루어집니다. Data Type의 타입 중 VARCHAR2 형식은 데이터가 가변형 문자열이라는 의미를 가지며 문자열의 데이터가 10byte보다 작다면 데이터의 공간도 작다는 것을 의미합니다.  

SELECT의 기본 형식 

SELECT문은 DB에 저장되어 있는 데이터를 조회하는 데 사용합니다.

FROM 절은 조회할 데이터가 저장되어 있는 테이블을 지정할 때 사용합니다.

SELECT의 기본 문법

해당 테이블의 모든 열을 조회하려면 * 을 사용하면 됩니다.

SELECT * FROM EMP;

위와 같이 EMP테이블의 모든 열 정보를 출력하게 됩니다. 하지만 EMPNO(사원번호), ENAME, JOB 만 출력하고 싶을 땐

SELECT EMPNO, ENAME, JOB FROM EMP;

컬럼 별칭 설정하기 (ALIAS)

SELECT EMPNO 사원번호, ENAME AS 이름 FROM EMP;

EMPNO에는 한 칸 이상을 띄우고 바꿀 명칭 혹은 ENAME처럼 뒤에 AS를 붙여 기술하는 방법 2가지가 존재합니다. 

tnsnames.ora는 오라클 서버로 접속할 때 필요한 프로토콜 및 포트번호, 서버주소, 서비스네임(인스턴스네임)등을 설정하는 파일입니다.

tnsnames.ora의 디폴트 경로는 $ORACLE_HOME/network/admin/ 에 위치하고 있습니다. 해당위치에도 없다면 

oracle 계정에서 lsnrctl status 을 수행하면 경로를 확인할 수 있습니다. 

[oracle@test02 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 31-AUG-2021 17:00:50

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.132)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                19-AUG-2021 17:12:27
Uptime                    11 days 23 hr. 48 min. 23 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/database/product/12.2.0.1/network/admin/listener.ora
Listener Log File         /oracle/database/diag/tnslsnr/test02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.132)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

해당 위치에서 vi 로 tnsnames.ora 파일을 수정해주면 됩니다 

서비스네임 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 연결할 IP)(PORT = 연결 링크의 포트))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = 서비스네임)
    )
  )

수정 및 추가 후 .  :wq 로 저장.

DB에서 실행시킨 쿼리의 실행속도를 확인하려면 아래와 같이  SET TIMING ON을 사용하면 된다.

SET TIMING ON
SELECT * FROM TAB;

== 결과 == 

PL/SQL procedure successfully completed.
Elapsed: 00:00:03.07

Elapsed 에서 실행 시간 결과가 출력된다. 하나의 수행속도를 알고싶으면 위와 같은 방법을 쓰면 되지만 한 블록 즉 여러개의 쿼리를 실행할때의 실행 결과를 알고싶으면 아래와 같이 사용하면 된다. 

SET TIMING ON
BEGIN
UPDATE TEST_EXAMPLE_TB SET DATA1 = 'INET1' WHERE data1 = 'index1__34344';
UPDATE TEST_EXAMPLE_TB SET DATA1 = 'IDT2' WHERE data1 = 'index1__551';
UPDATE TEST_EXAMPLE_TB SET DATA1 = 'INE3' WHERE data1 = 'index1__345545';
UPDATE TEST_EXAMPLE_TB SET DATA1 = 'IET4' WHERE data1 = 'index1__9535';
UPDATE TEST_EXAMPLE_TB SET DATA1 = 'IET5' WHERE data1 = 'index1__965555';
UPDATE TEST_EXAMPLE_TB SET DATA1 = 'IDT6' WHERE data1 = 'index1__7455';
UPDATE TEST_EXAMPLE_TB SET DATA1 = 'IDT7' WHERE data1 = 'index1__928904';
UPDATE TEST_EXAMPLE_TB SET DATA1 = 'IET8' WHERE data1 = 'index1__355600';
UPDATE TEST_EXAMPLE_TB SET DATA1 = 'IET9' WHERE data1 = 'index1__153639';
UPDATE TEST_EXAMPLE_TB SET DATA1 = 'IDE10' WHERE data1 = 'index1__876439';
END;

BEGIN ~ END를 사용해 쿼리를 묶어 실행하게 된다면 여러개의 실행 속도 결과를 출력할 수 있다. 

ORA-00054: 자원이 사용중이고, NOWAIT가 지정되어 있습니다. 라는 에러가 발생할때 해결 방법입니다. 

SELECT A.SID
     , A.SERIAL#
     , A.STATUS
  FROM V$SESSION A
     , V$LOCK B
     , DBA_OBJECTS C
 WHERE A.SID         = B.SID
   AND B.ID1         = C.OBJECT_ID
   AND B.TYPE        = 'TM'
   AND C.OBJECT_NAME = '테이블 이름';

조회된 SID값과  SERIAL 값을 KILL 해주면 정상적으로 작동되는것을 확인할 수 있습니다 

ALTER SYSTEM KILL SESSION 'SID값,SERIAL값'

5차 재난지원금 신청일

이번 5차 재난지원금의 신청일은 9월 6일부터 진행됩니다.(오프라인 신청은 13일)

이번 국민지원금(5차 재난지원금)은 국민 88%가 대상이며 1인 가구는 지난 6월 건강보험료 본인부담금이 17만원 이하면 지급 대상입니다. 4인 가구는 직장 가입자 기준 외벌이는 31만원, 맞벌이는 39만원 이하에 해당되어야 합니다.

9월 6일엔 1과 6인 신청자, 9월 7일엔 2와 7, 9월 8일엔 3과 8, 9월 9일엔 4와 9, 9월 10일엔 출생년도 끝자리가 5와 0 입니다.
 

재난지원금 사용처

이번 5차 재난지원금은 음식점은 물론 주유소와 학원, 병원, 서점, 편의점 등 일상과 관련된 매장에서 사용이 가능하며

지난 재난지원금과 동일하게 백화점, 대형마트, 세금, 보험료, 통신료에서는 사용이 불가능합니다.

또한 이번 5차 재난지원금에서 추가로 사용이 불가능하게 된 대표적인곳은 바로 스타벅스입니다.

그외에도 이케아 등 대형·외국계 업체와 백화점 외부에 있는 샤넬과 루이비통 등 명품 매장에서도 사용이 불가능합니다.
또한,  대기업이 운영하는 프랜차이즈 브랜드 본사 직영점에서는 국민지원금을 사용할 수 없고, 프랜차이즈 점주가 운영하는 가맹점에서는 사용이 가능합니다. 이번 재난지원금은 사용처를 잘 확인해보고 사용하셔야 될꺼같습니다.

For문 사용법 

FOR 증감변수 IN 초기값..최종값 LOOP 처리문; END LOOP;

아래는 테이블 생성후 그 테이블에 100만건의 데이터를 넣어보는 FOR문입니다.

테이블 생성

CREATE TABLE TEST_EXAMPLE_TB ( seq_no NUMBER NOT NULL, data1 VARCHAR2(100), data2 VARCHAR2(100) );

FOR문 실행 

DECLARE 
vn_data1 VARCHAR2(100) := 'index1__';
vn_data2 VARCHAR2(100) := 'index2__'; 
BEGIN 
FOR i IN 1..1000000 LOOP
INSERT INTO TEST_EXAMPLE_TB VALUES (i, vn_data1 || i, vn_data2 || i);
END LOOP;
COMMIT;
END;

1. 인덱스 정의

어떠한 데이터가 저장소 즉 하드 디스크에 어느 위치에 저장되어있는지 위치 정보를 가진 주소록 같은 개념이다. 이러한 인덱스가 존재한다면 DML을 실행하였을 때 보다 빠른 쿼리 검색이 가능해진다.

2. 인덱스 생성의 개념 및 원리

Table Full Scan(모든 테이블 스캔  à PGA내의 Sort Aread서 Sort정렬 공간 부족시 Temporary tablespace를 이용해 정렬 후  -> HDD Block에에 기록 즉 인덱스는 데이터가 정렬되어 들어간다.

3. 인덱스의 구조와 작동 원리(B-Tree 방법 기준 )

테이블 인덱스

테이블은 컬럼이 여러 개, 데이터가 정렬되지 않고 입력된 순서대로 들어간다

인덱스는 컬럼이  Key컬럼(사용자가 인덱스를 지정하라고 지정한 컬럼)과 ROWID컬럼 두개로 이루어져 있다. (오름차순, 내림차순 정렬 가능)

EX) SELECT * from index_test where num = 100;

만약 사용자가 데이터가 100만건이 있는 테이블의 데이터를 위와같이 SELECT문으로 조회하였을 때 먼저 서버 프로세스가 구문파싱 과정을 거친 후 DB Buffer Chache에 num가 100인 정보가 있는지 먼저 확인후 만약 해당 정보가 없다면 100이라는 정보를 가진 블록을 찾아서 DB buffer chache로 가져온 후 해당 정보를 사용자에게 보여주게 된다.

1)  Index 존재 -> where절의 조건으로 준 컬럼이 index의 Key로 생성되어있는지 확인후 인덱스에 먼저가서 100이라는 정보가 어떤 ROWID를 가지고 있는지 해당 ROWID에 있는 블록에 있는 데이터만 조회하여 DB Buffer Chache에 복사 후 사용자에게 보여지게 된다.

2)  Index X  -> 100이라는 데이터가 어떠한 위치에 있는지 위치 정보를 모르기 때문에 100만건에 대한 정보를 모두 읽게된다. 즉 100만건을 모두 DB buffer cache에 복사 후 Full Scan 방법으로 하나하나 읽으며 100 값을 찾게 된다.

Linux에서 Oracle 계정이 아닌 일반 계정에서 sqlplus 접속 방법과 sysdba계정 접속을 위한 dba권한 부여 방법을 알아보겠습니다. 

dba권한 부여는 

vi /etc/group 에서 수정하는 방법도 있지만 이 방법보다 아래의 방법으로 여러 계정을 특정 그룹에 추가할 때 더욱 유용하고 편리할 수 있습니다.

일반계정에서 sqlplus(db) 접속방법 

먼저 oracle 계정으로 접속 후 vi .bash_profile 에있는 환경변수를 복사해 줍니다.

위 내용들을 복사해준후 원하시는 계정으로 접속 후 해당 환경변수 내용을 그대로 붙여넣기 해주시면 됩니다. 수정 후

:wq로 저장하신 후에 변경내용 적용을 위한 . .bash_profile 을 입력해주시면 sqlplus 접속이 가능해집니다.

2. 일반계정에 sysdba권한 부여 방법 

일반 계정으로 sysdba의 권한을 부여하고 싶으시다면 해당 user의 group을 dba group으로 만들면 됩니다.  

 usermod -G dba [유저 네임] 을 해주시면 해당 user의 group에 dba가 추가된 것을 확인 할 수 있습니다. 

id [유저 네임] 으로 확인이 가능합니다.