Oracle PL/SQL Cursor
-------------------------------------------------------------------------------
출처: http://cafe.naver.com/litave.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=39
-------------------------------------------------------------------------------
<<== 목차==>>
1. 변수Scope
2. Cursor
2.1 암시적, 명시적커서
2.6 커서제어
2.8 cursor for loop
4. Query에Lock 걸기
-------------------------------------------------------------------------------
1. 변수Scope
Set serveroutput on
declare
x number := 10;
y number := 20;
z number;
begin
z := x + y;
dbms_output.put_line('결과(out) : ' || to_char(z));
declare
x number := 20;
begin
z := x + y;
dbms_output.put_line('결과(in) : ' || to_char(z));
end;
z := x + y;
dbms_output.put_line('결과(out2) : ' || to_char(z));
end;
/
2. Cursor(오라클에서명령을저장하기위해열어놓는메모리공간)
- 현장에서많이사용하는중요한변수
두가지종류: 암시적커서, 명시적커서
Select * from emp;의결과를가져오고싶으면메모리를2차원적공간으로OS에서받아올필요가있다.
받아온메모리공간에해당실행의결과를저장하고, 제일위에줄부터하나씩받아올수있다.
특정ROW를한줄씩받아와서전달하고, 모든ROW가전달되면할당된메모리를모두날려버린다.
2.1 암시적으로Cusor가동작하는방법
1) OPEN : 메모리를열고실행결과를메모리에저장하는단계(Result Set을얻는다.)
2) PATCH : 특정Row를보내는것
3) Close : 할당받은메모리공간은반납하는것
2.2 명시적커서는암시적방법을사용자가강제적으로하는것이다.
2.3 Cursor에는Result Set이있다.
2.4. RowNUM 패치된순서에따라번호를붙여놓은것.
2.5 커서의사용
declare
cursor c1 is <= 메모리영역을할당함
Select empno, ename, sal
from emp;
c2 c1%rowtype; <=c1의결과를같는모든Cursor의Row 타입을따라가라!!
begin
open c1;
Fatch c1 into c2; <= c2의row하나가c1에들어간것임//여기가여러번수행되어야함
close c1;
2.6 Cursor를제어하기위한방법
%RowCount : 현재Cursor가현재반납한패치의수를반납한다.(몇변패치했는지알려줌)
%Found : Result Set이패치할내용이있느냐?(찾았는가?)
- true(찾을것이있다.)/false로반환
%NotFound : Result Set이패치할내용이없느냐?
- 해당커서내에패치할내용이없느냐?
- true/false 로반환
%IsOpen : 해당커서가열려있느냐?
- true/false로반환
2.7 emp 테이블에사원이름과사원번호만화면에출력하기
Set serveroutput on
declare
cursor c1 is
Select empno, ename
from emp
where deptno = 10;
c2 c1%rowtype;
counter number;
begin
open c1;
dbms_output.put_line(' EMPNO ENAME');
dbms_output.put_line('---------- ----------');
loop
fetch c1 into c2;
exit when c1%NotFound;
dbms_output.put_line(' ' ||to_char(c2.empno) || ' ' || c2.ename);
end loop;
counter := c1%rowCount; --변수의개수출력
dbms_output.put_line(to_char(counter) || '의Row가선택되었습니다. ');
close c1;
end;
/
SQL> @cur1
EMPNO ENAME
---------- ----------
7782 CLARK
7839 KING
7934 MILLER
3의Row가선택되었습니다.
PL/SQL 처리가정상적으로완료되었습니다.
2.8 cursor for loop
커서를여러개선언한경우사용
Open되었는지일일이확인해가면서Open되지않았으면다시Open해주어하고, 중간중간에Close하는작업을하지않아도된다.
Loop도많이사용하므로이부분도대체할수있다.
For문과Cursor부분을연동해서사용할수있다.
Set serveroutput on
declare
cursor c1 is
Select empno, ename
from emp
where deptno = 10;
counter number;
begin
dbms_output.put_line('EMPNO ENAME');
dbms_output.put_line('----- ----------');
for c2 in c1 loop -- open fatch 자동으로일어남
dbms_output.put_line(to_char(c2.empno) || ' ' || c2.ename);
end loop; -- close 자동으로일어남
-- counter := c1%rowCount; --변수의개수출력
-- dbms_output.put_line(to_char(counter) || '의Row가선택되었습니다. ');
end;
/
SQL> @cur2
EMPNO ENAME
------- ----------
7782 CLARK
7839 KING
7934 MILLER
PL/SQL 처리가정상적으로완료되었습니다.
2.8 cursor for loop에서커서도정의하지않고포함시켜사용하기
Set serveroutput on
declare
begin
dbms_output.put_line('EMPNO ENAME');
dbms_output.put_line('------- ----------');
for c2 in (Select empno, ename from emp where deptno = 10) loop -- open fatch 자동으로일어남
dbms_output.put_line(to_char(c2.empno) || ' ' || c2.ename);
end loop; -- close 자동으로일어남
end;
/
SQL> @cur3
EMPNO ENAME
------- ----------
7782 CLARK
7839 KING
7934 MILLER
PL/SQL 처리가정상적으로완료되었습니다.
3. 부서별정보를비교해서사용할필요성을느꼈다.
100여개의부서를만들려면, 커서가100여개가선언되어야하고, 부서명도달라야한다.
Select empno, ename
from emp
where deptno = 10;
- 치환변수를사용해서커서를사용하는부분만변경해서사용하면편할것이다.
Set serveroutput on
declare
cursor c1(vno number) is -- 이렇게하면치환변수가된다.
Select empno, ename
from emp
where deptno = vno; -- vno : 치환변수
counter number;
begin
dbms_output.put_line('EMPNO ENAME');
dbms_output.put_line('----- ----------');
for c2 in c1(10) loop -- open fatch 자동으로일어남
dbms_output.put_line(to_char(c2.empno) || ' ' || c2.ename);
end loop; -- close 자동으로일어남
end;
/
- vno(치환변수)를이용하여, 코드를간단하게할수있다.
1) 상위세명만출력하기
Set serveroutput on
declare
cursor c1 is -- 이렇게하면치환변수가된다.
Select empno, ename, sal
from emp
Order by sal desc;
counter number;
c2 c1%rowtype;
begin
dbms_output.put_line('EMPNO ENAME');
dbms_output.put_line('----- ----------');
open c1;
for i in 1..3 loop
fetch c1 into c2;
dbms_output.put_line(to_char(c2.empno) || ' ' || c2.ename || ' ' || c2.sal);
end loop;
close c1;
end;
/
4. query에lock을거는방법
- for update 을가장뒤에적어주면다른사람이DML을사용할수없게할수있다.
- query 순간부터lock을걸라는표시
Select empno, ename
from emp
where deptno = 10
for update nowait;
- nowait 옵션은: 내가쿼리수행중에다른사람이접근하면경고메세지출력후다른작업을할수있도록해주는것.
5. 연습
EX8.1)
SQL> alter table emp
2 add(stars varchar2(100));
테이블이변경되었습니다.
Set verify off
Accept p_empno prompt 'please enter the employee number : '
declare
v_empno emp.empno%type := &p_empno;
v_asterisk emp.stars%type := null;
cursor emp_cursor is
Select empno, nvl(Round(sal/100), 0) sal
from emp
where empno = v_empno
for update;
begin
for emp_record in emp_cursor loop
for i in 1..emp_record.sal loop
v_asterisk := v_asterisk || '*';
end loop;
update emp
set stars = v_asterisk
where current of emp_cursor;
v_asterisk := null;
end loop;
commit;
end;
/
SQL> @cur_2;
please enter the employee number : 7844
PL/SQL 처리가정상적으로완료되었습니다.
SQL> @cur_2;
please enter the employee number : 7900
PL/SQL 처리가정상적으로완료되었습니다.
SQL> @cur_2;
please enter the employee number : 8000
PL/SQL 처리가정상적으로완료되었습니다.
SQL> Select empno,sal, stars from emp
2 where empno IN(7844, 7900,8000);
EMPNO SAL STARS
---------- --------- -----------------------------------------------------------------------------
7900 950 **********
7844 1500 ***************
Ex8.2)
SQL> create table top_dogs
2 (name varchar2(25),
3 salary number(11,2));
테이블이생성되었습니다.
Accept p_n prompt 'Please enter the number of top money_makers : '
declare
v_n number(3) := &p_n;
v_name s_emp.last_name%type;
v_salary s_emp.salary%type;
Cursor emp_cursor is
select last_name, salary from s_emp
where salary is not null
order by salary desc;
begin open emp_cursor;
fetch emp_cursor into v_name, v_salary;
while emp_cursor%rowcount <= v_n and emp_cursor%found loop
insert into top_dogs(name, salary)
values(v_name, v_salary);
fetch emp_cursor into v_name, v_salary;
end loop;
close emp_cursor;
commit;
end;
/
SQL> @cur_3;
Please enter the number of top money_ma
PL/SQL 처리가정상적으로완료되었습니다
SQL> select * from top_dogs;
NAME SALARY
------------------------- ----------
Velasquez 2500
Ropeburn 1550
Nguyen 1525
6. 종합문제3번
ENAME SAL SUMSAL
------- ----- -------
SCOTT 3000 3000
SMITH 1600 4600
KING 2000 6600
.
.
Set serveroutput on
declare
Cursor emp_cursor is
select ename, sal from emp;
v_sum number := 0;
v_cnt number := 0;
begin
dbms_output.put_line('ENAME SAL SUMSAL');
dbms_output.put_line('------ ---- -------');
for c1 in emp_cursor loop
v_sum := v_sum + c1.sal;
dbms_output.put_line(c1.ename || ' ' || to_char(c1.sal) || ' ' || to_char(v_sum));
v_cnt := v_cnt + 1;
end loop;
dbms_output.put_line(to_char(v_cnt)||' 행이출력되었습니다.');
end;
/