■ 절차형 SQL (PL/SQL)
SQL 에서 사용하는 절차 지향적인 프로그램이며, 조건에 따른 분기, 반복 등의 제어를 활용하여 DB 작업의 고효율화를 기반으로 높은 생산성을 확보할 수 있게 해준다.
■ 절차형 SQL의 구성
프로지서, 사용자 정의 함수, 트리거 등 다소 차이가 있으나 필수적인 요소로는
[DECLEAR] / [BEGIN] / [END] 이다.
■ 프로시저 (Procedure)
절차형 SQL을 활용하여 특정 기능을 수행할 수 있는 트랜잭션 언어
- 파라미터 변수 입력/출력을 구분
IN : 운영체제에서 프로시저로 전달
OUT : 프로시저에서 처리된 결과가 운영체제로 전달
INOUT : IN과 OUT두 가지 기능을 동시 수행
- 프로시저 호출 : EXECUTE 프로시저명(파라미터1, 파라미터2);
= 줄여서 EXEC명령어도 사용가능하다.
- 프로시저 오류 검사 : SHOW ERROR;
- 프로시저 삭제 : DROP PROCEDURE 프로시저명;
■ 사용자 정의 함수 (Function)
일련의 연산 처리 결과를 단일값으로 반환할 수 있는 절차형 SQL
단일 값을 반환한다는 것이 프로시저와의 가장 큰 차이점!!
* 프로시저와 다르게 리턴값은 단일값!
- 사용자 정의 함수 호출쿼리 : DQL또는 DML문장을 활용하여 사용자 정의 함수를 호출할 수 있다.
ex) UPDATE EMP SET AGE = 사용자 정의 함수(파라미터) WHERE EMP_ID = '20202020';
■ 트리거 (Trigger)
[ 개념 ] 트리거는 데이터베이스에 명시된 이벤트가 발생할 때마다 DBMS가 이에 대응해서 자동적으로 호출하는 일종의 프로시저
특정 테이블에 DML이 동작하면 DBMS에서 자동적으로 실행되도록 구현된 프로그램
- 이벤트는 전체 트랜잭션 대상과 각 행에 의해 발생되는 경우 모두를 포함 가능, 테이블과 뷰(View), DB 작업 대상으로 정의
- 일반적으로 이벤트와 관련된 테이블의 삽입, 삭제, 변경 작업을 DBMS가 자동적으로 실행시키기 위해서 이지만, 데이터 무결성 및 로그 메시지 출력 등의 별도 처리를 위해 사용되기도 함
:OLD / :NEW || deleted / inserted --> 전후 변수는 언어마다 다름
[ 트리거 이벤트 ] BEFORE (DML이 실행되기 전 트리거 실행) / AFTER (DML이 실행된 후 트리거 실행)
* 트리거 작성 주의사항
- 트리거 내에는 COMMIT, ROLLBACK 등의 DCL을 사용할 수 없다.
- 오류에 특히 주의해야한다.
■ 데이터 분석 함수
- 집계함수 (AGGREGATE FUNCTION) : * 집계함수도 그룹함수의 일종이다.
[ 종류 ] COUNT( ) / SUM( ) / AVG( ) / MAX( ) / MIN( ) / STDDEV( ) : 표준편차 / VARIAN( ) : 분산 값
- 그룹함수 (GROUP FUNCTION)
[ 종류 ] ROLLUP(가능한 한 소집계만 생성) / CUBE(결합 가능한 모든 값에 대해 다차원 집계를 생성) / GROUPING SETS (다양한 소계집합, ROLLUP, CUBE와 달리 컬럼 간 순서와 무관한 결과를 얻을 수 있음)
- 윈도우함수 (WINDOW FUNCTION) : OLAP (On-Line Analytical Processing) 함수라고도 함
- 윈도우 함수는 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수로 분석(ANALYTIC) 함수 또는 순위 (RANK)함수라고도 한다.
+ 데이터베이스를 사용하는 온라인 분석처리용도로 사용 / 데이터 기반 의사결정의 증가에 따라 그 중요성이 더욱 증가
[ 종류 ]
RANK (후순위 넘어감 / 2위, 2위, 2위, 5위, 6위 ..) / DENSE_RANK (후순위 안넘어감 / 2위, 2위, 2위 ,3위, 4위..) / ROW_NUMBER (레코드의 순위계산, 동일순위 무시하고 그냥 쭉 / 2위, 3위, 4위 ..)
/ FIRST_VALUE / LAST_VALUE / LAG / LEAD / RATIO_TO_REPORT / PERCENT_RANK / CUME_DIST / NTILE 등
* PARTITION BY 구는 GROUP BY가 가진 집약 기능이 없다. 그래서 이로 인해 ROW수가 줄어들지 않는다.
* PARTITION BY구 뒤에는 순위의 기준이 되는 대상을 조정할 수 있다.
* 윈도우 함수에는 OVER 문구가 필수적으로 포함
* OLAP (On-Line Analytical Processing) = 다차원 분석 시스템
사용자가 정보에 대해 직접 접근해 대화식으로 정보를 분석하고 의사결정에 활용하는 과정
* OLTP (On-Line Transaction Processing)
트랜잭션을 수집하고 분류, 저장, 유지보수, 갱신, 검색하는 기능을 수행하는 실시간 거래 처리 시스템
■ 응용시스템과의 DBMS 접속기술
- JDBC (Java DataBase Connectivity) : 자바에서 제공하는 DBMS 데이터 질의 조작 API
- ODBC (Open DataBase Connectivity) : 어떤 응용프로그램을 사용하는지에 관계없이, 데이터베이스를 자유롭게 사용하기 위하여 만든 응용프로그램의 표준방법
- MyBatis : SQL Mapping 기반 오픈 소스 Access Framework
마이바티스 장점 : 복잡한 JDBC코드 단순화 / SQL그대로 사용 / Spring 프레임워크와 통합기능 제공 / 우수한 성능
* 입력변수 처리 변수를 #{Parameter Name}으로 처리한다.
+ 동적 SQL 사용
+ 절차형 SQL 호출 (statementType ="CALLABLE"로 꼭 설정 / 호출전 CALL 작성)
■ 커서 (Cursor)
쿼리문의 처리 결과가 저장되어 있는 메모리 공간을 가리키는 포인터(Pointer)로, 내부에서 자동으로 생성되어 사용되는 묵시적 커서와 사용자가 직접 정의해서 사용하는 명시적 커서가 있음
■ INDEX 인덱스
- 트리기반 인덱스 (B트리 인덱스, B+트리 인덱스) : 인덱스를 저장하는 블록들이 트리 구조를 이루고 있는 것
- B트리 인덱스
일반적 인덱스 방식으로, 루트 노드에서 하위 노드로 키 값의 크기를 비교해 나가면서 단말 노드로 키 값의 크기를 비교해 나가면서 단말 노드에서 찾고자 하는 데이터를 검색하고, 키 값과 레코드를 가리키는 포인터들이 트리 노드에 오름차순으로 저장된다.
- 비트맵 인덱스 (분포도가 좋은 컬럼에 적합하며, 성능 향상 효과를 얻을 수 있다.)
인덱스 컬럼의 데이터를 0,1로 변환하여 인덱스키로 사용하는 방법으로, 키 값을 포함하는 로우(Row)의 주소를 제공하는 것이 목적인 인덱스
■ VIEW (뷰)
CREATE (OR REPLACE) VIEW [테이블명] AS SELECT [컬럼1, 컬럼2, 컬럼3] FROM [테이블];
* CREATE TABLE에서 기존 테이블 정보를 이용해 새로운 테이블을 정의할 때 사용함.
[ CREATE TABLE 신규테이블명 AS SELECT 속성명,[속성명,...] FROM 기존테이블명; ]
■ DCL 명령문
DBMS에서 사용자 그룹관리는 '역할 기반 접근제어(RBAC : Role Based Access Control) 그룹 관리를 기반으로 한다.
- 역할(ROLE) 부여를 통한 사용자 그룹 관리 :
Role 은 DB에서 사용자 그룹과 권한 사이의 중개 역할을 하며, 이를 통해 빠르고 정확하게 필요한 권한을 부여할 수 있
게한다.
- 사용자 그룹생성 CREATE USER [사용자그룹명] IDENTIFIED BY [사용자그룹 패스워드] ;
GRANT CREATE SESSION TO [사용자그룹명];
- 사용자 그룹 변경 ALTER USER [사용자그룹명] IDENTIFIED BY [사용자그룹 패스워드]
[사용자 권한 부여 GRANT]
- 사용자등급 지정
GRANT 사용자 등급 TO 사용자리스트 [IDENTIFIED BY 암호];
- 테이블 및 속성에 대한 권한 부여
GRANT 권한리스트(ALL/SELECT/UPDATE/DELETE/INSERT/ALTER) 또는 사용자권한 ON [개체] TO [사용자리스트] (WITH GRANT OPTION);
ex) GRANT SELECT, DELETE ON 테이블A TO user001; → user001에게 테이블A에서 select, delete 할 수 있는 권한줌
* WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 다시 부여할 수 있다는 의미의 옵션
[사용자 권한 회수 REVOKE]
- 사용자등급 해제
REVOKE 권한리스트(ALL/SELECT/UPDATE/DELETE/INSERT/ALTER) 또는 사용자권한 ON [개체] FROM 사용자리스트;
- 테이블 및 속성에 대한 권한 취소
REVOKE [GRANT OPTION FOR] 설정했던_사용자 등급 ON [개체] FROM [사용자] [CASCADE];
* GRANT OPTION FOR : 다른 사용자에게 권한을 부여 할 수 있는 권한을 취소함
* DCL 무결성관련 명령어 (TCL인데 DCL도 된다/문제에 맞춰서 정답에 넣어야함~)
COMMIT / ROLLBACK / SAVEPOINT
■ DDL 명령문
[DDL 생성문 - CREATE TABLE ]
CREATE TABLE [테이블명]
(속성명 data_type [NOT NULL], ... ,
PRIMARY KEY (속성명),
UNIQUE (속성명),
FOREIGN KEY(속성명) REFERENCES 참조테이블(속성명),
CONSTRAINT 제약조건명 CHECK(조건식));
[DDL 명령문 - ALTER TABLE ]
ALTER TABLE 테이블명 ADD 속성명 데이터타입 [DEFALUT '기본값']; --- 새로운 속성(열) 추가 (2020.3회 실기출제)
ALTER TABLE 테이블명 ALTER | MODIFY 속성명 [SET DEFAULT '기본값']; --- 특정 속성의 정의 변경 시
ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE]; --- 특정 속성을 삭제 시
* ALTER TABLE 기존테이블명 RENAME TO 변경될테이블명; - 오라클
= RENAME TABLE 기존테이블명 TO 변경될테이블명; - MYSQL
[ DDL 명령문 - DROP TABLE ]
DROP TABLE 테이블명 CASCADE (CONSTRAINTS); --- 제거 테이블을 참조하는 다른 모든 테이블을 다삭제
DROP TABLE 테이블명 RESTRICT; --- 다른 개체가 제거할 요소를 참조중일 때 제거를 취소
* DDL문으로 객체도 생성, 변경, 제거 가능
ex) DROP USER user001; → 관리자 권한으로 user001계정을 삭제하는 DDL
ex) ALTER USER user001 IDENTIFIED BY 신규비밀번호 → user001의 비밀번호를 변경
(동일하게 DROP, CREATE, ALTER, TRUNCATE 사용)
[ TRUNCATE : 테이블형태는 두고 모든 튜플을 삭제 ]
TRUNCATE TABLE 테이블명;
■ TCL 단어 정의 (Transaction Control Language)
[개념] 트랜잭션의 원자성을 보장하면서 데이터의 무결성을 유지해주는 언어
- COMMIT : 트랜잭션이 성공적으로 끝나면 데이터 베이스가 새로운 일관성 상태를 가지기 위해 변경된 모든 내용을 반영하기위해 사용하는 명령어 (트랜잭션의 수행된 결과를 실제 물리적인 디스크에 영구적으로 저장하기 위해 사용)
- ROLLBACK : 아직 COMMIT 되지 않은 변경된 모든 내용들을 취소하고 데이터베이스 이전 상태로 되돌리는 명령어
(하나의 트랜잭션처리가 비정상 종료되어 데이터베이스의 일관성을 깨뜨린 경우, 트랜잭션 일부가 정상 처리되었더라도 트랜잭션의 원자성을 실현하기위해 이 트랜잭션이 수행한 모든 연산을 취소시킴)
- SAVEPOINT : 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 ROLLBACK하는 명령어
ex) SAVEPOINT s01; → s01이라는 이름으로 저장점 생성
ex) ROLLBACK TO SAVEPOINT s01; → s01 저장점으로 트랜잭션을 원복하는 문장
■ 로킹(Locking) 기법
트랜잭션이 사용하는 데이터 항목에 대하여 잠금(Lock)을 설정한 트랜잭션이 해제(unlock)할 때까지 독점적으로 사용할 수 있게 상호배제 기능을 제공하는 기법이다.
- 로킹의 대상이 되는 객체의 크기를 로킹단위라고 한다.
- 로킹은 한 트랜잭션이 어떤 데이터에 대해서 액세스 하는 동안 다른 트랜잭션 역시 이 데이터에 접근하기 위해서는 Lock을 소유 하고 있어야만 액세스 할 수 있도록 하는 방법
- 로킹은 작을 수록 병행수준은 뛰어나지만 관리가 어렵고, 클수록 병행수준은 낮아지지만 관리는 쉽다.
■ DML - SELECT 문의 실행 작동 순서
① FROM → ② WHERE → ③ GROUP BY → ④ HAVING → ⑤ SELECT → ⑥ DISTINCT → ⑦ ORDER BY
■ JOIN 조인 (논리적 조인 : 사용자 SQL문에 표현되는 테이블 결합 방식)
[ SELF JOIN ] : 한 테이블 안에서 조인연산을 수행
[ INNER JOIN ]
- 동등조인 (EQUI JOIN)
- NATURAL JOIN : 같은 컬럼 추출
- CROSS JOIN : 조건없는 모든 데이터의 조합 추출
[ OUTER JOIN ]
- LEFT OUTER JOIN : 왼쪽기준 데이터 추출 + 교집합 오른쪽 데이터
- RIGHT OUTER JOIN : 오른쪽 기준 데이터 추출 + 교집합 왼쪽 데이터
- FULL OUTER JOIN : 양쪽 전부다 추출
ex) 조인사용
SELECT a.학생, b.성적
FROM a LEFT OUTER JOIN b
ON a.학번 = b.학번
■ 물리적조인 : 데이터베이스 옵티마이저에 의해 내부적으로 발생하는 테이블 결합방식
Nested Loop Join / Merge Join / Hash Join
■ 집합연산자 (2개 이상의 질의 결과를 연결하여 하나로 결합하는 방식)
- UNION : 중복행 제거 합집합
- UNION ALL : 중복행 제거안하는 합집합
- INTERSECTION : 교집합
- EXCEPT (MINUS) : 차집합 → 앞의 SQL문과 뒤의 SQL문의 결과 사이의 차집합
(중복행 제거, MINUS로 사용하는 SQL있음)
■ 서브쿼리개념
[개념] 다른 SQL문 안에 포함되어 WHERE 절, FROM 절, SELECT 절에 사용되는 또 다른 SQL문
[동작 방식에 따른 서브쿼리]
- 비상관(Uncorrelated) 서브쿼리 : 서브쿼리가 메인쿼리의 컬럼을 가지고 있지 않은 형태
- 상관(Correlated) 서브쿼리 : 서브쿼리가 메인쿼리의 컬럼을 가지고 있는 형태
[반환 데이터 형태에 따른 서브쿼리]
- Single Row : 서브쿼리 결과가 항상 1건 이하인 서브쿼리 / 단일행 비교 =, <=, >= , <>
- Multiple Row : 서브쿼리의 실행결과가 여러건 / 다중 행 비교 IN, ALL, ANY, SOME, EXISTS
- Multiple Column (다중컬럼) : 서브쿼리 결과가 여러컬럼으로 반환
■ 관계대수 (Relational Algebra)
관계형 데이터베이스에서 원하는 정보와 그 정보를 어떻게 유도하는가를 기술한 절차적 정형 언어
관계 대수는 테이블에서 튜플을 검색하기 위해 필요한 연산자들의 모음이다.
집합 연산자에는 합집합, 교집합, 차집합, 카티션 프로젝트가 있고
순수 관계 연산자에는 셀렉트(select), 프로젝트(project), 조인(join), 디비전(division) 이 있다.
'자격증 > 정보처리기사' 카테고리의 다른 글
| [정보처리기사_실기] Chapter 10. 프로그래밍 언어 활용 (0) | 2020.10.11 |
|---|---|
| [정보처리기사_실기] Chapter 9. 소프트웨어 개발 보안 구축 (0) | 2020.10.11 |
| [정보처리기사_실기] Chapter 7. 애플리케이션 테스트 관리 (0) | 2020.10.11 |
| [정보처리기사_실기] Chapter 6. 화면 설계 (0) | 2020.10.11 |
| [정보처리기사_실기] Chapter 5. 인테페이스 구현 (0) | 2020.10.11 |