PL/SQL 如何执行带返回游标的存储过程 ！（SQL 窗口中如何调用）

[skyakon]

CREATE OR REPLACE PROCEDURE POOLING_GETSTATISTICSLIST (
idstations in varchar2,
idresults in INT,
hotNames in VARCHAR,
startTime in DATE,
endtime in DATE,
PID in varchar2,
p_curosr out p_myrefcursor.my_cursor
) AS
--
-- NAME :
-- DESCRIPTION :
-- AUTHOR :

contions VARCHAR2(4000);
tableId INt;
tableName VARCHAR2(20);
sqlStr VARCHAR(4000);
RunitID int;

BEGIN
SELECT ID into RunitID FROM RUnit WHERE ID_Station=idstations;
tableId:= mod(RunitID,10);
tableName := 'Re_Polling_' || to_char(tableId);
contions := ' where 1=1 ';
if idstations is not null then
contions := contions ||' and '||GetPerfectCond('ID_Station',idstations);
end if;
IF idresults != -1 then
contions := contions||' and Re_Result= ' || to_char(idresults);
end if;
IF hotNames is not null then
contions := contions||' and '||GetPerfectCond('HOT_Name',hotNames);
end if;
IF startTime is not null then
contions := contions||' and InsertTime>= ' || GetTimeQuoted(startTime) ;end if;
IF endtime is not null then
contions := contions||' and InsertTime<= ' || GetTimeQuoted(endtime);
end if;
IF PID is not null then
contions:=contions||' and '||GetPerfectCond('PID',PID);
end if;

sqlStr := '';
sqlStr := sqlStr || ' SELECT ID_Station ';
sqlStr := sqlStr || ' ,PID ';
sqlStr := sqlStr || ' ,AVG(to_number (PValue)) as PValue ';
sqlStr := sqlStr || ' ,Name_Station ';
sqlStr := sqlStr || ' ,Location ';
sqlStr := sqlStr || ' ,HOT_Name , Re_Result';
sqlStr := sqlStr ||','||GetTimeQuoted(StartTime)||' StartTime,'||GetTimeQuoted(EndTime)||' EndTime';
sqlStr := sqlStr || ' FROM ' || tableName ;sqlStr := sqlStr || contions;
sqlStr := sqlStr || ' GROUP BY ';
sqlStr := sqlStr || ' ID_Station,PID , Re_Result ';
sqlStr := sqlStr || ' ,Name_Station ';
sqlStr := sqlStr || ' ,Location ';
sqlStr := sqlStr || ' ,HOT_Name ' ;OPEN p_curosr FOR sqlstr;
END Pooling_GetStatisticsList;

[心中的彩虹]

[Quote=引用楼主 skyakon 的回复:]
CREATE OR REPLACE PROCEDURE POOLING_GETSTATISTICSLIST (
idstations in varchar2,
idresults in INT,
hotNames in VARCHAR,
startTime in DATE,
endtime in DATE,
PID in varchar2,
p_……
[/Quote]

var cur refcursor;

exec POOLING_GETSTATISTICSLIST('wkc',....,:cur);

print cur;

[minitoy]

sql窗口调用不了.
使用sqlplus按楼上的方法定义变量后调用存储过程,然后显示cursor内容

[vber1010]

declare
v_cur p_myrefcursor.my_cursor;
begin
POOLING_GETSTATISTICSLIST('aa', 1, sysdate, sysdate, 'bbb', v_cur);
for cur in v_cur loop
--...
end loop;
--...
end;

[vber1010]

得到返回的游标，和普通的一样用。

[cnwz]

定义一个游标，执行后遍历游标，用语句查看你的游标内容

--测试：
declare
  cur SYS_REFCURSOR;
  l_data ADDBOOK%ROWTYPE;
begin
  pinfo(cur);
  loop
    exit when cur%notfound;
    FETCH cur INTO l_data;
    DBMS_OUTPUT.put_line (l_data.col_name||' '||l_data.idx_name||' '||l_data.data_type||' '||l_data.nullable||' '||l_data.comments);
  end loop;
  CLOSE cur;
END;

参考这个试试看

[Phoenix_99]

SQL> var cur refcursor;
SQL>exec POOLING_GETSTATISTICSLIST('a',1,'a',sysdate,sysdate,'a',:cur);
SQL>print :cur;