BS Blog: DBMS

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

2012-10-07

DBMS/오라클] RemoteOperationException_에러_해결방법_리눅스

RemoteOperationException: ERROR: NMO not setuid-root (Unix-only)
Hi All,

I was trying using Oracle Enterprise Manager to start the database/Listener and when I tried to start the database/listener through OEM i got the following error

RemoteOperationException: ERROR: NMO not setuid-root (Unix-only)

Later on I discovered that the error was since the root.sh script was not run properly.

Then i tried to rerun the root.sh which is located in $ORACLE_HOME/root.sh the

above error disapperad. It was because the improper permissions on oracle binaries in particular nmo,nmhs,nmb binaries.

Later on after running the root.sh the following permission were giving the nmo,nmhs and nmb binaries

-rwsr-s— 1 root oinstall 23744 Oct 15 16:17 nmo
-rwsr-x— 1 root oinstall 46480 Oct 15 16:17 nmhs
-rwsr-s— 1 root oinstall 17256 Oct 15 16:17 nmb

Here ’s’ stands for that SUID bit is set for this binaries

결론 root 로 로그인

$ORACLE_HOME/root.sh 실행

DBMS/오라클]sqlplus 에서 vi 편집기 사용하기

sqlplus 에서 vi 편집기 사용하기

단계1 오라클 사용자로 터미날 열어서 cd

단계2 ls -a

-- .bash_porfile 파일이 보일것이다. 이것을 조작한다.

단계3 geidt .bash_profile

--> export EDITOR=vi 추가 후 저장

단계 4 로그 아웃 후 다시 접속

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

DBMS/오라클]오라클 DB추가 후 사용자 설정

출처 : http://wyseburn.tistory.com/165

오라클 DB추가 후 사용자 설정
1. /oracle/11g/bin/dbca 를 사용하여 DB,SID를 추가(SID는 8자 이내로 설정)

2. 리스너 설정(listener.ora, tnsnames.ora)

----------- listener.ora -----------------

LISTENER =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = 아이피)
(PORT = 1521)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = 기존SID)
(ORACLE_HOME = /oracle/11g)
)
(SID_DESC =
(SID_NAME = 추가SID)
(ORACLE_HOME = /oracle/11g)
)
)

----------- tnsnames.ora -----------------

기존SID =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = 아이피)
(PORT = 1521)
)
(CONNECT_DATA =
(SID =기존SID)
)
)
추가SID =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = 아이피)
(PORT = 1521)
)
(CONNECT_DATA =
(SID = 추가SID)
)
)

3. 추가한 DB에 sysdba 로 접속하여 사용자 생성

sqlplus / as sysdba 를 하면 환경변수 ORACLE_SID 에 설정된 SID(DB)로 접속되므로

export ORACLE_SID=추가된SID 를 실행하고 sqlplus / as sysdba를 사용하여 접속한다.

4. 테이블스 페이스 추가

CREATE TABLESPACE 스페이스명 DATAFILE '/oracle/oradata/추가SID/스페이스명.dbf' size 500M;

5. 사용자 추가

CREATE USER 사용자명 IDENTIFIED BY 비밀번호 DEFAULT TABLESPACE 스페이스명 TEMPORARY TABLESPACE TEMP QUOTA 100M ON 스페이스명;

6. 권한설정

GRANT CONNECT, RESOURCE TO 사용자명

-----------------------------------------------------------------------------------------------
오라클 비밀번호 만료 관련 1. 해당 username 의 프로파일 확인

SELECT username,profile FROM dba_users;

2. 프로파일의 비밀번호 기간 제한 없앰

ALTER PROFILE 프로파일명 LIMIT password_life_time UNLIMITED;

2012-10-06

DBMS/오라클] 프로 시저 대신 패키지를 써야 하는 이유

출처 : http://mt1716.egloos.com/9176977

프로 시저 대신 패키지를 써야 하는 이유

오라클 패키지의 마법을 풀어봅시다 - 프로시저 대신 패키지를 써야 하는 이유

Advanced Oracle 2007/07/29 02:00

많은 오라클 전문가들이 프로시저대신 패키지를 사용할 것을 권장한다. 특히 패키지를 만든 오라클 사람들이...
하지만, 왜 그럴까? 많은 사람들이 이 사실을 모르고, 심지어 프로시저를 사용하면 되는데 패키지가 무슨 필요? 라며 잘못된 견해를 전파한다.
프로시저가 아닌패키지를 사용해야 하는 이유는, 결론부터 말하면 패키지의 향상된 의존성(Dependency)관리때문이다.
아래 간단한 패키지와 프로시저가 있다. 이 둘의 기능(하는 일)은 완전히 동일하다. 다만 하나는 패키지로 구현되어 있고, 다른 하나의 프로시저로 구현되어 있을 뿐이다.
-- 패키지
create or replace package pkgtest as
procedure pkgtest_proc(v_id int);
end pkgtest;
/
create or replace package body pkgtest as
procedure pkgtest_proc(v_id int)
is
v_name varchar2(1);
begin
select name into v_name from pkgtest_table;
end;
end pkgtest;
/
-- 프로시저
create or replace procedure nopkg_proc(v_id int)
is
v_name varchar2(1);
begin
select name into v_name from pkgtest_table;
end;
/
여기서 주목해야 할 것은 pkgtest 패키지와 nopkg_proc 프로시저가 모두 pkgtest_table에 대해 의존성(Dependency)를 가지고 있다는 사실이다. 여기에서 간혹 심각한 문제가 발생한다.
Pkgtest_table에 대해 DDL을 수행하게 되면 이 테이블을 참조하고 있는 모든 객체에 대해 무효화(Invalidation)가 수행된다.아래 스크립트를 보자
-- pkgtest_table에 대해 의존성을 가지는 패키지와 프로시저가 Valid 상태이다.
SQL> select object_name,object_type, status
from dba_objects where object_name in ('PKGTEST', 'NOPKG_PROC');

OBJECT_NAM OBJECT_TYPE STATUS
---------- -------------------- --------------
NOPKG_PROCPROCEDURE VALID
PKGTEST PACKAGE VALID
PKGTEST PACKAGE BODYVALID
-- Pkgtest_table에 대해 Alter를 수행하면?
SQL> alter table pkgtest_table add name2 varchar(1);

OBJECT_NAMOBJECT_TYPE STATUS
------------------------------ --------------
NOPKG_PROCPROCEDURE INVALID
PKGTEST PACKAGE VALID
PKGTESTPACKAGE BODYINVALID
위의 결과에서 다음과 같은 재밌는 사실을 발견할 수 있다.

NOPKG_PROC 프로시저는 기대했던 대로 INVALID 상태가 되었음을 알 수 있다.
PKGTEST 패키지는 좀 특이한다.
- PKGTEST 패키지 바디(body)기대했던 대로 INVALID 상태가 되었음을 알 수 있다.
- 반면 PKGTEST 패키지 자체는 놀랍게도 여전히 VALID 상태이다.

패 키지의 이러한 특징을 가리켜 흔히 "패키지는 의존성 체인을 깬다"라는 표현을 사용한다. 위의 예를 들면 pkgtest_table이 변경됨으로써 pkgtest 패키지가 무효화될 위기임에도 불구하고 중간에 패키지 바디라는 중간 객체만이 무효화되고 패키지 자체는 무효화되지 않는다.
이 패키지의 특정, 즉 의존성 체인을 깨는 특징이 왜 그렇게 중요할까? 그 이유는 하드 파싱과 관련이 있다.
만일 수십 개의 프로시저가 이 pkgtest_table에 대해 의존성을 가지는 상태에서 운영상의 이유로 pkgtest_table을 Alter했다고 가정해보자. 이수십 개의 프로시저가 모두 INVALID 상태가 될 것이고, 따라서 이 프로시저들을 수행하는 모든 쿼리는 재컴파일이 이루어져 한다. 붐~! 아마 library cache pin이라는 이름의 대기 현상의 증가하면서 자칫 시스템 장애를 불러일으킬 수 있다.
하지만 프로시저가 아닌 패키지로 되어 있었다면? 다행히 패키지 자체는 여전히 VALID 상태이이 때문에 이 패키지들을 사용하는 모든 쿼리 또한 재사용이 가능하 다. 실행 시점에 패키지 바디만 리컴파일해주면 된다. 옙!! 여러분은 방금 시스템 장애로부터 사장님을 구한 셈이다.
이것을 증명하기 위해 pkgtest_table을 Alter한 후, 다음과 같이 패키지와 프로시저의 수행 결과를 SQL Trace를 이용해 분석해보자.
-- 패키지를 참고하는 쿼리 문장은 하드 파싱을 수행하지 않는다.(즉, Library cache Miss가 발생하지 않으며 리컴파일또한 수행하지 않는다)
BEGIN pkgtest.pkgtest_proc(1); END;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.01 0 0 1 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.01 0 0 1 1
Misses in library cache during parse: 0 <-- 여기를 주목하세요!!Optimizer mode: ALL_ROWS
Parsing user id: 55
-- 하지만 프리시저를 사용하는 하드파싱을 수행한다.(즉, Library cache miss가 발생하고 리컴파일을 수행한다)
BEGIN nopkg_proc(1); END;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 1 0
Execute 1 0.00 0.00 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.01 0 0 1 1
Misses in library cache during parse: 1 <-- 여기를 주목. 모든 악의 근원!!!Optimizer mode: ALL_ROWS
Parsing user id: 55
왜 오라클이 굳이 패키지라는 복잡한 개념을 구현했는지 이해가 되는가? 프로그래밍의 간편함과 더불어 쿼리 재사용성 증가라는 탁월한 효과를 얻을 수 있기 때문이다.
이제 입질이 슬슬 오는가...?
PS)
SQL Server 2005 에서는 Statement Level의 Recompile을 지원함으로써 오라클의 패키지와 동일한 효과를 제공한다. 자세한 내용은 나중에...
하드파싱(리컴파일)에 의한 library cache pin 대기 현상에 대해서는 아래 필자의 책 참고~~

DBMS/오라클]오라클_힌트_모음

출처 : http://stillrabbit.blogspot.com/2007/12/1.html

1.ALL_ROWS

     Goal : Best Throughput

     용도 : 전체 RESOURCE 소비를 최소화 시키기 위한 힌트.

            Cost-Based 접근방식.



     예   : SELECT /*+ALL_ROWS */ EMPNO,ENAME

            FROM   EMP

            WHERE  EMPNO = 7655;



  2.FIRST_ROWS

     Goal : Best Response Time

     용도 : 조건에 맞는 첫번째 row를 리턴하기 위한 Resource

            소비를 최소화 시키기위한 힌트.

            Cost-Based 접근방식.

     특징 : - Index Scan 이 가능하다면 Optimizer가 Full Table Scan 대신

              Index Scan을 선택한다.

            - Index Scan 이 가능하다면 Optimizer가 Sort-Merge 보다

              Nested Loop 을 선택한다.

            - Order By절에의해 Index Scan 이 가능하다면,

              Sort과정을 피하기위해 Index Scan을 선택한다.

            - Delete/Update Block 에서는 무시된다.    

            - 다음을 포함한 Select 문에서도 제외된다.

              집합연산자 (Union,Intersect,Minus,Union All)

              Group By

              For UpDate

              Group 함수

              Distinct  



     예   : SELECT /*+FIRST_ROWS */ EMPNO,ENAME

            FROM   EMP

            WHERE  EMPNO = 7655;



  3.CHOOSE

     Goal : Acess되는 테이블에 통계치 존재여부에 따라

            Optimizer로 하여금 Rule-Based Approach와 Cost-Based Approach

            중 하나를 선택할수 있게 한다.

     용도 : Data Dictionary가 해당테이블에 대해 통계정보를 가지고 있다면

            Optimizer는 Cost-Based Approach를 선택하고,

            그렇지 않다면 Rule-Based Approach를 선택한다.



     예   : SELECT /*+CHOOSE */ EMPNO,ENAME

            FROM   EMP

            WHERE  EMPNO = 7655;



  4.RULE

     용도 : Rule-Based 최적화를 사용하기위해.



     예   : SELECT /*+RULE */ EMPNO,ENAME

            FROM   EMP

            WHERE  EMPNO = 7655;



B. Access Methods 로써의 Hints



  1.FULL

     용도 : 해당테이블의 Full Table Scan을 유도.

   

     예   : SELECT /*+FULL(EMP) */ EMPNO,ENAME

            FROM   EMP

            WHERE  EMPNO = 7655;

          * 테이블 Alias 가 있는경우는 Alias사용.

             Schema Name은 사용안함(From 에 SCOTT.EMP 라고 기술해도 hint에는 EMP사용).



  2.ROWID

     용도 : 지정된 테이블의 ROWID를 이용한 Scan 유도



  3.CLUSTER

     용도 : 지정된 테이블Access에 Cluster Scan 유도.

            Cluster된 Objects에만 적용가능.

   

     예   : SELECT /*+CLUSTER(EMP) */ ENAME,DEPTNO

            FROM   EMP,DEPT

            WHERE  DEPTNO = 10

            AND    EMP.DEPTNO = DEPT.DEPTNO;



  4.HASH

     용도 : 지정된 테이블Access에 HASH Scan 유도.

            /*+HASH(table) */



  5.HASH_AJ

     용도 : NOT IN SubQuery 를 HASH anti-join으로 변형

            /*+HASH_AJ */



  6.HASH_SJ

     용도 : correlated Exists SubQuery 를 HASH semi-join으로 변형

            /*+HASH_SJ */



  7.INDEX

     용도 : 지정된 테이블Access에 Index Scan 유도.

          * 하나의 index만 지정되면 optimizer는 해당index를 이용.

          * 여러개의 인덱스가 지정되면  optimizer가 각 index의

            scan시 cost를 분석 한 후 최소비용이 드는 index사용.

            경우에 따라 optimizer는 여러 index를 사용한 후 결과를

            merge하는 acees방식도 선택.

          * index가 지정되지 않으면 optimizer는 테이블의 이용가능한

            모든 index에 대해 scan cost를 고려후 최저비용이 드는

            index scan을 선택한다.

     예   : SELECT /*+INDEX(EMP EMPNO_INDEX) */ EMPNO,ENAME

            FROM   EMP

            WHERE  DEPTNO=10



  8.INDEX_ASC

     용도 : INDEX HINT와 동일 단,ASCENDING 으로 SCAN함을 확실히 하기위함.



  9.INDEX_COMBINE

     용도 : INDEX명이 주어지지 않으면 OPTIMIZER는 해당 테이블의

            best cost 로 선택된 Boolean combination index 를 사용한다.

            index 명이 주어지면 주어진 특정 bitmap index 의

            boolean combination 의 사용을 시도한다.

        

            /*+INDEX_COMBINE(table index) */



 10.INDEX_DESC

     용도 : 지정된 테이블의 지정된 index를 이용 descending으로 scan

            하고자할때 사용.



            /*+INDEX_DESC(table index) */



 11.INDEX_FFS

     용도 : full table scan보다 빠른 full index scan을 유도.



            /*+INDEX_FFS(table index) */



 12.MERGE_AJ

     용도 : not in subquery를 merge anti-join으로 변형



            /*+MERGE_AJ */



 13.MERGE_SJ

     용도 : correalted EXISTS subquery를 merge semi-join으로 변형



            /*+MERGE_SJ */



 14.AND_EQUAL

     용도 : single-column index의 merge를 이용한 access path 선택.

            적어도 두개이상의 index가 지정되어야한다.



           /*+AND_EQUAL(table index1,index2...) */

         

 15.USE_CONCAT

     용도 : 조건절의 OR 를 Union ALL 형식으로 변형한다.

            일반적으로 변형은 비용측면에서 효율적일때만 일어난다.



          /*+USE_CONCAT */           

 

           

C. JOIN 순서를 결정하는 Hints



  1.ORDERED

     용도 : from절에 기술된 테이블 순서대로 join이 일어나도록 유도.

       

          /*+ORDERED */

     예   : SELECT /*+ORDERED */ TAB1.COL1,TAB2.COL2,TAB3.COL3

            FROM   TAB1,TAB2,TAB3

            WHERE  TAB1.COL1=TAB2.COL1

            AND    TAB2.COL1=TAB3.COL1;



  2.STAR

     용도 : STAR QUERY PLAN이 사용가능하다면 이를 이용하기위한 HINT.

            STAR PLAN은 규모가 가장큰 테이블이 QUERY에서 JOIN ORDER상

            마지막으로 위치하게 하고 NESTED LOOP 으로 JOIN이 일어나도록

            유도한다.

            적어도 3개 테이블 이상이 조인에 참여해야하며 LARGE TABLE의

            CONCATENATED INDEX는 최소 3컬럼 이상을 INDEX에 포함해야한다.

            테이블이 ANALYZE 되어 있다면 OPTIMIZER가 가장효율적인 STAR PLAN을

            선택한다.   



         /*+STAR */



D. JOIN OPERATION을 결정하는 HINTS.



  1.USE_NL

     용도 : 테이블의 JOIN 시 테이블의 각 ROW가 INNER 테이블을 NESTED LOOP

            형식으로 JOIN 한다.



         /*+USE_NL(inner_table) */

 

     예   : SELECT /*+ORDERD USE_NL(CUSTOMER) */

            FROM   ACCOUNT.BALANCE,CUSTOMER.LAST_NAME,CUSTOMER.FIRST_NAME

            WHERE  ACCOUNT.CUSTNO = CUSTOMER.CUSTNO;



  2.USE_MERGE

     용도 : 지정된 테이블들의 조인이 SORT-MERGE형식으로 일어나도록 유도.



         /*+USE_MERGE(table) */

          * 괄호안의 테이블은 JOIN ORDER상의 뒤의 테이블(?)



  3.USE_HASH

     용도 : 각 테이블간 HASH JOIN이 일어나도록 유도.



         /*+USE_HASH(table) */

          * 괄호안의 테이블은 JOIN ORDER상의 뒤의 테이블(?)



  4.DRIVING_SITE

     용도 : QUERY의 실행이 ORACLE에 의해 선택된 SITE가 아닌 다른 SITE에서

            일어나도록 유도.



         /*+DRIVING_SITE(table) */

     예   : SELECT /*+DRIVING_SITE(DEPT)  */

            FROM   EMP,DEPT@RSITE

            WHERE  EMP.DEPTNO = DEPT.DEPTNO;    



            DRIVING_SITE 힌트를 안쓰면 DEPT의 ROW가 LOCAL SITE로 보내져

            LOCAL SITE에서 JOIN이 일어나지만,

            DRIVING_SITE 힌트를 쓰면 EMP의 ROW들이REMOTE SITE로 보내져

            QUERY가 실행된후 LOCAL SITE로 결과가 RETURN된다.

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)

DBMS/오라클]SQL_Loader_사용법

출처 : http://blog.naver.com/hirokorea/20020980191
         http://nhmjh.egloos.com/1438719
문법
      SQLLDR [keyword=] value [ [keyword=] value ]...
예제
      SQLLDR scott/tiger control='c:\xxx.ctl' log='xxx.log' direct=true
키워드
USERID
  오라클 사용자 이름과 암호를 지정 합니다.

CONTROL
  콘트롤 파일 이름, SQL*Loader을 수행하기 위해서는 항상 지정해 주어야 합니다.

LOG
  로그 파일 이름을 지정 합니다. (기본 이름은 controlfile.log)

BAD
  거 부된 레코드 모두를 저장하는 배드 파일 이름을 지정 합니다.

DATA
  입력 데이터 파일 이름을 지정 합니다.

DISCARD
  Load시 선택되지 않은 레코드가 저장되는 디스카드 파일(선택 사항)

DISCARDMAX
  버림(discard)의 최대 허용 갯수를 지정 합니다.

ERRORS
  허용하 는 배드 레코드의 최대 수를 지정 합니다.

DIRECT
  TRUE로 설정되면 SQL*Loader는 DIRECT PATH를 사용.
반대의 경우는 기본 값인 CONVENTIONAL PATH를 사용 합니다.

PARFILE
  추가 파라미터 파일을 지정 합니다.

PARALLEL
  DIRECT 로드에서만 적합한 이 파라미터는 다중 병렬 DIRECT로드가
수행되도록 지정 합니다.

FILE
  병렬 DIRECT로드의 경우 임시 세그먼트가 생성될 파일을 지정 합니다.
①　SAMPLE TABLE 생성
-------------------------------------------------------------------------
CREATE TABLE TMP(
    SEQ_NUM     VARCHAR2(10) ,
    UNIQ_KEY    CHAR(7) ,
    RECORD_NUM NUMBER(7,0),
    ISS_TIME    DATE,
    IF_STTS     CHAR(2),
    MEMO        VARCHAR2(100)
)
/
②　SAMPLE 시퀀스 생성
-------------------------------------------------------------------------
CREATE SEQUENCE TMP_SEQ
INCREMENT BY 1
START WITH 1
MAXVALUE 10000000000000000
NOMINVALUE
CYCLE
CACHE 20
NOORDER
/
③　SAMPLE 데이터 파일 생성
-------------------------------------------------------------------------
C:\S_DATAFILE.DAT
      120060123125959MEMO1
     2220060124125959MEMO2
    33320060125125959MEMO3
④　SAMPLE SQLLOADER CONTROLFILE 생성
-------------------------------------------------------------------------
C:\S_DATAFILE.CTL
Options (Rows = 1000)
Load DATA
INFILE     'C:\S_DATAFILE.DAT'
BADFILE  'C:\S_DATAFILE.BAD'
APPEND
    INTO TABLE TMP (
        -- SEQ_NUM컬럼의 최대값 +1을 대입
        SEQ_NUM            SEQUENCE(MAX,1) ,
        -- 시퀀스 TMP_SEQ의 다음할당값 대입
        UNIQ_KEY            TMP_SEQ.NEXSVAL ,
        RECORD_NUM      POSITION( 1 : 7   ) DECIMAL EXTERNAL ,
        ISS_TIME             POSITION( 8 : 21 ) DATE "YYYY/MM/DD HH:MI:SS" ,
        IF_STTS               CONSTANT "00" ,
        MEMO                 POSITION( 22 : 116   ) CHAR
    )
⑤　SQLLOADER 실행
-------------------------------------------------------------------------
C:>sqlldr scott/tiger@XXXcontrol = C:\S_DATAFILE.CTL
//-----------------------------------------------------------------------------
출처 : http://cafe.naver.com/superadmin.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=42
sqlloader를 사용하기 위해서는 미리 테이블이 만들어져 있어야 함.

sqlloader 화일 샘플

#!/bin/csh
if ( $#argv != 2) then
    echo "Usage: $0 <DataFile> <logFileName>"
    exit 1
endif

sqlldr userid=oracle/oracle control=tblmacs_tmpidrlog.ctl data=$1 log=$2.log commit_discontinued=true

data는 raw데이타 들어갈 부분, log는 생략해도 무방..., commit으로 시작하는 부분도 생략가능

실행방법

sqlloader.sh "실제데이타 화일"
ex) sqlloader.sh  SWBLS1_FWEBIDR1_ID0001_T20030603000109.DAT

이런식으로 사용 가능(원격폴더 지정시)
## sqlloader.sh /bear02/macslog/20030401/SWBLS1_FWEBIDR1_ID0001_T20030401000110.DAT /bear01/brewlog/src/eshock/log/z0000

control화일 샘플

load data
INFILE *
append
into table TBLMACS_TMPIDRLOG
when RECORD_TYPE='R'
(
        RECORD_TYPE position(01:01) char
       ,RECORD_SEQ  position(02:08) char
       ,SVC_TYPE    position(09:09) char
       ,CALL_NUM    position(10:20) char
       ,CHARGE_NUM  position(21:31) char
       ,CP_NO       position(32:36) char
       ,CONTENTS_NO position(37:38) char
       ,MENU_NO     position(39:40) char
       ,CALL_START  position(41:54) char
       ,DURATION    position(55:63) char
       ,PKT_CNT     position(64:72) char
       ,PHONE_TYPE  position(73:87) char
       ,BROWSER_VER position(88:102) char
)

position은 순서대로 1부터 1까지 자름, 그다음 2칸째부터 8칸 이후까지를 의미함.

[ SQL*Loader 사용하기 ]
작성일자 : 2001년 6월 11일
작 성 자 : 조수환

1. SQL*Loader를 이용해서 Data를 넣을 파일을 Excel에서 생성한다.
        - 파일을 *.csv로 저장을 한다.
          (SQL*Loader 를 사용하기 위해서는 ,(콤마)가 필요하다)
        즉, 다음과 같은 데이타 포멧이면 된다.
                10000,CEO, 등록
                10020,CEO CEO실,등록
                150000,솔루션사업부 문,등록
                150010,솔루션사업부문 신프라팀,등록
                150020, 솔루션사업부문 기관영업팀,등록
                150030,솔루션사업부문 사업지원팀,등록
                150040, 솔루션사업부문 CS팀,등록

2. csv 파일에는 departcode, departname, gubun 의 형식을 데이타가 들어가 있다.

        Ex>        departcode,         departname,         gubun
                ---------- ------------------------------
                   180040         경영전략부문         홍보팀
                   190000         재경부문
                   190010         재경부문         재경기획팀
                   190020         재경부문         재경팀
                   190030         재경부문         물류지원팀
                   200000         인재기획실

3. Data를 넣을 테이블의 명세서는 다음과 같다.
        SQL> desc departinfo;
         이름       널?      유형
         ---------- -------- ------------
         ID         NOT NULL NUMBER
         DEPARTCODE              NUMBER(6)
         DEPARTNAME          VARCHAR2(40)
         CREATEDATE          DATE
         MODIFYDATE          DATE
         GUBUN               CHAR(4)


4. 콘트롤 파일(abc.ctl) 을 하나 만들고, 다음과 같은 내용을 기록한다.

        LOAD DATA
        INFILE '부서코드.csv'                        -- 참조할 파일은 부서코드.csv
        replace                                        -- 테이블의 내용을 모두 삭제한 후에 입력
        INTO TABLE departinfo                        -- 데이타가 들어갈 테이블 명
        fields terminated by ','                -- ,(콤마)를 기준으로 구분한다.
        (
          id                sequence(1,1),                -- 1부터 1씩 증가시킨다.
          departcode        integer external,        -- 외부의 데이타를 가져온다.
          departname        char(40),                -- 문자열은 40
          createdate        sysdate,                -- Load를 할 때 sysdate값을 넣어준다.
          modifydate    sysdate,                -- Load를 할 때 sysdate값을 넣어준다.
          gubun        char(4)                                -- 문자열은 4
        )

5. SQL*Loader를 실행시킨다.
        # sqldlr atomx/akstp@sysora control=abc.ctl log=0611.log
        - > 위와 같이 하면 부서코드.csv의 내용이 conrolfile의 정의와 함께
            테이블에 쭉 들어간다.

< 참조> 다른 컨트롤 파일의 내용

1. 컨트롤 파일(bbb.ctl)의 내용
        LOAD DATA
        INFILE '사원인사.csv'
        replace
        INTO TABLE companyinfo
        fields terminated by ','
        (
          id                sequence(1,1),
          companycode        integer external,
          name                char(20),
          departcode        integer external,
          tel                char(4) nullif tel = blanks,        -- 만일 공백이면 NULL상태로 만든다.
          enteringdate        date ,
          createdate        sysdate,
          modifydate    sysdate,
          gubun        char(4)
        )

2. sqlldr atomx/akstp@sysora control=bbb.ctl log=0611.log

//-----------------------------------------------------------------------------------
출처 : http://www.dbguide.net/dbqa/dbqa120001.jsp?mode=view&key=subject&search=Loader&divcateno=256&divcateno_=243&pg=1&idx=1
SQL*Loader 는 외부 화일의 데이타를 ORACLE 데이타베이스의 table에 넣기 위한 유틸리티입니다. SQL*Loader를 사용하려면 외부 데이타 화일과 컨트롤 화일이 필요합니다. 컨트롤 화일이라고 하는 것은 로드하는 데이타의 정보를 저장한 화일입니다.
간단한 샘플 컨트롤 화일을 설명하겠습니다.

    load data                        제어 화일의 선두에는 반드시 이 말이 필요합니다.
    infile sample.dat              외부 화일을 지정합니다.
    replace                           테이블에 데이타 넣는 방법 지정
    into table TABLE_NAME   데이타를 로드하는 테이블을 지정
    fields terminated by ','     데이타 필드의 종결 문자 지정
    (a integer external,          테이블의 열, 외부 데이타 화일의 데이타 형을 지정
     b char)

    참고로 replace 외에 다음의 옵션이 가능합니다.

    replace                 테이블의 기존 행을 모두 삭제(delete)하고 insert
    append                 새로운 행을 기존의 데이타에 추가
    insert                   비어 있는 테이블에 넣을 때
    truncate                테이블의 기존 데이타를 모두 truncate 하고 insert

    SQL*Loader를 실행하면 아래의 화일이 작성됩니다.

    * 로드 작업 중 동작에 관한 통계 등을 포함한 로그 화일(확장자는 log)
    * 데이타 에러 때문에 로드가 안된 레코드를 저장한 화일(확장자는 bad)
    * 사용자의 선택 기준에 적합하지 않은 레코드를 저장한 화일(discard 화일)
      이것은 discardfile 옵션으로 별도로 지정해야 생성됩니다.

실 행 방법은 다음과 같습니다.

$sqlldr scott/tiger control=sample.ctl data=sample.dat1.1 임의의 열에 변화없는 고정 문자열(값)을 입력한 경우테이블 구조
    create table cons_test
    (a number,
     b number,
     c number,
     d varchar(10))

컨 트롤 화일 (즉 이예에서 sample.ctl)
    load data
    infile cons.dat
    replace
    into table cons_test
    fields terminated by ','
    (a integer external,
     b integer external,
     c CONSTANT '100',
     d char)

외부 데이타 화일 (즉, 이예에서 sample.dat)
    1,2,DATA
    2,4,DATA2

검색결과
    SQL>select * from cons_test;

           A       B       C D
    ---------------------------------
           1       2     100 DATA
           2       4     100 DATA2

주 의사항 : 이 예에서 C열은 데이타 화일에 넣어서는 안 됩니다. COSNTANT는 그것으로 완결된 열 지정의 하나가 됩니다. integer external 데이타 형은 수치 데이타를 문자형식(ASCII CODE)로 나타낸 것입니다.
1.2 로드한 때의 날짜를 데이타로 로드하고 싶은 경우테 이블 구조
   create table sysdatetb
    (a number,
    b date,
    c varchar(10))

컨트롤 화일
    load data
    infile sysdate2.dat
    replace
    into table sysdatetb
    fields terminated by ','
    (a integer external,
    b sysdate,
    c char(10))

외부 데이타 화일
    111,STRINGS
    222,STRINGS2

검 색결과
SQL>select * from sysdatetb;

        A B         C
-----------------------------
      111 13-MAY-94 STRING
      222 13-MAY-94 STRING2

주의사항 : 이 예에서는 B열은 데이타 화일에 넣어서는 안됩니다. SYSDATE는 그것으로 완결된 열 지정의 하나가 됩니다. 새로운 시스템 날짜매김은 컨벤셔널 패스에서는 실행 시에 삽입된 각각의 레코드 배열마다 또, 다이렉트 패스의 경우는 로드된 각각의 레코드의 블럭마다 사용됩니다.
1.3 임의의 수치열에 연속 번호(sequence)를 붙이고 싶은 경우테이블 구조
    create table rectb
    (a varchar(10),
    b number,
    c varchar(10))

컨트롤 화일
    load data
    infile rec.dat
    replace
    into table rectb
    fields terminated by ','
    (a char,
    b recnum,
    c char)

외부 데이타 화일
   A,a
    B,b
   C,c

검색결과
SQL>select * from rectb;

   A       B C
    ---------------------
   A       1 a
   B       2 b
   C       3 c

주의사항 : 이 예에서는 B 열은 데이타 화일에 넣어서는 안됩니다. RECNUM는 그것으로 완전한 열 지정의 하나가 됩니다. 연속번호는 1부터 차례대로 1씩 더해져서 번호가 매겨집니다. 가산된 번호를 둘씩 건너 뛰거나 하는 것은 불가능 합니다.
1.4 임의의 수치열에 연속 번호(sequence)를 임의의 간격으로 붙이고 싶은 경우테이블 구조
    create table seqtb
    (a varchar(10),
    b number,
    c varchar(10))

컨트롤 화일
    load data
    infile seq.dat
    replace
    into table seqtb
    fields terminated by ','
    (a char,
    b sequence(100,5),
    c char)

외부 데이타 화일
    1,a
    2,b
    3,c

검색결과
    SQL>select * from seqtb;

    A         B C
    -------------------------
    1       100 a
    2       105 b
    3       110 c

주의 사항 : 이 예에서는 B열은 데이타 화일에 넣어서는 안 됩니다. SEQUENCE는 그것으로 완결된 열 지정의 하나가 됩니다. 초기 값 100과 늘인 값 5는 다른 수치로 변경 가능합니다.

1.5 로드하는 논리 레코드를 구성하는 물리 레코드가 복수열로 구성된 경우
     (물리 레코드의 1바이트 째로 판단되는 경우)

테이블 구조
    create table conti_test
    (a varchar(10),
    b varchar(10),
    c varchar(10))

컨트롤 화일
    load data
    infile conti.dat
    replace
   continueif this
   (1) = '%'
   into table conti_test
   fields terminated by ','
   (a char,
   b char,
   c char)

외부 데이타 화일
%1,
%2,
3
%A,B
,C
%a,b
%c
%d
,ef

검색결과
    SQL>select * from conti_test;

    A      B      C
    ---------------------
    1      2      3
    A      B      C
    a      bcd    ef

주의사항 : 이 예의 경우 1바이트 째가 계속 행의 체크를 위해서 사용되기 때문에, 실 데이타를 1 바이트 째부터 시작해서는 안됩니다. 상기 예의 경우, 레코드의 선두 바이트가 '%'일 때 다음의 레코드가 연결됩니다.

1.6 외부 데이타 화일의 물리 레코드가 복수 레코드로 구성된 경우
     (구성하는 물리 레코드 수가 모두 일정한 경우)

테이블 구조
    create table con_test
    (a varchar(10),
    b varchar(10),
    c varchar(10))

컨 트롤 화일
    load data
    infile conti.dat
    replace
    --일례로 모든 논리레코드가 그 레코드로 구성됩니다.
    concatenate 2
   into table con_test
    fields terminated by ','
    (a char,
    b char,
    c char)

외부 데이타 화일
    1,2,
    3
    a,b,
    c
    A,
    B,C

검색결과
    SQl) select * from con_test;

    A       B       C
    -------------------------
    1       2       3
    a       b       c
    A       B       C

주의사항 : 하나의 논리 레코드가 모두 일정한 갯수의 물리 레코드로부터 성립되는 것 같은 단순한 경우에 한합니다.

1.7 데이타의 잘린 문자를 데이타로 로드하고 싶은 경우테 이블 구조
    create table enc
    (a varchar(10),
    b varchar(10),
    c varchar(10))

컨트롤 화일
    load data
     infile enc.dat
    replace
    into table enc
    fields terminated by ',' optionally enclosed by ' " ' and ' " '
    (a char,
    b char,
    c char)

외부 데이타 화일
"abc,d",2,3
   "a,,d",4,5

검색결과
SQL>select * from enc;

    A       B       C
    -------------------------
    abc,d   2       3
    a,,d    4       5

1.8 포지션 지정 시 char 형 데이타 전후의 blank도 로드하고 싶은 경우테이블 구조
    create table pretb
    (a varchar(10),
    b varchar(10),
    c varchar(10))

컨트롤 화일
    load data
    infile pre.dat
    replace
   preserve blanks
    into table pretb
    (a position(01:05) char,
    b position(06:10) char,
    c position(11:20) char)

외부 데이타 화일
    12 4 67890 ab def hi
      2   67890 ab def hi

검색 결과
    SQL>select * from pretb;

    A       B       C
    --------------------------
    12 4    67890   ab def hi
      2       67890   ab def hi

결과 확인
    SQL>select length(a), length(c) from pretb;

    LENGTH(A) LENGTH(C)
    -------------------
             5        10
             5        10

1.9 어떤 데이타 열의 데이타 유무와 상관없이 데이타가 없는 경우 NULL 데이타를 넣도록 하고 싶다테 이블 구조
    create table tratb
    (a varchar(10),
    b varchar(10),
    c varchar(10))

컨트롤 화일
    load data
    infile tra.dat
    replace
    into table tratb
    fields termintated by ','
    trailing nullcols
    (a char,
    b char,
    c char)

외부 데이타 화일
    1,aa,
    2,bb,FF
    3,cc,

검색결과
    SQL>select * from tratbl

    A       B       C
    ------------------------
    1       aa
    2       bb      FF
    3       cc

주의사항 : trailing nullcols를 사용하지 않으면 1 레코드째와 3 레코드째가 데이타 에러가 됩니다. 데이타가 들어 있기도 하고 없기도 한 열의 데이타는 데이타 화일의 최후로 가져갑니다.

1.10 CHAR 형 필드가 BLANK로 채워져 있을 때 NULL을 삽입하고 싶은 경우테이블 구조
    create table nulltb
    (a varchar(10),
    b varchar(10),
    c varchar(10))

컨트롤 화일
    load data
    infile null.dat
    replace
    into table nulltb
    fields terminated by ','
    (a char,
    b char,
    c char(10) nullif c = blanks)

외부 데이타 화일
    aa,bb, ,
    11,22, ,
    99,88,AA
    00,00,BB

검색결과
    SQL>select * from nulltb;

    A       B       C
    -------------------------
    aa      bb
    11      22
    99      88      AA
    00      00      BB

주의 사항 : 검색결과의 1 행째, 2 행째의 열 C는 블랭크가 아니라 NULL 입니다.
1.11 DATE 필드가 BLANK로 채워져 있을 때 NULL을 삽입하고 싶은 경우**** 테이블 구조 ****
create table nulltb2
(a varchar(10),
b varchar(10),
c date)

**** 콘트롤 화일 ****
load data
infile null2.dat
replace
into table nulltb2
fields terminated by ','
(a char,
b char,
c date "YY/MM/DD" nullif c = blanks)

**** 외부 데이타 화일 ****
aa,bb, ,
11,22, ,
99,88,92/11/11,
00,00,94/12/12,

**** 검색결과 ****
SQL>select * from nulltb2;

A       B       C
-------------------------
aa      bb
11      22
99      88      92/11/11
00      00      94/12/12

주의사항 : 검색결과의 1 행째, 2 행째의 열 C는 블랭크가 아니라 NULL 입니다.
1.12 POSITION 지정 시 BLANK를 그대로 로드하고 싶은 경우**** 테이블 구조 ****
create table nulltb2
(a varchar(10),
b varchar(10),
c date)

**** 콘트롤 화일 ****
--- position 지정으로 블랭크를 그대로 입력 원하는 경우
--- preserve blanks를 지정한다.
load data
infile null3.dat
replace
preserve blanks
into table nulltb2
(a position(1:2) char,
b position(3:4) char nullif b = blanks,
c position(5:13) date "YY/MM/DD")

**** 외부 데이타 화일 ****
998892/11/11
    94/12/12

**** 검색결과 ****
SQL>select * from nulltb2;

A       B       C
-------------------------
99      88      92/11/11
                94/12/12

SQL>select length(a), length(b) from nulltb2;

LENGTH(A) LENGTH(B)
-------------------
        2         2
        2

주의사항 : 이 경우 2 레코드째는 A에 블랭크가 들어가고 B에 NULL이 들어갑니다.

1.13 데이타 화일의 수치 데이타 열에 BLANK가 들어가 있을 때 0을 입력하고 싶은 경우**** 테이블 구조 ****
create table def2
(a varchar(10),
b varchar(10),
c number)

**** 콘트롤 화일 ****
load data
infile def2.dat
replace
into table def2
fields terminated by ','
(a char,
b char,
c integer external defaultif c = blanks)

**** 외부 데이타 화일 ****
11,11,123
22,22, ,
33,33, ,
44,44, ,

**** 검색결과 ****
SQL>select * from deft;

A       B              C
------------------------
11      11           123
22      22             0
33      33             0
44      44             0

1.14 데이타가 NULL일 때 NULL이라고 하는 문자열을 넣고 싶은 경우**** 테이블 구조 ****
create table ifnulltb
(a varchar(10),
b varchar(10),
c varchar(10))

**** 콘트롤 화일 ****
load data
infile ifnull.dat
replace
into table ifnulltb
fields terminated by ','
(a char,
b char "nvl(:b,'NULL')",
c char)

**** 외부 데이타 화일 ****
1,2,3,
A,,B
a,b,c

**** 검색결과 ****
SQL>select * from ifnulltb;

A       B       C
-------------------------
1       2       3
A       NULL    B
a       b       c

주의 사항 : NVL과 같은 SQL 함수는 DIRECT LOAD의 경우 SQL 인터페이스를 경유하지 않기 때문에 사용할 수 없습니다.

1.15 어떤 열을 모두 대문자(소문자)로 변환하여 로드하고 싶은 경우**** 테이블 구조 ****
create table uptb
(a varchar(10),
b varchar(10))

**** 콘트롤 화일 ****
load data
infile upper.dat
replace
into table uptb
fields terminated by ','
(a char "lower(:a)",
b char "upper(:b)")

**** 외부 데이타 화일 ****
aBcDeFg,AbCdEf
ccDD11,ffGG22

**** 검색결과 ****
SQL>select * from uptb;

A         B
--------------------
abcdefg   ABCDEF
cdd11     FFGG22

1.16 ZONE 형 데이타를 로드하고 싶은 경우ZONE 의 데이타 형식은 1문자 1바이트로 나타납니다.

숫자 +123이라면

bit     1234    5678    1234    5678    1234    5678
        1111    0001    1111    0010    1100    0011
10진            1               2         +        3

최후의 8비트의 상위 4비트(부호비트)로 정부가 지정됩니다.

       1100(정) 1101(부)

가 됩니다.

원래는 EBCDIC코드에서

Hex F0 to F9 → +0 to +9
Hex C0 to C9 → +0 to +9(부호비트)
Hex D0 to D9 → +0 to -9(부호비트)

이것을 아스키로 변환했기 때문에

부호비트        {ABCDEFGHI}JKLMNOPQR
------------------------------------
부 호            ++++++++++----------
수              01234567890123456789

가 됩니다.

**** 테이블 구조 ****
create table z
(a varchar(10),
i number,
j number)

**** 콘트롤 화일 ****
load data
infile filename.dat
replace
into table z
(a position (01) char,
i position(02:07) zoned(6),
j position(08:13) zoned(6))

**** 외부 데이타 화일 ****
A12345{12345}

**** 검색결과 ****
SQL>select * from z;

A            I        J
-----------------------
A       123450 -123450

1.17 decimal 형 데이타를 로드하고 싶은 경우decimal 데이타는 1바이트에 2개의 숫자가 지정됩니다.

숫자 +123 이라면

bit    1234 5678 1234 5678
       0001 0010 0011 1100
10진      1    2    3    +

끝의 4비트는 부호비트로 불리고

     1100(정)        1101(부)

가 됩니다.

**** 테이블 구조 ****
create table dec
(a varchar(10),
col1 number,
col2 number)

**** 콘트롤 화일 ****
load data
infile filename.dat
replace
into table dec
(a position (01:01) char,
col1 position(02:05) decimal(7,4),
col2 position(06:09) decimal(7,4))

**** 외부 데이타 화일 ****
데이타화일은 바이너리이기 때문에 more 등으로 볼 수 없습니다.

4112 3456 7c12 3456 7d0a

**** 검색결과 ****
SQL>select * from dec;

A               COL1        COL2
--------------------------------
A           123.4567   -123.4567

주 의 사항 :콘트롤 화일에서 decimal 형을 사용하는 것에 따라 (length, scale)을 지정합니다.

1.18 고정 길이 레코드 데이타를 로드하고 싶은 경우**** 테이블 구조 ****
create table fixtb
(a varchar(10),
b varchar(10))

**** 콘트롤 화일 ****
load data
infile fix.dat
"FIX 6"
replace
into table fixtb
(a char(3),
b char(3))

**** 외부 데이타 화일 ****
123456abcdef

**** 외부 데이타 화일(16진) ****
3132 3334 3536 6162 6364 6566

**** 검색결과 ****
SQL>select * from fixtb;

A      B
---------------
123    456
abc    def

주 의 사항 : 데이타 화일의 끝에는 OxOa(CARRIAGE RETURN)가 들어가면 안 됩니다. OxOa도 1문자로 카운트되기 때문입니다.

# by 꾸술 | 2006/10/25 10:11 | 백업&리커버리 | 트랙백 | 덧글(3)

트랙백 주소 : http://nhmjh.egloos.com/tb/1438719