ORACLE 动态实施SQL语句_SQL

ORACLE 动态执行SQL语句

转自：http://zhaisx.iteye.com/blog/856472

ORACLE 动态执行SQL语句

博客分类：

Oracle

SQLOracleCC++C#?

Oracle 动态SQL
Oracle 动态SQL有两种写法：用 DBMS_SQL 或 execute immediate，建议使用后者。试验步骤如下：

1. DDL 和 DML

Sql代码??
/***?DDL?***/??
begin??
????EXECUTE?IMMEDIATE?'drop?table?temp_1';??
????EXECUTE?IMMEDIATE?'create?table?temp_1(name?varchar2(8))';??
end;??
??
/***?DML?***/??
declare??
????v_1?varchar2(8);??
????v_2?varchar2(10);??
????str?varchar2(50);??
begin??
????v_1:='测试人员';??
????v_2:='北京';??
????str?:=?'INSERT?INTO?test?(name?,address)?VALUES?(:1,?:2)';??
????EXECUTE?IMMEDIATE?str?USING?v_1,?v_2;??
????commit;??
end;??

2. 返回单条结果

Sql代码??
declare??
????str?varchar2(500);??
????c_1?varchar2(10);??
????r_1?test%rowtype;??
begin??
????c_1:='测试人员';??
????str:='select?*?from?test?where?name=:c?WHERE?ROWNUM=1';??
????execute?immediate?str?into?r_1?using?c_1;??
????DBMS_OUTPUT.PUT_LINE(R_1.NAME||R_1.ADDRESS);??
end?;??

3. 返回结果集

Sql代码??
CREATE?OR?REPLACE?package?pkg_test?as??
????/*?定义ref?cursor类型??
????不加return类型，为弱类型，允许动态sql查询，??
????否则为强类型，无法使用动态sql查询;??
????*/??
????type?myrctype?is?ref?cursor;??
??
????--函数申明??
????function?get(intID?number)?return?myrctype;??
end?pkg_test;??
/??
??
CREATE?OR?REPLACE?package?body?pkg_test?as??
--函数体??
????function?get(intID?number)?return?myrctype?is??
????????rc?myrctype;?--定义ref?cursor变量??
????????sqlstr?varchar2(500);??
????begin??
????????if?intID=0?then??
????????????--静态测试，直接用select语句直接返回结果??
????????????open?rc?for?select?id,name,sex,address,postcode,birthday?from??
student;??
????????else??
????????????--动态sql赋值，用:w_id来申明该变量从外部获得??
????????????sqlstr?:=?'select?id,name,sex,address,postcode,birthday?from?student??
where?id=:w_id';??
????????????--动态测试，用sqlstr字符串返回结果，用using关键词传递参数??
????????????open?rc?for?sqlstr?using?intid;??
????????end?if;??
??
????????return?rc;??
????end?get;??
??
end?pkg_test;??
/ ?