As title, direct the query took less than 3 seconds, but after get into a stored procedure, the results can not come, as if stuck, could you tell me what's the problem?

CodePudding user response:

What you posted and see,

CodePudding user response:

 create or replace PROCEDURE headquarters in stores not available varieties detail 
(
V_SUBBH NVARCHAR2 IN 
- v_lsdl NVARCHAR2 IN 
) AS 
V_code VARCHAR2 (20); 
V_name VARCHAR2 (20); 
The begin 
EXECUTE IMMEDIATE 'TRUNCATE TABLE lyl_ stores not available varieties of subsidiary' headquarters is available; 
DECLARE CURSOR SUBPHK_CURSOR IS SELECT code, name the from organization where NVL (ISFORBIDDEN, 0)=0 and code<> '03010757' and code like '% V_SUBBH %' order by code; 
The BEGIN 
The OPEN SUBPHK_CURSOR; 
LOOP 
The FETCH SUBPHK_CURSOR INTO v_code, v_name; 
Store information not available varieties - headquarters becomes available 
Insert into stores not available varieties lyl_ headquarters becomes available details (number, the name and store the article number, name of commodity) 
The Select v_code, v_name, a.h h article number, commodity name Amy polumbo m 
The From yw A 
Left the join KCLB lb on lb. Id=a. b 
Left the join (SELECT HH, subbh FROM SUBPHK WHERE SL<> 0 AND SUBBH=v_code group by HH, SUBBH having SUM (sl) & gt; 0) sub on sub. Hh=a.h h 
Where NVL (a. KSL, 0) & gt; 0 and sub. Subbh is null 
The order by a. b, a.h h; 
END LOOP; 
The CLOSE SUBPHK_CURSOR; 
END; 
COMMIT; 
RETURN; 
END; 

CodePudding user response:

The building Lord, you can monitor the stored procedure under waiting for what wait for events, monitoring methods refer to my blog,

CodePudding user response:

reference liuzhijian2008x reply: 3/f

the building Lord, you can monitor the stored procedure under waiting for what wait for events, monitoring methods refer to my blog,

or use vernier fetch bulk collect sectional insert data into

CodePudding user response:

In PLSQL the test window tracking and see where the card,

CodePudding user response:

Don't know how the cursor in the PL/SQL code you are simulated outside?

CodePudding user response:

Don't feel need a cursor

CodePudding user response:

How many times your cursor in the loop