代码之家 › 专栏 › 技术社区 › Esteban Rincon

如何在PL/SQL块中打印游标?

ref-cursor oracle-sqldeveloper plsql oracle

Esteban Rincon · 技术社区 · 6 年前

我好像无法打印光标,我做错什么了?

DECLARE
  LEADEMAIL VARCHAR2(200);
  CLIENTID NUMBER;
  v_Return ON24MASTER.WEBCAST_REPORTS.ResultSetCursor;
BEGIN
  LEADEMAIL := 'nunyo@business.com';
  CLIENTID := 22921;

  v_Return := WEBCAST_REPORTS.LEAD_BASIC_INFO(
    LEADEMAIL => LEADEMAIL,
    CLIENTID => CLIENTID
  );

    DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
--    :v_Return := v_Return;

END;

Error report -
ORA-06550: line 14, column 26:
PLS-00306: wrong number or types of arguments in call to '||'
ORA-06550: line 14, column 5:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

大部分代码都是直接从SQL developer运行函数获得的。

这是包函数:

  FUNCTION LEAD_BASIC_INFO(
    leadEmail VARCHAR2,
    clientId NUMBER
  ) RETURN ResultSetCursor IS
    resultSet ResultSetCursor;
    email VARCHAR2(1000);
    webcastEngagement NUMBER(10,1);
    videoEngagement NUMBER(10,1);
    documentEngagement NUMBER(10,1);
    totalEngagement NUMBER(10,1);
    --averageEngagement NUMBER(4,1);
    totalWebcastSeconds NUMBER(10);
    engagementMinutes NUMBER(10, 1);
    last30DaysEM NUMBER(10, 1);
    last60DaysEM NUMBER(10, 1);
    fromDate DATE;
    engagementPrediction NUMBER(10);
  BEGIN...

select 语句,因为函数也有DML。

2 回复 | 直到 6 年前

Gary_W 6 年前

您不能打印这样的游标;它必须隐式地将行和列转换为字符串,这太出乎意料了。这个 dbms_output.put_line() 过程只接受字符串参数或任何可以隐式转换为字符串的参数。游标不能。

您必须在游标结果集上循环,获取适当的记录类型;然后 dbms_output 在该循环中调用,该循环将结果集中的所有列值(如果尝试模拟select,则格式化并可能填充)连接到单个字符串中。

不知道具体怎么做 ON24MASTER.WEBCAST_REPORTS.ResultSetCursor 定义(大概 TYPE ResultSetCursor IS REF CURSOR ),或者在过程中填充它的查询返回的内容-哪些列名-很难更具体。

variable rc refcursor;

DECLARE
  LEADEMAIL VARCHAR2(200);
  CLIENTID NUMBER;
BEGIN
  LEADEMAIL := 'nunyo@business.com';
  CLIENTID := 22921;

  :rc := WEBCAST_REPORTS.LEAD_BASIC_INFO(
    LEADEMAIL => LEADEMAIL,
    CLIENTID => CLIENTID
  );

END;
/

print rc

在块a绑定变量之前 rc the variable command . 在块内部使用,而不是在本地 v_Return ,所以这甚至不需要在本地声明。(注意前面的冒号 :rc print 参考光标。(这些doc链接是针对SQL*Plus的,但是它们是 the many commands SQL Developer supports

使用虚拟包:

create or replace package WEBCAST_REPORTS AS
  TYPE ResultSetCursor IS ref cursor;
  FUNCTION LEAD_BASIC_INFO(
    leadEmail VARCHAR2,
    clientId NUMBER
  ) RETURN ResultSetCursor;
end WEBCAST_REPORTS;
/

create or replace package body WEBCAST_REPORTS AS
  FUNCTION LEAD_BASIC_INFO(
    leadEmail VARCHAR2,
    clientId NUMBER
  ) RETURN ResultSetCursor IS
    resultSet ResultSetCursor;
  BEGIN
    OPEN resultSet FOR select * from dual;
    RETURN resultSet;
  END LEAD_BASIC_INFO;
end WEBCAST_REPORTS;
/

然后,上面显示的代码作为脚本运行,在“脚本输出”窗口中显示:

PL/SQL procedure successfully completed.


D
-
X

Matthew McPeak 6 年前

在Oracle 12c中,您可以使用 DBMS_SQL.RETURN_RESULT . 即。,

DECLARE
  LEADEMAIL VARCHAR2(200);
  CLIENTID NUMBER;
  v_Return ON24MASTER.WEBCAST_REPORTS.ResultSetCursor;
BEGIN
  LEADEMAIL := 'nunyo@business.com';
  CLIENTID := 22921;

  v_Return := WEBCAST_REPORTS.LEAD_BASIC_INFO(
    LEADEMAIL => LEADEMAIL,
    CLIENTID => CLIENTID
  );

    DBMS_SQL.RETURN_RESULT(v_Return); 

END;