익명 PL/SQL 예제

익명 PL/SQL이란 이름을 가지지 않은 PL/SQL프로그램으로 서버에 저장되지 않고, 일회성으로 실행되는 유형의 PL/SQL프로그램을 말하는 것이다.

 

앞의 글에서 생성한 scott 계정의 테이블 emp를 사용해 다음과 같은 사항을 처리하는 PL/SQL 프로그램을 생각해보자.

 

-요구 사항 : (사번, 사원명, 업무)가 값 (v_empno, v_ename, v_job)으로 주어졌을 때,

1. 사번 v_empno가 테이블 emp에 존재한다면, 테이블의 해당 로우의 (사원명,업무)를 (v_ename, v_job)으로 변경

2. 존재하지 않으면, -해당 정보를 테이블 emp에 등록

                         -등록 시 부서 코드 값은 20을 기본값으로 사용

 

DECLARE
	--상수
    c_default_deptno CONSTANT NUMBER := 20; -- 기본 부서 코드
    
    --처리 대상 사원 정보를 값으로 가지는 변수 정의
    v_empno NUMBER(4) := 7788; --처리 대상 사번
    v_ename VARCHAR2(10) := 'SCOTT'; --처리 대상 사원명
    v_job VARCHAR2(9) := 'ANALYST'; --처리 대상 사원의 업무
    
    -- 추가 변수 
    v_cnt NUMBER;  --건수
    
    BEGIN
    	SELECT COUNT(*)
        	INTO v_cnt
            FROM emp
        WHRER empno = v_empno;
        
        --1. 해당 사번이 테이블 emp에 존재하면
        IF v_cnt > 0 THEN
        -- 1.1 (사원명, 업무)를 (v_ename, v_job)으로 변경
        	UPDATE emp
            	SET ename = v_ename,
                	job = v_job
              WHERE empno = v_empno;
              
       		DBMS_OUTPUT.PUT_LINE('사원 "' || v_ename || "'의 정보가 변경되었습니다.');
       ELSE --2. 해당 사번이 emp 테이블에 존재하지 않으면
       	-- 새로운 사원 정보를 테이블에 등록
        INSERT INTO emp(empno, ename, job, deptno)
        VALUES(v_empno, v_ename, v_job, c_default_deptno);
        
        DBMS_OUTPUT.PUT_LINE('신입사원 "' || v_ename || "'의 정보가 등록되었습니다.');
       END IF;
       COMMIT;
      EXCEPTION WHEN OTHERS THEN
      	ROLLBACK; --모든 변경 취소
        DBMS_OUTPUT.PUT_LINE('응용 프로그램 오류 발생' || CHR(10) || SQLERRM);
      END;
   /