oracle where子句中的变量值 [英] Variable values in oracle where clause

问题描述

I am trying to pass an variable value in my Select statement where clause

DATE_DIM table is like below

UNIQUE_ID   WEEK_NAME   WEEK_BEGIN  WEEK_END
1   Week 6, 2018    20180205    20180211
2   Week 5, 2018    20180129    20180204
3   Week 4, 2018    20180122    20180128
4   Week 3, 2018    20180115    20180121

And Fact Table is like

ACCT_ID WEEK_NAME   SALES
10001   20180205    10
10001   20180206    20
10001   20180207    10
10001   20180208    15
1002    20180129    100
1002    20180130    100
1002    20180131    100
1002    20180132    100

Here I need an Output like

ACCT_ID WEEK_NAME   SALES
10001   Week 6, 2018    55
1002    Week 5, 2018    406

So Basically I wanna loop each ACCT_ID like

SELECT *
FROM Fact_Table
where Week_Name between (Select Week_Begin from DATE_DIM WHERE WEEk_NAME =: v_Wknm) /* Here I will have to loop Each Week_name for Each Acct_ID */

我尝试过：



这就是我的尝试

What I have tried:

This is what I have tried

DECLARE v_Init_Cnt NUMBER(10):=1;
        v_Tot_Cnt NUMBER(10);

BEGIN

    SELECT COUNT(*) INTO v_Tot_Cnt FROM DIM_DATE;

    WHILE(v_Init_Cnt <= v_Tot_Cnt)
    LOOP

    dbms_output.put_line(v_Init_Cnt);

    VARIABLE v_Id NUMBER(10);
    exec :v_Id:= v_Init_Cnt;

    dbms_output.put_line('var' || v_Id);

    SELECT * FROM FACT_TABLE 
    WHERE WEEK_NAME between (select WEEk_BEGIN FROM DIM_DATE WHERE UNIQUE_ID :=v_Init_Cnt) and (select WEEk_END FROM DIM_DATE WHERE UNIQUE_ID :=v_Init_Cnt) 

    v_Init_Cnt :=  v_Init_Cnt +1;
    END LOOP;

END;  

我使用TOAD，在变量exec上它会提示我一个窗口？如何避免这种情况？

I am using TOAD, on variable exec it prompts me up an window?? How will avoid that?

推荐答案

我在CHill60的解决方案中提出了第二个想法1.为什么要让数据库为您完成工作而循环。



但是，我想补充几点：



在PL / SQL中你不需要使用分号来自变量。通常在使用主机变量时使用该语法。当您引用在PL / SQL中声明的变量时，您可以使用原样。请考虑以下内容

I second the idea in CHill60's solution 1. Why loop when you can let the database do the work for you.

However, I'd like to add few points:

In PL/SQL you don't need to use semicolon in from of variable. That syntax is typically used when using a host variable. When you're referring to variables that are declared inside PL/SQL you can use the as-is. Consider the following

DECLARE 
   vCriteria NUMBER;
   vResult   NUMBER;
BEGIN
   vCriteria := 5;

   SELECT COUNT(*)
   INTO vResult
   FROM SomeTable
   WHERE SomeColumn <= vCriteria;

   DBMS_OUTPUT.PUT_LINE('Result ' || vResult);
END;

另一件事是 WEEK_NAME 和附表。周名称看起来像以YYYYMMDD格式存储在字符串中的日期。如果这是正确的，我建议使用oracle date 而不是字符串。



如果事实表包含日期然后您可以轻松地从日期值中提取周数，周期，季度等。例如 TO_CHAR（datecolumn，'IW'）返回给定日期的ISO周数。日期数据类型owuld为您提供了更多的灵活性，据我所知，它将消除对定义一周开始的单独表的需要等等。

Another thing is the WEEK_NAME and the accompanying table. The week names look like dates stored in a string in YYYYMMDD format. If that is correct, I would suggest using oracle date instead of string.

If the fact table would contain dates then you could easily extract the week number, monhth, quarter etc from the date value. For example TO_CHAR(datecolumn, 'IW') returns the ISO week number for the given date. Date data type owuld give you a lot more flexibility and as far as I can see, it would eliminate the need for separate tables that define start of the week and so on.

我是害怕我无法访问Oracle所以这个解决方案使用基本的T-SQL语法。



你所要做的就是根据week_name加入两个表信息，然后按帐户和week_name分组...这工作

I'm afraid I haven't got access to Oracle so this solution is using basic T-SQL syntax.

All you have to do is join the two tables based on the week_name information and then group by the account and week_name ... this works

select A.ACCTID, B.WEEK_NAME, SUM(SALES)
from Fact A
inner join DATE_DIM B ON A.WEEK_NAME >= B.WEEK_BEGIN AND A.WEEK_NAME <= B.WEEK_END
group by A.ACCTID, B.WEEK_NAME

您很少需要在RDBMS中使用循环 - 我写了一篇文章给出了替代方案在SQL Server中处理循环 [ ^ ] - 虽然它以SQL Server为目标这些原则也适用于Oracle（以及使用T-SQL的MySQL和任何其他RDBMS）。



另外，还有一些函数可以给出周数（例如请参阅询问汤姆 - 一年中的一周 [ ^ ]所以你可以将日期存储在 Fact 表（作为日期类型）并计算周数，以避免必须拥有 DATE_DIM table

It's a rare occurrence that you would need to use a loop in an RDBMS - I wrote an article giving alternatives Processing Loops in SQL Server[^] - although it targets SQL Server the principles also apply to Oracle (and MySQL and any other RDBMS that uses T-SQL).

As another aside, there are functions that give week numbers (e.g. see Ask Tom - week of the year[^] so you could just store the date on the Fact table (as a Date type) and calculate the week number from that to avoid having to have the DATE_DIM table

这篇关于oracle where子句中的变量值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！