使用Oracle的DBMS_SQL包執行動態SQL語句

admin

如何利用DBMS_SQL包執行動態SQL語句:

DECLARE
       v_cursor NUMBER;
       v_stat NUMBER;
       v_row NUMBER;
       v_id NUMBER;
       v_no VARCHAR(100);
       v_date DATE;
       v_sql VARCHAR(200);
       s_id NUMBER;
       s_date DATE;
BEGIN
     s_id := 3000;
     s_date := SYSDATE;
     v_sql := 'SELECT id,qan_no,sample_date FROM "tblno" WHERE id > :sid and sample_date < :sdate';
     v_cursor := dbms_sql.open_cursor; --開啟遊標;
     dbms_sql.parse(v_cursor, v_sql, dbms_sql.native); --解析動態SQL語句;
     dbms_sql.bind_variable(v_cursor, ':sid', s_id); --繫結輸入引數;
     dbms_sql.bind_variable(v_cursor, ':sdate', s_date);
     
     dbms_sql.define_column(v_cursor, 1, v_id); --定義列
     dbms_sql.define_column(v_cursor, 2, v_no, 100);
     dbms_sql.define_column(v_cursor, 3, v_date);
     v_stat := dbms_sql.execute(v_cursor); --執行動態SQL語句。
     LOOP
         EXIT WHEN dbms_sql.fetch_rows(v_cursor)<=0; --fetch_rows在結果集中移動遊標,如果未抵達末尾,返回1。        
         dbms_sql.column_value(v_cursor, 1, v_id); --將當前行的查詢結果寫入上面定義的列中。
         dbms_sql.column_value(v_cursor, 2, v_no);
         dbms_sql.column_value(v_cursor, 3, v_date);
         dbms_output.put_line(v_id || ';' || v_no || ';' || v_date);
     END LOOP;
     dbms_sql.close_cursor(v_cursor); --關閉遊標。
END;


結果:

3095;S051013XW00010;15-10月-05
3112;A051013XW00027;10-10月-05
3113;A051013XW00028;13-10月-05
3116;S051013XW00031;13-10月-05

1. CREATE OR REPLACE PROCEDURE copy (   
   
2.      source      IN VARCHAR2,   
   
3.      destination IN VARCHAR2) IS   
   
4.      id_var             NUMBER;   
   
5.      name_var           VARCHAR2(30);   
   
6.      birthdate_var      DATE;   
   
7.      source_cursor      INTEGER;   
   
8.      destination_cursor INTEGER;   
   
9.      ignore             INTEGER;   
   
10.   BEGIN   
    
11.    
    
12.   -- Prepare a cursor to select from the source table:   
    
13.      source_cursor := dbms_sql.open_cursor;   
    
14.      DBMS_SQL.PARSE(source_cursor,   
    
15.          'SELECT id, name, birthdate FROM ' || source,   
    
16.           DBMS_SQL.native);   
    
17.      DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, id_var);   
    
18.      DBMS_SQL.DEFINE_COLUMN(source_cursor, 2, name_var, 30);   
    
19.      DBMS_SQL.DEFINE_COLUMN(source_cursor, 3, birthdate_var);   
    
20.      ignore := DBMS_SQL.EXECUTE(source_cursor);   
    
21.    
    
22.   -- Prepare a cursor to insert into the destination table:   
    
23.      destination_cursor := DBMS_SQL.OPEN_CURSOR;   
    
24.      DBMS_SQL.PARSE(destination_cursor,   
    
25.                   'INSERT INTO ' || destination ||   
    
26.                   ' VALUES (:id_bind, :name_bind, :birthdate_bind)',   
    
27.                    DBMS_SQL.native);   
    
28.    
    
29.   -- Fetch a row from the source table and insert it into the destination table:   
    
30.      LOOP   
    
31.        IF DBMS_SQL.FETCH_ROWS(source_cursor)>0 THEN   
    
32.          -- get column values of the row   
    
33.          DBMS_SQL.COLUMN_VALUE(source_cursor, 1, id_var);   
    
34.          DBMS_SQL.COLUMN_VALUE(source_cursor, 2, name_var);   
    
35.          DBMS_SQL.COLUMN_VALUE(source_cursor, 3, birthdate_var);   
    
36.    
    
37.   -- Bind the row into the cursor that inserts into the destination table. You   
    
38.   -- could alter this example to require the use of dynamic SQL by inserting an   
    
39.   -- if condition before the bind.   
    
40.         DBMS_SQL.BIND_VARIABLE(destination_cursor, ':id_bind', id_var);   
    
41.         DBMS_SQL.BIND_VARIABLE(destination_cursor, ':name_bind', name_var);   
    
42.         DBMS_SQL.BIND_VARIABLE(destination_cursor, ':birthdate_bind',   
    
43. birthdate_var);   
    
44.         ignore := DBMS_SQL.EXECUTE(destination_cursor);   
    
45.       ELSE   
    
46.    
    
47.   -- No more rows to copy:   
    
48.         EXIT;   
    
49.       END IF;   
    
50.     END LOOP;   
    
51.    
    
52.   -- Commit and close all cursors:   
    
53.      COMMIT;   
    
54.      DBMS_SQL.CLOSE_CURSOR(source_cursor);   
    
55.      DBMS_SQL.CLOSE_CURSOR(destination_cursor);   
    
56.    EXCEPTION   
    
57.      WHEN OTHERS THEN   
    
58.        IF DBMS_SQL.IS_OPEN(source_cursor) THEN   
    
59.          DBMS_SQL.CLOSE_CURSOR(source_cursor);   
    
60.        END IF;   
    
61.        IF DBMS_SQL.IS_OPEN(destination_cursor) THEN   
    
62.          DBMS_SQL.CLOSE_CURSOR(destination_cursor);   
    
63.        END IF;   
    
64.        RAISE;   
    
65.   END;   
    
66. /   

複製代碼

例如，动态SQL可以让你创建一个程序上的表的名称，直到运行时才知道。