Oracle9i installation default date format is 'DD-MM-RR', this can pass
Select * from sys.nls_database_parameters;
View.
Therefore, there is a problem with the storage procedure for the input parameter to the Date type, in which the basic table (TRANFICSTAT)
The record date format is 'YYYY-MM-DD'. The original code is as follows:
- Compare the capacity of the same time of the two stations
Create or Replace Procedure Hy_ContraSt_Period
Depotcode1 in varchar2,
Depotcode2 in varchar2,
Startdate1 in date,
Enddate1 in Date,
Cur_Return Out cur_define.gb_cur) IS
-
Cur_define.gb_cur is a custom cursor type Begin
Case
When (depotcode1 is null) or (depotcode2) is null the
Return;
Else
Open Cur_Return for
SELECT
SUM (Normal_Schemes) AS is class,
Sum (Overtime_Schemes) AS overtime,
SUM (Normal_Seats) AS start-class seat number,
Sum (Overtime_Seats) AS overtime seat
From tranficstat
Where denddate> =
Startdate1
And Senddate <
Enddate1 1
And depot = depotcode1
Group by depot
union
SELECT
SUM (Normal_Schemes) AS is class,
Sum (Overtime_Schemes) AS overtime,
SUM (Normal_Seats) AS start-class seat number,
Sum (Overtime_Seats) AS overtime seat
From tranficstat
Where denddate> =
Startdate1
And Senddate <
Enddate1 1
And depot = depotcode2
Group by depot;
End Case;
End hy_contrast_period;
With UNION, you expect to return two records, but it is found to always return only one record. The problem of the problem occurs on the date format conversion, the format of the parameters is the 'DD-MM-RR', and the record format on the left side of the condition is 'YYYY-MM-DD', as long as all right side conditions are changed to
Where senddate> = to_date (to_char
Startdate1, 'YYYY-MM-DD'), 'YYYY-MM-DD')
And senddate Enddate1, 'YYYY-MM-DD'), 'YYYY-MM-DD') 1; The symptom can be eliminated. Of course, you can also modify the format on the left, in turn, make the dates format on both sides, and can also directly modify the NLS_DATE_FORMAT of the system.
