[PL/SQL] DBMS_SQL (2) : 기본활용

DBMS_SQL (2)

기본 활용

1. SELECT

DECLARE
    -- 출력 변수 선언
    VN_EMP_ID       EMPLOYEES.EMPLOYEE_ID%TYPE;
    VS_EMP_NAME     EMPLOYEES.EMP_NAME%TYPE;
    VS_JOB_ID       EMPLOYEES.JOB_ID%TYPE;
    
    VS_SQL          VARCHAR2(1000);
    
    -- 바인드 변수 선언과 값 설정
    VS_JOB          EMPLOYEES.JOB_ID%TYPE       := 'SA_REP';
    VN_SAL          EMPLOYEES.SALARY%TYPE       := 7000;
    VN_MANAGER      EMPLOYEES.MANAGER_ID%TYPE   := 148;
    
    -- DBMS_SQL 패키지 관련 변수
    -- 1. 커서를 연다
    VN_CUR_ID       NUMBER  := DBMS_SQL.OPEN_CURSOR();
    VN_RETURN       NUMBER;
BEGIN
    VS_SQL := 'SELECT   EMPLOYEE_ID, EMP_NAME, JOB_ID
               FROM     EMPLOYEES
               WHERE    JOB_ID = :A
               AND      SALARY < :B
               AND      MANAGER_ID = :C';
    
    -- 2. 파싱
    DBMS_SQL.PARSE(VN_CUR_ID, VS_SQL, DBMS_SQL.NATIVE);
    
    -- 3. 바인드변수 연결(WHERE 절에 사용한 변수가 3개이므로 각 변수별로 3번 호출)
    DBMS_SQL.BIND_VARIABLE(VN_CUR_ID, ':A', VS_JOB);
    DBMS_SQL.BIND_VARIABLE(VN_CUR_ID, ':B', VN_SAL);
    DBMS_SQL.BIND_VARIABLE(VN_CUR_ID, ':C', VN_MANAGER);
    
    -- 4. 결과 선택 컬럼 정의
    -- SELECT 순서에 따라 순번을 맞추고 결과를 담을 변수와 연결
    DBMS_SQL.DEFINE_COLUMN(VN_CUR_ID, 1, VN_EMP_ID);
    DBMS_SQL.DEFINE_COLUMN(VN_CUR_ID, 2, VS_EMP_NAME, 80);
    DBMS_SQL.DEFINE_COLUMN(VN_CUR_ID, 3, VS_JOB_ID, 10);
    
    -- 5. 쿼리 실행
    VN_RETURN := DBMS_SQL.EXECUTE(VN_CUR_ID);
    
    -- 6. 결과 패치
    LOOP
        IF DBMS_SQL.FETCH_ROWS(VN_CUR_ID) = 0 THEN
            EXIT;
        END IF;
        
        -- 7. 패치된 결과 값 받아오기
        DBMS_SQL.COLUMN_VALUE(VN_CUR_ID, 1, VN_EMP_ID);
        DBMS_SQL.COLUMN_VALUE(VN_CUR_ID, 2, VS_EMP_NAME);
        DBMS_SQL.COLUMN_VALUE(VN_CUR_ID, 3, VS_JOB_ID);
        
        -- 결과 출력
        DBMS_OUTPUT.PUT_LINE('EMP_ID   : ' || VN_EMP_ID);
        DBMS_OUTPUT.PUT_LINE('EMP_NAME : ' || VS_EMP_NAME);
        DBMS_OUTPUT.PUT_LINE('JOB_ID   : ' || VS_JOB_ID);
    END LOOP;
    
    -- 8. 커서 닫기
    DBMS_SQL.CLOSE_CURSOR(VN_CUR_ID);
END;

정말 길다... 복잡하다... 확실히 DBMS_SQL 보다는 NDS가 편한것이 느껴진다.

2. INSERT

TRUNCATE TABLE CH13_PHYSICIST;
 
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);
    
    -- 1. 커서를 연다
    VN_CUR_ID   NUMBER := DBMS_SQL.OPEN_CURSOR();
    VN_RETURN   NUMBER;
BEGIN
    VS_SQL  := 'INSERT INTO CH13_PHYSICIST VALUES(:A, :B, :C)';
    
    -- 2. 파싱
    DBMS_SQL.PARSE(VN_CUR_ID, VS_SQL, DBMS_SQL.NATIVE);
    
    -- 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;

주석 내용과 같이 바인드 변수를 연결할 때, 바인드 변수명을 직접 매개변수로 이용하므로 바인드 변수명은 반드시 일치해야한다.

3. UPDATE

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'));
 
DECLARE
    VN_IDS      CH13_PHYSICIST.IDS%TYPE     := 3;
    VS_NAME     CH13_PHYSICIST.NAMES%TYPE   := ' UPDATED';
    
    VS_SQL      VARCHAR2(1000);
    
    VN_CUR_ID   NUMBER  := DBMS_SQL.OPEN_CURSOR();
    VN_RETURN   NUMBER;
BEGIN
    VS_SQL := 'UPDATE CH13_PHYSICIST SET NAMES = NAMES || :A WHERE IDS < :B';
    
    DBMS_SQL.PARSE(VN_CUR_ID, VS_SQL, DBMS_SQL.NATIVE);
    
    DBMS_SQL.BIND_VARIABLE(VN_CUR_ID, ':A', VS_NAME);
    DBMS_SQL.BIND_VARIABLE(VN_CUR_ID, ':B', VN_IDS);
    
    VN_RETURN := DBMS_SQL.EXECUTE(VN_CUR_ID);
    
    DBMS_SQL.CLOSE_CURSOR(VN_CUR_ID);
    
    DBMS_OUTPUT.PUT_LINE('UPDATE 결과 건수 : ' || VN_RETURN);
END;

4. DELETE

DECLARE
    VN_IDS      CH13_PHYSICIST.IDS%TYPE := 3;
    
    VS_SQL      VARCHAR2(1000);
    
    VN_CUR_ID   NUMBER  := DBMS_SQL.OPEN_CURSOR();
    VN_RETURN   NUMBER;
BEGIN
    VS_SQL := 'DELETE CH13_PHYSICIST WHERE IDS < :B';
    
    DBMS_SQL.PARSE(VN_CUR_ID, VS_SQL, DBMS_SQL.NATIVE);
    
    DBMS_SQL.BIND_VARIABLE(VN_CUR_ID, ':B', VN_IDS);
    
    VN_RETURN := DBMS_SQL.EXECUTE(VN_CUR_ID);
    
    DBMS_SQL.CLOSE_CURSOR(VN_CUR_ID);
    
    DBMS_OUTPUT.PUT_LINE('DELETE 결과건수 : ' || VN_RETURN);
    COMMIT;
END;