CREATE OR REPLACE PACKAGE BODY mypack IS
PROCEDURE myproc(
in_real_name IN VARCHAR2,
in_companyId IN VARCHAR2,
in_party IN VARCHAR2,
in_startDate IN VARCHAR2,
in_endDate IN VARCHAR2,
outcursor IN OUT mycursor
)
IS
startDatetmp VARCHAR2(100) := in_startDate || ' 00:00:00';
endDatetmp VARCHAR2(100) := in_endDate || ' 23:59:59';
condition VARCHAR2(500) := 'b.area_id = 201';
sqlStr VARCHAR2(5000);
rect t_user%rowtype;
condition_1 VARCHAR2(500) := 'and create_date >=
to_date('||chr(39)||startDatetmp||chr(39)||','||
chr(39)||'yyyy-mm-dd hh24:mi:ss'||chr(39)||')
and create_date <=
to_date('||chr(39)||endDatetmp||chr(39)||','||
chr(39)||'yyyy-mm-dd hh24:mi:ss'||chr(39)||')';
condition_2 VARCHAR2(500) := 'and start_date >=
to_date('||chr(39)||startDatetmp||chr(39)||','||
chr(39)||'yyyy-mm-dd hh24:mi:ss'||chr(39)||')
and start_date <=
to_date('||chr(39)||endDatetmp||chr(39)||','||
chr(39)||'yyyy-mm-dd hh24:mi:ss'||chr(39)||')';
condition_3 VARCHAR2(500) := 'and createtime >=
to_date('||chr(39)||startDatetmp||chr(39)||','||
chr(39)||'yyyy-mm-dd hh24:mi:ss'||chr(39)||')
and createtime <=
to_date('||chr(39)||endDatetmp||chr(39)||','||
chr(39)||'yyyy-mm-dd hh24:mi:ss'||chr(39)||')';
BEGIN
if in_real_name is not null then
condition := condition||' and b.real_name like'||chr(39)||'%'||in_real_name||'%'||chr(39);
end if;
if in_companyId is not null then
condition := condition||' and b.company_id ='||in_companyId;
end if;
if in_party is not null then
condition := condition||' and b.party like'||chr(39)||'%'||in_party||'%'||chr(39);
end if;
condition := condition||' and company_id is not null';
---define 动态Sql
sqlStr := 'select t.userId,t.companyId,t.realname,t.companyName,t.party,t.dlcs, mkcs, time,dz1+dz2+dz3 dz,dt, spcs, xwcs,wxd, mkcs+dt+wxd*10+(dz1+dz2+dz3)*0.1+t.time+t.spcs+t.xwcs yhjf from ('||
' select b.id as userId,b.company_id as companyId,b.real_name as realname,'||
' (select count(id) from t_record where name = '||chr(39)||'登陆次数'||chr(39)||' and user_id = b.id '||condition_1||') dlcs,'||
'(select count(id) from t_record where name!='||chr(39)||'登陆次数'||chr(39)||' and user_id = b.id '||condition_1||') mkcs,'||
'nvl((select sum(use_time) from T_VIDEO_RECORD where user_id=b.id '||condition_2||'),0) time,(select company_name from t_company where id=company_id) companyName,b.party ,'||
'(select count(id) from t_praise where userid=b.id '||condition_3||') dz1,'||
'(select count(id) from t_picture_praise where user_id=b.id '||condition_1||') dz2,'||
'(select count(video_id) from t_video_praise where user_id=b.id '||condition_1||') dz3,'||
'(select count(id) from t_dyn_details where user_id=b.id and dyn_id=67 '||condition_1||') wxd,'||
'nvl((select sum(score) from t_question_record where user_id=b.id '||condition_1||'),0) dt,'||
'get_user_spcs( b.id,'||chr(39)|| in_startDate||chr(39)||','||chr(39)||in_endDate||chr(39)||') spcs,'||
'get_user_xwcs( b.id,'||chr(39)|| in_startDate||chr(39)||','||chr(39)||in_endDate||chr(39)||') xwcs'||
' from t_user b where '||condition||') t ';
OPEN outcursor FOR sqlStr;
loop
fetch outcursor into rect;
exit when outcursor%notfound;
Dbms_Output.put_line('Policy_code:'||rect.realname);
end loop;
close outcursor;
RETURN;
END myproc;
END;
3 个解决方案
#1
http://blog.csdn.net/sych888/article/details/53170158
#2
rect t_user%rowtype;
fetch outcursor into rect;
游标的结构和 t_user 的结构一样吗?
fetch outcursor into rect;
游标的结构和 t_user 的结构一样吗?
#3
没看懂问的问题。。
http://www.cnblogs.com/sc-xx/archive/2011/12/03/2275084.html
这里的游标的详细使用
http://www.cnblogs.com/sc-xx/archive/2011/12/03/2275084.html
这里的游标的详细使用
#1
http://blog.csdn.net/sych888/article/details/53170158
#2
rect t_user%rowtype;
fetch outcursor into rect;
游标的结构和 t_user 的结构一样吗?
fetch outcursor into rect;
游标的结构和 t_user 的结构一样吗?
#3
没看懂问的问题。。
http://www.cnblogs.com/sc-xx/archive/2011/12/03/2275084.html
这里的游标的详细使用
http://www.cnblogs.com/sc-xx/archive/2011/12/03/2275084.html
这里的游标的详细使用