declare

 cursor emp_cur(d_no number) is

   select empno, ename, job, sal

   from emp

   where deptno = d_no;


begin

 for rec_emp_cur in emp_cur(10) loop --> for 문의 커서 변수에 파라미터 입력


   p(rec_emp_cur.job||', '||rec_emp_cur.ename||'('||rec_emp_cur.empno

     ||')''s salary is '||rec_emp_cur.sal);


   p(emp_cur%rowcount||' rows processed');


 end loop;


end;

/


↓ ↓ ↓ 


declare

 cursor emp_cur(d_no number) is

   select empno, ename, job, sal

   from emp

   where deptno = d_no;


begin

 for rec_emp_cur in emp_cur(&dept_no) loop --> for 문의 커서 변수에 파라미터 입력


   p(rec_emp_cur.job||', '||rec_emp_cur.ename||'('||rec_emp_cur.empno

     ||')''s salary is '||rec_emp_cur.sal);


   p(emp_cur%rowcount||' rows processed');


 end loop;


end;

/

↓ ↓ ↓ 


create or replace procedure emp_cur_proc (dept_no number) is

 cursor emp_cur(d_no number) is

   select empno, ename, job, sal

   from emp

   where deptno = d_no;


begin

 for rec_emp_cur in emp_cur(dept_no) loop


   p(rec_emp_cur.job||', '||rec_emp_cur.ename||'('||rec_emp_cur.empno

     ||')''s salary is '||rec_emp_cur.sal);


   p(emp_cur%rowcount||' rows processed');


 end loop;


end;

/


exec emp_cur_proc(10)

exec emp_cur_proc(30)


  - parameters: name, data type, but NO size


  - 파라미터 값에 따라 active set이 달라짐



declare

 cursor emp_cur(d_no number) is

   select empno, ename, job, sal

   from emp

   where deptno = d_no;


 rec_emp_cur emp_cur%rowtype;


begin

 open emp_cur(10); --> cursor open 시 파라미터 입력

 loop

   fetch emp_cur into rec_emp_cur;


   exit when emp_cur%notfound;


   p(rec_emp_cur.job||', '||rec_emp_cur.ename||'('||rec_emp_cur.empno

     ||')''s salary is '||rec_emp_cur.sal);

 end loop;


 close emp_cur;

end;

/

(Implicit) Cursor For Loop using Subquery

==> attribute를 사용할 수 없음


begin

 for rec_emp_cur in (select empno, ename, job, sal

     from emp

     where deptno = 10)

 loop

   p(rec_emp_cur.job||', '||rec_emp_cur.ename||'('||rec_emp_cur.empno

     ||')''s salary is '||rec_emp_cur.sal);

--    p(%rowcount||' rows processed'); --> 에러: ORA-06550: 줄 8, 열6:PLS-00103: 심볼 "%"를 만났습니다 다음 중 하나가 기대될 때:

--    p(sql%rowcount||' rows processed'); --> 에러 없이 컴파일되나 sql%rowcount 값이 NULL 값이 됩니다.

 end loop;

end;

/


  * NOT need: OPEN, FETCH, CLOSE, and variables to hold the fetched data

==> FOR LOOP 문에서 OPEN, FETCH, CLOSE가 자동적으로 실행됨


  * implicit record ( ex. rec_emp_cur )

==> cursor for loop를 벗어나면 접근 불가



declare

 cursor emp_cur is

   select empno, ename, job, sal*1.3 as sal

   from emp

   where deptno = 10;

begin

 for rec_emp_cur in emp_cur loop --> implicit record


   p(rec_emp_cur.job||', '||rec_emp_cur.ename||'('||rec_emp_cur.empno

     ||')''s salary is '||rec_emp_cur.sal);


   p(emp_cur%rowcount||' rows processed');


 end loop;


--  p(emp_cur%rowcount||' rows processed');


--  p(rec_emp_cur.job||', '||rec_emp_cur.ename||'('||rec_emp_cur.empno

--    ||')''s salary is '||rec_emp_cur.sal);

end;

/


       declare

 cursor emp_cur is

   select empno, ename, job, sal

   from emp

   where deptno = 10;


 rec_emp_cur emp_cur%rowtype;


begin

 open emp_cur;

 loop

   fetch emp_cur into rec_emp_cur;


   exit when emp_cur%notfound;


   p(rec_emp_cur.job||', '||rec_emp_cur.ename||'('||rec_emp_cur.empno

     ||')''s salary is '||rec_emp_cur.sal);

 end loop;


 close emp_cur;

end;

/



  문제) 급여가 2000 이상인 사원의 사원번호, 사원이름, 부서이름, 직급, 급여를

출력하는 pl/sql 문을 작성하세요.

단, 커서를 사용하셔야 합니다.

emp, dept 테이블을 사용하세요.



declare

 커서 선언;

 레코드 변수 선언;


begin

 커서 오픈;

 루프

   커서 페치하여 레코드 변수에 저장;

   커서 애트리뷰트를 이용한 탈출 조건;

   화면 출력;

 루프 종료;


end;

/


↓ ↓ ↓ 


declare

 cursor c_ed is

   select e.empno, e.ename, d.dname, e.job, e.sal

   from emp e join dept d

   on (e.deptno = d.deptno)

   where e.sal >= 2000;


 r_ed c_ed%rowtype;


begin

 open c_ed;


 loop

   fetch c_ed into r_ed;

   

   exit when c_ed%notfound;


   p('부서이름: ' || r_ed.dname);

   p('사원번호: ' || r_ed.empno || ', 사원이름: ' || r_ed.ename);

   p('직    급: ' || r_ed.job || ', 급    여: ' || r_ed.sal);

   p('----------------------------------------');

 end loop;


 close c_ed;


end;

/




  * Loop를 이용한 Fetch: while 보다는 basic(건수를 알 수 없을 때)이나 

cursor for loop(건수를 알 때)을 이용


declare

 cursor emp_cur is

   select empno, ename, job, sal

   from emp

   where deptno = 10;


 v_empno emp.empno%type;

 v_ename emp.ename%type;

 v_job   emp.job%type;

 v_sal   emp.sal%type;


--  w_switch boolean := true; --> 최초 진입을 위한 변수


begin

 open emp_cur;

 fetch emp_cur into v_empno, v_ename, v_job, v_sal;

 while (emp_cur%found) loop --> cursor attribute를 이용한 while 조건 검증 까다로움

--  while (w_switch) loop

   fetch emp_cur into

     v_empno, v_ename, v_job, v_sal;

   p(v_job||', '||v_ename||'('||v_empno||')''s salary is '||v_sal);


/*    if emp_cur%notfound then

     w_switch := false;

   end if;

*/

 end loop;


 p(emp_cur%rowcount||' rows processed');


 close emp_cur;

end;

/


↓ ↓ ↓ 


declare

 cursor emp_cur is

   select empno, ename, job, sal

   from emp

   where deptno = 10;


 v_empno emp.empno%type;

 v_ename emp.ename%type;

 v_job   emp.job%type;

 v_sal   emp.sal%type;

b_switch boolean := true; --> basic loop나 for loop를 쓰면 불필요한 변수


begin

 open emp_cur;


 while (b_switch) loop --> cursor attribute를 이용한 while 조건 검증 까다로움

   fetch emp_cur into

     v_empno, v_ename, v_job, v_sal;


   if emp_cur%notfound then

     b_switch := false;

   else

     p(v_job||', '||v_ename||'('||v_empno||')''s salary is '||v_sal);

   end if;


 end loop;


 p(emp_cur%rowcount||' rows processed');


 close emp_cur;

end;

/



declare

 cursor emp_cur is

   select empno, ename, job, sal

   from emp

   where deptno = 10;


 v_empno emp.empno%type;

 v_ename emp.ename%type;

 v_job   emp.job%type;

 v_sal   emp.sal%type;

begin

 open emp_cur;


 fetch emp_cur into v_empno, v_ename, v_job, v_sal;


 while (emp_cur%found) loop --> cursor attribute를 이용한 while 조건 검증 까다로움

   p(v_job||', '||v_ename||'('||v_empno||')''s salary is '||v_sal);


   fetch emp_cur into

     v_empno, v_ename, v_job, v_sal;


 end loop;


 p(emp_cur%rowcount||' rows processed');


 close emp_cur;

end;

/



↓ ↓ ↓ 


declare

 cursor emp_cur is

   select empno, ename, job, sal

   from emp

   where deptno = 10;


 v_empno emp.empno%type;

 v_ename emp.ename%type;

 v_job   emp.job%type;

 v_sal   emp.sal%type;


begin

 open emp_cur;

 loop

   fetch emp_cur into

     v_empno, v_ename, v_job, v_sal;


   exit when emp_cur%notfound;


   p(v_job||', '||v_ename||'('||v_empno||')''s salary is '||v_sal);

 end loop;


 p(emp_cur%rowcount||' rows processed');


 close emp_cur;

end;

/


  * 커서변수이름%ISOPEN

  * 커서변수이름%NOTFOUND

  * 커서변수이름%FOUND

  * 커서변수이름%ROWCOUNT


    - SQL 문을 이용해서 cursor attribute를 직접 참조할 수 없음


select my_cur%notfound from dual; --> (X)


'Oracle > PL/SQL' 카테고리의 다른 글

15일차 # 7-15: Cursor For Loop  (0) 2012.04.24
15일차 # 7-14: Cursor  (0) 2012.04.24
15일차 # 7-5: Controlling Explicit Cursors  (0) 2012.04.24
15일차 # 7 - Explicit Cursors  (0) 2012.04.24
15일차 # nested table 초기화  (0) 2012.04.24

  * OPEN

1) EXECUTE : SQL statements

2) IDENTIFY: Active Set --> 준비하는 단계, row 추출은 FETCH에서

3) POSITION: Active Set의 첫번째 행으로 포인터 이동


  * FETCH ... INTO 

1) RETRIEVE: 포인터가 가리키는 Active Set의 행 획득 ==> INTO 절이 필요한 이유

2) ADVANCE : Active Set의 다음 행으로 포인터 이동


  * CLOSE

1) DISABLE, RELEASE, UNDEFINE: 커서 관련 각종 정리 작업


'Oracle > PL/SQL' 카테고리의 다른 글

15일차 # 7-14: Cursor  (0) 2012.04.24
15일차 # 7-17: Explicit Cursor Attributes  (0) 2012.04.24
15일차 # 7 - Explicit Cursors  (0) 2012.04.24
15일차 # nested table 초기화  (0) 2012.04.24
15일차 # 6-26: 추가 BULK COLLECT  (0) 2012.04.24

  # Cursor


  - a kind of pointer to the private SQL area created in memory by server

  - the private SQL area stores the information on processing SQL statement



  * implicit cursor (by PL/SQL) --> 커서 선언 불필요

- SQL cursor

- implicit cursor for loop or cursor for loop using subquery


  * explicit cursor (by programmer) --> 선언부에서 커서 변수 선언

- cursor

- cursor with parameters --> 파라미터 값에 따라 active set의 내용이 달라짐

- cursor for loop

- cursor for loop with parameters --> 파라미터 값에 따라 active set의 내용이 달라짐

- for update cursor --> update 또는 delete 문의 current of 절


  * Cursor For Loop: 둘 이상의 결과를 갖는 SQL 문을 간편하게 처리하는데 유용

- 커서 레코드 자동 선언

- 커서 제어(OPEN, FETCH, CLOSE) 자동 처리




# Active Set


  - 커서의 SQL 문(보통 select 문)이 실행된 결과 행의 집합

        declare

 type job_type is table of emp.job%type;

 emp_job job_type;

begin

 emp_job := job_type('학생', '회사원', '변호사', '교사');


 p(emp_job(1));

 p(emp_job(4));

end;

/



declare

 type job_type is table of emp.job%type;

 emp_job job_type := job_type('학생', '회사원', '변호사', '교사');

begin

 p(emp_job(1));

 p(emp_job(4));

end;

/

        drop table t1 purge;

create table t1 as select * from emp where 1=2;


   ------------------------------


declare

 TYPE emp_record_table_type IS TABLE OF emp%rowtype

   INDEX BY PLS_INTEGER;


 n emp_record_table_type;

begin

 select * BULK COLLECT into n

 from emp

 order by sal desc;

    

 for i in n.first .. n.last loop

   insert into t1 values n(i);

   p(i||' elements');

 end loop;


 p(n(4).empno||' '||n(4).ename);

end;

/


   ------------------------------


select * from t1;

'Oracle > PL/SQL' 카테고리의 다른 글

15일차 # 7 - Explicit Cursors  (0) 2012.04.24
15일차 # nested table 초기화  (0) 2012.04.24
15일차 # 6-20: Using Collection Methods  (0) 2012.04.24
15일차 # 6-15: PL/SQL Collections  (0) 2012.04.24
15일차 # 6-14: Update with records  (0) 2012.04.24

참고 : http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/collections.htm#sthref1123


  * element 중간에 값이 빠졌을 경우 해결 방법



declare

 TYPE number_table_type IS TABLE OF number

   INDEX BY PLS_INTEGER;


 n number_table_type;

begin


 n(8) := 800;

 n(3) := 300;


 p(n.count);

 p(n.first);

 p(n.last);


 for i in n.first .. n.last loop

   p(n(i));

 end loop;

end;

/

↓ ↓ ↓ 


declare

 TYPE number_table_type IS TABLE OF number

   INDEX BY PLS_INTEGER;


 n number_table_type;

 cnt number;

begin


 n(8) := 800;

 n(3) := 300;

 n(6) := 600;


 cnt := n.first;


 while cnt is not null loop --> not null 조건에 유의

   p(n(cnt));

   cnt := n.next(cnt); --> collection method NEXT

 end loop;


end;

/

'Oracle > PL/SQL' 카테고리의 다른 글

15일차 # nested table 초기화  (0) 2012.04.24
15일차 # 6-26: 추가 BULK COLLECT  (0) 2012.04.24
15일차 # 6-15: PL/SQL Collections  (0) 2012.04.24
15일차 # 6-14: Update with records  (0) 2012.04.24
15일차 # 6-13: Insert with records  (0) 2012.04.24

  - 액세스시 색인/첨자(키 값)이 필요


  - 장점

1) simplify the code

2) loop through each element with ease by using collection built-in methods

3) improved performance - cache static data



  * Associative arrays (index-by tables)

: let you look up elements using arbitrary numbers and strings for subscript values.

          hash tables in other programming languages.

- sets of (unique index key, value) pairs

   1) 상대적으로 작은 룩업 테이블에 적합 <== 메모리에 생성되므로

   2) 크기를 미리 알 수 없는 집합 데이터에 적합 <== 크기 제한 없으므로

   3) 색인값이 유연(flexible) <== 색인값: 음수, 양수, 문자열 가능하고 nonsequential도 가능하므로



  * Nested tables

   1) hold an arbitrary number of elements.

   2) use sequential numbers as subscripts.

   3) can be sparse

   4) 데이터베이스 테이블에 저장 가능



  * Varrays (short for variable-size arrays)

   1) hold a fixed number of elements ==> 개수를 미리 알고 있을시 적합

   2) the number of elements can be changed at runtime.

   3) use sequential numbers as subscripts.

   4) 데이터베이스 테이블에 저장 가능



  * Choosing Which PL/SQL Collection Types to Use (When to use What?)

==> http://goo.gl/0dDcf


    - Varray

. Use to preserve ordered list

. Use when working with a fixed set, with a known number of entries

. Use when you need to store in the database and operate on the Collection as a whole


    - Nested Table

. Use when working with an unbounded list that needs to increase dynamically

. Use when you need to store in the database and operate on elements individually



    - Associative Array

. Use when there is no need to store the Collection in the database 

. Its speed and indexing flexibility make it ideal for internal application use

        ...

update retired_emps

set ROW = emp_rec <-- ROW 키워드에 주목

where empno = &emp_no;

...



----------------------


set serveroutput on


select * from e1

where rownum = 1;


declare

 emp_rec e1%rowtype;

begin

 select * into emp_rec

 from e1

 where rownum = 1;


 emp_rec.comm := 20;

 emp_rec.deptno := 10;


 update e1

 set ROW = emp_rec

 where empno = emp_rec.empno;


 commit;


end;

/


select * from e1;


'Oracle > PL/SQL' 카테고리의 다른 글

15일차 # 6-20: Using Collection Methods  (0) 2012.04.24
15일차 # 6-15: PL/SQL Collections  (0) 2012.04.24
15일차 # 6-13: Insert with records  (0) 2012.04.24
15일차 # 6-9: %ROWTYPE attribute  (0) 2012.04.24
15일차 # 6-7: Record type  (0) 2012.04.24

        drop table e1 purge;

create table e1

 as select * from emp where 1 = 2;


declare

 emp_rec e1%rowtype; --> %rowtype을 이용한 레코드 변수 선언

begin

 select * into emp_rec

 from emp

 where empno = 7788;


 emp_rec.sal := emp_rec.sal * 1.1;


 insert into e1 values emp_rec; --> VALUES 절 이후에 레코드 변수가 사용되었음

end;

/


select * from emp where empno = 7788;

select * from e1;

'Oracle > PL/SQL' 카테고리의 다른 글

15일차 # 6-15: PL/SQL Collections  (0) 2012.04.24
15일차 # 6-14: Update with records  (0) 2012.04.24
15일차 # 6-9: %ROWTYPE attribute  (0) 2012.04.24
15일차 # 6-7: Record type  (0) 2012.04.24
15일차 # 6-3: Composite Data Types  (0) 2012.04.24

  * Advantages

- select * 문에서 유용

- 컬럼 갯수와 타입이 실행시 변경될 시 유용

(데이터베이스 컬럼의 갯수와 데이터 타입을 알 필요 없음)

- 대상: 테이블, 뷰, 커서, 커서 변수



create or replace procedure emp_record_proc(a number)

is 

 TYPE emp_rec_type IS RECORD

        (empno emp.empno%type, --┐

 sal   emp.sal%type, --┼> %ROWTYPE으로

 job   emp.job%type); --┘


 emp_rec emp_rec_type;

begin

 select empno, sal, job into emp_rec

 from (select empno, sal, job

       from emp 

       where deptno = a

       order by sal desc) e 

 where rownum = 1;


 p(emp_rec.empno);

 p(emp_rec.sal);

 p(emp_rec.job);


end;

/    



exec emp_record_proc(10)

exec emp_record_proc(30)


↓ ↓ ↓ 


create or replace view v1001 

as select empno, sal, job from emp;


create or replace procedure emp_rowtype_proc(a number)

is 

 emp_rec v1001%rowtype;

begin

 select empno, sal, job into emp_rec

 from (select empno, sal, job

       from emp 

       where deptno = a

       order by sal desc) e 

 where rownum = 1;


 p(emp_rec.empno);

 p(emp_rec.sal);

 p(emp_rec.job);


end;

/    



exec emp_rowtype_proc(10)

exec emp_rowtype_proc(30)


   ------------------------------


declare

 emp_rec emp%rowtype;

begin

 select * into emp_rec

 from emp

 where empno = 7788;


 p(emp_rec.empno||' '||emp_rec.ename||' '||emp_rec.job||' '||emp_rec.sal);

end;

/


'Oracle > PL/SQL' 카테고리의 다른 글

15일차 # 6-14: Update with records  (0) 2012.04.24
15일차 # 6-13: Insert with records  (0) 2012.04.24
15일차 # 6-7: Record type  (0) 2012.04.24
15일차 # 6-3: Composite Data Types  (0) 2012.04.24
15일차 # 확인문제  (0) 2012.04.24

+ Recent posts