② INSERT문, UPDATE문, DELETE문 처리
DBMS_SQL 패키지를 사용해 데이터를 변경하는 동적 SQL을 처리해 보자. 이번에도 역시 ch13_physicist 테이블을 대상으로 처리할 것이다. 먼저 테이블을 깨끗이 비워 보자.
입력
TRUNCATE TABLE ch13_physicist;
결과
table CH13_PHYSICIST이(가) 잘렸습니다.
이제 INSERT하는 예제를 만들어 보자.
입력
DECLARE
vn_ids ch13_physicist.ids%TYPE := 1;
vs_name ch13_physicist.names%TYPE := 'Galileo Galilei';
vd_dt ch13_physicist.birth_dt%TYPE := TO_DATE('1564-02-15', 'YYYY-MM-DD');
vs_sql VARCHAR2(1000);
-- DBMS_SQL 패키지 관련 변수
vn_cur_id NUMBER := DBMS_SQL.OPEN_CURSOR(); -- 1.커서를 연다
vn_return NUMBER;
BEGIN
-- INSERT문 작성
vs_sql := 'INSERT INTO ch13_physicist VALUES (:a, :b, :c)';
-- 2. 파싱
DBMS_SQL.PARSE (vn_cur_id, vs_sql, DBMS_SQL.NATIVE);
-- 3. 바인드 변수 연결 (VALUES 절에서 사용한 변수가 3개 이므로 각 변수별로 총 3회 호출)
DBMS_SQL.BIND_VARIABLE ( vn_cur_id, ':a', vn_ids);
DBMS_SQL.BIND_VARIABLE ( vn_cur_id, ':b', vs_name);
DBMS_SQL.BIND_VARIABLE ( vn_cur_id, ':c', vd_dt);
-- 4. 쿼리 실행
vn_return := DBMS_SQL.EXECUTE (vn_cur_id);
-- 5. 커서 닫기
DBMS_SQL.CLOSE_CURSOR (vn_cur_id);
--결과 건수 출력
DBMS_OUTPUT.PUT_LINE('결과건수: ' || vn_return);
COMMIT;
END;
결과
결과건수: 1
정상적으로 1개의 로우가 INSERT되었다. INSERT문에서 결과를 패치할 필요가 없으므로 이전 SELECT문에서 사용했던 결과 선택 컬럼 정의, 결과 패치, 패치된 결과 값을 받아 오는 과정이 없어 상대적으로 코드가 줄어 들었다. 또한 바인드 변수를 연결할 때도 NDS를 사용하면 바인드 변수명은 중요치 않고 개수와 순서만 맞춰 주어도 됐었지만, DBMS_SQL.BIND_VARIABLE 프로시저는 바인드 변수명을 직접 매개변수로 사용하므로 바인드 변수명을 반드시 맞춰야 한다.
이번에는 UPDATE문을 DBMS_SQL로 처리해 보는데, 전에 먼저 ch13_physicist 테이블에 다음과 같이 데이터를 더 입력해 보자.
입력
INSERT INTO ch13_physicist VALUES (2, 'Isaac Newton', TO_DATE('1643-01-04', 'YYYY-MM-DD'));
INSERT INTO ch13_physicist VALUES (3, 'Max Plank', TO_DATE('1858-04-23', 'YYYY-MM-DD'));
INSERT INTO ch13_physicist VALUES (4, 'Albert Einstein', TO_DATE('1879-03-14', 'YYYY-MM-DD'));
COMMIT;
결과
1개 행 이(가) 삽입되었습니다.
1개 행 이(가) 삽입되었습니다.
1개 행 이(가) 삽입되었습니다.
커밋되었습니다.
입력
DECLARE
vn_ids ch13_physicist.ids%TYPE := 3;
vs_name ch13_physicist.names%TYPE := ' UPDATED';
vs_sql VARCHAR2(1000);
-- DBMS_SQL 패키지 관련 변수
vn_cur_id NUMBER := DBMS_SQL.OPEN_CURSOR(); -- 1. 커서를 연다
vn_return NUMBER;
BEGIN
-- UPDATE문 작성
vs_sql := 'UPDATE ch13_physicist SET names = names || :a WHERE ids < :b' ;
-- 2. 파싱
DBMS_SQL.PARSE (vn_cur_id, vs_sql, DBMS_SQL.NATIVE);
-- 3. 바인드 변수 연결
DBMS_SQL.BIND_VARIABLE ( vn_cur_id, ':a', vs_name);
DBMS_SQL.BIND_VARIABLE ( vn_cur_id, ':b', vn_ids);
-- 4. 쿼리 실행
vn_return := DBMS_SQL.EXECUTE (vn_cur_id);
-- 5. 커서 닫기
DBMS_SQL.CLOSE_CURSOR (vn_cur_id);
--결과 건수 출력
DBMS_OUTPUT.PUT_LINE('UPDATE 결과건수: ' || vn_return);
COMMIT;
END;
결과
UPDATE결과건수: 2
위 예제의 UPDATE문에서 바인드 변수를 제거하고 실제 들어갈 값으로 바꾼 SQL문은 다음과 같다.
UPDATE ch13_physicist
SET names = names || ‘ UPDATED’
WHERE ids < 3;
즉 ids가 3보다 작은 건의 names 컬럼 값을 자신의 값에 ‘ UPDATED’란 문자열을 붙여 갱신하는 문장이다. ch13_physicist 테이블에는 총 4건의 데이터가 있고 이중 ids값이 3보다 작은 것은 2개이므로, 2건이 갱신됐음을 알 수 있다. 직접 테이블을 조회해 확인해 보자.
입력
SELECT *
FROM ch13_physicist;
결과
IDS NAMES BIRTH_DT
--- --------------------------- ---------------
1 Galileo Galilei UPDATED 1564-02-15
2 Isaac Newton UPDATED 1643-01-04
3 Max Plank 1858-04-23
4 Albert Einstein 1879-03-14
DELETE문도 UPDATE문과 유사한 방식으로 처리할 수 있다. 동적 SQL 구문을 DELETE문으로 바꿔 보자.
입력
DECLARE
vn_ids ch13_physicist.ids%TYPE := 3;
vs_sql VARCHAR2(1000);
-- DBMS_SQL 패키지 관련 변수
vn_cur_id NUMBER := DBMS_SQL.OPEN_CURSOR(); -- 1. 커서를 연다
vn_return NUMBER;
BEGIN
-- DELETE문 작성
vs_sql := 'DELETE ch13_physicist WHERE ids < :b' ;
-- 2. 파싱
DBMS_SQL.PARSE (vn_cur_id, vs_sql, DBMS_SQL.NATIVE);
-- 3. 바인드 변수 연결
DBMS_SQL.BIND_VARIABLE ( vn_cur_id, ':b', vn_ids);
-- 4. 쿼리 실행
vn_return := DBMS_SQL.EXECUTE (vn_cur_id);
-- 5. 커서 닫기
DBMS_SQL.CLOSE_CURSOR (vn_cur_id);
--결과 건수 출력
DBMS_OUTPUT.PUT_LINE('DELETE 결과건수: ' || vn_return);
COMMIT;
END;
결과
DELETE 결과건수: 2
이전 예제와 차이점은 바인드 변수가 WHERE 절에 한 번만 사용되어 BIND_VARIABLE 프로시저를 한 번만 호출했다는 점이다. 결과를 보면 알겠지만 성공적으로 데이터가 삭제됐다.
이 외에도 익명 블록이나 DDL문 역시 DBMS_SQL로 처리할 수 있으며 그 사용 방식도 지금까지 살펴 봤던 예제와 동일하게 처리하면 된다.