6. 커서, 트리거, 전체 텍스트 검색

1) 커서(Cursor)

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

 

1.1) 커서의 처리 순서

 

커서의 작동 순서

 

1.2) 커서의 선언

DECLARE cursor)name CURSOR [ LOCAL | GLOBAL ]
     [ FORWARD_ONLY | SCROLL ]
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
     [ TYPE_WARNING ]
     FOR select_statement
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
[;]

 

 

1.3) 커서 열기

OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name }

 

cf. @@FETCH_STATUS

@@FETCH_STATUS 는 FETCH 문의 실행 상태를 반환한다. ( 0 : 수행, -1 : 실패, -2 : 행 없음)

 

1.4) 커서 닫기

CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name } 

 

1.5) 커서 할당 해제

DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name }

 

ex)

DECLARE @index INT = 0, @bYear INT = 0, @age INT = 0
SET @index = 0;

DECLARE cur CURSOR FOR
    SELECT birthYear FROM userTbl;

OPEN cur;
FETCH NEXT FROM cur INTO @bYear;

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @age = @age + (YEAR(GETDATE()) - @bYear) + 1;
	SET @index = @index + 1; -- 평균나이 구하기 위해 count

	FETCH NEXT FROM cur INTO @bYear;
END

CLOSE cur;
DEALLOCATE cur;

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

 

예제 실행 화면

 

 

2) 트리거(Trigger)

2.1) 트리거의 개요

트리거는 제약 조건과 더불어서 데이터 무결성을 위해서 SQL Server에서 사용할 수 있는 또 다른 기능이며 테이블 또는 뷰에 부착되는 프로그램 코드라고 생각하면 된다. DDL 트리거, DML 트리거, LOGON 트리거가 있으며 그중 DML 트리거를 가장 많이 사용한다. 트리거는 저장 프로시저와 작동이 비슷하지만, 직접 실행시킬 수는 없고 오직 해당 테이블이나 뷰에 이벤트가 발생할 경우에만 실행된다. 트리거에는 저장 프로시저와 달리 매개변수를 지정하거나 반환 값(Return 값)을 사용할 수도 없다.

 

2.2) 트리거의 종류

AFTER 트리거

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

 

INSTEAD OF 트리거

BEFORE 트리거라고도 하며 이벤트가 발생하기 전에 작동하는 트리거이다. 테이블뿐 아니라 뷰에도 작동되며 INSERT, UPDATE, DELETE 세 가지 이벤트로 작동한다.

 

CLR 트리거

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

 

2.3) 트리거의 사용

CREATE TRIGGER 트리거이름
ON { 테이블이름 | 뷰이름 }
[ WITH ENCRYPTION ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS
       실행할 SQL 문들

 

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

트리거에서 INSERT, UPDATE, DELETE 작업이 수행되면 임시로 사용되는 시스템 테이블 inserted와 deleted가 있다. 이 두 테이블은 사용자가 임의로 변경 작업을 할 수는 없고, 단지 참조(SELECT)만 할 수 있다.

• inserted 테이블 : INSERT 트리거나 UPDATE 트리거를 부착시켜 놓았다면 해당 테이블에 INSERT나 UPDATE 명령이 수행되면 해당 테이블의 데이터가 삽입 또는 변경되고 그다음에 inserted 테이블에 삽입 또는 변경된 동일한 데이터가 저장된다(변경 전의 데이터가 아니라 변경 후의 데이터이다)
• deleted 테이블 : DELETE와 UPDATE 작업이 수행되면 우선 해당 테이블의 행 데이터가 삭제 또는 변경된 후에, 삭제 또는 변경되기 전의 데이터가 저장된다.

2.5) 기타 트리거

• 다중 트리거(Multiple Triggers)는 하나의 테이블에 동일한 트리거가 여러 개 부착되어 있는 것을 말한다.
• 중첩 트리거(Nested Triggers)는 트리거가 또 다른 트리거를 작동하는 것을 말한다.

중첩 트리거의 예

 

ex)

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

UPDATE userTBL
   SET addr = '제주'
 WHERE userID = 'EJW';

 

예제 실행 화면

 

SELECT * FROM backup_userTBL;

 

예제 실행 화면

 

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

 

외래 키의 삭제, 업데이트 규칙 변경

 

DELETE userTBL 
 WHERE userID = 'JYP';

 

예제 실행 화면

 

3) 전체 텍스트 검색(Full Text  Search)

전체 텍스트 검색 서비스는 SQL Server 추가 기능이다. 긴 문장으로 구성된 열의 내용을 검색할 때 전체 텍스트 인덱스를 사용해서 빠른 시간에 검색하는 것이다. 전체 텍스트 검색은 첫 글자뿐 아니라, 중간의 단어나 문장으로도 인덱스를 생성해 주기 때문에 순식간에 검색 결과를 얻을 수 있다. SQL Server 설치 시  검색을 위한 전체 텍스트 및 의미 체계 추출 기능을 선택해서 설치한다면 자동으로 SQL Full-test Filter Daemon Launcher라는 서비스가 등록되고 자동으로 가동이 된다.

 

3.1) 전체 텍스트 인덱스(Full Text Index)

전체 텍스트 인덱스는 텍스트로 이루어진 문자열 데이터의 내용을 가지고 생성한 인덱스를 말한다. SQL Server에서 생성한 일반적인 인덱스와는 몇 가지 차이점이 있다.

 

• 일반 인덱스는 테이블 당 여러 개를 생성할 수 있지만, 전체 텍스트 인덱스는 테이블 당 하나만 생성할 수 있다.
• 일반 인덱스는 Insert, Update, Delete 되면 인덱스도 자동으로 업데이트된다. 전체 텍스트 인덱스에 데이터를 추가하는 채우기(Population)는 일정 예약이나 특별한 요청에 의해서 수행되거나, 새로운 데이터를 Insert 시에 자동으로 수행되도록 할 수도 있다.
• 전체 텍스트 인덱스는 char, varchar, nvarchar, text, ntext, image, xml, varbinary(max), FILESTREAM 등의 열에 생성이 가능하다.
• 전체 텍스트 인덱스를 생성할 테이블에는 Primary Key가 반드시 있어야 하며 Unique Key가 있을 수 있다.

 

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

전체 텍스트 카탈로그는 전체 텍스트 인덱스가 저장되는 가상의 공간이다. 전체 텍스트 인덱스를 생성하기 전에 꼭 텍스트 카탈로그를 생성해 놓아야 한다. SQL Server 2008부터 전체 텍스트 인덱스가 데이터베이스 내부에 저장되기 때문에 전체 텍스트 카탈로그의 의미가 별로 중요하지 않게 되었다.

 

3.3) 전체 텍스트 인덱스 채우기(Population)

인덱스 채우기는 전체 텍스트 인덱스를 생성하고 관리하는 것이다.

• 전체 채우기 : 처음 전체 인덱스를 생성할 때 지정한 열의 모든 데이터 행에 대해서 인덱스를 생성하는 것을 말한다. 일반적으로 처음에는 전체 채우기를 수행해야 한다.
• 변경 내용 추적 기반 채우기 : 전체 채우기를 수행한 이후에, 변경된 내용을 채우는 것을 말한다.
• 증분 타임스탬프 기반 채우기 : 증분 채우기는 마지막 채우기 후 추가, 삭제, 수정된 행에 대해서 전체 텍스트 인덱스를 업데이트한다.

 

3.4) 중지 단어 및 중지 목록

실제로 검색에서 무시할 만한 단어는 아예 전체 텍스트 인덱스로 생성하지 않는 편이 좋다. 이것이 중지 단어(stopword)이다. 중지 목록(stoplist)은 이러한 중지 단어를 관리하기 위한 집합으로 생각하면 된다.

 

200612_FullTextSearch.sql

0.02MB

ex)

SELECT * FROM newsPaperTBL
 WHERE article LIKE '%배우%';

 

예제 실행 화면

 

Full-text Filter Daemon Launcher 가동 확인

 

CREATE FULLTEXT CATALOG newsCatalog AS DEFAULT;

SELECT * FROM sys.fulltext_catalogs;

CREATE FULLTEXT INDEX ON newsPaperTBL(article)
KEY INDEX PK__newspaper
ON newsCatalog;

 

newpaperTBL에 있는 기본 키

 

SELECT * FROM sys.dm_fts_index_keywords(DB_ID(),OBJECT_ID('dbo.newsPaperTBL'));

 

예제 실행 화면

 

SELECT * FROM newsPaperTBL 
 WHERE CONTAINS (article, '코로나');

 

예제 실행 화면

 

SELECT * FROM newsPaperTBL 
 WHERE CONTAINS (article, '코로나 OR 배우');

 

예제 실행 화면

 

전체 텍스트 인덱스 설정 과정

 

SELECT * FROM sys.dm_fts_index_keywords(DB_ID(),OBJECT_ID('dbo.FulltextTBL'));

 

예제 실행 화면

 

SELECT * FROM FulltextTBL 
 WHERE CONTAINS (description, '남자');

 

예제 실행 화면

 

test)

SELECT LEFT(Names,1)+','+RIGHT(NAMEs,2) AS '회원명', REPLACE(Addr,'부산시','') AS '주소', 
       Mobile AS '폰번호', UPPER(Email) AS '이메일'
  FROM membertbl;
  --
SELECT d.Names AS '장르', b.Author AS '작가', b.Names AS '책제목' FROM divtbl AS d
 INNER JOIN bookstbl AS b ON d.Division = b.Division
 ORDER BY d.Names DESC, b.Author ASC;
 --
INSERT INTO divTBL VALUES ('I001','프로그래밍');
UPDATE memberTBL set Levels = 'A', Mobile = '010-7625-0675' WHERE Names = '성명건';
--
SELECT r.Idx AS '대여번호', m.Names AS '대여회원', d.Names AS '장르', 
       b.Names AS '책제목', b.ISBN AS 'ISBN',  r.rentalDate AS '대여일' FROM rentalTBL AS r
 INNER JOIN memberTBL AS m ON r.memberIdx = m.Idx
 INNER JOIN booksTBL AS b ON r.bookIdx = b.Idx
 INNER JOIN divTBL AS d ON d.Division = b.Division;

 

실행 화면