存储过程中要用动态sql,例如: create or replace procedure pro_test(para1 in varchar2) as v_sql varchar2(2000); begin v_sql:='select t.* from tab t'; v_sql:=v_sql||' where t.aaa in '||para1; execute immediate v_sql; end;
para1 in varchar2(……) select t.* from tab t where t.aaa in ( select * from(select regexp_substr(para1||',','[^,]+',1,rownum) from a connect by rownum<=length(regexp_replace(para1||',','[^,]+'))) )
举例说明:
select t.* from tab t
where t.aaa in ('1', '2', '3');
例如:para1 VARCHAR2(256):= 'AAA'
或者你在写存储过程的时候,当个参数传进来
例如:CREATE OR REPLACE PROCEDURE 存储过程名(para1) is....不知道,是不是你想要的结果
我想要这样的条件
where t.aaa in ('1', '2', '3')
但括号里是动态的,所以我想写成
where t.aaa in para1
所以我想问para1如何定义?直接用varchar2好像不行
调用的是存储过程还是SQL
create or replace procedure pro_test(para1 in varchar2)
as
v_sql varchar2(2000);
begin
v_sql:='select t.* from tab t';
v_sql:=v_sql||' where t.aaa in '||para1;
execute immediate v_sql;
end;
select t.* from tab t
where t.aaa in
(
select * from(select regexp_substr(para1||',','[^,]+',1,rownum) from a
connect by rownum<=length(regexp_replace(para1||',','[^,]+')))
)