PL/SQL, 저장 프로시저(SP)

PL/SQL

• SQL만으로 구현이 어렵거나 구현 불가능한 작업을 수행하기 위해 오라클에서 제공하는 프로그래밍 언어
• 일반 프로그래밍 요소 다 가지고 있고, DB 업무처리하기 위한 최적화된 언어
• 기본단위 : 블록(BLOCK)
• 기본형식
  • DECLARE (선언부) : 변수, 상수, 커서 선언
  • BEGIN (실행부) : SQL에 선택, 반복처리 등
  • END

DECLARE
	[실행에 필요한 여러 요소 선언];
BEGIN
	[작업을 위해 실제 실행하는 명령어];
EXCEPTION
	[PL/SQL 수행 도중 발생하는 오류 처리];
END;

 

• 화면 출력하기
• SERVEROUTPUT 환경변수 값을 ON 으로 변경해야한다

SET SERVEROUTPUT ON;
BEGIN
	DBMS_OUTPUT.PUT_LINE('HELLO');
END;
/

 

• 반복문

DECLARE
  V_NUMBER NUMBER := 13;
BEGIN
  IF MOD(V_NUMBER, 2) = 1 THEN
    DBMS_OUTPUT.PUT_LINE('홀수입니다!');
  END IF;
END;
/

-- 기본 loop
DECLARE
  V_CNT NUMBER := 0;
  V_SUM NUMBER := 0;
BEGIN
  LOOP
    V_CNT := V_CNT + 1;
    V_SUM := V_SUM + V_CNT;
    DBMS_OUTPUT.PUT_LINE(V_SUM);
    EXIT WHEN V_CNT = 10;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('총 합: ' ||V_SUM);
END;
/

-- FOR LOOP
DECLARE
  V_CNT NUMBER;
  V_SUM NUMBER := 0;
BEGIN
  FOR V_CNT IN 1..10 LOOP
    V_SUM := V_SUM + V_CNT;
    DBMS_OUTPUT.PUT_LINE(V_SUM);
  END LOOP;
END;
/

-- WHILE LOOP
DECLARE
  V_CNT NUMBER := 0;
  V_SUM NUMBER := 0;
BEGIN
  WHILE V_CNT < 10 LOOP
    V_CNT := V_CNT + 1;
    V_SUM := V_SUM + V_CNT;
    DBMS_OUTPUT.PUT_LINE(V_SUM);
  END LOOP;
END;
/

 

 

SP

• Stored Procedure : 저장 프로시저
• 서버에 저장해놓고 주기적으로 반복해서 사용
• 쿼리문들의 집합
• 여러 동작들을 일괄적으로 처리할때 사용
• 서버 성능 향상
• 유지보수 용이
• 보안강화
• 프로그래머가 생성해놓은 쿼리문을 마치 하나의 메소드 형식으로 관리하는 것

 

• 프로시저 생성과 실행

-- 프로시저 생성
CREATE OR REPLACE PROCEDURE pro_noparam
IS
  V_EMPNO NUMBER(4) := 7788;
  V_ENAME VARCHAR2(10);
BEGIN
  V_ENAME := 'SCOTT';
  DBMS_OUTPUT.PUT_LINE('V_EMPNO: '|| V_EMPNO);
  DBMS_OUTPUT.PUT_LINE('V_ENAME: '|| V_ENAME);
END;
/

-- 프로시저 실행
EXECUTE pro_noparam;

 

• 파라미터를 사용하는 프로시저
  • 파라미터를 지정할 때 사용하는 모드 
    • IN : 지정하지 않으면 기본값으로 프로시저를 호출할 때 값을 입력받음
    • OUT : 호출할때 값을 반환
    • IN OUT  :  호출할 때 값을 입력받은 후 실행 결과 값을 반환

-- 파라미터 사용하는 프로시저

-- 학번으로 검색하여, 같은 학번 학생의 학년을, 입력받은 학년으로 변경한다
CREATE OR REPLACE PROCEDURE TEST2
(
  V_STU_NO IN STUDENT.STU_NO%TYPE,
  V_STU_GRADE IN STUDENT.STU_GRADE%TYPE  
)

IS
BEGIN
  UPDATE STUDENT
  SET STU_GRADE = V_STU_GRADE
    WHERE STU_NO = V_STU_NO;
    
END TEST2;
/

-- 실행
EXECUTE TEST2(20153075, 3);