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
同意baojianjun(包子) 兄的看法。 大家不应该太依赖技巧!
如果你用oracle9i可以用CREATE AGGREGATE FUNCTION 建一个函数将字符串合并起来
select function_xxx()from dual;
算不算1条sql啊,从效果看是一条
但不是用select语句。
用procedure。
我個人認為這是一個很大的誤區,
ORALCE的功能和強大,
SQL查詢語句是它的一個部分而已,
應該嘗試更深入的研究而不要每天在為SQL語句的排列和技巧努力
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
大家不应该太依赖技巧!