Oracle下在存储过程中调用另一个带参数的存储过程的错误.

Question

小弟遇到一问题,需要在Oracle中获取数据(当指定的表出现Insert事件时就马上将数据抽取出来).表结构如下:
A表:
ID NOT NULL VARCHAR2(37)
 EVENT_ID NOT NULL NUMBER(20)
B表:
EVENT_ID NOT NULL NUMBER(20)
EVENT_TYPE NUMBER(2)
............C表:
 CORRELATED_EVENT_ID NOT NULL NUMBER(20)
BASE_EVENT_IDS NOT NULL VARCHAR2(4000)
表结构说明:
A,B,C三表中的EVENT_ID字段内容相同,C表中的BASE_EVENT_IDS字段是存储有EVENT_ID字段所组成的字符串,每个EVENT_ID之间用逗号","隔开.
任务说明:
现在我要做的任务是:
①.当A表有数据插入时,要取出B表中对应EVENT_ID的信息,输出到文件,进入②;
②.检查C表是否有对应的EVENT_ID的记录,如果有,取出BASE_EVENT_IDS,并将BASE_EVENT_IDS拆散成一个个EVENT_ID;进入③;
③.迭代由BASE_EVENT_IDS拆散而成EVENT_ID数据,取出一个,从B表中取出对应EVENT_ID的信息,输出到文件,进入④;
④.检查③中所取得的EVENT_TYPE,如果EVENT_TYPE=0,继续③,否则,将③中所EVENT_ID作为参数,执行②;问题:
现在基本上所有逻辑处理均完成,我写了一个触发器和一个存储过程去实现任务的要求.存储过程代码如下:
CREATE PROCEDURE get_event_info(correlate_event_id IN A.event_id%TYPE,case_id IN A.id%TYPE) IS
   CURSOR get_event_info(input_event_id NUMBER) IS 
SELECT * FROM B WHERE event_id =input_event_id;
CURSOR get_correlate_info(input_event_id VARCHAR2) IS
SELECT * FROM C WHERE correlated_event_id =TO_NUMBER(input_event_id);
correlate_file_name VARCHAR2(64);
event_file_name VARCHAR2(64);
correlate_file_handle utl_file.file_type;
event_file_handle utl_file.file_type;
TYPE event_id_table IS TABLE OF NUMBER(20);
event_list event_id_table;
h_event_info B%ROWTYPE;
h_correlate C%ROWTYPE;
temp_event_id VARCHAR2(64);
i NUMBER(20);
next_event_id A.event_id%TYPE;
next_case_id A.id%TYPE;
BEGIN
i :=0;
next_case_id :=case_id;
event_file_name := 'Event_Info_'||case_id||'.csv';
correlate_file_name := 'Correlate_'||case_id||'.csv';
event_file_handle := utl_file.fopen('UTL_TEMP',event_file_name,'W');
correlate_file_handle := utl_file.fopen('UTL_TEMP',correlate_file_name,'W');
FOR h_event_info IN get_event_info(correlate_event_id) LOOP
utl_file.put_line(event_file_handle,h_event_info.EVENT_ID||','||h_event_info.NAME||','||h_event_info.EVENT_TYPE||','||h_event_info.SRC_ADDRESS||','||h_event_info.SRC_HOST_NAME);
END LOOP;
FOR h_correlate IN get_correlate_info(correlate_event_id) LOOP
event_list :=event_id_table(substr(h_correlate.base_event_ids,2,length(h_correlate.base_event_ids)-2));
FOR i in event_list.FIRST .. event_list.LAST LOOP
FOR h_event_info IN get_event_info(event_list(i)) LOOP
IF h_event_info.EVENT_TYPE=0 THEN
utl_file.put_line(event_file_handle,h_event_info.EVENT_ID||','||h_event_info.NAME||','||h_event_info.EVENT_TYPE||','||h_event_info.SRC_ADDRESS||','||h_event_info.SRC_HOST_NAME);
ELSE
next_event_id :=h_event_info.EVENT_ID;
                                              get_event_info(next_event_id,next_case_id);
END IF;
END LOOP;
END LOOP;
END LOOP;
utl_file.fclose(event_file_handle);
utl_file.fclose(correlate_file_handle);
END get_event_info;
我的想法是在存储过程中调用自己,实现递归去处理要做的逻辑.问题出在get_event_info(next_event_id,next_case_id);
这句上,当我想创建这个存储过程时,Oracle报如下错误:
 43  END get_event_info;
 44  /Warning: Procedure created with compilation errors.SQL> SHOW ERR PROCEDURE get_event_info;
Errors for PROCEDURE GET_EVENT_INFO:LINE/COL ERROR
-------- -----------------------------------------------------------------
36/6     PL/SQL: Statement ignored
36/6     PLS-00306: wrong number or types of arguments in call to
         'GET_EVENT_INFO'提示说我的参数错误,我可以百分之一百肯定参数类型没有问题,因为我已经输出过参数查看过,我甚至以以下这句去替代,问题依旧:
get_event_info(55290146209546,'7YYymfSMBABDL0zY6LSWNag==');
当我去掉get_event_info(next_event_id,next_case_id);这句后,存储过程是正常的,以exec get_event_info(55290146209546,'7YYymfSMBABDL0zY6LSWNag==');这句来执行存储过程也是没有任何异常地生成文件的.
我怀疑我是在存储过程中调用带参数的存储过程上所犯的错,因为我试过调用其他存储过程,在调用无参数的存储过程时是能正常编译的,但是调用有参数的存储过程则出现同样问题.请问各位朋友究竟错误是在哪里呢???万分感谢!

Answer 1

CURSOR和get_event_info同名，这样系统无法确定谁是谁我猜问题出在
get_event_info(next_event_id,next_case_id);

Answer 2

换头像啦
you want your life back?why?

Answer 3

传入参数不能为null值，否则会提示无效的参数类型错误。

Answer 4

非常感谢shiyiwan ！唉，你不说我都没发现，游标居然与存储过程同名..不过貌似不是这个原因，我今天下午尝试过用相同代码新建立两个不同名字的存储过程，（例如A和B）,然后在get_event_info(next_event_id,next_case_id);这句位置修改成引用对方（A中修改成 B（next_event_id,next_case_id），B中相应位置改成A（next_event_id,next_case_id）），但是问题依旧.......

Answer 5

谢谢wildwave 的提醒，我确认过绝对没有传入空数值或其他数据类型。我所选的测试数据只足够让各个FOR循环执行一次，所以也不存在在迭代过程中出现空值或其他数据类型等问题。

Answer 6

哎呀，上面引用错了，不好意思。谢谢zhangwonderful 的提醒，我确认过绝对没有传入空数值或其他数据类型。我所选的测试数据只足够让各个FOR循环执行一次，所以也不存在在迭代过程中出现空值或其他数据类型等问题。

Answer 7

莫非那个调用存储过程的那部分，我要用execute immdiate去改写？？明天上班试试。。郁闷撒。

Answer 8

谢谢。今天我也考虑到这一点，所以做了一个尝试，后来发现在存储过程中调用其他存储过程是有两种情况：
1.调用无参数的存储过程：编译成功
2.调用带菜蔬的存储过程：编译失败，报同样的错误。
谢谢shiyiwan 的帮忙，我明天也会继续努力

Answer 9

被某朋友教训：你写那堆烂东西，又没注释，你叫别人怎么看？？然后深深感受到提问工作做的不够，所以补上注释（尽管看起来也好不了多少，不过应该不用被人教训。 - -！）。另外，该存储仍未有问题，希望各位朋友不吝赐教。感谢！CREATE PROCEDURE get_event_info(correlate_event_id IN A.event_id%TYPE,case_id IN A.id%TYPE) IS
--定义游标
    CURSOR get_event_info(input_event_id NUMBER) IS 
        SELECT * FROM B WHERE event_id =input_event_id;
    CURSOR get_correlate_info(input_event_id VARCHAR2) IS
        SELECT * FROM C WHERE correlated_event_id =TO_NUMBER(input_event_id);
--声明一个Table类型
    TYPE event_id_table IS TABLE OF NUMBER(20);
--声明变量
    correlate_file_name VARCHAR2(64);
    event_file_name VARCHAR2(64);
    correlate_file_handle utl_file.file_type;
    event_file_handle utl_file.file_type;
    event_list event_id_table;
    h_event_info B%ROWTYPE;
    h_correlate C%ROWTYPE;
    temp_event_id VARCHAR2(64);
    i NUMBER(20);
--这两个变量打算用来作为递归时的输入参数
    next_event_id A.event_id%TYPE;
    next_case_id A.id%TYPE;
BEGIN
--给变量赋予初始值
    i :=0;
--定义输出文件的名字（简称文件A和文件B）
    next_case_id :=case_id;
    event_file_name := 'Event_Info_'||case_id||'.csv';
    correlate_file_name := 'Correlate_'||case_id||'.csv';
--打开文件
    event_file_handle := utl_file.fopen('UTL_TEMP',event_file_name,'W');
    correlate_file_handle := utl_file.fopen('UTL_TEMP',correlate_file_name,'W');    
--打开游标1，将在B表中获取与参数Event_id所对应的数据，导出到文件A中
    FOR h_event_info IN get_event_info(correlate_event_id) LOOP    
        utl_file.put_line(event_file_handle,h_event_info.EVENT_ID||','||h_event_info.NAME||','||h_event_info.EVENT_TYPE||','||h_event_info.SRC_ADDRESS||','||h_event_info.SRC_HOST_NAME);
    END LOOP;
--打开游标2
    FOR h_correlate IN get_correlate_info(correlate_event_id) LOOP
--将对应关系，输出到文件中
utl_file.put_line(correlate_file_handle,h_correlate.event_id||','||h_correlate.base_event_ids);
--获取C表中与参数Event_id所对应base_event_ids字段，并将经过处理后的字符串仍进上面所定义的Table类型的变量中（表述有点烂，反正将一串东东扔进数组中）
        event_list :=event_id_table(substr(h_correlate.base_event_ids,2,length(h_correlate.base_event_ids)-2));
--迭代数组
        FOR i in event_list.FIRST .. event_list.LAST LOOP
--从数组中获取一个元素，作为游标1的参数，打开游标
            FOR h_event_info IN get_event_info(event_list(i)) LOOP
--检查对应的数据的Event_type字段，如等于0，输出到文件中
                IF h_event_info.EVENT_TYPE=0 THEN
                    utl_file.put_line(event_file_handle,h_event_info.EVENT_ID||','||h_event_info.NAME||','||h_event_info.EVENT_TYPE||','||h_event_info.SRC_ADDRESS||','||h_event_info.SRC_HOST_NAME);
--如不等于0，递归
                ELSE
                    next_event_id :=h_event_info.EVENT_ID;
                    get_event_info(next_event_id,next_case_id);
                END IF;                
            END LOOP;
        END LOOP;
    END LOOP;
--关闭打开的文件
    utl_file.fclose(event_file_handle);    
    utl_file.fclose(correlate_file_handle);
--结束
END get_event_info;

Answer 10

问题解决,原来真如shiyiwan所说的那样,存储过程与游标同名所导致的问题.很奇怪.我测试中明明调用其他带参数的存储过程(代码一样,名字不同),但也发生相同错误.以至搞错了方向.(太粗心了 -_-! )
再次感谢shiyiwan的帮助.
结帖给分.