ORACLE动态SQL

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



1 . DDL 和 DML

/**/ /*** 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 . 返回单条结果
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 . 返回结果集
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; 
/