저장프로시저와 사용자 지정함수 사용예

C#에서 비교

프로시저(Procedure) : 리턴값없으면 ---- 저장프로시저와는 다른말

public void test()

{

}

function : 리런값있으면 평션

public void test()

{

  return;

}

개념

저장 프로시저(Stored Procedure)란 SQL Server에서 제공되는 프로그래밍 기능

저장 프로시저는 한마디로 쿼리문의 집합으로 어떠한 동작을 일괄 처리하기 위한 용도로 사용

구문 형식

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 

    [ { @parameter [ type_schema_name. ] data_type } 

        [ VARYING ] [ = default ] [ [ OUT [ PUT ] 

    ] [ ,...n ] 

[ WITH <procedure_option> [ ,...n ]

[ FOR REPLICATION ] 

AS { <sql_statement> [;][ ...n ] | <method_specifier> }

저장프로시져

저장프로시저 확인

프로시저 간단한 사용법

CREATE PROC selstudent -- 저장프로시저 만들기

AS

SELECT * FROM student

GO

EXEC selstudent -- EXECUTE selstudent      -- 저장된 프로시저를 쓰는것이다

ALTER PROC selstudent --프로시저 수정

AS

SELECT Name, majorcode FROM student

GO

DROP PROC selstudent -- 프로시저 삭제

매개변수의 사용

구문 형식

정의 

@입력매개변수이름  데이터형식  [ = 디폴트값]

@출력매개변수이름  데이터형식  OUTPUT

실행

EXECUTE 프로시저이름 [전달값]

EXECUTE 프로시저이름 @변수명 OUTPUT

프로그래밍 기능

프로그래밍 기능을 이용해 유연한 기능으로 확장 가능

리터 값의 이용

RERUN 문을 사용해서 성공 및 실패 여부 확인

저장프로시저내 오류처리

@@ERROR 함수 및 TRY/CATCH 문을 이용

임시 저장프로시저

# 또는 ##을 붙임

TEMPDB에 저장됨

use study_exam2

CREATE PROC usp_studentfind -- @ 골뱅이는 변수

@SNO NCHAR(8) --@SNO 매개변수

AS --

SELECT * FROM student --

WHERE SNO = @SNO --

GO

EXEC usp_studentfind '0757003' -- 저장프로시저 이름적고 변수이름 적으면된다

ALTER PROC usp_studentfind -- @ 골뱅이는 변수

@SNO NCHAR(8), --@SNO 매개변수   , 콤마를 빠뜨리지 말거라

@MajorCode NCHAR(2) -- 

AS --

SELECT * FROM student --

WHERE SNO = @SNO AND --@변수 2개선언

MajorCode = @MajorCode --

GO

EXEC usp_studentfind '0757003' ,'02'  --프로시저 '첫번째 매개변수' , '두번째매개변수'

저장프로시저의 특징

SQL Server의 성능을 향상시킬 수 있다.

동일한 저장 프로시저가 자주 사용될 경우에는 일반 쿼리를 반복해서 실행하는 것보다 SQL Server의 성능이 크게 향상될 수 있다

모듈식 프로그래밍이 가능하다.

저장프로시저를 생성해 놓으면, 언제든지 실행이 가능하다

보안을 강화할 수 있다.

사용자 별로 테이블에 접근 권한을 주지 않고, 저장 프로시저에 접근 권한을 줌으로써 좀 더 보안을 강화한다

네트워크 전송량의 감소시킨다.

저장 프로시저 이름 및 매개변수 등 몇 글자의 텍스트만 전송하면 되므로 네트워크의 부하를 줄일 수 있다

저장프로시저의 예

EXEC sp_help student

프로시저 사용

프로시저 사용

USE pubs

GO

-- 여기도 매개변수의기본값을 넣을수있다

CREATE PROC usp_titleSelectArow

(

@title_id VARCHAR(6)

)AS

SELECT *

FROM titles

WHERE title_id = @title_id

GO

EXEC usp_titleSelectArow 'BU1032'

 

ALTER PROC usp_titleSelectArow

(

@title_id VARCHAR(6) = 'MC2222'  -- 이것은 디폴트값이다 값이없으면 MC2222로간다는말이지

)AS

SELECT *

FROM titles

WHERE title_id = @title_id

GO

EXEC usp_titleSelectArow DEFAULT --  매개변수를 집어넣지 않으면 기본값으로 출력된다 ㅋ