如何在PostgreSQL存储过程中使用refcursor调用输出?

huangapple go评论57阅读模式
英文:

How I can call the output in the refcursor using Stored procedure in PostgreSQL?

问题

我想在refcursor中输出,但它不起作用。

我尝试了很多方法来在refcursor中获取输出,有时它显示<未命名Portal 1>。

英文:

I want to output in refcursor but it's not working.

I tried a lot of thing to get output in refcursor and sometimess its showing <Unnamed Portal 1>

CREATE OR REPLACE PROCEDURE &quot;MasterUser&quot;.s_m_userapplication(
	v_app_no text,
	INOUT v_refcur refcursor DEFAULT &#39;rs_resultone&#39;::refcursor)
LANGUAGE &#39;plpgsql&#39;
AS $BODY$
BEGIN
   open v_refcur for select AM_App_ProcCD,AM_App_ProcDt
   from &quot;MasterUser&quot;.Master_Application
   where &quot;MasterUser&quot;.appnov1(CAST(AM_AAPP_NO_TYPE AS INTEGER), AM_AAPP_NO_YEAR, AM_AAPP_NO_MONTH, AM_AAPP_NO_SEQ) =(v_App_No);
END;
 
 
 
$BODY$;

ALTER PROCEDURE &quot;MasterUser&quot;.s_m_userapplication(text, refcursor)
    OWNER TO USERDBA;

答案1

得分: 0

当你使用INOUT refcursor时,你将始终查看一列和一条记录,使用my_procedure()调用。在列值内,你可以查看你选择的记录。如果你从后端获取游标的数据,你不需要关心IDE的视图。在后端,这将起作用。如果你需要像select * from table一样查看数据,那么你必须使用函数而不是存储过程。

英文:

When you are using INOUT refcursor you will be viewing always one column and one record using the call my_procedure(). Inside the column value you can view your selecting records. (See picture) If you're getting data from the cursor on the backend, you don't care about the IDE view. On the backend side this will work. If you need to view data like as select * from table then you must use function instead of procedure.

如何在PostgreSQL存储过程中使用refcursor调用输出?

huangapple
  • 本文由 发表于 2023年2月8日 17:52:13
  • 转载请务必保留本文链接:https://go.coder-hub.com/75383974.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定