01 절차형 SQL 작성하기
1. 프로시저
1.1 프로시저의 개념
: 절차형 SQL을 활용하여 특정 기능 수행할 수 있는 트랜잭션 언어
호출을 통해 실행, 데이터 조작어를 수행
1.2 프로시저 구성
1.3 프로시저 상세
1) 선언부
: 프로시저 명칭, 변수, 인수, 데이터 타입 정의
CREATE [OR REPLACE] PROCEDURE 프로시저_명
(파라미터_명 [MODE-IN/OUT/INOUT] 데이터_타입
)
IS
변수 선언
2) 시작/종료부
: 프로시저의 시작, 종료 표현
BEGIN : 프로시저 시작
END : 프로시저 끝
3) 제어부
: 조건문, 반복문 이용하여 처리
--조건부
IF 조건 THEN
문장;
ELSE 조건 THEN
문장;
ELSE
문장;
END IF;
--케이스문
CASE 변수
WHEN 값1 THEN
SET 명령어;
ELSE;
SET 명령어;
END CASE;
--반복문
LOOP
문장;
EXIT WHEN 탈출조건;
END LOOP;
WHILE 반복조건 LOOP
문장;
EXIT WHEN 탈출조건;
END LOOP;
FOR 인덱스 IN 시작값...종료값
LOOP;
문장;
END LOOP;
4) 프로시저 SQL
: DML을 주로 사용
SELECT : 조회
INSERT : 생성
UPDATE : 변경
DELETE : 삭제
5) 예외부
실행 중 발생가능한 예외상황을 수행
EXECPTION
WHEN 조건 THEN
SET 명령어;6) 실행부
DBMS의 적용, 취소 여부를 결정
COMMIT : 하나의 트랜잭션이 성공적으로 끝나고 일관성이 있을 때 사용
ROLLBACK : 하나의 트랜잭션이 비정상적으로 종료되어 원자성이 깨질 때 다시 시작, 연산 취소
1.4 프로시저 호출문 작성
EXECUTE 프로시저명(파라미터)
2. 사용자 정의함수
2.1 사용자 정의함수 개념
: 절차형 SQL을 활용하여 SQL 처리를 수행하고 수행 결과를 단일 값으로 반환
2.2 사용자 정의함수 구성
종료 시 단일 값을 반환
2.3 사용자 정의함수 구문
1) 선언부
CREATE [OR REPLACE] FUNCTION 함수명
(파라미터_명 [MODE-IN/OUT/INOUT] 데이터_타입
)
IS
RETURN 데이터_타입
변수 선언2) 시작/종료부
정의함수의 실행 시작과 종료
3) 제어부
단위 블록별 실행흐름을 제어하는 부분
4)SQL
조회, 추가, 수정, 삭제
5) 예외부
실행 중 발생 가능한 예외상황
6) 반환부
사용자 정의함수를 호출한 쿼리에 반환하는 단일값을 정의
2.4 사용자 정의함수 호출문 작성
함수명(파라미터)
3. 트리거
3.1 트리거의 개념
: 특정 테이블에 삽입, 수정, 삭제 등 데이터 변경 이벤트가 발생하면 DBMS에서 자동적으로 실행되도록 구현된 프로그램
3.2 트리거의 목적
특정 테이블에 대한 데이터 변경을 시작점으로 설정, 작업을 자동적으로 수행하기 위해
데이터 무결성 유지, 로그 메시지 출력 등 별도 처리를 위해
3.3 트리거의 종류
행 트리거 : 데이터 변화가 생길 때마다 실행
문장 트리거 : 트리거에 의해 단 한 번 실행
3.4 트리거의 구성
: EVENT 명령어를 통해 트리거 실행을 위한 이벤트를 인지
3.5 트리거의 구문
1) 선언부
CREATE [OR REPLACE] TRIGGER 트리거명2) 이벤트부
순서 유형 ON 테이블명
[FOR EACH ROW]BEFORE : DML 수행 전에 트리거 실행하도록 지정
AFTER : DML가 성공적으로 실행되었을 때만 트리거 실행하도록 지정
3) 시작/종료부
정의함수의 실행, 종료를 알려줌
4) 제어부
단위 블록별 실행흐름을 제어하는 부분
5) SQL
데이터 관리를 위한 조회, 추가, 수정, 삭제를 수행
6) 예외부
실행 중 발생 가능한 예외 상황을 수행
02 응용 SQL 작성하기
1 집계성 SQL 작성
1.1 데이터 분석 함수의 개념
: 총합,평균 등의 데이터 분석을 위해 복수 행 기준의 데이터를 모아서 처리하는 목적
1.2 데이터 분석 함수의 종류
1.3 집계 함수
1) 집계 함수의 개념
: 여러 행 또는 테이블 전체 행으로부터 하나의 결괏값을 반환하는 함수
2) 집계 함수 구문
GROUP BY 컬럼
HAVING 조건식3) 집계 함수의 종류
COUNT, SUM, AVG, MAX, MIN, STDDEV, VARIAN
1.4 그룹 함수
1) 그룹 함수의 개념
: 테이블 전체 행을 하나 이상의 컬럼을 기준으로 컬럼 값에 따라 그룹화하여 그룹별로 결과를 출력
2) 그룹 함수의 유형
--ROLLUP(중간 집계 값 산출)
GROUP BY 컬럼 ROLLUP 컬럼
HAVING 조건식
--CUBE(다차원 집계를 생성)
GROUP BY 컬럼 CUBE 컬럼
HAVING 조건식
--GROUPING SETS(개별 집계)
GROUP BY 컬럼 GROUPING SETS 컬럼
HAVING 조건식1.5 윈도 함수
1) 윈도 함수의 개념
데이터베이스를 사용한 온라인 분석 처리 용도
OLAP
2) 윈도 함수의 구문
3) 윈도 함수의 분류
순위 함수, 행순서 함수, 그룹 내 비율 함수
2. 특정 기능 수행 SQL문 작성
2.1 응용시스템 DBMS 접속 기술
1) 자바 데이터베이스 연결
JDBC : SQL을 사용하여 DBMS에 질의, 데이터 조작하는 API 제공
2) MyBatis
: SQL 쿼리를 XML 파일로 분리하여 매핑
2.2 MyBatis 작성 문법
1) SQL 문장의 입력 파라미터 사용 방법
#{파라미터명}
2) 동적 SQL
<if 조건>
</if>
3) 절차형 SQL 호출
프로시저 호출 가능
3 데이터 제어어 명령문 작성
3.1 데이터 제어어(DCL)의 개념
데이터 보안, 무결성 유지, 병행 제어, 회복을 위해 DBA가 사용하는 제어용 언어
3.2 데이터 제어어의 유형
GRANT : 사용 권한 부여
REVOKE : 사용 권한 취소
3.3 데이터 제어어(DCL) 명령문
1) GRANT 명령어
GRANT 권한 ON 테이블 TO 사용자
2) REVOKE 명령어
REVOKE 권한 ON 테이블 FROM 사용자