ORACLE 动态执行SQL语句 - 司广孟
ORACLE 动态执行SQL语句
本文转自 http://zhaisx.iteye.com/blog/856472
Oracle 动态SQL
Oracle 动态SQL有两种写法:用 DBMS_SQL 或 execute immediate,建议使用后者。试验步骤如下:
1.DDL和DML
1 /*** DDL ***/ 2 begin 3 EXECUTE IMMEDIATE \'drop table temp_1\'; 4 EXECUTE IMMEDIATE \'create table temp_1(name varchar2(8))\'; 5 end; 6 7 /*** DML ***/ 8 declare 9 v_1 varchar2(8); 10 v_2 varchar2(10); 11 str varchar2(50); 12 begin 13 v_1:=\'测试人员\'; 14 v_2:=\'北京\'; 15 str := \'INSERT INTO test (name ,address) VALUES (:1, :2)\'; 16 EXECUTE IMMEDIATE str USING v_1, v_2; 17 commit; 18 end;
2.返回单条
1 declare 2 str varchar2(500); 3 c_1 varchar2(10); 4 r_1 test%rowtype; 5 begin 6 c_1:=\'测试人员\'; 7 str:=\'select * from test where name=:c WHERE ROWNUM=1\'; 8 execute immediate str into r_1 using c_1; 9 DBMS_OUTPUT.PUT_LINE(R_1.NAME||R_1.ADDRESS); 10 end ;
3.返回结果集
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 ; --动态测试,用sqlstr字符串返回结果,用using关键词传递参数 open rc for sqlstr using intid; end if; return rc; end get; end pkg_test; /
- 上一篇 »Oracle执行SQL语句的过程
- 下一篇 »Oracle动态执行SQL四种方式的例子