【讨论中】游标中如何使用in条件变量

luiszhu

回复 8# cenalulu
我现在只能在游标中定义子查询，能正常运行，但是速度太慢了，测试下来是 MODEL_NUM in那一部分导致速度突降。PRODUCT_CFG 表有2000多行，MODEL_NUM是6位字符。请问有什么办法提高速度？

DECLARE v_Site VARCHAR( DEFAULT Location;
DECLARE v_PRODUCT_ID INT(11) DEFAULT PID;
DECLARE v_PRODUCT_NAME VARCHAR(32) DEFAULT PNAME;
DECLARE v_CAPACITY VARCHAR(32) DEFAULT PCAPACITY;
DECLARE no_more_data INT DEFAULT 0;

DECLARE cur_fm CURSOR FOR Select FISCAL_WEEK,OPERATION,ROUND(SUM(PRIME_PASS)/SUM(PRIME_TESTED),3),ROUND(SUM(TOTAL_PASS)/SUM(TOTAL_TESTED),3) from WEEKLY where BUILD_GROUP in (Select distinct b.BUILD_GROUP from PRODUCT a,BUILD_GROUP_CFG b where b.SITE = v_Site and a.PRODUCT_ID = b.PRODUCT_ID and a.product_Name = v_PRODUCT_NAME and a.CAPACITY = v_CAPACITY union Select b.BUILD_GROUP from BUILD_GROUP_CFG b where b.PRODUCT_ID = 9999) and MODEL_NUM in (Select c.MODEL_NUM from PRODUCT_CFG c where c.PRODUCT_INTERNAL_Name = v_PRODUCT_NAME and c.CAPACITY = v_CAPACITY) group by FISCAL_WEEK,OPERATION;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET no_more_data=1;