[2020 정보처리기사 실기 암기] 8. 절차형 SQL 작성하기, 응용 SQL 작성하기

* 프로시저 구성

 선언부, 시작/종료부, 제어부, SQL, 예외부, 실행부

 

BEGIN : 프로시저의 시작, END : 프로시저의 끝

 

* 프로시저 사용자 정의 함수

 SELECT(조회), INSERT(생성), UPDATE(변경), DELETE(삭제)

 

* DB프로시저 

  절차형 SQL을 활용하여 특정 기능을 수행할 수 있는 트랜잭션 언어

 

* 프로시저 선언부

  IN : 운영체제에서 프로세저로 값을 전달하는 모드

  OUT : 프로세저에서 처리된 결과를 운영체제로 전달하는 모드

  INOUT : IN과 OUT의 두가지 기능을 동시에 수행하는 모드

 

* 프로시저 실행부

  COMMIT : 하나의 트랜잭션이 성공적으로 끝났을 때 사용

  ROLLBACK : 부분적으로 연산을 취소하는 연산

 

* DB 사용자 정의함수

  절차형 SQL을 활용하여 일련의 SQL 처리 수행, 결과를 단일 값으로 반환할 수 있는 절차형 SQL

 

* 사용자 정의함수 구성

  선언부, 시작/종료부, 제어부, SQL, 예외부, 반환부

 

* 트리거

  특정 테이블에 삽입, 수정, 삭제 등의 데이터 변경 이벤트 발생 시 DBMS에서 자동적으로 실행되도록 구현

 

* 트리거 종류

  행트리거 : 데이터 변화가 생길 때마다 실행

  문장트리거 : 트리거에 의해 단 한 번 실행

 

* OR REPLACE : 사용자 정의함수가 이미 존재할 때 컴파일 에러가 발생하지 않도록 하는 키워드

   CREATE : DBMS 내 객체 생성

   [MODE] : 변수 입출력 구분, IN OUT INOUT으로 구성

   IS/(AS) : PL/SQL의 블록 시작

 

* DML 트리거 실행 순서 전후 관계

  AFTER : INSERT, UPDATE, DELETE 수행 전에 트리거 실행되도록 지정

  BEFORE : INSERT, UPDATE, DELETE 성공적으로 실행되었을 때만 트리거 실행하도록 지정

 

* 집계 함수 명

  여러 행 또는 테이블 전체 행으로부터 하나의 결괏값을 반환하는 함수

  COUNT : 복수 행의 줄 수

  SUM :  복수 행의 해당 컬럼 간의 합계

  AVG : 복수 행의 해당 컬럼 간의 평균

  MAX, MIN, STDDEV(표준편차), VARIAN(분산)

 

* 데이터 분석 함수 종류

  집계함수 : 여러 행 또는 테이블 전체 행으로부터 하나의 결괏값 반환

  그룹함수 : 소그룹 간의 소계 및 중계 등 중간 합계 분석 데이터 산출

  윈도함수 : 데이터 베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해 표준 SQL에 추가된 기능

 

* 그룹함수

  ROLL UP : 중간 집계 값

  CUBE : 다차원 지비계

  GROUPING SET : 개별 집계

 

* 윈도함수 중 순위 함수

  RANK : 동일 순위 레코드 존재 시 후순위는 넘어감 ( 1, 2, 2, 4, 5 )

  DENSE_RANK : 동실 순위 존재 해도 후순위로 넘어가지 않음 ( 1, 2, 2, 3, 4 )

  ROW_RANK : 동일 순위 값이 존재해도 연속 번호 부여 ( 1, 2, 3, 4, 5 )

 

* 윈도함수 중 행순서 함수

  FIRST_VALUE : 집계 함수의 MIN과 동일한 결과,  파티션별 윈도에서 가장 먼저 나오는 값

  LAST_VALUE : 집계 함수의 MAX와 동일한 결과, 파티션별 윈도에서 가장 늦게 나오는 값

  LAG : 파티션별 윈도에서 1부터 이전 몇 번째 행 값 가져옴

  LEAD : 파티션별 윈도에서 1부터 이후 몇 번째 행 값 가져옴

 

* 윈도함수 중 그룹 내 비율 반환 함수

  백분율을 보여주거나 행의 순서별 백분율 등 비율과 관련된 통계를 보여주는 함수

  RATIO_TO_REPORT : 주어진 그룹에 대해 합을 기준으로 각 로우의 상대적 비율을 반환, 결괏값은 0~1

  PERCENT_RANK : 주어진 그룹에 대해 제일 먼저나오는 것을 0, 제일 늦게 나오는 것을 1, 순서별 백분율, 0~1

 

* 응용 DBMS 접속 기술인 JDBC

  자바 언어를 이용하여 개발하며, SQL을 사용하여 DBMS에 질의하고 데이터를 조작하는 API를 제공하는 기술

 

* MyBatis로 DB 접근 시 장점

  복잡한 JDBC 코드를 단순화, SQL을 거의 그대로 사용가능, Spring 기반 프레임워크와 통합기능 제공, 우수한 성능

 

* DCL

  DB관리자가 데이터 보안, 무결성 유지, 병행 제어, 회복을 위해 관리자가 사용하는 제어용 언어

 

* GRANT UPDATE ON STUENCT TO USER WITH GRANT OPTION;

  수정 권한 부여, 다른 사용자에게 권한부여 하는 권한 추가

 

* REVOKE DELETE ON STUDENT FROM USER CASCADE CONSTRAINTS;

  데이터 삭제 권한 취소, 연쇄적인 권한 해제