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
sqlserver很简单都能做到? 怎没做请指教。
sqlserver不用游标程序如下: create function f_str(@a varchar(10),@b bit) returns varchar(8000) as begin declare @r varchar(8000) set @r='' if @b=0 select @r=@r+','+B from 表 where a=@a and b is not null else select @r=@r+','+C from 表 where a=@a and c is not null return(stuff(@r,1,1,'')) end
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 function f_str(@a varchar(10),@b bit)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
if @b=0
select @r=@r+','+B from 表 where a=@a and b is not null
else
select @r=@r+','+C from 表 where a=@a and c is not null
return(stuff(@r,1,1,''))
end
B和C是要连接的字段