SQL Server 강의 6일차

안녕하세요~! 문쑹입니다 :) 오늘은 SQL Server 강의 마지막 날 입니다 :(

짧은 시간동안 속성으로 되게 많이 배운 것 같은데 저의 실력은 늘지 않는군요 ㅠㅠ

오늘도 퐈이팅있게 포스팅해보겠습니다!

 

커서

커서는 행의 집합을 다루기에 많은 편리한 기능을 제공해준다. SQL Server의 성능을 느리게 하는 요인이 될 수 있으므로, 특별한 경우가 아니라면 되도록 사용하지 않을 것을 권장한다. 파일처리시의 파일 포인터와 비슷한 작동을 한다.

커서의 작동 순서

예를 들어 userTBL 테이블을 처리하는 커서는 아래와 같이 사용합니다.

DECLARE userTBL_cursor CURSOR GLOBAL
	FOR SELECT height FROM userTBL;

이번에는 userTBL에 있는 회원들의 수, 회원들의 전체 나이, 회원들의 평균 나이를 구하는 소스를 작성해보겠습니다.

DECLARE @index INT = 0,
		@bYear INT = 0,
		@age INT = 0;	--변수 선언 및 초기화

SET @index = 0;	--index 초기화

DECLARE cur CURSOR FOR	-- 커서를 열어준 순간 한 줄씩 읽음
SELECT birthYear FROM userTBL;	--반복문을 돌릴 테이블 선언

OPEN cur;	--커서를 열어준다.
FETCH NEXT FROM cur INTO @bYear;	--우선, 첫행을 읽는다.

--성공적으로 읽어졌다면 @@FETCH_STATUS 함수는 0을 반환하므로, 계속 처리한다.
WHILE @@FETCH_STATUS = 0
BEGIN
	--------------------------
	--	데이터 처리			--
	--------------------------
	SET @age += (YEAR(GETDATE()) - @bYear) + 1 -- +1은 한국식 나이를 표현하기 위해
	SET @index += 1;	--회원수를 카운팅해주기위해

	FETCH NEXT FROM cur INTO @bYear;	--다음행을 읽고, WHILE문으로 이동
END

CLOSE cur	-- 커서를 닫음
DEALLOCATE cur	--커서 할당 해제

PRINT '나이합 ' + CAST(@age AS VARCHAR);
PRINT '회원수 ' + CAST(@index AS VARCHAR);
PRINT '회원들의 평균 나이는 ' + CAST((@age / @index) AS VARCHAR(5))

사용해보니까 C#의 배열을 반복문에 돌려서 결과를 하나하나 출력하는 것과 유사합니다.

 

트리거의 개념

제약조건과 더불어서 데이터의 무결성을 위한 또 다른 기능이다. 트리거의 종류로는 DML트리거, DDL트리거, LOGON 트리거 세 가지가 있고 테이블(Table) 또는 뷰(View)에 부착(Attach)되는 프로그램 코드이다. 저장 프로시저와 비슷하게 작동하지만 직접 실행시킬 수는 없고 오직 해당 테이블이나 뷰에 이벤트(입력, 수정, 삭제)가 발생할 경우에만 실행된다. 저장프로시저와 달리 매개변수나 리턴값을 사용할 수 없다.

 

트리거의 간단한 예제를 보겠습니다.

CREATE TRIGGER testTRG	-- 트리거 이름
ON testTBL	-- 트리거를 부착할 테이블
AFTER DELETE, UPDATE	-- 삭제, 수정후에 작성하도록 지정
AS
	PRINT(N'트리거가 작동했습니다.');	-- 트리거 실행시 작동되는 코드들

 

트리거의 종류

AFTER 트리거

• 테이블에 INSERT, UPDATE, DELETE등의 작업이 일어났을 때 작동하는 테이블
• AFTER 트리거는 테이블에만 작동하며 뷰에는 작동하지 않는다.

INSTEAD OF 트리거

• 이벤트가 발생하기 전에 작동하는 트리거
• 테이블뿐 아니라 뷰에도 작동되며, 주로 뷰에 업데이트가 가등하도록 할 때 사용

CLR트리거

• T-SQL 저장 프로시저 대신 .NET Framework에서 생성되는 트리거

트리거를 작업하는 한 번해보겠습니다.

sqlDB에 backup_userTBL 테이블을 하나 생성해보겠습니다. id는 기본 키 값을 넣어주고 ID사양을 1씩 증가해주겠습니다. 

CREATE OR ALTER TRIGGER trg_BackupUserTBL ON userTBL -- 트리거 테이블 생성
AFTER UPDATE	-- 값을 수정했을 때 작동
AS 
BEGIN
	DECLARE @trgType nvarchar(3)	-- 트리거 타입

	IF (COLUMNS_UPDATED() > 0 )	-- 업데이트 되었다.
	BEGIN
		SET @trgType = '수정';

		INSERT INTO backup_userTBL
		SELECT userID, name, birthYear, addr, mobile1, mobile2, height, mDate, @trgType
		FROM deleted;
	END
END
GO

트리거 테이블을 생성하는 소스 코드입니다.

userTBL에 있는 'EJW'의 주소를 '경북'에서 '제주'로 바꾸는 기 위해서 UPDATE를 한 번만 하였는데 적용된 값은 두 번입니다. 하지만 아까 만든 backup_userTBL 테이블의 값을 보면 UPDATE했던 값들이 들어 가있습니다. 이렇듯 ROLLBACK과 비슷한 작용을 하기위해서 안전 장치를 만들어 놓는 것입니다.

 

트리거 삭제

CREATE OR ALTER TRIGGER trg_BackupUserTBL ON userTBL -- 트리거 테이블 생성
AFTER UPDATE, DELETE	-- 값을 수정 또는 삭제
AS 
BEGIN
	DECLARE @trgType nvarchar(3)	-- 트리거 타입

	IF (COLUMNS_UPDATED() > 0 )	-- 업데이트 되었다.
	BEGIN
		SET @trgType = '수정';
	END

	ELSE	--삭제
	BEGIN
		SET @trgType = '삭제';
	END
		INSERT INTO backup_userTBL
		SELECT userID, name, birthYear, addr, mobile1, mobile2, height, mDate, @trgType
		FROM deleted;
END
GO

삭제해주기 위해서 소스 코드를 조금 변경해주겠습니다.

userID가 'JYP'인 사람을 삭제해주겠습니다. 하지만 삭제가 안 될겁니다. 왜냐면 userTBL과 buyTBL이 부모와 자식관계이기 때문에 삭제가 되지않으면서 오류가 발생할 겁니다. 그럴땐 아래와 같이 설정해주세요

먼저, sqlDB - 테이블 - 키 - FK_buyTBL을 더블 클릭해주세요

그럼 이런 창이 나타날 거예요! 여기서 INSERT 및 UPDATE 사양을 펼쳐준 후 삭제 규칙과 업데이트 규칙을 계단식 배열로 바꿔준 다음 닫기를 누른 후 저장을 해주세요! 이렇게 되면 부모값이 지워지면 자식의 값도 같이 지워집니다!

 

트리거가 생성하는 임시테이블

inserted 테이블

INSERT와 UPDATE 작업 시에 변경 후의 행 데이터와 동일한 데이터가 저장된다.

 

deleted 테이블

DELETED와 UPDATE 작업이 수행되면 우선 해당 테이블에 삭제 또는 변경된 후에, 삭제 또는 변경되기 전의 데이터가 저장된다.

 

INSTEAD OF 트리거 (BEFORE 트리거)

테이블에 변경이 가해지기 전에 작동되는 트리거. 주로 뷰에 행이 삽입되거나 변경, 삭제 될 때 사용된다. INSTEAD OF 트리거가 작동하면, 시도된 INSERT, UPDATE, DELETE문은 무시된다.

 

 

전체 텍스트 검색

전체 텍스트 검색 서비스(Full Text Search Service)는 SQL Server 추가 기능이다. 긴 문장으로 구성된 열의 내용을 검색할 때 전체 텍스트 인덱스를 사용해서 빠른 시간에 검색하는 것이다. 기존 인덱스가 중간에 들어있는 글자로 검색할 때는 인덱스를 사용하지 못하는 문제점을 해결해 준다. 'SQL Full-text filter Daemon Launcher'라는 서비스가 등록되고 가동되어야 한다.

SQL Server 2019 구성 관리자 화면

신문기사와 같이, 텍스트로 이루어진 문자열 데이터의 내용을 가지고 생성한 인덱스

 

SQL Server의 인덱스와 차이점

일반 인덱스는 테이블 당 여러 개를 생성할 수 있지만, 전체 텍스트 인덱스는 테이블 당 하나만 생성할 수 있다.

전체 텍스트인덱스에 데이터 추가하는 채우기(Population)는 일정 에약이나 특별한 요청에 의해서 수행되거나, 새로운 데이터는 insert할때 자동으로 수행되게 할 수 도 있다.

전체 텍스트 인덱스는 char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary(max), FILESTREAM등의 열에 생성 가능하다. 전체 텍스트 인덱스를 생성할 테이블에는 Primary Key나 Unique Key가 있어야한다.

 

전체 텍스트 카탈로그(Full Text Catalog)

전체 텍스트 인덱스가 저장되는 가상의 공간

전체 텍스트 인덱스가 생성되기 전에 생성해 놓아야 함.

전체 텍스트 인덱스 채우기

전체 텍스트 인덱스를 생성하고 관리하는 것을 말함

 

채우기 방법

전체 채우기

처음 전체 텍스트 인덱스를 생성할 때 지정한 열의 모든 데이터 형에 대해서 인덱스를 생성하는 것을 말한다.

변경 내용 추적 기반 채우기

전체 채우기를 수행한 이후에, 변경된 내용을 채우는 것을 말한다.

증분 타임스탬프 기반 채우기

증분 채우기는 마지막 채우기 후 추가, 삭제, 수정된 행에 대해서 전체 텍스트 인덱스를 업데이트 한다.

 

중지 단어 및 중지 목록

실제로 검색에서 무시할 만한 단어들은 아에 전체 텍스트 인덱스로 생성하지 않기 위한 것을 '중지 단어'라고 부름

'중지 목록(stoplist)'은 이러한 중지 단어들을 관리하기 위한 집합

 

오늘은 쪽지 시험이 있어서 내용이 이전보다는 짧네요! 쪽지 시험 내용을 포스팅할 수 있는지 확인해보고 올릴수 있으면 올려보겠습니다 :) 감사합니다.

Hasta Luego~!