SQL Developer如何执行存储过程

[孙大诚_SunRobin]

create or replace procedure USP_CA_GetAOrHCampaigns
(pi_serialNo in VarChar2,
my_cur out sys_refcursor) is
begin
open my_cur for
select xxx from ...;
end USP_CA_GetAOrHCampaigns;

这是这个存储过程，请问这个存储过程怎么测试一下，现在用的是SQL Developer.

[tomshenhao]

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/devdays2012/mod2_sqldev/mod2_sqldev.html?cid=6267&ssid=108791759479565

[孙大诚_SunRobin]

引用 1 楼 tomshenhao 的回复:

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/devdays2012/mod2_sqldev/mod2_sqldev.html?cid=6267&ssid=108791759479565

DECLARE
  PI_SERIALNO VARCHAR2(200);
  MY_CUR sys_refcursor;
BEGIN
  PI_SERIALNO := '2003004495';

  USP_CA_GETAORHCAMPAIGNS(
    PI_SERIALNO => PI_SERIALNO,
    MY_CUR => MY_CUR
  );
  /* Legacy output: 
DBMS_OUTPUT.PUT_LINE('MY_CUR = ' || MY_CUR);
*/ 
  :MY_CUR := MY_CUR; --<-- Cursor
--rollback; 
END;

但是执行结果是 Bind Variable "MY_CUR" is NOT DECLARED anonymous block completed 请问问题出在哪里？

[华而不实]

DECLARE
  PI_SERIALNO VARCHAR2(200);
  MY_CUR sys_refcursor;
BEGIN
  PI_SERIALNO := '2003004495';

  USP_CA_GETAORHCAMPAIGNS(
    PI_SERIALNO => PI_SERIALNO,
    MY_CUR => MY_CUR
  );
  /* Legacy output: 
DBMS_OUTPUT.PUT_LINE('MY_CUR = ' || MY_CUR);
*/ 
  --:MY_CUR := MY_CUR; --<-- Cursor
/**do something using MY_CUR*/
--rollback; 
END;

[善若止水]

my_cur 是作为输出的，并不是输入。在没有定义的情况下用，肯定是报错了。必须先声明，再打开才行。

[孙大诚_SunRobin]

引用 7 楼 yangxuan992 的回复:

DECLARE PI_SERIALNO VARCHAR2(200); MY_CUR sys_refcursor; temp vp_campaign%rowtype; BEGIN PI_SERIALNO := '2003004495'; USP_CA_GETAORHCAMPAIGNS( PI_SERIALNO => PI_SERIALNO, MY_CUR => MY_CUR ); --OPEN MY_CUR;已经打开了。。。 LOOP fetch MY_CUR into temp; exit when MY_CUR%notfound; DBMS_OUTPUT.PUT_LINE('MY_CUR = ' || temp.analystno); END LOOP; END;

Error report - ORA-06504: PL/SQL: Return types of Result Set variables or query do not match ORA-06512: at line 14 06504. 00000 - "PL/SQL: Return types of Result Set variables or query do not match" *Cause: Number and/or types of columns in a query does not match declared return type of a result set variable, or declared types of two Result Set variables do not match. *Action: Change the program statement or declaration. Verify what query the variable actually refers to during execution. 请问，这个是什么原因造成的？？？

[华而不实]

引用 8 楼 sundacheng1989 的回复:

[quote=引用 7 楼 yangxuan992 的回复:] DECLARE PI_SERIALNO VARCHAR2(200); MY_CUR sys_refcursor; temp vp_campaign%rowtype; BEGIN PI_SERIALNO := '2003004495'; USP_CA_GETAORHCAMPAIGNS( PI_SERIALNO => PI_SERIALNO, MY_CUR => MY_CUR ); --OPEN MY_CUR;已经打开了。。。 LOOP fetch MY_CUR into temp; exit when MY_CUR%notfound; DBMS_OUTPUT.PUT_LINE('MY_CUR = ' || temp.analystno); END LOOP; END;

Error report - ORA-06504: PL/SQL: Return types of Result Set variables or query do not match ORA-06512: at line 14 06504. 00000 - "PL/SQL: Return types of Result Set variables or query do not match" *Cause: Number and/or types of columns in a query does not match declared return type of a result set variable, or declared types of two Result Set variables do not match. *Action: Change the program statement or declaration. Verify what query the variable actually refers to during execution. 请问，这个是什么原因造成的？？？[/quote] 能不能把你的存储过程贴出来？

[孙大诚_SunRobin]

引用 3 楼 yangxuan992 的回复:

DECLARE
  PI_SERIALNO VARCHAR2(200);
  MY_CUR sys_refcursor;
BEGIN
  PI_SERIALNO := '2003004495';

  USP_CA_GETAORHCAMPAIGNS(
    PI_SERIALNO => PI_SERIALNO,
    MY_CUR => MY_CUR
  );
  /* Legacy output: 
DBMS_OUTPUT.PUT_LINE('MY_CUR = ' || MY_CUR);
*/ 
  --:MY_CUR := MY_CUR; --<-- Cursor
/**do something using MY_CUR*/
--rollback; 
END;

刚刚接触PLSQL,不怎么熟悉。USP_CA_GETAORHCAMPAIGNS这个存储过程的作用是，向CAMPAIGN表按一定条件取出数据。我怎么循环呢？下边的代码总是报错。

DECLARE
  PI_SERIALNO VARCHAR2(200);
  MY_CUR sys_refcursor;
  temp vp_campaign%rowtype;
BEGIN
  PI_SERIALNO := '2003004495';

  USP_CA_GETAORHCAMPAIGNS(
    PI_SERIALNO => PI_SERIALNO,
    MY_CUR => MY_CUR
  );
  OPEN MY_CUR;
  LOOP
    fetch MY_CUR into temp;  
    exit when MY_CUR%notfound;  
    DBMS_OUTPUT.PUT_LINE('MY_CUR = ' || temp.analystno);
  END LOOP;
END;

[孙大诚_SunRobin]

引用 4 楼 zhaoxiangchong 的回复:

my_cur 是作为输出的，并不是输入。在没有定义的情况下用，肯定是报错了。必须先声明，再打开才行。

那个在开头不是declare了吗，我又忘了什么吗？

[华而不实]

DECLARE PI_SERIALNO VARCHAR2(200); MY_CUR sys_refcursor; temp vp_campaign%rowtype; BEGIN PI_SERIALNO := '2003004495'; USP_CA_GETAORHCAMPAIGNS( PI_SERIALNO => PI_SERIALNO, MY_CUR => MY_CUR ); --OPEN MY_CUR;已经打开了。。。 LOOP fetch MY_CUR into temp; exit when MY_CUR%notfound; DBMS_OUTPUT.PUT_LINE('MY_CUR = ' || temp.analystno); END LOOP; END;