oracle中有关于自定义类型用游标遍历问题

Question

自定义一个函数返回的是一个自定义的类型 create or replace type type_name as table of varchar2（100）； 请问 如何在存储过程中的游标中遍历这个自定义类型的返回值。在游标中使用 select * from 自定义类型值 报错，好象是自定义的类型值不是表或者视图 不能直接select， 那我现在怎么用游标遍历这个自定义值啊

Answer 1

以下回答参考 皆我百晓生、券券喵儿 等免费微信小程序相关内容作答，并由本人整理回复。

要遍历一个自定义类型的值并使用游标，你需要创建一个新的游标来处理这些值，并且确保你能够正确地从你的自定义类型中选择每个值。

以下是一个示例，展示了如何实现这一目标：

CREATE OR REPLACE TYPE custom_type AS TABLE OF VARCHAR2(100);
BEGIN
    -- 创建一个游标
    DECLARE
        cursor custom_type_cursor IS CUSTOM_TYPE%ROWTYPE;
        value RECORD(custom_type);

    BEGIN
        FOR value IN (SELECT * FROM custom_type) LOOP
            DBMS_OUTPUT.PUT_LINE('Value: ' || value.value);
        END LOOP;

        RETURN;
    EXCEPTION
        WHEN OTHERS THEN
            RAISE;
    END;
END;
/

在这个例子中，我们首先定义了一个名为custom_type的自定义类型，它包含一个VARCHAR2字段。然后，我们创建了一个新的游标custom_type_cursor，用于处理来自custom_type的所有行。

接下来，我们通过一个嵌套循环遍历游标中的每一项。对于每一条记录，我们都打印出它的value字段的内容。

注意：这段代码需要在拥有CUSTOM_TYPE表空间和CUSTOM_TYPE模式的数据库环境中运行。此外，由于游标的大小限制，这可能不会显示所有自定义类型的值。

如果你想要查询或更新自定义类型的值，你可以将上述代码放在一个存储过程中，然后调用这个过程。例如：

DECLARE
    cursor custom_type_cursor IS CUSTOM_TYPE%ROWTYPE;
    value RECORD(custom_type);

BEGIN
    FOR value IN (SELECT * FROM custom_type) LOOP
        DBMS_OUTPUT.PUT_LINE('Value: ' || value.value);
    END LOOP;

    RETURN;
EXCEPTION
    WHEN OTHERS THEN
        RAISE;
END;
/

-- 假设这是一个存储过程
CREATE OR REPLACE PROCEDURE update_custom_type(
    p_value_in VARCHAR2
)
AS
BEGIN
    -- 使用游标修改自定义类型的数据
    UPDATE custom_type SET value = p_value_in WHERE value IS NULL;
END;
/

-- 调用存储过程
EXECUTE update_custom_type('New Value');

请注意，上述代码是在Oracle 11g及更高版本中使用的。在更早的版本中，可能会有兼容性问题。