SQL错误：ORA-01403：未找到数据
|
当消防更新查询
update JISPBILCORBILLINGPRD501.TBLMACCOUNTADDRESS set Address1=\'NehateSir\',stateid=\'STT0002\' where accountid=\'ACC000000068\'
Error starting at line 4 in command:
update JISPBILCORBILLINGPRD501.TBLMACCOUNTADDRESS set Address1=\'NehateSir\',stateid=\'STT0002\' where accountid=\'ACC000000068\'
Error report:
SQL Error: ORA-01403: no data found
ORA-06512: at \"WOM.TRGBILLINGADDRESS\", line 10
ORA-04088: error during execution of trigger \'WOM.TRGBILLINGADDRESS\'
01403. 00000 - \"no data found\"
*Cause:
*Action:
create or replace
TRIGGER TRGBILLINGADDRESS AFTER
INSERT OR
DELETE OR
UPDATE ON jispbilcorbillingprd501.TBLMACCOUNTADDRESS FOR EACH ROW
DECLARE
add1 varchar2(200) ;
add2 varchar2(200);
cityid varchar2(200);
stateid varchar2(200);
pincd varchar2(200);
parlcid varchar2(12);
BEGIN
select endbcirclename into parlcid from jispbilcorbillingprd501.tblmaccountprofile where accountid =:NEW.accountid;
SELECT address1,
address2,
city_id,
state_id,
pincode
INTO add1,
add2,
cityid,
stateid,
pincd
FROM wom.tbltaddress ta
WHERE ta.ID IN
(SELECT vbac.billing_address_id
FROM wom.vw_billaddresschange vbac,
wom.tbltaddress ita
WHERE vbac.billing_address_id = ita.ID
AND vbac.lcid = parlcid
);
IF
add1 = :new.address1 AND add2 = :new.address2 AND cityid = :new.cityid AND stateid = :new.stateid
AND pincd = :new.zip THEN
dbms_output.put_line(\'Address Already Exist in tbltaddress table\');
ELSE
UPDATE wom.tbltaddress ta
SET ta.address1 = :new.address1,
ta.address2 = :new.address2,
ta.city_id = :new.cityid,
ta.country_id = \'CTR0001\',
ta.state_id = :new.stateid,
ta.pincode = :new.zip
WHERE ta.ID IN
(SELECT vbac.billing_address_id
FROM wom.vw_billaddresschange vbac,
wom.tbltaddress ita
WHERE vbac.billing_address_id = ita.ID
AND vbac.lcid = parlcid
);
END IF;
END;
没有找到相关结果
已邀请:
2 个回复
枫湃揩乾纲
SELECT address1, address2, city_id, state_id, pincode INTO add1, add2, cityid, stateid, pincd FROM wom.tbltaddress ta WHERE ta.ID IN (SELECT vbac.billing_address_id FROM wom.vw_billaddresschange vbac, wom.tbltaddress ita WHERE vbac.billing_address_id = ita.ID AND vbac.lcid = parlcid );异常的内容。 您应该捕获以下异常：BEGIN SELECT address1, address2, city_id, state_id, pincode INTO add1, add2, cityid, stateid, pincd FROM wom.tbltaddress ta WHERE ta.ID IN ( SELECT vbac.billing_address_id FROM wom.vw_billaddresschange vbac, wom.tbltaddress ita WHERE vbac.billing_address_id = ita.ID AND vbac.lcid = parlcid ); EXCEPTION WHEN no_data_found THEN NULL; -- or do anything useful END;或更高版本，请使用语句而不是在触发器中实现。诉嘎归亮
BEGIN SELECT nvl(COUNT(1),0),nvl(MIN(COST),0),DOCUMENTPARTID INTO l_itemexistCount,l_contractcost,l_docpartid FROM CONTRACTPRICE WHERE RESELLERID= l_companyid AND CONTRACTID=l_contractid AND MANUFACTURERPART = l_manufacturerpart and status = 0 AND DOCUMENTPARTID=l_docpartid AND ROWNUM <=1 GROUP BY DOCUMENTPARTID ORDER BY MIN(COST) DESC; IF nvl(l_itemexistCount,0) > 0 THEN l_staticStr4 := l_staticStr4 || \'\\n(\' || l_count || \') - \' || l_manufacturerpart || \' [$\' || l_contractcost||\']\'; p_paramout:=p_paramout||\',\'||l_docpartid; l_count:= l_count + 1; END IF; EXCEPTION WHEN no_data_found THEN NULL; -- or do anything useful END;