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
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
解决方案 »
- 一个事务中可否执行多条DDL或DCL
- ORACLE返回sys_refcursor的问题
- php 如何读取oracle 中是CLOB类型
- 初学者,2个简单的小问题?
- 求助oracle 11g的对于空表的问题!
- Oracle中有哪个系统表包含有其它各个表的各个字段的类型和长度?
- 我在sql*Plus中创建表和相应的触发器,但总是有错误:创建的触发器带有编译错误。 请看下面代码:
- oracle中有没有bit(sql server中有)数据类型?
- 如何避免向数据库同时插入相同的记录?
- 求一条SQL如何优化
- 请教关于ORACLE表锁定的问题!谢谢
- 连接处于域中的oracle服务器,几分钟后数据库连接将会自动断开.错误为经典的'hostdef扩展名不存在',哪位大哥有比较好的解决办法?
function getValueOfTab(p_id in number) is return varchar2 is
str varchar(300);
cursor c is select id,value from your_table where id = p_id;
rst c%rowtype;
begin
str = ' ';
open c
loop
fetch c into rst;
exit when c%notfound;
str = str || rst.value;
end loop;
close c;
return trim(str);
end;
用游标我也作了,但是我的查询的记录相当多。我的目的其实和bzszp(www.bzszp.533.net)的差不多。就是为了在对主表查询的时候能够将只表的一个字段的数据作为主表的一部分输出。但是因为记录很多,所以我用游标以后,速度太慢了。
这样可以提高效率。
请参见
http://www.zdnet.com.cn/developer/code/story/0,2000081534,39252129,00.htm
Creat or Replace Function fn_test(in_aa in number) return varchar2
is
v_out varchar2(2000);
type tab_type is table of your_table%type index by binary_integer;
main_tab tab_type;
begin
for row in (select * from your_table where id = in_aa) loop
main_tab(main_tab.count+1) := row;
end loop;
v_out := '';
for row in main_tab.first .. main_tab.last loop
v_out := v_out ||'<'||main_tab(row).value||'>';
end loop;
return v_out;
end;