碰到一个棘手存储过程拼接字符串的问题

冬冬冬冬冬冬冬冬冬冬 2014-07-29 09:11:35 
create or replace package body LogOperation2 is

  procedure pLog_system_sel

  (

    plistLog      out listlog,

    pPageSize     in  integer,

    pPageCurrent  in  integer,

    pRowCount     out integer,

    pPageCount    out integer,

    pStartTime    in  nvarchar2,

    pEndTime      in  nvarchar2,

    OrganUnitName in  nvarchar2,

    OrderNo       in  nvarchar2

  )

  as

  sqlstr      varchar2(1000);

  pBegRow     integer;

  pEndRow     integer;

  startTime   date;

  endTime     date;

  begin

    --定义开始和结束行数

    pBegRow := (pPageCurrent - 1) * pPageSize + 1;

    pEndRow := pPageCurrent * pPageSize;

    --日期不为空，把字符串转换成日期

    if(length(pStartTime) > 0) and (length(pEndTime) > 0) then

      startTime := to_date(concat(pStartTime,' 00:00:01'),'yyyy-mm-dd hh24:mi:ss');

      endTime := to_date(concat(pEndTime,' 23:59:59'),'yyyy-mm-dd hh24:mi:ss');

    end if;

    --开始确认行数

    sqlstr := 'select count(1) into :pRowCount from Log_system a  where 1 = 1 ';

    --时间

    if(length(pStartTime) > 0) and (length(pEndTime) > 0) then

      sqlstr := sqlstr || ' and a.ExamineDate between '|| startTime ||' and '|| endTime;

    end if;

    --名称

    if length(OrganUnitName) > 0 then

      sqlstr := sqlstr || ' and b.UnitName like ''%' ||OrganUnitName|| '%''';

    end if;

    --单号

    if length(OrderNo) > 0 then

      sqlstr := sqlstr ||' and c.OrderNo like ''%'||OrderNo || '%''';

    end if;

    execute immediate sqlstr into pRowCount;

    --计算页数

    if pRowCount = 0 then

      pPageCount := 0;

    else

      pPageCount := ceil(pRowCount * 1.0 / pPageSize) ;

    end if;



    --结果集

    sqlstr := 'select * from (';

    sqlstr := sqlstr || 'select rownum as rn,a.Examine_ID,a.User_ID,Status_ID,ExamineRemark,ExamineDate,';

    sqlstr := sqlstr || ' OrderNo from Log_system where 1=1';

    --时间

    if(length(pStartTime) > 0) and (length(pEndTime) > 0) then

      sqlstr := sqlstr || ' and ExamineDate between '|| startTime ||' and '|| endTime;

    end if;

    --单位名称

    if length(OrganUnitName) > 0 then

      sqlstr := sqlstr || ' and OrganUnitName like ''%' ||OrganUnitName|| '%''';

    end if;

    --订单号

    if length(OrderNo) > 0 then

      sqlstr := sqlstr ||' and OrderNo like ''%'||OrderNo || '%''';

    end if;

    

    sqlstr := sqlstr || ' )where rn between :pBegRow and :pEndRow';

    pMsg := sqlstr;

    open plistLog for sqlstr using pBegRow,pEndRow;

  end;

end;

现在问题是，因为where后面的条件是动态拼接的，如果没有那个条件的话，就不会出现在sqlstr字符串中，那么execute immediate 后面的into和open plistLog后面的using也同时需要做出调整，否则会出现，参数不符合的错误，是不是into后面的和using也能动态拼接？
...全文
309 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
小灰狼W 2014-07-29
  • 打赏
  • 举报
 回复
上面的代码貌似没有后面描述得问题 into和using后面的参数不能动态拼接，后面跟的是多个变量，自然不能放到一个变量里 一般来说，select into 后面跟的变量都是固定的。每个查询都有检索的目标，这个目标不确定是件很奇怪的事。如果真有这样的需求，可以用下面的方法来绕过去: 例如 execute immediate 'select a,b,c,d,e from ...' into v_a,v_b,v_c,v_d,v_e; 在某些条件下，只能检索出a,b,c 那么用d,e用null来占位即可 execute immediate 'select a,b,c,null,null from ...' into v_a,v_b,v_c,v_d,v_e; 至于using后面的变量，建议只用在固定的条件上。变动的条件，直接将值拼接在语句中。using clause的一个好处是可以使用绑定变量，但是它根据动态语句中的绑定变量标识符来按顺序对号入座，给动态的条件带来很大的麻烦
冬冬冬冬冬冬冬冬冬冬 2014-07-29
  • 打赏
  • 举报
 回复
这样的话有5个条件，就疯了。
冬冬冬冬冬冬冬冬冬冬 2014-07-29
  • 打赏
  • 举报
 回复
引用 3 楼 zlloct 的回复:
[quote=引用 2 楼 chaoyangzhixue 的回复:] [quote=引用 1 楼 zlloct 的回复:] [quote=引用 楼主 chaoyangzhixue 的回复:] quote] 你加个if条件来判断就行了啊，不同的sqlstr执行不同的的SQL语句
嗯，就是因为我加了if才会有的参数不能出现在sql字符串中，才导致执行sql字符串后，出现“缺失关键字”的重大问题。[/quote] 我的意思是你根据sqlstr中是否含有where来通过if判断进行不同的open。 比如： if instr(sqlstr,':spBegRow')<>=0 then open plistLog for sqlstr using pBegRow,pEndRow; else open plistLog for sqlstr; end if;[/quote] 那么if的嵌套深度就变成2的乘方了。比如条件有两个，条件1和条件2，那么if只能这么写了 

if 条件1 then
  if 条件2 then
    open plistLog for sqlstr using 条件1,条件2;
  else
    open plistLog for sqlstr using 条件1;
  end if;
else
  if 条件2 then
    open plistLog for sqlstr using 条件2;
  else
    open plistLog for sqlstr;
  end if;
end if;
CT_LXL 2014-07-29
  • 打赏
  • 举报
 回复
引用 2 楼 chaoyangzhixue 的回复:
[quote=引用 1 楼 zlloct 的回复:] [quote=引用 楼主 chaoyangzhixue 的回复:] quote] 你加个if条件来判断就行了啊，不同的sqlstr执行不同的的SQL语句
嗯，就是因为我加了if才会有的参数不能出现在sql字符串中，才导致执行sql字符串后，出现“缺失关键字”的重大问题。[/quote] 我的意思是你根据sqlstr中是否含有where来通过if判断进行不同的open。 比如： if instr(sqlstr,':spBegRow')<>=0 then open plistLog for sqlstr using pBegRow,pEndRow; else open plistLog for sqlstr; end if;
冬冬冬冬冬冬冬冬冬冬 2014-07-29
  • 打赏
  • 举报
 回复
引用 1 楼 zlloct 的回复:
[quote=引用 楼主 chaoyangzhixue 的回复:] 
create or replace package body LogOperation2 is
  procedure pLog_system_sel
  (
    plistLog      out listlog,
    pPageSize     in  integer,
    pPageCurrent  in  integer,
    pRowCount     out integer,
    pPageCount    out integer,
    pStartTime    in  nvarchar2,
    pEndTime      in  nvarchar2,
    OrganUnitName in  nvarchar2,
    OrderNo       in  nvarchar2
  )
  as
  sqlstr      varchar2(1000);
  pBegRow     integer;
  pEndRow     integer;
  startTime   date;
  endTime     date;
  begin
    --定义开始和结束行数
    pBegRow := (pPageCurrent - 1) * pPageSize + 1;
    pEndRow := pPageCurrent * pPageSize;
    --日期不为空，把字符串转换成日期
    if(length(pStartTime) > 0) and (length(pEndTime) > 0) then
      startTime := to_date(concat(pStartTime,' 00:00:01'),'yyyy-mm-dd hh24:mi:ss');
      endTime := to_date(concat(pEndTime,' 23:59:59'),'yyyy-mm-dd hh24:mi:ss');
    end if;
    --开始确认行数
    sqlstr := 'select count(1) into :pRowCount from Log_system a  where 1 = 1 ';
    --时间
    if(length(pStartTime) > 0) and (length(pEndTime) > 0) then
      sqlstr := sqlstr || ' and a.ExamineDate between '|| startTime ||' and '|| endTime;
    end if;
    --名称
    if length(OrganUnitName) > 0 then
      sqlstr := sqlstr || ' and b.UnitName like ''%' ||OrganUnitName|| '%''';
    end if;
    --单号
    if length(OrderNo) > 0 then
      sqlstr := sqlstr ||' and c.OrderNo like ''%'||OrderNo || '%''';
    end if;
    execute immediate sqlstr into pRowCount;
    --计算页数
    if pRowCount = 0 then
      pPageCount := 0;
    else
      pPageCount := ceil(pRowCount * 1.0 / pPageSize) ;
    end if;

    --结果集
    sqlstr := 'select * from (';
    sqlstr := sqlstr || 'select rownum as rn,a.Examine_ID,a.User_ID,Status_ID,ExamineRemark,ExamineDate,';
    sqlstr := sqlstr || ' OrderNo from Log_system where 1=1';
    --时间
    if(length(pStartTime) > 0) and (length(pEndTime) > 0) then
      sqlstr := sqlstr || ' and ExamineDate between '|| startTime ||' and '|| endTime;
    end if;
    --单位名称
    if length(OrganUnitName) > 0 then
      sqlstr := sqlstr || ' and OrganUnitName like ''%' ||OrganUnitName|| '%''';
    end if;
    --订单号
    if length(OrderNo) > 0 then
      sqlstr := sqlstr ||' and OrderNo like ''%'||OrderNo || '%''';
    end if;
    
    sqlstr := sqlstr || ' )where rn between :pBegRow and :pEndRow';
    pMsg := sqlstr;
    open plistLog for sqlstr using pBegRow,pEndRow;
  end;
end;
现在问题是，因为where后面的条件是动态拼接的，如果没有那个条件的话，就不会出现在sqlstr字符串中，那么execute immediate 后面的into和open plistLog后面的using也同时需要做出调整，否则会出现，参数不符合的错误，是不是into后面的和using也能动态拼接？
你加个if条件来判断就行了啊，不同的sqlstr执行不同的的SQL语句[/quote] 嗯，就是因为我加了if才会有的参数不能出现在sql字符串中，才导致执行sql字符串后，出现“缺失关键字”的重大问题。
CT_LXL 2014-07-29
  • 打赏
  • 举报
 回复
引用 楼主 chaoyangzhixue 的回复:
create or replace package body LogOperation2 is
  procedure pLog_system_sel
  (
    plistLog      out listlog,
    pPageSize     in  integer,
    pPageCurrent  in  integer,
    pRowCount     out integer,
    pPageCount    out integer,
    pStartTime    in  nvarchar2,
    pEndTime      in  nvarchar2,
    OrganUnitName in  nvarchar2,
    OrderNo       in  nvarchar2
  )
  as
  sqlstr      varchar2(1000);
  pBegRow     integer;
  pEndRow     integer;
  startTime   date;
  endTime     date;
  begin
    --定义开始和结束行数
    pBegRow := (pPageCurrent - 1) * pPageSize + 1;
    pEndRow := pPageCurrent * pPageSize;
    --日期不为空，把字符串转换成日期
    if(length(pStartTime) > 0) and (length(pEndTime) > 0) then
      startTime := to_date(concat(pStartTime,' 00:00:01'),'yyyy-mm-dd hh24:mi:ss');
      endTime := to_date(concat(pEndTime,' 23:59:59'),'yyyy-mm-dd hh24:mi:ss');
    end if;
    --开始确认行数
    sqlstr := 'select count(1) into :pRowCount from Log_system a  where 1 = 1 ';
    --时间
    if(length(pStartTime) > 0) and (length(pEndTime) > 0) then
      sqlstr := sqlstr || ' and a.ExamineDate between '|| startTime ||' and '|| endTime;
    end if;
    --名称
    if length(OrganUnitName) > 0 then
      sqlstr := sqlstr || ' and b.UnitName like ''%' ||OrganUnitName|| '%''';
    end if;
    --单号
    if length(OrderNo) > 0 then
      sqlstr := sqlstr ||' and c.OrderNo like ''%'||OrderNo || '%''';
    end if;
    execute immediate sqlstr into pRowCount;
    --计算页数
    if pRowCount = 0 then
      pPageCount := 0;
    else
      pPageCount := ceil(pRowCount * 1.0 / pPageSize) ;
    end if;

    --结果集
    sqlstr := 'select * from (';
    sqlstr := sqlstr || 'select rownum as rn,a.Examine_ID,a.User_ID,Status_ID,ExamineRemark,ExamineDate,';
    sqlstr := sqlstr || ' OrderNo from Log_system where 1=1';
    --时间
    if(length(pStartTime) > 0) and (length(pEndTime) > 0) then
      sqlstr := sqlstr || ' and ExamineDate between '|| startTime ||' and '|| endTime;
    end if;
    --单位名称
    if length(OrganUnitName) > 0 then
      sqlstr := sqlstr || ' and OrganUnitName like ''%' ||OrganUnitName|| '%''';
    end if;
    --订单号
    if length(OrderNo) > 0 then
      sqlstr := sqlstr ||' and OrderNo like ''%'||OrderNo || '%''';
    end if;
    
    sqlstr := sqlstr || ' )where rn between :pBegRow and :pEndRow';
    pMsg := sqlstr;
    open plistLog for sqlstr using pBegRow,pEndRow;
  end;
end;
现在问题是，因为where后面的条件是动态拼接的，如果没有那个条件的话，就不会出现在sqlstr字符串中，那么execute immediate 后面的into和open plistLog后面的using也同时需要做出调整，否则会出现，参数不符合的错误，是不是into后面的和using也能动态拼接？
你加个if条件来判断就行了啊，不同的sqlstr执行不同的的SQL语句
容器技术助力企业应用平滑上云案例分享
越来越多的企业选择用Docker作为上云的迁移方案，如何让上云的过程更加平滑无缝，如何支持或适配不同的日志、监控、存储、部署能力成了一个棘手的问题。本次分享主要聚焦上述问题进行探讨。
mybatis mysql 调用存储过程 多个返回值_被Mybatis坑惨了
关注米兜Java并置顶，一起涨姿势～关注米兜Spring2020热点推荐1抛去try {...} catch {...} finally {...}，让代码更轻便2无所不能的JOIN，来一波速学3不要上来就分库分表,啥都不知道！4阿里为何建议返回值不用枚举？...
为什么JSON.parse会损坏大数字，如何解决这个问题？
微信搜索 【大迁世界】, 我会第一时间和你分享前端行业趋势，学习途径等等。本文 GitHub已收录，有一线大厂面试完整考点、资料以及我的系列文章。从10多年前JSON在线编辑器的早期开始，用户经常反映编辑器有时会破坏...
java读取1t json文件_webmagic 实现爬取数据以 JSON 格式保存到数据库
最近的工作中碰到一个比较棘手的问题: 采集的数据的格式不一样, 且不同的网页的内容出现的也比较随意, 当时也是摸不到头脑.经过接近半天的 摸索:首先采集到目标位置的信息;接着处理采集的信息转化为 json...
webmagic 实现爬取数据以 JSON 格式保存到数据库
最近的工作中碰到一个比较棘手的问题: 采集的数据的格式不一样, 且不同的网页的内容出现的也比较随意, 当时也是摸不到头脑. 经过接近半天的 摸索: 　首先采集到目标位置的信息; 　接着处理采集的...
Oracle

17,086

社区成员

55,238

社区内容

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章