create or replace PROCEDURE P_TOTAL_LOOP(ORGID in NUMBER,sDate in date,
HTYPE in NUMBER,USEFLAG in NUMBER,
FACTORYID in NUMBER,COMMID in NUMBER,
VOLID in NUMBER,CAPA in NUMBER,
HU_NUMBER in NUMBER,RTU_NUMBER in NUMBER) IS
VI_LOOP INTEGER := 0;
VC_SQL VARCHAR2(1280);
VC_SQL1 VARCHAR2(1280);
TYPE CUR_ACQDATA IS REF CURSOR;
ACQDATA CUR_ACQDATA;
REC d_terminal_total%ROWTYPE;
BEGIN
VC_SQL := 'SELECT * FROM d_terminal_total where orgid=' || V_orgid
|| ' and datatime=' || V_sDate || ''
|| ' and htype=' || V_HTYPE
|| ' and useflag=' || V_USEFLAG
|| ' and FACTORYID=' || V_FACTORYID
|| ' and COMMID=' || V_COMMID
|| ' and VOLID=' || V_VOLID
|| ' and CAPA=' || V_CAPA ;
OPEN ACQDATA FOR VC_SQL;
FETCH ACQDATA
INTO REC;
IF ACQDATA%FOUND THEN
dbms_output.put_line('bian hao wei zhao dao');
/* VC_SQL1 := 'update D_STATISTICS_total '
|| ' set HU_NUMBER=' || V_HU_NUMBER || ','
|| ' RTU_NUMBER=' || V_RTU_NUMBER || ' '
|| ' where orgid=' || V_orgid
|| ' and datatime=f_d2i(' || V_sDate || ')'
|| ' and htype=' || V_HTYPE
|| ' and useflag=' || V_USEFLAG
|| ' and FACTORYID=' || V_FACTORYID
|| ' and COMMID=' || V_COMMID
|| ' and VOLID=' || V_VOLID
|| ' and CAPA=' || V_CAPA ; */
/* update D_STATISTICS_total
set HU_NUMBER= HU_NUMBER, RTU_NUMBER=RTU_NUMBER
where orgid=orgid and datatime=sDate and htype= HTYPE and useflag= USEFLAG
and FACTORYID= FACTORYID and COMMID= COMMID and VOLID= VOLID and CAPA= CAPA ;
commit;*/
ELSE
dbms_output.put_line('biao yi geng xin');
VC_SQL1:='insert into d_terminal_total(orgid,datatime,htype,useflag,factoryid,commid,volid,capa,hu_number,rtu_number)
values(' || V_orgid ','
|| V_sDate || ','
|| V_HTYPE
|| V_USEFLAG ','
|| V_FACTORYID ','
|| V_COMMID ','
|| V_VOLID ','
|| V_CAPA ')' ;
EXECUTE IMMEDIATE VC_SQL1;
/* insert into d_terminal_total
values(V_orgid,V_sDate,V_HTYPE,V_USEFLAG,V_FACTORYID,V_COMMID,V_VOLID,V_CAPA) ; */
COMMIT;
END IF;
/* IF (ACQDATA%ISOPEN) THEN
CLOSE ACQDATA;
END IF; */
END P_TOTAL_LOOP;
HTYPE in NUMBER,USEFLAG in NUMBER,
FACTORYID in NUMBER,COMMID in NUMBER,
VOLID in NUMBER,CAPA in NUMBER,
HU_NUMBER in NUMBER,RTU_NUMBER in NUMBER) IS
VI_LOOP INTEGER := 0;
VC_SQL VARCHAR2(1280);
VC_SQL1 VARCHAR2(1280);
TYPE CUR_ACQDATA IS REF CURSOR;
ACQDATA CUR_ACQDATA;
REC d_terminal_total%ROWTYPE;
BEGIN
VC_SQL := 'SELECT * FROM d_terminal_total where orgid=' || V_orgid
|| ' and datatime=' || V_sDate || ''
|| ' and htype=' || V_HTYPE
|| ' and useflag=' || V_USEFLAG
|| ' and FACTORYID=' || V_FACTORYID
|| ' and COMMID=' || V_COMMID
|| ' and VOLID=' || V_VOLID
|| ' and CAPA=' || V_CAPA ;
OPEN ACQDATA FOR VC_SQL;
FETCH ACQDATA
INTO REC;
IF ACQDATA%FOUND THEN
dbms_output.put_line('bian hao wei zhao dao');
/* VC_SQL1 := 'update D_STATISTICS_total '
|| ' set HU_NUMBER=' || V_HU_NUMBER || ','
|| ' RTU_NUMBER=' || V_RTU_NUMBER || ' '
|| ' where orgid=' || V_orgid
|| ' and datatime=f_d2i(' || V_sDate || ')'
|| ' and htype=' || V_HTYPE
|| ' and useflag=' || V_USEFLAG
|| ' and FACTORYID=' || V_FACTORYID
|| ' and COMMID=' || V_COMMID
|| ' and VOLID=' || V_VOLID
|| ' and CAPA=' || V_CAPA ; */
/* update D_STATISTICS_total
set HU_NUMBER= HU_NUMBER, RTU_NUMBER=RTU_NUMBER
where orgid=orgid and datatime=sDate and htype= HTYPE and useflag= USEFLAG
and FACTORYID= FACTORYID and COMMID= COMMID and VOLID= VOLID and CAPA= CAPA ;
commit;*/
ELSE
dbms_output.put_line('biao yi geng xin');
VC_SQL1:='insert into d_terminal_total(orgid,datatime,htype,useflag,factoryid,commid,volid,capa,hu_number,rtu_number)
values(' || V_orgid ','
|| V_sDate || ','
|| V_HTYPE
|| V_USEFLAG ','
|| V_FACTORYID ','
|| V_COMMID ','
|| V_VOLID ','
|| V_CAPA ')' ;
EXECUTE IMMEDIATE VC_SQL1;
/* insert into d_terminal_total
values(V_orgid,V_sDate,V_HTYPE,V_USEFLAG,V_FACTORYID,V_COMMID,V_VOLID,V_CAPA) ; */
COMMIT;
END IF;
/* IF (ACQDATA%ISOPEN) THEN
CLOSE ACQDATA;
END IF; */
END P_TOTAL_LOOP;
show errors
HTYPE in NUMBER,USEFLAG in NUMBER,
FACTORYID in NUMBER,COMMID in NUMBER,
VOLID in NUMBER,CAPA in NUMBER,
HU_NUMBER in NUMBER,RTU_NUMBER in NUMBER)
和下面sql里的参数名称不一致
VC_SQL := 'SELECT * FROM d_terminal_total where orgid=' || V_orgid
|| ' and datatime=' || V_sDate || ''
|| ' and htype=' || V_HTYPE
|| ' and useflag=' || V_USEFLAG
|| ' and FACTORYID=' || V_FACTORYID
|| ' and COMMID=' || V_COMMID
|| ' and VOLID=' || V_VOLID
|| ' and CAPA=' || V_CAPA ;
IF ACQDATA%FOUND THEN
dbms_output.put_line('biao yi geng xin');
ELSE
insert into d_terminal_total values(V_orgid,f_d2i(V_sDate),V_HTYPE,V_USEFLAG,V_FACTORYID,V_COMMID,V_VOLID,V_CAPA,V_HU_NUMBER,V_RTU_NUMBER);
END IF;