Oracle 存储过程速度慢

[开发之路]

有个游标

    cursor c1(v_mcode varchar2, v_ifchk varchar2) is
      select *
        from a
        where (a.mrlmcode = v_mcode or v_mcode is null) and
               a.mrlrdate>=to_date(P_SDATE, 'yyyymmdd')
               and a.mrlifchk = v_ifchk
               order by a.mrlrdate;

     view_mrl a%rowtype;

     open c1(P_MICODE, P_ISCHK);
      loop
        fetch c1 into view_mrl;

执行c1用时0.047秒
测试到 fetch c1 into view_mrl; 停了几秒
我觉得是这影响了速度，不知道是什么原因
最后也能执行成功，就是速度慢

[gguxxing008]

你是怎么测试这里面每条语句的执行时间的？？总不会是看timing吧

[开发之路]

我用plsql啊

[minitoy]

fetch慢？没遇到过。按理说有可能慢的应该是open吧。等高手。

[ngx20080110]

試一下
FETCH ... BULK COLLECT INTO ...

[开发之路]

fetch c1 BULK COLLECT into view_mrl;
报这个错
编译错误

错误：PLS-00497: 无法混合 INTO 列表中的单行和多行 (BULK)
行：40
文本：fetch c1 BULK COLLECT into view_mrl;

[minitoy]

view_mrl这个得改成table变量。
DECLARE
TYPE NameList IS TABLE OF employees.last_name%TYPE;
TYPE SalList IS TABLE OF employees.salary%TYPE;
CURSOR c1 IS SELECT last_name, salary FROM employees WHERE salary > 10000;
names NameList;
sals SalList;
TYPE RecList IS TABLE OF c1%ROWTYPE;
recs RecList;
v_limit PLS_INTEGER := 10;
PROCEDURE print_results IS
BEGIN
IF names IS NULL OR names.COUNT = 0 THEN -- check if collections are empty
DBMS_OUTPUT.PUT_LINE('No results!');
ELSE
DBMS_OUTPUT.PUT_LINE('Results: ');
FOR i IN names.FIRST .. names.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(' Employee ' || names(i) || ': $' || sals(i));
END LOOP;
END IF;
END;
BEGIN
DBMS_OUTPUT.PUT_LINE('--- Processing all results at once ---');
OPEN c1;
FETCH c1 BULK COLLECT INTO names, sals;
CLOSE c1;
print_results();
DBMS_OUTPUT.PUT_LINE('--- Processing ' || v_limit || ' rows at a time ---');
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO names, sals LIMIT v_limit;
EXIT WHEN names.COUNT = 0;
print_results();
END LOOP;
CLOSE c1;
DBMS_OUTPUT.PUT_LINE('--- Fetching records rather than columns ---');
OPEN c1;
FETCH c1 BULK COLLECT INTO recs;
FOR i IN recs.FIRST .. recs.LAST
LOOP
-- Now all the columns from the result set come from a single record
DBMS_OUTPUT.PUT_LINE(' Employee ' || recs(i).last_name || ': $'
|| recs(i).salary);
END LOOP;
END;
/

[开发之路]

是要把用的字段全用变量取出来吧

[开发之路]

编译报错

TYPE t_mrlrdate IS TABLE OF a.mrlrdate%TYPE;
v_mrlrdate  t_mrlrdate;

在loop里
if v_mrlrdate>=to_date(p_edate,'yyyymmdd')
编译错误

错误：PLS-00306: 调用 '>=' 时参数个数或类型错误
行：55
文本：if v_mrlrdate>=to_date(p_edate,'yyyymmdd') and c1%rowcount=1 then

[minitoy]

v_mrlrdate相当于一个数组，你需要用first和last来for循环取

FOR i IN names.FIRST .. names.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(' Employee ' || names(i) || ': $' || sals(i));
END LOOP;

[开发之路]

我改了调试

fetch c1 BULK COLLECT  into v_mrlmcode,v_mrlsl,v_mrlrdate,v_mrlprdate,v_mrlifchk

还是慢

[开发之路]

我们解决了，有高人