[SQL] 저장 프로시저(SP)

저장 프로시저(SP, Stored Procedure)

• 데이터베이스 내에 저장된 서브루틴
• 영구저장모듈(PSM) 이라고도 함
• 데이터 뿐만아니라 처리 로직을 DB에 저장할 수 있음을 의미

SQL/PSM 표준이 있으나 DBMS 별로 구문에 차이가 존재

• Oracle의 경우 PL/SQL : 표준 SQL 언어 + 변수, 정의, 조건문, 반복문 등의 기능을 가짐
• 범용 프로그래밍 언어 사용 가능 (Oracle의 경우 Java)

💡 저장프로시저의 장점

• 하나의 요청으로 일련의 SQL문을 수행
• 미리 컴파일되어 있는 코드를 실행하므로 처리시간이 줄어듬
• 응용프로그램 수준의 데이터 무결성 관리를 대신함
• 응용프로그램을 수정하지 않고서도 프로그램의 동작을 변경

💡 저장프로시저의 단점

• DBMS 별로 SP 지원 방식 및 기능에 차이가 있어서 코드의 이식성이 좋지않음
• SP개발을 위한 IDE 환경이 제공되지 않는 DBMS의 경우에는 개발 및 디버깅이 어려움
• 응용프로그램과 연계되는 경우가 흔하여 SP코드를 변경하는 경우에는 오류의 발생 가능성이 높음

---

[SP 생성]

CREATE\[OR REPLACE\] PROCEDURE <procedure_name> {  
<arg1> [IN|OUT|IN OUT] (data_type),  
<arg2> [IN|OUT|IN OUT] (data_type),  
...  
}  
IS|AS  
...  
BEGIN  
...  
END;

[SP 실행]

CALL <stored\_procedure>  
-- 또는  
EXECUTE <stored\_procedure>

-- [컴파일 에러 로그 확인 방법]

select \* from USER\_ERRORS where NAME = 'stored\_procedure'  
and type = 'PROCEDURE';

[SP 삭제]

DROP PROCEDURE <procedure_name>

[SP 카탈로그 정보]

• ALL_OBJECTS : object_type을 'PROCEDURE'으로 검색
• USER_PROCEDURES : 사용자가 정의한 프로시저들
• USER_SOURCE : 프로시저 소스 코드( 컴파일 에러 발생도 포함 )

 🍀 SP 예시

CREATE OR REPLACE PROCEDURE  
print\_hello( param in nvarchar2 )  
IS  
BEGIN  
  DBMS\_OUTPUT.PUT\_LINE( 'hello' || param );  
END;  

EXECUTE print\_hello('Foo');  

-- 실행결과  
-- >> hello Foo