用函数解决。
SQL > select job,ename from emp;
JOB ENAME
--------- ----------
CLERK SMITH
SALESMAN ALLEN
SALESMAN WARD
MANAGER JONES
SALESMAN MARTIN
MANAGER BLAKE
MANAGER CLARK
ANALYST SCOTT
PRESIDENT KING
SALESMAN TURNER
CLERK ADAMS
JOB ENAME
--------- ----------
CLERK JAMES
ANALYST FORD
CLERK MILLER
已选择14行。
SQL > create or replace function f_ename(jobno in varchar2)
2 return varchar2 is
3
4 str_return varchar2(200);
5 cursor c_ename(v_jobno varchar2) is select ename from emp where job=v_jobno;
6 begin
7 for str_temp in c_ename(jobno) loop
8 str_return:=str_return | |str_temp.ename;
9 end loop;
10 return str_return;
11 end;
12 /
函数已创建。
SQL > select f_ename(job) from (
2 select distinct job from emp);
F_ENAME(JOB)
---------------------------------------
SCOTT FORD
SMITH ADAMS JAMES MILLER
JONES BLAKE CLARK
KING
ALLEN WARD MARTIN TURNER
SQL >
加上job列:
SQL > select job,f_ename(job) from (
2 select distinct job from emp);
JOB F_ENAME(JOB)
-------------------------------------------------------
ANALYST SCOTT FORD
CLERK SMITH ADAMS JAMES MILLER
MANAGER JONES BLAKE CLARK
PRESIDENT KING
SALESMAN ALLEN WARD MARTIN TURNER
SQL > select job,ename from emp;
JOB ENAME
--------- ----------
CLERK SMITH
SALESMAN ALLEN
SALESMAN WARD
MANAGER JONES
SALESMAN MARTIN
MANAGER BLAKE
MANAGER CLARK
ANALYST SCOTT
PRESIDENT KING
SALESMAN TURNER
CLERK ADAMS
JOB ENAME
--------- ----------
CLERK JAMES
ANALYST FORD
CLERK MILLER
已选择14行。
SQL > create or replace function f_ename(jobno in varchar2)
2 return varchar2 is
3
4 str_return varchar2(200);
5 cursor c_ename(v_jobno varchar2) is select ename from emp where job=v_jobno;
6 begin
7 for str_temp in c_ename(jobno) loop
8 str_return:=str_return | |str_temp.ename;
9 end loop;
10 return str_return;
11 end;
12 /
函数已创建。
SQL > select f_ename(job) from (
2 select distinct job from emp);
F_ENAME(JOB)
---------------------------------------
SCOTT FORD
SMITH ADAMS JAMES MILLER
JONES BLAKE CLARK
KING
ALLEN WARD MARTIN TURNER
SQL >
加上job列:
SQL > select job,f_ename(job) from (
2 select distinct job from emp);
JOB F_ENAME(JOB)
-------------------------------------------------------
ANALYST SCOTT FORD
CLERK SMITH ADAMS JAMES MILLER
MANAGER JONES BLAKE CLARK
PRESIDENT KING
SALESMAN ALLEN WARD MARTIN TURNER
create table grm_task_mscanfreqpoint(
moncenterid varchar2(8) not null, --制定任务的监测中心编号 8位
task_code varchar2(10) not null, --任务编号 格式:yyyymmdd##
frequency number(20,4) not null, --中心频率(MHz)
ifbw number(20,4) null, --中频带宽(KHz)
holdtime number(10) null, --驻留时间 单位ms
squelchthreshold varchar2(20) null, --静躁门限,带单位(dBuV)
demodmode varchar2(20) null, --解调模式(选项)
constraint pk_grm_task_mscanfreqpoint primary key(moncenterid,task_code,frequency),
constraint fk_grm_result_mscanfreqpoint foreign key(moncenterid,task_code)
references grm_task_main (moncenterid,task_code)
);修改后的函数如下( oralce版本为 8.05)
create or replace function uf_frequency(v_moncenterid in varchar2, v_task_code in varchar2)
return varchar2 is
str_return varchar2(200);
cursor c_ename(v_moncenterid varchar2, v_task_code varchar2) is select to_char(frequency) frequency
from grm_task_mscanfreqpoint where moncenterid=v_moncenterid and task_code = v_task_code;
begin
for str_temp in c_ename(v_moncenterid, v_task_code) loop
str_return:=str_return | |str_temp.frequency;
end loop;
return str_return;
end;
try:
create or replace function uf_frequency(v_moncenterid in varchar2, v_task_code in varchar2)
return varchar2 is
str_return varchar2(200);
cursor c_ename(v_mid varchar2, v_code varchar2) is select to_char(frequency) frequency
from grm_task_mscanfreqpoint where moncenterid=v_mid and task_code = v_code;
begin
for str_temp in c_ename(v_moncenterid, v_task_code) loop
str_return:=str_return ||str_temp.frequency;
end loop;
return str_return;
end;
/
return varchar2
as
returnstr varchar2(2000);
cursor aa is select * from s where s#=id;
begin
for a in aa loop
returnstr:=returnstr||' '||a.s#||' '||a.sname||' '||a.age;
end loop;
dbms_output.put_line(returnstr);
return returnstr;
end bbb;