Chapter 6 SQL - 저장프로시저 Stored Procedure

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

• 데이터베이스 내에 저장된 서브루틴(subroutine)
  • 영구저장모듈(PSM, Persistent Storage Module)이라고도 함
  • 데이터 뿐만 아니라 처리 로직(processing  logic)을 DB에 저장할 수 있음을 의미함
• SQL/PSM 표준이 있으나 DBMS 별로 구문에 차이가 있음
  • DBMS 고유의 언어 사용 가능
    • Oracle의 경우 PL/SQL: 표준 SQL + 변수 정의, 조건문, 반복문 등의 기능을 가짐
    • MSSQL Server의 경우 Transact-SQL
    • DB2의 경우 SQL/PL
  • 범용 프로그래밍 언어 사용 가능 (Oracle의 경우 Java)

저장프로시저의 장단점

• 장점
  • 하나의 요청(request)으로 일련의 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 >
    local_var1 data_type;
    ...
BEGIN
    ...
END;

 

SP 실행

CALL stored_procedure (...)

또는

EXECUTE stored_procedure (...)

• 컴파일 에러 로그 확인 방법

select * from USER_ERRORS where NAME = 'stored_procedure' and type = 'PROCEDURE';

여기서 USER_ERRORS는 사용자가 발생시킨 에러를 뜻한다.

스크립트 출력 탭에 DBMS_OUTPUT.put_line의 결과가 출력되게 하려면 호출되기 전에

SET SERVEROUTPUT ON;

이 명령을 실행해두어야 한다.

 

SP 삭제 및 카탈로그 정보

• SP 삭제

DROP PROCEDURE procedure_name;

• SP 카탈로그 정보 확인
  • ALL_OBJECTS: object_type을 'PROCEDURE'로 검색
  • USER_PROCEDURES: 사용자가 정의한 프로시저들
  • USER_SOURCE: 프로시저 소스 코드 (컴파일 에러가 발생한 것 포함)

SP 예

CREATE OR REPLACE PROCEDURE
print_hello(param1 in nvarchar2)
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello ' || param1);
    
END;
/ --- 현재까지 입력된 PL/SQL 블록을 실행함

SET SERVEROUTPUT ON;

EXECUTE print_hello('Foo');

 

SP 예제

dept 테이블에 데이터가 추가되었을 때 dept_size에도 데이터를 추가하려고 한다.

insert into dept_size
select dept, count(*)
from student
group by dept;

이때 insert문을 여러번 실행하면

 

데이터가 중복해서 들어간다.

이를 방지하기 위해

drop table dept_size;

create table dept_size {
    dept nvarchar2(10),
    stnum number(3)
);

CREATE OR REPLACE PROCEDURE refresh_dept_size
IS
BEGIN
    delete dept_size;
    insert into dept_size (dept, stnum)
    select dept, count(*)
    from student
    group by dept;
END;
/

execute refresh_dept_size;

insert문 대신에 프로시져 refresh_dept_size를 정의해 사용한다.

카탈로그 정보를 확인해 보자.

object_type이 procedure인 것을 볼 수 있음

테이블 dept를 변경하고 나서 프로시져 refresh_dept_size를 실행하면 결과가 바뀜

 

create or replace procedure
print_hello (param1 in nvarchar2 )
is
begin
    DBMS_OUTPUT.put_line('Hello ' || param1 );
end;
/

SET SERVEROUTPUT ON;

select * from user_errors
where name = 'PRINT_HELLO';

select * from user_procedures;

execute print_hello('홍길동');

drop table dept_size;

create table dept_size (
    dept nvarchar2(10),
    stnum number(3)
);

select * from dept_size;

create or replace procedure
refresh_dept_size
is
begin

delete from dept_size;

insert into dept_size
select dept, count(*)
from student
group by dept;

end;
/

select * from user_procedures
where object_name = 'REFRESH_DEPT_SIZE';

select * from dept_size;

select * from student;

update student set dept = '전기'
where sno = 100;

execute refresh_dept_size;