220914 - PL/SQL - 제어문, 커서

DB 프로그래밍 => 복잡한 비즈니스 로직 만드는 PL/SQL (절차형)

 

 

 

문법

•  

DECLARE => 선언부(변수, 상수 선언)
BEGIN => 실행부(제어문, 반복문, 함수 정의)
EXCEPTION => 예외처리부(로직 처리 오류 발생)

 

 

 

•  

1. 한 문장이 끝날 때마다 세미콜론(;) 작성
2. 한줄 주석 : --A, 여러줄 주석: /*A*/
3. 쿼리문 수행 위해서는 / 입력 필수며, PL/SQL 블록은 / 있으면 종결된 것으로 간주

 

 

 

 

•  

변수명 타입 := 표현식;
상수명 CONSTANT 타입 := 상수값;

 

 

 

 

• 연산자

** : 제곱
|| : 연결연산자 
= : 같다
<> : 같지 않다

 

 

 

 

• 데이터 타입

스칼라 : 숫자, 문자, 날짜, BOOLEAN
레퍼런스 : 데이터베이스 기존의 칼럼에 맞추어 %TYPE Attribute를 이용

 

 

• 스칼라

DECLARE
    v_num NUMBER; --변수명 타입
BEGIN
    v_num:=50; --값을 변수에 대입
    dbms_output.put_line(v_num);
END;
/

 

 

•  

DECLARE
    v_age NUMBER; 
BEGIN
    v_age:=27;
    dbms_output.put_line('당신의 나이는 '||v_age||'입니다'); 
END;
/

 

 

• 레퍼런스

DECLARE
    v_eno employee.eno%type; --employee 테이블의 eno열과 같은 타입
BEGIN
    v_eno:=2**3; 
    dbms_output.put_line(v_eno);
END;
/

 

 

 

 

• %ROWTYPE : 행 전체 타입 이용 가능

변수명 테이블%ROWTYPE;

 

 

•  

DECLARE
    v_emp employee%rowtype;
BEGIN
    v_emp.salary:=3000;
    dbms_output.put_line(v_emp.salary); --3000
END;
/

 

 

 

※ SQL PLUS 사용 시 : SET SERVER OUTPUT ON 입력 후 시작

※ 보기 - DBMS 출력 

 

 

 

 

SELECT문

SELECT 컬럼1,컬럼2,.. INTO 변수1, 변수2,.. FROM 테이블명 WHERE 조건;

 

 

•  

DECLARE
    v_eno employee.eno%type;  --
    v_ename employee.ename%type;
BEGIN
    select eno, ename into v_eno, v_ename from employee where ename='SCOTT';
    dbms_output.put_line('사원번호           사원이름');
    dbms_output.put_line('-------------------------');
    dbms_output.put_line(v_eno||'               '||v_ename);
END;
/

=>

사원번호           사원이름
-------------------------
7788               SCOTT

 

 

 

 

제어문

• IF 조건문

1.
IF 조건 THEN 처리명령..;
END IF;

2.
IF 조건 THEN 처리명령..;
ELSE 처리명령..;
END IF;

3. 
IF 조건 THEN 처리명령;
ELSIF 조건 THEN 처리명령;
ELSIF 조건 THEN 처리명령;
ELSE 처리명령;
END IF;

 

 

•  

DECLARE
    v_num1 number:=15;
    v_num2 number:=32;
BEGIN
    if v_num1 > v_num2 then
        dbms_output.put_line(v_num1||'이 큰 수입니다');
    else
        dbms_output.put_line(v_num2||'이 큰 수입니다');
    end if;
END;
/

=>

32이 큰 수입니다

 

 

 

•  

DECLARE
    v_emp employee%rowtype;
    v_sal number(7,2);
BEGIN
    select * into v_emp from employee where ename='SCOTT';
    if (v_emp.commission is null) then
        v_emp.commission:=0; --0 대입
    end if;
    v_sal := v_emp.salary*12+v_emp.commission;
    dbms_output.put_line('사원번호   사원이름    연봉');
    dbms_output.put_line('-------------------------');
    dbms_output.put_line(v_emp.eno||'    '||v_emp.ename||'    '||v_sal);
END;
/

=>

사원번호   사원이름    연봉
-------------------------
7788    SCOTT    36000

 

 

 

 

• CASE문

•  

CASE WHEN v_salary between 1 and 1000 THEN
dbms_output.put_line('낮음');
WHEN v_salary between 1001 and 2000 THEN
dbms_output.put_line('중간');
WHEN v_salary between 2001 and 3000 THEN
dbms_output.put_line('높음');
ELSE
dbms_output.put_line('최상위');
END CASE;
END;
/

 

 

 

 

• LOOP 반복문

LOOP 처리문;
EXIT WHEN 조건;
END LOOP;

 

 

•  

DECLARE
    v_dan number:=3;
    v_count number:=1;
BEGIN
    dbms_output.put_line('** '||v_dan||'단 **');
    LOOP
        dbms_output.put_line(v_dan||' X '||v_count||' = '||v_dan*v_count);
        v_count:=v_count+1;
    EXIT WHEN v_count>9;
    END LOOP;
END;
/

=>

** 3단 **
3 X 1 = 3
3 X 2 = 6
3 X 3 = 9
3 X 4 = 12
3 X 5 = 15
3 X 6 = 18
3 X 7 = 21
3 X 8 = 24
3 X 9 = 27

 

 

 

 

• FOR 반복문

FOR 변수 IN (REVERSE) 초기값..최종값 //REVERSE: 역순 출력
LOOP 
처리문;
END LOOP;

 

 

•  

DECLARE
    v_dan number:=3;
BEGIN
    dbms_output.put_line('** '||v_dan||'단 **');
    for i in 1..9
    loop
        dbms_output.put_line(v_dan||' X '||i||' = '||v_dan*i);
    end loop;
END;
/

 

 

 

 

• WHILE 반복문

WHILE 조건
LOOP 처리문;
END LOOP;

 

 

•  

DECLARE
    v_dan NUMBER:=3;
    v_cnt NUMBER:=1;
BEGIN
    dbms_output.put_line('** '||v_dan||'단 **');
    WHILE v_cnt<=9
    LOOP
        dbms_output.put_line(v_dan||' X '||v_cnt||' = '||v_dan*v_cnt);
        v_cnt:=v_cnt+1;
    END LOOP;
END;
/

 

 

 

 

• CONTINUE문

: 반복문 LOOP 내에서 특정 조건에 부합할 때 해당 로직 pass, 다음 LOOP 조건으로 넘어가 처리문 실행

 

 

•  

DECLARE
    v_dan NUMBER:=3;
    --v_cnt NUMBER:=1;
BEGIN
    dbms_output.put_line('** '||v_dan||'단 **');
    FOR v_cnt in 1..9
    loop
        continue when v_cnt=5;
        dbms_output.put_line(v_dan||' X '||v_cnt||' = '||v_dan*v_cnt);
    end loop;
END;
/

=>

** 3단 **
3 X 1 = 3
3 X 2 = 6
3 X 3 = 9
3 X 4 = 12
3 X 6 = 18
3 X 7 = 21
3 X 8 = 24
3 X 9 = 27

 

 

※ 나머지 : mod(숫자,나눌숫자);

 

 

 

 

• GOTO문

: 지정한 레이블로 이동

 

 

•  

DECLARE
    v_dan NUMBER:=3;
    --v_cnt NUMBER:=1;
BEGIN
    dbms_output.put_line('** '||v_dan||'단 **');
    FOR v_cnt in 1..9
    loop
        --continue when mod(v_cnt,2)=0;
        if (v_cnt = 5) then
            goto third; 
        end if;
        dbms_output.put_line(v_dan||' X '||v_cnt||' = '||v_dan*v_cnt);
    end loop;
    <<third>> 
    dbms_output.put_line('구구단 출력 완료');
END;
/

=>

** 3단 **
3 X 1 = 3
3 X 2 = 6
3 X 3 = 9
3 X 4 = 12
구구단 출력 완료

 

 

 

 

제어문 관련 문제

○ 사원번호가 7499인 사원 => 사원이름, 부서명, 급여 출력

DECLARE
    v_ename employee.ename%type;
    v_salary employee.salary%type;
    v_dname department.dname%type;
BEGIN
    select e.ename, e.salary, d.dname into v_ename, v_salary, v_dname from employee e, department d where eno=7499 and e.dno=d.dno;
    dbms_output.put_line('사원이름     부서명    급여');
    dbms_output.put_line('-------------------------');
    dbms_output.put_line(v_ename||'      '|| v_dname   || '    '||v_salary);
END;
/

=>

사원이름     부서명    급여
-------------------------
ALLEN      SALES    1600



○SCOTT의 사원번호 사원이름 부서명 출력 => 부서코드 기반 부서명 출력 (테이블 없다고 가정하고 if문으로)

DECLARE
    v_emp employee%rowtype;
    v_dname VARCHAR2(15);
BEGIN
    select * into v_emp from employee where ename='SCOTT';
    if (v_emp.dno=10) then
        v_dname:='ACCOUNTING'; 
    elsif (v_emp.dno=20) then
        v_dname:='RESEARCH';
    elsif (v_emp.dno=30) then
        v_dname:='SALES';
    else
        v_dname:='OPERATIONS';
    end if;
    dbms_output.put_line('사원번호   사원이름    부서명');
    dbms_output.put_line('-------------------------');
    dbms_output.put_line(v_emp.eno||'    '||v_emp.ename||'    '||v_dname);
END;
/

=>

사원번호   사원이름    부서명
-------------------------
7788    SCOTT    RESEARCH



○급여 수준 파악 프로그램 완성
1~1000 낮음 / 1001~2000 중간 / 2001~3000 높음 / 3001~ 최상위
ex. SCOTT : 높음

내답변:

DECLARE
    v_emp employee%rowtype;
    v_level VARCHAR2(10);
BEGIN
    select * into v_emp from employee where ename='SCOTT';
    if (v_emp.salary<=1000) then
        v_level:='낮음'; 
    elsif (v_emp.salary<=2000) then
        v_level:='중간'; 
    elsif (v_emp.salary<=3000) then
        v_level:='높음'; 
    else
        v_level:='최상위'; 
    end if;
    --dbms_output.put_line('사원번호   사원이름    부서명');
    --dbms_output.put_line('-------------------------');
    dbms_output.put_line(v_emp.ename||' : '||v_level);
END;
/

=>

SCOTT : 높음



예시답변:

DECLARE
    v_salary employee.salary%type;
    v_ename employee.ename%type;
    v_level VARCHAR2(10);
BEGIN
    select salary, ename into v_salary,v_ename from employee where ename='SCOTT';
    if (v_salary between 1 and 1000) then
        v_level:='낮음'; 
    elsif (v_salary<=2000) then
        v_level:='중간'; 
    elsif (v_salary<=3000) then
        v_level:='높음'; 
    else
        v_level:='최상위'; 
    end if;
    --dbms_output.put_line('사원번호   사원이름    부서명');
    --dbms_output.put_line('-------------------------');
    dbms_output.put_line(v_ename||' : '||v_level);
END;
/

 

 

 

 

커서 (CURSOR)

: SELECT문의 수행 결과가 여러 행인 경우를 처리해야 할 때 사용

=> Oracle 서버에서 할당한 전용 메모리 영역에 대한 포인터
=> SELECT 문의 결과 집합을 처리

반복문과 함께 사용됨

 

 

 

• 명시적 커서 : 사용자가 정의해 사용하는 커서

CURSOR 선언, OPEN, FETCH, CLOSE 4단계 명령

 

DECLARE

CURSOR 커서명 IS 명령문 (SELECT문 등);

BEGIN

OPEN 커서명;
FETCH 커서명 INTO 변수명;
CLOSE 커서명;

END;

 

 

• 커서 사용 전

DECLARE
    v_eno employee.eno%type;
    v_ename employee.ename%type;
    v_salary employee.salary%type;
BEGIN
    select eno, ename, salary into v_eno, v_ename, v_salary from employee where salary>=2000;
    dbms_output.put_line('사원번호    사원이름    급여');
    dbms_output.put_line('---------------------------');
    dbms_output.put_line(v_eno||'   '||v_ename||'    '||v_salary);
    
END;
/

=>오류

ORA-01422: 실제 인출은 요구된 것보다 많은 수의 행을 추출합니다
ORA-06512:  6행

 

 

 

• 커서 사용 후

DECLARE
    v_emp employee%rowtype;
    CURSOR cs_sal
    IS
    select eno, ename, salary from employee where salary>=2000;
BEGIN
    OPEN cs_sal();
    dbms_output.put_line('사원번호    사원이름    급여');
    dbms_output.put_line('---------------------------');
    LOOP
        FETCH cs_sal INTO v_emp.eno, v_emp.ename, v_emp.salary;
        EXIT WHEN cs_sal%NOTFOUND; --더이상 패치할 행 없을 때
        dbms_output.put_line(v_emp.eno||'   '||v_emp.ename||'    '||v_emp.salary);
    END LOOP;
    CLOSE cs_sal;
END;
/

=>

사원번호    사원이름    급여
---------------------------
7566   JONES    2975
7698   BLAKE    2850
7782   CLARK    2450
7788   SCOTT    3000
7839   KING    5000
7902   FORD    3000

 

 

 

 

• 묵시적 커서 : 오라클 내부에서 자동 생성, 사용

%FOUND : 결과 집합의 패치 로우 수가 1개 이상이면 TRUE, 아니면 FALSE를 반환
%NOTFOUND : 결과 집합의 패치 로우 수가 0이면 TRUE, 아니면 FALSE를 반환
%ROWCOUNT : 영향 받은 결과 집합의 로우 수 반환, 없으면 0을 반환
%ISOPEN : 묵시적 커서는 항상 FALSE를 반환(이 속성으로 참조할 때는 이미 해당 묵시적 커서는 닫힌 상태 이후이기 때문)