高分求解一个Oracle在存储过程中组合处理输入参数的问题。

Question

//存储过程如下，关键点只有星号处(
StartTime IN DATE  DEFAULT NULL,
EndTime IN DATE  DEFAULT NULL,
Duration IN NUMBER DEFAULT NULL,
SSNO_1 IN NUMBER DEFAULT NULL
)
AS
StartTime_1 DATE:=StartTime;
EndTime_1 DATE:=EndTime;
Duration_ NUMBER(10,0):=Duration;
SSNO_1_ NUMBER(3,0) :=SSNO_1;StoO_selcnt INTEGER;
StoO_error  INTEGER;
StoO_rowcnt INTEGER;
StoO_crowcnt INTEGER := 0;
StoO_fetchstatus INTEGER := 0;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
BEGIN
  /* fetch records from alarm_recovery_record */
  BEGIN
    DECLARE
     v_SC_ID ALARM_RECOVERY_RECORD.SC_ID%TYPE;
     v_SS_NO ALARM_RECOVERY_RECORD.SS_NO%TYPE;
     v_SU_NO ALARM_RECOVERY_RECORD.SU_NO%TYPE;
     v_SO_NO ALARM_RECOVERY_RECORD.SO_NO%TYPE;
     v_SA_NO ALARM_RECOVERY_RECORD.SA_NO%TYPE;
     v_ALARM_ID ALARM_RECOVERY_RECORD.ALARM_ID%TYPE;
     v_ALARM_TIME ALARM_RECOVERY_RECORD.ALARM_TIME%TYPE;
     v_ALARM_DURATION ALARM_RECOVERY_RECORD.ALARM_DURATION%TYPE;
    
    /*************************************/   
    CURSOR AlarmRecoveryRecordCursor IS
         SELECT SC_ID,SS_NO,SU_NO,SO_NO,SA_NO,ALARM_ID,ALARM_TIME,ALARM_DURATION,FROM SA.ALARM_RECOVERY_RECORD
            WHERE Alarm_Time>=INSERTALARM_RECORD_FOR_R_TEST.StartTime_1
                  and Alarm_Time<=INSERTALARM_RECORD_FOR_R_TEST.EndTime_1
and ALARM_DURATION>=INSERTALARM_RECORD_FOR_R_TEST.Duration_
                  and SS_NO=INSERTALARM_RECORD_FOR_R_TEST.SSNO_1_;
    /*************************************/   
        
      
     BEGIN
    OPEN AlarmRecoveryRecordCursor;
    StoO_error   := 0;
    StoO_rowcnt  := 0;
    LOOP
      FETCH AlarmRecoveryRecordCursor INTO v_SC_ID,v_SS_NO,v_SU_NO,v_SO_NO,v_SA_NO,v_ALARM_ID,v_ALARM_TIME,v_ALARM_DURATION;
      EXIT WHEN  AlarmRecoveryRecordCursor%NOTFOUND;
      BEGIN
         StoO_rowcnt := StoO_rowcnt+1;
         INSERT INTO SA.ALARM_RECORD_FOR_REPORT(SC_ID, SS_NO, SU_NO, SO_NO, SA_NO, Alarm_ID, Alarm_Time, Alarm_Duration)
            VALUES(v_SC_ID,v_SS_NO,v_SU_NO,v_SO_NO,v_SA_NO,v_ALARM_ID,v_ALARM_TIME,v_ALARM_DURATION); 
         EXCEPTION
         WHEN NO_DATA_FOUND OR DUP_VAL_ON_INDEX THEN
         NULL;
         WHEN OTHERS THEN
           CLOSE AlarmRecoveryRecordCursor;
         StoO_error := SQLCODE;
         StoO_errmsg := SQLERRM;
         raise_application_error(SQLCODE, SQLERRM,true); 
      END;
    END LOOP;
    CLOSE AlarmRecoveryRecordCursor;
     END;
   END;
  
END INSERTALARM_RECORD_FOR_R_TEST;上面的存储过程是对的，但由于字节型数据SSNO_1有多个值，即有可能SSNO_1=1 || SSNO_1=2 || SSNO_1=3等等，如果采取上面的存储过程，我在外面调用这个存储过程时只能循环调用它。我希望能采对SSNO_1改为字符串输入参数，比如SSNO_1='1,2,3,4,'或者其他形式，再在存储过程中组合这个条件，如何实现呢？

Answer 1

Instring For Matching First Value Found
SELECT INSTR(<value>, <value_to_match>, <direction>, <instance>
FROM dual;
List parsing first valueTake up to the character before the first comma
SELECT SUBSTR('abc,def,ghi', 1 ,INSTR('abc,def,ghi', ',', 1, 1))
FROM dual;
 Take the value between the commas
SELECT SUBSTR('abc,def,ghi',  INSTR('abc,def,ghi',',', 1, 1)+1,
INSTR('abc,def,ghi',',',1,2)-INSTR('abc,def,ghi',',',1,1)-1)
FROM dual;
 Take the value after the last comma
SELECT SUBSTR('abc,def,ghi', INSTR('abc,def,ghi',',', 1, 2)+1)
FROM dual;
http://www.psoug.org/reference/substr_instr.html

Answer 2

用动态游标。
create or replace procedure t_test0(s varchar2)
as
  type myrctype is ref cursor; 
  sqlstr varchar2(2000);
  rc myrctype;
  v_id number;
  v_name varchar(100);
begin
  sqlstr :='select * from test0 where id in ('||s||')';
  open rc for sqlstr;
  loop
    fetch rc into v_id,v_name;
    exit when rc%NOTFOUND;
    dbms_output.put_line('id='||v_id||',NAME='||v_name);
  end loop;
end;
/

Answer 3

http://72.14.207.104/search?q=cache:eNF3DxaIroIJ:www.builderau.com.au/architect/database/print.htm%3FTYPE%3Dstory%26AT%3D39129882-39024547t-20000985c+split+string+oracle&hl=en
create or replace type split_tbl as table of varchar2(32767);
/
show errors;create or replace function split
(
    p_list varchar2,
    p_del varchar2 := ','
) return split_tbl pipelined
is
    l_idx    pls_integer;
    l_list    varchar2(32767) := p_list;
AA
    l_value    varchar2(32767);
begin
    loop
        l_idx := instr(l_list,p_del);
        if l_idx > 0 then
            pipe row(substr(l_list,1,l_idx-1));
            l_list := substr(l_list,l_idx+length(p_del));        else
            pipe row(l_list);
            exit;
        end if;
    end loop;
    return;
end split;
/
show errors;With this function, I can run a query like this:SQL> select * from table(split('one,two,three'));one
two
three

Answer 4

sqls:='SELECT SC_ID,SS_NO,SU_NO,SO_NO,SA_NO,ALARM_ID,ALARM_TIME,ALARM_DURATION,FROM SA.ALARM_RECOVERY_RECORD WHERE Alarm_Time>=INSERTALARM_RECORD_FOR_R_TEST.StartTime_1
and Alarm_Time<=INSERTALARM_RECORD_FOR_R_TEST.EndTime_1 and ALARM_DURATION>=INSERTALARM_RECORD_FOR_R_TEST.Duration_ and'              
i:=1;
v_ssno:='abc,def,ghi';
str:='';
loop
if((instr(v_ssno,',',i,1))!=0) then 
if (i=1) then 
str:=str||'(SS_NO='''||substr(v_ssno,i,(instr(v_ssno,',',i,1)-i))||'''';
i:=(instr(v_ssno,',',i,1)+1);                  
else 
str:=str||' or SS_NO='''||substr(v_ssno,i,(instr(v_ssno,',',i,1)-i))||'''';
i:=(instr(v_ssno,',',i,1)+1);
end if;
insert into zc.tmp(v_field_id)values(i);
else
str:=str||' or SS_NO='''||substr(v_ssno,i)||''')';
insert into zc.tmp(v_field_id)values(i);
exit;
end if;
end loop;sqls:=sqls||str||';'
---上边把sql语句拼好了.下面是得到一个cursor
open AlarmRecoveryRecordCursor for sqls;loop
fetch AlarmRecoveryRecordCursor into v_SC_ID,v_SS_NO,v_SU_NO,v_SO_NO,v_SA_NO,v_ALARM_ID,v_ALARM_TIME,v_ALARM_DURATION;EXIT WHEN  AlarmRecoveryRecordCursor%NOTFOUND;.....

Answer 5

不好意思上面v_ssno:='abc,def,ghi';是我测试用底
你改成INSERTALARM_RECORD_FOR_R_TEST.SSNO_1_;就好了

Answer 6

谢谢楼上的。请问 zc.tmp(v_field_id)，还需要重新定义吧。

Answer 7

不好意思insert into zc.tmp(v_field_id)values(i);这边得zc.tmp(v_field_id)这个,是我得临时表,跟你得没什么关系,是为了我自己测试的时候检查我得到的值才用的.