검색결과 리스트
ORACLE 에 해당되는 글 16건
- 2015.07.16 - Archive log mode 확인 및 모드 변경 1
- 2015.07.16 - Tablespace 사용률 관리 확인 스크립트 1
- 2015.07.16 - Redo Log 상태 확인 스크립트
- 2015.07.16 - SQL Prompt 변경하기. (ex. SYS@PROD)
- 2015.07.16 [ORA-00361: cannot remove last log member ]
- 2015.06.16 [ORA-00845] MEMORY_TARGET not supported on this system
- 2014.02.20 #시작전
- 2014.02.16 1. 프로시져 생성
- 2014.02.16 8장 .예외처리
- 2014.02.16 7장. 명시적 커서 사용
글
- Archive Log mode 확인
SYS@PROD>archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch Oldest online log sequence 24 Current log sequence 26 |
- Archive Log Mode로 변경
해당 모드로의 변경은 데이터베이스가 Mount 상태일 때 가능.
SYS@PROD>alter database archivelog; alter database archivelog * ERROR at line 1: ORA-01126: database must be mounted in this instance and not open in any instance SYS@PROD>shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SYS@PROD>startup mount ORACLE instance started.
Total System Global Area 417546240 bytes Fixed Size 2213936 bytes Variable Size 327157712 bytes Database Buffers 83886080 bytes Redo Buffers 4288512 bytes Database mounted.
Database altered.
SYS@PROD>archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch Oldest online log sequence 24 Next log sequence to archive 26 Current log sequence 26 |
- No Archive Log mode 변경
SYS@PROD>startup mount ORACLE instance started.
Total System Global Area 417546240 bytes Fixed Size 2213936 bytes Variable Size 327157712 bytes Database Buffers 83886080 bytes Redo Buffers 4288512 bytes Database mounted. SYS@PROD>alter database noarchivelog;
Database altered.
SYS@PROD>archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch Oldest online log sequence 24 Current log sequence 26 |
'ORACLE > TIP' 카테고리의 다른 글
- Tablespace 사용률 관리 확인 스크립트 (1) | 2015.07.16 |
---|---|
- Redo Log 상태 확인 스크립트 (0) | 2015.07.16 |
- SQL Prompt 변경하기. (ex. SYS@PROD) (0) | 2015.07.16 |
설정
트랙백
댓글
글
- 스크립트
set lines 1000; col file# for 999; col ts_name for a10; col total_blocks for 9999999; col used_blocks for 9999999; col pct_used for a10; col file_dir for a45 select distinct d.file_id file#, d.tablespace_name ts_name, d.bytes /1024 / 1024 MB, d.bytes / 8192 MB, sum(e.blocks) used_blocks, to_char(nvl(round(sum(e.blocks)/(d.bytes/8192),4),0)*100,'09.99')|| ' %' pct_used, d.file_name file_dir from dba_extents e, dba_data_files d where d.file_id = e.file_id group by d.file_id , d.tablespace_name , d.bytes, d.file_name
order by 1,2; |
- 조회결과
'ORACLE > TIP' 카테고리의 다른 글
- Archive log mode 확인 및 모드 변경 (1) | 2015.07.16 |
---|---|
- Redo Log 상태 확인 스크립트 (0) | 2015.07.16 |
- SQL Prompt 변경하기. (ex. SYS@PROD) (0) | 2015.07.16 |
설정
트랙백
댓글
글
- Redo Log 상태확인 스크립트.
set line 200 col group# for 999 col mb for 999 col member for a45 col sql# for a8 col arc for a5
select a.group#, a.member, b.bytes/1024/1024 MB, b.sequence# "SEQ#", b.status, b.archived "ARC" from v$logfile a, v$log b where a.group#=b.group# order by 1,2 / |
- 출력결과
'ORACLE > TIP' 카테고리의 다른 글
- Archive log mode 확인 및 모드 변경 (1) | 2015.07.16 |
---|---|
- Tablespace 사용률 관리 확인 스크립트 (1) | 2015.07.16 |
- SQL Prompt 변경하기. (ex. SYS@PROD) (0) | 2015.07.16 |
설정
트랙백
댓글
글
* 위와같이 접속 계정과 인스턴스명으로 구성하기위한 방법.
- 오라클 홈디렉토리 하위 $ORACLE_HOME/sql/admin 디렉토리로 이동 glogin.sql편집.
- sql 접속 후 set을 통해 수행해도되지만, 수행할 때마다 세팅해줘야하는 번거로움이 있으므로 위에 스크립트 편집.
- vi 편집기를 이용하여, 하단에 세줄 추가.
[oracle@edydr1p0-PROD dbhome_1]$ cd $ORACLE_HOME/sqlplus/admin [oracle@edydr1p0-PROD admin]$ ls -ltr total 20 -rw-r--r-- 1 oracle oinstall 2118 Feb 16 2003 pupbld.sql -rw-r--r-- 1 oracle oinstall 813 Mar 7 2006 plustrce.sql -rw-r--r-- 1 oracle oinstall 226 Aug 3 2009 libsqlplus.def drwxr-xr-x 2 oracle oinstall 4096 Jun 8 10:37 help -rw-r--r-- 1 oracle oinstall 421 Jul 13 15:25 glogin.sql |
저장 후 실행.
'ORACLE > TIP' 카테고리의 다른 글
- Archive log mode 확인 및 모드 변경 (1) | 2015.07.16 |
---|---|
- Tablespace 사용률 관리 확인 스크립트 (1) | 2015.07.16 |
- Redo Log 상태 확인 스크립트 (0) | 2015.07.16 |
설정
트랙백
댓글
글
* Log File Group의 그룹의 멤버가 1개일 때 멤버 삭제시 발생하는 에러.
* Redo Log Group의 멤버는 최소 1개.
SQL> @log
GROUP# MEMBER MB SEQ# STATUS ARC ------ --------------------------------------------- ---- ---------- ---------------- ----- 1 /u01/app/oracle/oradata/PROD/redo01a.log 100 17 INACTIVE NO 2 /u01/app/oracle/oradata/PROD/redo02a.log 100 18 INACTIVE NO 3 /u01/app/oracle/oradata/PROD/redo03a.log 100 20 CURRENT NO 4 /u01/app/oracle/oradata/PROD/redo04_a.log 5 19 INACTIVE NO
SQL> alter database drop logfile member 2 '/u01/app/oracle/oradata/PROD/redo04_a.log'; alter database drop logfile member * ERROR at line 1: ORA-00361: cannot remove last log member /u01/app/oracle/oradata/PROD/redo04_a.log for group 4
|
=> Log file group의 멤버는 최소 1개이기 때문에, member가 지워지지 않는다. 그룹을 지움으로써 삭제해야함.
SQL> alter database drop logfile group 4;
SQL> @log GROUP# MEMBER MB SEQ# STATUS ARC ------ --------------------------------------------- ---- ---------- ---------------- ----- 1 /u01/app/oracle/oradata/PROD/redo01a.log 100 17 INACTIVE NO 2 /u01/app/oracle/oradata/PROD/redo02a.log 100 18 INACTIVE NO 3 /u01/app/oracle/oradata/PROD/redo03a.log 100 20 CURRENT NO
|
'ORACLE > ORA-XXXXX' 카테고리의 다른 글
[ORA-00845] MEMORY_TARGET not supported on this system (0) | 2015.06.16 |
---|
설정
트랙백
댓글
글
- 데이터베이스를 새로 생성 할 때 데이터베이스를 기동하며 만난 에러
* 11g 기능 중 하나인 AMM 을 사용하기위해 memory_target, memory_max_size 세팅 후 DB기동할 때.
* 원인 마운트된 /dev/shm 크기가 memory_target 혹은 memory_max_size 보다 작은 경우.
[oracle@edydr1p0-PROD ~]$ sqlplus /nolog @create_db
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 15 11:14:50 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
File created.
ORA-00845: MEMORY_TARGET not supported on this system CREATE DATABASE PROD * ERROR at line 1: ORA-01034: ORACLE not available Process ID: 0 Session ID: 0 Serial number: 0
DOC>###################################################################### DOC>###################################################################### DOC> The following statement will cause an "ORA-01722: invalid number" DOC> error and terminate the SQLPLUS session if the user is not SYS. DOC> Disconnect and reconnect with AS SYSDBA. DOC>###################################################################### DOC>###################################################################### DOC># SELECT TO_NUMBER('MUST_BE_AS_SYSDBA') FROM DUAL * ERROR at line 1: ORA-01034: ORACLE not available Process ID: 0 Session ID: 0 Serial number: 0
Disconnected |
- 해결방법
* 마운트된 /dev/shm 용량 확인 [root@edydr1p0 ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda3 92G 48G 40G 55% / /dev/sda1 99M 26M 69M 27% /boot tmpfs 500M 156M 345M 32% /dev/shm .host:/ 465G 78G 387G 17% /mnt/hgfs * 용량증설을 위해 /dev/shm umount [root@edydr1p0 ~]# umount /dev/shm umount: /dev/shm: device is busy umount: /dev/shm: device is busy
** device busy 상태로, umount가 되지않는다. -l 옵션으로 umount, umount 확인. [root@edydr1p0 ~]# umount -l tmpfs [root@edydr1p0 ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda3 92G 48G 40G 55% / /dev/sda1 99M 26M 69M 27% /boot
.host:/ 465G 78G 387G 17% /mnt/hgfs * 용량 1기가로 증설 후 mount. [root@edydr1p0 ~]# mount -t tmpfs shmfs -o size=1g /dev/shm [root@edydr1p0 ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda3 92G 48G 40G 55% / /dev/sda1 99M 26M 69M 27% /boot .host:/ 465G 78G 387G 17% /mnt/hgfs
shmfs 1.0G 0 1.0G 0% /dev/shm *PROD DB 접속확인. SQL> startup force ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2213936 bytes Variable Size 314574800 bytes Database Buffers 96468992 bytes Redo Buffers 4288512 bytes Database mounted. Database opened. SQL> select instance_name from v$instance;
INSTANCE_NAME ---------------- PROD |
'ORACLE > ORA-XXXXX' 카테고리의 다른 글
[ORA-00361: cannot remove last log member ] (0) | 2015.07.16 |
---|
설정
트랙백
댓글
글
#. tnsnames.ora 클라이언트가 서버를 찾아갈때 필요한 정보를 저장한 파일
cat /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENERS_ORCL =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
)
ORCL2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
(INSTANCE_NAME = orcl2)
)
)
ORCL1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
(INSTANCE_NAME = orcl1)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
RAC환경의 서비스는 세션의 묶음.
업무별로 서비스를 묶고, 서비스의 이름을 가지고 들어오면 그 그룹안에 있는 인스턴스 중에 보다 여유로운 인스턴스에 연결
* 업무의 분할이 RAC 튜닝의 포인트.
SYS@orcl1>show parameter listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string
remote_listener string LISTENERS_ORCL
설정
트랙백
댓글
글
PROCEDURE : 미리 정리된 로직에 따라 필요한 명령을 절차적으로 실행하는 서브프로그램
리턴값을 가질 수도 있음.
FUNCTION : 미리 정리된 로직에 따라 필요한 명령을 절차적으로 실행하는 서브프로그램
리턴값이 반드시 있어야한다.
PACKAGE : 서브 프로그램의 묶음.
TRIGGER : 특정 이벤트 발생 시 자동으로 수행되는 PL/SQL 블록
# 치환 변수
BEGIN
UPDATE EMP
SET SAL = 4000
WHERE DEPTNO = &ID;
END ;
/
Enter value for id: 50
old 4: WHERE DEPTNO = &ID;
new 4: WHERE DEPTNO = 50;
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.25
& 치환 변수는 SQLplus/ SQLdeveloper 에서만 사용되는 변수. (SQLplus차원에서 바꾼다.)
SQLplus차원에서 바뀌어 서버에 전달된다.
BEGIN
UPDATE EMP
SET SAL = 4000
WHERE DEPTNO = 50;
END ;
/
이렇게 전달된다.
- 프로시져 생성.
CREATE PROCEDURE UPDATE_EMP --SPEC(PL/SQL 헤더) 시작
( P_SAL NUMBER, --파라미터
P_DEPTNO NUMBER) --SPEC 끝
AS --BODY 시작
V_EMP NUMBER ; --로컬변수
BEGIN
UPDATE EMP
SET SAL = P_SAL
WHERE DEPTNO = P_DEPTNO;
END ;
/ --Body 끝
- 프로시져 수정 시 OR REPLACE를 써서 덮어쓸 수 있다.
- 프로시져 수행시 파라미터값을 넣어서 쓰면된다.
-프로시져 구조를 보기 위해선 DESC UPDATE_EMP;
# 이름지정방식
CREATE OR REPLACE PROCEDURE raise_salary
( p_id IN employees.eployee_id%TYPE DEFAULT 100
p_percent IN NUMBER := 10)
~~~~~~~~
EXECUTE RAISE_SALARY (101,20) : 위치 지정 방식
기본값을 쓴다고 하더라도 앞에있는 파라미터부터 값을 다 주어야한다.
EXECUTE RAISE_SALARY (P_PERCENT =>20) : 이름 지정 방식
CREATE PROCEDURE P1
(
P1 NUMBER :=1
P2 NUMBER :=2
P3 NUMBER :=3
P4 NUMBER :=4)
IS
~~~~~~~~~~~~~~~~~~~~~~~
EXECUTE P1(100,200,300,400)
EXECUTE P1( 1,2,300) --위치 지정방식
EXECUTE P1 (P=>300) --이름 지정방식
EXECUTE P1 (100, P4=>500) --혼합지정방식
EXECUTE P1( P4=>500, 100) → ERROR
혼합 지정방식을 이용하면 이름지정방식이 먼저 나올경우 뒤는 계속 이름 지정방식을 써야한다.
설정
트랙백
댓글
글
8. 예외 처리
SQL> ALTER TABLE emp
ADD CONSTRAINT emp_ck CHECK ( sal > 0 ) ; --제약조건 추가하고 실습시작.
SQL>
SELECT empno, ename, sal
FROM emp
WHERE deptno = 10 ;
EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
--------------------------------
BEGIN
UPDATE emp
SET sal = 3000
WHERE empno = 7782 ; --정상 수행 가능한 문장.
UPDATE emp
SET sal = 0
WHERE empno = 7934 ; --제약조건 위배, Error문장.
END ;
/
ERROR at line 1:
ORA-02290: check constraint (ORA1.EMP_CK) violated
ORA-06512: at line 6
→ PL/ SQL 블럭중에서 하나라도 실패하는게 있다면, 정상 수행되는 문장도 수행되지 않는다.
SQL> SELECT empno, ename, sal
FROM emp
WHERE deptno = 10 ;
EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
#TX-0 OPEN
UPDATE emp
SET sal = 9999
WHERE empno = 7782 ;
BEGIN
UPDATE emp
SET sal = 3000
WHERE empno = 7782 ; --정상 수행 가능한 문장.
UPDATE emp
SET sal = 0
WHERE empno = 7934 ; --제약조건 위배, Error문장.
END ;
/
SQL> SELECT empno, ename, sal
FROM emp
WHERE deptno = 10 ;
EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 9999
7839 KING 5000
7934 MILLER 1300
→ PL/SQL블럭이 성공이 되든 실패가 되든 사용자가 COMMIT/ ROLLBACK 하지 않으면
트랜잭션은 종료되어있지 않다. update문은 성공하고 PL/SQL블럭은 실패한 결과를 보여준다.
Ex1) 모든 ERROR상황은 OTHERS를 통해 잡을 수 있다.
BEGIN
UPDATE EMP
SET SAL = 3000
WHERE EMPNO = 7782;
UPDATE EMP
SET SAL = 0
WHERE EMP = 7934 ;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR');
END;
/
EX2)
SET SERVEROUTPUT ON
BEGIN --암시적으로 세이브 포인트가 생긴다.
UPDATE EMP
SET SAL = 3000
WHERE EMPNO = 7782; --실행됨.
UPDATE EMP
SET SAL = 0
WHERE EMP = 7934 ; --실행안됨 Exception 절로넘어가서 필요한 처리기로간다.
UPDATE EMP
SET SAL = 3000
WHERE EMPNO = 7782; --시도조차 하지 않는다.
EXCEPTION
WHEN NO_DATA_FOUND THEN
DMBS_OUTPUT.PUT_LINE('NO DATA');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR'); --에러처리를 한다.
END;
/
#에러를 핸들링 하지 못한다면 해당 PL/SQL블럭은 취소가 된다.
EX)오라클 서버로부터 만들어진 암시적 에러
SET SERVEROUTPUT ON
DECLARE
V_SAL NUMBER ;
BEGIN
SELECT SAL INTO V_SAL
FROM EMP
WHERE EMPNO= 1234 ;
END ;
/
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4
Ex) SQLERRMM 을 이용한 에러 확인.
SQL> SET SERVEROUTPUT ON
BEGIN
UPDATE emp
SET sal = 3000
WHERE empno = 7782 ;
UPDATE emp
SET sal = 0
WHERE empno = 7934 ;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ( SQLERRM ) ;
END ;
/
ORA-02290: check constraint (ORA1.EMP_CK) violated
PL/SQL procedure successfully completed.
※ 미리 정의된 예외 처리
ex)
SET SERVEROUTPUT ON
emp_rec emp%ROWTYPE ;
BEGIN
SELECT * INTO emp_rec FROM emp WHERE ename = UPPER('&name') ;
DBMS_OUTPUT.PUT_LINE ( emp_rec.sal ) ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('NO_DATA_FOUND') ;
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE ('TOO_MANY_ROWS') ;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('OTHERS') ;
END ;
/
Enter value FOR name: RYU OLD 6: WHERE ename = UPPER('&name') ;
NEW 6: WHERE ename = UPPER('RYU') ;
NO_DATA_FOUND PL/SQL
PROCEDURE successfully completed.
※ 미리 정의되지 않은 예외 처리
ex)
SET SERVEROUTPUT ON
DECLARE
emp_rec emp%ROWTYPE ;
e_ck EXCEPTION ;
PRAGMA EXCEPTION_INIT (e_ck , -2290) ; --예외생성.
BEGIN
SELECT * INTO emp_rec FROM emp WHERE ename = UPPER('&name') ;
IF emp_rec.sal < 2000 THEN
UPDATE emp SET sal = &salary WHERE empno = emp_rec.empno ;
END IF ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('NO DATA') ;
WHEN E_CK THEN
DBMS_OUTPUT.PUT_LINE ('Invalid Salary') ;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM) ;
END ;
/
※ 사용자 정의 예외
SET SERVEROUTPUT ON
v_deptno NUMBER := 50 ;
v_name VARCHAR2(20) := 'Testing' ;
e_invalid_department EXCEPTION;
BEGIN
UPDATE dept SET dname = v_name WHERE deptno = v_deptno ;
IF SQL%NOTFOUND THEN
RAISE e_invalid_department ;
END IF;
COMMIT;
EXCEPTION
WHEN e_invalid_department THEN
DBMS_OUTPUT.PUT_LINE('No such department id.');
END;
/
No such department id. PL/SQL
PROCEDURE successfully completed.
※ RASE_APPLICATION_ERROR 사용
– 마치 오라클 서버에서 만들어진것 처럼 ERROR코드와 ERROR메세지가 나온다.
– 비정상종료를 강제로 유발시키고자할때 사용한다(암시적인 에러와 동일하다)
– 이전에 정상실행된 SQL문이 있더라도 ROLLBACK된다.
UPDATE dept SET dname = 'Testing' WHERE deptno = 50 ;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR ( -20001, 'No such department id.' ) ;
END IF;
END;
/
ERROR at line 1:
ORA-20001: No such department id.
ORA-06512: at line 7
ex2) 사용자 정의 에러문장은 보여주지만, 정상종료된다.
BEGIN
UPDATE dept SET dname = 'Testing' WHERE deptno = 50 ;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR ( -20001, 'No such department id.' ) ;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM) ;
END;
/
ORA-20001: No such department id. PL/SQL
PROCEDURE successfully completed.
Ex3)
DECLARE
emp_rec emp%ROWTYPE ;
BEGIN
SELECT * INTO emp_rec FROM emp WHERE deptno = 10 ;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
RAISE_APPLICATION_ERROR ( -20001, 'Too Many Rows', TRUE ) ;
--FALSE 로 변경하면, 원래에러메시지만 나온다.
END ;
/
ERROR at line 1: ORA-20001: Too Many Rows ORA-06512: at line 9 ORA-01422: exact
FETCH returns more than requested NUMBER OF rows
※ 예외 전달
ex1)
UPDATE emp SET sal = 7777 WHERE empno = 7788 ;
BEGIN
UPDATE emp SET sal = 9999 WHERE empno = 7566 ;
UPDATE emp SET sal = 0 WHERE empno = 7839 ;
UPDATE emp SET sal = 9999 WHERE empno = 7499 ;
END ; – ERROR1
UPDATE emp SET sal = 7777 WHERE empno = 7369 ;
END ;
/
ERROR1은 그 호출한 바깥으로 에러를 전달한다, 바깥쪽 블럭에도 EXCEPTION이 없기때문에 예외처리 되지 않으며, 어떠한 결과도 변경되지 않는다.
ERROR at line 1:
ORA-02290: check constraint (ORA1.EMP_CK) violated
ORA-06512: at line 11
SQL> SELECT empno, ename, sal
FROM emp
WHERE empno IN (7788, 7566, 7839, 7369, 7499) ;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7566 JONES 2975
7788 SCOTT 3000
7839 KING 5000
EX2)
SQL> SET SERVEROUTPUT ON
BEGIN
UPDATE emp SET sal = 7777 WHERE empno = 7788 ;
BEGIN
UPDATE emp SET sal = 9999 WHERE empno = 7566 ;
UPDATE emp SET sal = 0 WHERE empno = 7839 ; → Exception절로 이동한다.
UPDATE emp SET sal = 9999 WHERE empno = 7499 ; → 시도하지 않는다.
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM) ;
END ;
UPDATE emp SET sal = 7777 WHERE empno = 7369 ; → 예외가 처리되었으므로 실행된다.
END ;
/
ORA-02290: CHECK CONSTRAINT (ORA1.EMP_CK) violated PL/SQL
PROCEDURE successfully completed. SQL>
SELECT empno,
ename,
sal
FROM emp
WHERE empno IN (7788, 7566, 7839, 7369,7499) ;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 7777 7499 ALLEN 1600 7566 JONES 9999 7788 SCOTT 7777 7839 KING 5000 SQL> ROLLBACK ;
EX3)
SQL>
SET SERVEROUTPUT ON
BEGIN
UPDATE emp SET sal = 7777 WHERE empno = 7788 ;
BEGIN
UPDATE emp SET sal = 9999 WHERE empno = 7566 ;
UPDATE emp SET sal = 0 WHERE empno = 7839 ; → 에러 발생.
UPDATE emp SET sal = 9999 WHERE empno = 7499 ;
END ;
UPDATE emp SET sal = 7777 WHERE empno = 7369 ;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM) ; → 에러처리.
END ;
/
ORA-02290: check constraint (ORA1.EMP_CK) violated
PL/SQL procedure successfully completed.
SQL> SELECT empno, ename, sal
FROM emp
WHERE empno IN (7788, 7566, 7839, 7369,7499) ;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7566 JONES 9999
7788 SCOTT 7777
7839 KING 5000
#정리다시해야함.
중첩된 데이터의 피엘에스큐엘을 사용하면 정상 적으로 되다가 서브블록이 호출되면
자기불럭의 익셉션 절을 찾고, 핸들링했다면 메인블럭으로 돌아와서 나머지명령을 실행한다.
서브블록에서 에러를 핸들링 하지못했다면(익셉션절이 없거나, 있지만 처리기가 제대로 되지않을경우)
메인절의 익셉션 절로 넘어가고 그 사이에있는 것은 실행조차 되지 않는다.
메인절에서 처리기를 정상 처리해줬다면 서브블럭은 정상처리된다.
BEGIN
SQL 1
SQL 2 - ERROR EXCEPTION절로 넘어간다.
SQL 3 -실행조차 하지않는다.
EXCEPTION
END ;
Q> 만약 SQL 3번을 꼭 사용해야한다면. 중첩된 블록을 이용한다.
BEGIN
SQL 1
BEGIN
SQL 2
EXCEPTION
END ;
SQL 3
EXCEPTION
END ;
'ORACLE > PL/SQL' 카테고리의 다른 글
7장. 명시적 커서 사용 (0) | 2014.02.16 |
---|---|
6장 . 조합데이터 유형 (0) | 2014.02.16 |
5장. 제어 구조 작성 (0) | 2014.02.16 |
4장 PL/SQL 프로그램에서 SQL 문과 상호 작용 (0) | 2014.02.16 |
3장 실행문 작성 (0) | 2014.02.16 |
설정
트랙백
댓글
글
7. 명시적 커서 사용
ex1)많은 행을 가져오므로 ERROR
SQL>
DECLARE
emp_rec emp%ROWTYPE ;
BEGIN
SELECT * INTO emp_rec FROM emp WHERE deptno = 10 ;
DBMS_OUTPUT.PUT_LINE ( emp_rec.empno || ' ' || emp_rec.ename ) ;
END ;
/
ERROR at line 1: ORA-01422: exact
FETCH returns more than requested NUMBER OF rows ORA-06512: at line 4
EX2) 커서 생성을 통한 여러 행 불러오기.
CURSOR emp_cur --Cursor 생성.
IS
SELECT * FROM emp WHERE deptno = 10 ;
emp_rec emp%ROWTYPE ;
BEGIN
OPEN emp_cur ; --open이 되어야 문장이 실행된다.
FETCH emp_cur INTO emp_rec ; --첫번째 행만 FETCH.
DBMS_OUTPUT.PUT_LINE ( emp_rec.empno || ' ' || emp_rec.ename ) ;
FETCH emp_cur INTO emp_rec ; --그 다음행 선별하여 꺼내온다.
DBMS_OUTPUT.PUT_LINE ( emp_rec.empno || ' ' || emp_rec.ename ) ;
CLOSE emp_cur ; --메모리 할당 해제.
END ;
/
7782 CLARK 7839 KING PL/SQL
PROCEDURE successfully completed.
Ex3) LOOP를 통한 반복.
DECLARE
CURSOR emp_cur
IS
SELECT * FROM emp WHERE deptno = 10 ;
emp_rec emp%ROWTYPE ;
BEGIN
OPEN emp_cur ;
LOOP
FETCH emp_cur INTO emp_rec ;
EXIT
WHEN emp_cur%NOTFOUND ;
DBMS_OUTPUT.PUT_LINE ( emp_rec.empno || ' ' || emp_rec.ename ) ;
END LOOP ;
CLOSE emp_cur ;
END ;
/
7782 CLARK 7839 KING 7934 MILLER PL/SQL
PROCEDURE successfully completed.
Ex3) For Loop문과 결합된 Cursor : 암시적으로 OPEN / FETCH / CLOSE가 수행된다.
CURSOR emp_cur
IS
SELECT * FROM emp WHERE deptno = 10 ;
BEGIN
FOR emp_rec IN emp_cur --emp_rec선언 하지않아도 자동적으로 선언됨.
LOOP
DBMS_OUTPUT.PUT_LINE ( emp_rec.empno || ' ' || emp_rec.ename ) ;
END LOOP ;
END ;
/
7782 CLARK 7839 KING 7934 MILLER PL/SQL
PROCEDURE successfully completed.
Ex4)
FOR emp_rec IN
( SELECT * FROM emp WHERE deptno = 10
)
LOOP
DBMS_OUTPUT.PUT_LINE ( emp_rec.empno || ' ' || emp_rec.ename ) ;
END LOOP ;
END ;
/
7782 CLARK
7839 KING
7934 MILLER
PL/SQL procedure successfully completed.
Ex4) Parameter가 포함된 커서.
DECLARE
CURSOR emp_cur ( p_deptno NUMBER )
IS
SELECT * FROM emp WHERE deptno = p_deptno ;
BEGIN
FOR emp_rec IN emp_cur (10)
LOOP
DBMS_OUTPUT.PUT_LINE ( emp_rec.deptno || ' : ' || emp_rec.empno || ' ' || emp_rec.ename ) ;
END LOOP ;
FOR emp_rec IN emp_cur (20)
LOOP
DBMS_OUTPUT.PUT_LINE ( emp_rec.deptno || ' : ' || emp_rec.empno || ' ' || emp_rec.ename ) ;
END LOOP ;
END ;
/
SELECT empno, ename, sal, deptno FROM emp
WHERE deptno = 10 FOR UPDATE ; --검색된 행에대해 LOCK이 걸린다.
※ WHERE CURRENT OF 절 사용
Ex1)현재 내가 FETCH 해온 그 행에대해 직접적인 UPDATE를 한다.
DECLARE
CURSOR emp_cur
IS
SELECT * FROM emp WHERE deptno = 10 FOR UPDATE ;
BEGIN
FOR emp_rec IN emp_cur
LOOP
IF emp_rec.sal < 2000 THEN
UPDATE emp SET sal = sal * 1.1 WHERE CURRENT OF emp_cur ;
/* WHERE empno = emp_rec.empno */ --최선이아니다!.
END IF ;
END LOOP ;
END ;
/
전재 조건으로 반드시 해당 커서는 선언될 때 FOR UPDATE 절이 있어야한다.
LOCK이 많이 발생해 자주 쓸 순 없다.
* 해결방안. #음성178 확인50분 부터
DECLARE
CURSOR emp_cur
IS
SELECT a.*, rowid rid from a FROM emp a WHERE deptno = 10 FOR UPDATE ;
BEGIN
FOR emp_rec IN emp_cur
LOOP
IF emp_rec.sal < 2000 THEN
UPDATE emp SET sal = sal * 1.1 WHERE emp_rec.rid;
END IF ;
END LOOP ;
END ;
/
'ORACLE > PL/SQL' 카테고리의 다른 글
8장 .예외처리 (0) | 2014.02.16 |
---|---|
6장 . 조합데이터 유형 (0) | 2014.02.16 |
5장. 제어 구조 작성 (0) | 2014.02.16 |
4장 PL/SQL 프로그램에서 SQL 문과 상호 작용 (0) | 2014.02.16 |
3장 실행문 작성 (0) | 2014.02.16 |
RECENT COMMENT