![](https://blog.kakaocdn.net/dn/biMSTE/btqz1BM3k5J/8qKoQMUyQCzqKIkULnOAVk/img.jpg)
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
|
20. PL SQL : SQL언어에 절차적프로그래밍 언어를 가미해 만든것. SQL문장에
변수정의, 조건처리(if), 반복(loop, while, for)등을 지원하여 sql의 단점보완
-기능 변수선언, 비교처리, 반복처리
-구조 declare 선언
begin 실행
exception 예외
end 종료
-변수의 종류
--스칼라:기존sql의 자료형과 유사
ex) ename varchar2(10);
--레퍼런스: 선언된 다른변수 혹은 데이터베이스 컬럼에 맞추어 선언(%type).
또한 row(행)단위로 참조할수있는 %rowtype
ex) vempno emp.empno%type;
ex) vemp emp%rowtype;
-select문 사용
--into절 필요 개수와 데이터타입 길이 일치해야함
-레코드타입: 프로그램언어의 구조체와 유사. 필드의 집합을
-하나의 논리적 단위로 처리할수있게 해주므로 테이블의 row를 읽어올때 편리
ex) TYPE emp_rec_type IS RECORD (v_empno emp.empno%type,
v_ename emp.ename%type,
v_sal emp.sal%type,
v_deptno emp.deptno%type);
v_emp emp_rec_type;
--is table of :배열과 유사, 동적으로 자유롭게 증가 가능
ex) type emp_table_ename_type is TABLE OF emp.ename%type
INDEX BY PLS_INTEGER;
vemp_ename emp_table_ename_type;
-if 사용
-- if ~~~ then ~~~
elsif ~~~ then ~~~
else ~~~
end if;
-loop 사용
-- loop
출력문
n:=n+1;
if n>=5 then exit;
end loop;
-for loop 사용
-- for n in 1..5 loop
end loop;
-while loop는 안배웠나;
21. 저장 프로시저, 함수, 커서
-프로시져: 반복적인 작업을 하나로 묶어놓은것을 의미함. 다시 입력할 필요 없이 간단히 호출하여 실행 결과를 얻을수있음. 성능향상, 호환성문제 해결
-생성 create or replace procedure proc01
is
변수선언
begin
select문
+필요한 기능
end;
-사용 in 데이터 전달받을때
out 수행결과 받을때
inout 두가지 목적 모두
-실행: exec 프로시저이름; /execute 프로시저이름;
-삭제 : drop procedure 프로시저이름
|
cs |
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
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
|
SET SERVEROUTPUT ON;
-- plsql의 구조
declare
v_sum number:=10;
v_name varchar(40):='hong';
begin
dbms_output.put_line(v_sum);
dbms_output.put_line(v_name);
end;
--plsql의 구조2
declare
vempno number(4);
vempname varchar2(10):='hong';
begin
vempno:=7788;
dbms_output.put_line('no'||vempno);
dbms_output.put_line('name'||vempname);
end;
--plsql의 구조3 select에 into 사용
declare
vempno emp.empno%type;
vname emp.ename%type;
begin
select empno, ename into vempno, vname
from emp
where empno = 7934;
dbms_output.put_line('empno : '||vempno);
dbms_output.put_line('empname : '||vname);
end;
--프로시져:반복적인 작업을 하나로 묶어놓은것을 의미함
--is 와 begin 사이에 변수
create procedure proc01
is
v_sum number:= 0;
begin
select avg(sal) into v_sum
from emp;
dbms_output.put_line('sum:'||v_sum);
end;
--실행
exec proc01();
--외부에서 사번을 받아, 사번에 대한 정보 출력
--if / else / end if;
create or replace procedure proc02(a number)
is
v_sal number:=0;
begin
select avg(sal) into v_sal
from emp
where deptno= a;
if v_sal is null then
dbms_output.put_line('입력하신 번호의 사원이 없습니다');
else
dbms_output.put_line('평균임금은 '||v_sal);
end if;
end;
exec proc02(7788);
--레퍼런스 사원번호, 사원명, 입사일, 급여
create or replace procedure proc03(eno emp.empno%type)
is
v_empno emp.empno%type;
v_ename emp.ename%type;
v_hiredate emp.hiredate%type;
v_sal emp.sal%type;
begin
select empno, ename, hiredate, sal into v_empno, v_ename, v_hiredate, v_sal
from emp
where empno = eno;
if v_ename is null then
dbms_output.put_line('no data');
else
dbms_output.put_line('사원번호 : '||v_empno);
dbms_output.put_line('이름 : '||v_ename);
dbms_output.put_line('입사일 : '||v_hiredate);
dbms_output.put_line('급여 : '||v_sal);
end if;
end;
--exception
create or replace procedure proc0003(eno emp.empno%type)
is
v_empno emp.empno%type;
v_ename emp.ename%type;
v_hiredate emp.hiredate%type;
v_sal emp.sal%type;
begin
select empno, ename, hiredate, sal into v_empno, v_ename, v_hiredate, v_sal
from emp
where empno = eno;
dbms_output.put_line('사원번호 : '||v_empno);
dbms_output.put_line('이름 : '||v_ename);
dbms_output.put_line('입사일 : '||v_hiredate);
dbms_output.put_line('급여 : '||v_sal);
exception
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('데이터 없음');
end;
exec proc03(7788);
exec proc0003(102);
--exception
create or replace procedure proc4(a number, b number)
is
begin
dbms_output.put_line(a/b);
exception
when zero_divide then
dbms_output.put_line('0으로 나눌수없습니다');
end;
exec proc4(4, 0);
create or replace procedure proc5(a number)
is
emp_rec emp%rowtype;
begin
select * into emp_rec
from emp
where empno = a;
dbms_output.put_line(emp_rec.empno);
dbms_output.put_line(emp_rec.ename);
dbms_output.put_line(emp_rec.sal);
exception
when NO_DATA_FOUND then
dbms_output.put_line('no dataaaa');
end;
exec proc5(7844);
exec proc5(102);
--레코드타입1
create or replace PROCEDURE proc6(a number)
is
TYPE emp_rec_type IS RECORD(empno emp.empno%type,
sal emp.sal%type,
hiredate emp.hiredate%type);
v_emp_rec emp_rec_type;
begin
select empno, sal, hiredate into v_emp_rec
from emp
where empno = a;
dbms_output.put_line(v_emp_rec.empno);
dbms_output.put_line(v_emp_rec.sal);
dbms_output.put_line(v_emp_rec.hiredate);
end;
exec proc6(7844);
--레코드타입2
create or replace procedure proc7(a number)
is
TYPE emp_rec_type IS RECORD(empno emp.empno%type,
ename emp.ename%type,
job emp.job%type,
sal emp.sal%type,
deptno emp.deptno%type);
vemp emp_rec_type;
begin
select empno, ename, job, sal, deptno into vemp
from emp
where empno = a;
dbms_output.put_line(vemp.empno);
dbms_output.put_line(vemp.ename);
dbms_output.put_line(vemp.job);
dbms_output.put_line(vemp.sal);
dbms_output.put_line(vemp.deptno);
exception
when no_data_found then
dbms_output.put_line('no data');
end;
exec proc7(7844);
exec proc7(784);
--2000이상row / 2000~4000middle / 4000이상high
--if... deptno 10=a 20=b 30=c 40=d
--사원번호, 이름, 급여, 급여결과, 부서코드 부서명
create or replace procedure proc08(a number)
is
TYPE emp_rec_type IS RECORD (v_empno emp.empno%type,
v_ename emp.ename%type,
v_sal emp.sal%type,
v_deptno emp.deptno%type);
v_emp emp_rec_type;
v_salresult varchar2(20);
v_dname varchar2(20);
begin
select empno, ename, sal, deptno into v_emp
from emp
where empno = a;
if (v_emp.v_sal < 2000) then v_salresult := 'low';
elsif (v_emp.v_sal<4000) then v_salresult := 'mid';
else v_salresult := 'high';
end if;
if v_emp.v_deptno = 10 then v_dname:='a';
elsif v_emp.v_deptno =20 then v_dname := 'b';
elsif v_emp.v_deptno =30 then v_dname := 'c';
else v_dname := 'd';
end if;
dbms_output.put_line(v_emp.v_empno||'/'||v_emp.v_ename||'/'||v_emp.v_sal||'/'||v_salresult||'/'||v_emp.v_deptno||'/'||v_dname);
exception
when no_data_found then
dbms_output.put_line('no data');
end;
exec proc08(7844);
--루프문
declare
n number(4):=0;
begin
loop
dbms_output.put_line(n);
n:=n+1;
if n>=5 then
exit;
end if;
end loop;
end;
--13579 출력
declare
v_number number(4):= 1;
begin
loop
dbms_output.put_line(v_number);
v_number:=v_number+2;
if v_number>=10 then
exit;
end if;
end loop;
end;
--for문 : for i in 1..5 loop
--declare
begin
for i in 1..5 loop
dbms_output.put_line(i);
end loop;
end;
--procedure + 루프
create or replace procedure getDept(dcode emp.deptno%type)
is
type emp_table_ename_type is TABLE OF emp.ename%type
INDEX BY PLS_INTEGER;
vemp_ename emp_table_ename_type;
begin
select ename bulk collect into vemp_ename
from emp
where deptno = dcode;
for i in vemp_ename.first .. vemp_ename.last loop
dbms_output.put_line(vemp_ename(i));
end loop;
end;
exec getDept(10);
--20번부서 사원의 모든 자료 출력
create or replace procedure getdept001(dcode emp.deptno%type)
is
Type emp_table_type is table of emp%rowtype
index by pls_integer;
v_emp emp_table_type;
begin
select * bulk collect into v_emp
from emp
where deptno=dcode;
for i in v_emp.first.. v_emp.last loop
dbms_output.put_line(v_emp(i).empno);
dbms_output.put_line(v_emp(i).ename);
dbms_output.put_line(v_emp(i).sal);
dbms_output.put_line(v_emp(i).deptno);
dbms_output.put_line(v_emp(i).hiredate);
end loop;
exception
when no_data_found then
dbms_output.put_line('no data');
end;
|
cs |
반응형
'2019 > ORACLE' 카테고리의 다른 글
SQL Index (0) | 2019.11.26 |
---|---|
SQL Sequence (0) | 2019.11.26 |
SQL 가상테이블 뷰(추후수정) (0) | 2019.11.26 |
SQL 제약조건(추후수정) (0) | 2019.11.26 |
SQL DDL (추후수정) (0) | 2019.11.26 |