哪位高手帮忙优化一下存储过程

Question

下面是我写的一个存储过程，第一次写存储过程，运行速度很慢，哪位高手帮忙给提点意见，这个过程该怎么优化，或者哪里需要优化.谢谢各位了！
CREATE OR REPLACE PROCEDURE test_sjjc(
    lctable     IN VARCHAR,
    lctabzj     IN NUMBER ,
    lczj        IN YWSLLCSLB.ZJ%TYPE
)
IS
    str_zj        VARCHAR2 (200);                          --sql语句的主键值变量
    str_up        VARCHAR2 (2000);                         --sql语句的临时变量
    str_p         VARCHAR2 (2000);                         --sql语句的临时变量
    wj            VARCHAR2 (200);                          --sql语句中外键的临时变量
    zjm           VARCHAR2 (200);                          --sql语句中的主键名
    checkid       NUMBER := 0;                             --判断第一个循环是否执行    TYPE sel IS REF CURSOR;                                --声明一个游标
    sel_zzj sel;                                           --定义一个游标
BEGIN
     FOR i_table IN ((SELECT t1.table_name tname
                    FROM user_constraints t1,user_cons_columns t2 
                    WHERE t1.r_constraint_name = t2.constraint_name 
                        AND t2.table_name = lctable
                    INTERSECT
                    SELECT DISTINCT(BYWM) tablename
                    FROM SJJC_ZDJCTABLE,
                         (SELECT ywlcdm
                            FROM YWSSLB
                            WHERE ZJ = lczj) ywlcdm_t
                    WHERE LCDM = ywlcdm_t.ywlcdm)
                    MINUS
                    SELECT DISTINCT bywm 
                    FROM sjjc_output s 
                    WHERE s.lcslzj = lczj)
    LOOP
        checkid := checkid + 1;        --检索出表的主键
        SELECT col.column_name cname
        INTO zjm
        FROM user_constraints con,
             user_cons_columns col
        WHERE   con.constraint_name = col.constraint_name
            AND con.constraint_type = 'P'
            AND con.table_name = i_table.tname;
            
        --检索出表的外键名
        SELECT r.column_name
        INTO wj
        FROM user_cons_columns col,
             (SELECT coln.table_name, coln.column_name, conn.r_constraint_name
              FROM  user_constraints conn, user_cons_columns coln
              WHERE conn.constraint_name = coln.constraint_name
                  AND coln.table_name = i_table.tname) r
        WHERE  r.r_constraint_name = col.constraint_name
            AND col.table_name = lctable;        str_p :=
             ' SELECT ' || zjm ||
             ' FROM ' || i_table.tname ||
             ' WHERE ' || wj || ' = '|| lctabzj;
        str_up :=
             str_p ||
             ' UNION ALL ' ||
             ' SELECT DISTINCT(0) ' || zjm ||
             ' FROM dual ' ||
             ' WHERE NOT EXISTS(' || str_p || ')';
        OPEN sel_zzj FOR str_up;
        LOOP
            --得到和流程相关的表的主键值
            FETCH sel_zzj INTO str_zj;
            EXIT WHEN sel_zzj%NOTFOUND;
            --执行检查模块
            TEST_SJJC_GYTDSYQDJ_CRDJ(i_table.tname, str_zj, lczj);
            IF str_zj != 0 AND str_zj IS NOT NULL
            THEN
                test_sjjc(i_table.tname, str_zj,lczj);
            END IF;
        END LOOP;
        CLOSE sel_zzj;
    END LOOP;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
END test_sjjc

Answer 1

intersect，对两个结果集进行交集操作，已经不包括重复行，所以不需要加distinct
Minus，对两个结果集进行差操作，已经不包括重复行，所以不需要加distinct
建议先将数据放到临时表中，然后从临时表中提取进行循环处理，性能会高很多的。

Answer 2

LOOP 里面再嵌套LOOP , 性能肯定差了多少啊！
   可以采用分析函数来解决内层循环的问题，那样性能会高很多！

Answer 3

感觉循环内部搜索主键和外键的两条查询是否可以合并成一条查询。
还有str_up这个sql，似乎也可以优化，因为后面又是从dual去查not exists的。
还有TEST_SJJC_GYTDSYQDJ_CRDJ(i_table.tname, str_zj, lczj);
            IF str_zj != 0 AND str_zj IS NOT NULL
            THEN
                test_sjjc(i_table.tname, str_zj,lczj);
            END IF; 
递归调用可能会有问题，比如递归的某一个commit了，外层rollback不回去吧？

Answer 4

主外键的查询确实可以放在一块，但是效率和分开差不多，写一块后要嵌套子查询，比现在的长，可读性就差了；
NOT EXISTS的我再想想，看看如何再优化。
关于递归commit的确实是这样，应该再完善一下。

Answer 5

服务器性能这个我也是这么想的，服务器业务肯定比本地多，只是没想到差这么多。我要完成的功能是给出一个流程表名及主键，在数据库中通过这个表获得和这个流程表相关的所有业务表，并通过TEST_SJJC_GYTDSYQDJ_CRDJ（）过程进行处理，这个TEST_SJJC_GYTDSYQDJ_CRDJ 过程效率还行，我现在列出的只是查找从从表关联主表的内容，还有一个和从主表关联从表的过程，很类似，没有列出来。
user_cons_column和 user_constraints 是系统视图，通过这两个视图获得相关表的关系。