오늘 정리
서브쿼리?? 쿼리 문 안의 select문을 말하며, 인라인뷰는 from절에 들어가고 하나의 테이블처럼 사용된다.(사용되는 컬럼명은 주쿼리에서 사용이 가능) 서브쿼리는 조건절에 들어가고, 변수(값)처럼 사용된다. 중첩 서브쿼리는 where문에 나타나며, 조건을 설정해줄 때 사용한다.
복습
[🧠힌트문]
- 힌트문에 오타 있으면 무시됨.
- RDBMS가 세운 실행계획보다 개발자가 세운 실행 계획이 옳다고 판단될 때 옵티마이저에게 내 생각(개발자의)을 전달할 수 있는 유일한 방법이다.
[ℹ️인덱스]
- 인덱스 여부에 따라 실행계획이 달라질 수 있다. (힌트문을 통해 인덱스 조회 조건 설정 가능)
--조회 조건 대상이 인덱스가 있을때
SELECT
empno, ename
FROM emp
WHERE empno = 7566 ;
--조회 조건 대상이 인덱스가 없을때
SELECT
empno, ename
FROM emp
WHERE ename = 'SMITH';
- pk가 아닌 일밤 컬럼도 인덱스를 가질 수 있다.
-
DDL 구문 사용
--인덱스 생성 CREATE INDEX i_ename ON emp(ename ASC);
-
강의 내용
[select문 실습]
- 선분 조건 : 범위 스캔, 구간 검색이 필요한 경우!
- 조건 검색 시 사용자 입력 값 조회 → 두 번째 select 문의 경우, 사용자 입력 값으로 시작하는 단어 조회
--문제3:사원의 이름이 A로 시작되는 사원들의 사원번호를 출력하시오.
--Like A%(A로 시작), %A(A로 끝), _A(두번째가 A) 등
SELECT ename,empno
FROM emp
WHERE ename LIKE 'A%';
SELECT ename,empno
FROM emp
WHERE ename LIKE :x||'%';[서브쿼리]
- from절 인라인뷰(테이블 자리 → 주쿼리에서 컬럼명 사용 가능)
- 조건절 서브쿼리(값의 자리)
--1.temp에서 연봉이 가장 많은 직원의 row를 찾아서 이 금액과 동일한 금액을
--받는 직원의 사번과 성명을 출력하시오.(by 서브쿼리)
SELECT max(salary) FROM temp;
SELECT emp_id, emp_name, salary
FROM temp
WHERE salary = 100000000;
SELECT emp_id, emp_name, salary
FROM temp
WHERE salary =
(
SELECT max(salary)
FROM temp
);[함수 만들기]
- 자주 쓰는 부분은 DDL을 통해 함수로 만들어 사용할 수 있음.
CREATE OR REPLACE FUNCTION func_crate(pdate varchar2)
RETURN number
IS
tmp number;
BEGIN
tmp :=0;
SELECT crate INTO tmp
FROM test02
WHERE cdate = (
SELECT max(cdate)
FROM test02
WHERE cdate<pdate
);
RETURN tmp;
END;
- 오라클 저장된 함수 확인 방법(상단 탭 : 스키마 브라우저 → function으로 탭 설정)
[쿼리문 작성 시]
- 가능하면 테이블은 한 번만 읽어서 처리한다.
- 경우의 수를 줄이기(카디널리티) → 인라인 뷰, 조인하기 전 그룹화
- 그룹핑을 하면서 그룹함수가 필요한 부분이 있다면 같이 해도 된다.(sum(decode 패턴))
- 왜 조인을 하나?
- 가상데이터를 조인할때는 그룹화하기전에 조인하기
- 실습
--(조건)날짜별, (조건)물품구분별 (그룹)판매개수와 (그룹)판매가격!!
-- +전체 총계, 물품별 소계
--그룹함수 계산이 안되는 것 -> 날짜, 물품구분
--그룹함수 되는 것->판매개수, 판매가격
--1)전체 조회
SELECT *
FROM T_ORDERBASKET;
--2)조회 시 참고해야하는 값 조회
SELECT indate_vc, gubun_vc,qty_nu, qty_nu*price_nu
FROM T_ORDERBASKET;
--3)조인할 더미테이블 조회
--왜 3개의 로우가 있는 테이블과 조인하는가?
--롤업을 사용하지 않고, 추가하기 위해서
--현재 조회테이블 아래에 소계, 총계까지 추가하여 세 종류의 로우가 필요하기에!
SELECT '1' NO FROM dual
UNION ALL
SELECT '2' FROM dual
UNION ALL
SELECT '3' FROM dual;
--4)조인해보기
SELECT qty_nu, qty_nu*price_nu
FROM T_ORDERBASKET,
(SELECT '1' NO FROM dual
UNION ALL
SELECT '2' FROM dual
UNION ALL
SELECT '3' FROM dual
)b;
--5)조인 후 decode 조건으로 rownum 별 설정하기
--1은 판매날짜, 물품구분, 판매개수, 판매가격 다 출력
--2는 판매날짜(소계)물품구분, 판매개수, 판매가격 출력
--3은 판매날짜(총계),판매개수,판매가격 출력
SELECT decode(b.NO,'1',indate_vc,'2','소계','3','총계') AS "판매날짜"
,decode(b.NO,'1',gubun_vc,'2',gubun_vc) AS "물품구분"
,sum(qty_nu)||'개' AS "판매개수"
,to_char(sum(qty_nu*price_nu),'999,999')||'원' AS "판매가격"
FROM T_ORDERBASKET,
(SELECT '1' NO FROM dual
UNION ALL
SELECT '2' FROM dual
UNION ALL
SELECT '3' FROM dual
)b
GROUP BY decode(b.NO,'1',indate_vc,'2','소계','3','총계')
,decode(b.NO,'1',gubun_vc,'2',gubun_vc)
ORDER BY decode(b.NO,'1',indate_vc,'2','소계','3','총계')
,decode(b.NO,'1',gubun_vc,'2',gubun_vc);
강의 마무리
- 내일은 join 학습(natural, self, outer, non-equal join)
