oracle SQL 错误：ORA-01403：未找到数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5716467/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-10 03:13:36 来源:igfitidea点击:
SQL Error: ORA-01403: no data found
提问by user714698
when fire update query
当触发更新查询时
update JISPBILCORBILLINGPRD501.TBLMACCOUNTADDRESS set Address1='NehateSir',stateid='STT0002' where accountid='ACC000000068'
that time this error occurs
那个时候出现这个错误
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:
bellow is trigger code
下面是触发代码
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;
回答by Quassnoi
This query:
这个查询：
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
);
does not return anything which causes an exception in PL/SQL.
不返回任何导致PL/SQL.
You should catch this exception:
您应该捕获此异常：
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;
If you are using Oracle 9ior above, use MERGEstatement instead of implementing UPSERTin a trigger.
如果您正在使用Oracle 9i或以上，请使用MERGE语句而不是UPSERT在触发器中实现。
回答by user3707529
Watch out below example
注意下面的例子
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;
