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

참조키(외래키)는 데이터의 참조 무결성을 유지하기 위한 필수 저건으로 아래와 같은 특성을 지닙니다.

참조키를 정의하는 기본형식은 아래와 같습니다.

constraint_name : 제약조건의 이름으로 데이터베이스 내에서 유일해야 합니다.

ref_table : 참조되는 테이블 이름입니다.

ref_column : 참조되는 컬럼 또는 컬럼들입니다.

NO ACTION : 참조되는 테이블의 행이 삭제 또는 수정되었을 경우 SQL Server에서는 오류 메세지를 띄우고, 해당 작업은 롤백(RollBack)됩니다.

CASCADE : 참조되는 테이블의 행이 삭제 또는 수정되었을 경우 이를 참조하는 테이블에서 자동으로 변경되도록 설정하는 옵션이니다. 옵션을 지정하지 않으면 디폴트는 NO ACTION 입니다.

그럼 위의 기본형식을 이용하여 실제 테이블을 만들어 보겠습니다.

위의 CREATE 명령어는 참조되는 테이블의 PRIMARY KEY인 cus_id를 참조하여

참조하는 테이블인 Customer_Order의  cus_id를 참조키로 설정하고 있습니다.

위와 같이 제약조건명을 안주고 아래와 같이 좀 더 간단한 방법으로도 참조키를 설정할 수 있습니다.

위의 명령대로 테이블을 만들고  Enterprise Manager를 열어서 새 데이터베이스 다이어그램을 실행하고, 만들어진 두 테이블을 추가해보면 제약조건의 이름을 DBMS가 자동으로 생성해 줬다는 것을 확인 할 수 있습니다. 위에서 설명한 CASCADE와 NO ACTION 옵션은 아래와 같이 설정합니다.

테이블 생성 중에 참조키를 설정하지 않고, 나중에 필요하게 되었을때는 다음과 같이 ALTER TABLE 이용해서 참조키를 설정 할 수 있습니다.