dbms. mssql 저장 프로시저와 트랜젝션 처리

아주 예전에 정리했던 내용입니다.

저장 프로시저


저장 프로시저

  • 저장 프로시저는 사용빈도가 높은 SQL쿼리들을 미리 정의해둔 SQL집합/그룹이다.
  • 컴파일된 SQL문이라는 것이 특징
  • 성능/관리/속도면에서 큰 장점을 가진다.
-- 생성1 
CREATE proc 프로시져이름 
AS 
"저장프로시져 내용" 
-- 실행 
exec 프로시저이름 
 
-- 예제
CREATE proc GetZipcode01 
AS 
SELECT * FROM zipcode 
WHERE dong LIKE '북가%' 
 
exec GetZipcode01 
-- 생성2 - 매개변수를 사용한 저장프로시져 
CREATE proc 프로시져이름 
@변수명1 정의1, 
@변수명2 정의2 
AS 
"저장프로시져 내용" 
-- 실행 
exec 프로시저이름 외부변수, 외부변수2 
 
-- 예제
CREATE proc GetZipcode02 
@dong varchar(10) --외부변수 선언 
AS 
declare @tmp varchar(100) --내부변수 선언 
SET @tmp = '%'+@dong+'%' 
SELECT * FROM zipcode 
WHERE dong LIKE @tmp 
 
exec GetZipcode02 '북가좌' 
-- 생성3 - 동적 저장프로시져 : 동적 SQL문 
-- 사용기 게시판 코드가 uses 
-- 구입기 게시판 코드가 buys 
-- 라 할 때, 게시판 출력 SQL문은 : 
-- select * from uses, select * from buys 등으로 상황에 따라 바뀌어야 한다. 
 
CREATE proc dyna_sql 
@tbname varchar(10) 
AS 
declare @tmp varchar(100) 
SET @tmp='select * from '+@tbname 
print @tmp 
exec(@tmp) -- 동적 SQL문 실행(스트링 SQL문을 실행한다) 
 
-- 실행 
exec dyna_sql 'uses' -- 사용기 list출력 
exec dyna_sql 'buys' -- 구입기 list출력 
 
-- 또 다른 동적 쿼리문 
ALTER proc dyna_sql2 
@tbname varchar(10), 
@fdname varchar(10), 
@search varchar(10) 
AS 
declare @tmp varchar(100) 
SET @tmp='select * from '+@tbname+' where '+@fdname+'='+@search 
print @tmp 
exec(@tmp) -- 동적 SQL문 실행(스트링 SQL문을 실행한다) 
 
-- 테이블을 생성하는 동적 SQL 
CREATE proc make_board_pds 
@tbname varchar(10), 
@types varchar(5) 
AS 
declare @board varchar(100) 
declare @pds varchar(100) 
declare @sql varchar(400) 
SET @board='create table '+@tbname+'(no smallint, subject varchar(10), views smallint, wdate smalldatetime ' 
SET @pds=', filename varchar(10), sizes smallint, fileimg varchar(10)' 
 
IF @types = 'board' 
  SET @sql=@board+')' 
Else 
  SET @sql=@board+@pds+')' 
 
-- print @sql 
exec(@sql) 
 
-- 실행 
exec make_board_pds 'test','board' -- 게시판 생성 
exec make_board_pds 'test2','pds' -- 자료실 생성 
트랜젝션



MSSQL 트랜잭션

  • SQL 명령 실행시 발생할 수 있는 오류에 대비할 수 있도록 DB를 관리해 주는 명령
  • 트랜잭션 시작 : begin tran
  • 트랜잭션 실행 : commit tran
  • 트랜잭션 취소 : rollback tran
  • 트랜잭션 관련 통계는 구성요소서비스에서 확인 가능
  • select @@error –> 실행되었던 쿼리문결과 에러가 얼마나 났는지 알 수 있다
CREATE proc tran01 
@tbname varchar(10), 
@types varchar(5), 
@comment varchar(20) 
AS 
begin tran 
INSERT tableinfo(tbname, comment, types) 
VALUES(@tbname, @comment, @types) 
 
IF @@error>0 --오류가 발생하면 
  begin 
    print '오류방생!' 
    rollback tran 
  end 
else
  begin 
    print '제대로 실행!' 
    commit tran 
  end 
 
exec tran01 'aaa','board','테스트' 


다른글 읽기