DDL 和 DML
/*** DDL ***/
begin
EXECUTE IMMEDIATE drop table temp_;
EXECUTE IMMEDIATE create table temp_(name varchar());
end;
/*** DML ***/
declare
v_ varchar();
v_ varchar();
str varchar();
begin
v_:=测试人员;
v_:=北京;
str := INSERT INTO test (name address) VALUES (: :);
EXECUTE IMMEDIATE str USING v_ v_;
commit;
end;
返回单条结果
declare
str varchar();
c_ varchar();
r_ test%rowtype;
begin
c_:=测试人员;
str:=select * from test where name=:c WHERE ROWNUM=;
execute immediate str into r_ using c_;
DBMS_OUTPUTPUT_LINE(R_NAME||R_ADDRESS);
end ;
返回结果集
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 varchar();
begin
if intID= then
静态测试直接用select语句直接返回结果
open rc for select idnamesexaddresspostcodebirthday from student;
else
动态sql赋值用:w_id来申明该变量从外部获得
sqlstr := select idnamesexaddresspostcodebirthday from student where id=:w_id;
动态测试用sqlstr字符串返回结果用using关键词传递参数
open rc for sqlstr using intid;
end if;
return rc;
end get;
end pkg_test;
/
