在1个变量中存储多个行并存储-Oracle存储过程 [英] Fetch MULTIPLE ROWS and STORE in 1 VARIABLE - ORACLE STORED PROCEDURE

问题描述

我正在研究ORACLE存储的程序，我对此表示怀疑. 我有一个查询，该查询获取多于1行，并且我想将所有这3行的值存储在1变量中. 有人可以帮我吗?

I am working on ORACLE STORED PROCEDURES and I have a doubt. I have a query which fetches more than 1 row and I want to store all those 3 row's values in 1 Variable. Can anybody please help me with this.

我的查询是这样的:

SELECT STUDENT_NAME FROM STUDENT.STUDENT_DETAILS WHERE CLASS_ID= 'C';

SELECT STUDENT_NAME FROM STUDENT.STUDENT_DETAILS WHERE CLASS_ID= 'C';

此查询获取3个名称

杰克， 吉尔 兔子

Jack, Jill, Bunny

我希望所有这3个名称都存储在1个变量中，即C_NAMES. 之后，我将在后续过程中使用该变量.

I want all those 3 names to be stored in 1 variable i.e C_NAMES. And after that I am using that variable in further steps of my procedure.

有人可以帮我吗?

非常感谢您的时间和精力.

I would highly appreciate your time and effort.

预先感谢

Vrinda:)

推荐答案

CREATE PROCEDURE a_proc
AS
    CURSOR names_cur IS
        SELECT  student_name
        FROM    student.student_details
        WHERE   class_id = 'C';

    names_t  names_cur%ROWTYPE;
    TYPE names_ntt IS TABLE OF names_t%TYPE; -- must use type
    l_names  names_ntt;
BEGIN
    OPEN  names_cur;
    FETCH names_cur BULK COLLECT INTO l_names;
    CLOSE names_cur;

    FOR indx IN 1..l_names.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(l_names(indx).student_name);
    END LOOP;
END a_proc;

这篇关于在1个变量中存储多个行并存储-Oracle存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！