ORACLE中DBMSSQL的用法.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.native);?dbms_sql.bind_variable(v_cursor, :v_id, v_id);?v_stat := dbms_sql.execute(v_cursor);?dbms_sql.cl