不太明白你的意思.参数直接是变量也可以啊 select * from a where instr(customer_type,'arg_type') > 0
需要做连接字符串,用execute来执行
SQL> select * from a; ITEM_NO ITEM_SUB_NO ITEM_NAME ---------- ----------- -------------------- 111 1 55 111 2 54 111 6 54 333 8 45 222 4 29 444 5 446 rows selectedSQL> select * from a where item_no like '&_t%';-- 输入 3 ITEM_NO ITEM_SUB_NO ITEM_NAME ---------- ----------- -------------------- 333 8 45SQL> select * from a where item_no like '&_t%';-- 输入2 ITEM_NO ITEM_SUB_NO ITEM_NAME ---------- ----------- -------------------- 222 4 29SQL>
type t_refCur is ref cursor procedure( p_arg varchar2 ) is l_cur t_refCur begin open l_cur for 'select * from a where customer_type like :arg' using p_arg||'%'; end ;
select * from a where instr(customer_type,'arg_type') > 0
---------- ----------- --------------------
111 1 55
111 2 54
111 6 54
333 8 45
222 4 29
444 5 446 rows selectedSQL> select * from a where item_no like '&_t%';-- 输入 3 ITEM_NO ITEM_SUB_NO ITEM_NAME
---------- ----------- --------------------
333 8 45SQL> select * from a where item_no like '&_t%';-- 输入2 ITEM_NO ITEM_SUB_NO ITEM_NAME
---------- ----------- --------------------
222 4 29SQL>
procedure( p_arg varchar2 )
is
l_cur t_refCur
begin
open l_cur for
'select * from a where customer_type like :arg' using p_arg||'%';
end ;