使用Oracle的DBMS - SQL包执行动态SQL语句-

1、使用Oracle的DBMS_SQL包执行动态SQL语句在某些场合下,存储过程或触发器里的SQL语句需要动态生成。Oracle的DBMS_SQL包可以用来执行动态SQL语句。本文通过一个简单的例子来展示如何利用DBMS_SQL包执行动态SQL语句:DECLAREv_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;BEGINs_id := 3000;s_date := SYSDATE;v_sql :=

2、 SELECT id,qan_no,sample_date FROM tblnoWHERE id :sid and sample_date 0 THEN - get column values of the row dbms_sql.COLUMN_VALUE(source_cursor, 1, id_var; dbms_sql.COLUMN_VALUE(source_cursor, 2, name_var; dbms_sql.COLUMN_VALUE(source_cursor, 3, birthdate_var; - Bind the row into the cursor that ins

3、erts into the destination table. You could alter this example to require the use of dynamic SQL by inserting an if condition before the bind. dbms_sql.bind_variable(destination_cursor, :id_bind, id_var; dbms_sql.bind_variable(destination_cursor, :name_bind, name_var; dbms_sql.bind_variable(destinati

4、on_cursor, :birthdate_bind, birthdate_var; ignore := dbms_sql.execute(destination_cursor; ELSE - No more rows to copy: EXIT; END IF; END LOOP; - Commit and close all cursors: COMMIT; dbms_sql.close_cursor(source_cursor; dbms_sql.close_cursor(destination_cursor; EXCEPTION WHEN OTHERS THEN IF dbms_sql

5、.is_open(source_cursor THEN dbms_sql.close_cursor(source_cursor; END IF; IF dbms_sql.is_open(destination_cursor THEN dbms_sql.close_cursor(destination_cursor; END IF; RAISE; END; / Bulk Insert Demo DECLARE stmt varchar2(200; empno_array dbms_sql.number_table; empname_array dbms_sql.varchar2_table; c

6、 NUMBER; dummy NUMBER; BEGIN FOR i IN 0.9 LOOP empno_array(i := 1000 + i; empname_array(I := get_name(i; END LOOP; stmt := insert into emp values(:num_array, :name_array; c := dbms_sql.open_cursor; dbms_sql.parse(c, stmt, dbms_sql.NATIVE; dbms_sql.bind_array(c, :num_array, empno_array; dbms_sql.bind_array(c, :name_array, empname_array; dummy := dbms_sql.execute(c; dbms_sql.close_cursor(c; EX