Oracle 将行合并为一个行以发送短信

这是一个测试用例。

Create table Student  (
Stuid number(5) primary key, Status varchar2(10),name varchar2(300)
Class varchar2(30),mob varchar2(30));

Insert into Student values (1, 'PRESENT', 'ABC','PREP','+923008664447');
Insert into Student values (2, 'PRESENT', 'A','PREP','+923008664447');
Insert into Student values (3, 'PRESENT', 'BC','PREP','+923008664447');
Insert into Student values (4, 'PRESENT', 'DABC','TWO','+923008664447');
Insert into Student values (5, 'PRESENT', 'FABC','TWO','+923008664447');
Insert into Student values (6, 'PRESENT', 'AGBC','PREP','+923008664447');
Insert into Student values (7, 'PRESENT', 'AC','PREP','+923008664447');

create table subject (
sno number(4) primary key,class varchar2(30), subj varchar2(40));

Insert into Subject values (1, 'PREP','ENGLISH');
Insert into Subject values (2, 'PREP','URDU');
Insert into Subject values (3, 'PREP','MATH');
Insert into Subject values (4, 'TWO','ENGLISH');
Insert into Subject values (5, 'TWO','URDU');

create table exam (
exid number(4) primary key , exam_type varchar2(50));

Insert into exam values (1, '1st Term 2016');
Insert into exam values (2, '2nd Term 2016');
Insert into exam values (3, 'Final term 2016');

create table Test (
ID Number(5) Primary key,CLASS VARCHAR2(30),exid number(4) references exam(exid)
class varchar2(30),sno number(4) references subject(sno),tmarks number(3));

Insert into Test values (1, 'PREP',2,1,50);
Insert into Test values (2, 'PREP',2,2,50);
Insert into test values (1, 'PREP',2,3,50);
Insert into test values (1, 'TWO',1,4,50);
Insert into test values (1, 'TWO',1,5,50);

create table Test1 (
id number(5) references test(id),tstuid number(5) references student(stuid)
obtmarks number(5,2)remarks varchar2(50));

Insert into Test1 values (1, 1,30,'PAS);
Insert into Test1 values (1, 2,16.7,'PAS');
Insert into test1 values (1, 3,23,'PAS',);
Insert into test1 values (1, 4,10,'FAIL');
Insert into Test1 values (2, 1,39,'PAS);
Insert into Test1 values (2, 2,16.7,'PAS');
Insert into test1 values (2, 3,23,'PAS',);
Insert into test1 values (2, 4,10,'FAIL');
Insert into Test1 values (3, 1,3,'PAS);
Insert into Test1 values (3, 2,11,'PAS');
Insert into test1 values (3, 3,2,'PAS',);
Insert into test1 values (3, 4,1,'FAIL');

create table msgout (
id number(7) primary key,msgto varchar2(20),msg varchar2(1000),send varchar2(30));

我想给父母发送短信。但是当使用下面的代码时，父母收到的消息是分开的。例如，如果我们有4个主题，家长收到的消息是4次而不是1次。

declare
a number;         -- Student ID
b varchar2(300);  --Student Name
c varchar2(30);  --Mobile
CURSOR cc IS
select distinct stuid,,name,mobile from student where status='PRESENT';
BEGIN
open cc;
LOOP
fetch Cc into A,B,C;
insert into msgout (id,msgto,msg)
values
(a,c,'Marks Sheet of student '|| b || 'is as under: '|| :SNO ||':'|| :obtmarks ||' / '|| tmarks);
EXIT when Cc%NOTFOUND;
next_record;
END LOOP;
Close c;
end;

If student have 5 subjects the message is appearing in that fashion:
>> Respected Parents! Marks Sheet of Sara Nadeem of class NineS for the DECEMBER TEST 2016 is as under. Eng: 37 / 50 His/Her Perct. is 74. Regards!
>> Respected Parents! Marks Sheet of Sara Nadeem of class NineS for the DECEMBER TEST 2016 is as under. Math17 / 50 His/Her Perct. is 74. Regards!
>> Respected Parents! Marks Sheet of Sara Nadeem of class NineS for the DECEMBER TEST 2016 is as under. Isl: 27 / 50 His/Her Perct. is 74. Regards!

但我要求的输出是：

我所需的OutPut是：
尊敬的父母! 2016年12月测试ABC级9S成绩单见下表。
英： 37/50 ：
乌尔都语： 39/50
数学： 3/50

我想给一个学生发送1条短信，而不是5条短信。请指教

所以你想让每个学生在你的输出中一行？

如果是，将所有的表连接起来。然后使用listaggg ()为每个学生组合结果。

这里有一些让你开始的东西：

select st.stuid,
       'Respected Parents! Marks Sheet of ' || st.name || 
       ' of class NineS for the DECEMBER TEST 2016 is as under.' || 
       chr(10) || listagg( obtmarks || '/' || tmarks, chr(10)) within group (order by tmarks) sms
from   student st
join   test1 t1
on     st.stuid = t1.tstuid
join   test t
on     t.sno = st.stuid
group  by st.stuid, st.name;

STUID  SMS                                                                                                         
1      Respected Parents! Marks Sheet of ABC of class NineS for the DECEMBER TEST 2016 is as under.
30/50
39/50    
2      Respected Parents! Marks Sheet of A of class NineS for the DECEMBER TEST 2016 is as under.
16.7/50
16.7/50 

这将为您提供一行/学生，文本格式设置在几行上。参加考试、参加科目等。根据需要在文本中放置这些内容。

然后，您可以循环查看此结果，调用您的SMS发送例程。

注意： Listaggg()是11.2的特性。因此，如果你真的在9i上，你需要用strag代替：

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:15637744429336