//存储过程如下,关键点只有星号处(
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,'或者其他形式,再在存储过程中组合这个条件,如何实现呢?
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,'或者其他形式,再在存储过程中组合这个条件,如何实现呢?
解决方案 »
- -- 同志们:尽量让你的查询走索引吧! --
- sql 分行查询怎么写?sql语句 subitemlist
- oracle rac终于安装完了,最后还是遇到了一个小问题
- oracle的 同义词检索问题
- Oracle数据库Redo故障的恢复
- Oracle 9i 安装问题!不知道大家有没有遇到过这个问题!
- 替换clob字段中的某几个字符的sql或储存过程该怎么写?
- pl/sql中流程控制有没有 select case 或choose case之类的语句〉难道只有用if 吗?
- oracle中,如何建立数据库链接(dblink)
- 編寫一個函數,实现加減乘除的功能,要求有異常處理功能,兄弟必须得用orcale写哦
- 一个存储过程
- PL/SQL中怎样用sql语句或function函数处理以下数据
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
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;
/
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
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;.....
你改成INSERTALARM_RECORD_FOR_R_TEST.SSNO_1_;就好了