参考:
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
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货