[한기교] SQL 활용 필기(15) - 저장 프로시저와 사용자 정의 함수(다시듣기)

< 프로시저 >

ㅁ 프로시저의 개념
: 자주 자용되는 질의문들을 하나로 묶어서 저장해두고 필요할 때 마다 명령문처럼 실행할 수 있도록 해주는 개념
: 범용 언어의 프로시저 / 함수와 유사한 개념

ㅁ 일반 질의문과의 차이점
1) 일반 질의문
- 사용자 또는 응용 프로그램이 실행하고자 하는 SQL문을 DBMS에 전송하고 그 결과를 받음
- 대량의 복잡한 질의문들이 반복적으로 입력되면 그만큼 시스템에 부담이 됨
- DBMS에도 처리해야 하는 일이 늘어나 시스템에 더 큰 부담이 됨
2) 프로시저
- 프로시저 내용은 DBMS에 포함되어 있고 실행 방안도 미리 작성되어 있음
- 사용자나 응용 프로그램은 프로시저 이름과 매개변수 값(필요 시)만을 전송하면 됨
- 복잡한 SQL문의 단순화

 

ㅁ 프로시저 생성 구문

CREATE [PROCEDURE|PROC] 프로시저이름 
AS 
BEGIN SQL문 END //BEGIN END는 SQL문이 하나만 있다면 생략 가능 

ㅁ 프로시저 실행 문법

EXEC 프로시저이름 

ㅁ 프로시저 수정 문법

ALTER PROCEDURE 프로시저이름 
AS SQL문 

DROP PROCEDURE 프로시저이름

 

ㅁ 프로시저의 매개변수
- 프로시저 실행 시 조건값 등을 변경 할 수 없을까?
- 사원번호 109번에 대한 정보를 추출하는 저장 프로시저를 생성하고 이를 110번에 대한 정보를 추출하도록 저장 프로시저 변경
- 저장 프로시저 수행 시 사원번호를 입력으로 주어 해당 사원정보를 추출하도록 할 수 없을까? --> 매개변수 사용
- 저장 프로시저 수행 시 수행 질의문에 특정 값을 매개변수로 전달할 수 있도록 하여 다양한 조건을 하나의 질의문으로 수행할 수 있도록 지원해 줌

 

ㅁ 입력 매개변수의 선언
- 생성

CREATE PROCEDURE 프로시저이름 
@매개변수명 타입, … 
AS SQL문 

 

- 실행

EXEC 프로시저이름 매개변수값

ㅁ 출력 매개변수
- 입력 매개변수와 반대로 프로시저의 처리 결과값을 반환하는 매개변수
- 출력 매개변수 선언

CREATE PROCEDURE 프로시저이름 
@매개변수명 타입 OUTPUT, … 
AS 
SELECT @매개변수명= 속성명 
FROM … WHERE…

 

< 사용자 정의 함수>

ㅁ 사용자 정의 함수
- 사용자 정의 함수는 프로시저와 달리 RETURN문을 이용하여 하나의 값을 반드시 반환해야함

 

 

ㅁ 프로시저와의 차이점

 

 

- 사용자 정의 함수의 기본 문형

CREATE FUNCTION 함수명 

- (@매개변수명 타입, ...)

RETURNS 반환타입 

- AS

[BEGIN]SQL 문[END]