怎么样写ORACLE自定义函数返回表？

Question

请教各位高人，我现在有这么个烦心事：有一个恶长恶长的SQL语句（真的很长），带了十多个参数，在PL/SQL里运行通过。问题是：我要在程序页面（ASP.NET）里来调用这个长的SQL语句的话，拼这么长SQL用字符串连接的话实在太麻烦了。我是想：把这个超长的SQL语句写成一个ORACLE的自定义函数，并返回一个table，那么在程序里只要调用 "select * from F_函数" 不就可以了嘛？但是：小弟没写过返回table的自定义函数，请高手指点，并给出范例，多谢多谢！这个恶心的SQL貌似这样的：
。

Answer 1

在函数中，如果输出结果集，请使用引用游标。
具体参看：http://hi.baidu.com/zhao_e893/blog/item/5c7097356a9d8f1190ef3937.html

Answer 2

我要在程序页面（ASP.NET）里来调用这个长的SQL语句的话，拼这么长SQL用字符串连接的话实在太麻烦了。
你就是嫌弃拼字符串麻烦。可以把拼接字段串的过程用函数来实现嘛!
select f_Getsql() into ls_Sql from dual;
这样,你就直接执行ls_sql就行了

Answer 3

多谢回帖,请继续指教...TO:jdsnhan(柳荫凉) 
能说的具体点吗? f_Getsql() 这个函数怎么写?

Answer 4

觉得通过函数拼字符串可能还不如直接在.net里面拼呢
用函数拼字符串，然后调用函数得到字符串，再执行字符串，整个过程要比直接在.net里面拼字符串要麻烦得多

Answer 5

那么按我的想法把SQL写成一个ORACLE返回表的自定义函数可行吗?如果可以,应该怎么写?

Answer 6

xiaoxiao1984(笨笨猫儿) ( ) 信誉：100    Blog  2006-12-08 09:36:50  得分: 0  
 
 
   觉得通过函数拼字符串可能还不如直接在.net里面拼呢
用函数拼字符串，然后调用函数得到字符串，再执行字符串，整个过程要比直接在.net里面拼字符串要麻烦得多  
 
--
这种方法我赞同
。net里面拼字符串多容易阿

Answer 7

StringBuilder sb = new StringBuilder();
            sb.Append("Select ");
            sb.Append(StoreProp.TABLE_NAME);
            sb.Append(".*, ");
            sb.Append(Prop.COLUMN_PROP_ID);
            sb.Append(",");
            sb.Append(Prop.COLUMN_BRAND_ID);
            sb.Append(",");
            sb.Append(Store.COLUMN_STORE_ID);
            sb.Append(" From ");
            sb.Append(StoreProp.TABLE_NAME);
            sb.Append(" inner join ");
            sb.Append(Prop.TABLE_NAME);
            sb.Append(" On ");
            sb.Append(StoreProp.TABLE_NAME);
            sb.Append(".");
            sb.Append(StoreProp.COLUMN_PROP_NO);
            sb.Append(" = ");
            sb.Append(Prop.TABLE_NAME);
            sb.Append(".");
            sb.Append(Prop.COLUMN_PROP_NO);
            sb.Append(" inner join ");
            sb.Append(Store.TABLE_NAME);
            sb.Append(" On ");
            sb.Append(StoreProp.TABLE_NAME);
            sb.Append(".");
            sb.Append(StoreProp.COLUMN_STORE_NO);
            sb.Append(" = ");
            sb.Append(Store.TABLE_NAME);
            sb.Append(".");
            sb.Append(Store.COLUMN_STORE_NO);
            if (condition.Length > 0)
            {
                sb.Append(" Where ");
                sb.Append(condition);
            }--这不是挺容易么

Answer 8

to:icedut(冰)
一般情况下我是按照这么来拼SQL的,但是我遇到的这个SQL有你例子的20倍那么长....:(..所以我在想有没有别的辙可以简便点??xiaoxiao1984(笨猫儿) "自定义函数是不能够返回表的，如果想返回记录集，应该使用存储过程返回游标"存储过程返回游标,是这样吗?有例子就好了.我记得在MSSQL里可以写个返回记录集table的函数的...ORACLE不行啊....
  

Answer 9

我记得在MSSQL里可以写个返回记录集table的函数的...ORACLE不行啊....
--
oracle要用游标的
一般情况下我是按照这么来拼SQL的,但是我遇到的这个SQL有你例子的20倍那么长....:(..
--那你再存储过程之中，不还是要拼写sql语句么，一样很长啊

Answer 10

zt
这一阵子，论坛上多次出现了询问如何从oracle中返回动态记录集的问题。由于我一直在oracle7的平台上工作，对这个问题没有太多了解，也回答不好。今天晚上凉风习习，正是学习的好时候，就对这个问题做了一点初步的学习。下面是一些心得，供大家讨论。测试版本：oracle 8.16企业版
参考：
http://download-west.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a89856/06_ora.htm#1614
http://download-west.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a89856/11_dynam.htm#7875
相关问题：
http://www.csdn.net/expert/Topic/306/306300.shtm
http://www.csdn.net/expert/Topic/336/336154.shtm
...测试过程：
1、建立测试表
CREATE TABLE student
 (
  id                         NUMBER,
  name                       VARCHAR2(30),
  sex                        VARCHAR2(10),
  address                    VARCHAR2(100),
  postcode                   VARCHAR2(10),
  birthday                   DATE,
  photo                      LONG RAW
 )
/2、建立带ref cursor定义的包和包体及函数：
CREATE OR REPLACE
package pkg_test as
/* 定义ref cursor类型
   不加return类型，为弱类型，允许动态sql查询，
   否则为强类型，无法使用动态sql查询;
*/
  type myrctype is ref cursor; --函数申明
  function get(intID number) return myrctype;
end pkg_test;
/CREATE OR REPLACE
package body pkg_test as
--函数体
   function get(intID number) return myrctype is
     rc myrctype;  --定义ref cursor变量
     sqlstr varchar2(500);
   begin
     if intID=0 then
        --静态测试，直接用select语句直接返回结果
        open rc for select id,name,sex,address,postcode,birthday from student;
     else
        --动态sql赋值，用:w_id来申明该变量从外部获得
        sqlstr := 'select id,name,sex,address,postcode,birthday from student where id=:w_id';
        --动态测试，用sqlstr字符串返回结果，用using关键词传递参数
        open rc for sqlstr using intid;
     end if;     return rc;
   end get;end pkg_test;
/3、用pl/sql块进行测试：
declare
  w_rc       pkg_test.myrctype; --定义ref cursor型变量  --定义临时变量，用于显示结果
  w_id       student.id%type;
  w_name     student.name%type;
  w_sex      student.sex%type;
  w_address  student.address%type;
  w_postcode student.postcode%type;
  w_birthday student.birthday%type;begin
  --调用函数，获得记录集
  w_rc := pkg_test.get(1);  --fetch结果并显示
  fetch w_rc into w_id,w_name,w_sex,w_address,w_postcode,w_birthday;
  dbms_output.put_line(w_name);
end;4、测试结果：
通过。在上述的online document中有相当多的内容，包括怎样在pro*c传递动态参数给存储过程、ref cusor的使用限制、open cursor for的4中不同方法等等。如需要的话请大家参考原文，恕我不一一列举。欢迎大家对此进行讨论！

Answer 11

What Are Table Functions?Table functions return a collection type instance representing rows in a table. They
can be queried like a table by calling the function in the FROM clause of a query,
Accepting and Returning Multiple Rows with Table Functions
enclosed by the TABLE keyword. They can be assigned to a PL/SQL collection
variable by calling the function in the SELECT list of a query.
Example: Assigning the Result of a Table Function
The following example shows how you can assign the result of a table function to a
PL/SQL collection variable. Because the table function is called from the SELECT
list of the query, you do not need the TABLE keyword.create type numset_t as table of number;
/
create function f1(x number) return numset_t pipelined is
begin
Accepting and Returning Multiple Rows with Table Functions
for i in 1..x loop
  pipe row(i);
end loop;
return;
end;
/
-- pipelined function in from clause
select * from table(f1(3));

Answer 12

StringBuilder sb = new StringBuilder();
sb.Append("Select ").Append(StoreProp.TABLE_NAME).Append(".*, ").Append(Prop.COLUMN_PROP_ID)....
可以连续写啊，不需要那么辛苦每行的写

Answer 13

个人爱好,有人习惯在oracle里拼,有人喜欢在外部拼

Answer 14

包里面用一个存储过程,返回游标,就可以了>包的定义
1) 包头
create or replace package mypk
as
  type t_cursor is ref cursor;
  procedure proc(name varchar2,c out t_cursor,a number);
end;2) 包体
create or replace package body mypk
as
  procedure proc(name varchar2,c out t_cursor,a number)
  as 
  begin
  open c for select * from test where id=a and name=name;
  end proc;
end;