[SQL] PL/SQL

 

PL/SQL : 오라클 자체에서 내장되어 있는 절차적언어

- SQL 보완

- 변수 정의, 조건 처리(IF), 반복 처리(LOOP, WHILE, FOR)등 지원

- 블록 구조

 

 

기능

- 변수 선언

- 비교 처리

- 반복 처리

 

 

장점

- 블록 안의 여러 SQL 구문이 한번에 서버로 전송되므로 수행속도가 향상되고, 통신량도 줄일 수 있다

 

 

 

BLOCK 구조

1. 이름부 (IS/AS) : 블록의 이름 지정

- 아무것도 쓰지 않으면 익명 블록이 됨

2. 선언부 (DECLARE) : 변수나 상수 선언

3. 실행부 (BEGIN) : SQL문

- PL문 안에 SQL문을 쓰는것

- 제어문, 반복문

- 커서

4. 예외 처리부 (EXCEPTION)

5. END

- END문 뒤에 ;를 선언하면 하나의 블록이 끝남

 

 

#set serveroutput on : 출력을 위해 선언

 

 

변수

- 선언부에서만 사용 가능

- 기본 형태

변수이름 [constraint] 데이터타입 [not null] [:= | default expression];

- DECLARE 부분에서 선언한 변수들은 / 기호를 만나면 해당 PS/SQL문이 종료됨과 동시에 사라짐

 

# ||는 JAVA 출력문에서 + 와 같은 개념

 

 

 

 

 

변수

1. 단순 변수

- 스칼라 변수 : 단일값을 갖는 변수의 데이터형을 직접 지정해주는 변수(정수, 실수)형태를 직접 지정해주는 변수

- 참조 변수 : 데이터 형을 다른 컬럼에서 참조 후 지정하는 방식의 변수

  - %type : 열 단위로 참조

    기본 형태

변수명 참조할테이블명.테이블의칼럼명%TYPE;

 

#into : id를 ex_id에 넣겠다, name을 ex_name에 넣겠다

 

 

  - %rowtype : 로우(행) 단위로 참조

    기본 형태

변수명 참조할테이블명&ROWTYPE;

 

 

 

 

 

 

2. 복합 변수 :변수 하나안에 사용자가 원하는 여러가지 다른 유형의 데이터를 포함해서 새로운 형태로 만든 변수

- 레코드 타입 : 필드들의 집합을 하나의 논리적 단위로 처리할 수 있게 해줌

  - row를 읽어올 때 편리

 

    -> 조건 : 사원 번호가 7499인 사원의 사원 번호와 사원명과 업무를 출력

                record type의 type명은 emp_record_type으로 지정

 

#선언한 타입과 입력한 데이터값이 같아야함

 

 

 

- 테이블 타입

  - row에 대해 배열처럼 접근하기 위해 기본키 사용 

  - 배열과 유사

  - PL/SQL 테이블을 접근하기 위해 BINARY_INTEGER 데이터형의 기본키와 PL/SQL 테이블 요소를 저장하는 스칼라 또는 레코드 데이터형 컬럼 포함

  - 동적으로 자유롭게 증가 가능

 

 

    -> 조건 : Table Type 변수를 사용하여 사원 번호가 7499 인 사원의 이름을 조회해서 해당 변수에 저장 한 후 출력

 

 

 

---

 

 

조건문

 

IF

1. IF ~ END IF

- 기본 형태

IF 조건 THEN 실행문장;
ELSE 실행문장;
END IF;

#조건이 참이면 THEN뒤에 문장을 실행

 

employees 테이블

 

 

  -> 조건 : employees 테이블에서 employee_id가 10인 사원의 employee_id, first_name, vdname 출력

              first_name이 '추'이면 '마케팅부서' , first_name이 '김'이면 '기획부'

 

 

 

 

2. IF ~ THEN ~ ELSIF ~ END IF

- 기본 형태

IF 조건 THEN 실행문장;
ELSIF 조건 THEN 실행문장;
END IF;

  -> 조건 : employees 테이블에서 employee_id가 10인 사원의 employee_id, first_name, vdname 출력

              first_name이 '추'이면 '마케팅부서' , first_name이 '김'이면 '기획부'

 

 

 

 

3. IF ~ THEN ~ ELSE ~ END IF

- 기본 형태

IF 조건 THEN 실행문장;
ELSE 실행문장;
END IF;

  -> 조건 : employee_id가 10인 사원의 first_name이 '김'이면 first_name+'씨는 멸치', '추'이면 first_name+'씨는 돼지'

 

 

 

 

 

 

CASE

 

- 기본 형태

CASE 조건
WHEN 조건1 THEN 결과1
WHEN 조건2 THEN 결과2
…
WHEN 조건N THEN 결과N
ELSE 기타처리문; (없으면생략가능)
END CASE;

  -> 조건 : employees 테이블에서 employee_id가 10인 사원의 employee_id, first_name, vdname 출력

              first_name이 '추'이면 '마케팅부서' , first_name이 '김'이면 '기획부'

 

 

 

 

---

 

 

반복문

 

1. BASIC LOOP

- 기본 형태

LOOP
PL/SQL문장;

PL/SQL문장;
EXIT조건;
END LOOP;

  -> 조건 : 0부터 반복해서 5보다 커지면 반복문 탈출

 

 

 

2. WHILE LOOP

- 기본 형태

WHILE 조건 LOOP
PL/SQL문장;

PL/SQL문장;
EXIT조건;
END LOOP;

  -> 조건 : 0부터 반복해서 5보다 커지면 반복문 탈출

 

 

 

3. FOR LOOP

- 기본 형태

FOR counter IN [REVERSE] start..end LOOP
실행코드;
실행코드;
 …
END LOOP;

  -> 조건 : 0부터 반복해서 5보다 커지면 반복문 탈출