PLSQL
SQL만으로는 구현이 어렵거나 구현 불가능한 작업을 수행하기위해 오라클에서 제공하는 프로그래밍 언어
PL/SQL 블록의 기본형식
변수이름 자료형 := 값또는 값이 도출되는 여러 표현식;
상수정의 변수이름 CONSTANT 자료형 := 값또는 값을 도출하는 여러 표현식;
변수 기본값 지정 변수이름 자료형 DEFAULT 값 도는 값 도출되는 여러 표현식;
변수 NULL값 저장 막기 변수이름 자료형 NOT NULL := 또는 DEFAULT 값 또는 값이 도출되는 표현식;
변수 자료형
참조형 변수이름 테이블이름.열이름%TYPE
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
--icia_user 계정
SET SERVEROUTPUT ON; --실행결과를 화면에 출력(PL/SQL CMD 창에서)
--보기 탭의 DBMS 출력
BEGIN
DBMS_OUTPUT.PUT_LINE('HELLO, PL/SQL'); --DBMS 출력물
END;
/
DECLARE --선언부(변수선언)
V_EMPNO NUMBER(4) := 7788; --변수명/데이터타입/:= 변수값
V_ENAME VARCHAR2(10);
V_TAX CONSTANT NUMBER(1) := 3; --상수
V_DEPTNO NUMBER(2) DEFAULT 10; --변수의 기본 값
BEGIN
V_ENAME := 'SCOTT'; --변수에 값을 대입(:= 대입연산자)
DBMS_OUTPUT.PUT_LINE('V_ENAME: ' || V_ENAME);
DBMS_OUTPUT.PUT_LINE('V_EMPNO: ' || V_EMPNO);
DBMS_OUTPUT.PUT_LINE('V_TAX: ' || V_TAX);
--V_TAX := 5; --V_TAX는 상수이므로 값을 변경할 수 없다.(변수에 새로운 값을 대입하면 오류)
DBMS_OUTPUT.PUT_LINE('V_DEPTNO: ' || V_DEPTNO);
V_DEPTNO := 20; --DEFAUL는 기본값이므로 변경이 가능
DBMS_OUTPUT.PUT_LINE('V_DEPTNO: ' || V_DEPTNO);
END;
/
DECLARE
V_DEPTNO DEPT.DEPTNO%TYPE := 50;
BEGIN
SELECT DEPTNO
INTO V_DEPTNO --해당 쿼리값을 변수에 대입
FROM DEPT
WHERE DEPTNO = 10;
DBMS_OUTPUT.PUT_LINE('V_DEPTNO: ' || V_DEPTNO);
END;
/
DECLARE
V_DEPT_ROW DEPT%ROWTYPE; --ROWTYPE은 하나의 행의 전체 COLUMN
BEGIN
SELECT DEPTNO, DNAME, LOC INTO V_DEPT_ROW
FROM DEPT
WHERE DEPTNO = 40;
DBMS_OUTPUT.PUT_LINE('DEPTNO: ' || V_DEPT_ROW.DEPTNO);
DBMS_OUTPUT.PUT_LINE('DNAME: ' || V_DEPT_ROW.DNAME);
DBMS_OUTPUT.PUT_LINE('LOC: ' || V_DEPT_ROW.LOC);
END;
/
DECLARE
V_DEPTNO DEPT.DEPTNO%TYPE := 50;
V_DNAME DEPT.DNAME%TYPE := 50;
V_LOC DEPT.LOC%TYPE := 50;
BEGIN
SELECT DEPTNO, DNAME, LOC INTO V_DEPTNO, V_DNAME, V_LOC --각각의 COLUMN을 각각의 변수에 대입한다.
FROM DEPT
WHERE DEPTNO = 40;
DBMS_OUTPUT.PUT_LINE('V_DEPTNO: ' || V_DEPTNO);
DBMS_OUTPUT.PUT_LINE('V_DNAME: ' || V_DNAME);
DBMS_OUTPUT.PUT_LINE('V_LOC: ' || V_LOC);
END;
/
DECLARE
V_NUMBER NUMBER := 13;
BEGIN
IF MOD(V_NUMBER, 2) = 4 THEN --MOD는 몫을 구하는 연산자
DBMS_OUTPUT.PUT_LINE('V_NUMBER는 홀수입니다.');
ELSE
DBMS_OUTPUT.PUT_LINE('V_NUMBER는 짝수입니다.');
END IF;
DBMS_OUTPUT.PUT_LINE('IF 문 끝');
END;
/
/*
문제
90점 이상은 A학점
80점 이상은 B학점
70점 이상은 C학점
60점 이상은 D학점
나머지는 F학점
(ELSIF)
*/
DECLARE
V_SCORE NUMBER := 85;
BEGIN
IF V_SCORE >= 90 THEN
DBMS_OUTPUT.PUT_LINE('A학점 입니다.');
ELSIF V_SCORE >= 80 THEN
DBMS_OUTPUT.PUT_LINE('B학점 입니다.');
ELSIF V_SCORE >= 70 THEN
DBMS_OUTPUT.PUT_LINE('C학점 입니다.');
ELSIF V_SCORE >= 60 THEN
DBMS_OUTPUT.PUT_LINE('D학점 입니다.');
ELSE
DBMS_OUTPUT.PUT_LINE('F학점 입니다.');
END IF;
END;
/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
------- PLSQL
/*
문제 : 직원번호가 7000(홍길동)번인 직원에 대한 정보를 조회하여 출력
- 7000번 홍길동 사원 전산팀 1000원 (이번달 급여액)
현재등급에 따라 각 등급별 최소급여액과 최대급여액 출력
-현재 등급은 1등급이며 최저급여:1000원 최대급여:2000원
*/
DECLARE
V_EMPNO EMP.EMPNO%TYPE;
V_ENAME EMP.ENAME%TYPE;
V_JOB EMP.JOB%TYPE;
V_DNAME DEPT.DNAME%TYPE;
V_SALCOM BONUS.SAL%TYPE;
V_GRADE SALGRADE.GRADE%TYPE;
BEGIN
SELECT E.EMPNO, E.ENAME, E.JOB, D.DNAME, B.SAL+B.COMM
INTO V_EMPNO, V_ENAME, V_JOB, V_DNAME, V_SALCOM
FROM EMP E, DEPT D, BONUS B
WHERE E.EMPNO = 7000
AND E.DEPTNO = D.DEPTNO
AND E.EMPNO = B.EMPNO
AND B.PAYMONTH = TO_CHAR(SYSDATE, 'YYYYMM');
DBMS_OUTPUT.PUT_LINE(V_EMPNO || '번 ' || V_ENAME || ' ' ||
V_JOB || ' ' ||V_DNAME || ' ' || V_SALCOM || '원');
SELECT GRADE
INTO V_GRADE
FROM EMP
WHERE EMPNO = V_EMPNO;
IF V_GRADE = 1 THEN
DBMS_OUTPUT.PUT_LINE('현재 등급은 ' ||
V_GRADE || '이며 최저급여:1000원 최대급여:2000원');
ELSIF V_GRADE = 2 THEN
DBMS_OUTPUT.PUT_LINE('현재 등급은 ' ||
V_GRADE || '이며 최저급여:3000원 최대급여:4000원');
ELSIF V_GRADE = 3 THEN
DBMS_OUTPUT.PUT_LINE('현재 등급은 ' ||
V_GRADE || '이며 최저급여:5000원 최대급여:6000원');
ELSE
DBMS_OUTPUT.PUT_LINE('현재 등급은 ' ||
V_GRADE || '이며 최저급여:7000원 최대급여:8000원');
END IF;
END;
/
CUSOR
SQL 커서는 Oracle 서버에서 할당한 전용 메모리 영역에 대한 포인터이다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DECLARE
V_DEPTNO EMP.DEPTNO%TYPE;
CURSOR C1 (P_DEPTNO EMP.DEPTNO%TYPE) IS --EMP.DEPTNO를 P_DEPTNO로 명시해라
SELECT DEPTNO, EMPNO, ENAME
FROM EMP
WHERE DEPTNO = P_DEPTNO;
BEGIN
V_DEPTNO := &INPUT_DEPTNO;
FOR C1_REC IN C1(V_DEPTNO) LOOP --메모리의 시작주소는 C1에 가지고있다.
DBMS_OUTPUT.PUT_LINE('DEPTNO : ' || C1_REC.DEPTNO ||
', EMPNO : ' || C1_REC.EMPNO || ', ENAME : ' || C1_REC.ENAME);
END LOOP;
DBMS_OUTPUT.PUT_LINE('FOR LOOP END.');
END;
/