Oracle--sql Program Optimization Case One

The following is a part of a stored procedure:

PROCEDURE Sap_man_routing_so (citem_id VARCHAR2,
csite_id VARCHAR2,
CTYPE VARCHAR2)
Is
V_routing VARCHAR2 (40);
BEGIN
IF (citem_id not like '%c% ')
Then
SELECT NVL (TRIM (RR. routing_id), ")
Into v_routing
From (SELECT MAPL. Plnnr | | ' _ ' | | MAPL. Werks routing_id
From Sap_mapl_qmcustomer MAPL
WHERE MAPL. MATNR = citem_id
and MAPL. Werks = csite_id
and EXISTS
(SELECT NULL
From Sap_mapl_qmcustomer
WHERE Sap_mapl_qmcustomer. MATNR =
citem_id
and Sap_mapl_qmcustomer. Werks =
csite_id)) RR;
elsif (citem_id like '%c% ')
Then
SELECT NVL (TRIM (RR. routing_id), ")
Into v_routing
From (SELECT MAX (MAPL. PLNNR) routing_id
From Sap_mapl_qmcustomer MAPL
WHERE MAPL. Matnr = citem_id and MAPL. Werks = csite_id) RR;
END IF;
From the code point of view, the program seems to have no flaws, the implementation is quite smooth, but one day suddenly has been reported

ORA-01403: No data found ORA-06512: in the STG. Sap_so_bom_proc ", line 428
ORA-06512: In the STG. Sap_so_bom_proc ", line 1097

Through the analysis found missing on the result set row number to make a judgment, if the return is 0 rows of data, that is no value, no value will be error,

So here's how to judge the program:

PROCEDURE Sap_man_routing_so (citem_id VARCHAR2,
csite_id VARCHAR2,
CTYPE VARCHAR2)
Is
V_routing VARCHAR2 (40);
qty Number;
BEGIN
Select COUNT (*) into qty
From (SELECT MAPL. Plnnr | | ' _ ' | | MAPL. Werks routing_id
From Sap_mapl_qmcustomer MAPL
WHERE MAPL. Matnr = citem_id
and MAPL. Werks = csite_id
and EXISTS
(SELECT NULL
From Sap_mapl_qmcustomer
WHERE Sap_mapl_qmcustomer. MATNR =
citem_id
and Sap_mapl_qmcustomer. Werks =
csite_id)) RR;
IF qty>0 Then
IF (citem_id not like '%c% ')
Then
SELECT NVL (TRIM (RR. routing_id), ")
Into v_routing
From (SELECT MAPL. Plnnr | | ' _ ' | | MAPL. Werks routing_id
From Sap_mapl_qmcustomer MAPL
WHERE MAPL. MATNR = citem_id
and MAPL. Werks = csite_id
and EXISTS
(SELECT NULL
From Sap_mapl_qmcustomer
WHERE Sap_mapl_qmcustomer. MATNR =
citem_id
and Sap_mapl_qmcustomer. Werks =
csite_id)) RR;
elsif (citem_id like '%c% ')
Then
SELECT NVL (TRIM (RR. routing_id), ")
Into v_routing
From (SELECT MAX (MAPL. PLNNR) routing_id
From Sap_mapl_qmcustomer MAPL
WHERE MAPL. Matnr = citem_id and MAPL. Werks = csite_id) RR;
END IF;
ELSE
--Program vulnerability, does not consider the return value of 0 rows of results, now add the Judgment parameter qty as the total number of returned rows, greater than 1 will execute the original program, otherwise directly assigned null value add by zhangguipeng20170216
v_routing:= ";
END IF;

Oracle--sql Program Optimization Case One