oracle 函数问题(传SQL语句)

[iis40]

我给oracle函数的in变量传一个SQL语句，请问怎么在函数体内执行。谢谢

[RobinHZ]

execute

[iis40]

老大，具体怎么用啊，下面是函数内的部分代码
select distinct a.projectid,a.userid,b.worktypeid
from tp_dayreport a,tp_worktype b
where a.statusflag='1'
and a.projectid=v_projectId
and a.reportdate>=v_date1
and a.reportdate<=v_date2
and b.workTypeId in (execute(v_sql)) --这句有问题
order by a.projectid,a.userid,b.worktypeid) c,

v_sql是我的一句select语句，提示说必须声明标志符"execute"

[bzszp]

s_sql:='select distinct a.projectid,a.userid,b.worktypeid
from tp_dayreport a,tp_worktype b
where a.statusflag='1'
and a.projectid=v_projectId
and a.reportdate>=v_date1
and a.reportdate<=v_date2
and b.workTypeId in ('||v_sql||')';
execute immediate s_sql into ...;

[RobinHZ]

呵呵，参见bzszp(SongZip)所述。

[iis40]

不会吧，我上面的那些语句只是整个函数中的一部分，不会叫我把整个函数体都拼成一个字符串吧

[bzszp]

你可以做成带参数的游标
不知能不能满足楼主的需要

[beckhambobo]

oracle有这样一种方法，可能大家比较少见，现在介绍，让大家分享:
select * from plsql_function
以下是一个例子：

create or replace type mytabletype
as table of number;
/
create or replace function strtab(p_str in varchar2)
return mytabletype
as
lstr varchar2(1000) default p_str||',';
ln number;
ldata mytabletype:=mytalbetype();
begin
loop
ln:=instr(lstr,',');
exit when (nvl(ln,0)=0);
ldata.extend;
ldata(ldata.count):=ltrim(rtrim(substr(lstr,1,ln-1)));
lstr:=substr(lstr,ln+1);
end loop;
return ldata;
end;
/
select * from table(cast(strtab(v_sql) as mytabletype));

以上方法利用对象原理，oracle已对它支持，楼主可以做以上一函数，在查询语句可以实现

[iis40]

create or replace function fun_yggstjone
(
v_projectId in tp_projectinfo.projectid%type,
v_date1 in tp_dayreport.reportdate%type,
v_date2 in tp_dayreport.reportdate%type,
v_workTypeIds in varchar2
)
return pck_stagzl.sta_cur
is
v_sql varchar2(200);
v_retcur pck_stagzl.sta_cur;
begin
open v_retcur for
select e.projectid,e.userid,e.sum_wamount,f.worktypeid,f.wt_wamount
from (
select projectid,userid,sum(workamount) sum_wamount
from tp_dayreport
where statusflag='1'
and projectid=v_projectId
and reportdate>=v_date1
and reportdate<=v_date2
group by projectid,userid
order by projectid,userid
) e,

(
select c.*,d.wt_wamount
from (
select distinct a.projectid,a.userid,b.worktypeid
from tp_dayreport a,tp_worktype b
where a.statusflag='1'
and a.projectid=v_projectId
and a.reportdate>=v_date1
and a.reportdate<=v_date2
//错误 and b.workTypeId in (v_sql)
order by a.projectid,a.userid,b.worktypeid) c,

(select projectid,userid,worktypeid,sum(workamount) wt_wamount
from tp_dayreport
where statusflag='1'
and projectid=v_projectId
and reportdate>=v_date1
and reportdate<=v_date2
group by projectid,userid,worktypeid
order by projectid,userid,worktypeid) d
where c.projectid=d.projectid(+)
and c.userid=d.userid(+)
and c.worktypeid=d.worktypeid(+)
order by c.projectid,c.userid,c.worktypeid
) f
where e.projectid=f.projectid
and e.userid=f.userid
order by f.projectid,f.userid,f.worktypeid;
return v_retcur;
end fun_yggstjone;

我的v_sql是类似'11,12,13'的字符串，目的是为了把b.workTypeId等于11或12或13的数据查询出来；但是数据库系统却认为我在查询b.workTypeId等于字符串'11,12,13'的数据，结果呢，数据一条也没查询出来，请问各位高手，此问题怎么解决，加分100

[darkhorsedba]

用动态的PL/SQL!

[beckhambobo]

create or replace type mytabletype as table of number;
/

create or replace function strtab(p_str in varchar2)
return mytabletype
as
lstr varchar2(1000) default p_str||',';
ln number;
ldata mytabletype:=mytabletype();
begin
loop
ln:=instr(lstr,',');
exit when (nvl(ln,0)=0);
ldata.extend;
ldata(ldata.count):=ltrim(rtrim(substr(lstr,1,ln-1)));
lstr:=substr(lstr,ln+1);
end loop;
return ldata;
end;
/

SQL> select * from table(cast(strtab('11,12,13') as mytabletype));

COLUMN_VALUE
------------
11
12
13

SQL> create table bb(id varchar2(2),name varchar2(10));

Table created

SQL> insert into bb values('11','张三');

1 row inserted

SQL> insert into bb values('12','李四');

1 row inserted

SQL> insert into bb values('13','王五');

1 row inserted

SQL> select * from bb where id in (select * from table(cast(strtab('11,12,13') as mytabletype)));

ID NAME
-- ----------
11 张三
12 李四
13 王五

楼主满意了吧，以后回复贴子前，请理解清楚别人的答案！

[hot.wind]

bzszp(SongZip)的方法最直观。如果你的函数是一个大的sql，拼一下也没什么。