老大,具体怎么用啊,下面是函数内的部分代码 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"
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 ...;
呵呵,参见bzszp(SongZip)所述。
bzszp(SongZip)的方法最直观。如果你的函数是一个大的sql,拼一下也没什么。
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已对它支持,楼主可以做以上一函数,在查询语句可以实现
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
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 13SQL> create table bb(id varchar2(2),name varchar2(10));Table createdSQL> insert into bb values('11','张三');1 row insertedSQL> insert into bb values('12','李四');1 row insertedSQL> insert into bb values('13','王五');1 row insertedSQL> select * from bb where id in (select * from table(cast(strtab('11,12,13') as mytabletype)));ID NAME -- ---------- 11 张三 12 李四 13 王五楼主满意了吧,以后回复贴子前,请理解清楚别人的答案!
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"
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 ...;
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已对它支持,楼主可以做以上一函数,在查询语句可以实现
(
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
/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
13SQL> create table bb(id varchar2(2),name varchar2(10));Table createdSQL> insert into bb values('11','张三');1 row insertedSQL> insert into bb values('12','李四');1 row insertedSQL> insert into bb values('13','王五');1 row insertedSQL> select * from bb where id in (select * from table(cast(strtab('11,12,13') as mytabletype)));ID NAME
-- ----------
11 张三
12 李四
13 王五楼主满意了吧,以后回复贴子前,请理解清楚别人的答案!