通过使用字符串变量作为索引, 能够比较快速的判断数据是否存在于table变量中。性能比直接通过FOR循环判断的方式提高数倍。使用FOR循环是1.5秒左右,使用此代码大约0.25秒。
例子中的表是自己定义的,可以根据自己的需要进行修改。
declare
CURSOR dds_exl IS
SELECT EXL_NAME
FROM parallel_run_nodes_2 WHERE secondary_headend = 'NASDQ'
union all
SELECT EXL_NAME
FROM parallel_run_nodes_2 WHERE secondary_headend = 'NASDQ';
TYPE T_EXL IS RECORD
(EXL_name exchange_list.exl_name%type
);
TYPE T_EXL_SET IS TABLE OF T_EXL INDEX BY VARCHAR2(50);
v_EXL_array T_EXL_SET;
v_exl VARCHAR2(50);
v_found BOOLEAN;
BEGIN
OPEN dds_exl;
LOOP
FETCH dds_exl INTO v_exl;
EXIT WHEN dds_exl%NOTFOUND;
IF not v_EXL_array.EXISTS(v_exl) THEN
v_EXL_array(v_exl).EXL_name := v_exl;
END IF;
END LOOP;
v_exl := v_EXL_array.FIRST;
WHILE v_exl IS NOT NULL LOOP
v_exl := v_EXL_array.NEXT(v_exl);
END LOOP;
dbms_output.put_line('row count is ' || v_EXL_array.COUNT);
END;
附:使用FOR循环的例子
declare
CURSOR dds_exl IS
SELECT EXL_NAME
FROM parallel_run_nodes_2 WHERE secondary_headend = 'NASDQ'
union all
SELECT EXL_NAME
FROM parallel_run_nodes_2 WHERE secondary_headend = 'NASDQ';
TYPE T_EXL IS RECORD
(EXL_name exchange_list.exl_name%type
);
TYPE T_EXL_SET IS TABLE OF T_EXL INDEX BY PLS_INTEGER;
v_EXL_array T_EXL_SET;
v_exl VARCHAR2(50);
v_found BOOLEAN;
BEGIN
OPEN dds_exl;
LOOP
FETCH dds_exl INTO v_exl;
EXIT WHEN dds_exl%NOTFOUND;
v_found := FALSE;
FOR i IN 1..v_EXL_array.COUNT LOOP
IF v_EXL_array(i).EXL_name = v_exl THEN
v_found := TRUE;
EXIT;
END IF;
END LOOP;
IF v_found = FALSE THEN
v_EXL_array(v_EXL_array.COUNT + 1).EXL_name := v_exl;
END IF;
END LOOP;
dbms_output.put_line('row count is ' || v_EXL_array.COUNT);
END;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/51146/viewspace-683208/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/51146/viewspace-683208/