Oracle SQL:声明要在查询&中使用的变量子查询 [英] Oracle SQL: Declaring variables to use in queries & subqueries

问题描述

我对这个Oracle数据库有些陌生，并且继承了带有多个子查询的大型查询.我想通过在查询中声明一些变量以供稍后引用来对其进行优化，但是我似乎无法正确地做到这一点.

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;

我得到的错误是:

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:

看来我的错误出现在第1行和第7行，所以我显然不知道如何正确编写它.一点帮助将不胜感激.谢谢！

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!

推荐答案

您需要一个INTO子句，说明要从哪个变量中获取查询结果:

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

因此，为需要提取的每一列定义一个变量，并使用相应的类型，并添加INTO子句以在变量中提取查询结果.

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:

• 将类型定义为数组；您可能需要数字数组varchar2， ...，取决于所提取列的类型
• 为您需要提取的每一列定义一个变量
• 添加一个BULK COLLECT INTO子句，说它可以大量获取所有 行到数组变量中

• 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

例如:

declare
  type tyTabNuber is table of number index by pls_integer;
  type ty...
  --
  vTabNumber tyTabNumber;
  ...
begin
  select ...
  BULK COLLECT INTO vTabNumber, ...
  FROM ...
  ...
end

这篇关于Oracle SQL:声明要在查询&中使用的变量子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！