The 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 OUT IN mycursor
)
IS
StartDatetmp VARCHAR2 (100) :=in_startDate | | '00:00:00';
EndDatetmp VARCHAR2 (100) :=in_endDate | | '23:59:59';
Condition VARCHAR2 (500) :='b.a rea_id=201'.
SqlStr VARCHAR2 (5000);
The rect t_user % rowtype;
Condition_1 VARCHAR2 (500) :='and create_date & gt;=
To_date (' | | CRH (39) | | startDatetmp | | CRH (39) | | ', '| |
CRH (39) | | 'yyyy - mm - dd hh24: mi: ss' | | CRH (39) | | ')
And create_date & lt;=
To_date (' | | CRH (39) | | endDatetmp | | CRH (39) | | ', '| |
CRH (39) | | 'yyyy - mm - dd hh24: mi: ss' | | CRH (39) | | ');
Condition_2 VARCHAR2 (500) :='and the start_date & gt;=
To_date (' | | CRH (39) | | startDatetmp | | CRH (39) | | ', '| |
CRH (39) | | 'yyyy - mm - dd hh24: mi: ss' | | CRH (39) | | ')
And the start_date & lt;=
To_date (' | | CRH (39) | | endDatetmp | | CRH (39) | | ', '| |
CRH (39) | | 'yyyy - mm - dd hh24: mi: ss' | | CRH (39) | | ');
Condition_3 VARCHAR2 (500) :='and createtime & gt;=
To_date (' | | CRH (39) | | startDatetmp | | CRH (39) | | ', '| |
CRH (39) | | 'yyyy - mm - dd hh24: mi: ss' | | CRH (39) | | ')
And createtime & lt;=
To_date (' | | CRH (39) | | endDatetmp | | CRH (39) | | ', '| |
CRH (39) | | 'yyyy - mm - dd hh24: mi: ss' | | CRH (39) | | ');
The BEGIN
If in_real_name is not null then
Condition:=condition | | 'and b.r eal_name like' | | CRH (39) | | '%' | | in_real_name | | '%' | | CRH (39);
End the if;
If in_companyId is not null then
Condition:=condition | | 'and b.com pany_id=' | | in_companyId;
End the if;
If in_party is not null then
Condition:=condition | | 'and p. arty like' | | CRH (39) | | '%' | | in_party | | '%' | | CRH (39);
End the if;
Condition:=condition | | 'and company_id is not null';
- define dynamic Sql
SqlStr:='select t.u serId, t.com panyId, t.r ealname, t.com panyName, t.p arty, t.d LCS, MKCS, time, dz1 + dz2 + dz3 dz, dt, SPCS, XWCS, WXD, MKCS + dt + WXD * 10 + (dz1 + + dz3 dz2) * 0.1 + t.t ime + t.s PCS + t.x WCS yhjf from (' | |
'the select b.i d as userId, b.com pany_id as companyId, b.r eal_name as realname,' | |
'(select count (id) from t_record where name=' | | CRH (39) | | 'landing times' | | CRH (39) | |' and user_id=b.i d '| | condition_1 | |') DLCS, '| |
'(select count (id) from t_record where the name!='| | CRH (39) | |' landing times' | | CRH (39) | | 'and user_id=b.i d' | | condition_1 | | ') MKCS, '| |
'NVL ((select sum (use_time) from T_VIDEO_RECORD where user_id=b.i d' | | condition_2 | | '), 0) time, (select company_name from t_company where id=company_id) companyName, p. arty, '| |
'(select count (id) from t_praise where userid=b.i d' | | condition_3 | | ') dz1, '| |
'(select count (id) from t_picture_praise where user_id=b.i d' | | condition_1 | | ') dz2, '| |
'(select count (video_id) from t_video_praise where user_id=b.i d' | | condition_1 | | ') dz3, '| |
'(select count (id) from t_dyn_details where user_id=b.i d and dyn_id=67' | | condition_1 | | ') WXD, '| |
'NVL ((select sum (score) from t_question_record where user_id=b.i d' | | condition_1 | | '), 0) dt, '| |
'get_user_spcs (b.i d,' | | CRH (39) | | in_startDate | | CRH (39) | | ', '| | CRH (39) | | in_endDate | | CRH (39) | |') SPCS, '| |
'get_user_xwcs (b.i d,' | | CRH (39) | | in_startDate | | CRH (39) | | ', '| | CRH (39) | | in_endDate | | CRH (39) | |') XWCS '| |
'the from t_user where b' | | condition | | 't');
The OPEN outcursor FOR sqlStr;
Loop
The fetch outcursor into the rect.
Exit the when outcursor % notfound;
Dbms_Output. Put_line (' Policy_code: '| | the rect, realname);
End loop;
The close outcursor;
The RETURN;
END myproc;
END;
CodePudding user response:
http://blog.csdn.net/sych888/article/details/53170158CodePudding user response:
The rect t_user % rowtype;The fetch outcursor into the rect.
The structure of the cursor and t_user structure?
CodePudding user response:
Didn't understand to ask question,,http://www.cnblogs.com/sc-xx/archive/2011/12/03/2275084.html
The detailed use cursor