如何使用select语句编写Oracle过程(特别是在SQL Developer上)?

DECLARE
  a VARCHAR2(4000);          -- Give it a size
BEGIN                        -- Begin the anonymous PL/SQL block
  Getmarketdetails2(a);      -- Call the procedure
  DBMS_OUTPUT.PUT_LINE( a ); -- Output the value
END;                         -- End the anonymous PL/SQL block
/                            -- End the PL/SQL statement

或:

VARIABLE a VARCHAR2(4000);   -- Create a bind variable
EXEC Getmarketdetails2(:a);  -- Execute the procedure using the bind variable
PRINT a                      -- Print the bind variable

假设是最新的Oracle版本,您可以使用 dbms_sql.return_result()

create or replace PROCEDURE Getmarketdetails2
IS
  c1 SYS_REFCURSOR;  
BEGIN

  OPEN c1 FOR 
  SELECT *
  from  dual;
  DBMS_SQL.RETURN_RESULT(c1);

END Getmarketdetails2;
/

然后简单地运行

exec Getmarketdetails2

唯一的缺点是SQL Developer只将结果显示为文本,而不是适当的结果网格。

这是我在Oracle中返回光标的方式

PROCEDURE GetAllData (P_CURSOR OUT SYS_REFCURSOR)
IS
BEGIN

  OPEN P_CURSOR FOR
    SELECT *
    FROM TABLE ;

END GetAllData ;

Declare a Varchar2; 
exec Getmarketdetails2(a);

Declare 
  a Varchar2(10); 
Begin
  Getmarketdetails2(a);
End;