看你返回类型定义了 个你个例子看看: create or replace type t_ret_table is table of varchar2(20);create or replace function f_test(var_num in integer) return t_ret_table is var_out t_ret_table; begin var_out := t_ret_table(); var_out.extend(var_num); for i in 1..var_num loop var_out(i) := i; end loop; return var_out; end f_test; / set serverout on declare aa t_ret_table; begin aa := f_test(10); for i in 1..aa.count loop dbms_output.put_line(aa(i)); end loop; end; /
比如scott用户下的emp表的部分,下面要求写一个函数,传入的参数deptno,然后要输出员工的empno,enane,job,deptno.表如下所示,请写出函数源码。谢谢。empno ename job deptno 7369 SMITH CLERK 20 7499 ALLEN SALESMAN 30 7521 WARD SALESMAN 30 7566 JONES MANAGER 20 7654 MARTIN SALESMAN 30 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7788 SCOTT ANALYST 20 7839 KING PRESIDENT 10 7876 ADAMS CLERK 30 7900 JAMES CLERK 20 7902 FORD ANALYST 10 7934 MILLER CLERK 10
代码返回的TABLE 定义成一个RECORD的TABLE就可以了下面代码没测过,可以会有错create or replace function f_test(var_num in integer) return t_ret_table is type t_rec is record (empno varchar2(20),enane varchar2(20),job varchar2(20),deptno varchar2(20)); type t_ret_table is table of t_rec; var_out t_ret_table; begin execute immediate 'select empno,enane,job,deptno from emp where deptno='''||var_num ||'''' bulk collect into var_out; return var_out; end f_test; /
但是你可以将查询的多个员工的信息通过字符串拼装,然后再返回出来啊 !
个你个例子看看:
create or replace type t_ret_table is table of varchar2(20);create or replace function f_test(var_num in integer) return t_ret_table is
var_out t_ret_table;
begin
var_out := t_ret_table();
var_out.extend(var_num);
for i in 1..var_num loop
var_out(i) := i;
end loop;
return var_out;
end f_test;
/
set serverout on
declare
aa t_ret_table;
begin
aa := f_test(10);
for i in 1..aa.count loop
dbms_output.put_line(aa(i));
end loop;
end;
/
7369 SMITH CLERK 20
7499 ALLEN SALESMAN 30
7521 WARD SALESMAN 30
7566 JONES MANAGER 20
7654 MARTIN SALESMAN 30
7698 BLAKE MANAGER 30
7782 CLARK MANAGER 10
7788 SCOTT ANALYST 20
7839 KING PRESIDENT 10
7876 ADAMS CLERK 30
7900 JAMES CLERK 20
7902 FORD ANALYST 10
7934 MILLER CLERK 10
type t_rec is record (empno varchar2(20),enane varchar2(20),job varchar2(20),deptno varchar2(20));
type t_ret_table is table of t_rec;
var_out t_ret_table;
begin
execute immediate 'select empno,enane,job,deptno from emp where deptno='''||var_num ||'''' bulk collect into var_out;
return var_out;
end f_test;
/
函数与过程是pl/sql的两种子程序
返回一个值,一般用函数
返回多个值,一般用过程