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种情况。
解决方案 »
- 100分求一sql语句
- oracle 中关于update select 语句的问题
- VMWARE里oracle安装报错"无法确定主机的IP地址时产生该异常错误"
- ORACLE,如何更改数据库用户名和密码?
- 还算简单的问题---要求使用自表关联和exists两种方法实现
- 急求解决ORACLE9I的语句问题
- 在设计阶段,我们只有数据库的表结构,这时候,有没有办法预估数据库的物理大小?
- 在线等如何在oracle中只备份存储过程/函数/和包(100分)
- 我刚才发的帖子怎么没显示??再贴一遍把, 关于oracle没有响应的问题
- 在redhat enterprise 3下安装oracle9i错误提示求救!
- PL/SQL 开发问题
- 建一个长名字的数据库
最后那个动态游标是错的'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(……) 就可以了'''' 这样出来的才是一个 '