在PL/SQL创建procedure如下,关于变量v_lottype的使用,请高手指点指点!create or replace procedure test_procedure is v_lottype varchar2(40);
v_eoh number;
v_eoh1 number;begin
v_lottype:='(''P'',''R'',''M'')';
v_cuthour:='00'; --v_eoh
select nvl(sum(componentqty),0) into v_eoh from table1 ----返回结果为0,不正确 where lottype in v_lottype
and histdate=to_char(sysdate,'yyyymmdd')
and hour= v_cuthour ;
select nvl(sum(componentqty),0) into v_eoh1 from table1 ----返回正确结果1500 where lottype in ('P','R','M')
and histdate=to_char(sysdate,'yyyymmdd')
and hour= v_cuthour ;end;
v_eoh number;
v_eoh1 number;begin
v_lottype:='(''P'',''R'',''M'')';
v_cuthour:='00'; --v_eoh
select nvl(sum(componentqty),0) into v_eoh from table1 ----返回结果为0,不正确 where lottype in v_lottype
and histdate=to_char(sysdate,'yyyymmdd')
and hour= v_cuthour ;
select nvl(sum(componentqty),0) into v_eoh1 from table1 ----返回正确结果1500 where lottype in ('P','R','M')
and histdate=to_char(sysdate,'yyyymmdd')
and hour= v_cuthour ;end;
而
v_lottype:='(''P'',''R'',''M'')'却只能理解为一个字符串
EXECUTE IMMEDIATE 'select nvl(sum(componentqty),0) from table1 where lottype in ' || v_lottype ||
'and histdate=to_char(sysdate,''yyyymmdd'') and hour= v_cuthour' INTO v_eoh;