[오라클로 배우는 데이터베이스 입문] 16. PL/SQL 기초
1. PL/SQL 구조
- 블록이란? PL/SQL의 기본 단위
- PL/SQL: SQL만으로 구현이 어렵거나 구현 불가능한 작업을 수행하기 위해 오라클에서 제공하는 프로그래밍언어,변수, 조건 처리 등 다양한 기능 사용 가능
| 구성 키워드 | 필수/선택 | 설명 |
| DECLARE(선언부) | 선택 | 실행에 사용될 변수, 상수, 커서 등을 선언 |
| BEGIN(실행부) | 필수 | 조건문, 반복문, SELEC, DML, 함수 등을 정의 |
| EXCEPTION(예외 처리부) | 선택 | PL/SQL 실행 도중 발생하는 오류(예외 상황)를 해결하는 문장 기술 |
※ 작성을 끝낸 PL/SQL은 END키워드로 종료 명시
| ○ 기본 형식 DECLARE [실행에 필요한 여러 요소 선언]; BEGIN [작업을 위해 실제 실행하는 명령어]; EXCEPTION [PL/SQL 수행 도중 발생하는 오류 처리]; END; |
- HELLO, PL/SQL 출력하기
-PL/SQL 주석
2. 변수와 상수
- 변수 선언과 값 대입하기
○ 변수: 데이터를 일시적으로 저장하는 요소로 이름과 저장할 자료형을 지정하여 선언부(DECLARE)에서 작성
○ 기본 변수 선언과 사용
| 변수 이름 자료형 := 값 또는 값이 도출되는 여러 표현식; |
| 변수 이름 : 데이터를 저장할 변수 이름 지정 |
| 자료형: 선언한 변수에 저장할 데이터의 자료형 지정 |
| := : 선언한 변수에 값을 할당/ 오른쪽 값에 왼쪽 변수를 대입하겠다는 뜻/ 값을 넣지 않을 거면 생략 가능 |
| 값 또는 값이 도출되는 여러 표현식; : 변수에 저장할 첫 데이터 값 혹은 저장할 수 있는 값이 결과로 반환되는 표현식 |
○ 상수 정의하기
: 한번 저장하면 프로그램이 종료될 때까지 유지되는 저장요소를 상수라고 한다.
| 변수 이름 CONSTANT 자료형 := 값 또는 값이 도출되는 여러 표현식; |
| 변수 이름 : 데이터를 저장할 변수 이름 지정 |
| CONSTANT : 선언한 변수를 상수로 정의, 한번 저장한 값을 변하지 않음 |
| 자료형: 선언한 변수에 저장할 데이터의 자료형 지정 |
| := : 선언한 변수에 값을 할당/ 오른쪽 값에 왼쪽 변수를 대입하겠다는 뜻/ 값을 넣지 않을 거면 생략 가능 |
| 값 또는 값이 도출되는 여러 표현식; : 변수에 저장할 첫 데이터 값 혹은 저장할 수 있는 값이 결과로 반환되는 표현식 |
○ 변수의 기본값 지정하기
| 변수 이름 자료형 DEFAULT 값 또는 값이 도출되는 여러 표현식; |
| 변수 이름 : 데이터를 저장할 변수 이름 지정 |
| 자료형: 선언한 변수에 저장할 데이터의 자료형 지정 |
| DEFAULT : DEFALUT 키워드를 작성하여 변수의 기본값 명시 |
| 값 또는 값이 도출되는 여러 표현식; : 변수에 저장할 첫 데이터 값 혹은 저장할 수 있는 값이 결과로 반환되는 표현식 |
○ 변수에 NULL 값 저장 막기
선언한 변수에 특정 값을 할당하지 않으면 NULL값이 기본으로 할당되기 때문에 NOT NULL키워드를 사용한 변수는 반드시 선언과 동시에 특정 값을 지정해야 함
| 변수 이름 자료형 NOT NULL := 또는 DEFAULT 값 또는 값이 도출되는 여러 표현식; |
| 변수 이름 : 데이터를 저장할 변수 이름 지정 |
| 자료형: 선언한 변수에 저장할 데이터의 자료형 지정 |
| NOT NULL: NOT NULL 키워드를 작성하여 변수에 NULL이 저장되지 못하게 막음 |
| := 또는 DEFAULT 값 또는 값이 도출되는 여러 표현식; : 변수에 저장할 첫 데이터 값 혹은 저장할 수 있는 값이 결과로 반환되는 표현식/ NULL이 아닌 값을 할당해야 함/DEFAULT 변수 사용해서 기본값 설정해도 됨 |
○ 변수 이름 정하기
식별자: 변수를 포함한 PL/SQL문에서 지정하는 객체 이름
| 1) 같은 블록 안에서 식별자는 고유해야 하며 중복될 수 없음 2) 대.소문자를 구별하지 않음 3) 테이블이름 붙이는 규칙과 같은 규칙을 따름 - 이름은 문자로 시작해야 한다 - 이름은 30byte이하여야 한다(영어는 30byte, 한글은 15byte) - 이름은 영문자(한글 가능), 숫자(0-9), 특수문자($,#,_)사용할 수 있다 - SQL 키워드는 테이블 이름으로 사용할 수 없다(SELECT, FROM 등 사용불가) |
- 변수의 자료형
○ 스칼라형 : 숫자, 문자열, 날짜 등과 같이 오라클에서 기본으로 정의해놓은 자료형으로 내부 구성요소가 없는 단일 값
| 분류 | 자료형 | 설명 |
| 숫자 | NUMBER | 소수점을 포함할 수 있는 최대 38자리 숫자 데이터 |
| 문자열 | CHAR | 최대32,767바이트 고정 길이 문자열 데이터 |
| VARCHAR2 | 최대 32,767바이트 가변 길이 문자열 데이터 | |
| 날짜 | DATE | 기원전 4712년 1월 1일부터 서기 9999년 12월 31일까지 날짜 데이터 |
| 논리 데이터 | BOOLEAN | PL/SQL에서만 사용할 수 있는 논리 자료형으로 true, false, NULL 포함 |
○ 참조형: 특정 테이블 열의 자료형이나 하나의 행 구조를 참조하는자료형
/ 열을 참조할 때 %TYPE, 행을 참조할때 %ROWTYPE 사용
| 변수 이름 테이블 이름.열이름%TYPE; |
| 변수 이름 : 데이터가 저장될 변수의 이름을 저장 |
| 테이블 이름.열이름 : 특정 테이블에 속한 이름을 명시, 명시된 테이블의 열과 같은 크기의 자료형이 지정됨 |
| %TYPE: 앞에서 지정한 테이블의 열과 같은 자료형 및 크기임을 명시. 이후 := 또는 DEFAULT 키워드를 사용해 값을 먼저 지정 |
| 변수 이름 테이블 이름%ROWTYPE; |
| 변수 이름 : 데이터가 저장될 변수의 이름을 저장 |
| 테이블 이름 : 특정 테이블에 속한 이름을 명시, 명시된 테이블의 열과 같은 크기의 자료형이 지정됨 |
| %TYPE과는 달리 저장할 값을 직접 지정할 수 있음 |
○ 복합형, LOB형
| 복합형 | 여러 종류 및 개수의 데이터를 저장하기 위해 사용자가 직접 정의하는 자료형 | |
| 분류 | 자료형 | 설명 |
| 컬렉션 | TABLE | 한 가지 자료형의 데이터를 여러 개 저장(테이블의 열과 유사) |
| 레코드 | RECORD | 여러 종류 자료형의 데이터를 저장(테이블의 행과 유사) |
| LOB형(LargeObject) | 대용량 텍스트, 이미지, 동영상, 사운드 데이터 등 대용량 데이터를 저장하기 위한 자료형으로 BLOB,CLOB등이 있음 | |
3. 조건 제어문
조건문: 특정 조건식을 통해 상황에 따라 실행할 내용을 달리하는 방식의 명령어
○ IF 조건문
| 종류 | 설명 |
| IF-THEN | 특정 조건을 만족하는 경우 조건 수행 |
| IF-THEN-ELSE | 특정 조건을 만족하는 경우와 반대 경우에 각각 지정한 작업 수행 |
| IF-THEN-ELSIF | 여러 조건에 따라 각각 지정한 작업 수행 |
○ IF-THEN
주어진 조건식의 결과 값이 true인 경우 작업 수행 false 또는 NULL인 경우 작업 수행하지 않고 다음 내용 실행
| IF 조건식 THEN [ true 또는 false 판별이 가능한 조건식을 지정, 여러 연산자 및 함수 사용 가능 수행할 명령어; [ 조건식의 결과 값이 true일 때 실행할 명령어 지정, 여러 명령어 지정가능 END IF; [ IF 종료] |
○ IF-THEN-ELSE
주어진 조건식의 결과 값이 true인 경우 실행할 명령어와 true가 아닐 때 실행할 명령어 각각 지정
| IF 조건식 THEN [ true 또는 false 판별이 가능한 조건식을 지정, 여러 연산자 및 함수 사용 가능 수행할 명령어; [ 조건식의 결과 값이 true일 때 실행할 명령어 지정, 여러 명령어 지정가능 ELSE 수행할 명령어; [조건식의 결과 값이 true가 아닌 경우 실행할 명령어 지정] END IF; [ IF 종료] |
○ IF-THEN-ELSIF
여러 종류의 조건을 지정하여 각 조건을 만족하는 경우마다 다른 작업의 수행 지정
| IF 조건식 THEN ---1 수행할 명령어; ---2 ELSIF 조건식---3 수행할 명령어; ---4 ELSIF---5 수행할 명령어---6 ... ELSE---7 수행할 명령어;---8 END IF; ---9 |
| 1~2 : 1조건식의 결과 값이 true면 2명령어 수행하고 false면 다음 ELSIF조건식으로 넘어감 |
| 3~6 : 3조건식의 결과 값이 true면 4명령어 수행하고 false면 다음 ELSIF 혹은 ELSE로 넘어감(5~6 동일) |
| 7~8 : 7 위 IF,ELSIF 조건식의 결과 값이 어디서도 true가 나오지 않으면 명령어 수행 |
| 9 : IF문 종료 |
- CASE 조건문: IF문과 마찬가지로 조건식의 결과 값에 따라 여러가지 수행 작업 지정 가능
| 종류 | 설명 |
| 단순CASE문 | 비교 기준이 되는 조건의 값이 여러 가지일 때 해당 값만명시하여 작업 수행 |
| 검색CASE문 | 특정한 비교 기준 없이 여러 조건식을 나열하여 조건식에 맞는 작업 수행 |
○ 단순 CASE
| CASE 비교기준----1 WHEN 값1 THEN----2 수행할 명령어;----3 WHEN 값2 THEN----4 수행할 명령어;----5 ... ELSE----6 수행할 명령어;----7 END CASE;----8 |
| 1) 여러 결과 값이 나올 수 있는 비교 기준 지정 2) 비교 기준의 결과 값이 값1과 일치하면 3)을 수행하고 나머지 명령어는 건너뜀, 4)~5)동일 6)~7) 6) 위의 WHEN값에 일치하는 값 못찾으면 마지막으로 ELSE에 지정한 7)작업 수행 8) CASE문 종료 |
○ 검색 CASE
| CASE WHEN 조건식1 THEN----1 수행할 명령어;----2 WHEN 조건식2 THEN----3 수행할 명령어;----4 ... ELSE----5 수행할 명령어;----6 END CASE; |
| 1)~4) 1)조건식1의 결과값이 true라면 2)작업 수행 나머지 건너뜀/3)~4) 동일 5)~6) 5) 위에 실행한 WHEN절의 조건식을 만족하는 경우가 없으면 6)작업 수행 |
4. 반복 제어문
반복문은 특정 작업을 반복하여 수행하고자 할 때 사용
| PL/SQL이 제공하는 반복문 | |
| 종류 | 설명 |
| 기본 LOOP | 기본 반복문 |
| WHILE LOOP | 특정 조건식의 결과를 통해 반복 수행 |
| FOR LOOP | 반복 횟수를 정하여 반복 수행 |
| Cursor FOR LOOP | 커서를 활용한 반복 수행 |
| 반복 수행을 중단시키거나 특정 반복 주기를 건너뛰는 명령어 | |
| EXIT | 수행중인 반복 종료 |
| EXIT-WHEN | 반복 종료를 위한 조건식 지정하고 만족하면 반복 종료 |
| CONTINUE | 수행중인 반복의 현재 주기를 건너뜀 |
| CONTINUE-WHEN | 특정 조건식을 지정하고 조건식을 만족하면 현재 반복 주기를 건너뜀 |
- 기본 LOOP : 반복의 종료 시점이나 조건식을 따로 명시하지않으므로 무한루프하게 됨/보통 EXIT명령어를 함께 사용하여 반복을 종료하도록 함
| LOOP 반복 수행 작업; END LOOP; |
- WHILE LOOP: 반복 수행 여부를 결정하는 조건식을 먼저 지정 후 true일 때 반복 false일 때 끝냄
| WHILE 조건식 LOOP 반복 수행 작업; END LOOP; ※ 반복 수행되기 전에 조건식을 검사하므로 조건식의 결과 값에 따라 단 한 번도 반복 수행되지 않을 수 있음! |
- FOR LOOP: 반복 횟수를 지정할 수 있는 반볻문으로 지정한 시작값부터 1씩 증가하여 종료값에 이를 때까지 반복수행
| FOR i IN 시작 값 . . 종료 값 LOOP 반복 수행 작업; END LOOP; ※ i : 반복 수행 중의 시작과 종료 값 사이의 현재 숫자가 저장되는 특수한 변수로 카운터라고 함 |
| 역순으로 반복하고 싶다면? |
| FOR i IN REVERSE 시작 값 . . 종료 값 LOOP 반복 수행 작업; END LOOP; |
- CONTUNUE문, CONTINUE-WHEN문
: 반복수행중 CONTUNUE가 실행되면 현재 반복주기에 수행해야 하는 작업 건너뛰고 다음 반복주기로 넘어감
