--某同事說報表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,還是跳脫字元的使用

oracle procedure儲存過程輸入引數之動態sql傳入(括號及,逗號的特殊處理

相關文章