oracle子查询引用外表_Oracle SQL：声明要在查询&中使用的变量子查询

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