获取多行并存储在 1 个变量中 - ORACLE 存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16967199/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Fetch MULTIPLE ROWS and STORE in 1 VARIABLE - ORACLE STORED PROCEDURE
提问by vrindamarfatia
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.
我正在研究 ORACLE 存储过程，但我有疑问。我有一个查询超过 1 行，我想将所有这 3 行的值存储在 1 个变量中。任何人都可以帮我解决这个问题。
My QUERY goes like this :
我的查询是这样的：
SELECT STUDENT_NAME
FROM STUDENT.STUDENT_DETAILS
WHERE CLASS_ID= 'C';
SELECT STUDENT_NAME
FROM STUDENT.STUDENT_DETAILS
WHERE CLASS_ID= 'C';
Here this query fetches 3 names
这里这个查询获取 3 个名字
Hyman, Jill, Bunny
Hyman、吉尔、兔子
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.
我希望所有这 3 个名称都存储在 1 个变量中，即 C_NAMES。之后，我将在程序的进一步步骤中使用该变量。
Can anyone please help me with this.
任何人都可以帮我解决这个问题。
I would highly appreciate your time and effort.
我非常感谢您的时间和精力。
Thanks in advance,
提前致谢，
Vrinda :)
温达 :)
回答by the_slk
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;
回答by Ederson
Depending on your Oracle version(>= 11G(11.2)), you can use LISTAGG:
根据您的 Oracle 版本(>= 11G(11.2))，您可以使用 LISTAGG：
SELECT LISTAGG(STUDENT_NAME,',') WITHIN GROUP (ORDER BY STUDENT_NAME)
FROM STUDENT.STUDENT_DETAILS
WHERE CLASS_ID= 'C';
EDIT: If your Oracle version is inferior to 11G(11.2), take a look here
编辑：如果您的 Oracle 版本低于 11G(11.2)，请看这里
回答by vrindamarfatia
Hi all and Thank you for your time. I have resolved the question and all thanks to Ederson.
大家好，感谢您的时间。我已经解决了这个问题，感谢埃德森。
Here is the solution :
这是解决方案：
SELECT WM_CONCAT(STUDENT_NAME)
FROM STUDENT.STUDENT_DETAILS WHERE CLASS_ID= 'C';
Now if you are using this in a stored procedure or PLSQL you just have to create a variable and use SELECT INTOwith it and print the variable.
现在，如果您在存储过程或 PLSQL 中使用SELECT INTO它，您只需创建一个变量并使用它并打印该变量。
Here is the code
这是代码
DECLARE
C_NAMES VARCHAR2(100);
BEGIN
SELECT WM_CONCAT(STUDENT_NAME) INTO C_NAMES
FROM STUDENT.STUDENT_DETAILS WHERE CLASS_ID= 'C';
dbms_output.put_line(sname);
END;
Thanks again for your help people.
再次感谢您的帮助人们。
回答by vc 74
You'll need a cursor for that:
你需要一个游标：
DECLARE
CURSOR stud_cur IS
SELECT STUDENT_NAME FROM STUDENT.STUDENT_DETAILS WHERE CLASS_ID= 'C';
l_stud STUDENT.STUDENT_DETAILS%ROWTYPE;
BEGIN
OPEN stud_cur;
LOOP
FETCH stud_cur INTO l_stud;
EXIT WHEN stud_cur%NOTFOUND;
/* The first time, stud_cur.STUDENT_NAME will be Hyman, then Jill... */
END LOOP;
CLOSE stud_cur;
END;
