動態SQL三(處理批次、和游標配合) (PL/SQL 二十六)

※處理批次

※修改、查詢

DECLARE
    TYPE ooo_ename IS TABLE OF EMP.ENAME%TYPE INDEX BY PLS_INTEGER;
    TYPE ooo_job IS TABLE OF EMP.JOB%TYPE INDEX BY PLS_INTEGER;
    TYPE ooo_sal IS TABLE OF EMP.SAL%TYPE INDEX BY PLS_INTEGER;
    xxx_emp_ename ooo_ename;
    xxx_emp_job ooo_job;
    xxx_emp_sal ooo_sal;
    
    dynamic_sql VARCHAR2(200);
    xxx_emp_deptno EMP.DEPTNO%TYPE := 30;
BEGIN
    dynamic_sql := '
        UPDATE EMP SET SAL = SAL * 1.2 WHERE DEPTNO = :p1
        RETURN ENAME, JOB, SAL INTO :p2, :p3, :p4
    ';
    -- SELECT ENAME, JOB, SAL FROM EMP WHERE DEPTNO = :p1
    
    EXECUTE IMMEDIATE dynamic_sql USING xxx_emp_deptno
    RETURN BULK COLLECT INTO xxx_emp_ename, xxx_emp_job, xxx_emp_sal;
    
    /* 
    EXECUTE IMMEDIATE dynamic_sql 
    BULK COLLECT INTO xxx_emp_ename, xxx_emp_job, xxx_emp_sal
    USING xxx_emp_deptno;
    */
    
    FOR i IN 1..xxx_emp_ename.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(xxx_emp_ename(i));
        DBMS_OUTPUT.PUT_LINE(xxx_emp_job(i));
        DBMS_OUTPUT.PUT_LINE(xxx_emp_sal(i) || CHR(10));
    END LOOP;
END;

※註解部分是查詢

※FORALL

上一個例子的xxx_emp_deptno只是一個值，如果是多個值就要用FORALL

DECLARE
    TYPE xxx_nested IS TABLE OF EMP.EMPNO%TYPE;
    xxx_empnos xxx_nested := xxx_nested(7369, 7499, 7521);
    
    TYPE ooo_ename IS TABLE OF EMP.ENAME%TYPE INDEX BY PLS_INTEGER;
    TYPE ooo_job IS TABLE OF EMP.JOB%TYPE INDEX BY PLS_INTEGER;
    TYPE ooo_sal IS TABLE OF EMP.SAL%TYPE INDEX BY PLS_INTEGER;
    xxx_emp_ename ooo_ename;
    xxx_emp_job ooo_job;
    xxx_emp_sal ooo_sal;
    
    dynamic_sql VARCHAR2(200);
BEGIN
    dynamic_sql := '
        DELETE FROM EMP WHERE EMPNO = :p1
        RETURN ENAME, JOB, SAL INTO :p2, :p3, :p4
    ';
    
    FORALL j IN 1..xxx_empnos.COUNT
        EXECUTE IMMEDIATE dynamic_sql USING xxx_empnos(j)
        RETURN BULK COLLECT INTO xxx_emp_ename, xxx_emp_job, xxx_emp_sal;
    
    FOR i IN 1..xxx_emp_ename.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(xxx_emp_ename(i));
        DBMS_OUTPUT.PUT_LINE(xxx_emp_job(i));
        DBMS_OUTPUT.PUT_LINE(xxx_emp_sal(i) || CHR(10));
    END LOOP;
END;

※可以一次性的刪除

※和游標配合

※不使用陣列

DECLARE
    xxx_cur SYS_REFCURSOR;
    xxx_emp_row EMP%ROWTYPE;
    xxx_emp_deptno EMP.DEPTNO%TYPE := 30;
BEGIN
    OPEN xxx_cur FOR 'SELECT * FROM EMP WHERE DEPTNO = :p1' USING xxx_emp_deptno;
    
    LOOP
        FETCH xxx_cur INTO xxx_emp_row;
        EXIT WHEN xxx_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(xxx_emp_row.EMPNO);
        DBMS_OUTPUT.PUT_LINE(xxx_emp_row.ENAME);
        DBMS_OUTPUT.PUT_LINE(xxx_emp_row.JOB || CHR(10));
    END LOOP;
    CLOSE xxx_cur;
END;

※所以如果sql有in時，可以如下使用

※IN

IF xxx = 'Y' THEN
    ooo := ' AND emp_id in(1,2,3)';
ELSIF xxx = 'N' THEN
    ooo := ' AND emp_id in(4,5)';
END IF;
    
OPEN v_cur FOR 'SELECT * FROM EMP
    WHERE xxx = :aaa || ooo
USING x;
    
LOOP
    FETCH v_cur INTO datas;
    EXIT WHEN v_cur%NOTFOUND;
    -- v_cur%ROWCOUNT;
    -- do something
END LOOP;
    
CLOSE v_cur;

※ooo必需要用||連起來才行，否則會執行錯誤，因為連起來它認為是字串

※使用陣列

DECLARE
    xxx_cur SYS_REFCURSOR;
    TYPE xxx_emp IS TABLE OF EMP%ROWTYPE INDEX BY PLS_INTEGER;
    xxx_emp_row xxx_emp;
    xxx_emp_deptno EMP.DEPTNO%TYPE := 30;
BEGIN
    OPEN xxx_cur FOR 'SELECT * FROM EMP WHERE DEPTNO = :p1' USING xxx_emp_deptno;
    FETCH xxx_cur BULK COLLECT INTO xxx_emp_row;
    CLOSE xxx_cur;
    
    FOR i IN 1..xxx_emp_row.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(xxx_emp_row(i).EMPNO);
        DBMS_OUTPUT.PUT_LINE(xxx_emp_row(i).ENAME);
        DBMS_OUTPUT.PUT_LINE(xxx_emp_row(i).JOB || CHR(10));
    END LOOP;
END;