[DB/Oracle] ORACLE 개념

1. ROWNUM / ROWID

-ROWNUM은 where 절에 의해 추출된 data에 ROW단위로 붙는 순번으로 자료의 순서를 다른 목적으로 

이용하고자 할때 유용하게 쓰이는 정보이다.

-where절에 ROWNUM을 조건이 들어갈때는 항상 다른 조건을 모두 만족시킨 data에 의해서 조건이 반영된다.

 

 

 

또한 where절에 rownum으로 < > = <= >= 등의 연산자를 붙여서 조건절을 만들면 rownum <(=)  "" 이외에는

다른조건이 붙지 않는것을 확인할 수 있다. 

만약 rownum > 5 또는 rownum = 5 와 같은 행을 보고 싶다면 IN LINE VIEW를 이용하여 자료를 한번더 가공해야 한다.

 

** IN LINE VIEW 

 - 서브쿼리가 FROM절 안에서 사용되는 경우 해당 서브쿼리를 '인라인뷰'라고 한다.

 - FROM절에서 사용된 서브쿼리의 결과가 하나의 테이블에 대한 VIEW처럼 사용된다.

 [ SELECT * FROM ( SELECT * FROM TABLE ORDER BY ID DESC) WHERE ROWNUM < 5;

 

2. ROWID : DATABASE 내의 모든 ROW가 가지는 유일한 식별자로 select를 통해 확인할 수 있지만 UPDATE 하거나, INSERT하는것은 불가능 하다.

 

3. DECODE : 프로그래밍 언어의 IF문을 SQL문장 또는 PL/SQL안으로 끌어들여 사용하기 위해 만들어진 오라클 함수로 select 시 decode는 from을 제외하고 어디서든지 사용할 수 있다. DECODE와 IF문의 대표적인 표현 방법을 한번 알아보자

 

DECODE의 부등호 비교는 위에 보시다시피  값이 같으냐 아니냐를 비교할수 있도록 되어있다.

부등호 비교의경우는  DECODE함수를 이용하되, 다른 함수를 함께 사용하여 대소비교를 마치 등호비교 하듯이 해야한다

이때 주로 사용되는 함수는 주로 SIGN, LEAST, GREATEST등 이 있다.

SIGN : 값이 음수인지, 양수인지, 0 인지를 나타내며,

LEAST : 주어진 값중 최소값을 리턴

GREATEST : 주어진값중 최대값을 리턴한다.

 

SIGN 함수를 사용하여, 두 값을 뺴서 양수인지 음수인지 0인지를 통해 비교하였다.

OR 또는 IN비교 : DECODE로 바로 표현할 수 있다.

AND비교문

 

중첩 IF문 : DECODE(A,B,DECODE(A,C,1),2) 이런식으로 DECODE안에 DECODE를 다시 사용한다. 

 

 

NOT IN : IN 연산자와 반대 개념으로 포함되어 있지 않은 값을 찾는다.

NOT IN 을 보기 전에 IN을 보면 아래에 해당한 쿼리문을 보면 조건절에서

HOBBY IN ( null, '등산') 의경우는 

HOBBY = null or HOBBY = '등산' 과 동일하며, 이렇게되면 HOBBY = NULL이란 조건은 항상 FALSE

가 RETURN 된다 NULL의 경우는 IS NULL 또는 IS NOT NULL로 비교되어야 한다.

NOT IN 에서의 경우는 NOT IN (null, '낚시') 는  HOBBY <> null and HOBBY <> '낚시' 와 동일한데 

이경우는 HOBBY <> null 이 항상 FASLE가 되므로 해당 결과 값이 하나도 나올수 없게 된다.

 

 

 

HOBBY = '등산'인결과만 출력되었음

''

HOBBY <> NULL 이 항상 FALSE이므로 결과가 하나도 출력되지 않음

 

NOT IN ('낚시')와 HOBBY IS NOT NULL의 결과&nbsp;

 

IN LINE VIEW

INLINE VIEW를 이용하는데, CREATE를 시키지 않고 SQL안에 기술하는 방식이다.

VIEW를 CREATE시켜 놓으면 자주 사용해야 사용가치가 있는데, 한번 쓰고 사용하지 않을거라면 VIEW를 만들어서

사용할 가치가 없다.

일회성으로 쓰이는 VIEW를 SQL안으로 포함시켜서 편리하게 사용하자는 의도에서 INLINE VIEW를 사용한다.

 

위의 쿼리문은 사원의 연봉이 해당 사원의 직급의 평균 연봉보다 높은 사람을 나타내는 쿼리문인데, 여기서

평균연봉 까지 나타낼경우 join을 시켜 줘야 하는데 이경우에 INLINE VIEW 를 사용하여 쉽게 나타낼수 있다.

이경우 서브쿼리를 통해서 나온 결과값을 한개의 테이블로 사용하여 나타낸다.

 

 서브쿼리로 계급에 따른 평균연봉을 나타내는 서브쿼리를 만들어 해당 결과물을 B로 나타내 주고

해당 쿼리에서 나온 결과값을 조건문에거 a.salary > b.avg_sal 을 통해서 연봉이 평균연봉보다 많은 사람을 조건을 붙ㅇ주었다.

 

//문제2

 

VIEW : 내가 원하는 커럼들은 모아서 저장하여 사용하는 것으로, CREATE하여 사용해야 한다.

 

[VIEW 생성/수정 형식]

CREATE [OR REPLACE] VIEW 생성할VIEW이름

AS

SELECT문

[VIEW 삭제]

DROP VIEW 삭제할VIEW이름

 

 

 

HIERACHICAL SELECT

CONNECT BY .....   START WITH 형식은 

자료의 구조가 계층적으로 이루어진 경우 상위자료에서 하위자료의 자료전개 및 하위 자료에서 상위자료의 역전개를

위하여 사용되는 SELECT 이다

TDEPT를 예로 들자면, 부모노드의 DEPT_CODE = 자식노드의 PARENT_DEPT의 논리식이

CONNECT BY PRIOR DEPT_CODE = PARENT_DEPT으로 표현된다.

 

START WITH구문은 전개 또는 역전개시 전개를 시작할행을 지정해주는 문장이다.

예를들어 START WITH DEPT_CODE = '000000'을 지정하면 DEPT_CODE가 '000000'인 행부터 전개를 시작하라는 의미가 된다.

 

 

아래의 CONNECT BY문의 결과를 보면

이런 계층적인 구조를 나타낼때

CONNECT BY PRIOR 부모의 코드 = 자식의 부모코드

START WITH 코드 = '' (코드를 나타낼 시작위치지정)

 

Connect by PRIOR dept_code = parent_code를 해석하자면

기준이 되는 dept_code = '000000'이 parent_code인 것들을 쭉쭉 찾아가는것이고,

[ '000000'을 부모로 갖는 'AA0001,BA0001,CA0001' 또다시 'AA0001'을 부모로 갖는 'AB0001,AC0001' 계층적으로 계쏙 나간다.][부모의 자식을 찾아간다]

 

 

where 절을 추가할 경우 해당 CA0001를 부모 dept_code로 가지고 있던것들이 BA0001에 포함되어나오는데

CONNECT BY PRIOR ~ START WITH 사이에 조건을 AND로 추가하니 

'CA0001'을 상위부서로 갖는 부서들은 포함되지 않았다. 

 

--역전개로써 자신의 상위부서를 찾는방법이다.

전개하는 방법과 달라진것은

connect by prior parent_dept = dept_code 의 순서가 바뀌었다.

Connect by PRIOR parent_code = dept_code는

기준이 되는 dept_code = 'CD0001'의 부모 값을 쭉쭉 찾아가는것이다.

['CD0001'의 부모를 찾고 'CA0001'의 부모를 찾는 '000000' ] [자식의 부모를 찾아간다]

 

 

//문제

--부서별로 부서장과 성명을 보여주는데 부서의 전개에 의해 도출된 결과와 같은 형식으로 보는 SQL문을 작성하라

 

HINTS : select, delete, update, insert등의 DML을 수행시에 OPTIMIZER가 관여하게 되는데, OPTIMIZER는 수행하고자 하는DML을 가장 효율적으로 처리할 수 있는 최적화를 찾게되는데, 어떤 테이블을 먼저 읽을 것인지, 테이블을 읽을떄 인덱스를 이용할지 등 여러요인을 고려하여 실행계획이 작성되면 이 실행계획에 의해서 DML이 수행된다.

OPTIMIZER가 선택한 최적화 경로인 수행결로를 PLAN을 이용해 들여다 볼수있다.

HINTS는 이렇게 OPTIMIZER가 실행계획을 작성하는 단계에 인위적으로 개발자가 관여 하겠다는 의도를 가질때 사용된다.

 

 

 

[출처] https://m.blog.daum.net/why_i_am/14?category=2113847