Oracle day6

# Oracle 중요문제 Mission4 리뷰(10/4)

# 4번 문제(고급)

-- 4.상환계획(TREPAY_PLAN) 테이블에서, 현재 대출 중인 계좌의 오늘 이후 이번 달 입금 계획을 검색하세요.
-- trepay_plan, tacct 테이블 사용
-- 현재 연체 중인 계좌 상환이 완료된 계좌는 제외, DLQ_CNT IS NULL AND REPAY IS NULL
-- SYSDATE 함수를 사용하고, 검색결과는 아래와 다를 수 있음
-- PAY_DT가 이번달 (2022년 10월)에 해당되는 행 검색
-- 검색결과는 PAY_DT, LNACT, LNACT_SEQ 정렬

SELECT * 
  FROM trepay_plan a -- M의 집합
 WHERE pay_dt BETWEEN TRUNC(SYSDATE,'DD') -- 이번달의 첫번째 날짜 리턴
                           -- 
                  AND TRUNC(ADD_MONTHS(SYSDATE,1),'MM')-1/86400
   AND  (lnact,lnact_seq) IN (SELECT lnact,lnact_seq
                                FROM tacct
                               WHERE dlq_dt IS NULL 
                                 AND repay  IS NULL) -- 1의 집합
ORDER BY pay_dt, lnact, lnact_seq ;

 

# EXISTS

• 하나라도 만족하는 행이 있으면 작업을 멈춘다.

• 값의 존재 유무를 판단하여 참이냐 거짓이냐를 판단하는 루프문이라고 생각하면 된다.

select 'YES'
  from dual
       -- EXISTS : 서브 쿼리를 실행해서 검색되는 행이 있냐 없냐, 행의 존재 유무를 평가
 where EXISTS (select * from emp where deptno = 10); -- F
               -- 3개의 행을 검색
               -- 조건에 만족하는 행을 하나만이라도 발견하면 멈춤
               -- and rownum = 1

 

1. 집합 연산자

 

• SELECT 리스트의 표현식은 개수가 일치해야 합니다.

• 후속 query에 있는 각 열의 데이터 유형은 첫번째 query 에 있는 상응하는 열의 데이터 유형과 일치해야 합니다.

• ORDER BY 절은 명령문의 맨 끝에만 올 수 있습니다.

• 중복 행은 UNION ALL 외에는 자동으로 제거됩니다.

• 첫번째 query의 열 이름이 결과에 나타납니다.

 

# union(업무에서 사용 권장 x, 성능 ↓)

union

• union연산자는 중복 행을 제거한 후 양쪽 query에서 행을 반환합니다.

select * from emp where deptno in (10, 30)
union -- 두 문장을 이어줌
select * from emp where deptno in (20, 30);

조건 결과

# union all

union all

• union all 연산자는 모든 중복 행을 포함하여 양쪽 query의 결과를 반환합니다.

SELECT DEPTNO, JOB, SUM(SAL) 
  FROM EMP 
 GROUP BY DEPTNO, JOB 
UNION ALL 
SELECT DEPTNO, NULL, SUM(SAL) 
           -- null은 자릿수 맞추기 위해사용.
  FROM EMP 
 GROUP BY DEPTNO ;

결과화면

 

# minus

minus

• minus연산자는 첫번째 query에 의해 선택되지만 두번째 query 결과 집합에는 없는 모든 구분 행을 반환합니다.

SELECT * 
  FROM EMP 
 WHERE DEPTNO IN (20,30)
MINUS -- 차집합
SELECT * 
  FROM EMP 
 WHERE DEPTNO IN (10,30);

결과 화면

# intersect

intersect

• intersect연산자는 양쪽 query에 공통되는 행을 반환합니다.

• 겹치지 않는거 반환

SELECT branch, COUNT(ln_amt)
FROM tacct
WHERE ln_dt BETWEEN '2020/01/01' AND '2020/12/31' 
AND lmt_typ IS NULL 
GROUP BY branch
INTERSECT
SELECT branch, COUNT(ln_amt)
FROM tacct
WHERE ln_dt BETWEEN '2021/01/01' AND '2021/12/31' 
AND lmt_typ IS NULL 
GROUP BY branch 
ORDER BY 1 ;

결과화면

2. 유저 관리

# 유저 생성 및 암호 변경

• DBA는 CREATE USER 문을 사용하여 유저를 생성합니다.

show user; -- 현재 로그인된 유저 확인

결과화면

 

# 권한이 없다면 생성 불가.

create user GIL
identified by ORACLE;

권한이 없음

 

# 현재 계정 권한 확인.

select * from session_privs; -- 현재 유저 권한 확인.

현재 권한 확인

# 현재 계정 접속

• 사용자 GIL은 CREATE SESSION 권한을 가지고 있지 않습니다. 로그인이 거절됨.

•  유저는 생성했지만 DB에 로그인할 수 있는 기본적인 권한을 준적이 없다.

 

# create session 권한 부여.

grant create session to GIL;

 

# 권한이 없으면 생성도 조회도 불가.

create table t1(ID NUMBER);
select * from TEST.EMP;

결과화면

# 생성, 조회 권한부여.

grant create table to GIL;
grant select on test.EMP to GIL;

 

# CONNECT,  RESOURCE 권한 부여.

   CONNECT :  접속 권한

   RESOURCE : (생성, 수정, 삭제), 데이터(입력, 수정, 조회, 삭제) 권한 

grant connect, resource to GIL;