ORACLE中DBMS_SQL的用法.docxVIP

ORACLE中DBMS_SQL的用法?对于一般的select操作,如果使用动态的sql语句则需要进行以下几个步骤:open ? cursor--- parse--- define ? column--- excute--- fetch ? rows--- close ? cursor;而对于dml操作(insert,update)则需要进行以下几个步骤:open ? cursor--- parse--- bind ? variable--- execute--- close ? cursor;对于delete操作只需要进行以下几个步骤:open ? cursor--- parse--- execute--- close ? cursor;? ?例一：create table test(n_id ? number, ?v_name ?varchar2(50), d_insert_date date);alter table test add constraint pk_id ?primary key(n_id);?declare? ?v_cursor ? number;? ?v_sql ? ? ?varchar2(200);? ?v_id ? ? ? number;? ?v_name ? ? varchar2(50);? ?v_date ? ? date;? ?v_stat ? ? number;begin??? ?v_id := 1;? ?v_name := 测试 insert;? ?v_date := sysdate;? ?v_cursor := dbms_sql.open_cursor; ?--打开游标? ?v_sql := insert into test(n_id, v_name, d_insert_date) values(:v_id,:v_name,:v_date);? ?dbms_sql.parse(v_cursor, v_sql, dbms_sql.native); ?--解析SQL? ?dbms_sql.bind_variable(v_cursor, :v_id, v_id); ? --绑定变量? ?dbms_sql.bind_variable(v_cursor, :v_name, v_name);? ?dbms_sql.bind_variable(v_cursor, :v_date, v_date);??? ?v_stat := dbms_sql.execute(v_cursor); ?--执行? ?dbms_sql.close_cursor(v_cursor); ? --关闭游标? ?commit;end;? ?例二：?declare? ?v_cursor ? number;? ?v_sql ? ? ?varchar2(200);? ?v_id ? ? ? number;? ?v_name ? ? varchar2(50);? ?v_stat ? ? number;begin? ? v_name := 测试 update;? ? v_id := 1;? ? v_cursor := dbms_sql.open_cursor;? ? v_sql := update test set v_name = :v_name, d_insert_date = :v_date where n_id = :v_id;? ? dbms_sql.parse(v_cursor, v_sql, dbms_sql.native);? ? dbms_sql.bind_variable(v_cursor, :v_name, v_name);? ? dbms_sql.bind_variable(v_cursor, :v_date, sysdate);? ? dbms_sql.bind_variable(v_cursor, :v_id, v_id);? ? v_stat := dbms_sql.execute(v_cursor);? ? dbms_sql.close_cursor(v_cursor);? ? commit;end;? ?例三：?declare? ? v_cursor ? number;? ? v_sql ? ? ?varchar2(200);? ? v_id ? ? ? number;? ? v_stat ? ? number;begin?? ?v_id := 1;? ?v_sql := delete from test where n_id = :v_id;? ?v_cursor := dbms_sql.open_cursor;? ?dbms_sql.parse(v_cursor, v_sql, dbms_sql.n