ora-01403: No data found

Demand:

Plan to take out a fund for all valuation dates from the Fa_product_net_value table, and then apply this date to calculate the corresponding indicator.

Declare
V_date_1 date;
V_product Nvarchar2 (20): = ' 00010000388015 ';
V_date Date: =date ' 2015-06-19 ';
Begin
Dbms_output. ENABLE (Buffer_size=>null);

While V_date<=date ' 2015-12-31 ' Loop

Select T.d_esti_date into v_date_1 from Dc_edw.fa_product_net_value t
where t.sk_product = V_product
and t.d_esti_date = V_date;

Dbms_output.put_line (v_date_1);
V_date: = v_date+1;
End Loop;

End

Run times wrong

Online Find solution: The hint is because the Fa_product_net_value table does not detect the data, (when there are records in the table but the field value is null, and the table does not exist in the record is two different concepts), lookup table found

July 25, 2015 No data

SELECT * FROM Dc_edw.fa_product_net_value t
where t.sk_product = ' 00010000388015 ';

Solution:

Catch exception, use exception when no_data_found then processing

Declare
V_date_1 date;
V_product Nvarchar2 (20): = ' 00010000388015 ';
V_date Date: =date ' 2015-06-19 ';
Begin
Dbms_output. ENABLE (Buffer_size=>null);

While V_date<=date ' 2015-12-31 ' Loop

Select T.d_esti_date into v_date_1 from Dc_edw.fa_product_net_value t
where t.sk_product = V_product
and t.d_esti_date = V_date;

Dbms_output.put_line (v_date_1);
V_date: = v_date+1;
End Loop;
Exception when No_data_found then
V_date_1: = Date ' 9999-01-01 ';
End

can run correctly, but found that after the exception is thrown, you cannot continue running the while program.

The results are displayed only until the 2015-07-24 day.

Analysis Reason: When the program runs to 2015-07-25, there is no data, the exception throws the given default value ' 9999-01-01 ', the program ends.

Try to use an aggregate function.

Declare
V_date_1 date;
V_product Nvarchar2 (20): = ' 00010000388015 ';
V_date Date: =date ' 2015-06-19 ';
Begin
--dbms_output. ENABLE (Buffer_size=>null);
While V_date<=date ' 2015-12-31 ' Loop
Select Max (t.d_esti_date) into v_date_1 from Dc_edw.fa_product_net_value t
where t.sk_product = V_product
and t.d_esti_date = V_date;
Dbms_output.put_line (v_date_1);
V_date: = v_date+1;
End Loop;
End

Perfect solution to the problem.

Analysis reason: No data found exception is reported when a stored procedure with a function is executed.