가끔 데이터 조회 결과를 1:N 인데 1:1 로 합쳐서 표시할때 유용하다.

예제는 다음과 같다.


/* 엘클라시코 포워드 라인업 */
/* 오라클 11g 기준 문자열 연결 예제 (LISTAGG .. ) */
WITH EL_CLASICO_FORWARD_LINE_UP AS (
  SELECT
    '크리스티아누 호날두' AS PLAYER,
    '레알 마드리드' AS CLUB
  FROM DUAL
  UNION ALL
  SELECT
    '가레스 베일',
    '레알 마드리드'
  FROM DUAL
  UNION ALL
  SELECT
    '카림 벤제마',
    '레알 마드리드'
  FROM DUAL
  UNION ALL
  SELECT
    '리오넬 메시',
    'FC 바르셀로나'
  FROM DUAL
  UNION ALL
  SELECT
    '네이마르',
    'FC 바르셀로나'
  FROM DUAL
  UNION ALL
  SELECT
    '루이스 수아레즈',
    'FC 바르셀로나'
  FROM DUAL  
)
SELECT 
  CLUB,
  LISTAGG(PLAYER, '/') WITHIN GROUP(ORDER BY CLUB) AS FORWARDS
FROM EL_CLASICO_FORWARD_LINE_UP
GROUP BY CLUB;


※ 결과


저작자 표시
신고

먼저 DIRECT-PATH INSERT 에 대한 구조를 대략적으로나마 이해하면 좋을 듯 싶다.


나도 데이토의 올라온 내용을 따라해보고 까먹을까봐 일단 예제 순서만 메모.

  1. 해당 세션에 패러렐 DML 허용;
  2. 임시 테이블에 로깅해제;
  3. APPENT 힌트 사용
  4. 테이블 로깅;
  5. 다시 패러렐 DML 비허용

여기에 대한 관련 링크들은 다음과 같음.



저작자 표시
신고

오라클에서 쿼리 수행계획을 찾다보니 확인하고,
다음과 같이 샘플 예제를 넣어서 확인해버릇 해야겠다.


내가 이해하기로는 크게 3 블럭으로 나눈다.

  1. PLAN_TABLE(플랜계획내용을 저장하는 테이블) 에 STATEMENT_ID 를 부여
  2. 실행할 쿼리
  3. 플랜계획내용 조회
SET SERVEROUTPUT ON;
EXPLAIN PLAN SET statement_id = 'EX1' INTO PLAN_TABLE FOR

SELECT * FROM "대상테이블" /* 수행계획을 확인할 대상쿼리 */

SELECT * FROM PLAN_TABLE
WHERE STATEMENT_ID = 'EX1'

위 쿼리가 실행할 샘플 예제 입니다.


참고링크 : http://najuung.tistory.com/56

저작자 표시
신고

Mac OS 에서 sqldeveloper 로 접속시 locale not recognized 에러머세지로 연결불가.

아마 로케일 문제인듯.

설치된 sqldeveloper 는 영문판임.

구글링중 발견.


{sqldeveloper 설치위치}/Contents/Resources/sqldeveloper/sqldeveloper/bin/sqldeveloper.conf


파일을 열고 VMOption 에서 


AddVMOption -Duser.language=ko

AddVMOption -Duser.country=KR 


이분은 친절한 캡쳐포스팅 : http://ending1.tistory.com/49

저작자 표시
신고
  1. 소액결제 현금화 2017.12.07 04:19 신고

    감사합니다 ^&^

오라클에서 데이터를 다중 조건에 맞게 업데이트하기 위한 방법중 첫번째.

SET 절과 (WHERE, AND) 조건 절과 동일한 내용으로 업데이트 쿼리를 수행.


샘플 데이터는 다음과 같다.


테이블 : JKUN

위 테이블에서 업데이트 대상은 NUM_1 필드(전체)이다.


이 데이터들을 순번으로 업데이트 할 예정.

다음은 가상 데이터

    SELECT 1 AS RN, 4 AS NUM_SEQ
    FROM DUAL
    UNION ALL
    SELECT 2 AS RN, 5 AS NUM_SEQ
    FROM DUAL
    UNION ALL
    SELECT 3 AS RN, 6 AS NUM_SEQ
    FROM DUAL
    UNION ALL
    SELECT 4 AS RN, 7 AS NUM_SEQ
    FROM DUAL
    UNION ALL
    SELECT 5 AS RN, 8 AS NUM_SEQ
    FROM DUAL

    /* 위 가상 데이터를 지속적으로 인라인 뷰 */

위 데이터들은 NUM_SEQ 를 기준으로 NUM_1 을 수정한다. 이 첫번째 케이스로 업데이트를 수행하기 위해서는 반드시 조건/기준 컬럼이 필요.


UPDATE JKUN
SET NUM_1 = (
  SELECT RN FROM (
    SELECT 1 AS RN, 4 AS NUM_SEQ
    FROM DUAL
    UNION ALL
    SELECT 2 AS RN, 5 AS NUM_SEQ
    FROM DUAL
    UNION ALL
    SELECT 3 AS RN, 6 AS NUM_SEQ
    FROM DUAL
    UNION ALL
    SELECT 4 AS RN, 7 AS NUM_SEQ
    FROM DUAL
    UNION ALL
    SELECT 5 AS RN, 8 AS NUM_SEQ
    FROM DUAL
  ) RRD
  WHERE RRD.NUM_SEQ = JKUN.NUM_SEQ
)
WHERE EXISTS (
  SELECT 1
  FROM (
    SELECT 1 AS RN, 4 AS NUM_SEQ
    FROM DUAL
    UNION ALL
    SELECT 2 AS RN, 5 AS NUM_SEQ
    FROM DUAL
    UNION ALL
    SELECT 3 AS RN, 6 AS NUM_SEQ
    FROM DUAL
    UNION ALL
    SELECT 4 AS RN, 7 AS NUM_SEQ
    FROM DUAL
    UNION ALL
    SELECT 5 AS RN, 8 AS NUM_SEQ
    FROM DUAL
  ) RD
  WHERE NUM_SEQ = JKUN.NUM_SEQ
);

위 쿼리를 서술형으로 풀어서 정리하자면

JKUN 테이블에 있는 NUM_1 컬럼의 값들을 JKUN 테이블의 NUM_SEQ 컬럼과 인라인 뷰의 NUM_SEQ 컬럼과 매칭하여 RN 컬럼 값을 JKUN 테이블의

NUM_1 값들을 일괄적으로 수정.




저작자 표시
신고

WITH 구문내의 쿼리 결과를 Alias 를 지정하여 이어서 SELECT 절과 이용.

옵티마이저에서 인라인뷰나 임시테이블로 여김.

WITH WITH_EX AS
(
  SELECT ROWNUM R, 'TEST1' STR, SYSDATE DT FROM DUAL
  UNION ALL
  SELECT ROWNUM, 'TEST2', SYSDATE FROM DUAL
  UNION ALL
  SELECT ROWNUM, 'TEST3', SYSDATE FROM DUAL
)
SELECT * FROM WITH_EX;


조인도 가능

SELECT * FROM WITH_EX A LEFT OUTER JOIN (
  SELECT 1 R, 'TESTSSSSS' STR, SYSDATE DT FROM DUAL
  UNION ALL
  SELECT 2 R, 'TESTSSSSS' STR, SYSDATE DT FROM DUAL
) B
ON A.R = B.R;

저작자 표시
신고

오라클 시퀀스 정리


아래 내용은 심플하게 정리된 포스팅입니다.


출처 : 오라클 sequence 의 사용이유와 주의점 ( http://javakorea.tistory.com/175 )

시퀀스 사용이유가 데이터 입력시 동시성 때문에 register가 겹칠때 이를 방지 하기 위해 commit이 완료되지 않더라도 시퀀스를 사용하면 여러사용자가 중복되지않은 register(회원가입번호) 를 얻을수있어서 정상적 으로 회원가입이 가능하다. syntax) create sequence 시퀀스명 minvalue 1 // 최소 생성 시퀀스값 max value 99999999999 // 최대 생성 increment by 1 // 증가값 이값은 max-min보다 작아야함 start with 207 // 시작 시퀀스값 nocache // 시퀀스 생성을 위해 미리 값을 할당하는것, 동시성에 좋은 옵션, no하면 미리 할당X noorder//요청되는 순서대로 값을 생성함, 디폴트값은 no nocycle//생성된 시퀀스값이 최대치 혹은 최소치(감소시)에 다다랐을때 초기값부터 다시 시작할지 물어봄 디폴트값은 no . cycle로 돌아가면 minvalue로 돌아감 시퀀스명 " " 을 넣으면 대소문자 구분을 할 수 있다 근데 문제점은 "aaa" 를 만들고 drop sequence aaa 하면 자동으로 AAA 라고 인식을 해서 지워지지 않는다. 하이 테이블에 a시퀀스를 쓰고 헬로 테이블에 a시퀀스를 사용하면 a시퀀스에 현재 저장된 last_number를 따라가기 때문에 테이블,컬럼,데이터 입력시 원하는 시퀀스를 헷갈리지않게 명칭을 잘 정해야할것


저작자 표시
신고



보통 쿼리 (SQL스크립트) 를 짜거나, 이미 작성되있는 쿼리가 정렬이 안되어있으면
상당히 짜증이 치민다.
그런데 Toad For 씨리즈들은 라이센스가... 회사에서 사용하기에는....
그래서 불편함을 참다가.. 우연이 찾았다.
FreeSQLFormatter .
SQL 스크립트를 옵션에 맞게 정렬해주는 심플한 유틸리티.
고마운 소스포지~ㅋㅋㅋ
아래압축파일을 받아서 해당 OS 에 맞는 버전으로 압축을 해제하고 실행하면 된다.



해당 다운로드 링크 출처는  http://sourceforge.net/projects/fsqlf/ 

 
저작자 표시
신고

'DataBase' 카테고리의 다른 글

[ER-WIN] Names Option  (0) 2016.10.25
memsql  (0) 2013.12.30
FreeSQLFormatter [SQL정렬]  (0) 2012.02.13
NoSQL  (0) 2011.01.14
ERStudio  (0) 2010.08.24
오라클에 돌고 있는 쿼리 시간 및 쿼리 확인하기

  SELECT TO_CHAR (SID) sid, serial# serialNumber,
   SUBSTR (TO_CHAR (last_call_et), 1, 6) executeSeconds, userName, machine,
   b.sql_text sqlText
  FROM v$session a, v$sqltext b
  WHERE username NOT IN ('SYSTEM', 'SYS')
   AND a.TYPE != 'BACKGROUND'
   AND a.status = 'ACTIVE'
   AND a.sql_address = b.address(+)
   AND a.sql_hash_value = b.hash_value(+)
  ORDER BY a.last_call_et DESC,
   a.SID,
   a.serial#,
   b.address,
   b.hash_value,
   b.piece
   

이 쿼리를 돌리면 현재 오라클에서 돌고 있는 쿼리와 수행 시간을 알 수 있다.

다만 저 쿼리를 돌리는 계정이 $session과 $sqltext 를 확인할 수 있는 권한이 있어야 한다.

저작자 표시
신고


 시퀀스란?
 

◈ 유일(UNIQUE)한 값을 생성해주는 오라클 객체입니다. 

◈ 시퀀스를 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로

생성할수 있습니다. 

◈ 보통 primary key 값을 생성하기 위해 사용합니다. 

◈ 메모리에 Cache되었을 때 Sequence 값의 액세스 효율이 증가 합니다. 

◈ Sequence는 테이블과는 독립적으로 저장되고 생성됩니다. 따라서 하나의 sequence를 
    여러 테이블에서 쓸 수 있습니다.
 



시퀀스 생성
 




START WITH : 시퀀스의 시작 값을 지정합니다. n을 1로 지정하면 1부터 순차적으로 
                     시퀀스번호가 증가 합니다. 

INCREMENT BY : 시퀀스의 증가 값을 말합니다. n을 2로 하면 2씩 증가합니다. 
                        START WITH를 1로 하고 INCREMENT BY를 2으로 하면 1, 3, 5,7,.. 이렇게 
                       시퀀스  번호가 증가하게 됩니다. 

MAXVALUE n | NOMAXVALUE : MAXVALUE는 시퀀스가 증가할수 있는 최대값을 말합니다.                                             NOMAXVALUE는 시퀀스의 값을 무한대로 지정합니다. 

MINVALUE n | NOMINVALUE : MINVALUE는 시퀀스의 최소값을 지정 합니다. 
                                      기본값은 1이며, NOMINVALUE를 지정할 경우 최소값은 무한대가 됩니다 

 


 SQL>CREATE SEQUENCE emp_seq 
        START WITH 1 
        INCREMENT BY 1 
        MAXVALUE 100000 ; 

      sequence created. 

     시작 값이 1일고 1씩 증가하고, 최대값이 100000만이 되는 시퀀스를 생성했습니다. 


 SQL>INSERT INTO emp(empno, ename, hiredate ) VALUES(emp_seq.NEXTVAL, 'julia' , sysdate); 

    empno는 컬럼값을 입력할 때 일일이 다음 값을 기억하지 않아도 NEXTVAL을 사용하여 
    자동으로 입력할 수 있습니다. 

  CURRVAL : 현재 값을 반환 합니다. . 
   NEXTVAL : 현재 시퀀스값의 다음 값을 반환 합니다. 


 SQL>SELECT emp_seq.CURRVAL FROM DUAL ; 

        CURRVAL 
        --------- 
                  1 

 SQL>SELECT emp_seq.NEXTVAL FROM DUAL ; 

       NEXTVAL 
     --------- 
                 2
 



사용규칙 


  ◈ NEXTVAL, CURRVAL을 사용할 수 있는 경우
    - subquery가 아닌 select문
    - insert문의 select절
    - insert문의 value절
    - update문의 set절


  ◈ NEXTVAL, CURRVAL을 사용할 수 없는 경우
    - view의 select절
    - distinct 키워드가 있는 select문
    - group by, having, order by절이 있는 select문
    - select, delete, update의 subquery
    - create table, alter table 명령의 default값


시퀀스의 수정 및 삭제
 




START WITH는 수정할수 없습니다
START WITH 절이 없다는 점을 빼고는 CREATE SEQUENCE와 같습니다. 
 


 SQL>ALTER SEQUENCE emp_seq 
         INCREMENT BY 2 
         CYCLE; 

      sequence altered. 

      2씩 증가하고, 최대값을 넘으면 다시 처음부터 순환하도록 수정하였습니다. 


      DROP 문을로 필요하지 않은 시퀀스는 삭제 할수 있습니다. 

 SQL>DROP SEQUENCE PRD_SEQ; 
        sequence dropped.
 

  ================================================ 
    * 오라클 정보공유 커뮤니티 oracleclub.com 
    * http://www.oracleclub.com 
    * http://www.oramaster.net 
    * 운영자 : 김정식 (oramaster _at_ naver.com)
  ================================================
※ oracleclub 강좌를 다른 홈페이지에 퍼가실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

 

 

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

 

오라클 자동증가(Create sequence) 오라클 시퀀스 생성

 

MS-SQL에서 테이블 생성시 자동증가할수 있는 명령어가 있습니다.
근데 오라클은 없냐고 반문하시면 비슷하게 한번 만들어 보겠습니다.

 

아래와 같이 SEQUENCE 를 생성하고 인서트할때 시퀀스명.NEXTVAL을 이용하면 됩니다.

 

 

1. SEQUENCE 생성


   create sequence autonum 
     increment by 1   -- 1씩 증가하라는거고요
     start with 1        -- 1부터 시작이고요 100 시작할려면 100 이라고 쓰시면 됩니다.
     nomaxvalue       -- 최대값 제한이 없다
     nocycle             
     nocache;
 
DEFAULT VALUE를 이용할려고 했는데 이것도 안됩니다.

create table test(num number default autonum.nextval, name varchar2(10)); 
1행에 오류:
ORA-00984: 열을 사용할 수 없습니다

 

 

 

2. TABLE 생성


  create table test(num number, name varchar2(10));

 

 

 

3. 자동증가값 생성


  입력시 다음과 같이 해주시면 됩니다.
  insert into test (num)  values (autonum.nextval);

 

조회 해보면 1 값이 들어가 있습니다.


select * from test

       NUM NAME
---------- ----------
         1

 

삭제는 drop sequence autonum; 이렇게 하시면 됩니다.

 

유의할 사항은 시퀀스명.Nextval 하면 전에 값으로 되돌릴수 없습니다.

현재값은 시퀀스명.CurrVal 로 조회 하시면 됩니다.

 

select 시퀀스명.currval from dual;


 

 

 

 

 

 

 

# SEQUENCE - 같은말 반복


 [ 대부분 SEQ 사용 ]
-----------------------------------------------------------------------------
CREATE SEQUENCE SEQ1
START WITH 1
INCREMENT BY 1
NOMAXVALUE
NOMINVALUE
NOCYCLE
NOCACHE

 


 [ 1 ] SEQUENCE 생성 -> 하나의 Object
-----------------------------------------------------------------------------


CREATE SEQUENCE SEQ1
START WITH 1              // 처음 시작값
INCREMENT BY 1         // 1씩 증가
MAXVALUE 1E27          // 최대값
MINVALUE 1                // 최소값(다시 시작할때는 1부터 시작)
CYCLE                       // 최대값이 지나면 다시 시작해서 사용
NOCACHE                  // 캐쉬상 메모리에 올려놓지 않는다

 

 


 [ 2 ] 지우거나 수정
-----------------------------------------------------------------------------


DROP SEQUENCE SEQ1;

ALTER SEQUENCE SEQ1 CYCLE;
-> START WITH는 ATER로 변경할 수 없다.

 

 


 [ 3 ] 테이블 생성 및 SEQ1 적용
-----------------------------------------------------------------------------


CREATE TABLE SEQTEST (
             NO NUMBER, 
             NAME VARCHAR2(10)

);

CREATE TABLE SEQTEST2
AS
SELECT * FROM SEQTEST;

 

 


 [ 4 ] SEQUENCE의 마지막 값 조회
------------------------------------------------------------------------------

SELECT LAST_NUMBER

   FROM USER_SEQUENCES
 WHERE SEQUENCE_NAME = 'SEQ1';

 

 


 [ 5 ] SEQUENCE의 현재 값 조회
------------------------------------------------------------------------------


SELECT SEQ1.CURRVAL

   FROM DUAL;

 

 

 


 

 ▶ SEQUENCE를 사용할 수 없는 경우 : SEQUENCE는 PL/SQL과 SQL 코딩시 몇몇 제한되게 사용됨.
-----------------------------------------------------------------------------------------------

 

 ex 1 )

 

INSERT INTO TEST (AAA, BBB)

SELECT AAA, TEST_SEQ.NEXTVAL

   FROM TEST

 ORDER BY BBB;


[ORA-02287: sequence number not allowed here] 라는 에러를 발생할 것이다.

 

 ex 2 )

 

SELECT TEST_SEQ.NEXTVAL FROM DUAL

UNION ALL

SELECT TEST_SEQ.NEXTVAL FROM DUAL 

 

[ORA-02287: sequence number not allowed here]

 

 

 ex 3 )

 

SELECT COUPON_STR,
             COUPON_SEQ 
   FROM AA
 WHERE GAMEID = 'high'
      AND COUPON_SEQ  = AA_SQ1.NEXTVAL

 

 

 ex 4 ) 등등.. 그룹함수도 마찬가지...

 

 

 ▶ SEQUENCE 사용 불가


 SELECT 문

 

    1. WHERE 절 안에서
    2. GROUP BY나 ORDER BY 절에서
    3. DISTINCT 절에서
    4. UNION 이나 INTERSECT 나 MINUS 와 함께 사용 할때
    5. 서브 쿼리 안에서

 기타

 

    6. UPDATE나 DELETE 의 서브쿼리
    7. VIEW나 SNAPSHOPT 에서
    8. 테이블 정의시 CHECK 조건이나 DEFAULT 에서

 

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

 

 회피 방법 생각해 볼 문제....

 

 

 

 

 

 ▶ SEQUENCE 초기화

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

 ※ 힌트 : 증가된 시퀀스번호만큼 빼주어 값을 초기화하는 프로지져를 생성한다.

     http://blog.naver.com/jadin1 참고.

 

CREATE OR REPLACE PROCEDURE P_RESET_SEQ(SEQ_NAME IN VARCHAR2)
IS
  L_VAL NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT ' || SEQ_NAME || '.NEXTVAL FROM DUAL ' INTO L_VAL;
  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || SEQ_NAME || ' INCREMENT BY -' || L_VAL || ' MINVALUE 0';
  EXECUTE IMMEDIATE 'SELECT ' || SEQ_NAME || '.NEXTVAL FROM DUAL ' INTO L_VAL;
  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || SEQ_NAME || ' INCREMENT BY 1 MINVALUE 0';
END;


출처 : Tong - 병조님의 Oracle통

저작자 표시
신고

+ Recent posts