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;
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.