I'm somewhat new to this Oracle database and I've inherited a large-ish query with several sub-queries. I'd like to optimize it by declaring a few variables to reference later on within the queries, but I can't seem to get it right.
Here is an extremely dumbed-down version of my query that, if I can get this in the right format, I think I can get the full version working:
DECLARE
outage_start_time INTEGER := 1456894800;
outage_end_time INTEGER := 1457586000;
DST_offset INTEGER := 0;
time_zone_offset INTEGER := 4;
BEGIN
WITH subquery AS (
SELECT DISTINCT
mytable.tickets AS "TicketID"
FROM mytable
WHERE
mytable_start_time >= outage_start_time AND
mytable_end_time < outage_end_time
)
SELECT DISTINCT
subquery."TicketID" AS "Ticket ID",
TO_CHAR(TO_DATE('1970/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS')+(mytable.start_time/((60*60)*24)-(time_zone_offset + DST_offset)/24),'MM/DD/YYYY HH:MI:SS PM') AS "Outage Start",
TO_CHAR(TO_DATE('1970/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS')+(mytable.end_time/((60*60)*24)-(time_zone_offset + DST_offset)/24), 'MM/DD/YYYY HH:MI:SS PM') AS "Outage End"
--other stuff
FROM mytable
LEFT OUTER JOIN subquery ON mytable.tickets = subquery."TicketID"
;
END;
The error that I'm getting is:
Error starting at line : 1 in command -
DECLARE
outage_start_time INTEGER := 1456894800;
outage_end_time INTEGER := 1457586000;
DST_offset INTEGER := 0;
time_zone_offset INTEGER := 4;
BEGIN
WITH subquery AS (
SELECT DISTINCT
mytable.tickets AS "TicketID"
FROM T528
WHERE
mytable.start_time >= outage_start_time AND
mytable.end_time < outage_end_time
)
SELECT DISTINCT
subquery."TicketID" AS "Ticket ID",
TO_CHAR(TO_DATE('1970/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS')+(mytable.start_time/((60*60)*24)-(time_zone_offset + DST_offset)/24), 'MM/DD/YYYY HH:MI:SS PM') AS "Outage Start",
TO_CHAR(TO_DATE('1970/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS')+(mytable.end_time/((60*60)*24)-(time_zone_offset + DST_offset)/24), 'MM/DD/YYYY HH:MI:SS PM') AS "Outage End"
--other stuff
FROM T528
LEFT OUTER JOIN subquery ON mytable.tickets = subquery."TicketID"
;
END;
Error report -
ORA-06550: line 7, column 3:
PLS-00428: an INTO clause is expected in this SELECT statement
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
It looks like my errors are coming at lines 1 and 7, so I clearly have no idea how to write this properly. A bit of help would be GREATLY appreciated. Thanks!
解决方案
You need an INTO clause, to say in which variables to fetch the result of your query:
SELECT DISTINCT
subquery."TicketID" AS "Ticket ID",
TO_CHAR(TO_DATE('1970/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS')+(mytable.start_time/((60*60)*24)-(time_zone_offset + DST_offset)/24), 'MM/DD/YYYY HH:MI:SS PM') AS "Outage Start",
TO_CHAR(TO_DATE('1970/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS')+(mytable.end_time/((60*60)*24)-(time_zone_offset + DST_offset)/24), 'MM/DD/YYYY HH:MI:SS PM') AS "Outage End"
--other stuff
INTO variable1, variable2, ...
FROM T528
So, define a variable for each column you need to fetch, with corresponding type, and add the INTO clause to fetch the result of your query in your variables.
The solution above works well if your query returns exactly one row, fetching the values into scalar variables.
If your query returns more than one row, you need some array variables, to handle all the values; one way to fetch it could be:
define a type as an array; you may need arrays of numbers, varchar2,
..., dending on the type of the fetched columns
define a variable for each column you need to fetch
add a BULK COLLECT INTO clause, to say it to massively fetch all the
rows into the array variables
After the statement, youll'have your array variables filled with the result set of your query
For example:
declare
type tyTabNuber is table of number index by pls_integer;
type ty...
--
vTabNumber tyTabNumber;
...
begin
select ...
BULK COLLECT INTO vTabNumber, ...
FROM ...
...
end