dbms_sql包的用法

dbms_sql包的用法

通常运用 DBMS_SQL 包一般分为 如下 几步：

1. open cursor ： 打开 cursor

2. parse cursor ：解析你要执行的 SQL 语句

3. bind variable ：如果要执行的 SQL 语句中包含变量，在此就需要绑定变量

4. execute ：执行 SQL 语句

5. close cursor ：在执行后关闭此 cursor.

对于一般的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 CUST(N_ID number, V_NAME varchar2(50), D_INSERT_DATE date);
alter table cust 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 cust(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;
/
select * from CUST;

例二：

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 cust 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;
/
select * from CUST;

例三：

declare
v_cursor number;
v_sql varchar2(200);
v_id number;
v_stat number;
begin

v_id := 1;
v_sql := 'delete from cust 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.close_cursor(v_cursor);
commit;
end;
/
select * from CUST;

例四：

declare
v_cursor number;
v_sql varchar2(200);
v_id number;
v_name varchar2(50);
v_date varchar2(10);
v_stat number;
begin

v_sql := 'select n_id, v_name, to_char(d_insert_date, ''yyyy-mm-dd'') from cust';
v_cursor := dbms_sql.open_cursor; --打开游标
dbms_sql.parse(v_cursor, v_sql, dbms_sql.native); --解析游标
dbms_sql.define_column(v_cursor, 1, v_id); --定义列
dbms_sql.define_column(v_cursor, 2, v_name, 50); --注意：当变量为varchar2类型时，要加长度
dbms_sql.define_column(v_cursor, 3, v_date, 10);
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_name);
dbms_sql.column_value(v_cursor, 3, v_date);
dbms_output.put_line(v_id || ':' || v_name || ':' || v_date);
end LOOP;
end;