oracle – SQL错误:ORA-01403:未找到任何数据
发布时间:2021-03-18 22:32:26 所属栏目:站长百科 来源:网络整理
导读:当火更新查询时 update JISPBILCORBILLINGPRD501.TBLMACCOUNTADDRESS set Address1='NehateSir',stateid='STT0002' where accountid='ACC000000068' 那个时候发生这个错误 Error starting at line 4 in command:update JISPBILCORBILLINGPRD501.TBLMACCOUNTA
当火更新查询时 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; 解决方法这个查询:SELECT address1,pincode INTO add1,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 ); 不会返回任何导致PL / SQL异常的内容. 你应该抓住这个例外: BEGIN SELECT address1,pincode INTO add1,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; 如果您使用的是Oracle 9i或更高版本,请使用MERGE语句,而不是在触发器中实现UPSERT. (编辑:上饶站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |