[PLSQL 반복문]
1. basic loop문
– 반복할 조건이나 대상이 없음
– 횟수가 정해져 있지 않음
– 무한 loop문이라 종료 조건이 필수임.
2. for문
– 반복할 대상이나 횟수가 정해져 있음
3. while문
– 반복할 조건이 정해져 있음.
[basic loop문]
** 문법
begin
loop
반복문1;
반복문2;
exit when 조건;
end loop;
end;
/** 예제1) 1~10 출력
declare
vno number := 1;
begin
loop
dbms_output.put_line(vno);
vno := vno+1;
exit when vno = 11;
end loop;
end;
/** 예제2) 구구단 2단 출력
declare
vno number := 1;
begin
loop
dbms_output.put_line('2 X '||vno||' = '||to_char(2*vno, '99'));
vno := vno+1;
exit when vno = 10;
end loop;
end;
/[for문]
반복할 대상, 횟수가 정해져 있는 경우 사용
** 문법
begin
for 반복변수 in 대상 loop
반복문1;
반복문2;
end loop;
end;
/** 예제1) 구구단 2단 출력
begin
for vno in 1..9
loop
dbms_output.put_line('2 X '||vno||' = '||to_char(2*vno, '99'));
end loop;
end;
/[while문]
** 문법
begin
while 조건 loop
반복문1;
반복문2;
end loop;
end;
/** 예제1) 구구단 2단 출력
declare
vno number := 1;
begin
while vno < 10
loop
dbms_output.put_line('2 X '||vno||' = '||to_char(2*vno, '99'));
vno := vno+1;
end loop;
end;
/[별 출력]
** 출력값
*
***
*****
********* 코드
declare
vspace varchar2(2) := ' ';
vstar varchar2(2) := '*';
begin
dbms_output.put_line('');
for i in 1..4
loop
dbms_output.put_line(lpad(vspace,4-i,vspace)||lpad(vstar,(2*i-1),vstar));
end loop;
end;
/[cursor]
데이터 조회 시 disk -> db buffer cache에 올려두고 사용자가 원하는 데이터만 fetch하여 출력하기 위해 PGA에 임시로 보관 후 출력. 이때 PGA의 저장 단위를 cursor라고 함. 메모리의 공간할당의 최소 단위.
** cursor가 중요한 이유
기본적으로는 하나의 변수에는 하나의 값만 전달 가능.
cursor에 여러 값을 저장해두고 fetch하면 하나의 변수에 여러 값을 전달 가능.
** cursor 종류
1. 묵시적 커서
– ORACLE이 자동으로 선언한 커서
** 속성
– SQL%ROWCOUNT : cursor에 들어있는 행의 개수
– SQL%FOUND : cursor에 아직 수행할 데이터가 있는지
– SQL%NOTFOUND : cursor에 수행할 데이터가 없는지
– SQL%ISOPEN : cursor가 open인지
2. 명시적 커서
– 사용자가 직접 정의한 커서
– 반복문을 사용하여 여러 값을 동시 출력, 연산하기 위한 목적으로 사용
** 속성
– 커서명%ROWCOUNT
– 커서명%FOUND
– 커서명%NOTFOUND
– 커서명%ISOPEN
[cursor 사용법]
1. cursor 정의
– 반복문을 수행할 데이터를 정의
– 커서 이름 명시
– declare 절에서 정의 가능
– for문을 사용하여 정의하는 것도 가능.
2. open
3. fetch
4. close
** 예제)
emp의 모든 직원 정보를 아래처럼 출력.
SMITH의 10% 인상된 급여는 880입니다.
declare
vename varchar2(10);
vsal number;
begin
select ename, round(sal*1.1) into vename, vsal
from scott.emp;
dbms_output.put_line(vename||'의 10% 인상된 급여는 '||vsal||'입니다,');
end;
/
-- error
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 5
-- 한 번에 여러 값을 전달할 수 없다. 따라서 cursor가 필요하다.** 방법1) 커서 정의, open, fetch, close -> basic loop
declare
vename varchar2(10);
vsal number;
cursor c_emp is
select ename, round(sal*1.1) as newsal
from scott.emp;
begin
open c_emp;
loop
fetch c_emp into vename, vsal;
exit when c_emp%notfound;
dbms_output.put_line(vename||'의 10% 인상된 급여는 '||vsal||'입니다,');
end loop;
close c_emp;
end;
/** 방법2) 커서 정의, for 사용 (open, fetch, close 생략)
declare
cursor c_emp is
select ename, round(sal*1.1) as newsal
from scott.emp;
begin
for result in c_emp
loop
dbms_output.put_line(result.ename||'의 10% 인상된 급여는 '||result.newsal||'입니다,');
end loop;
end;
/** 방법3) for만 사용 (정의, open, fetch, close 생략)
begin
for result in (select ename, round(sal*1.1) as newsal
from scott.emp)
loop
dbms_output.put_line(result.ename||'의 10% 인상된 급여는 '||result.newsal||'입니다,');
end loop;
end;
/예제)
student 테이블의 모든 학생의 정보를 아래와 같이 출력.
이름 : 서재수, 키 : 172, 몸무게 : 64
** 1) 정석
declare
vname varchar2(10);
vhei number;
vwei number;
cursor c1 is
select name, height, weight
from scott.student;
begin
dbms_output.put_line('');
open c1;
loop
fetch c1 into vname, vhei, vwei;
exit when c1%notfound;
dbms_output.put_line('이름 : '||vname||', 키 : '||vhei||', 몸무게 : '||vwei);
end loop;
close c1;
end;
/** 2) 정의 & for
declare
cursor c1 is
select name, height, weight
from scott.student;
begin
dbms_output.put_line('');
for result in c1
loop
dbms_output.put_line('이름 : '||result.name||', 키 : '||result.height||', 몸무게 : '||result.weight);
end loop;
end;
/** 3) for만
begin
dbms_output.put_line('');
for result in (select name, height, weight
from scott.student)
loop
dbms_output.put_line('이름 : '||result.name||', 키 : '||result.height||', 몸무게 : '||result.weight);
end loop;
end;
/[예외처리부 (exception)]
프로그램 실행에 방해되는 조건을 정의.
사용자가 정의한 에러코드, 에러내용을 정의
** 방법
1. system error
– oracle이 내부적으로 정의한 예외명을 사용
– 정해져 있는 상황을 정해진 예외명을 사용하여 예외처리
ex) 조회할 데이터가 없는 경우 -> NO_DATA_FOUND
2. 사용자 정의 error
– 정해져 있는 상황이 아닌 특별한 상황에서의 예외 발생시킬 경우 사용.
ex) 상위 관리자가 없는 경우 -> 사용자가 직접 예외명을 정의하고 해당 예외를 발생할 경우 에러처리 필요.
시스템 에러명
예외명: ACCESS_INTO_NULL
예외번호: ORA-06530
설명: 정의되지 않은 오브젝트 속성에 값을 할당하고자 했을 때 발생되는 예외.
예외명: CASE_NOT_FOUND
예외번호: ORA-06592
설명: CASE 문의 WHEN 절에 해당되는 조건이 없고 ELSE 절도 없을 경우에 발생되는 예외.
예외명: COLLECTION_IS_NULL
예외번호: ORA-06531
설명: 선언되지 않은 컬렉션 (nested table, varray)에 EXISTS 이외의 메소드를 사용했을 때 발생되는 예외.
예외명: CURSOR_ALREADY_OPEN
예외번호: ORA-06511
설명: 이미 열려진 커서를 열려고 시도 했을 때 발생되는 예외.
예외명: DUP_VAL_ON_INDEX
예외번호: ORA-00001
설명: 유일인덱스에 중복값을 입력했을 경우 발생되는 예외.
예외명: INVALID_CURSOR
예외번호: ORA-01001
설명: 잘못된 커서 조작이 실행될 때 발생되는 예외.
예외명: INVALID_NUMBER
예외번호: ORA-01722
설명: 문자를 숫자로의 변환 시 실패가 될 때 발생되는 예외.
예외명: LOGIN_DENIED
예외번호: ORA-01017
설명: 잘못된 사용자명 이나 암호로 로그인을 시도했을 때 발생되는 예외.
예외명: NO_DATA_FOUND
예외번호: ORA-01403
설명: PL/SQL SELECT 문이 한 건도 리턴 하지 못했을 경우 발생하는 예외
예외명: NOT_LOGGED_ON
예외번호: ORA-01012
설명: 접속되지 않은 상태에서 데이터베이스에 대한 요청이 PL/SQL 프로그램으로 실행된 경우 발생되는 예외.
예외명: PROGRAM_ERROR
예외번호: ORA-06501
설명: PL/SQL 이 내부적인 문제를 가지고 있는 경우 발생되는 예외
예외명: ROWTYPE_MISMATCH
예외번호: ORA-06504
설명: 할당문에서 호스트 커서 변수와 PL/SQL 커서 변수의 데이터 형이 불일치 할 때 발생되는 예외
예외명: STORAGE_ERROR
예외번호: ORA-06500
설명: PL/SQL 이 실행될 때 메모리가 부족하거나 메모리상에 문제가 일어났을 때 발생하는 예외
예외명: SUBSCRIPT_BEYOND_COUNT
예외번호: ORA-06533
설명: 컬렉션의 요소 개수보다 더 큰 첨자 값으로 참조한 경우 발생되는 예외.
예외명: SUBSCRIPT_OUTSIDE_LIMIT
예외번호: ORA-06532
설명: 컬렉션의 첨자의 한계를 벗어난 참조가 일어났을 때 발생되는 예외
예외명: SYS_INVALID_ROWID
예외번호: ORA-01410
설명: 문자열을 ROWID 로 변환할 때 무효한 문자열의 표현일 경우 발생되는 예외
예외명: TIMEOUT_ON_RESOURCE
예외번호: ORA-00051
설명: 자원에 대한 대기시간이 초과했을 때 발생하는 예외
예외명: TOO_MANY_ROWS
예외번호: ORA-01422
설명: PL/SQL SELECT 문이 두 건 이상의 행을 리턴 했을 때 발생되는 예외
예외명: VALUE_ERROR
예외번호: ORA-06502
설명: 산술, 변환, 절삭 또는 크기 제약에 에러가 생겼을 때 발생되는 예외
예외명: ZERO_DIVIDE
예외번호: ORA-01476
설명: 0으로 나누려 했을 때 발생하는 예외.
[기본 예외 처리]
사용자가 에러 상황을 직접 정의하는 것이 아닌, 이미 만들어진 상황에 대한 에러를 처리하는 방법.
exception부에서만 에러 처리.
** 예제) emp 테이블을 사용하여 사번을 입력하면 사원이름과 현재 급여 출력.
> !vi emp1.sql
accept vempno prompt '사번을 입력하세요 : '
declare
vename scott.emp.ename%type;
vsal scott.emp.sal%type;
begin
select ename, sal into vename, vsal
from scott.EMP
where empno = &vempno;
dbms_output.put_line('사원명 : '||vename||', 급여 : '||vsal);
exception
when no_data_found then
dbms_output.put_line('there is no such empno');
end;
/