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'.
The BEGIN
If in_real_name is not null then
Condition:=condition | | 'and b.r eal_name like' | | '%' | | in_real_name | | '%'.
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' | | '%' | | in_party | | '%'.
End the if;
Condition:=condition | | 'and company_id is not null';
The OPEN outcursor FOR
The 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 + dz2 + dz3) * 0.1 + t.t ime + t.s PCS +
T.x WCS yhjf
The from (select b.i d as userId,
B.com pany_id as companyId,
B.r eal_name as realname,
(select count (id)
The from t_record
Where name='landing times'
And user_id=b.i d
And create_date & gt;=
To_date (startDatetmp,
'- dd yyyy - mm hh24: mi: ss')
And create_date & lt;=
To_date (endDatetmp,
'- dd yyyy - mm hh24: mi: ss')) DLCS,
(select count (id)
The from t_record
Where the name!
='login number'And user_id=b.i d
And create_date & gt;=
To_date (startDatetmp,
'- dd yyyy - mm hh24: mi: ss')
And create_date & lt;=
To_date (endDatetmp,
'- dd yyyy - mm hh24: mi: ss')) MKCS,
NVL ((select sum (use_time)
The from T_VIDEO_RECORD
Where user_id=b.i d
And the start_date & gt;=
To_date (startDatetmp,
'- dd yyyy - mm hh24: mi: ss')
And the start_date & lt;=
To_date (endDatetmp,
'- dd yyyy - mm hh24: mi: ss')),
0) time,
(select company_name from t_company where id=company_id) companyName,
P. arty,
(select count (id)
The from t_praise
Where userid=b.i d
And createtime & gt;=
To_date (startDatetmp,
'- dd yyyy - mm hh24: mi: ss')
And createtime & lt;=
To_date (endDatetmp,
'- dd yyyy - mm hh24: mi: ss')) dz1,
(select count (id)
The from t_picture_praise
Where user_id=b.i d
And create_date & gt;=
To_date (startDatetmp,
'- dd yyyy - mm hh24: mi: ss')
And create_date & lt;=
To_date (endDatetmp,
'- dd yyyy - mm hh24: mi: ss')) dz2,
(select count (video_id)
The from t_video_praise
Where user_id=b.i d
And create_date & gt;=
To_date (startDatetmp,
'- dd yyyy - mm hh24: mi: ss')
And create_date & lt;=
To_date (endDatetmp,
'- dd yyyy - mm hh24: mi: ss')) dz3,
(select count (id)
The from t_dyn_details
Where user_id=b.i d
And dyn_id=67
And create_date & gt;=
To_date (startDatetmp,
'- dd yyyy - mm hh24: mi: ss')
And create_date & lt;=
To_date (endDatetmp,
'- dd yyyy - mm hh24: mi: ss')) WXD,
NVL ((select sum (score)
The from t_question_record
Where user_id=b.i d
And create_date & gt;=
To_date (startDatetmp,
'- dd yyyy - mm hh24: mi: ss')
And create_date & lt;=
To_date (endDatetmp,
'- dd yyyy - mm hh24: mi: ss')),
0) dt,
Get_user_spcs (b.i d, in_startDate in_endDate) SPCS,
Get_user_xwcs (b.i d, in_startDate in_endDate) XWCS
The from t_user b
The where condition) t;
The RETURN;
END myproc;
END;
This condition into seem to have a problem, a great god, please help take a look at how to take it to the cursor query in SQL, thanks
CodePudding user response:
You put the wrong also posted;Or, to put this as the base table involved also send you,
CodePudding user response:
Define the type of good mycursor in baotou?CodePudding user response:
The building Lord, you put your business take out,To help you deal with again,
CodePudding user response:
First of all, you'll see compile with show errors exception information