KOSTA 교육 (PL/SQL, 제어문, 반복문, EXCEPTION, CURSOR, PROCEDURE)

 배운 내용

1. PL/SQL
2. 제어문
3. 반복문
4. EXCEPTION(예외)
5. CURSOR(커서)
6. PROCEDURE(프로시저)

 

1. PL/SQL

이미 개념을 학습한 부분으로 처음부터 보고싶다면 24일차 부터 참고.
오늘은 이어서 학습하도록 하겠다.

• 데이터유형에는 기본데이터 타입, 레퍼런스 타입 둘로 나뉜다.
• 기본데이터는 평소에 사용하던 VARCHAR2(), NUMBER 등이 있겠고,
  레퍼런스형은 변수명 테이블명.칼럼명 % TYPE, ROWTYPE 등이 있다.
• TYPE은 테이블에 있는 하나의 컬럼명을 담고 있으며 ROWTYPE은 테이블을 담고 있는 데이터타입이다.
• 미션 1
  사원의 이름과, 이메일을 출력하려면 해당 출력대상의 테이블에 있는 칼럼을 가져와야한다, 때문에 TYPE으로 가져와 변수를 입력하고 조건은 201번사원으로 선언해주고 출력한다.
• 미션 2
  해당 사원을 추가하기 위해서 MAX(employee_id)를 통해 최대 번호를 출력하고 206이란 것을 알아내고 해당 번호에 INSERT를 통해 해당 사원을 추가하고 COMMIT을 통해 저장한다.
• ROWTYPE 예제
  변수명 테이블명%ROWTYPE으로 선언한다.
  선언한 변수명을 SELECT문에 INTO 해주고 WHERE절에 조건에 해당되는 값을 출력한다.

2. 제어문

제어문은?

IF, ELSE, ELSIF를 통해 조건식을 만들어준다.

대표 예제

제어문 예제 1

• IF 변수명 = 변수값 THEN으로 선언하고 END IF로 IF문을 끝내야한다.
• IF 변수명 = 변수값 THEN ELSE로 선언하고 END IF로 끝낸다.
• IF 변수명 = 변수값 THEN ELSIF ELSE로 선언하고 END IF로 끝낸다.
• 위의 변수명과 변수값은 DECLARE에 선언해주어야 한다.
• 퀴즈
  10~120중 임의의 부서번호에서 평균 급여에 따라 등급이 출력되로록 해보자.
  ROUND(DBMS_RANDOM.VALUE(10,120), -1);를 통해서 10~120 임의의 수를 난수로 출력한다.
  AVG()를 사용하여 평균급여를 구하고 INTO로 타입을 넣어준다.
  부서번호를 랜덤으로 했을 때 GROUP BY를 부서번호로 그룹해줘야 해당 부분만 출력이 될 것이다.
  그리고 IF ELSE문으로 출력한다.
  CASE WHEN문은 IF ELSE 대신 사용할 수 있다.

3. 반복문

반복문은?

LOOP, WHILE, FOR문을 사용하여 반복을 한다. 주로 FOR문을 사용한다.

대표 예제

반복문 예제 1

• LOOP문은 LOOP를 먼저 선언하고 i 반복값을 선언한 다음 EXIT WHEN을 선언하여 i가 만족하는 값 까지 나올 때 탈출한다.
• WHILE문은 i의 값을 먼저 나타내고 LOOP를 선언한 다음 i의 반복값을 선언한다.
• FOR문은 FOR i IN 반복시작..반복끝값 LOOP를 선언한다.
• 여기서 공통적인 사항은 END LOOP; 를 통해 반복문을 종료하여야 한다.
• 퀴즈로 구구단 3단을 만드는 예제를 만들어보았다. 위의 예제를 참고하여 풀면 쉽게 풀 수 있을 것이다.

4. 예외(EXCEPTION)

예외는?

PL/SQL의 오류를 예외라고 하며 컴파일시 문법, 실행시 발생하는 오류를 나타낸다.

• 사전 정의 오라클 서버 예외 : 선언필요 X, 예외 발생시 예외절로 자동 이동
• 사용자 예외 강제 발생 : 선언부에서 예외를 정의, 실행부에서 RAISE문 사용

대표 예제

예외 예제 1

• EXCEPTION에 자동으로 이동하기 때문에 BEGIN에 예외절을 따로 만들어준다.
• UNIQUE 제약조건 갖는 컬럼에 중복된 데이터를 입력
  WHEN DUP_VAL_ON_INDEX THEN
  dbms_output.put_line(‘이미 존재하는 사원입니다.’);
• — SELECT문 결과값이 2개 이상 ROW를 반환
  WHEN TOO_MANY_ROWS THEN
  dbms_output.put_line(‘검색된 행이 많습니다.’);
• — 데이터 값이 없을 때
  WHEN NO_DATA_FOUND THEN
  dbms_output.put_line(‘검색된 사원이 없습니다.’);
• — 모든 것에 해당이 되지 않는 예외일 때
  WHEN OTHERS THEN
  dbms_output.put_line(‘그 밖의 예외 사유.’);
• 예외를 강제로 발생시킬 경우 변수명 EXCEPTION을 선언하여 예외를 정의해주고 BEGIN에 조건이 불만족할 시 RAISE를 통해 오류를 발생시킨다.

5. 커서(CURSOR)

커서는?

SELECT문 결과 집합이 다중 로우일 경우 커서를 사용하여 처리할 수 있다.
테이블을 하나의 객체로 만들어 사용한다.

• 선언방식 : CURSOR 커서변수명 IS SELECT ~ FROM 테이블명;

대표 예제

카서 LOOP문 예제 1

• CURSOR 커서 변수명 IS SELECT ~ FROM으로 선언을 해준다.
• BEGIN에서 커서 변수를 OPEN해준다.
• LOOP문을 이용할 때 FETCH를 선언하여 커서를 선언하고 INTO를 통해 ROWTYPE을 선언한다. 여기서 LOOP문 주의할 점은 EXIT WHEN을 이용해 탈출을 선언하는데 NOTFOUND를 함께 선언하여 내용을 찾지 못했을 때 탈출한다.
• 급여누계는 0으로 선언한 변수에서 ROWTYPE의 급여를 순서대로 더해주면 누계가 된다.
• 마지막으로 END LOOP; 그리고 CLOSE 카서변수; 를 선언하여 반복과 카서변수를 닫는다.

카서 FOR문 예제 1

• FOR문의 장점은 따로 카서를 OPEN 하거나 CLOSE 하지 않아도 되며 EXIT선언을 하지 않아도 된다.
• LOOP문과 동일하게 DECLARE선언을 해주고 BEGIN에서 FOR문을 이용하여 반복한다. FOR ROWTYPE IN 커서 변수 LOOP를 통하여 ROWTYPE이 커서변수가 반복될 때 마다 하나씩 반복되어진다.
  여기서 급여누계도 동일하게 반복시킨다.
• 그리고 OPEN 하지 않았으니 END LOOP만 하여 끝낸다.

6. 프로시저(PROCEDURE)

프로시저는?

자주 사용되는 PL/SQL블록을 재사용하기 위해 모듈화 하는 것

• 선언방식 : CREATE OR REPLACE PROCEDURE 프로시저명(매개변수 IN 데이터타입)

대표 예제

프로시저 예제 1

• CREATE OR REPLACE PROCEDURE 프로시저명(매개변수 IN 데이터타입)으로 선언한 다음 IS 커서 선언 IS SELECT ~ FROM ~ WHERE로 사원번호와 employees의 사원번호 데이터타입을 같게 한 후 사원 목록을 출력할 준비를 한다.
• 여기서 목록의 다른 항목들도 출력하기 위해 ROWTYPE을 통하여 출력한다.