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 대절님

댓글을 달아 주세요



티스토리 툴바