.NET TEAM - MindVirus

ORA-01722 : 수치가 부적합합니다.
이 오류는 넘버형에 강제로 스트링 타입을 넣을 때 발생하는 오류이다.

프로시져 또는 트리거 실행 시 SELECT INTO에서 조회된 쿼리의 ROW가 존재하지 않을때

ORA-01403 에러발생 조치

1. Select  절에서 Row가 없는 경우 Exception으로 뺀다.

예)

  select rtptno
    into v_rtptno
    from mosstbat
   where pt_no = :new.q_patientno;

결과가 no_data_found로 나온 경우

아래와 같이 exception을 추가해 준다.

 begin

  select rtptno
    into v_rtptno
    from mosstbat
   where pt_no = :new.Q_PATIENTNO;

  exception
   when no_data_found then null;
   when others then
    null;

 end;

오라클 힌트와 인덱스를 이용한 최대값 MAX 구하기 index_desc 

오라클 필드 (Alter table) 추가/수정/삭제

alter table 테이블명 modify(컬럼명 변수형);

alter table 테이블명 drop(컬럼명);

alter table 테이블명 add(컬럼명 변수형);

EX) alter table test_tb add(user VARCHAR2(30));

하루더하기
Select sysdate + 1 from dual

시간더히기
Select sysdate + 1/24 from dual

분더히기
Select sysdate + 1/(24*60) from dual

초더히기
Select sysdate + 1/(24*60*60) from dual

아래 링크에서 받을수 있습니다.

http://www.oracle.com/technology/software/tech/windows/ora_mts/htdocs/utilsoft.html

ALL_ROWS

             /*+ ALL_ROWS */

-         최소한의 자원을 사용하여 결과값의 전체를 추출하게 합니다.

 

AND_EQUAL

             /*+ AND_EQUAL (table index index [index] [index] [index] ) */

-         복수의 단일 컬럼을 스캔하여 머지 방식으로 처리하게 합니다.

 

APPEND_HINT

             /*+ APPEND */

-         직렬 모드 데이터베이스에서 Direct INSERT를 실행하게 합니다.

-         Enterprise Edition 이 아닌 데이터베이스의 기본 모드는 직렬 모드입니다. 이러한 직렬 모드 데이터 베이스에서의 INSERT 작업은 Conventional를 기본값으로 하고 병렬 처리 시에는 Direct INSERT를 기본값으로 합니다.

 

CACHE_HINT

             /*+ CACHE (table) +/

-         풀 테이블 스캔의 사용 시, 테이블에서 읽어온 블럭을 버퍼의 LRU 리스트 의 MRU 쪽에 위치시킵니다. 작은 테이블의 사용 시 유용합니다.

 

CHOOSE_HINT

             /*+ CHOOSE +/

-         Rule-Based 와 Cost-Based 방식 간의 선택을 유도합니다. 선택 기준은 사용 객체의 분석 정보 존재 여부이며, 사용되는 객체들중 하나라도 분석 정보가 존재한다면 Cost-Based 방식을 사용하게 됩니다.

 

CLUSTER_HINT

             /*+ CLUSTER (table) +/

-         지정 테이블의 클러스터 스캔을 유도합니다. 클러스터된 객체에만 사용할 수 있습니다.

 

CURSOR_SHARING_EXACT

             /*+ CURSOR_SHARING_EXACT +/

-         바인드 변수 값의 교체를 불가능하게 합니다.

-         기본적으로 CURSOR_SHARING 파라미터를 사용하여, 안전하다고 판단될 시 SQL 내의 바인드 변수 값을 교체할 수 있게 되어 있습니다.

 

DRIVING_SITE

             /*+ DRIVING_SITE (table) +/

-         오라클이 선택한 SITE 대신, 지정한 SITE를 사용하여 쿼리를 실행합니다. Rule-Based 와 Cost-Based, 두 모드 다 사용 가능합니다.

 

DYNAMIC_SAMPLING

             /*+ DYNAMIC_SAMPLING ( [table] n ) +/

-         해당 객체의 Selectivity 와 Cardinality 에 대한 보다 자세한 정보를 자동으로 생성시켜 실행합니다.

-         값은 0 부터 10 까지 지정할 수 있으며, 높을 수록 보다 자세한 정보를 생성하게 됩니다. 테이블에 해당 값을 지정하지 않았을 경우, 기본 값은 CURSOR 레벨의 값이 쓰여집니다.

 

EXPAND_GSET_TO_UNION

             /*+ EXPAND_GSET_TO_UNION +/

-         GROUP BY GROUPING SET 혹은 GROUP BY ROLLUP 등과 같은 구문을 포함하는 쿼리에 사용할 수 있습니다.

-         이 힌트는 기존의 쿼리를 개별적인 그룹 생성 후, UNION ALL 방식으로 실행되게 유도합니다.

 

FACT_HINT

             /*+ FACT (table) +/

-         스타 변형 구문에서 사용되며 해당 테이블이 FACT 테이블로 사용되게 유도합니다.

 

FIRST_ROWS

             /*+ FIRST_ROWS (n) +/

-         전체 결과값의 반환 대신 지정한 숫자만큼 로우의 결과값을 반환하는데 집중하게 유도합니다.

 

FULL_HINT

             /*+ FULL (table) */

-         지정한 테이블에 대해 풀 테이블 스캔을 유도합니다.

 

HASH_HINT

             /*+ HASH (table) */

-         지정한 테이블에 대해 hash 스캔을 수행하도록 유도합니다.

-         클러스터 테이블 만을 대상으로 합니다.

 

HASH_AJ

             /*+ HASH_AJ */

-         EXISTS 구문 뒤에 오는 서브 쿼리에 사용되며 HASH_SJ, MERGE_SJ 혹은 NL_SJ 등을 사용할 수 있습니다.

-         HASH_SJ 은 hash semi-join 이고, MERGE_SJ 은 sort merge semi-join 이며 NL_SJ 은 nested loop semi-join 입니다.

 

INDEX

             /*+ INDEX (table index [index] [index] ... ) */

-         지정한 테이블의 인덱스 스캔을 실행하도록 유도합니다.

-         Domain, B-tree, bitmap, bitmap join 인덱스 등이 사용될 수 있으나, bitmap 인덱스 들의 사용 시, INDEX 힌트보다는 INDEX_COMBINE 힌트 사용이 추천됩니다.

 

INDEX_ASC

             /*+ INDEX-ASC (table [index] [index] ... ) +/

-         해당 테이블의 인덱스를 순차적 방식으로 스캔하게 합니다.

-         해당 쿼리가 인덱스 범위 스캔의 사용 시, 인덱스 값의 순차적 방식으로 읽게 됩니다.

 

INDEX_COMBINE

             /*+ INDEX_COMBINE (table [index] [index] ... ) +/

-         해당 테이블에 Bitmap 인덱스의 존재 시, Bitmap 인덱스를 통한 액세스를 유도합니다.

-         힌트 내에 인덱스의 이름이 쓰여지지 않을 시, 해당 인덱스의 Boolean 값을 사용하여 최적의 Cost를 산출하여 실행하게 됩니다.

 

INDEX_DESC

             /*+ INDEX_DESC (table [index] [index] ... ) +/

-         지정한 인덱스에 대해 인덱스 스캔을 역순으로 실행합니다.

-         해당 쿼리가 인덱스 범위 스캔의 사용 시, 인덱스 컬럼의 값을 사용하여 역순으로 실행합니다.

-         파티션 인덱스에서는 파티션 별 개별적인 실행이 이루어집니다.

 

INDEX_FFS

/*+ INDEX_FFS (table [index] [index] ... ) +/

-         풀 테이블 스캔 대신에 빠른 풀 테이블 스캔의 실행을 유도합니다.

 

LEADING_HINT

             /*+ LEADING (table) +/

-         테이블 간의 조인 시에 지정한 테이블을 먼저 수행하도록 유도합니다.

-         두 개 이상의 LEADING 힌트의 사용 시, 힌트 자체가 사용되어 지지 않습니다.

-         ORDERED 힌트와 더불어 사용시, LEADING 힌트는 사용되지 않습니다.

 

MERGE

             /*+ MERGE (table) +/

-         각 쿼리의 결과값을 머지합니다.

-         해당 쿼리 내에 GROUP BY 절의 사용 이나 SELECT 구문에 DISTINCT 가 사용되었을 시, 머지의 실행이 가능할 경우에만 힌트가 실행됩니다.

-         IN 과 서브 쿼리의 사용 시, 서브 쿼리와 상위 쿼리 간의 상호 관계가 없을 때에만 머지의 실행이 가능합니다.

-         이 힌트는 Cost-based 가 아닙니다. 따라서 액세스하는 실행 쿼리 블럭에 MERGE 힌트가 반드시 명시되어야만 합니다. 그렇지 않을 경우 옵티마이저는 다른 실행 계획을 수립합니다.

 

MERGE_AJ

             HASH_AJ 를 참조하십시요.

 

MERGE_SJ

             HASH_AJ 를 참조하십시요.

 

NL_AJ

             HASH_AJ 를 참조하십시요.

 

NL_SJ

             HASH_AJ 를 참조하십시요.

 

NOAPPEND

             /*+ NOAPPEND +/

-         병럴 모드에서의 INSERT 작업을 Conventional 방식으로 수행합니다.

-         병렬 모드에서는 Direct-path INSERT 가, 직렬 모드에서는 Conventional INSERT가 기본값입니다.

 

NOCACHE

             /*+ NOCACHE (table) +/

-         풀 테이블 스캔의 사용 시, 테이블에서 읽어온 블럭을 버퍼의 LRU 리스트 의 LRU 쪽에 위치시킵니다. 기본 모드입니다.

 

NO_EXPAND

             /*+ NO_EXPAND +/

-         실행 쿼리 내에 OR 나 WHERE 절의 IN 이 사용되었을 시, Cost-Based 옵티마이저가 쿼리 처리를위해 OR 를 사용한 확장을 사용하는 것을 방지합니다.

-         일반적으로 옵티마이저는 위와 같은 경우 OR – 확장의 가격이 확장을 사용하지 않는 것보다 적을 시, 확장 방식으로 수행합니다.

 

NO_FACT

             /*+ NO_FACT (table) +/

-         Star 변형 시, 해당 테이블의 FACT 테이블로서의 사용을 방지합니다.

 

NO_INDEX

             /*+ NO_INDEX (table [index] [index] ... ) +/

-         지정 테이블의 인덱스 사용을 방지합니다.

 

NO_MERGE

             /*+ NO_MERGE (table) +/

-         머지 처리 방식의 사용을 방지합니다.

 

NOPARALLEL

             /*+ NOPARALLEL (table) +/

-         지정한 테이블의 병렬 처리를 방지합니다.

-         테이블의 지정된 PARALLEL 값에 대해서 우선권을 가집니다.

-         중첩 테이블에 대해서는 병렬 처리를 할 수 없습니다.

 

NOPARALLEL_INDEX

             /*+ NOPARALLEL_INDEX (table [index] [index] ... ) +/

-         인덱스 스캔 작업의 병렬 처리를 방지합니다.

-         인덱스에 지정된 PARALLEL 값에 우선권을 가집니다.

 

NO_PUSH_PRED

             /*+ NO_PUSH_PRED (table) +/

-         결과값에 대한 조인 방식 서술의 강제적 수행을 방지합니다.

 

NO_PUSH_SUBQ

             /*+ NO_PUSH_SUBQ +/

-         서브 쿼리의 결과값을 머지하지 않는 실행 계획이 실행 계획 설립 단계에서 제일 마지막으로 참조되는 것을 방지합니다.

-         일반적으로 서브 쿼리의 Cost 가 높거나, 처리 로우의 갯수를 크게 줄여주지 못할 때에는 서브 쿼리를 마지막에 참조하는 것이 성능 향상에 도움이 됩니다.

 

NOREWRITE

             /*+ NOREWRITE +/

-         해당 쿼리 블럭의 쿼리 재생성의 실행을 방지합니다.

-         QUERY_REWRITE_ENALBE 파라미터에 대해 우선권을 가집니다.

-         NOREWRITE 힌트의 사용 시, Function-Based 인덱스의 사용이 금지됩니다.

 

NO_UNNEST

             /*+ NO_UNNEST +/

-         해당 서브 쿼리 블럭의 UNNESTING 설정의 사용을 방지합니다.

 

ORDERED

             /*+ ORDERED +/

-         FROM 절에 나열된 테이블의 순서대로 조인 작업을 실행합니다.

 

ORDERED_PREDICATE

             /*+ ORDERED_PREDICATE +/

-         옵티마이저에 의한 조인 관계의 Cost를 산출하기 위해 미리 정해둔 조인 관계 별 실행 순서의 사용을 방지합니다.

n         인덱스 키를 사용한 조인 관계들은 제외됩니다.

-         이 힌트는 쿼리의 WHERE 절에 사용하십시요.

 

PARALLEL

             /*+ PARALLEL (table [ [, n |, DEFAULT |, ] [, n | DEFAULT ] ] ) +/

-         병렬 처리에 사용될 서버 프로세스의 갯수를 설정합니다.

-         병렬 처리 조건에 위배될 시, 힌트는 사용되지 않습니다.

-         임시 테이블에 대한 PARALLEL_HINT 사용 시, 힌트는 사용되지 않습니다.

 

PARALLEL_INDEX

             /*+ PARALLEL_INDEX (table [ [index] [, index]...]

[ [, n |, DEFAULT |, ] [, n | DEFAULT ] ] ) +/

-         파티션 인덱스의 인덱스 범위 스캔 작업의 병렬 처리에 할당될 서버 프로세스의 갯수를 지정합니다.

 

PQ_DISTRIBUTE

             /*+ PQ_DISTRIBUTE (table [,] outer_distribution, inner_distribution) +/

-         병렬 조인 시, Producer 프로세스와 Consumer 프로세스 간의 데이터 전달 방식을 지정합니다.

 

PUSH_PRED

             /*+ PUSH_PRED (table) +/

-         결과값에 대한 조인 방식 서술의 강제적 수행을 실행합니다.

 

PUSH_SUBQ

             /*+ PUSH_SUBQ +/

-         머지가 불가능한 서브 쿼리들의 우선 실행 계획을 실행 계획 수립시 먼저 참조하도록 합니다.

-         서브 쿼리의 사용 객체가 Remote 테이블이거나, 머지 조인의 사용 시 힌트는 실행되지 않습니다.

 

REWRITE

             /*+ REWRITE [ ( [materialized_view] [materialized_view]...) ] +/

-         실행 계획의 가격에 상관없이 Materialized View 를 사용하여 쿼리 재생성을 하도록 합니다.

-         Materialized View 를 지정할 시, 지정한 Materialized View 의 가격에 상관없이 무조건 쿼리 재생성을 실행합니다.

-         Materialized View 를 지정하지 않을 시, 오라클은 사용 가능한 모든 Materialized View 를 참조하여 그 중 가장 가격이 낮은 Materialized View 를 사용하여 쿼리 재생성을 합니다.

-         Materialized View 를 지정하지 않는 힌트의 사용이 권장됩니다.

 

ROW_ID

             /*+ ROWID (table) +/

-         지정한 테이블의 스캔을 ROWID 방식으로 수행하게 합니다.

 

RULE

             /*+ RULE +/

-         실행 계획을 Rule-Based 방식으로 실행하게 합니다.

-         해당 쿼리 블럭에 다른 힌트 또한 사용되었을 경우, 다른 힌트들은 사용되지 않습니다.

 

STAR

             /*+ STAR +/

-         Star 쿼리 계획이 사용 가능하다면, 실행하게 합니다.

-         Star 쿼리 계획이란 가장 큰 테이블이 마지막 순서로 조인되며, 조인될 시 가장 큰 테이블 내의 Concatenated 인덱스에 대해 Nested Loop 조인 방식으로 실행되는 것을 말합니다.

-         최소한 세개 이상의 테이블이 사용되며, 제일 큰 테이블의 Concatenated 인덱스의 생성에 최소한 세 개 이상의 컬럼이 사용되어야 하며, 액세스나 조인 방식에 충돌이 없어야만 이 힌트는 사용됩니다.

 

STAR_TRANSFORMATION

             /*+ STAR_TRANSFORMATION +/

-         옵티마이저가 Star 변형 작업에 최적화된 실행 계획을 수립, 실행하도록 합니다.

-         힌트를 사용하지 않을 시, 옵티마이저는 일반적인 작업에 최적화된 실행 계획을 수행합니다.

-         힌트를 사용하였어도 변형 작업에 맞추어진 실행 계획을 실행한다는 보장은 없습니다. 다른 일반적인 힌트의 사용과 마찬가지로 비교 분석 후, 오라클의 판단에 따라 다른 실행 계획이 실행될 수 있습니다.

 

UNNEST

             /*+ UNNEST +/

-         서브 쿼리 블럭에 대해 인증성 만을 검사하게 합니다.

-         인증이 되었다면 그 이상의 검증 작업없이 서브쿼리에 대한 UNNESTING 의 설정을 가능하게 합니다.

 

USE_CONCAT

             /*+ USE_CONCAT +/

-         WHERE 절의 OR 조인 을 UNION ALL 로 변경하여 수행하게 합니다.

-         일반적으로 이러한 변경은 결과값의 병합 수행의 가격이 수행하지 않을 시의 가격 보다 낮을 때에만 실행됩니다.

 

USE_HASH

             /*+ USE_HASH (table [table]...) +/

-         Hash 조인 방식으로 각 테이블을 조인하게 합니다.

 

USE_MERGE

             /*+ USE_MERGE (table [table]...) +/

-         Sort-Merge 방식으로 각 테이블을 조인하게 합니다.

 

USE_NL

             /*+ USE_NL (table [table]...) +/

- Nested-Loop 방식으로 각 테이블을 조인하게 합니다

===============================================================================
※ 수치함수
===============================================================================

▣ 소수점 아래 n자리수가 되게 column, expression 또는 value를 반올림
ROUND(column/value, n)
 ex) ROUND(45.923, 2) ☞ 45.923을 소수점 두자리까지 반올림하여 45.92를 리턴

▣ 소수점 아래 n자리수가 되게 column이나 value를 절삭. n이 음수면 소수점 왼쪽수치 0으로 절삭
TRUNC(column/value, n)
ex) TRUNC(45.923)  ☞ 45.923 소수점 아래를 절삭하여 45를 리턴

▣ 해당 컬림이 NULL이면 express-2의 값을, NULL이 아니면 express-1의 값을 리턴
NVL2(Column, Express1, Express2)
ex) select sal, nvl2(comm, comm, 0)
        from emp
     where deptno = 30);

▣ 정의된 컬럼의 값이 NULL인 경우 지정한 값으로 대체하는 기능
NVL(Column, Express1)
ex) select sal, nvl(comm, 0)
        from emp
     where deptno = 30);


===============================================================================
※ 문자함수
===============================================================================

◈ column이나 문자상수 value로부터 위치번호 pos에서 시작하여 n문자 길이의 스트링을 리턴,
만약 n이 생략되었다면, 스트링은 pos로부터 column이나 문자상수 value의 끝까지를 가져온다.
SUBSTR(column/value, pos, n)
ex) SUBSTR(ename, 2, 3) ☞ ENAME의 2번째 위치에서부터 3자리를 리턴한다.

◈ 숫자 75를 문자값으로 변환합니다.
SQL> select chr(75) from dual;

CHR(75)
--------
K

◈ 2개의 문자값을 결합합니다.
SQL> select concat(concat(ename, ' is a '), job) from emp;

CONCAT(CONCAT(ENAME,'ISA'),JOB)
--------------------------------------------------
SMITH is a CLERK
ALLEN is a SALESMAN
...................................................

◈ 정의된 문장 단어의 첫 번째 문자를 대문자로 변환
SQL> select initcap('the soap') from dual;

INITCAP('THESOAP
----------------
The Soap

◈ 정의된 문장을 모두 소문자로 변환합니다.
SQL> select lower('MR. samuel hillhouse') from dual;

LOWER('MR.SAMUELHILLHOUSE')
----------------------------------------
mr. samuel hillhouse

◈ 정의된 문장을 모두 대문자로 변환합니다.
SQL> select upper('mr. SAMUEL hillhouse') from dual;

UPPER('MR.SAMUELHILLHOUSE')
----------------------------------------
MR. SAMUEL HILLHOUSE


===============================================================================
※ 기타함수
===============================================================================

◎  PL/SQL에서 사용되는 IF-THEN-ELSE 문장과 유사한 방법으로 해독
DECODE함수는 각각의 search 값과 비교한 다음에 표현식(expression)을 해독
만약 표현식이 Search와 같으면, result를 리턴한다.
DECODE(expression, search, result, default)
ex)select ename
             ,deptno
             ,decode (deptno, 10, 'AC'
                                     ,20, 'RE'
                                     ,30, 'SA'
                                     ,40, 'OP',
                                           'NONE')
       from emp;

◎ 해당날짜가 속한 달의 마지막 날짜를 반환합니다.
LAST_DAY(sysdate)....
ex)select sysdate today
            , last_day(sysdate)
        from dual;
 

출처 : http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1010&seq=69&page=1&position=1


년도와 월이 주어졌을 경우, 해당 월의 달력을 생성해주는 쿼리를 만들어 보자.
쿼리는 아래와 같다. (2000년 01월이 주어졌을 경우)


SELECT   MIN (DECODE (TO_CHAR (days, 'd'), 1, TO_CHAR (days, 'fmdd'))) 일,
         MIN (DECODE (TO_CHAR (days, 'd'), 2, TO_CHAR (days, 'fmdd'))) 월,
         MIN (DECODE (TO_CHAR (days, 'd'), 3, TO_CHAR (days, 'fmdd'))) 화,
         MIN (DECODE (TO_CHAR (days, 'd'), 4, TO_CHAR (days, 'fmdd'))) 수,
         MIN (DECODE (TO_CHAR (days, 'd'), 5, TO_CHAR (days, 'fmdd'))) 목,
         MIN (DECODE (TO_CHAR (days, 'd'), 6, TO_CHAR (days, 'fmdd'))) 금,
         MIN (DECODE (TO_CHAR (days, 'd'), 7, TO_CHAR (days, 'fmdd'))) 토
    FROM (SELECT     base_mon + LEVEL - 1 days,
                         (  TRUNC (base_mon + LEVEL - 1, 'd')
                          - TRUNC (TRUNC (base_mon + LEVEL - 1, 'y'), 'd')
                         ) / 7 + 1 week_grp
                FROM (SELECT TO_DATE ('200001', 'yyyymm') base_mon
                        FROM DUAL)
          CONNECT BY base_mon + LEVEL - 1 <= LAST_DAY (base_mon))
GROUP BY week_grp
ORDER BY week_grp



connect by level 절을 사용하지 못하는 버전일 경우에는, 아래와 같이 dict 테이블을 이용한다.
아래 예는 위의 쿼리 와는 다르게 오늘로부터 몇달 후인지를 나타내는 변수 :n에 따라 달력을 생성한다.
(:n=-1 일 경우 지난달, 0은 이번달, 1은 다음달, 2는 다다음달,...)


SELECT   MIN (DECODE (TO_CHAR (days, 'd'), 1, TO_CHAR (days, 'fmdd'))) 일,
         MIN (DECODE (TO_CHAR (days, 'd'), 2, TO_CHAR (days, 'fmdd'))) 월,
         MIN (DECODE (TO_CHAR (days, 'd'), 3, TO_CHAR (days, 'fmdd'))) 화,
         MIN (DECODE (TO_CHAR (days, 'd'), 4, TO_CHAR (days, 'fmdd'))) 수,
         MIN (DECODE (TO_CHAR (days, 'd'), 5, TO_CHAR (days, 'fmdd'))) 목,
         MIN (DECODE (TO_CHAR (days, 'd'), 6, TO_CHAR (days, 'fmdd'))) 금,
         MIN (DECODE (TO_CHAR (days, 'd'), 7, TO_CHAR (days, 'fmdd'))) 토
    FROM (SELECT base_mon + ROWNUM - 1 days,
                     (  TRUNC (base_mon + ROWNUM - 1, 'd')
                      - TRUNC (TRUNC (base_mon + ROWNUM - 1, 'y'), 'd')
                     )
                   / 7
                 + 1 week_grp
            FROM (SELECT TRUNC (ADD_MONTHS (SYSDATE, :n), 'mm') base_mon
                    FROM DUAL), dict
           WHERE base_mon + ROWNUM - 1 <= LAST_DAY (base_mon))
GROUP BY week_grp
ORDER BY week_grp

SQL 분석함수 http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1030&seq=2


more..