AniBumiRami의 블로그

2-7. DCL

1. DCL 개요

• 유저를 생성하고 권한을 제어할 수 있는 DCL(DATA CONTROL LANGUAGE) 명령어

2. 유저와 권한

  가. 유저 생성과 시스템 권한 부여

• 유저 생성 명령어 : CREATE USER PJS IDENTIFIED BY KOREA7;

• 유저생성 권한 부여 : GRANT CREATE USER TO SCOTT;

• 로그인 할 수 있도록 권한 부여 : GRANT CREATE SESSION TO PJS;

• 테이블 생성 권한 부여 : GRANT CREATE TABLE TO PJS;

  나. OBJECT에 대한 권한 부여

• 다른 접속자에게 테이블을 SELECT 할 수 있는 권한 부여 : GRANT SELECT ON MENU TO SCOTT;

3. ROLE을 이용한 권한 부여

  • ROLE 생성 명령어 : CREATE ROLE LOGIN_TABLE;

  • ROLE에 권한 부여 : GRANT CREATE SESSION, CREATE TABLE TO LOGIN_TABLE;

  • 유저에서 ROLE 부여 : GRANT LOGIN_TABLE TO PJS;

2-8. 절차형 SQL

1. 절차형 SQL 개요

  • 절차형 SQL을 이용하면 SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈을 생성할 수 있다.

2. PL/SQL 개요

  가. PL/SQL 특징

• ORACLE의 PL/SQL은 BLOCK 구조로 되어있고 BLOCK 내에는 DML 문장과 QUERY 문장, 그리고 절차형 언어(IF, LOOP) 등을 사용할 수 있으며, 절차적 프로그래밍을 가능하게 하는 트랜잭션 언어이다.

1. PL/SQL은 BLOCK 구조로 되어있어 각 기능별 모듈화가 가능하다.

2. 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환한다.

3. IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다.

4. DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.

5. PL/SQL은 ORACLE에 내장되어 있으므로 ORACLE과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다.

6. PL/SQL은 응용 프로그램의 성능을 향상시킨다.

7. PL/SQL은 여러 SQL 문장을 BLOCK으로 묶고 한 번에 BLOCK 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.

  나. PL/SQL 구조

  다. PL/SQL 기본 문법 (SYNTAX)

• 프로시저 생성 명령어 :

CREATE [OR REPLACE] PROCEDURE [PROCEDURE_NAME] (ARGUMENT1 [MODE] DATA_TYPE1, ARGUMENT2 [MODE] DATE_TYPE2, …. ..) IS [AS] … … BEGIN … … EXCEPTION … … END; /

• 프로시저 삭제 명령어 :

DROP PROCEDURE [PROCEDURE_NAME];

3. T-SQL 개요

  가. T-SQL 특징

• 근본적으로 SQL Server를 제어하기 위한 언어로서, MS사에서 ANSI/ISO 표준의 SQL에 약간의 기능을 더 추가해 보완적으로 만든 것이다.

  나. T-SQL 구조

  다. T-SQL 기본 문법 (SYSTAX)

• 프로시저 생성 명령어

CREATE PROCEDURE [SCHEMA_NAME.]PROCEDURE_NAME @PARAMETER1 DATA_TYPE1 [MODE], @PARAMETER2 DATA_TYPE2 [MODE], … … WITH AS … … BEGIN … … ERROR 처리… … END;

• 프로시저 삭제 명령어

DROP PROCEDURE [SCHEMA_NAME.]PROCEDURE_NAME;

4. PROCEDURE의 생성과 활용

• 프로시저 생성 명령어 :

create or replace procedure p_dept_insert (v_deptno in number, v_dname in varchar2, v_loc in varchar2, v_result out varchar2)

is cnt number := 0;

begin select count(*) into cnt from dept where deptno = v_deptno and rownum =1;

if cnt>0 then v_result :='이미 등록된 부서번호이다';

else insert into dept (deptno, dname, loc) values (v_deptno, v_dname, v_loc);

commit;

v_result :='입력완료~!';

end if;

exception when others then rollback;

v_result :='ERROR 발생';

end;

/

5. USER DEFINED FUNCTION의 생성과 활용

  • 데이터베이스 내에 저장해 놓은 명령문의 집합을 의미, 사용자가 별도의 함수를 만들 수 있다.

  • 예) K-리그 8월 경기결과와 두 팀간의 점수차를 ABS 함수를 사용하여 절대값으로 출력한다. (내장함수 ABS 사용) 

select sche_date 경기일자, hometeam_id || '-' || awayteam_id 팀들, 

home_score || '-' || away_score SCORE,

abs(home_score - away_score) 점수차

from schedule

where gubun= 'Y'

and sche_date between '20120801' and '20120831'

order by sche_date;

  • UTIL_ABS 라는 사용자 정의 함수 생성

create or replace function util_abs (v_input in number) 

return number is v_return number :=0;

begin if v_input < 0 then v_return := v_input * -1;

else v_return := v_input;

end if;

return v_return;

end;

/

  • 같은 쿼리문을 사용자 정의 함수를 사용

select sche_date 경기일자, hometeam_id || '-' || awayteam_id 팀들, 

home_score || '-' || away_score SCORE, 

util_abs(home_score - away_score) 점수차

from schedule

where gubun = 'Y'

and sche_date between '20120801' and '20120831'

order by sche_date;

  • 결과같은 동일하다.

6. TRIGGER의 생성과 활용

  • Trigger 란?

○ 특정한 테이블에 INSERT, UPDATE, DELETE 와 같은 DML문이 수행되었을 때, DB에서 자동으로 동작하도록 작성된 프로그램

○ Trigger 는 데이터베이스에 의해 자동 호출되지만 하나의 트랜잭션 안에서 일어나는 일련의 작업이다.

즉, 하나의 트랜잭션이 rollback 이나 commit 이 일어나면 Trigger도 같이 rollback이나 commit 이 실행 된다.

  • Trigger 생성 소스

create or replace trigger summary_sales

after insert on order_list for each row declare

o_date order_list.order_date%type;

o_prod order_list.product%type;

begin o_date := :NEW.order_date;

o_prod := :NEW.product;

update sales_per_date set qty=qty + :NEW.qty, 

amount = amount + :NEW.amount where sale_date = o_date 

and product = o_prod;

if sql%notfound then insert into sales_per_date values(o_date, o_prod, :NEW.qty, :NEW.amount);

end if;

end;

/

7. 프로시저와 트리거의 차이점

출처 : SQL 전문가 가이드 교재

2-6. 윈도우 함수

1. WINDOW FUNCTION 개요

  • 복잡한 프로그램을 하나의 SQL 문장으로 쉽게 해결할 수 있다.

  • WINDOW FUNCTION SYNTAX

• WINDOW 함수에는 OVER 문구가 키워드로 필수 포함된다.

• SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ( [PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절] ) FROM 테이블 명;

® ARGUMENTS (인수) : 함수에 따라 0~N개의 인수가 지정될 수 있다.

® PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.

® ORDER BY 절 : 어떤 항목에 대해 순위를 지정할 지 ORDER BY 절을 기술한다.

® WINDOWING 절 : 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다. 

ROWS는 물리적인 결과 행의 수를, RANGE는 논리적인 값에 의한 범위를 나타내는데, 둘 중 하나를 선택해서 사용할 수 있다.

SQL Server 에서는 지원하지 않는다.

2. 그룹 내 순위 함수

  가. RANK 함수

• 특정 항목에 대한 순위를 구하는 함수

• 특정 범위 (PARTITION) 내에서 순위를 구할 수 있고, 전체 데이터에 대한 순위를 구할 수도 있다.

• 동일한 값에 대해서는 동일한 순위를 부여하게 된다.

• 예) 

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 함수와 흡사하나, 동일한 순위를 하나의 건수로 취급하는 것이 틀린 점이다.

• 예) 

select job, ename, sal, 

 rank() over (order by sal desc) RANK,

 dense_rank() over (order by sal desc) DENSE_RANK

 from emp;

  다. ROW_NUMBER 함수

• 동일한 값이라도 고유한 순위를 부여한다.

3. 일반 집계 함수

  가. SUM 함수

• 파티션별 윈도우의 합을 구한다.

• 예)

select mgr, ename, sal, sum(sal) over (partition by mgr) MGR_SUM from emp;

  나. MAX 함수

• 파티션별 윈도우의 최대값을 구한다.

  다. MIN 함수

• 파티션별 윈도우의 최소값을 구한다.

  라. AVG 함수

• 파티션별 원하는 조건에 맞는 데이터에 대한 통계값을 구한다.

• 예)

select mgr, ename, hiredate, sal,

 round(avg(sal) over (partition by mgr order by hiredate rows between 1 preceding and 1 following)) as MGR_AVG

from emp;

• ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING : 현재 행을 기준으로 파티션 내에서 앞의 한 건, 현재 행, 뒤의 한 건을 범위로 지정한다.

  마. COUNT 함수

• 예)

select ename, sal,

count(*) over (order by sal range between 50 preceding and 150 following) as SIM_CNT

from emp;

• RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING : 현재 행의 급여값을 기준으로 급여가 -50 에서 +150 의 범위 내에 포함된 모든 행이 대상이 된다.

4. 그룹 내 행 순서 함수

  가. FIRST_VALUE 함수

• 파티션별 윈도우에서 가장 먼저 나온 값을 구한다.

• 예) 부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 먼저 나온 값을 출력한다.

select deptno, ename, sal,

first_value(ename) over (partition by deptno order by sal desc rows unbounded preceding) as DEPT_RICH

from emp;

• RANGE UNBOUNDED PRECEDING : 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정한다.

• 같은 값이 존재할 경우 내부적으로 ORDER BY 절을 이용하여 정렬한다.

  나. LAST_VALUE 함수

• 예) 부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 마지막에 나온 값을 출력한다.

select deptno, ename, sal, 

last_value(ename) over (partition by deptno order by sal desc rows between current row and unbounded following) as DEPT_POOR

from emp;

• ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING : 현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를 지정한다.

  다. LAG 함수

• 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다.

• SQL Server 에서는 지원하지 않는다.

• 예)

select ename, hiredate, sal,

lag(sal, 2, 0) over (order by hiredate) as PREV_SAL

from emp where job = 'SALESMAN';

• LAG(SAL, 2, 0)의 기능은 두 행 앞의 SALARY를 가져오고, 가져올 값이 없는 경우 0으로 처리한다.

  라. LEAD 함수

• 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있다.

• SQL Server 에서는 지원하지 않는다.

• 예) 직원들을 입사일자가 빠른 기준으로 정렬을 하고, 바로 다음에 입사한 인력의 입사일자를 함께 출력한다.

select ename, hiredate,

lead(hiredate, 1) over (order by hiredate) as "NEXTHIRED" 

from emp;

5. 그룹 내 비율 함수

  가. RATIO_TO_REPORT 함수

• 파티션 내 전체 SUM값에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있다.

• 결과 값은 >0 & <= 1 의 범위를 가진다.

• 예) JOB이 SALESMAN인 사원들을 대상으로 전체 급여에서 본인이 차지하는 비율을 출력한다.

select ename, sal,

round(ratio_to_report(sal) over (), 2) as R_R

from emp 

where job='SALESMAN';

  나. PERCENT_RANK 함수

• 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여, 행의 순서별 백분율을 구한다.

• 예) 같은 부서 소속 사원들의 집합에서 본인의 급여가 순서상 몇 번째 위치쯤에 있는지 0과 1 사이의 값으로 출력한다.

select deptno, ename, sal,

percent_rank() over (partition by deptno order by sal desc) as P_R

from emp;

  다. CUME_DIST 함수

• 파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구한다.

• 예) 같은 부서 소속 사원들의 집합에서 본인의 급여가 누적 순서상 몇 번째 위치쯤에 있는지 0과 1 사이의 값으로 출력한다.

select deptno, ename, sal, 

cume_dist() over (partition by deptno order by sal desc) as CUME_DIST

from emp;

  라. NTILE 함수

• 파티션별 전체 건수를 ARGUMENT 값으로 N 등분한 결과를 구할 수 있다.

• 예)

select ename, sal, ntile(4) over (order by sal desc) as QUAR_TILE from emp;

위 예제에서 NTILE(4)의 의미는 14명의 팀원을 4개 조로 나눈다는 의미이다. 전체 14명을 4개의 집합으로 나누면 몫이 3명, 나머지가 2명이 된다. 나머지 2명은 앞의 조부터 할당 된다. 즉, 4명 + 4명 + 3명 + 3명으로 조를 나누게 된다.

출처 : SQL 전문가 가이드 교재

2-4. 서브쿼리

○ 서브쿼리를 사용할 때 주의사항

1. 서브쿼리를 괄호로 감싸서 사용한다.

2. 서브쿼리는 단일 행(Single Row) 또는 복수 행(Multiple Row) 비교 연산자와 함께 사용 가능하다. 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하이어야 하고 복수 행 비교 연산자는 서브쿼리의 결과 건수와 상관 없다.

3. 서브쿼리에서는 ORDER BY를 사용하지 못하다. ORDER BY 절은 SELECT 절에서 오직 한 개만 올 수 있기 때문에 ORDER BY 절은 메인쿼리의 마지막 문장에 위치해야 한다.

1. 단일 행 서브 쿼리

2. 다중 행 서브쿼리

3. 다중 칼럼 서브쿼리

  • 서브쿼리의 결과로 여러 개의 칼럼이 반환되어 메인쿼리의 조건과 동시에 비교되는것을 의미한다.

  • 예) SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE (TEAM_ID, HEIGHT) IN (SELECT TEAM_ID, MIN(HEIGHT) FROM PLAYER GROUP BY TEAM_ID) ORDER BY TEAM_ID, PLAYER_NAME;

4. 연관 서브쿼리

  • 서브쿼리 내에 메인쿼리 칼럼이 사용된 서브쿼리

  • 예) SELECT STADIUM_ID, STADIUM_NAME 경기장명 FROM STADIUM a WHERE EXISTS (SELECT 1 FROM SCHEDULE X WHERE X.STADIUM_ID = A.STADIUM AND X.SCHE_DATE BETWEEN '20120501' AND '20120502');

5. 그밖에 위치에서 사용하는 서브쿼리

  가. SELECT 절에 서브쿼리 사용하기

  나. FROM 절에서 서브쿼리 사용하기

§ 인라인 뷰(INLINE VIEW) 라고 하며, 서브쿼리의 결과가 마치 실행 시에 동적으로 생성된 테이블인 것처럼 사용할 수 있다.

§ 예) SELECT T.TEAM_NAME 팀명, P.PLAYER_NAME 선수명, P.BACK_NO 백넘버 FROM (SELECT TEAM_ID, PLAYER_NAME, BACK_NO FROM PLAYER WHERE POSITION = 'MF' ) P, TEAM T WHERE P.TEAM_ID = T.TEAM_ID ORDER BY 선수명;

  다. HAVING 절에서 서브쿼리 사용하기

§ 그룹함수와 함께 사용될 때 그룹핑된 결과에 대해 부가적인 조건을 주기 위해서 사용한다.

§ 예) SELECT P.TEAM_ID 팀코드, T.TEAM_NAME 팀명, AVG(P.HEIGHT) 평균키 FROM PLAYER P, TEAM T WHERE P.TEAM_ID = T.TEAM_ID GROUP BY P.TEAM_ID, T.TEAM_NAME HAVING AVG(P.HEIGHT) < (SELECT AVG(HEIGHT) FROM PLAYER WHERE TEAM_ID = 'K02);

  라. UPDATE 문의 SET 절에 사용하기

• 예) UPDATE TEAM A SET A.STADIUM_NAME = (SELECT X.STADIUM_NAME FROM STADIUM X WHERE X.STADIUM_ID = A.STADIUM_ID);

• 서브쿼리의 결과가 NULL 을 반환할 경우 해당 컬럼의 결과가 NULL 이 될 수 있기 때문에 주의해야 한다.

  마. INSERT 문의 VALUES 절에서 사용하기

• 예) PLAYER 테이블에 '홍길동' 이라는 선수를 삽입하고자 한다. 이때 PLAYER_ID 값을 현재 사용중인 PLAYER_ID에 1을 더한 값으로 넣고자 한다.

INSERT INTO PLAYER (PLAYER_ID, PLAYER_NAME, TEAM_ID) VALUES ((SELECT TO_CHAR(MAX(TO_NUMBER(PLAYER_ID))+1) FROM PLAYER), '홍길동', 'K06');

6. 뷰 (VIEW)

  • 기본 구조 : CREATE VUEW V_PLAYER_TEAM AS SELECT P.PLAYER_NAME, P.POSITION, P.BACK_NO, P.TEAM_ID, T.TEAM_NAME FROM PLAYER P, TEAM T WHERE P.TEAM_ID = T.TEAM_ID;

2-5. 그룹 함수

1. 데이터 분석 개요

  • ANSI/ISO SQL 표준은 데이터 분석을 위해 다음 세 가지 함수를 정의하고 있다.

1. AGGREGATE FUNCTION

• COUNT, SUM, AVG, MAX, MIN 외 각종 집계 함수들이 포함

2. GROUP FUNCTION

• 소계, 중계, 합계, 총 합계 등 여러 레벨의 그룹 간의 합계를 계산할 때 쓰인다.

• ROLLUP, CUBE, GROUPING SETS

3. WINDOW FUNCTION

• 분석 함수 (ANALYTIC FUNCTION), 순위 함수 (RANK FUNCTION)

2. ROLLUP 함수

  • ROLLUP에 지정된 Grouping Column의 List는 Subtotal 을 생성하기 위해 사용되어지며, Grouping Columns의 수를 N이라고 했을 때 N+1 Level 의 Subtotal 이 생성된다. 중요한 것은 Rollup의 인수는 계층 구조이므로 인수 순서가 바뀌면 수행 결과도 바뀌게 되므로 인수의 순서에도 주의해야 한다.

  • 예) 

select dname, job, count(*) "Total Empl", sum(sal) "Total Sal" 

 from emp, dept 

 where dept.deptno=emp.deptno

 group by rollup(dname, job);

  • ROLLUP 함수 결합 칼럼 사용 예제

select dname, job, mgr, sum(sal) "Total Sal" 

 from emp, dept 

 where dept.deptno=emp.deptno

 group by rollup(dname, (job, mgr));

3. CUBE 함수

  • 결합 가능한 모든 값에 대하여 다차원 집계를 생성한다.

  • 내부적으로 GROUPING COLUMN의 순서를 바꾸어서 또 한 번의 QUERY를 추가 수행해야 한다.

  • ROLLUP에 비해 시스템에 많은 부담을 주므로 사용에 주의해야 한다.

  • 예)

select case grouping(dname) 

 when 1 

 then 'All Departments' 

 else dname

 end as dname,

 case grouping(job)

 when 1

 then 'All Jobs'

 else job

 end as job,

 count(*) "Total Empl", sum(sal) "Total Sal"

 from emp, dept

 where dept.deptno=emp.deptno

 group by cube (dname, job);

4. GROUPING SETS 함수

  • GROUP BY SQL 문장을 여러 번 반복하지 않아도 원하는 결과를 쉽게 얻을 수 있다.

  • 표시된 인수들 간에는 계층 구조인 ROLLUP 과는 달리 평등한 관계이므로 인수의 순서가 바뀌어도 결과는 같다.

  • 예)

select decode(grouping(dname), 1, 'All departments', dname) as dname,

 decode(grouping(job), 1, 'All jobs', job) as job,

 count(*) "Total Empl", sum(sal) "Total Sal"

 from emp, dept

 where dept.deptno = emp.deptno

 group by grouping sets (dname, job);

출처 : SQL 전문가 가이드 교재