저장 프로시져 procedure

저장 프로시저란 DB를 다룰 때 사용하는 복잡한 쿼리문을 함수처럼 등록해두고 
간단하게 사용할 수 있는 명령이다. 또한, 매개변수도 반영할 수 있기 때문에 편리하다.

-- 저장 프로시저 특징
-- SQL Server 성능 향상
-- 최초 실행시 최적화 및 컴파일 과정 수행 수 캐시 메모리 적재
-- 재 실행시 캐시에서 호출 후 재사용
-- 유지관리 용이
-- 외부 응용프로그램에서 SQL문장을 직접 작성하지 않고 저장 프로시저 내에서만 유지 관리
-- 모듈식 프로그래밍 가능
-- 보안 강화
-- 네트워크 전송량 감소

 

 

 

 

 

 

 

 

-- 기본 생성

create procedure usp_members
as
	select * from customersTbl;

 

-- 실행(함수실행할때 쓰는 exec사용)

exec usp_members

 

 

 

 

 

--매개변수 1개 조건일때의 생성

create procedure usp_users1
	@userName nvarchar(10)
as
	select * from customersTbl where custName = @userName;

--실행( 매개변수 값을 넣어줌)

exec usp_users1 '홍길동'

 

 

 

 

 

--매개변수가 2개이상일 때의 생성

create procedure usp_users2
	@saleDate date,
	@amount int
as
	select * from salesTbl
	where saleDate > @saleDate and sales > @amount;

 

--실행

exec usp_users2 '2018-09-05', 1000;

exec usp_users2 @amount = 1000, @saleDate = '2018-09-05';

 

 

 

 

 

-- default 값 지원(인자를 안주고 호출함)

create procedure usp_users3
	@birthDate date = '1980-01-01',
	@addr nvarchar(5) = '서울'
as
	select * from customersTbl
	where birthDate < @birthDate and addr = @addr;
go

exec usp_users3 

 

 

 

 

 

 

-- 출력 매개풀 생성

create procedure usp_users4
	@txtValue nchar(10),
	@outValue int OUTPUT
as
	insert into tempTbl values (@txtValue)
	select @outValue = IDENT_CURRENT('tempTbl')
go

-- tempTbl 생성
create table tempTbl (id int identity, txt nchar(10));
go



-- return 값 확인해보기
-- 1. 변수선언
declare @myValue int;
-- 2. 아웃풋(return)
exec usp_users4 '임시저장값', @myValue output;
-- 3. 출력
print '현재 ID 값 ==> ' + cast(@myValue as char(5))

 

 

 

 

 

 

-- 현재 프로시져 조회

select o.name, m.definition
from sys.sql_modules m
join sys.objects o ON m.object_id = o.object_id and o.type = 'P'

 

 

 

 

 

 

 

--if문

create procedure usp_ifElse
	@userName nvarchar(10)
as
	declare @bYear int
	select @bYear = year(birthDate) from customersTbl
	where custName = @userName;
	if(@bYear >= 2020)
		begin
			print N'아직 미성년자 입니다.';
		end
	else
		begin
			print N'성인 입니다';
		end
go



exec usp_ifElse '홍길동'

 

 

 

 

 

 

--case 문

create proc usp_case
	@userName nvarchar(10)
as
	declare @bYear int
	declare @zodiacSign nchar(3)
	select @bYear = year(birthDate) from customersTbl
	where custName = @userName

	set @zodiacSign = 
	case
		when ( @bYear%12 = 0) then '원숭이'
		when ( @bYear%12 = 1) then '닭'
		when ( @bYear%12 = 2) then '개'
		when ( @bYear%12 = 3) then '돼지'
		when ( @bYear%12 = 4) then '쥐'
		when ( @bYear%12 = 5) then '소'
		when ( @bYear%12 = 6) then '호랑이'
		when ( @bYear%12 = 7) then '토끼'
		when ( @bYear%12 = 8) then '용'
		when ( @bYear%12 = 9) then '뱀'
		when ( @bYear%12 = 10) then '말'
		else '양'
	end;

	print @userName + N'의 띠 ==> ' + @zodiacSign;

	exec usp_case '홍길동'

go

 

 

 

 

 

 

 

-- return문 사용

create proc usp_return
	@userName nvarchar(10) -- 들어오는 값
as
	declare @userId char(8);
	select @userId = custId from customersTbl
	where custName = @userName;
	if (@userId <>'') -- 없을 때(다를때)
		return 0;
	else
		return -1;
go

--  return을 받아야하므로 변수 선언 후 함수사용 : 0
declare @retVal int;
exec @retVal=usp_return '홍길동'
select @retVal
GO

-- 없는 것을 줬을 때 : -1
declare @retVal int;
exec @retVal=usp_return '꺼벙이'
select @retVal
GO

 

 

 

 

 

-- 사용자 정의의 타입 테이블  test

 

 

-- 테이블 생성
create type userTblType as table(
	userId char(8),
	name nvarchar(10),
	addr nvarchar(5)
);
go

-- 사용하는 프로시져 생성 (table -> table)
create proc usp_tableType
	@tblParam userTblType readonly
as
begin
	select * from @tblParam where addr = '부산';
end
go

-- 변수 선언 후 테이블처럼 데이터를 넣는다
declare @tblVar userTblType;
insert into @tblVar
select custId, custName, addr from customersTbl

-- 실행
exec usp_tableType @tblVar