oracle存储过程传入符号,oracle procedure存储过程输入参数之动态sql传入(括号及,逗号的特殊处理...

--某同事说报表sql相关的存储过程的输入参数如传入一个大字符串报错,真是如些吗

---创建业务表(订单表)所引用的订单类型数据

SQL> create table zidian(order_type varchar2(10));

Table created

SQL> insert into zidian values('100000');

1 row inserted

SQL> insert into zidian values('100001');

1 row inserted

SQL> insert into zidian values('100002');

1 row inserted

SQL> insert into zidian values('100003');

1 row inserted

SQL> insert into zidian values('100004');

1 row inserted

SQL> commit;

Commit complete

--创建业务表并插入数据

SQL> create table t_order(order_id int,order_type varchar2(10));

Table created

SQL> insert into t_order values(1,'100000');

1 row inserted

SQL> insert into t_order values(2,'100001');

1 row inserted

SQL> insert into t_order values(3,'100002');

1 row inserted

SQL> commit;

Commit complete

SQL> select * from zidian;

ORDER_TYPE

----------

100000

100001

100002

100003

100004

SQL> select * from t_order;

ORDER_ID ORDER_TYPE

--------------------------------------- ----------

1 100000

2 100001

3 100002

SQL> ---实现filter掉order_type为100000的订单数据

SQL> select * from t_order where order_type in ('100001','100002','100003','100004');

ORDER_ID ORDER_TYPE

--------------------------------------- ----------

2 100001

3 100002

---如果用存储过程又如何实现上述功能呢

SQL> create or replace procedure proc_filter(in_filter varchar2)

2  as

3  v_sql varchar2(4000);

4  v_cnt pls_integer;

5  begin

6    v_sql:='select count(*) from t_order where order_type in'||in_filter;

7    dbms_output.put_line(v_sql);

8    execute immediate v_sql into v_cnt;

9    dbms_output.put_line(v_cnt);

10  end;

11  /

Procedure created

SQL> exec proc_filter('(''100001'',''100002'',''100003'',''100004'')');

select count(*) from t_order where order_type in('100001','100002','100003','100004')

2

PL/SQL procedure successfully completed

小结;1,还是转义字符的使用