[본문스크랩] Mssql 프로시저의 기본

//프로시저 생성

create procedure sp_user   //proc로 줄여쓸 수 있다.       

as

begin

select u.id,name,age,login

from userinfo u, userlog l

where u.id=l.id

end

 

//프로시저 출력

exec sp_user  

 

 //개체 확인

sp_helptext sp_user  

 

 //프로시저 삭제

drop proc sp_user  

 

//#으로 테이블과 마찬가지로 임시프로시저 생성가능 (연결이 끊어지면 사라진다.)

create procedure #sp_user ~~  

 

//임시 프로시저 출력      

#sp_user  

 

엔터프라이즈에 가서 도구 안에 마법사를 가면 쉽게 프로시저를 만들 수 있다. 프로시저는 주로 삽입, 삭제, 업데이트에 쓰인다.

조회는 주로 뷰를 이용한다. 역시 엔터프라이즈에 가서 새뷰를 이용하면 보면서 쉽게 만들 수 있다.

뷰는 프로시저와 달리 테이블처럼 출력 할 수 있다.

select * from 뷰명

 

//매개변수(패러미터)가 있는 프로시저

create proc sp_user1

@age int

as

begin

select u.id,name,age,login

from userinfo u, userlog l

where u.id=l.id and age>@age

end

 

//출력시

sp_user1 25   //프로시저명 뒤에 매개변수값을 줘야한다.

//패러미터 값과 초기값을 가지는 프로시저 만들기

create proc sp_user3

@age int = 25,   // 값이 없을 경우 age=25의 초기값을 지닌다. 

@sex char(1) = 'm'

as

begin

select u.id,name,age,login

from userinfo u, userlog l

where u.id=l.id and age = @age and sex = @sex

end

 

sp_user3 22,'f'

//프로시저 문자열을 이용하기

create proc sp_test

@tb varchar(20),

@titleid varchar(20)

as

begin

declare @sql varchar(100)

set @sql = 'select * from ' + @tb + ' where title_id ='''+ @titleid+''''

exec(@sql)   //exec는 @sql을 실행한다는 뜻.

end

sp_test 'titles','bu1032'

//프로시저의 문자열 이용 (@num 값의 타입을 int가 아닌 varchar로 받아야 한다.)

if exists(select * from dbo.sysobjects where id=object_id('sp_extables'))

        drop proc sp_extables

go

create proc sp_extables

@num varchar(10)

as

begin

declare @str varchar(100)

set @str='select top ' +@num+ ' * from extable order by idx desc'

exec(@str)

end

sp_extables '10'

//output이 있는 프로시저 (위의 것은 input만 있었고 지금부터.. 중요~~)

create proc sp_getusername

@id varchar(10),

@name varchar(10) output,

@login int output

as

begin

select @name=name, @login=login from userinfo u, userlog l

where u.id=l.id and u.id=@id

end

declare @name varchar(10), @login int

exec sp_getusername 'sumi',@name output, @login output

select @name, @login   //sumi라는 id를 가진 사람의 name과  login을 출력하게 된다.