BS Blog: cursor

레이블이 cursor인 게시물을 표시합니다. 모든 게시물 표시

2012-10-07

DBMS/오라클] 각종 세션/커서 관련 유용한 쿼리

각종 세션/커서 관련 유용한 쿼리 | Oracle DB 2006/03/31 10:11

http://blog.naver.com/yc7497/130003056914

아래 모니터링 쿼리문은 여기 저기 기웃거리며 동냥해서 얻은 지식들을 적어 놓은 것입니다.
순서없이 뒤죽박죽이어서 보시기 불편할 지도 모르겠네요.

사용법은 오라클이 설치된 서버에 텔넷을 이용하여 root계정으로
접속후에 top 혹은 topas 명령으로 프로세스들을 감시하면서 cpu를 많이 차지하는 프로세스 아이디등을 발견하여 조치하는 것입니다.

==================================================

/*********************************************************************************************
* 각 테이블스페이스 사용용량 체크하기
*********************************************************************************************/
SELECT a.tablespace_name,
a.total "Total(Mb)",
a.total - b.free "Used(Mb)",
nvl(b.free,0) "Free(Mb)",
round((a.total - nvl(b.free,0))*100/total,0) "Used(%)"
from ( select tablespace_name,
round((sum(bytes)/1024/1024),0) as total
from dba_data_files
group by tablespace_name) a,
( select tablespace_name,
round((sum(bytes)/1024/1024),0) as free
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
order by a.tablespace_name;

/*********************************************************************************************

/*오라클서버의 메모리에 관한 테이블*/
select * from v$sgastat

select pool, sum(bytes) "SIZE"
from v$sgastat
where pool = 'shared pool'
group by pool

/*cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기*/
select c.sql_text
,b.SID
, b.SERIAL#
,b.machine
,b.OSUSER
,b.logon_time --이 쿼리를 호출한 시간
from v$process a, v$session b, v$sqltext c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value
and a.spid = '675958'
order by c.PIECE

/*cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기*/
select c.sql_text
from v$process a, v$session b, v$sqltext c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value
and a.spid = '171'
order by c.PIECE

/*프로세스 아이디를 이용하여 쿼리문 알아내기*/
select c.sql_text
,b.SID
, b.SERIAL#
,b.machine
,b.OSUSER
,b.logon_time --이 쿼리를 호출한 시간
from v$process a, v$session b, v$sqltext c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value
and a.spid = '1708032' --1912870/
order by c.PIECE

/*세션 죽이기(SID,SERAIL#)*/
ALTER SYSTEM KILL SESSION '8,4093'

/*hash_value 가지고 SQL 문장 찾아내는 쿼리 */
select sql_text from v$sqltext where hash_value= 317853294

/*오라클 세션과 관련된 테이블*/
select count(*) from v$session where machine ='머신이름' and schemaname ='스키마이름'

/*인엑티브 상태의 세션들-로그온타입이 과도하게 지나도록 없어지지 않는것은 세션을 죽이도록 한다
단 machine이 머신이름 에 해당하는 것만 죽이도록 한다
prev_hash_value 로 해쉬밸류를 알아내어 쿼리문을 확인할 수 있다
*/
select sid,serial#,username,status,logon_time,prev_hash_value,machine
from v$session
where machine ='머신이름' and schemaname ='스키마이름' and status = 'INACTIVE'

select count(*) from v$session where machine ='머신이름' and schemaname ='스키마이름' and status = 'INACTIVE'

select count(*) from v$session where machine ='머신이름' and schemaname ='스키마이름' and status = 'KILLED'

SELECT COUNT(*) FROM V$SESSION WHERE MACHINE ='머신이름' AND SCHEMANAME ='스키마이름' AND STATUS = 'ACTIVE'

--현재 스키마이름 스키마로 실행중인(active) 프로세스를 알아내는 쿼리문
select SID, SERIAL#, SQL_HASH_VALUE, PREV_HASH_VALUE,ROW_WAIT_OBJ#, LOGON_TIME, MACHINE
from v$session
where schemaname ='스키마이름' and status = 'ACTIVE'

--위에서 알아내 hash_value 값을 이용하여 해당 프로세스가 실행중인 쿼리문 알아내기
select * from v$sqltext where hash_value = 2626426688-
order by piece

select * from v$session
where machine ='머신이름' and schemaname ='스키마이름' and status = 'ACTIVE' and ROW_WAIT_OBJ# > 0

/******************************************************************************************/
현재 커서 수 확인하는 Query
/******************************************************************************************/

SELECT sid, count(sid) cursor
FROM V$OPEN_CURSOR
WHERE user_name = '유저이름'
GROUP BY sid
ORDER BY cursor DESC

SELECT sql_text, count(sid) cnt
FROM v$OPEN_CURSOR
GROUP BY sql_text
ORDER BY cnt DESC

116, 15217

select sql_text from v$sqltext
where hash_value= 3252988466 --1389452958
order by piece

select * from v$session_wait

select sid, serial#, username, taddr, used_ublk, used_urec
from v$transaction t, v$session s
where t.addr = s.taddr;

select * from sys.v_$open_cursor

select user_name, sql_text, count(*) cnt from sys.v_$open_cursor
where user_name = '유저이름' and sid = 114
group by user_name, sql_text
order by cnt desc

alter system kill session '27,127'

--ALTER SYSTEM KILL SESSION '45, 3977'
--이런식으로 통계정보를 생성하신 후에

analyze table emp compute statistics;

alter table customers pctfree 5 ;

select * from dba_tables
where table_name = 'RP_PART_MST'

/*******************************************************************************
* LOCK 관련
*******************************************************************************/

--V$LOCK 을 사용한 잠금 경합 모니터링
SELECT s.username, s.sid, s.serial#, s.logon_time,
DECODE(l.type, 'TM', 'TABLE LOCK',
'TX', 'ROW LOCK',
NULL) "LOCK LEVEL",
o.owner, o.object_name, o.object_type
FROM v$session s, v$lock l, dba_objects o
WHERE s.sid = l.sid
AND o.object_id = l.id1
AND s.username IS NOT NULL

--락이 걸린 세션 자세히 알아보기
select a.sid, a.serial#,a.username,a.process,b.object_name,
decode(c.lmode,2,'RS',3,'RX',4,'S',5,'SRX',8,'X','NO') "TABLE LOCK",
decode (a.command,2,'INSERT',3,'SELECT',6,'UPDATE',7,'DELETE',12,'DROP TABLE',26,'LOCK TABLE','UNknown') "SQL",
decode(a.lockwait, NULL,'NO wait','Wait') "STATUS"
from v$session a,dba_objects b, v$lock c
where a.sid=c.sid and b.object_id=c.id1
and c.type='TM'

--락이 걸린 세션 간단히 알아보기
select a.sid, a.serial#, b.type, c.object_name, a.program, a.lockwait,
a.logon_time, a.process, a.osuser, a.terminal
from v$session a, v$lock b, dba_objects c
where a.sid = b.sid
and b.id1 = c.object_id
and b.type = 'TM';

select a.sid, a.serial#, a.username, a.process, b.object_name
from v$session a , dba_objects b, v$lock c
where a.sid=c.sid and b.object_id = c.id1
and c.type = 'TM'

--락이 걸린 세션을 찾아 내어 세션을 죽이려고 해도 죽지 않는 경우
--아래 쿼리문으로 OS단의 PROCESS ID를 찾아내어 OS에서 죽인다
--kill -9 프로세스아이디
select substr(s.username,1,11) "ORACLE USER", p.pid "PROCESS ID",
s.sid "SESSION ID", s.serial#, osuser "OS USER",
p.spid "PROC SPID",s.process "SESS SPID", s.lockwait "LOCK WAIT"
from v$process p, v$session s, v$access a
where a.sid=s.sid and
p.addr=s.paddr and
s.username != 'SYS'

--위 쿼리문의 결과가 있다면 락이 걸린 세션이 있다는것이므로 아래의 쿼리문으로 세션을 죽인다
ALTER SYSTEM KILL SESSION '11,39061'

/**********************************************************************************************/
select *
from v$session
where sid in (80,100)

SELECT l.SESSION_ID,
LPAD(' ', DECODE(l.xidusn,0,3,0))
||l.oracle_username "User Name",
o.owner, o.object_name, o.object_type
FROM v$locked_object l, dba_objects o
WHERE l.object_id = o.object_id
ORDER by o.object_id, 1 desc

--아래 table 은 존재하지 않네...

SELECT sw.username "WAITING_USER", bu.username "LOCKING_USER",
dw.lock_type, dw.mode_held, dw.mode_requested,
dw.waiting_session, dw.holding_session
FROM dba_waiters dw, v$session sw, v$session bu
WHERE dw.waiting_session = sw.sid
ANd dw.holding_session = bu.sid

SELECT s.username, s.sid, s.serial#
FROM dba_blockers db, v$session s
WHERE db.holding_session = s.sid

SELECT vo.session_id,do.object_name, do.owner, do.object_type,do.owner, vo.xidusn, vo.locked_mode
FROM v$locked_object vo , dba_objects do
WHERE vo.object_id = do.object_id

SELECT do.object_name, do.owner, do.object_type,do.owner, vo.xidusn, vo.session_id, vo.locked_mode
FROM v$locked_object vo , dba_objects do
WHERE vo.object_id = do.object_id

Select *
FROM
all_col_comments
WHERE
table_name = '테이블이름'

/************************************************
alter session으로 죽지않는 프로세스 죽이기
1.oracle이 설치된 서버에 텔넷으로 root로 접속한다
2.su -오라클계정
3.sqlplus '/as sysdba''
4.connect system/sys
5.ALTER SYSTEM KILL SESSION '137,1723'
**************************************************/

DBMS/오라클]오라클_커서_사용법

원본 출처 :: http://netme.kr/bbs/board.php?bo_table=sub_2011&wr_id=23

1. 커서의 정의

커서란 SQL Plus에서 사용자가 실행한 SQL문의 단위를 의미합니다.
오라클렝서 수행한 모든 쿼리문은 커서 단위로 처리합니다.

PL/SQL의 SQL문처럼 하나의 결과를 리턴하는 경우 커서 없이도 SQL문의 실행결과가
암시적으로 커서에 저장되므로 이를 암시적 커서라고 합니다.

SQL문을 수행한 후에 결과로 얻어지는 행이 여러 개일 경우에는 암시적인 커서에 정보를
저장할 수 없기에 에러가 발생합니다. 이럴 경우에는 반드시 명시적인 커서를 사용해야 합니다.

명시적인 커서는 PL/SQL의 레코드(RECORD)와 PL/SQL의 테이블(TABLE)을 결합한 것으로서
프로그램 언어의 구조체 배열과 유사합니다.

[커서의 사용]

1) 커서를 선언한다.
CURSOR cur_name

2) 커서를 오픈한다.
OPEN cur_name

3) 커서에 조회한 결과를 인출해 지정한다.
FECTCH cur_name ...

4) 커서를 닫는다
CLOSE cur_name

2. 20번 부서에 근무하는 사원의 정보를 출력하는 예제

ed cur01

SET SERVEROUTPUT ON
declare
   vempno   NUMBER(4);
   vename   VARCHAR2(20);
   vsal   NUMBER(7, 2);

   CURSOR   C1
   IS
   select empno, ename, sal
   from emp
   where deptno=20;

begin
   OPEN C1;

   dbms_output.put_line('empno   ename   sal');

   LOOP
      FETCH C1 INTO vempno, vename, vsal;
      EXIT WHEN C1%NOTFOUND;

      dbms_output.put_line(to_char(vempno)||'   '||vename||'   '||to_char(vsal));
   END LOOP;

end;
/

저장하고 실행합니다.

@cur01
empno   ename   sal
7369   SMITH   800
...
...
...
...
...

PL/SQL procedure successfully completed.

3. OPEN-FETCH-CLOSE가 없이 커서 처리

ed cur02

SET SERVEROUTPUT ON
declare
   vemp   emp%ROWTYPE;

   CURSOR   C1
   IS
   select empno, ename, sal
   from emp
   where deptno=20;

begin
   dbms_output.put_line('empno   ename   sal');

   FOR   vemp   IN   C1   LOOP
      EXIT WHEN C1%NOTFOUND;

      dbms_output.put_line(to_char(vemp.empno)||'   '||vemp.ename||'   '||to_char(vemp.sal));
   END LOOP;

end;
/

정장하고 실행합니다.

@cur02
empno   ename   sal
7369   SMITH   800
...
...
...
...
...

PL/SQL procedure successfully completed.

4. 커서의 상태

%NOTFOUND   커서 영역의 자료가 모두 FETCH 됬는가를 알려줌
%FOUND      커서 영역에 FETCH가 되지 않은 자료가 있는가를 알려줌
%ISOPEN      커서가 OPEN된 상태인가를 알려줌
%ROWCOUNT   FETCH된 RECORD가 몇 개 있는지 알려줌

cur02 예제를 수정합니다.

ed cur02

SET SERVEROUTPUT ON
declare
   vemp   emp%ROWTYPE;

   CURSOR   C1
   IS
   select empno, ename, sal
   from emp
   where deptno=20;

begin
   dbms_output.put_line('empno   ename   sal   record count');

   FOR   vemp   IN   C1   LOOP
      EXIT WHEN C1%NOTFOUND;

      dbms_output.put_line(to_char(vemp.empno)||'   '||vemp.ename||'   '||to_char(vemp.sal)||'   '||C1%ROWCOUNT);
   END LOOP;

end;
/

저장하고 실행합니다.

@cur02

결과는 record count 추가되었습니다.

5. 커서를 활용한 실용 예제
1) 급여 총합을 구하는 예제
ed cur03

SET SERVEROUTPUT ON
declare
   tot   NUMBER := 0;

   CURSOR   emp_cursor
   IS
   select ename, sal
   from emp;

begin
   dbms_output.put_line('name   sal');
   dbms_output.put_line('------------------------------------------');

   FOR cur_var IN emp_cursor LOOP
      tot := tot + cur_var.sal;
      dbms_output.put_line(cur_var.ename);
      dbms_output.put_line('-   '||cur_var.sal);
   END LOOP;

   dbms_output.put_line('------------------------------------------');
   dbms_output.put_line('-   '||tot);
end;
/

저장하고 실행합니다

@cur03

2) 사원별 급여 현황을 그래포로 표현
ed cur04

SET SERVEROUTPUT ON
declare
   CURSOR   emp_cursor
   IS
   select ename, sal
   from emp
   order by sal desc;

   star   varchar2(100);
   cnt   number := 0;

begin
   dbms_output.put_line('   sal of emp');
   dbms_output.put_line('------------------------------------------');

   FOR cur_var IN emp_cursor LOOP
      star := NULL;
      cnt := round(cur_var.sal/100, 0);

      for i in 1.. cnt loop
         star := star||'*';
      end loop;

   dbms_output.put_line(cur_var.ename);
   dbms_output.put_line('-   '||star||'('||cur_var.sal||')');
   END LOOP;
end;
/

저장하고 실행합니다

@cur04

2012-10-06

DBMS/오라클]명시적_커서(Explicit_Cursor)_사용법

출처 : http://www.dbcore.net/~corenet/education/speciallec/sqlplsql/chap15-3.html

명시적 커서와 묵시적 커서에 대한 설명 문서 : 03_SQL_CURSOR.pdf

커서

묵시적 커서(Implicit Cursor)

명시적 커서(Explicit Cursor)

커서(Cursor) 명시적 커서(Explicit Cursor)

정의

● 명시적 커서는 하나 이상의 행을 액세스하는 SELECT 명령문을 처리하기 위해서사용
● 선언부에서 명시적으로 커서 선언
● 명시적 커서는 블록의 실행부에서 커서 조작 명령을 통해 처리
● 명령을 이용하여 질의에 의해 반환된 행들을 한번에 한 행씩 처리
● 커서 선언은 선언부에서, 커서 OPEN, FETCH, CLOSE 정의는 실행부에서 정의

처리 과정

① 커서 선언(DECLARE CURSOR절) : 커서는 공유풀 내의 전용 SQL 영역에 생성
② 커서 열기(OPEN절) : 커서를 활성화하여 커서와 연관된 SELECT 명령문 실행
③ 데이타 추출(FETCH절) : SELECT명령문에 의해 검색된 행 중에서 커서가 가리키는 행을 액세스
④ 커서 닫기(CLOSE절) : 오픈된 커서를 비활성화

커서 선언

● 커서를 선언하고, 커서와 관련된 SELECT 명령문 정의
● SELECT 문장에는 INTO절을 사용하지 않음
● INTO절은 실행부의 FETCH 명령문에서 사용
● 커서 선언 부분에서 지역 변수나 전역 변수 사용 가능
● 커서 정의에서 사용되는 지역 변수나 전역 변수는 반드시 커서 선언 전에 정의 필요
● 변수는 컬럼명과 다르게 선언

☞ 사용법

DECLARE CURSOR cursor IS
select_statement;

☞ 사용예

DECLARE
CURSOR Cursor_Emp IS
SELECT ename, deptno
FROM emp
WHERE sal > 2000;

커서 열기 (OPEN)

● 공유풀의 라이브러리 캐쉬에 설정된 커서 영역을 오픈
● 선언된 커서와 연관된 SELECT 명령문을 실행하여 active set을 구성
● 커서는 검색된 행으로 구성된 active set 중에서 첫 번째 행을 가르킴
● 실행과정
  - 구문분석(Parsing) : 문장이나 의미, 권한 등이 제대로 되어 있는지 검사
  - 바인딩(Binding) : 변수의 값을 할당
  - 실행(Excute) : 라이브러리 캐쉬에 할당된 커서 영역을 오픈하고 active set구성

☞ 사용법

OPEN cursor;

☞ 사용예

OPEN Cursor_Emp;

데이타 추출 (FETCH)

● 현재 커서가 가르키는 행의 값을 변수로 할당하는 과정
● 커서 선언시 사용한 SELECT 명령문의 컬럼 개수와 데이타타입이 FECTH ∼ INTO절의 변수 개수, 데이타타입과
동일해야 함

☞ 사용법

FETCH cursor INTO variable_list;

● variable_list
  - 현재 검색된 결과 행을 저장하기 위한 변수 리스트
  - variable_list 변수의 개수는 커서 선언부에서 정의한 SELECT문장의 컬럼 개수와 동일
  - 커서 안에서 PL/SQL 레코드 타입 설정이 가능하며, variable_list에도 사용 가능

☞ 사용예

FETCH Cursor_Emp INTO v_ename, v_deptno;

커서 닫기 (CLOSE)

● 현재 오픈된 커서를 닫음
● 커서 오픈(OPEN) 명령문에 의해 할당된 커서 영역 반환
● 현재 커서를 닫은 후에 다시 해당 커서를 오픈 가능

☞ 사용법

CLOSE cursor_name;

☞ 사용예

CLOSE Cursor_Emp;

▶ 명시적 커서 수행 방법

커서 속성

● 커서 상태를 검사하기 위해 제공되는 네 가지 속성
● SQL 명령문에서 커서 속성을 직접 참조할 수 없음

구 분	타입	내 용
%ROWCOUNT	Number	·현재까지 FETCH된 행의 갯수
%FOUND	Boolean	·FETCH할 행이 존재할 경우 값은 TRUE
%NOTFOUND	Boolean	·FETCH할 올 행이 없을 경우 값은 TRUE
%ISOPEN	Boolean	·커서가 오픈되어 있을 경우 값은 TRUE

☞ 사용예 : %ROWCOUNT

OPEN Cursor_Emp;

LOOP
FETCH Cursor_Emp INTO v_emp, v_deptno;
EXIT WHEN Cursor_Emp%ROWCOUNT >= 5;
END LOOP;

CLOSE Cursor_Emp;

☞ 사용예 : %NOTFOUND

OPEN Cursor_Emp;

LOOP
FETCH Cursor_Emp INTO v_emp, v_deptno;
EXIT WHEN Cursor_Emp%NOTFOUND;
END LOOP;

CLOSE Cursor_Emp;

☞ 사용예 : %ISOPEN

IF NOT Cursor_Emp%ISOPEN THEN
OPEN Cursor_Emp;
END IF;

☞ 커서를 사용한 프로시져 생성 실습

☞ 프로시져 생성

SQL> CREATE OR REPLACE PROCEDURE cur_exam (v_sal IN number)
IS
v_ename emp.ename%TYPE;
v_dname dept.dname%TYPE;

CURSOR Cursor_Emp IS
SELECT ename, dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.sal > v_sal;

BEGIN
OPEN Cursor_Emp;
IF NOT Cursor_Emp%ISOPEN THEN
OPEN Cursor_Emp;
END IF;
DBMS_OUTPUT.PUT_LINE('다음은 4월 승진자 명단입니다.');

LOOP
FETCH Cursor_Emp INTO v_ename, v_dname;
EXIT WHEN Cursor_Emp%ROWCOUNT >= 5
OR Cursor_Emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_dname||' 부서의 '||v_ename||'씨');
END LOOP;

CLOSE Cursor_Emp;
DBMS_OUTPUT.PUT_LINE('발령일은 4월 1일 입니다.');
DBMS_OUTPUT.PUT_LINE(' *^^* 만우절 행사였습니다.');

END cur_exam;

Procedure created.

☞ DBMS_OUTPUT.PUT_LINE을 사용하기위해 SERVEROUTPUT 환경변수를 ON상태로 설정

SQL> SET SERVEROUTPUT ON

☞ 실행 및 출력결과

SQL> EXEC cur_exam(2500);

다음은 4월 승진자 명단입니다.
ACCOUNTING 부서의 KING씨
RESEARCH 부서의 KEBIN씨
RESEARCH 부서의 SCOTT씨
RESEARCH 부서의 LEE씨
발령일은 4월 1일 입니다.
*^^* 만우절 행사였습니다.
PL/SQL procedure successfully completed.

CURSOR FOR 루프절

● PL/SQL의 선언부에 이미 명시적 커서가 선언되어 있어야 함
● FOR 루프가 초기화될 때 내부적으로 커서가 오픈
● FOR 루프가 반복될 때마다 커서가 가르키는 행을 액세스
● 루프는 마지막 행을 추출한 후 자동적으로 FOR 루프를 종료
● OPEN, FETCH, CLOSE의 기능을 하나의 FOR 루프에서 실행 가능

☞ 사용법

FOR record_name IN cursor_name LOOP
statement1;
statement2;
..................
END LOOP;

● record_name : 내부적으로 선언한 레코드
● cursor_name : 선언부에서 이미 선언한 커서명

☞ 사용예

☞ 프로시져 생성

SQL> CREATE OR REPLACE PROCEDURE cur_for(dnum IN NUMBER)
IS
CURSOR emp_cur IS
SELECT sal, comm
FROM emp
WHERE deptno = dnum;
total NUMBER := 0;
high_paid NUMBER := 0;
high_comm NUMBER := 0;

BEGIN
FOR emp_rec IN emp_cur LOOP
emp_rec.comm := NVL(emp_rec.comm,0);
total := total + emp_rec.sal + emp_rec.comm;
IF emp_rec.sal > 2000 THEN
high_paid := high_paid + 1;
END IF;
IF emp_rec.comm > emp_rec.sal THEN
high_comm := high_comm + 1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('1. 전체 : ' ||total);
DBMS_OUTPUT.PUT_LINE('2. $2000이상 받는 사원수 : '||high_paid);
DBMS_OUTPUT.PUT_LINE('3. 월급보다 커미션이 더 많은 사원수 : '||high_comm);

END cur_for;

Procedure created.

☞ 현재 데이타 확인

SQL> select sal,comm from emp where deptno=30;

SAL	COMM
--------	---------
1600	300
1250	1400
2850
1500	0
950

☞ 실행 및 출력결과

SQL> EXEC cur_for(30);

1. 전체 : 9850
2. $2000이상 받는 사원수 : 1
3. 월급보다 커미션이 더 많은 사원수 : 1
PL/SQL procedure successfully completed.

FOR UPDATE 절

● FOR UPDATE절을 사용한 트랜잭션이 완료되기 전까지 테이블에 액세스하지 못하도록 명시적으로 락을 생성하는
방법
● update나 delete 명령 수행 전에 대상 행에 대해 락을 생성하고자 할 때 사용

☞ 사용법

SELECT ...
FROM ...
FOR UPDATE [OF column_reference] [NOWAIT];

● 질의한 결과 행이 다른 세션에 의해 이미 락이 걸려있다면, 즉시 오라클 에러를 반환
● NOWAIT명령을 사용하지 않으면 다른 세션에 의한 락이 풀릴 때까지 기다림

☞ 사용예 :update, delete문장에 의해 사용될 deptno가 10인 사원의 행을 질의하면서 해당 행에 대해 락을 생성

DECLARE
CURSOR emp_cursor IS
SELECT empno, ename, sal
FROM emp
WHERE deptno = 10
FOR UPDATE OF sal NOWAIT;

WHERE CURRENT OF절

● 커서를 이용하여 가장 최근에 검색된 행을 삭제 또는 수정하는 경우에 사용
● 커서를 선언할 때 FOR UPDATE절이 반드시 있어야 함
● WHERE CURRENT OF절에 의해 지정된 커서는 가장 최근에 FETCH한 행을 가르킴

☞ 사용예

☞ 프로시져 생성

SQL> CREATE OR REPLACE PROCEDURE cur_where
IS
CURSOR emp_cur IS
SELECT ename, rowid
FROM   emp
WHERE deptno = 30
FOR UPDATE;
v_ename   emp.ename%TYPE;
v_rowid   ROWID;

BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur INTO v_ename,v_rowid;
EXIT WHEN emp_cur%NOTFOUND;

UPDATE emp
SET sal = sal*2
WHERE CURRENT OF emp_cur;

-- 보통은 UPDATE emp SET sal = sal*2 WHERE rowid = v_rowid;
-- 이나 UPDATE emp SET sal = sal*2 WHERE ename = v_ename; 로
-- 표현함.
END LOOP;
CLOSE emp_cur;
COMMIT;
END;

Procedure created.

매개변수가 있는 커서 선언

● 커서 선언시 매개변수 사용 가능
● 매개변수의 데이타타입은 스칼라 변수와 동일하고 크기 지정은 불가능
● 매개변수는 커서와 관련된 SELECT 명령문에서 참조하기 위해 사용
● 커서가 오픈될 때 커서에 매개변수 값을 전달하여 질의 내에서 사용

☞ 사용법

CURSOR cursor_name
[parameter datatype, ...)]
IS
select_statement;

☞ 사용예

CURSOR emp_cur(dnum number) IS
SELECT sal, comm
FROM emp
WHERE deptno = dnum;
BEGIN
FOR emp_rec IN emp_cur(30) LOOP
...

- 매개변수가 있는 커서를 선언하면 매개변수의 값이 바뀔때 마다 질의 조건이 바뀌므로 여러 개의 커서를
선언하는 것과 동일한 효과를 얻을 수 있음

커서

묵시적 커서(Implicit Cursor)

명시적 커서(Explicit Cursor)