오라클 자료 추가_수정_삭제(INSERT, UPDATE, DELETE,MERGE)

■ 테이블에 데이터 추가
    INSERT INTO 테이블명 [(컬럼명1, 컬럼명2, ...)] VALUES (값1, 값2, ...)

    한개의 테이블에 하나의 행만 추가 할 수 있다.

    오라클은 INSERT, UPDATE, DELETE 명령을 실행하면 자동으로 트랜잭션(작업)이 완료된 상태가 아니므로 다음중 하나을 이용하여 트랜잭션을 완료 해야 한다.

    COMMIT;    -- 트랜잭션 완료(INSERT, UPDATE, DELETE 완료)
                      --       데이터베이스에 저장
    ROLLBACK;  -- 트랜잭션 취소(INSERT, UPDATE, DELETE 취소)
                      --       데이터베이스에 저장 되지 않음

   sqlgate 에서 날짜를 문자 형으로 추가 할때 오류 발생하는 경우 TO_DATE() 함수를 이용한다.
   
   예 : INSERT INTO test1(num, birth) VALUES (1, TO_DATE('2000-10-10', 'YYYY-MM-DD'));

   CREATE TABLE ex1(
       num NUMBER
       ,name VARCHAR2(10)
       ,birth DATE
       ,age NUMBER(3)
   );

   SELECT * FROM ex1;
   DESC ex1;

INSERT INTO ex1 VALUES (1, '지수', '1980-02-02', 35);
   -- 주의 : 테이블작성할때 컬럼 순서에 맞게
SELECT * FROM ex1;
INSERT INTO ex1 VALUES (2, '자바', '2000-10-10');
   -- 에러 : 테이블컬럼의개수와 값의 개수가 불일치
INSERT INTO ex1 (num, name, birth) VALUES (2, '자바', '2000-10-10');
SELECT * FROM ex1;

INSERT INTO ex1 (num, name, age, birth) VALUES (3, '자바자바', 10, '2001-10-10');
   -- 에러 : 이름은 10byte인데 12byte를 추가해서 오류
   -- 한글한자는 3byte

INSERT INTO ex1 (num, name, age, birth) VALUES (3, '자바자', 1000, '2001-10-10');
    -- 오류:나이는 3자리

INSERT INTO ex1 (num, name, age, birth) VALUES (3, '자바자', 999, '011010');

INSERT INTO ex1 (num, name, age, birth) VALUES (4, ' 스프링', 33, '01-10-10 10:10');
   -- 오류:날짜형식

INSERT INTO ex1 (num, name, age, birth) VALUES (4, ' 스프링', 33, TO_DATE('01-10-10 10:10', 'RR-MM-DD HH:MI'));

COMMIT; -- 트랜잭션완료

INSERT INTO ex1 (num, name) VALUES (7, '머');
SELECT * FROM ex1;
ROLLBACK; -- 추가취소
SELECT * FROM ex1;
 


-- 서브쿼리를 이용한 여러행 추가
    INSERT INTO 테이블명 [(컬럼명, 컬럼명, ...)] subquery;

CREATE TBALE insa2 AS
   SELECT num, name, ssn FROM insa
               WHERE 1=0;  -- 데이터는 복사하지 않고 구조만 복사(제약조건은 복사된지않음)

DESC insa2;
SELECT * FROM insa2;

-- 존재하는 테이블에 서브쿼리를 이용하여 복사
INSERT INTO insa2 
    SELECT num, name, ssn FROM insa
    WHERE city='서울';
SELECT * FROM insa2;

DROP TABLE insa2;

//*****************************************
-- 서브쿼리를 이용한 하나의 INSERT문을 이용 여러 테이블에 데이터 추가
INSERT ALL | FIRST
   [INTO 테이블_1 VALUES (컬럼_1, 컬럼_2,...)]
   [INTO 테이블_2 VALUES (컬럼_1, 컬럼_2,...)]
    .......
Subquery;

 - ALL : 서브 쿼리의 결과 집합을 해당하는 insert 절에 모두 입력
 - FIRST : 서브 쿼리의 결과 집합을 해당하는 첫 번째 insert 절에 입력

CREATE TABLE insa2 AS
   SELECT num,name,ssn FROM insa WHERE 1=0;

CREATE TABLE insa3 AS
   SELECT num,basicpay,sudang FROM insa WHERE 1=0;

INSERT ALL
    INTO insa2 VALUES(num,name,ssn)
    INTO insa3(num,basicpay,sudang) VALUES(num,basicpay,sudang)
    SELECT num,name,ssn,basicpay,sudang FROM insa
       ;

SELECT * FROM insa2;
SELECT * FROM insa3;

DROP TABLE insa2;
DROP TABLE insa3;


//*****************************************
-- 서브쿼리를 이용한 조건에 만족하는 자료를 여러 테이블에 추가
  INSERT ALL
        WHEN 조건절_1 THEN
            INTO [테이블_1] VALUES (컬럼_1, 컬럼_2, ...)
        WHEN 조건절_2 THEN
            INTO [테이블_2] VALUES (컬럼_1, 컬럼_2, ...)
            ........
        ELSE
            INTO [테이블_3] VALUES (컬럼_1, 컬럼_2, ...)
  Subquery;

CREATE TABLE insa2 AS
   SELECT * FROM insa WHERE 1=0;
CREATE TABLE insa3 AS
   SELECT * FROM insa WHERE 1=0;
CREATE TABLE insa4 AS
   SELECT * FROM insa WHERE 1=0;

-- 서울사람은 insa2에, 경기사람은 insa3에, 나머지는 insa4에
  INSERT ALL
        WHEN city='서울' THEN
            INTO insa2 (num,name,city, ssn, ibsadate,buseo,jikwi, basicpay, sudang) VALUES (num,name,city, ssn, ibsadate,buseo,jikwi, basicpay, sudang)
        WHEN city='경기' THEN
            INTO insa3 (num,name,city, ssn, ibsadate,buseo,jikwi, basicpay, sudang) VALUES (num,name,city, ssn, ibsadate,buseo,jikwi, basicpay, sudang)
        ELSE
            INTO insa4 (num,name,city, ssn, ibsadate,buseo,jikwi, basicpay, sudang) VALUES (num,name,city, ssn, ibsadate,buseo,jikwi, basicpay, sudang)
  SEELCT * FROM insa;




■ 테이블의 데이터 수정
UPDATE 테이블명  SET 컬럼명= 변경할값[, 컬럼명= 변경할값, ...]  [WHERE 조건];

조건을 주지 않으면 모든 행의 컬럼값을 수정한다.
한번에 하나의 테이블만 수정 할 수 있다.

CREATE TABLE insa1 AS SELECT * FROM insa;

-- insa1 테이블에 2개의 컬럼을 추가
   pay : NUMBER(9)
   tax :  NUMBER(9)

ALTER TABLE insa1 ADD(pay NUMBER(9),
    tax NUMBER(9));
DESC insa1;

select * FROM insa1;

-- 테이블의 모든 데이터가 수정
UPDATE insa1 SET pay=basicpay+sudang,
            tax=(basicpay+sudang)*0.02;
select * FROM insa1;

commit;

- 여자사원의 sudang을 100000만원 추가
UPDATE insa1 SET sudang=sudang+100000
       WHERE SUBSTR(ssn,8,1) IN (2,4);
commit;

 
//*****************************************
■ 테이블의 데이터 삭제
   DELETE [FROM] 테이블명 [WHERE 조건];

   조건을 주지 않으면 모든 정보가 삭제 된다.

-- insa1의 자료중 city가 서울인 자료 삭제
DELETE FROM insa1 WHERE city='서울';

-- insa1의 모든 자료 삭제(구조는 삭제 되지 않음)
DELETE FROM insa1;
COMMIT;

SELECT * FROM insa1;
DESC insa1;


-- 모든 자료 삭제
TRUNCATE TABLE 테이블명;
   -- 모든 자료를 삭제하는 경우 DELETE보다 빠름
   -- ROLLBACK 불가(자동 COMMIT)

TRUNCATE TABLE ex1;
SELECT * FROM ex1;

DROP TABLE ex1;
DROP TABLE insa1;


 

■ MERGE(병합)
MERGE INTO 테이블_명 별칭
  USING 대상테이블/뷰 별칭
  ON ( 조인조건 )
  WHEN MATCHED THEN
    UPDATE SET
        컬럼_1=값_1
        ,컬럼_2=값_2
  WHEN NOT MATCHED THEN
    INSERT (컬럼_1, 컬럼_2, ...)
        VALUES(값_1, 값_2, ...);

CREATE TABLE test1 AS SELECT num, name, basicpay, sudang FROM insa WHERE city='서울';

CREATE TABLE test2 AS SELECT num, name, sudang FROM insa WHERE buseo='개발부';

SELECT * FROM test1;
SELECT * FROM test2;

MERGE INTO test2 t2
  USING test1 t1
  ON ( t1.num = t2.num )
  WHEN MATCHED THEN
      UPDATE SET t2.sudang=t1.sudang+t2.sudang
  WHEN NOT MATCHED THEN
      INSERT (t2.num, t2.name, t2.sudang)
           VALUES(t1.num, t1.name, t1.sudang);
COMMIT;

SELECT * FROM test1;
SELECT * FROM test2;