11.1 execute immudiate

 

用法1：立刻执行sql语句

v_sql := 'insert into tt';

execute immediate v_sql;

用法2：立刻执行sql语句，并赋值给某个变量

    v_sql := 'select count(1) from student';

execute immediate v_sql into v_num;

这是为了举例,实际直接’select count(1) intp v_num from student’ 就可以.

用法3：带参数的sql

    v_sql:='select * from student t where t.name=:1 and t.age=:2';

    execute immediate v_sql using 'ZhangSan',23;

使用execute immudiate 为了拼接sql语句的情况:

Declare

 v_l1 varchar2(255);

 v_l2 varchar2(255);

Select compare,qty into v_l1,v_l2 from a;

Execute immudiate ‘select * from a1 where a1’||v_l1||’v_l2’

--compare的值为> < = 

 

对execute 里面sql语句的检验:

 

--SQL很长,需要创建一个临时clob，用来存储拼接的sql,

这里的目的是检查execute immudiate里面的SQL语句是否正确.

这里面的sql的格式和下面select里面的是一样的.遇到变量用||进行拼接,单引号变成两个单引号,变量前后用单引号隔开

 

create table tt(test clob);

 

Insert into tt

 

Select ' ' from dual;  单引号里面放

 

Commit;

 

例子:

execute immediate '
INSERT INTO a
(
ID,
name,
passwd,
today
)

select
get_seq(''B_ADD_FO_INPUT''),
a.name,
b.passwd,
''' || SYSDATE || '''
FROM
a,b
WHERE a.id= b.a_id
AND ' || 'a.' || p_column1 || ' = ''' ||
p_column2 || '''
where a.qty ' || p_column3 || p_column4 || '
and not exists(select 1 from a where name = ''sanny'')';

对应的检验:

insert into tt

select'
INSERT INTO a
(
ID,
name,
passwd,
today
)

select

get_seq(''B_ADD_FO_INPUT''),
a.name,
b.passwd,
''' || SYSDATE || '''
FROM
a,b
WHERE a.id= b.a_id
AND ' || 'a.' || p_column1 || ' = ''' ||
p_column2 || '''
where a.qty ' || p_column3 || p_column4 || '
and not exists(select 1 from a where name = ''sanny'')' from dual;

commit;

 

执行存储过程之后:
select * from tt;