dBack

MERGE [ hint ]

   INTO [ schema. ] { table | view } [ t_alias ]

   USING { [ schema. ] { table | view }

         | subquery

         } [ t_alias ]

   ON ( condition )

WHEN MATCHED THEN

UPDATE SET column = { expr | DEFAULT }

           [, column = { expr | DEFAULT } ]...

[ DELETE where_clause ]

WHEN NOT MATCHED THEN

INSERT [ (column [, column ]...) ]

VALUES ({ expr [, expr ]... | DEFAULT })




• - INTO : DATA가 UPDATE되거나 INSERT 될 테이블 또는 뷰를 지정.
• - USING : 비교할 SOURCE 테이블 또는 뷰나 서브쿼리를 지정, INTO절의 테이블과 동일하거나 다를 수 있다.
• - ON : UPDATE나 INSERT를 하게 될 조건으로, 해당 조건을 만족하는 DATA가 있으면 WHEN MATCHED 절을 실행하게 되고, 없으면 WHEN NOT MATCHED 이하를 실행하게 된다.
• - WHEN MATCHED : ON 조건절이 TRUE인 ROW에 수행 할 내용 (UPDATE, DELETE포함 될 수 있음)
• - WHEN NOT MATCHED : ON 조건절에 맞는 ROW가 없을 때 수행할 내용 (INSERT)

SUBSTR : 문자열 자르기

SUBSTR(사용할 문자열 OR 숫자 , 자를위치 , [ 자를길이 ])

SELECT  SUBSTR('YYYYMMDD', 1, 4) --1부터 4까지
결과값  : YYYY

SELECT SUBSTR('YYYYMMDD', 4) --4를 포함한 전부

결과값 : MMDD

INSTR : 찾고자 하는 문자열의 인덱스를 리턴
INSTR('검색대상문자열', '찾고자하는 문자열', '시작할 인덱스' [,몇번째 발견한 것인지]')

INSTR(JOIN_ACCESS_POINT_CD,'007','1','1') = 0

1부터 시작해서 007이라는값이있는 인덱스를 반환

없으면 0

◈ Out(외부) Join 

 - equijoin 문장들의 한가지 제약점은 그것들이 조인을 생성하려 하는 두 개의 테이블의 두개 컬럼에서 공통된 값이 없다면 테이블로부터 테이터를 Return하지 않는 다는 것입니다. 
 - 정상적으로 조인 조건을 만족하지 못하는 행들을 보기위해 outer join을 사용합니다. 
    Outer join 연산자 "( + )"입니다. 
 - 조인시킬 값이 없는 조인측에 "( + )"를 위치 시킵니다. 
 - Outer join 연산자는 표현식의 한 편에만 올 수 있습니다. 

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

예제1) 일반 조인의 경우 

SQL> SELECT DISTINCT(a.deptno), b.deptno
         FROM emp a, dept b
         WHERE  a.deptno = b.deptno

DEPTNO     DEPTNO
---------- ----------
        10         10
        20         20
        30         30


예제2)out join을 했을 경우

SQL>  SELECT DISTINCT(a.deptno), b.deptno
          FROM emp a, dept b
          WHERE  a.deptno(+) = b.deptno

 DEPTNO     DEPTNO
 -------     ----------
     10         10
     20         20
     30         30
                 40

※ 다음의 쿼리를 한번 잘 보시기 바랍니다. 

SQL>  SELECT DISTINCT(a.deptno), b.deptno
          FROM emp a, dept b
          WHERE  a.deptno(+) = b.deptno
               AND a.ename LIKE '%';

    DEPTNO     DEPTNO
---------- ----------
        10         10
        20         20
        30         30

쿼리 결과를 잘 보면 out조인이 되지 않은 것을 알 수 있습니다.
위 쿼리를 out조인이 되기 위해서는 아래와 같이 고쳐야 합니다. 

SQL> SELECT DISTINCT(a.deptno), b.deptno
         FROM emp a, dept b
         WHERE  a.deptno(+) = b.deptno
              AND a.ename(+) LIKE '%'

    DEPTNO     DEPTNO
---------- ----------
        10         10
        20         20
        30         30
                    40

OUT조인 조건이 걸려있는 테이블에는 다른 조건절이 들어와도 
똑같이 OUT조인 연산자인 (+)를 해주어야 합니다.  

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

Oracle9i 부터는 ANSI/ISO SQL표준인 LEFT OUTER JOIN , RIGHT OUTER JOIN, FULL OUTER JOIN를 지원 합니다.

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

☞ LEFT OUTER JOIN 
 왼쪽 테이블에 조인시킬 컬럽의 값이 없는 경우 사용합니다. 
  
SQL>SELECT DISTINCT(e.deptno), d.deptno 
    FROM dept d LEFT OUTER JOIN emp e 
    ON d.deptno = e.deptno; 
  
  
☞ RIGHT OUTER JOIN 
 - 오른쪽에 테이블에 조인시킬 컬럽의 값이 없는 경우 사용합니다. 
  
SQL>SELECT DISTINCT(a.deptno), b.deptno 
    FROM emp a RIGHT OUTER JOIN dept b 
    ON a.deptno = b.deptno; 
  
  
☞ FULL OUTER JOIN 
양쪽 테이블에 다 outer join을 거는것을 TWO-WAY OUTER JOIN 또는 FULL OUTER JOIN이라 합니다. 
  
SQL>SELECT DISTINCT(a.deptno), b.deptno 
    FROM emp a FULL OUTER JOIN dept b 
    ON a.deptno = b.deptno; 
  
-- 위 세 문장의 결과는 아래와 같습니다. 
    DEPTNO     DEPTNO 
---------- ---------- 
        10         10 
        20         20 
        30         30 
                   40 
  
LEFT OUTER JOIN과 RIGHT OUTER JOIN의 테이블 순서를 바꾸어 가면서 테스트를 하시면 쉽게 이해를 하실 수 있습니다. 

sql 참고

http://www.oracleclub.com/lecture/1021

# test table 생성

create table test (

name varchar(10)

);

# test table에 osy란 number(10)타입의 컬럼 추가

SQL > alter table test add(osy number(10));

# test table에 osy 컬럼명을 osy88 로 컬럼명 변경

SQL > alter table test rename column osy to osy88;

# test table에 osy88 컬럼 데이터 타입 변경

SQL > alter table test modify(osy88 varchar(10));

# test table에 osy88 컬럼 삭제

SQL > alter table test drop(osy88);

Unique Key를 생성하는 방법은 DBMS마다 차이가 있다.
MS-SQL은 IDENTITY를, MySQL은 auto_increment와 같이 쉽게 사용할 수 있는 방법이 있는데 오라클에서는 Sequence를 사용하여 다음과 같이 유사하게 구현할 수 있다

1. 자동증가컬럼을 사용하고자 하는 MYTABLE테이블을 생성한다.

       CREATE TABLE MYTABLE
               (ID NUMBER, NAME VARCHAR2(20));

2. CREATE SEQUENCE 라는 문장을 사용하여 SEQ_ID라는 이름의 시퀀스를 만든다.

       CREATE SEQUENCE SEQ_ID INCREMENT BY 1 START WITH 10000;

      -- INCREMENT BY 1 : 증가값은 1
      -- START WITH 10000 :  10000부터 증가

3.  테이블에 데이터 입력시에는 NEXTVAL이라는 슈도 칼럼(Pseudo-column)을 이용하여 시퀸스를 사용한다.

       INSERT INTO MYTABLE VALUES( SEQ_ID.NEXTVAL, '홍길동');

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

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

sequence와 trigger를 통해서 auto-increment를 생성 할 수 있다.

먼저, auto-increment를 시행해볼 table을 만든다.

CREATE TABLE templet(

seq NUMBER PRIMARY KEY,

str1 VARCHAR2(256) NOT NULL,

str2 VARCHAR2(256) NOT null

)

seq가 1로부터 시작해서 1씩 증가할 수 있도록 sequence를 생성한다. 

CREATE SEQUENCE templet_sequence

START WITH 1

INCREMENT BY 1;

테이블에 insert 할 때마다 seq column에 이 sequence 를 적용시킬 수 있도록 trigger를 걸어보도록 하자.

CREATE OR REPLACE TRIGGER templet_trigger

BEFORE INSERT

ON templet

REFERENCING NEW AS NEW

FOR EACH ROW

BEGIN

SELECT TEMPLET_SEQUENCE.NEXTVAL INTO :new.seq FROM dual;

END;

이제 테이블에 새로운 row가 insert 될 때마다 이 test_trigger가 실행될 것이고, 결과적으로 seq가 자동으로 1씩 증가하게 된다.

오라클의 Tablespace.

테이블이 저장되는 공간.

오라클에서는 테이블스페이스라고 하여 테이블이 저장될 공간을 먼저 만들고 나서 테이블을 생성 한다

각각의 테이블을 테이블스페이스별로 나누어서 관리와 퍼포먼스의 향상을 가지고 오는것 이다

테이블스페이스를 생성하면 정의된 용량만큼 미리확보한 테이블 스페이스가 생성 되어지고 생성되어진 테이블 스페이스에 테이블 데이터가 저장 된다.

테이블스페이스 생성

create tablespace webacc

datafile 'C:\oracle\product\10.2.0\oradata\orcl\test.dbf' size 100m

default storage

(INITIAL    10K

NEXT      10K

MINEXTENTS 2

MAXEXTENTS 50

PCTINCREASE 50)

;

Oracle을 사용하다보면 종종 유저 Lock이 걸리곤 한다. 

일단 root 권한인 sys로 oracle에 접속하자

sqlplus sys/manager as sysdba;

유저 락 해제

alter user soctt2 account unlock;

유저의 락을 해제하면 비번과 권한설정을 새로 해주어야 한다.

alter user scott2 identified by tiger2;

grant create session to scott2;

grant create sequence to scott2;

grant create table to scott2;

grant drop any table to scott2;

그리고 테이블을 생성/삭제 하려는데, 테이블스페이스의 권한설정이 새로 생성한 scott2에게 없다고 에러를 낸다.

그럴 때는 scott2 유저에게 테이블스페이스의 권한까지 주자.

alter user scott2 quota unlimited on webacc;

이렇게하면 에러가 나지 않을 것이다