WITH문을 사용하여 자유자재로 쿼리를 뷰처럼 재사용할 수 있는 유용한 쿼리 Tip
*WITH 문의 기본 문장
WITH name AS ( 쿼리 )
WITH viewData AS
(
SELECT *
FROM TestTable_1
)
SELECT *
FROM viewData;
WITH문에 다중 쿼리를 사용하는 CASE
WITH viewData1 AS
(
SELECT *
FROM TestTable_1
),
viewData2 AS
(
SELECT *
FROM TestTable_2
)
SELECT V1.id,
V2.name,
V2.address
FROM viewData1 V1,
viewData2 V2
WHERE V1.id = V2.id
AND V1.name LIKE 'KIIM%';
좀 더 여러 테이블을 뷰 처럼 활용하는 CASE
WITH viewData1 AS
(
SELECT *
FROM TestTable_1
),
viewData2 AS
(
SELECT *
FROM TestTable_2
),
viewData3 AS
(
SELECT V1.id,
V2.name,
V2.address
FROM viewData1 V1,
viewData2 V2
WHERE V1.id = V2.id
AND V1.name LIKE 'KIM%'
)
SELECT *
FROM viewData3
WHERE ROWNUM <= 100;
--ROLLUP() 함수 : GROUP BY 집계데이타에 대해 소계를 추가 ==> 오라클 구문
: GROUP BY + ORDERY 결과에 소계를 추가한 것과 동일한 효과 임
EX)
SELECT DNAME, JOB, COUNT(*) "TOTAL EMPL" , SUM(SAL) "TOTAL SAL"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP(DNAME, JOB);
--CUBE() 함수 (MS-SQL : WITH CUBE )
- 다차원집계 가능 , ROLLUP은 SUBTOTAL만을 생성
GROUPING SET()함수
GROUPING SETS에 표시된 인수들에 대한 개별집계를 구할 수 있는 함수
EX)
SELECT
DNAME,JOB, COUNT(*) , SUM(SAL)
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY GROUPING SETS(DNAME, JOB);
결과)
DNAME JOB COUNT(*) SUM(SAL)
=============================
NULL CLERK 4 4150
NULL SALESMAN 4 5600
NULL PRESIDENT 1 5000
NULL MANAGER 3 8275
NULL ANALYST 2 6000
ACCOUNTING NULL 3 8750
RESEARCH NULL 5 10875
SALES NULL 6 9400
위 쿼리와 같은 결과를 얻기 위한 GROUP BY 쿼리
EX)
SELECT
DNAME, 'ALL JOB' JOB, COUNT(*) , SUM(SAL)
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME
UNION ALL
SELECT
'ALL JOB' DNAME, JOB, COUNT(*) , SUM(SAL)
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY JOB;
-- ANY (OR 기능)
WHERE 나이 >= (SELECT 나이 FROM .......)
-- GROUP BY ALL (WHERE 절과 일치 하지 않는 내용은 NULL 로 표시)
SELECT 주소, AVG(나이) AS 나이 FROM MEMBER
WHERE 성별='남'
GROUP BY ALL 주소
-- 모든 주소는 나오며 성별에 따라 나이 데이터는 NULL
-- WITH ROLLUP ==> MSSQL 구문
SELECT 생일, 주소, SUM(나이) AS 나이
FROM MEMBER
GROUP BY 생일, 주소 WITH ROLLUP
-- 생일 과 주소를 요약행이 만들어짐
-- GROUPING(컬럼명) ROLLUP 또는 CUBE 의 요약행인지 여부 판단(요약행이면 1 아니면 0)
: 즉 ROLLUP 이나 CUBE에 의한 소계가 계산된 결과에는 GROUPING(EXPR) = 1 이 표시되고 그 외의 결과
는 0이 표시됨
SELECT 생일, 주소, GROUPING(생일) AS 생일요약행여부
-- COMPUTE (GROUP BY 와 상관없이 별도의 테이블로 요약정보 생성)
SELECT 생일, 나이
FROM MEMBER
COMPUTE SUM(나이), AVG(나이)
FIRST_VALUE() 함수 (*오라클에만 있는 함수) : RANK 부여 후 가장먼저 나온 값을 출력한다.
FIRST_VALUE() 함수 (*오라클에만 있는 함수) : RANK 부여 후 가장 나중에 나온 값을 출력한다.
EX)
SELECT DEPTNO, ENAME, SAL,
FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED
PRECEDING)AS DEPT_RICH,
LAST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS BETWEEN CURRENT
ROW AND UNBOUNDED FOLLOWING) AS DEPT_POOR
FROM EMP;
결과)
DEPTNO NAME SAL DEPT_RICH DEPT_POOR
----------------------------------------------------------------
10 KING 5000 KING MILLER
10 CLARK 2450 KING MILLER
10 MILLER 1300 KING MILLER
20 SCOTT 3000 SCOTT SMITH
20 FORD 3000 SCOTT SMITH
20 JONES 2975 SCOTT SMITH
20 ADAMS 1100 SCOTT SMITH
20 SMITH 800 SCOTT SMITH
LEAD() 함수 : 특정데이타 바로 다음의 데이타를 출력 함 (MSSQL 은 지원안됨)
EX)
SELECT ENAME , HIREDATE, LEAD(HIREDATE,1) OVER (ORDER BY HIREDATE) AS "NEXTHIRED"
FROM EMP;
결과)
ENAME HIREDATE NEXTHIRED
----------------------------------------
SMITH 80/12/17 81/02/20
ALLEN 81/02/20 81/02/22
WARD 81/02/22 81/04/02
JONES 81/04/02 81/05/01
BLAKE 81/05/01 81/06/09
CLARK 81/06/09 81/09/08
RATIO_TO_REPORT() 함수 : 특정SUM 컬럼 값에 대한 백분율을 소수점으로 구함 (MS-SQL 은 지원안됨)
EX)
SELECT ENAME, SAL ,ROUND(RATIO_TO_REPORT(SAL) OVER() , 2) AS R_R
FROM EMP
WHERE JOB='SALESMAN';
ENAME SAL R_R
----------------------------
ALLEN 1600 0.29
WARD 1250 0.22
MARTIN 1250 0.22
TURNER 1500 0.27
PERCENT_RANK() 함수 : 값이 아닌 행의 순서별 백분율을 구함 (MS-SQL 은 지원안됨)
EX)
SELECT DEPTNO, ENAME, SAL,
PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS P_R
FROM EMP;
-- PIVOT (세로 컬럼을 가로 변경)
EX)
학년/ 반 / 학생수
1 1 40
1 2 45
2 1 30
2 2 40
3 1 10
3 2 10
위와 같이 SCHOOL 테이블이 있다면
SELECT 그룹할컬럼명, [열로변환시킬 행]
FROM 테이블
PIVOT(
SUM(검색할열)
FOR 옆으로만들 컬럼명
IN([열로변환시킬 행])
) AS 별칭
--실제 쿼리는
SELECT 학년, [1반], [2반]
FROM SCHOOL
PIVOT(
SUM(학생수)
FOR 반
IN([1반], [2반])
) AS PVT
-- UNPIVOT (가로 컬럼을 세로로)
SELECT 학년, 반, 학생수
FROM SCHOOL
UNPIVOT(
FOR 반
IN( [1반], [2반] )
) AS UNPVT
-- RANK (순위)
SELECT 컬럼명, RANK() OVER( ORDER BY 순위 기준 컬럼명) AS 순위
FROM 테이블
EX)
SELECT JOB, ENAME, SAL,
RANK() OVER (ORDER BY SAL DESC) ALL_RANK, -- 같은 값인 경우 동일한 순위 부여
RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK -- 업무내에서만 순위를 부여
FROM EMP;
-- DENSE_RANK () : RANK 함수와 동일하나 동일한 순위를 하나의 건수로 취급 하는 것이 틀린 점
EX)
SELECT JOB, ENAME, SAL,
RANK() OVER (ORDER BY SAL DESC) RANK, --동일한 순위가 존재하면 2건 인정
DENSE_RANK() OVER (ORDER BY SAL DESC) DENSE_RANK -- 동일순위가 2개 존재하더라도 1건
ROW_NUMBER() OVER (ORDER BY SAL DESC)ROWNUMBER_RANK -- 동일순위를 임의로 다르게 부여
FROM EMP;
결과)
JOB NAME SAL RANK DENSE_RANK ROWNUMBER_RANK
-----------------------------------------------------------------------------------------
PRESIDENT KING 5000 1 1 1
ANALYST FORD 3000 2 2 2
ANALYST SCOTT 3000 2 2 3
MANAGER JONES 2975 4 3 4 ===> 2위가 2건 존재할때 다음 순위 다름
-- PARTITION BY (그룹별로 순위 생성)
SELECT 컬럼명, RANK() OVER( PARTITION BY 그룹기준컬러명 ORDER BY 순위기준컬럼명) AS 순위
FROM 테이블
-- FULL OUTER JOIN (LEFT 조인과 RIGHT 조인을 합한것)
양쪽 어느 하나라도 데이가 있으면 나옴
-- ROW_NUMBER (순차번호 생성)
SELECT ROW_NUMBER() OVER( ORDER BY 기준열) AS 번호, 컬럼명
FROM 테이블
--Stored Procedure(저장 프로시저)
** 데이타베이스내에서 SQL 명령을 컴파일할때 캐시를 이용할 수 있으므로 처리가 매우 빠르다
반복적으로 SQL 명령을 실행할 경우 매회 명령마다 네트워크를 경유할 필요가 없다
어플리케이션마다 새로 만들 필요없이 이미 만들어진 프로시저를 반복 사용한다
데이타베이스 로직을 수정시 프로시저는 서버측에 있으므로 어플리케이션을 다시 컴파일할 필요가 없다
** 저장 프로시저의 소스 코드를 보고 싶으면 SP_HELPTEXT 프로시저명 을 사용한다
CREATE PROC 프로시저명 AS SQL문 /* 저장 프로시저 */
CREATE PROC 프로시저명 변수선언 AS SQL문 /* 인수를 가지는 저장 프로시저 */
CREATE PROC 프로시저명 WITH ENCRYPTION AS SQL문 /* 저장 프로시저 보안 설정 */
CREATE PROC 프로시저명 /* RETURN 값을 가지는 저장 프로시저 */
인수1 데이타형, ... 인수2 데이타형 OUTPUT
AS
SQL문
RETURN 리턴값
DROP PROCEDURE 프로시저명1, 프로시저명2, ... /* 저장 프로시저 삭제 */
명령어
BEGIN ... END /* 문장의 블록 지정 */
DECLARE @변수명 데이타형 /* 변수 선언 */
SET @변수명=값 /* 변수에 값 지정 */
PRINT @변수명 /* 한개의 변수 출력 */
SELECT @변수1, @변수2 /* 여러개의 변수 출력 */
IF 조건 /* 조건 수행 */
수행1
ELSE
수행2
WHILE 조건1 /* 반복 수행 */
BEGIN
IF 조건2
BREAK - WHILE 루프를 빠져 나간다
CONTINUE - 수행을 처리하지 않고 조건1로 되돌아간다
수행
END
EXEC 저장프로시저 /* SQL문을 실행 */
EXEC @(변수로 지정된 SQL문)
GO /* BATCH를 구분 지정 */
예제
1. 기본 저장 프로시저
CREATE PROC pUpdateSalary AS UPDATE Employee SET salary=salary*2
2. 인수를 가지는 저장 프로시저
CREATE PROC pUpdateSalary
@mul float=2, @mul2 int
AS
UPDATE Employee SET salary=salary* @Mul* @mul2
EXEC pUpdateSalary 0.5, 2 /* 모든 변수에 값을 대입 */
EXEC pUpdateSalary @mul2=2 /* 원하는 변수에만 값을 대입 */
3. 리턴값을 가지는 저장 프로시저
CREATE PROC pToday
@Today varchar(4) OUTPUT
AS
SELECT @Today=CONVERT(varchar(2), DATEPART(dd, GETDATE()))
RETURN @Today
DECLARE @answer varchar(4)
EXEC pToday @answer OUTPUT
SELECT @answer AS 오늘날짜
4. 변수 선언과 대입, 출력
** @는 사용자 변수이고 @@는 시스템에서 사용하는 변수이다
DECLARE @EmpNum int, @t_name VARCHAR(20)
SET @EmpNum=10
SET @t_name = '강우정'
SELECT @EmpNum
이런식으로 다중입력도 가능함.
SELECT @no = no, @name = name, @level = level
FROM OF_Member
WHERE userId ='"
4. Trigger(트리거)
** 한 테이블의 데이타가 편집(INSERT/UPDATE/DELETE)된 경우에 자동으로 다른 테이블의
데이타를 삽입, 수정, 삭제한다
** 트리거 내용을 보고 싶으면 SP_HELPTRIGGER 트리거명 을 사용한다
CREATE TRIGGER 트리거명 on 테이블명 FOR INSERT AS SQL문 /* INSERT 작업이 수행될때 */
CREATE TRIGGER 트리거명 on 테이블명 AFTER UPDATE AS SQL문 /* UPDATE 작업이 수행되고 난 후 */
CREATE TRIGGER 트리거명 on 테이블명 INSTEAD OF DELETE AS SQL문
DROP TRIGGER 트리거명
5. Cursor(커서)
** SELECT로 가져온 결과들을 하나씩 읽어들여 처리하고 싶을때 사용한다
** 커서의 사용방법은 OPEN, FETCH, CLOSE, DEALLOCATE등 4단계를 거친다
** FETCH에는 NEXT, PRIOR, FIRST, LAST, ABSOLUTE {n / @nvar}, RELATIVE {n / @nvar}가 있다
SET NOCOUNT on /* SQL문의 영향을 받은 행수를 나타내는 메시지를 숨긴다 */
DECLARE cStatus SCROLL CURSOR /* 앞뒤로 움직이는 커서 선언 */
FOR
SELECT ID, Year, City FROM aPlane
FOR READ on LY
OPEN cStatus /* 커서를 연다 */
DECLARE @ID varchar(50), @Year int, @City varchar(50), @Status char(1)
FETCH FROM cStatus INTO @ID, @Year, @City /* 커서에서 데이타를 하나씩 가져온다 */
WHILE @@FETCH_STATUS=0 /* 커서가 가르키는 결과의 끝까지 */
BEGIN
IF @Year <= 5 SET @Status='A'
ELSE IF @Year> 6 AND @Year <= 9 SET @Status='B'
ELSE SET @Status='C'
INSERT INTO aPlane(ID, City, Status) VALUES(@ID, @Year, @Status)
FETCH FROM cStatus INTO @ID, @Year, @City /* 커서에서 데이타를 하나씩 가져온다 */
END
CLOSE cStaus /* 커서를 닫는다 */
DEALLOCATE cStatus /* 커서를 해제한다 */
댓글을 달아 주세요