■ PL/SQL(Procedure Language Structured Query Language)
- 프로시저 생성자를 제공한다.
- 변수, 상수, 데이터 유형(레코드,배열)
- 조건문, 반복문
- 한번 작성하면 여러번 실행할 수 있는 재사용 가능한 프로그램 단위
- PL.SQL 이점
- 모듈식 프로그램 개발
- ORACLE 도구와 통합(ERP,HR,SCM,CRM,...)
- 이식성
- 예외처리
# PL/SQL 프로그램은 블록 구조 형식
| # 익명블록 구조 - 객체 프로그램이 아니다.(DB에 저장되어있지 않다.) DECLARE (선택) 선언부분(변수, 상수, 명시적커서, 사용자정의 예외사항) BEGIN (필수) 실행부분(SQL, 로직구현) EXCEPTION (선택) 예외사항 : 실행부분에서 발생한 오류에 대한 처리방법 END; (필수) / |
| BEGIN DBMS_OUTPUT.PUT_LINE('오늘 하루도 행복하자!'); -- 출력.한줄씩출력 DBMS_OUTPUT.PUT_LINE('2주동안에는 PLSQL개발자 마음으로 출근하자!'); END; / |
| # SQLPLUS 실행할때는 꼭 SET SERVEROUTPUT ON 로그인 후 SQL> SET SERVEROUTPUT ON --> 출력패키지 SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE('오늘 하루도 행복하자!'); -- 출력.한줄씩출력 3 DBMS_OUTPUT.PUT_LINE('2주동안에는 PLSQL개발자 마음으로 출근하자!'); 4 END; 5 / SQL> / 오늘 하루도 행복하자! 2주동안에는 PLSQL개발자 마음으로 출근하자! PL/SQL procedure successfully completed. |
SQL>L --> 방금 실행한 문장 재실행
# SQLPLUS 내 바인드 변수 출력
print :b_sal -- : 여기서는 콜론(:) 사용 무관
print b_comm
| BEGIN DBMS_OUTPUT.PUT_LINE('TODAY''S : '||to_char(sysdate,'yyyy-mm-dd')); DBMS_OUTPUT.PUT_LINE('TOMORROW''S : '||to_char(sysdate+1,'yyyy-mm-dd')); DBMS_OUTPUT.PUT_LINE(q'[TODAY'S : ]'||to_char(sysdate,'yyyy-mm-dd')); END; / |
# PL/SQL 에서 SQL함수 사용 시 주의 사항
- 프로시저문에서 사용할 수 있는 함수 : 단일행 함수
- 프로시저문에서 사용할 수 없는 함수 : DECODE, 그룹함수
| 예) BEGIN v_sum := sum(v_sum); -- 오류 : 프로시저문에서는 그룹함수를 사용 할 수 없다. END; / >> Usually a PL/SQL compilation error. |
■ 변수
- 데이터를 임시로 저장하는 메모리 영역
- 문자로 시작해야한다.
- 문자, 숫자, 특수문자(_,$,#) 포함할 수 있다.
- 변수이름의 길이는 30자 이하만 가능하다ㅏ.
- 예약어는 사용못한다.
<SYS> SELECT * FROM v$reserved_words; -- 예약어 목록
- 변수 선언시에 NOT NULL, CONSTANT(상수)로 지정된 변수에는 꼭 초기값을 할당해야 한다.
- 상수는 한번 받은 값만 계속 사용해야한다.
- 변수에 값을 할당하는 연산자는 :=, DEFAULT
- 변수는 변하는 값을 계속 입력할 수 있다.
| DECLARE v_name varchar2(20); -- 선언만 했기 때문에 null 상태 BEGIN DBMS_OUTPUT.PUT_LINE('My name is : '||v_name); -- 문자 || null -> 문자 v_name := '홍길동'; DBMS_OUTPUT.PUT_LINE('My name is : '||v_name); v_name := '박찬호'; DBMS_OUTPUT.PUT_LINE('My name is : '||v_name); END; / |
| DECLARE v_event varchar2(20) := q'[Father's day!]'; BEGIN DBMS_OUTPUT.PUT_LINE('3rd Sunday in June is : '||v_event); v_event := q'[Mother's day!]'; DBMS_OUTPUT.PUT_LINE('2rd Sunday in May is : '||v_event); END; / |
| DECLARE /* scalar data type : 단일값만 보유하는 변수 */ v_a number(7); v_b number(3) := 100; v_c varchar2(10) not null := 'oracle'; -- not null 제약조건 초기값 필수 v_d constant date default sysdate; v_e constant number(3) := 10; BEGIN v_a := 200; DBMS_OUTPUT.PUT_LINE(v_a); DBMS_OUTPUT.PUT_LINE(v_b); DBMS_OUTPUT.PUT_LINE(v_c); DBMS_OUTPUT.PUT_LINE(v_d); DBMS_OUTPUT.PUT_LINE(v_e); -- v_d := sysdate + 1; -- 오류 : 변수를 상수로 선언하여 다른 값을 가질 수 없다. -- DBMS_OUTPUT.PUT_LINE(v_d); END; / |
| DECLARE /* scalar data type */ /* local variable : 선언된 블록 프로그램에서만 수행하는 변수(지역변수) */ v_sal number := 1000; v_comm number := 100; v_tatal number; BEGIN v_tatal := v_sal + v_comm; DBMS_OUTPUT.PUT_LINE(v_tatal); END; / SELECT * FROM hr.employees WHERE salary > v_total; --> 오류 : v_total변수는 local variable이기때문에 블록바깥쪽에서 사용할 수 없다. >> ORA-00904: "V_TOTAL": invalid identifier |
↓
■ 바인드변수(Bind Variable)
- 호스트 환경에서 생성한다.
- 호스트변수라고도 한다.
- variable(var) 키워드를 이용해서 생성한다.
- 바인드변수는 SQL문과 PLSQL 블록에서 사용할 수 있다.
- 바인드 변수를 사용할 때는 콜론(:) 키워드를 바인드변수 이름 앞에 입력해야 한다.
- global variable처럼 쓰고 싶을 때 사용한다.
- global variable : 프로그램 어디서든지 사용하는 변수(전역변수)
- 바인드변수는 익명 블록 구조에서만 사용한다.
| variable b_total number DECLARE v_sal number := 1000; v_comm number := 100; BEGIN :b_total := v_sal + v_comm; DBMS_OUTPUT.PUT_LINE(:b_total); END; / SELECT * FROM hr.employees WHERE salary > :b_total; |
| var b_total number var b_sal number var b_comm number execute :b_sal := 1000 execute :b_comm := 100 DECLARE v_sal number := :b_sal; v_comm number := :b_comm; BEGIN :b_total := v_sal + v_comm; DBMS_OUTPUT.PUT_LINE(:b_total); END; / |
| var b_total number var b_sal number var b_comm number BEGIN :b_sal := 1000 -- execute 대신 BEGIN 안에서 값 할당 가능 :b_comm := 100 END; / |
| var aaa number := 10000; SELECT salary FROM hr.employees WHERE salary > :aaa; |
| /* main block, outer block */ DECLARE v_outer_variable varchar2(20) := 'global variable'; -- 프로그램 내에서 사용 BEGIN /* sub block, inner block */ DECLARE v_inner_variable varchar2(20) := 'local variable'; -- 서브블록에서만 사용 BEGIN DBMS_OUTPUT.PUT_LINE(v_inner_variable); DBMS_OUTPUT.PUT_LINE(v_outer_variable); END; DBMS_OUTPUT.PUT_LINE(v_outer_variable); -- DBMS_OUTPUT.PUT_LINE(v_inner_variable); -- 오류 : 본인블록 밖에서는 사용불가 END; / -- 마지막에만 표시!! |
# 메인블럭, 서브블럭 변수명 동일할 경우, 기본적으로 자기블럭 내 선언된 변수를 우선순위로 둔다.
| DECLARE v_father_name varchar2(20) := 'Patrick'; v_date_of_birth date := to_date('1960-01-01','yyyy-mm-dd'); BEGIN DECLARE v_child_name varchar2(20) := 'Mike'; v_date_of_birth date := to_date('1990-01-01','yyyy-mm-dd'); BEGIN DBMS_OUTPUT.PUT_LINE('Father''s Name : '||v_father_name); DBMS_OUTPUT.PUT_LINE('Date of Birth : '||v_date_of_birth); -- 서브블럭 내 선언된 1990-01-01 DBMS_OUTPUT.PUT_LINE('Child''s Name : '||v_child_name); END; DBMS_OUTPUT.PUT_LINE('Date of Birth : '||v_date_of_birth); -- 메인블럭 내 선언된 1960-01-01 END; / |
# 메인블럭, 서브블럭 변수명 동일할 경우, 서브에서 메인블럭의 변수를 사용하고 싶다 -> 레이블 설정
| <<outer>> -- 레이블이 있는 메인블럭의 변수를 사용하겠다. DECLARE v_father_name varchar2(20) := 'Patrick'; v_date_of_birth date := to_date('1960-01-01','yyyy-mm-dd'); BEGIN DECLARE v_child_name varchar2(20) := 'Mike'; v_date_of_birth date := to_date('1990-01-01','yyyy-mm-dd'); BEGIN DBMS_OUTPUT.PUT_LINE('Father''s Name : '||v_father_name); DBMS_OUTPUT.PUT_LINE('Date of Birth : '||outer.v_date_of_birth); -- 레이블.메인변수 1960-01-01 DBMS_OUTPUT.PUT_LINE('Child''s Name : '||v_child_name); DBMS_OUTPUT.PUT_LINE('Date of Birth : '||v_date_of_birth); -- 서브변수 1990-01-01 END; DBMS_OUTPUT.PUT_LINE('Date of Birth : '||v_date_of_birth); -- 메인변수 1960-01-01 END; / |
| BEGIN <<outer>> -- 이렇게 BEGIN안에 넣어서 사용도 가능 DECLARE v_father_name varchar2(20) := 'Patrick'; v_date_of_birth date := to_date('1960-01-01','yyyy-mm-dd'); BEGIN DECLARE v_child_name varchar2(20) := 'Mike'; v_date_of_birth date := to_date('1990-01-01','yyyy-mm-dd'); BEGIN DBMS_OUTPUT.PUT_LINE('Father''s Name : '||v_father_name); DBMS_OUTPUT.PUT_LINE('Date of Birth : '||outer.v_date_of_birth); DBMS_OUTPUT.PUT_LINE('Child''s Name : '||v_child_name); DBMS_OUTPUT.PUT_LINE('Date of Birth : '||v_date_of_birth); END; DBMS_OUTPUT.PUT_LINE('Date of Birth : '||v_date_of_birth); END; END outer; -- 마무리 / |
| <<outer>> DECLARE v_sal number(8,2) := 60000; v_comm number(8,2) := v_sal * 0.20; -- 60000* 0.20 = 12000 v_message varchar2(50) := 'eligible for commission'; BEGIN DECLARE v_sal number(8,2) := 50000; v_comm number(8,2) := 0; v_total number(8,2) := v_sal + v_comm; -- 50000 BEGIN v_message := 'Clerk not '||v_message; -- 메인변수 수정 Clerk not eligible for commission outer.v_comm := v_sal * 0.30; -- 메인변수 수정 50000 * 0.30 = 15000 DBMS_OUTPUT.PUT_LINE('**** sub block ****'); DBMS_OUTPUT.PUT_LINE(v_sal); -- 서브변수 50000 DBMS_OUTPUT.PUT_LINE(v_comm); -- 서브변수 0 DBMS_OUTPUT.PUT_LINE(v_total); -- 서브변수 50000 DBMS_OUTPUT.PUT_LINE(v_message); -- 메인변수 Clerk not eligible for commission END; DBMS_OUTPUT.PUT_LINE('**** main block ****'); DBMS_OUTPUT.PUT_LINE(v_sal); -- 메인변수 60000 DBMS_OUTPUT.PUT_LINE(v_comm); -- 메인변수 15000 DBMS_OUTPUT.PUT_LINE('Salesman '||v_message); -- 메인변수 Salesman Clerk not eligible for commission END; / |
■ IF 조건 제어문
- PL/SQL에서 조건에 따라 선별적으로 작업을 수행할 수 있다.
- TRUE/FALSE
- BOOLEAN DATA TYPE (TRUE, FALSE, NULL)
| IF 조건 THEN 참 ELSIF 조건 THEN 참 ELSIF 조건 THEN 참 ... ELSE 기본값 END IF; |
| DECLARE v_flag boolean := true; -- boolean : 문자형과 다르다. BEGIN IF v_flag THEN DBMS_OUTPUT.PUT_LINE('참'); END IF; END; / |
| DECLARE v_flag boolean := TRUE; BEGIN IF v_flag THEN DBMS_OUTPUT.PUT_LINE('참'); ELSE DBMS_OUTPUT.PUT_LINE('거짓'); END IF; END; / |
# 비교연산자
=, >, >=, <, <=, <>, !=, ^=
# 논리연산자
NOT, AND, OR
# 기타 비교연산자
BETWEEN AND, IN, LIKE
# NULL 연산자
IS NULL, IS NOT NULL
| DECLARE v_a number; BEGIN IF v_a IS NULL THEN DBMS_OUTPUT.PUT_LINE('변수에 NULL값으로 입력되어있습니다.'); ELSE DBMS_OUTPUT.PUT_LINE(v_a); END IF; END; / |
| DECLARE v_num1 number(2) := 10; v_num2 number(2) := 5; BEGIN IF v_num1 >= v_num2 THEN DBMS_OUTPUT.PUT_LINE(v_num1 - v_num2); ELSIF v_num1 < v_num2 THEN DBMS_OUTPUT.PUT_LINE(v_num2 - v_num1); END IF; END; / |
DECLARE v_num1 number(2) := 10; v_num2 number(2) := 5; BEGIN IF v_num1 >= v_num2 THEN DBMS_OUTPUT.PUT_LINE(v_num1 - v_num2); ELSE DBMS_OUTPUT.PUT_LINE(v_num2 - v_num1); END IF; END; / |
| [문제] v_sal : 10000 v_comm : null ((v_sal * 12) + (v_sal * 12 * v_comm)) DECLARE v_sal number := 10000; v_comm number; v_total number; BEGIN IF v_comm IS NOT NULL THEN v_total := ((v_sal * 12) + (v_sal * 12 * v_comm)); DBMS_OUTPUT.PUT_LINE(q'[(v_sal * 12) + (v_sal * 12 * v_comm) = ]'||v_total); ELSE v_total := (v_sal * 12); DBMS_OUTPUT.PUT_LINE(q'[v_sal * 12 = ]'||v_total); END IF; END; / |
■ CASE 표현식
| DECLARE v_grade char(1) := upper('a'); v_appraisal varchar2(30); BEGIN v_appraisal := CASE v_grade WHEN 'A' THEN '참잘했어요' WHEN 'B' THEN '잘했어요' WHEN 'C' THEN '다음에 잘해요' ELSE '니가 사람이야!!!' END; DBMS_OUTPUT.PUT_LINE('등급은 '||v_grade||', 평가는 '||v_appraisal); END; / |
| DECLARE v_grade char(1) := upper('a'); v_appraisal varchar2(30); BEGIN v_appraisal := CASE WHEN v_grade = 'A' THEN '참잘했어요' WHEN v_grade IN ('B','C') THEN '잘했어요' WHEN v_grade = 'D' THEN '다음에 잘해요' ELSE '니가 사람이야!!!' END; DBMS_OUTPUT.PUT_LINE('등급은 '||v_grade||', 평가는 '||v_appraisal); END; / |
■ 반복문
1. LOOP 문
- 조건이 없이 반복적인 작업 수행
- EXIT : 반복문 종료하는 문
| DECLARE v_cnt number := 1; BEGIN LOOP DBMS_OUTPUT.PUT_LINE(v_cnt); v_cnt := v_cnt + 1; IF v_cnt = 11 THEN EXIT; -- 무한루프 종료를 위해 조건과 함께 작성 END IF; END LOOP; END; / |
| [문제] 화면에 1 ~ 10까지 출력하는 프로그램을 작성해주세요. 단, 4,8번은 출력 제외 | |
| DECLARE i number := 0; BEGIN LOOP i := i + 1; IF i > 10 then EXIT; ELSIF i NOT IN (4,8) THEN DBMS_OUTPUT.PUT_LINE(i); END IF; END LOOP; END; / |
DECLARE i number := 1; BEGIN LOOP IF i IN (4,8) THEN NULL; -- 아무것도 안함 ELSE DBMS_OUTPUT.PUT_LINE(i); END IF; i := i + 1; EXIT WHEN i > 10; END LOOP; END; / |
# CONTINUE : 현재 루프를 종료하고 다음 루프로 반복하는 문
| DECLARE i number := 1; BEGIN LOOP IF i IN (4,8) THEN i := i + 1; CONTINUE; ELSE DBMS_OUTPUT.PUT_LINE(i); END IF; i := i + 1; EXIT WHEN i > 10; END LOOP; END; / |
DECLARE i number := 0; BEGIN LOOP i := i + 1; CONTINUE WHEN i = 4 OR i = 8; DBMS_OUTPUT.PUT_LINE(i); EXIT WHEN i = 10; END LOOP; END; / |
| >> CONTINUE를 만나 IF i IN (4,8) THEN 부터 반복 | >> CONTINUE를 만나 i := i + 1 부터 반복 |
| [문제] 1부터 100까지 합을 출력해주세요. DECLARE i number := 1; -- 카운터를 위한 변수. total number := 0; -- 누적합을 위한 변수, 초기값이 NULL이면 계산이 안되니 0으로 지정. -- 변수명 sum 불가. reserved_words는 사용이 안된다. BEGIN LOOP total := total + i; i := i + 1; EXIT WHEN i > 100; END LOOP; DBMS_OUTPUT.PUT_LINE('1부터 100까지 합 : '||total); END; / |
| [문제] 1부터 100까지 짝수만 출력해주세요. DECLARE i number := 1; BEGIN DBMS_OUTPUT.PUT_LINE('1부터 100까지 짝수만 출력 : '); LOOP IF mod(i,2) = 0 THEN DBMS_OUTPUT.PUT_LINE(i); END IF; i := i + 1; EXIT WHEN i >100; END LOOP; END; / |
'Oracle SQL' 카테고리의 다른 글
| 16 PL/SQL RECORD, Array (0) | 2023.10.27 |
|---|---|
| 15 PL/SQL WHILE LOOP, FOR, Implicit Cursor (1) | 2023.10.26 |
| 13-1 SAVEPOINT (0) | 2023.10.24 |
| 12 Sequence, Synonym, Extract, 날짜함수, 분석함수, Rank (0) | 2023.10.23 |
| 11 Unique, Check, Not Null, Rename, Flashback, Truncate, Comment, View (0) | 2023.10.20 |