zany2974

저장 프로시저(stored procedure)

-      실행할 단위로 저장해 놓구 필요할 때 사용

-      편리하며 처리 속도도 빠르다

----------------------저장프로시져---------------------------------

-- 1. 매개변수가없는경우

use testdb

create procedure usp_usertbl

as

select * from userTbl

usp_usertbl

execute usp_usertbl

exec usp_usertbl

-- 2. 매개변수가있는경우(저장프로시저의변수는char형보다varchar형을쓰는게좋다

drop procedure usp_usertbl

create procedure usp_userTbl @addr nvarchar(10)

as

select * from userTbl where addr=@addr

usp_usertbl '서울'

usp_usertbl '제주'

usp_usertbl 'LA'

-- 3. 매개변수가있을때기본값을설정한경우

drop procedure usp_userTbl

create procedure usp_userTbl @addr nvarchar(10) = '서울'

as

select * from userTbl where addr=@addr

usp_usertbl '서울'

usp_usertbl '제주'

usp_usertbl

-- 4. output 매개변수를사용했을경우

drop procedure usp_userTbl

create procedure usp_userTbl

@id nvarchar(10), @name nvarchar(10) output

as

select @name = (select name from userTbl where userid=@id)

declare @n varchar(10)

exec usp_userTbl 'ajh', @n output -- 반환값이있을때는반드시exec로실행

select @n

--  매개변수는varchar를써라

use pubs

select * from titles

declare @cname char(5)

set @cname = 'T'

select * from titles where title like @cname+ '%'

-- 위쿼리문시행이값이안나오는이유? char로할시고정되므로

-- 'T    %' 이것을검색rtrim(@cname) + '%' 이렇게써야한다.

--------------------------------------------------------------------------

----------------------- 예제

-- NorthWind 데이타베이스, Orders 테이블이용

-- 시작주문날짜와끝주문날짜를입력하여주문현황을검색

-- 예) sp_orders '1996/7/1','1997/12/31'

use northwind

select * from orders

create procedure sp_orders @s_date smalldatetime, @e_date smalldatetime

as

select * from orders where orderdate >= @s_date and orderdate <= @e_date

-- select * from orders where orderdate between @s_date and @e_date

sp_orders '1996/7/1','1996/7/31'

----------------------- 예제

-- testDB 데이터베이스, usertbl 테이블이용

-- 데이터삽입하는프로시저작성

-- 잘처리가되었으면'잘처리가되었습니다.'라는메세지출력

-- 예) sp_instUserTbl 'ccc',' 홍길순',2000,'서울'

use testDb

select * from usertbl

create procedure sp_instUserTbl @id varchar(10), @name varchar(10), @birth int, @addr varchar(10)

as

insert into usertbl(userid,name,birthyear,addr) values(@id,@name,@birth,@addr)

if @@rowcount >= 1

             print '잘처리되었습니다.'

sp_instUserTbl 'cdy',' 홍길순',2000,'서울'

-- 전역변수@@rowcount

create procedure sp_test

             as

             delete from t1

             select @@rowcount -- 여기에는최근에시행했던쿼리문의결과카운트가저장된다

sp_test

----------------------- 예제

-- testDB 데이터베이스, usertbl 테이블이용

-- 이름을입력하여검색(단일부만입력하여도검색가능)

-- 예) usp_name '김'

use testDb

select * from usertbl

create procedure usp_name @name varchar(10)

as

select * from usertbl where name like '%' + @name + '%'

usp_name '김'

----------------------- 예제

-- northwind 데이타베이스orders 테이블이용

-- 일단orders테이블에내용을임의의테이블t1에복사한다.

-- 예) sp_ordersupdate 'usa','american',@cnt

-- t1 테이블에있는shipcounry 컬럼에있는usa는american으로변경

-- select @cnt하면총변경된숫자가출력되게한다.

use northwind

select * into t1 from orders

select * from t1

create procedure sp_ordersupdate @old_country varchar(10),@new_country varchar(10), @cnt int output

as

update t1 set shipcountry=@new_country where shipcountry=@old_country

set @cnt = @@rowcount

declare @cnt int

exec sp_ordersupdate 'usa','american', @cnt output

select @cnt

----------------------- 예제

--테이블이름을입력하여자동으로테이블이생성되게한다.

--예) sp_createTable 'member', 5

--컬럼은임의로생성한다.

--같은이름의테이블이없을경우에만생성되게한다.

--테이블이5개가만들어진다(member1, member2...)

use testdb

create procedure sp_createTable @tbl_name varchar(10), @cnt int

as

if exists(select name from sys.objects where type = 'u' and name= @tbl_name + '1')

             begin

                           print '동일한테이블이존재합니다'

             end

else

             begin

                           declare @i int

                           set @i = 1

                           while(@i<=@cnt)

                                        begin

                                                     declare @sql nvarchar(500)

                                                     set @sql = 'create table ' + @tbl_name

                                                     + convert(varchar(100),@i) + '( id int, name varchar(10) )'

                                                     exec sp_executesql @sql

                                                     print ('테이블' + @tbl_name

                                                     + convert(varchar(100),@i) + '을생성했습니다.')

                                                     set @i = @i + 1

                                        end

             end

drop  procedure sp_createTable

sp_createTable 'member', 2