Oracle 5강

programing/Oracle 2009.04.13 21:32


**서브쿼리**
ex) Jones 사원보다 더 많은 급여를 받는 사원의 사번, 이름, 급여?
1) 먼저 Jones의 급여를 확인후
>SELECT sal
>FROM emp
>WHERE ename = 'JONES'    <- Jones의 급여를 알수있음.
2) jones의 급여보다 많은 사원을 구함.
>SELECT empno, ename, sal
>FROM emp
>WHERE sal >= 2750

3) 메인쿼리와 서브쿼리를 합쳐줌
>SELECT empno, ename, sal
>FROM emp
>WHERE sal > (SELECT sal
>       FROM emp
>       WHERE ename = 'JONES');

ex) Blake 사원보다 늦게 입사한 사원의 이름, 입사일?
1) BLAKE 사원의 입사일
2) .. 보다 늦게 입사한 사원의 이름, 입사일
sub>SELECT hiredate
>FROM emp
>WHERE ename = 'BLAKE';

main>SELECT ename, hiredate
>FROM emp
>WHERE hiredate >= '81/05/01';

sub + main
>SELECT ename, hiredate
>FROM emp
>WHERE hiredate >= (SELECT hiredate
>      FROM emp
>      WHERE ename = 'BLAKE');

** 서브쿼리의 종류 **
-sub쿼리의 결과값이 1개 일경우 : Single Row Subquery
-       ''       여러개 일경우 : Multiple Row Subquery
-sub쿼리에서 구하는 컬럼이 여러개일경우 : Multiple Column Subquery
** 서브쿼리의 **
1) () 괄호안에 꼭
2) S.R.S. -> S.R.O.    single row subquery 앞에는 single row operator(비교연산자)가 온다
3) M.R.S. -> M.R.O.    multiple row subquery 앞에는 multiple row operator가 온다   ex) sal IN(10,20,30)
4) 서브쿼리 안에서는 ORDER BY 절 사용금지 (의미없는 명령어이기 때문에 에러출력)
   -단 8i R2부터 FROM절에 사용되는 Subquery의 경우엔 의미가 있으므로 사용한다.

** M.R.O. ALL, ANY **
sal >all(최대값) <all(최소값) >Any(최소값) <Any(최대값) 비교값(서브쿼리결과)
800      F       T       F        T       850
900      F       F       T       T       950
1000      F       F        T       T      1050
1100      T        F       T       F

-all의 경우 mrs가 모두만족할때 참이 된다. (800>850) and (800>950) and (800>1050)
-any의 경우 mrs가 어느하나라도 만족하면 참이된다.  (800>850) or (800>950) or (800>1050)

>all 의경우 mrs의 최대값과 비교를 하면 boolean값이 바로나오기 때문에 최대값하고만 연산을해서 속도저하가 없다.

---------------------------------------------------------------------------------------------------------------
***** VIEW *****
-view는 새로운 공간을 차지하면서 테이블을 생성하는것이 아니라 문장의 글자만을 저장!!
-FROM 뒤에는 table명과 view명이 올수 있다. DML도 VIEW에 사용가능
-table로 필요한 정보를 빼어 만들었을경우 원본 data가 업데이트 될경우 table의 내용은 틀리게된다. but view는 동기화
-물리적 table에 근거한 논리적 가상 table!!

장점 :
1) 보안
2) 엑세스의 단순화(자주사용하는 쿼리문을 만들어서 select * from viewname으로 필요할때마다 간단히 사용가능)

>create view empvw30   <--뷰 생성
>as
>select empno, ename, sal, deptno <--AS아래의 내용이 텍스트로 저장됨. (select * from (); <--괄호안에 as아래의
>from emp       내용을 넣어서 실행)
>where deptno = 30;      

>desc empvw30    <-- empvw30이라는 뷰 구조 보기

>desc user_views;          <-- 내가 생성한 뷰의 컬럼 보기

>select view_name, text   <-- 뷰의 text 보기(뷰 생성시 as에 쓴내용)
>from user_views;

*DML사용*
>insert into empvw30
>values (1111, '홍길동', 3000,30);      <-- empvw30이라는 뷰에 data를 입력

>select * from empvw30;   <-- 뷰를 확인해보면 값입력 되어있음
>select * from emp;   <-- emp TABLE을 봐도 값 입력 되어있음.
--data는 테이블만 저장가능 view에 DML명령어를 입력하면 원본 table에 적용됨.

*VIEW 종류*
1)simple view : DML이 손쉽게 가능.

2)complex view : (join, distinct, group by, group function, having )등의 명령어가 들어가는 view
   DML이 안될경우 있음.

-- complex view 만들기 --
>create view AVG_DEPT   
>AS
>select deptno, avg(sal) as AvgSAL     <-- avg(sal)은 컬럼명으로 부적합하기 때문에 alias지정 해야함.
>FROM emp
>group by deptno; 

>INSERT into AVG_DEPT   <-- 새로운 data를 입력하려 할경우 ERROR!!!발생
>values (50, 5000);   <-- view는 select명령어를 저장한것 실제 data는 table에 저장됨.
         group을 지정할경우 평균값만을 가지고 있기때문에 판단불가.

-- VIEW 수정 --
DML 에서 생성 create, 수정 alter, 삭제 drop -- BUT!!! alter는 view에서 사용불가
>create or replace view empvw30  <-- create or replace 라는 명령어를 이용해 alter를 대신함.
>as         view가 없을 경우 생성하고 있을경우 대체함.
>select empno, ename, comm, deptno
>from emp
>where deptno = 20;

-- VIEW 지우기 --
>drop view empvw30;   <-- VIEW 지우기

----------------------------------------------------------------------------------------------
***** ROWNUM, ROWID ******
>select empno, ROWNUM, ROWID  <-- ROWNUM, ROWID(6363row의 주소) 는 실제 컬럼명이 아니지만 예외적으로 사용가능
>from emp;        이것을 PSUEDO 컬럼 이라함.

>select empno, ename, sal, rownum <-- sal를 이용해 정렬하기 때문에 rownum은 처음에 번호가 메겨져서 순서가 섞임
>from emp
>order by 3 desc;

>create view v2    <-- v2라는 view생성
>as
>select empno, ename, sal
>from emp
>order by sal desc;

>select empno, ename, sal, rownum <-- v2라는 것을 테이블로 간주하기 때문에 rownum이 순서데로 메겨짐.
>from v2;

>select empno, ename, sal, rownum 
>from v2
>where rownum <= 3;   <-- rownum이 1~3까지만 출력
-이것은 VIEW를 생성한후에 다시 select절을 만드는것이므로 번거로움 그래서 -
>select empno, ename, sal, rownum
>from (select empno, ename, sal  <-- FROM절에 VIEW에 쓸내용을 서브쿼리로 테이블을 만들어줌.
>      from emp        이것을 INLINE-VIEW라고 함.
>      order by sal desc)      from 절에 사용하는 orderby는 의미가 있으므로사용가능함.
>where rownum <=3;

'programing > Oracle' 카테고리의 다른 글

Oracle 7강  (0) 2009.04.13
Oracle 6강  (0) 2009.04.13
Oracle 5강  (0) 2009.04.13
Oracle 4강  (0) 2009.04.13
Oracle 3강  (0) 2009.04.13
Oracle 2강  (0) 2009.04.13
Posted by 대절님

댓글을 달아 주세요