gpt4 book ai didi

oracle - 如何从plsql存储过程返回集合类型

转载 作者:行者123 更新时间:2023-12-02 08:20:09 25 4
gpt4 key购买 nike

我在 PL/SQL 中有以下存储过程:

CREATE OR REPLACE PROCEDURE sample_procedure AS 
DECLARE
TYPE list_of_names_t
IS TABLE OF emp.emp_index%TYPE;
ignoreIndexes LIST_OF_NAMES_T := List_of_names_t();
BEGIN
-- Logic here which fills the values in the collection ignoreIndexes
END;

当从外部调用此存储过程时,如下所示:

    SET SERVEROUTPUT ON
EXEC sample_procedure
-- Line YY

@ Line YY,我想从 emp 表中检索记录,其中索引不在存储过程中准备的 ignoreindexes 集合中。

1)如何将存储过程中创建的嵌套表ignoreindexes返回给外界,以便我可以使用该表中的索引

提前致谢

最佳答案

首先,它们的类型需要在过程外部声明,以便类型定义对过程外部的代码可见。您可以在 SQL 中声明类型

CREATE TYPE list_of_names_t
AS TABLE OF NUMBER;

或者您可以在 PL/SQL 中声明它

CREATE OR REPLACE PACKAGE types_package
AS
TYPE list_of_names_t
IS TABLE OF emp.emp_index%type;
END;

然后,您的过程必须使用并返回 SQL 类型

CREATE OR REPLACE PROCEDURE sample_procedure( 
p_ignore_indexes OUT list_of_names_t
)
AS
BEGIN
-- Logic here which fills the values in the collection p_ignore_indexes
END;

或 PL/SQL 类型

CREATE OR REPLACE PROCEDURE sample_procedure( 
p_ignore_indexes OUT types_package.list_of_names_t
)
AS
BEGIN
-- Logic here which fills the values in the collection p_ignore_indexes
END;

当然,如果代码的目的是返回集合,那么编写函数比过程更有意义

CREATE OR REPLACE FUNCTION sample_function
RETURN types_package.list_of_names_t
AS
ignore_indexes types_package.list_of_names_t;
BEGIN
-- Logic here which fills the values in the collection ignore_indexes
RETURN ignore_indexes;
END;

当你调用该过程时,你会做类似的事情

DECLARE
l_ignore_indexes types_package.list_of_names_t;
BEGIN
l_ignore_indexes := sample_function;
-- Do something with l_ignore_indexes
END;

DECLARE
l_ignore_indexes types_package.list_of_names_t;
BEGIN
sample_procedure( l_ignore_indexes );
-- Do something with l_ignore_indexes
END;

关于oracle - 如何从plsql存储过程返回集合类型,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14247779/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com