본문 바로가기
2019/ORACLE

SQL PLSQL Procedure

by SOLYI 2019. 11. 26.

 

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(40);
 
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