③ INSERT문, UPDATE문, DELETE문, MERGE문 처리

SELECT문 뿐만 아니라 INSERT문, UPDATE문, DELETE문, MERGE문에 대해서도 동적 SQL을 사용할 수 있다. 먼저 다음과 같이 테이블을 생성해 보자.

입력

    CREATE TABLE ch13_physicist ( ids       NUMBER,
           names     VARCHAR2(50),
           birth_dt  DATE );

결과

    table CH13_PHYSICIST이(가) 생성되었습니다.

이제 ch13_physicist 테이블에 데이터를 넣는 INSERT문을 동적 쿼리로 만들어 보자. 바인드 변수 처리 방법을 배웠으니 앞으로는 모든 동적 SQL에서 바인드 변수를 사용할 것이다.

입력

    DECLARE
      vn_ids ch13_physicist.ids%TYPE := 10;
      vs_name ch13_physicist.names%TYPE := 'Albert Einstein';
      vd_dt ch13_physicist.birth_dt%TYPE := TO_DATE('1879-03-14', 'YYYY-MM-DD');

      vs_sql   VARCHAR2(1000);

    BEGIN
      -- INSERT문 작성
      vs_sql := 'INSERT INTO ch13_physicist VALUES (:a, :a, :a)';

      EXECUTE IMMEDIATE vs_sql USING vn_ids, vs_name, vd_dt;

      COMMIT;
    END;

테이블을 확인해 보면 데이터가 성공적으로 저장됐음을 알 수 있다. INSERT문에서도 동적 SQL의 처리 방법과 바인드 변수 사용법은 SELECT문과 동일하다. UPDATE문과 DELETE문 역시 같은 방식으로 처리된다.

입력

    DECLARE
      vn_ids ch13_physicist.ids%TYPE := 10;
      vs_name ch13_physicist.names%TYPE := 'Max Planck';
      vd_dt ch13_physicist.birth_dt%TYPE := TO_DATE('1858-04-23', 'YYYY-MM-DD');

      vs_sql VARCHAR2(1000);

      vn_cnt  NUMBER := 0;

    BEGIN
      -- UPDATE문
      vs_sql := 'UPDATE ch13_physicist
                    SET names = :a,
                        birth_dt = :a
                  WHERE ids = :a ';

      EXECUTE IMMEDIATE vs_sql USING vs_name, vd_dt, vn_ids;

      SELECT names
        INTO vs_name
        FROM ch13_physicist;

      DBMS_OUTPUT.PUT_LINE('UPDATE 후 이름: ' || vs_name);

      -- DELETE 문
      vs_sql := 'DELETE ch13_physicist
                  WHERE ids = :a ';

      EXECUTE IMMEDIATE vs_sql USING vn_ids;

      SELECT COUNT(*)
        INTO vn_cnt
        FROM ch13_physicist;

      DBMS_OUTPUT.PUT_LINE('vn_cnt : ' || vn_cnt);

      COMMIT;
    END;

결과

    UPDATE 후 이름: Max Planck
    vn_cnt : 0

UPDATE문이나 DELETE문도 사용법은 같다. 동적으로 구성하는 SQL문 구문과 바인드 변수 순서만 잘 맞춰주면 된다.