학습목표 학습목표 본 장은 데이터베이스 내에 하나의 객체로 저장되어 관리되는 응용프로그램과 같은 기능을 하는 저장 프로시저에 대해 학습한다. 사용자가 직접 작성하는 사용자 저장 프로시저, DBMS가 관리를 위해 미리 만들어 둔 시스템 저장 프로시저에 대한 여러 가지 사용방법을 학습하고 저장 프로시저와 유사하지만 효과적인 데이터베이스 관리와 사용을 위해 특정 개체에 발생하는 이벤트에 따라 자동으로 동작하도록 설계되는 트리거를 학습한다.

1. 저장프로시저의 의미 1.1 Transact-SQL과 저장 프로시저 SQL은 제어, 반복, 조건 처리 등의 기능이 없다.

1.2 Transact-SQL의 변수와 제어 명령문 저장프로시저의 의미 1.2 Transact-SQL의 변수와 제어 명령문 변수 선언 : DECLARE -> DECLARE @mem_ID char(20) -> DECLARE @mem_ID char(20), @mem_pass char(20), @mem_degree int 변수에 값 할당 : SET -> SET @mem_ID = 'kang123‘ -> SET @mem_pass = 'kangkang' -> SET @mem_degree = 300 변수의 값 출력 : SELECT -> SELECT @mem_ID, @mem_pass, @mem_degree

저장프로시저의 의미 1.2 Transact-SQL의 변수와 제어 명령문 -실습 1 2 3

1.2 Transact-SQL의 변수와 제어 명령문 저장프로시저의 의미 1.2 Transact-SQL의 변수와 제어 명령문 IF-ELSE 문 : 조건에 따라 2 가지 이상의 분기를 제공하는 명령 실습 : 사이즈가 ‘L’인 제품 중 재고량이 가장 많은 제품을 검색하라. 명령 : DECLARE @max_qty int, @p_size char(20), @p_code char(20) SELECT  @max_qty = max(재고수량) FROM 상품 WHERE 사이즈='L' SELECT @p_code = 상품코드 FROM  상품        WHERE 재고수량 = @max_qty AND 사이즈='L' IF @max_qty >= 20        SELECT @p_code as  '상품코드', @max_qty as '재고수량',                'L 사이즈 제품 중 재고량이 가장 많은 제품입니다.' AS '메시지'    ELSE     SELECT 'L 사이즈 제품은 재고량이 모두 20개 미만입니다.' 결과 :

1.2 Transact-SQL의 변수와 제어 명령문 저장프로시저의 의미 1.2 Transact-SQL의 변수와 제어 명령문 WHILE 문, BREAK 문, CONTINUE 문 : 조건에 따라 특정 부분의 반복과 반복을 조절하는 명령 실습 : ‘상품’ 테이블에서 재고수량이 10개 미만인 제품이 없을 때 까지 재고수량을 2개씩 올려주는 작업을 하시오. 명령 : DECLARE @cnt int SELECT @cnt = count(*) FROM 상품1 WHERE 재고수량 < 10 WHILE @cnt > 0 BEGIN         UPDATE  상품1 SET 재고수량 =재고수량 + 2        SELECT @cnt = count(*) FROM 상품1                WHERE 재고수량 < 10        IF @cnt > 0          CONTINUE        ELSE          BREAK END 결과 : 다음 슬라이드로…

1.2 Transact-SQL의 변수와 제어 명령문 저장프로시저의 의미 1.2 Transact-SQL의 변수와 제어 명령문 결과 : < 실행 전 테이블 > < 실행 과정 : 쿼리 분석기 > < 실행 후 테이블 >

1.3 저장 프로시저의 특징과 장점 저장프로시저의 의미 1.3 저장 프로시저의 특징과 장점 저장 프로시저 : 자주 사용되는 Transact-SQL을 개체로 저장해 둔 것 특징 : 함수와는 달리 이름을 이용하여 값을 반환하지 않는다.  프로시저의 이름을 프로시저 내부에서 사용할 수 없다. 매개 변수를 받아들이거나 반환할 수 있다. 프로시저 내에서 또 다른 프로시저를 호출할 수 있다. 실행과 관련된 제어 값과 메시지를 반환한다. 실행과정 : 사용자 정의 저장 프로시저 작성 및 저장 컴파일 실행 플랜 생성 실행 재 사용시는 4번 과정만 필요 저장 프로시저의 장점 실행속도의 효율성 모듈화 기능 네트워크 전송 효율 보안 설정

2. 저장프로시저의 종류 2.1 시스템 저장 프로시저 저장프로시저의 종류 2.1 시스템 저장 프로시저 sp_helpdb : 현재 동작 중인 SQL-Server 에 있는 모든 데이터베이스의 이름, 크기, 생성날짜, 소유주, 순위, 상태 등의 정보를 제공 실행 결과 :

sp_helptext : 지정한 저장 프로시저의 소스를 제공 저장프로시저의 종류 2.1 시스템 저장 프로시저 - 계속 sp_helptext : 지정한 저장 프로시저의 소스를 제공 실행 결과 :

sp_helplogins : 현재 접속중인 사용자 정보, 데이터베이스에 등록된 사용자 정보 제공 저장프로시저의 종류 2.1 시스템 저장 프로시저 - 계속 sp_helplogins : 현재 접속중인 사용자 정보, 데이터베이스에 등록된 사용자 정보 제공 실행 결과 :

2.1 시스템 저장 프로시저 - 계속 저장프로시저의 종류 시스템 저장 프로시저 기능 2.1 시스템 저장 프로시저 - 계속 시스템 저장 프로시저 기능 sp_helpindex / sp_helpfile / sp_tables / sp_server_info 인덱스 /테이블/ 뷰/ 파일/ 서버 의 정보 제공 sp_renamedb / sp_rename 데이터베이스/테이블 이름 변경 sp_spaceused 사용 중인 디스크 공간의 정보 제공 sp_configure 현재 서버의 환경 설정 변경 또는 표시 sp_dboption 데이터베이스 옵션을 표시 또는 변경 sp_who 현재 서버의 사용자와 프로세스 정보 제공 sp_lock / sp_depends 잠금 정보 /종속관계 제공 sp_addmessage spsmessage  테이블에 사용자 정의메세지 추가 sp_stored_procedures / sp_helptrigger 사용자 정의 저장 프로시저의 목록과 정보 /트리거 제공 sp_table_privileges 지정한 테이블에 대한 권한 정보 제공 sp_helpconstraint 지정된 개체에 적용된 제약사항 정보 제공

2.2 사용자 정의 저장 프로시저 저장프로시저의 종류 2.2 사용자 정의 저장 프로시저 저장 프로시저의 생성 및 관리 권한은 데이터베이스 소유주나 권한을 가진 자 만이 가능하다. 명령 내용 사용 예 CREATE PROCEDURE 저장프로시저 생성 CREATE PROCEDURE all_members As select * from 고객 ALTER PROCEDURE 수정 ALTER PROCEDURE all_members with encryption as  select * from 고객 order by 성명 DROP PROCEDURE 제거 DROP PROCEDURE all_members EXECUTE 프로시저명 실행 EXECUTE all_members

3. 저장프로시저의 사용 3.1 저장 프로시저의 생성과 사용 저장프로시저의 사용 저장 프로시저의 생성 구문 3.1 저장 프로시저의 생성과 사용 저장 프로시저의 생성 구문 CREATE PROCEDURE 프로시저이름   [{@매개변수명}  데이터타입 [= 기본 값] [OUTPUT] ] [WITH {RECOMPILE | ENCRYPTION | RECOMPILE, NCRYPTION }]    AS     [BEGIN]        SQL 문장.....     [END] 프로시저명 : 데이터베이스 내에서 유일해야 한다. 매개변수명 : 하나 이상 올 수 있고 각각은 쉼표로 구분한다. Output를 지정하면 프로시저의 실행 시 값을 반환 가능. 데이터 타입 : 매개변수가 사용할 데이터 타입이다. RECOMPILE 옵션 : 저장 프로시저를 실행할 때 마다 컴파일한다. ENCRYPTION 옵션 : 저장 프로시저의 SQL 문이 저장되는 syscomments 테이블의 내용을 암호화한다.

3. 저장프로시저의 사용 2 1 3 3.1 저장 프로시저의 생성과 사용 저장프로시저의 사용 3.1 저장 프로시저의 생성과 사용 실습 : ‘manpower’ 데이터베이스의 상품 관련 테이블을 이용한 전체정보를 검색하는 저장 프로시저를 생성하시오. 명령 : create procedure all_info_product as    select p.상품코드, s.소분류명, c.색상, p.사이즈, p.재고수량          from 상품 p,  소분류코드 s, , 색상분류 c          where p.소코드 = s.소코드 and  p.색상코드 = c.색상코드 생성 및 확인 2 1 3

3.1 저장 프로시저의 생성과 사용 - 계속 저장프로시저의 사용 3.1 저장 프로시저의 생성과 사용 - 계속 실습 : 저장 프로시저 all_info_product 의 내용 중 사이즈가 'XL'인 제품에 대해서만 검색하는 것으로 수정하시오. 명령 : ALTER PROCEDURE all_info_product as    select 상품코드, 소분류코드.소분류명, 색상분류.색상, 사이즈, 재고수량 from 상품,  소분류코드 , 색상분류          where 상품.소코드 = 소분류코드.소코드                 and  상품.색상코드 = 색상분류.색상코드                 and  사이즈 = 'XL' 실습 : 저장 프로시저 all_info_product 를 제거하시오. 명령 : DROP PROCEDURE all_info_product 실행 :

3.2 저장 프로시저에서 매개변수 사용 저장프로시저의 사용 3.2 저장 프로시저에서 매개변수 사용 저장 프로시저는 매개변수를 이용하여 외부로부터 값을 주는 것이 가능하고 응용 프로그램으로부터 값을 받아 실행 할 수 도 있다. 실습 : ‘고객’ 테이블에 자료 입력을 위하여 매개변수를 사용한 프로시저를 생성하시오. 생성 : CREATE PROCEDURE insert_members @고객ID char(30), @패스워드  char(10), @성명 nvarchar(30), @주민번호 char(15), @고객등급  nvarchar(4)='실버' AS INSERT INTO  고객(고객ID, 패스워드, 성명, 주민번호, 고객등급) VALUES(@고객ID, @패스워드, @성명, @주민번호, @고객등급) 실행 : Execute insert_members ‘girl007’, ‘girlpass’, ‘본드걸’, ’801212-1113333’ 참고 : ‘고객등급’은 기본 값 ’실버’ 가 자동으로 사용되며 주어지지 않은 값이 있는 경우는 null 상태로 레코드가 삽입된다. (단, 필수 입력난이 있으면 오류 발생)

3.2 저장 프로시저에서 매개변수 사용 - 계속 저장프로시저의 사용 3.2 저장 프로시저에서 매개변수 사용 - 계속 응용 프로그램 속에서 저장 프로시저를 호출하는 경우 저장 프로시저의 실행 결과를 매개 변수를 통해 반환한다. 실습 : ‘고객’ 테이블의 숫자를 검색하는 프로시저를 생성하고 그 프로시저를 실행하시오. 생성 : CREATE PROCEDURE count_of_members @cnt int OUTPUT AS @cnt = SELECT count(*) FROM  고객 실행 및 결과 :

트리거란? 4. 트리거란? 정의 : 특정 테이블의 데이터가 변경될 때 자동으로 다른 테이블의 데이터를 변경하도록 설계된 저장 프로시저의 특별한 형태. 매개변수를 이용할 수 없다. 장점 : 복잡한 데이터 적합성 검사 가능 연속적인 트리거의 실행으로 다른 데이터베이스나 서버에 대해서도 광범위한 참조 무결성 검사가 가능 단점 :  연속적인 트리거의 실행은 시스템의 성능을 떨어뜨린다. 설계 오류로 무한 루프 형태로 트리거가 동작 할 수도 있다.

트리거의 종류 5. 트리거의 종류 INSERT 트리거 : 특정 테이블에 삽입 이벤트가 발생할 때 동작하도록 설계되어 있는 트리거 DELETE 트리거 : 특정 테이블에 삭제 이벤트가 발생할 때 동작하도록 설계되어 있는 트리거, 삭제가 여러 건 수행되어도 트리거는 한번만 동작한다. UPDATE 트리거 : 특정 테이블에 수정이 일어날 때 동작하도록 설계되어 있는 트리거 참고 : 데이터베이스 조작도중 DBMS 가 사용하는 메모리 내의 입력 테이블과 삭제 테이블 입력테이블 : 입력된 레코드, 수정 후의 레코드 저장 삭제테이블 : 삭제된 레코드, 수정 전의 레코드 저장 트리거는 이 테이블을 보고 어떤 이벤트가 발생했는지를 파악하여 동작한다.

6. 트리거의 생성 및 실행 트리거 생성 및 실행 트리거 생성 구문: 트리거 생성과 실행 및 결과 확인 과정 6. 트리거의 생성 및 실행 트리거 생성 구문: 트리거 생성과 실행 및 결과 확인 과정 실습 : ‘상품’ 테이블에 update 가 발생하면 update 된 상품의 ‘상품코드’를 이용하여 재고수량을 검사하고 만약 재고수량이 10개 미만이 되면 업체에 그 상품을 100개 주문하기 위해 ‘업체주문’ 이라는 테이블에 그 정보를 삽입해 두는 트리거를 생성하시오. 분석 : UPDATE 트리거이다. ‘업체주문’, ‘상품’ 두 개의 테이블이 사용된다. 상품테이블에 update 발생 ->업체주문테이블에 insert 발생

6. 트리거의 생성 및 실행 - 계속 트리거 생성 및 실행 트리거 생성 : --트리거 이름 --트리거 발생동기 테이블 6. 트리거의 생성 및 실행 - 계속 트리거 생성 : --트리거 이름 --트리거 발생동기 테이블 --트리거 종류 --update는 삭제, 삽입이 실행되므로 삭제가 일어난 테이블 지정(‘상품’) --’상품’ 테이블에 갱신된 상품의 재고검사 --’업체주문’ 테이블에 삽입 실행

6. 트리거의 생성 및 실행 - 계속 트리거 생성 및 실행 업체주문 테이블의 형식 (현재 데이터는 없다고 가정) 6. 트리거의 생성 및 실행 - 계속 업체주문 테이블의 형식 (현재 데이터는 없다고 가정) 열이름 주문코드 상품코드 주문수량 형식 int char(10) ‘상품’ 테이블의 내용 : 트리거 실행을 확인하기 위한 update 명령 update 상품 set 재고수량 = 1 where  상품코드 = 'P15'

6. 트리거의 생성 및 실행 - 계속 트리거 생성 및 실행 트리거 실행을 확인하기 위한update 수행 6. 트리거의 생성 및 실행 - 계속 트리거 실행을 확인하기 위한update 수행 update 수행 후 ‘업체주문’ 테이블 확인

6. 트리거의 생성 및 실행 - 계속 트리거 생성 및 실행 트리거 수정 ALTER TRIGGER 트리거이름 ON 테이블[뷰] 6. 트리거의 생성 및 실행 - 계속 트리거 수정 ALTER TRIGGER 트리거이름 ON  테이블[뷰]      FOR { INSERT | DELETE | UPDATE } AS [BEGIN]          SQL 문 [END] 트리거 삭제 DROP TRIGGER 트리거이름 트리거 비활성화[활성화] ALTER TABLE  테이블이름 DISABLE[ENABLE] TRIGGER {트리거이름 | ALL }

7. 성능을 위한 트리거 관리하기 제약조건에 위배된 작업 뒤에 수행되는 트리거 7. 성능을 위한 트리거 관리하기 제약조건에 위배된 작업 뒤에 수행되는 트리거 트리거를 포함하고 있는 전체 트랜잭션을 롤백해 버리므로 유효한 작업까지 취소된다. 복잡하고 수행 시간이 긴 트리거는 해당 트리거가 수행되는 동안 계속해서 ‘LOCK’ 상태로 있으므로 ‘BLOCKING’ 이 될 수 있다. 재귀 트리거는 MS-SQL의 경우 32번 까지 허용된다. => 그 이상 호출하거나 도중에 빠져 나오지 않는다면 작업을 취소해 버리므로 아무 트랜잭션도 수행되지 않은 것과 같게 된다.

답: 시스템 저장 프로시저, 사용자 정의 저장 프로시저 단원 요약 단원요약 1 ★ SQL문에 반복처리, 조건 처리 등 프로그램적인 기능을 보강하여 데이터베이스 내 하나의 객체로 저장하여 두고 필요 시에 실행하는 것을 라고 한다. ? 답: 저장 프로시저 단원요약 2 ★ 저장 프로시저는 데이터베이스의 효과적인 관리를 위해 미리 만들어져 제공되는 와 사용자의 필요에 의해 생성하여 사용하는 가 있다. ? ? 답: 시스템 저장 프로시저, 사용자 정의 저장 프로시저

답: 트리거 답: CRAETE TRIGGER 단원 요약 단원요약 3 ★ 데이터의 무결성 유지와 효과적인 데이터 사용을 위해 특정 개체에 삽입, 갱신, 삭제와 같은 이벤트가 발생하면 자동으로 실행되는 저장 프로시저의 또 다른 형태인 가 있다. ? 답: 트리거 단원요약 4 ★ 트리거의 생성은 , 트리거의 수정은 ALTER TRIGGER문, 트리거의 제거는 DROP TRIGGER 문으로 작업한다. ? 답: CRAETE TRIGGER