一个ORACLE优化的PL/SQL语句的前后对比

DECLARE
TYPE Rc IS REF CURSOR;
l_Rc    Rc;
l_Dummy All_Objects.Object_Name%TYPE;
l_Start NUMBER DEFAULT Dbms_Utility.Get_Time;
BEGIN
FOR i IN 1 .. 1000 LOOP
    OPEN l_Rc FOR 'select object_name from all_objects where object_id = :x' USING i;
    FETCH l_Rc INTO l_Dummy;
    CLOSE l_Rc;
END LOOP;
Dbms_Output.Put_Line(Round((Dbms_Utility.Get_Time - l_Start) / 100, 2) || 'second...');
END;

执行时间：约1秒

DECLARE
TYPE Rc IS REF CURSOR;
l_Rc    Rc;
l_Dummy All_Objects.Object_Name%TYPE;
l_Start NUMBER DEFAULT Dbms_Utility.Get_Time;
BEGIN
FOR i IN 1 .. 1000 LOOP
    OPEN l_Rc FOR 'select object_name from all_objects where object_id = ' || i;
    FETCH l_Rc INTO l_Dummy;
    CLOSE l_Rc;
END LOOP;
Dbms_Output.Put_Line(Round((Dbms_Utility.Get_Time - l_Start) / 100, 2) || 'second...');
END;

执行时间：约14秒！