procedure SELECT_ROADVIORATE(AROADDEPART varchar2,ARID varchar2,
AVIOTYPE varchar2,ds out DataSet) is
roaddepart_where varchar2(100);
rid_where varchar2(100);
vioType_where varchar2(100);
begin
if AROADDEPART is not null then
roaddepart_where := ' and ROADDEPART=' || AROADDEPART;
else
roaddepart_where := '';
end if;
if ARID is not null then
rid_where := ' and RID=' || ARID;
else
rid_where := '';
end if;
if AVIOTYPE is not null then
vioType_where := ' and VIOTYPE=' || AVIOTYPE;
else
vioType_where := '';
end if;
open ds for
select * from ASE_ROADVIORATE
where '1'='1' || roaddepart_where || rid_where || vioType_where;end SELECT_ROADVIORATE;看看我这样拼接可对,
若是不对
这样的情况应该怎么实现比较好?
三个变量都允许为空,也就是3*2*1种情况。
AVIOTYPE varchar2,ds out DataSet) is
roaddepart_where varchar2(100);
rid_where varchar2(100);
vioType_where varchar2(100);
begin
if AROADDEPART is not null then
roaddepart_where := ' and ROADDEPART=' || AROADDEPART;
else
roaddepart_where := '';
end if;
if ARID is not null then
rid_where := ' and RID=' || ARID;
else
rid_where := '';
end if;
if AVIOTYPE is not null then
vioType_where := ' and VIOTYPE=' || AVIOTYPE;
else
vioType_where := '';
end if;
open ds for
select * from ASE_ROADVIORATE
where '1'='1' || roaddepart_where || rid_where || vioType_where;end SELECT_ROADVIORATE;看看我这样拼接可对,
若是不对
这样的情况应该怎么实现比较好?
三个变量都允许为空,也就是3*2*1种情况。
最后那个动态游标是错的'select * from ASE_ROADVIORATE where 1=1'
|| roaddepart_where || rid_where || vioType_where;
我做了一个例子测试通过 ,oracle10i
create or replace procedure p_test
as
v_name varchar2(100);
v_code varchar2(10);
v_ret varchar2(100);
cur Pageing.T_CURSOR;
v_sql varchar2(1000);
begin
v_code := '0258';
v_name := ' table_code = '||''''||v_code||'''';
-- 这里的赋值要注意 ,字符串的时候两边要加 ‘ 号
v_sql := ' select table_chinese from sys_table where '|| v_name;
--- 这样得出来的sql才是
--- select table_chinese from sys_table where table_code = '0258'
open cur for v_sql ;
fetch cur into v_ret;
dbms_output.put_line(v_ret);
close cur;
end ;
roaddepart_where := ' and ROADDEPART=''' || AROADDEPART || '''';else
roaddepart_where := '';
end if;
...
open ds for
'select * from ASE_ROADVIORATE where 1=1 ' || roaddepart_where || rid_where || vioType_where;
像这位兄弟这样的,我还有一个疑问
为什么要写成这样的:v_name := ' table_code = '||''''||v_code||''''
我想的是这样写的:v_name := 'table_code=' || ''' || v_code || '''
了解的望能解释一下,另在pl/sql里面能不能直接打印出来我构造的sql语句字符串
dbms_output.put_line(……) 就可以了'''' 这样出来的才是一个 '