Oracle PL/SQL - 개념, 커서

김소희·2025년 11월 7일
sql

PL/SQL 개요

PL/SQL은 Oracle's Procedural Language extension to SQL의 약자로, SQL문장에서 변수정의, 조건처리(IF), 반복처리(LOOP, WHILE, FOR) 등을 지원하며, 오라클 자체에 내장되어 있는 Procedure Language이다.

특징

  • 변수, 연산자, 제어문을 사용한 프로그래밍이 가능하다
  • 컴파일이 필요하므로 툴이 필요하다
  • 블록 구조로 되어 있고, 자체 컴파일 엔진을 가지고 있다
  • 프로시저, 트리거를 등록해서 사용하거나 1회용으로도 쓸 수 있다
  • 굉장히 옛날에 만들어져 방식이 구식이며, 세미콜론을 많이 사용한다

환경 설정

Tool > 보기 > DBMS 출력창 > + 버튼 클릭 > 사용자 접속(개발자)
DBMS 출력창은 이클립스의 console 창과 같은 역할을 한다.

PL/SQL 기본 구조

PL/SQL은 다음 세 부분으로 구성된다:

  • 선언부(DECLARE): 변수 선언
  • 실행부(BEGIN ~ END): 변수 값 할당, 제어구문 실행
  • 예외부(EXCEPTION): 예외 처리

선언부에 적힌 변수들은 해당 BEGIN~END 블록 내부에서만 유효(scope) 를 가진다.

기본 실행 예제

BEGIN
  DBMS_OUTPUT.PUT_LINE('HELLO WORLD');
END;

변수 선언과 사용

기본 변수 선언

DECLARE
  vno number(4);
  vname varchar2(20);
BEGIN
  vno := 100;
  vname := 'kglim';
  DBMS_OUTPUT.PUT_LINE(vno);
  DBMS_OUTPUT.PUT_LINE(vname || '입니다');
END;

변수의 스코프는 DECLARE와 BEGIN ~ END 블록 내에서만 유효하다.

변수 선언 방법

DECLARE
  v_job varchar2(10);
  v_count number := 10;  -- 초기값 설정
  v_date date := sysdate + 7;  -- 초기값 설정
  v_valid boolean not null := true;

SELECT 결과를 변수에 담기

SELECT로 실행된 결과를 변수에 담을 수 있다. 단, 결과가 1개만 가능하다.

DECLARE
  vno number(4);
  vname varchar2(20);
BEGIN
   select empno, ename
      into vno, vname  -- INTO 키워드로 변수에 담는다
   from emp
   where empno=&empno;  -- & 는 입력값을 받는 역할(Scanner와 유사)
   
   DBMS_OUTPUT.PUT_LINE('변수값 : ' || vno || '/' || vname);
END;

DML 작업 (INSERT, UPDATE, DELETE)

DML 작업에는 COMMIT이 필요하다.

실습 테이블 생성

create table pl_test(
  no number, 
  name varchar2(20), 
  addr varchar2(50)
);

INSERT 예제

DECLARE
  v_no number := '&NO';
  v_name varchar2(20) := '&NAME';
  v_addr varchar2(50) := '&ADDR';
BEGIN
  insert into pl_test(no, name, addr)
  values(v_no, v_name, v_addr);
  commit; -- 넣어주기
END;

변수 타입 제어

타입 지정 방법

  1. 일반 타입: v_empno number(10)
  2. %TYPE: v_empno emp.empno%TYPE - 테이블 컬럼의 타입을 그대로 사용한다
  3. %ROWTYPE: v_row emp%ROWTYPE - 테이블의 모든 컬럼 타입 정보를 담는다

%ROWTYPE 사용 예제

DECLARE
  v_emprow emp%ROWTYPE; 
BEGIN
  select *
    into v_emprow
  from emp
  where empno=7788;
  
  DBMS_OUTPUT.PUT_LINE(v_emprow.empno || '-' || v_emprow.ename || '-' || v_emprow.sal);
END;

시퀀스와 함께 사용하기

-- 시퀀스 생성 (8001~9999)
create sequence empno_seq
increment by 1
start with 8000
maxvalue 9999
nocycle
nocache;

-- 시퀀스를 사용한 INSERT
DECLARE
  v_empno emp.empno%TYPE;
BEGIN
  select empno_seq.nextval
    into v_empno
  from dual;
  
  insert into empdml(empno, ename)
  values(v_empno, '홍길동');
  commit;
END;

제어문

IF 문

DECLARE
  vempno emp.empno%TYPE;
  vename emp.ename%TYPE;
  vdeptno emp.deptno%TYPE;
  vname varchar2(20) := null;
BEGIN
    select empno, ename, deptno
        into vempno, vename, vdeptno
    from emp
    where empno=7788;
    
    IF(vdeptno = 10) THEN 
        vname := 'ACC';
    ELSIF(vdeptno=20) THEN 
        vname := 'IT';
    ELSIF(vdeptno=30) THEN 
        vname := 'SALES';
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('당신의 직종은 : ' || vname);
END;

IF 문 구조

IF() THEN 실행문
ELSIF() THEN 실행문
ELSIF() THEN 실행문
ELSE 실행문 (선택)
END IF;

IF-ELSE 예제

DECLARE
  vempno emp.empno%TYPE;
  vename emp.ename%TYPE;
  vsal   emp.sal%TYPE;
BEGIN
  select empno, ename, sal
      into vempno, vename, vsal
  from emp
  where empno=7788;
  
  IF(vsal >= 2000) THEN 
       DBMS_OUTPUT.PUT_LINE('당신의 급여는 BIG ' || vsal);
  ELSE
       DBMS_OUTPUT.PUT_LINE('당신의 급여는 SMALL ' || vsal);
  END IF;
END;

CASE 문

CASE 문은 일반 SQL과 동일하게 사용한다.

DECLARE
  vempno emp.empno%TYPE;
  vename emp.ename%TYPE;
  vdeptno emp.deptno%TYPE;
  v_name varchar2(20);
BEGIN
     select empno, ename, deptno
        into vempno, vename, vdeptno
     from emp
     where empno=7788;
     
     v_name := CASE 
                   WHEN vdeptno=10  THEN 'AA'
                   WHEN vdeptno in(20,30)  THEN 'BB'
                   WHEN vdeptno=40  THEN 'CC'
                   ELSE 'NOT'
               END;
               
    DBMS_OUTPUT.PUT_LINE('당신의 부서명:' || v_name);            
END;

반복문

Basic LOOP

DECLARE
  n number := 0;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE('n value : ' || n);
    n := n + 1;
    EXIT WHEN n > 5;
  END LOOP;
END;

구조

LOOP
  문장;
  EXIT WHEN (조건식)
END LOOP

WHILE LOOP

DECLARE
  num number := 0;
BEGIN
  WHILE(num < 6)
    LOOP
      DBMS_OUTPUT.PUT_LINE('num 값 : ' || num);
      num := num + 1;
    END LOOP;
END;

구조

WHILE(조건)
LOOP
   실행문;
END LOOP

FOR LOOP

BEGIN
  FOR i IN 0..10 LOOP
    DBMS_OUTPUT.PUT_LINE(i);
  END LOOP;
END;

FOR LOOP 활용 (1~100 합계)

DECLARE
  total number := 0;
BEGIN
  FOR i IN 1..100 LOOP
    total := total + i;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('1~100 총합 : ' || total);
END;

CONTINUE 문 (11g 이상)

DECLARE
  total number := 0;
BEGIN
  FOR i IN 1..100 LOOP
    DBMS_OUTPUT.PUT_LINE('변수 : ' || i);
    CONTINUE WHEN i > 5;  -- skip
    total := total + i;  -- 1, 2, 3, 4, 5만 합산
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('합계 : ' || total);
END;

예외 처리

기본 예외 처리

DECLARE
    v_empno emp.empno%TYPE;
    v_name  emp.ename%TYPE := UPPER('&name');
    v_sal   emp.sal%TYPE;
    v_job   emp.job%TYPE;
    v_deptno emp.deptno%TYPE;
BEGIN
  select empno, job, sal, deptno
    into v_empno, v_job, v_sal, v_deptno
  from emp
  where ename = v_name;
  
  IF v_job IN('MANAGER','ANALYST') THEN
     v_sal := v_sal * 1.5;
  ELSE
     v_sal := v_sal * 1.2;
  END IF;
  
  update emp
  set sal = v_sal
  where deptno=v_deptno;
  
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || '개의 행이 갱신 되었습니다');
  
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE(v_name || '는 자료가 없습니다');
    WHEN TOO_MANY_ROWS THEN
       DBMS_OUTPUT.PUT_LINE(v_name || '는 동명 이인입니다');
    WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE('기타 에러가 발생했습니다');
END;

예외 종류

  • NO_DATA_FOUND: 데이터가 없을 때
  • TOO_MANY_ROWS: 여러 행이 반환될 때
  • OTHERS: 기타 모든 예외

CURSOR (커서)

커서는 SELECT 문장이 반환한 결과 집합(Result Set)을 메모리에 할당하여 탐색할 수 있게 만드는 객체이다.
일반 SQL은 결과를 “집합” 단위로만 다루지만, 커서는 이 결과집합을 내부적으로 저장한 후 포인터(cursor position) 를 이동시키며 한 행씩 순서대로 접근할 수 있게 해준다.
커서는 행 단위로 데이터를 처리하는 방법을 제공한다. 여러 건의 데이터를 처리할 수 있으며, 각 행마다 다른 처리를 할 수 있다는 장점이 있다.

커서가 필요한 이유

커서는 단순히 많은 행을 반복해서 출력하려는 용도가 아니라, 각 행마다 적용해야 하는 계산 방식이나 처리 규칙이 다를 때 사용된다. 예를 들어 급여 시스템에서는 정규직·시간직·일용직 모두 같은 테이블에서 조회할 수 있지만 급여를 계산하는 방식은 서로 다르다. SQL은 한 번의 SELECT로 여러 행을 쉽게 가져오지만, 이렇게 “한 행마다 다른 계산식”을 적용하는 데에는 적합하지 않다. 그래서 커서는 행을 하나씩 읽어오면서 조건을 확인하고, 해당 행에 맞는 로직을 실행할 수 있게 해준다. 즉, 커서는 조회는 한 번 하고, 처리는 각 행 기준으로 따로 진행해야 할 때 필요하다.

현실 예시: 급여 계산 시스템

사번이름직종명월급시간시간급식대
10홍길동정규직120nullnullnull
11김유신시간직null10100null
12이순신일용직nullnull12010

이렇게 말하면 훨씬 직관적으로 이해된다.

이 테이블을 보면 한 번에 SELECT * FROM salary_info 로 세 사람을 모두 조회할 수는 있다.
하지만 각 사람의 급여를 계산하는 방식은 전부 다르다.

  • 홍길동(정규직) → 월급 그대로 사용
  • 김유신(시간직) → 시간 × 시간급 계산
  • 이순신(일용직) → 시간급만 사용

즉, “같은 테이블에서 나온 같은 조회 결과”임에도 불구하고
행마다 적용해야 하는 계산 공식이 다르다.

SQL은 전체 집합에 같은 규칙을 적용하는 데에는 강하지만,
이처럼 “행마다 다른 공식”을 적용하는 상황에는 적합하지 않다.

이렇게 조회는 집합으로 가져오고, 계산은 행별 규칙대로 처리해야 하는 상황에서 커서가 필요하다.

한번 조회해 놓은 결과집합을 커서에 올려놓고
행을 하나씩 가져오면서, 그 행에 맞는 계산공식을 적용한다.

SQL CURSOR 속성

SQL 문장의 결과를 테스트할 수 있는 커서 속성이다.

속성설명
SQL%ROWCOUNT가장 최근 SQL 문장에 영향받은 행의 수
SQL%FOUND하나 이상의 행에 영향을 미쳤다면 TRUE
SQL%NOTFOUND어떤 행에도 영향을 미치지 않았다면 TRUE
SQL%ISOPEN항상 FALSE (실행 후 즉시 암시적 커서를 닫기 때문)

커서 기본 구조

DECLARE
    CURSOR 커서이름 IS 쿼리문
BEGIN
    OPEN 커서이름        -- 커서 실행
    FETCH 커서이름 INTO 변수명들  -- 데이터 읽기
    CLOSE 커서이름       -- 커서 닫기
END

기본 커서 예제

DECLARE
  vempno emp.empno%TYPE;
  vename emp.ename%TYPE;
  vsal   emp.sal%TYPE;
  CURSOR c1 IS select empno, ename, sal from emp where deptno=30;
BEGIN
    OPEN c1;
    LOOP
      FETCH c1 INTO vempno, vename, vsal;
      EXIT WHEN c1%NOTFOUND;  -- 더 이상 row가 없으면 탈출
      DBMS_OUTPUT.PUT_LINE(vempno || '-' || vename || '-'|| vsal);
    END LOOP;
    CLOSE c1;
END;

FOR LOOP를 사용한 커서 (간단한 방법)

DECLARE
  CURSOR emp_curr IS select empno, ename from emp;
BEGIN
    FOR emp_record IN emp_curr
    LOOP
      EXIT WHEN emp_curr%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(emp_record.empno || '-' || emp_record.ename);
    END LOOP;
    CLOSE emp_curr;
END;

%ROWTYPE을 사용한 커서

DECLARE
  vemp emp%ROWTYPE;
  CURSOR emp_curr IS select empno, ename from emp;
BEGIN
  FOR vemp IN emp_curr
    LOOP
      EXIT WHEN emp_curr%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(vemp.empno || '-' || vemp.ename);
    END LOOP;
    CLOSE emp_curr;
END;

복잡한 커서 예제 (부서별 급여 합계)

DECLARE
  v_sal_total NUMBER(10,2) := 0;
  CURSOR emp_cursor
  IS SELECT empno, ename, sal FROM emp
     WHERE deptno = 20 AND job = 'CLERK'
     ORDER BY empno;
BEGIN
  DBMS_OUTPUT.PUT_LINE('사번 이 름        급 여');
  DBMS_OUTPUT.PUT_LINE('---- ---------- ----------------');
  
  FOR emp_record IN emp_cursor 
  LOOP
      v_sal_total := v_sal_total + emp_record.sal;
      DBMS_OUTPUT.PUT_LINE(
          RPAD(emp_record.empno,6) || 
          RPAD(emp_record.ename,12) ||
          LPAD(TO_CHAR(emp_record.sal,'$99,999,990.00'),16)
      );
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE('----------------------------------');
  DBMS_OUTPUT.PUT_LINE(
      RPAD(TO_CHAR(20),2) || '번 부서의 합 ' ||
      LPAD(TO_CHAR(v_sal_total,'$99,999,990.00'),16)
  );
END;

커서 실습 문제

테이블 생성

create table cursor_table
as
  select * from emp where 1=2;  -- 스키마만 복사

alter table cursor_table
add totalsum number;

문제

emp 테이블에서 사원들의 사번, 이름, 급여를 가져와서 cursor_table에 insert하는데, totalsum은 (급여 + comm)을 통해 계산한다. 단, 부서번호가 10인 사원은 totalsum에 급여만 넣는다.

해결

DECLARE
    result number := 0;
    CURSOR emp_curr IS select empno, ename, sal, deptno, comm from emp;
BEGIN
    FOR vemp IN emp_curr
    LOOP
        EXIT WHEN emp_curr%NOTFOUND;
        
        IF(vemp.deptno = 20) THEN 
            result := vemp.sal + nvl(vemp.comm,0);
            insert into cursor_table(empno, ename, sal, deptno, comm, totalsum) 
            values (vemp.empno, vemp.ename, vemp.sal, vemp.deptno, vemp.comm, result);
            
        ELSIF(vemp.deptno = 10) THEN 
            result := vemp.sal;
            insert into cursor_table(empno, ename, sal, deptno, comm, totalsum) 
            values (vemp.empno, vemp.ename, vemp.sal, vemp.deptno, vemp.comm, result);
            
        ELSE
            DBMS_OUTPUT.PUT_LINE('ETC');
        END IF;
    END LOOP;
    
    COMMIT;
END;

커서를 활용한 급여 계산 시스템

급여 정보 테이블 생성

create table salary_info
(
    empno number,
    ename varchar2(20),
    job_type varchar2(20),  -- 정규직, 시간직, 일용직
    monthly number,         -- 월급
    hours number,           -- 근무시간
    hourly number,          -- 시간당 급여
    meal number             -- 식대
);

insert into salary_info values(10,'홍길동','정규직',120,null,null,null);
insert into salary_info values(11,'김유신','시간직',null,10,100,null);
insert into salary_info values(12,'이순신','일용직',null,null,120,10);
commit;

급여 계산 로직

declare
   cursor sal_cursor is  
   select empno, ename, job_type, monthly, hours, hourly, meal 
   from salary_info;
   
   v_empno salary_info.empno%TYPE;
   v_ename salary_info.ename%TYPE;
   v_job_type salary_info.job_type%TYPE;
   v_monthly salary_info.monthly%TYPE;
   v_hours salary_info.hours%TYPE;
   v_hourly salary_info.hourly%TYPE;
   v_meal salary_info.meal%TYPE;
   v_pay  number;
begin
    open sal_cursor;
    
    loop
        fetch sal_cursor into v_empno, v_ename, v_job_type, v_monthly, v_hours, v_hourly, v_meal;
        exit when sal_cursor%NOTFOUND;
        
        -- 직종별 급여 계산
        if v_job_type = '정규직' then
             v_pay := nvl(v_monthly,0);
        elsif v_job_type = '시간직' then
             v_pay := nvl(v_hours,0) * nvl(v_hourly,0);
        elsif v_job_type = '일용직' then
             v_pay := nvl(v_hourly,0) + nvl(v_meal,0);
        else 
             v_pay := 0;
        end if;
        
        DBMS_OUTPUT.PUT_LINE('사번 :' || v_empno || ' 직종 : ' || v_job_type ||  '급여 : ' || v_pay);
        
    end loop;
    close sal_cursor;
end;

사용자 정의 예외 처리

DECLARE
    v_ename emp.ename%TYPE := '&p_ename';
    v_err_code NUMBER;
    v_err_msg VARCHAR2(255);
BEGIN
    DELETE emp WHERE ename = v_ename;
    
    IF SQL%NOTFOUND THEN
        RAISE_APPLICATION_ERROR(-20002,'my no data found');  -- 사용자 정의 예외
    END IF;
    
EXCEPTION 
    WHEN OTHERS THEN
        ROLLBACK;
        v_err_code := SQLCODE;
        v_err_msg := SQLERRM;
        DBMS_OUTPUT.PUT_LINE('에러 번호 : ' || TO_CHAR(v_err_code));
        DBMS_OUTPUT.PUT_LINE('에러 내용 : ' || v_err_msg);
END;

사용자 정의 예외는 -20000번대를 사용한다.

profile
김소희
개발자 소희의 노트
이전 포스트

Zustand

다음 포스트

Oracle PL/SQL - 프로시저

0개의 댓글