[SIST] Oracle_days04

1. 서브쿼리(subquery)

1) 하나의 SQL 문장의 절에 부속된(있는) 또 다른 SELECT 문장으로, 두 번의 질의를 수행해야 얻을 수 있는 결과를 한 번의 질의로 해결할 수 있는 문장이다.

 

2) subquery에는 두 종류의 연산자가 사용되며, 연산자의 오른쪽에 서브쿼리가 ( ) 묶여져 있다.

    -> 연산자 + (서브쿼리)
        - 결과로 하나의 행을 반환하는 연산자  >, <, >=, <=, < > 
        - 결과로 여러 행을 반환하는 SQL 연산자  IN, ANY, ALL 

3) 질의가 미지정된 값을 근거로 할 때 유용하다.

4) 서브쿼리는 메인쿼리의 조건으로도 사용된다.
    WHERE 조건절 안에 (서브쿼리)                   

5) 서브쿼리의 결과는 main out query에 의해 사용된다. 

6) 실행 순서 : 서브쿼리 먼저 실행 -> 그 결과를 main query에 전달하여 실행

7) WHERE, HAVING, INSERT INTO 절, UPDATE SET 절, SELECT, DELETE FROM 절에 서브쿼리를 사용할 수 있다.

8) 서브쿼리는 ORDER BY 절을 포함할 수 없다. 인라인 뷰에서는 사용할 수 있다.

9) 서브쿼리를 사용하면 성능은 저하되지만(단점), 코딩은 간결하고 쉽게할 수 있다.(장점)

---

2. 인라인뷰(Inline View)

1) FROM절 뒤에 사용되는 서브쿼리

 

2) FROM 테이블명 또는 뷰명

             ( 서브쿼리 ) -> 인라인뷰, 이렇게 와서 마치 테이블처럼 사용되어짐

---

3. WITH 절

1) 사용될 서브쿼리 블럭을 미리 선언하여 반복하여 사용할 수 있도록 함

 

2) 형식

- 여러 개의 서브쿼리를 선언하여 사용할 수 있음

 

3) WITH절을 불러서 사용하는 body 영역에서는 block명이 우선되므로, 테이블명은 사용할 수 없다.

- body는 바깥 영역, block은 () 괄호 안 영역

 

4) WITH절 내에 또 다른 WITH 절을 포함할 수 없다.

 

5) SET OPERATOR를 사용한 쿼리에서는 사용할 수 없다

 

6) WITH절을 사용하면 서브쿼리의 성능 저하를 방지할 수 있다.

 

7) 테이블 별칭 주기

- 테이블 별칭 주는 법 : FROM 테이블명 별칭

---

4. REGEXP_LIKE() 함수 활용하는 문제

문제1) insa 테이블에서 70년대생 남자사원만 아래와 같이 주민등록번호로 정렬해서 출력하세요.

실행 결과

쿼리

---

문제2) insa 테이블에서 70년대 12월생 모든 사원 아래와 같이 주민등록번호로 정렬해서 출력하세요.

---

5. LIKE의 ESCAPE에 대한 설명과 DML문 다루기

[LIKE의 ESCAPE]

- whildcard(%와 _ LIKE 연산자 기호)를 일반 문자처럼 쓰고 싶은 경우에 ESCAPE 옵션을 사용한다.

 

문제1) dept 테이블에서 부서명(dname)에 '%' 문자를 포함하는 부서정보 출력

---

[DML문 다루기]

- DML은 데이터 조작어로 테이블에 새로운 행을 추가, 삭제, 수정, 객체 이름 변경, 데이터 및 저장공간을 회수할 때 사용한다. -> INSERT, DELETE, UPDATE, RENAME, TUNCATE 문

 

[INSERT문 형식]

INSERT INTO 테이블명 (컬럼명..) VALUES (컬럼값..);
COMMIT;

 

[UPDATE 형식]

UPDATE 테이블명
SET 수정할 컬럼명 = 새로운 컬럼명[, 수정할 컬럼명 = 새로운 컬럼명, 수정할 컬럼명 = 새로운 컬럼명...][WHERE]

 

[DELETE 형식]

DELETE FROM 테이블명;   ->WHERE 조건 절이 없으면 모든 행(레코드) 삭제
[WHERE]  -> 조건식은 고유한키로 주어야 좋음 (loc를 주었을 때 다른 부서도 BOSTON에 있을 수 있기 때문에...)

 

주의! COMMIT을 해버리면 ROLLBACK이 되지 않기 때문에 DML문을 실행한 뒤 테이블을 확인해보고 COMMIT 하기

---

[DML문 예제]

[새로운 부서 추가하기]

 

주의할 점)

- 고유한 키값과 중복되는 값은 추가할 수 없다

- 컬럼값에 지정된 정밀도 값보다 더 큰 값을 넣을 수 없다.

---

[30번 부서의 부서명을 SALES -> SA%LES로 수정하기]

주의할점)

WHERE 절을 주지 않으면 모든 값이 다 바뀌게 된다.

---

[40번 부서, 50번 부서 삭제하기]

혹시라도 실수로 삭제 후 COMMIT을 해버렸다면.. 아래와 같이 INSERT문으로 다시 40번 부서를 추가하기

---

[여러 개의 부서 삭제하기]

---

[30번 부서명 SA%LES를 SALES로 수정하기]

 

- 오라클에도 Java의 replace() 메서드와 같은 함수가 있다. => REPLACE(문자열, 바꾸려고 하는 값, 바꿀 값)

* 는 모든 것을 의미하기 때문에 모든 컬럼을 출력하고 싶다면 스키마명.테이블명으로 코딩해야한다.

---

['ev' 문자열 포함하는 사원 정보를 출력하기 -> [ev] ]

<span color = blue> ev </span>

위와 같이 HTML 태그를 사용하면 찾고자 하는 단어의 색깔을 바꿀 수 있다고 한다.

---

[dept 테이블에서 부서명에 'r' 문자열을 포함하면 부서번호를 1증가 시키기]

 

쿼리를 작성한다면 아래와 같다.

 

하지만 실제로 쿼리를 실행시켜보면 에러가 발생한다. 에러 메세지는 다음과 같다.

 

- 에러 메시지 : ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found

- 해석 : 자식 레코드행에서 무결성 제약 조건 FK(외래키)에 위배되는 것을 찾았다.

 

즉, dept 테이블이 먼저 만들어지고 난 뒤 deptno를 PK(고유키)로 지정을 하고 emp 테이블을 만든 다음 dept 테이블의 deptno를 FK(외래키, 또는 참조키)로 지정을 했기 때문에 부서번호 컬럼인 deptno를 수정할 수 없는 것이다.

여기서 dept 테이블은 부모 테이블(parent)이고, emp 테이블은 자식 테이블(child)이다.

---

6. 오라클에서 현재 시스템의 날짜 / 시간 정보 가져오기

- SYSDATE 사용

결과 : 22/04/07  21

 

- CURRENT_DATE

 

- CURRENT_TIMESTAMP

---

7. 문자의 바이트를 알려주는 함수 : VSIZE()

---

8. NVL(), NVL2() 사용하는 문제 - 복습

---

9. 오라클 연산자

1) 비교 연산자

- WHERE 절에서 사용

 

- 숫자, 문자, 날짜의 크기나 순서를 비교하는 연산자이며, true / false / null 을 반환한다.

 

- 종류 : >,  >=,  <,  <=,  !=,  <>,  ^=,  ANY,  SOME,  ALL

(ANY, SOME, ALL은 서브쿼리 자세히 다룰 때 다시 배울 예정)

 

- LOB 자료형은 비교연산자를 사용할 수 없지만, PL/SQL에서는 CLOB 자료형은 데이터를 비교할 수 있다.

(자료형 설명할 때 다시 배울 예정)

 

2) 논리 연산자

- WHERE절에서 사용

 

- true, false, null을 반환

 

- 종류 : AND, OR, NOT

 

3) SQL 연산자

[종류]

- [NOT] IN (list) : list가 있는지 없는지 확인해주는 연산자

 

- [NOT] BETWEEN a AND b : a와 b 값의 사이에 있는지 확인해주는 연산자(a,b 포함 / b 값이 a 값보다 커야함)

 

- [NOT] LIKE : 찾고자 하는 문자열이 있는지 확인해주는 연산자 

 

- IS [NOT] NULL : NULL의 유무를 확인해주는 연산자 -> NULL 연산자

 

- ANY, SOME, ALL은 SQL 연산자 + 비교 연산자 -> 나중에 다시 자세히 설명

 

- EXISTS SQL 연산자  - WHERE (상관(상호연관, Correlated) 서브쿼리 값이 존재하면 true 반환) -> 나중에 다시 자세히 설명

ex) where EXISTS (select 'x' from dept 
                         where deptno=p.deptno);
       [NOT] EXISTS == [NOT] IN -> EXISTS 조건 대신 IN 조건을 사용해서 표현할 수도 있다.

 

4) 연결 연산자

- 문자열을 연결할 때 사용하는 연산자

 

- 종류 : ||

 

- 함수로는 CONCAT()을 사용해도 된다.

 

5) 산술 연산자

- 종류 : +  -  *  /

 

- 나머지를 구하는 산술 연산자는 없다. 나머지 구하는 것은 MOD() 함수를 사용해야 한다.

- Java와 동일하게 정수나 실수를 0으로 나누면 에러가 발생하지만, MOD 함수를 사용할 때는 에러가 발생하지 않는다.

 

- 나머지를 구하는 함수에는 REMAINDER()도 있다.

  차이점은 MOD는 FLOOR() 함수를 이용하고, REMAINDER()는 ROUND() 함수를 이용한다.

  FLOOR() 함수는 절삭 함수이고, ROUND() 함수는 반올림 함수이다.

 

6) SET(집합) 연산자 -> 내일 이어서 더 배울 예정

- UNION 연산자     - 합집합

 

- UNION ALL 연산자 - 합집합 + ALL

- INTERSECT 연산자 - 교집합

 

- MINUS 연산자     - 차집합

---

산술연산자를 다루면서 추가적으로 배운 내용 2가지 dual과 시노님(synonym)

10. dual 이란?

- SYS 관리자 계정이 소유하고 있는 테이블(table)이다.

 

- dual을 오라클 표준 테이블이라고 한다

.

- 행(레코드) 1개, 열(컬럼) 1개인 dummy 테이블이다.

 

- 일시적으로 날짜 연산, 산술 연산을 할 때 자주 사용한다.

 

- 스키마명.테이블명(sys.dual) 이렇게 사용해야 되는데 시노님(synonym)을 설정했기 때문에 테이블명만 바로 써서 사용할 수 있다.

 

- dual 테이블은 오라클 설치시 자동으로 생성되는 테이블이다.

---

11. 시노님(synonym) 이란?

- 먼저, 다른 계정의 테이블을 사용하려면 권한을 부여받아 사용할 수 있다.

 

- 권한은 SYS나 테이블을 소유하고 있는 소유자 계정에게 권한을 받으면 되는데 궁극적으로 소유자 계정에게 받는 것이 좋다.

 

- 권한을 부여 받으면 스키마.테이블명으로 코딩을 해야 사용할 수 있다. -> 이렇게 코딩하는 것이 번거로워 시노님(synonym)이 나왔다.

 

[시노님 설명]

1) 시노님은 하나의 객체에 대해 다른 이름을 정의하는 방법으로 즉, 스키마.테이블명으로 코딩하는 것을 다른 이름으로 간단하게 지정하는 것이 시노님(synonym)이다.

 

2) 시노님은 DB 전체에서 사용할 수 있는 객체이다.

 

3) 시노님의 종류는 2가지

- PRIVATE 시노님 : 소유자만 접근 가능

- PUBLIC 시노님 : 모든 사용자가 접근 가능

 

4) 시노님의 생성

- PUBLIC 시노님은 모든 사용자가 접근 가능하기 때문에 생성 및 삭제는 오직 DBA만이 할 수 있다.

 

- PUBLIC 시노님의 생성 순서
 (1) SYSTEM / SYS 권한으로 접속한다. - SYS 접속
 (2) PUBLIC 옵션을 사용하여 시노님을 생성한다. - SYS 접속
 (3) 생성된 시노님에 대해 객체 소유자로 접속한다. - SCOTT 접속(소유자)
 (4) 시노님에 권한을 부여한다. - SCOTT 접속(소유자)

 

5) 시노님 삭제는 DBA만 가능하다. -> SYS 계정

---

시노님 관련 설명 예제)

HR 계정에서 SCOTT 계정이 소유한 emp 테이블에 접근하려고 하면 할 수 없다.

아래와 같이 SCOTT 계정에서 emp 테이블을 SELECT 할 수 있도록 권한 부여를 하자

 

 

권한 부여를 하면 스키마.테이블명으로 접근할 수 있다.

 

SYS 계정에서 SCOTT계정이 소유하고 있는 emp 테이블의 시노님을 생성하자

 

HR 계정에서 시노님을 사용하여 emp 테이블에 접근할 수 있다.

 

단, SELECT만 가능한 권한을 부여했기 때문에 삭제하거나 수정하는 다른 작업은 할 수 없다.