Oracle12C拼接where条件

非著名魔兽解说 发布于 2016/09/21 15:02
阅读 280
收藏 0

@IdleMan 你好,想跟你请教个问题:

declare
  addr      varchar2(200);
	inputStr varchar2(1000);
	condition varchar2(1000);
	school    varchar2(200);
	cla       varchar2(200);
  season    varchar2(200);
begin
--inputStr相当于存储过程的输入参数,是JSON格式。
inputStr := '{"school":"XX市第20中学","cla":"三年二班","season":""}'

SELECT JSON_VALUE(inputStr, '$.school') INTO school FROM dual;--读取输入的值
SELECT JSON_VALUE(inputStr, '$.cla') INTO cla FROM dual;--读取输入的值
SELECT JSON_VALUE(inputStr, '$.season') INTO season FROM dual;--读取输入的值

select stuName,stuAge,stuNo from stu  where XXXXXX; 

--这里想要让condition的值是inputStr中读取的值,当school,cla,season为空的时候不拼接或者拼接1=1
--当school,cla,season不为空则拼接条件赋值给condition作为where条件
--请问select stuName,stuAge,stuNo from stu  where XXXXXX; 这部分应该怎么写?
end
commit;



加载中
0
IdleMan
IdleMan

if school is null and cla is null and season is null then

  execute immediate 'begin for item in(select stuName,stuAge,stuNo from stu) loop
  .....
  end loop end;';

else

 execute immediate 'begin for item in(select stuName,stuAge,stuNo from stu where xxx) loop
  .....
  end loop end;';

end if;

0
非著名魔兽解说
非著名魔兽解说

引用来自“IdleMan”的评论

if school is null and cla is null and season is null then

  execute immediate 'begin for item in(select stuName,stuAge,stuNo from stu) loop
  .....
  end loop end;';

else

 execute immediate 'begin for item in(select stuName,stuAge,stuNo from stu where xxx) loop
  .....
  end loop end;';

end if;

最后的位置原有的end loop; 本身是有分号的,这里应该是像您写的这样end loop end;';还是应该写成

end loop; end;'

0
两个人-
两个人-
declare
  addr      varchar2(200);
    inputStr varchar2(1000);
    condition varchar2(1000);
    school    varchar2(200);
    cla       varchar2(200);
  season    varchar2(200);
  v_sql clob;
begin
--inputStr相当于存储过程的输入参数,是JSON格式。
inputStr := '{"school":"XX市第20中学","cla":"三年二班","season":""}'
 
SELECT JSON_VALUE(inputStr, '$.school') INTO school FROM dual;--读取输入的值
SELECT JSON_VALUE(inputStr, '$.cla') INTO cla FROM dual;--读取输入的值
SELECT JSON_VALUE(inputStr, '$.season') INTO season FROM dual;--读取输入的值


  if school is not null then school:=' and '||school; end if;
  if cla is not null then cla:=' and '||cla; end if;
  if season is not null then season:=' and '||season; end if;
  
  v_sql:='select stuName,stuAge,stuNo from stu where 1=1 '||school||cla||season;
   
 dbms_output(v_sql); 
end;

commit;


试一下

返回顶部
顶部