Oracle动态SQL语句

Oracle动态SQL语句

(整理于2006年东莞裕元)

一.實現動態PL/SQL的方式:
   1. 本地動態SQL (EXECUTE IMMEDIATE)
   2. 使用DBMS_SQL包實現動態.

二.本地動態PL/SQL
   CREATE OR REPLACE
   PROCEDURE PRO_CHG_SAL(vi_ename IN emp.ename%TYPE,
                         vi_sal   IN emp.sal%TYPE)
   IS
      vc_updstring VARCHAR2(100);
   BEGIN
      vc_updstring := 'UPDATE emp SET sal=:sal WHERE ename = :ename';
      EXECUTE IMMEDIATE vc_updstring USING vi_sal,vi_ename;
      COMMIT;
   EXCEPTION
      WHEN OTHERS THEN
         ROLLBACK;
         RAISE_APPLICATION_ERROR(-20003,SQLERRM);
   END;

   當一個動態的INSERT,UPDATE或DELETE語句有一個RETURNING子句時,
   輸出綁定變量可以放到RETURNING INTO子句中或者使用該子句﹐
   在新的應用中,可以使用RETURNING INTO子句,而在舊的應用中﹐可以繼續使用USING子句.

   sql_stmt:='UPDATE emp SET sal = :1 WHERE emp_no = :2 RETRUNING ename,job INTO :3,:4 ';
   EXECUTE IMMEDIATE sql_stmt USING my_sal,my_empno,OUT my_ename,OUT my_job;
   EXECUTE IMMEDIATE sql_stmt USING my_sal,my_emp_no RETURNING INTO my_ename,my_job;

   指定參數模式:
   使用USING子句,可以不用為輸入綁定變量指定參數模式,因為這個模式默認為IN,
   使用RETURNING INTO子句,不能為輸出綁定變量指定參數模式,因為該模式是OUT.

三.用DBMS_SQL包實現動態.
   1. DBMS_SQL包執行的步驟:
      1> 將SQL語句或PL/SQL語句塊放到一個字符串中.
      2> 使用DBMS_SQL.PARSE分析該字符串.
      3> 使用DBMS_SQL.BIND_VARIABLE聯編輸入變量.
      4> 如果該語句不是查詢語句﹐使用DBMS_SQL.EXECUTE和/或DBMS_SQL.VARIABLE_VALUE執行它中,如果是查詢語句繼續執行步驟5>.
      5> 如果該語句為查詢語句,使用DBMS_SQL.DEFINE_COLUMN定義輸出變量.
      6> 使用DBMS_SQL.EXECUTE,DBMS_SQL.FETCH_ROWS,DBMS_SQL.COLUMN_VALUE以及DBMS_VARIABLE_VALUE執行查詢語句并提取結果.
   2. 動態SQL查詢emp_bonus的資料.
      CREATE OR REPLACE
      PROCEDURE PRO_EMP_BONUS(vi_column IN VARCHAR2,
                              vi_value  IN VARCHAR2)
      IS
         vn_cursor_id     INTEGER;        --游標ID
         vc_select_string VARCHAR2(100);  --SQL查詢語句
         vn_rows          INTEGER;
         vc_ename         emp_bonus.ename%TYPE;
         vc_job           emp_bonus.job%TYPE;
         vn_sal           emp_bonus.sal%TYPE;
         vn_comm          emp_bonus.comm%TYPE;
         vc_bonus_date    emp_bonus.bonus_date%TYPE;
      BEGIN
         --打開游標
         vn_cursor_id := DBMS_SQL.OPEN_CURSOR;
         --分析語句
         vc_selet_string := 'SELECT ename,job,sal,comm,bonus_date FROM emp_bonus WHERE '||vi_column||'= :value';
         DBMS_SQL.PARSE(vn_cursor_id,vc_select_string,DBMS_SQL.NATIVE);
         --綁定變量
         DBMS_SQL.BIND_VARIABLE(vn_cursor_id,':value',vi_value);
         --定義輸出變量
         DBMS_SQL.DEFINE_COLUMN(vn_cursor_id,1,vc_ename,10);
         DBMS_SQL.DEFINE_COLUMN(vn_cursor_id,2,vc_job,9);
         DBMS_SQL.DEFINE_COLUMN(vn_cursor_id,3,vn_sal);
         DBMS_SQL.DEFINE_COLUMN(vn_cursor_id,4,vn_comm);
         DBMS_SQL.DEFINE_COLUMN(vn_cursor_id,5,vc_bonus_date,8);
         --執行語句
         vn_rows := DBMS_SQL.EXECUTE(vn_cursor_id);
         --提取變量輸出
         LOOP
            IF DBMS_SQL.FETCH_ROWS(vn_cursor_id) > 0 THEN
               DBMS_SQL.COLUMN_VALUE(vn_cursor_id,1,vc_ename);
               DBMS_SQL.COLUMN_VALUE(vn_cursor_id,2,vc_job);
               DBMS_SQL.COLUMN_VALUE(vn_cursor_id,3,vn_sal);
               DBMS_SQL.COLUMN_VALUE(vn_cursor_id,4,vn_comm);
               DBMS_SQL.COLUMN_VALUE(vn_cursor_id,5,vc_bonus_date);
               DBMS_OUTPUT.PUT_LINE(vc_ename||vc_job||vn_sal||vn_comm||vc_bonus_date);
            ELSE
               EXIT;
            END IF;
         END LOOP;
         --關閉游標
         DBMS_SQL.CLOSE_CURSOR(vn_cursor_id);
      END;