[SQL개발자] SQL 활용 - 절차형 SQL

✔ 절차형 SQL

절차형 SQL이란

• SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈을 생성
• 프로시저, 사용자 정의 함수, 트리거 등이 존재
• 작성자의 기준으로 트랜잭션을 분할할 수 있음
• 절차적 코드는 PL/SQL 엔진이 처리하고 일반적인 SQL 문장은 SQL 실행기가 처리

PL/SQL의 특징

• Block 구조로 되어있어 각 기능별로 모듈화가 가능
• 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환
• IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 함
• DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있음
• Oracle에 내장되어 있으므로 Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있음
• 응용 프로그램의 성능을 향상시킴
• 여러 SQL 문장을 Block으로 묶고 한 번에 Block 전부를 서버로 보내기 때문에 통신량을 줄일 수 있음
• 변수와 상수 등을 사용하여 일반 SQL 문장을 실행할 때 WHERE절의 조건 등으로 대입할 수 있음
• PL/SQL에서 동적 SQL 또는 DDL 문장을 실행할 때는 EXECUTE IMMEDIATE를 사용해야 함

저장 모듈

• SQL 문장을 데이터베이스 서버에 저장하여
  사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램
• 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램
• Oracle의 저장 모듈에는 프로시저, 사용자 정의 함수, 트리거가 존재
  • 저장형 프로시저는 SQL을 로직과 함께 데이터베이스 내에 저장해 놓은 명령문의 집합을 의미
  • 사용자 정의 함수는 단독적으로 실행되기 보다는
    다른 SQL문을 통하여 호출되고 그 결과를 리턴하는 SQL의 보조적인 역할을 함
  • 트리거는 특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을 때
    데이터베이스에 자동으로 동작하도록 작성하는 프로그램으로
    데이터의 무결성과 일관성을 위해 사용자 정의 함수를 사용하도록 함

프로시저와 트리거의 차이점

• 프로시저
  • CREATE Procedure 문법 사용
  • EXECUTE 명령어로 실행
  • COMMIT, ROLLBACK 실행 가능 (TCL 사용 가능)

// 프로시저의 구성도
DECLARE (필수)
BEGIN (필수)
 CONTROL
 SQL
 EXCEPTION
 TRANSACTION
END (필수)

// 프로시저의 생성
CREATE [OR REPLACE] PROCEDURE 프로시저명(파라미터)
[지역변수 선언]
BEGIN
 프로시저 BODY;
END;

// 프로시저의 실행
EXECUTE 프로시저명;
EXEC 프로시저명;
CALL 프로시저명;

// 프로시저의 제거
DROP PROCEDURE 프로시저명;

• 트리거
  • CREATE Trigger 문법 사용
  • 생성 후 자동으로 실행
  • COMMIT, ROLLBACK 실행 안됨 (TCL 사용 불가능)

// 트리거의 구성
 DECLARE (필수)
EVENT (필수)
BEGIN (필수)
 CONTROL
 SQL
 EXCEPTION
END (필수)

// 트리거의 생성
CREATE [OR REPLACE] TRIGGER 트리거명 동작시기 동작 ON 테이블명
[REFERENCING NEW | OLD AS 테이블명]
[FOR EACH ROW [WHEN 조건식]]
BEGIN
 트리거 BODY;
END;

// 트리거의 제거
DROP TRIGGER 트리거명;