help2

--------------------------------------------------------
--  File created - Úterý-března-21-2017
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Procedure LOL
--------------------------------------------------------
set define off;

  CREATE OR REPLACE EDITIONABLE PROCEDURE "NAV0140"."LOL" (name IN varchar2 ,rok_od IN int,rok_do IN int)
AS
den varchar2(10);
cena int;

cursor list is
select dny,cena from
(
select dny, nvl(cena,0) as cena
  from
  (
  SELECT  distinct to_char(nn.den,'DY') as dny from nakup nn
  )y
  left JOIN
  (
  SELECT den,sum(cena) as cena
  from
  (
  SELECT to_char(n.den,'DY') as den,n.CENA FROM nakup n
  join zakaznik z on z.zID=n.zID
  where z.jmeno=name  and to_char(n.den,'YYYY') between rok_od and rok_do
  )x
  group by den
  )z
  ON y.dny=z.den
  )pp
  order by (CASE WHEN pp.dny='PO' then 1
                       WHEN pp.dny='ÚT' then 2
                       WHEN pp.dny='ST' then 3
                       WHEN pp.dny='ČT' then 4
                       WHEN pp.dny='PÁ' then 5
                       WHEN pp.dny='SO' then 6
                       WHEN pp.dny='NE' then 7 end);


 BEGIN
    dbms_output.put_line('Zakaznik: ' || name);
   OPEN list;
   LOOP
   FETCH list into den,cena;
      EXIT WHEN list%notfound;
      dbms_output.put_line(den || ' :' || cena);
   END LOOP;
   CLOSE list;
END;

/



set define off;

  CREATE OR REPLACE EDITIONABLE PROCEDURE "NAV0140"."LOL2" (rok_od IN int,rok_do IN int)
as
v_rok int;
v_den varchar2(10);
v_cena int;

cursor list is
  select * from
  (
  SELECT  to_char(den,'YYYY')as rok, to_char(den,'DY') as den,SUM(cena) AS CENA FROM nakup n
  GROUP BY to_char(den,'YYYY'), to_char(den,'DY'))x
  WHERE cena in(
  SELECT MAX(cena) from
  (SELECT  to_char(den,'YYYY')as rok, to_char(den,'DY') as den,SUM(cena) AS CENA FROM nakup n
  GROUP BY to_char(den,'YYYY'), to_char(den,'DY'))x
  group by rok
  )
  and rok between rok_od and rok_do
  order by rok;

   BEGIN
   OPEN list;
   LOOP
   FETCH list into v_rok,v_den,v_cena;
      EXIT WHEN list%notfound;
      dbms_output.put_line(v_rok||': '||v_den||': '|| v_cena);
   END LOOP;
   CLOSE list;
END;






set define off;

  CREATE OR REPLACE EDITIONABLE PROCEDURE "NAV0140"."LOL3" (rok_od IN int,rok_do IN int:=2015)
as
v_pohlavi varchar2(4);
v_cena int(10);
v_rokmax int := 2018;
v_rokmin int :=1900;

cursor list is
SELECT z.pohlavi,sum(n.cena)as cena FROM nakup n
join zakaznik z on z.zID=n.zID
where to_char(den,'YYYY') between rok_od and rok_do
group by pohlavi;

   BEGIN
   OPEN list;
   LOOP
   FETCH list into v_pohlavi,v_cena;
      EXIT WHEN list%notfound;

  if rok_od>-1 and rok_do>-1 then
    if v_pohlavi='muz' then dbms_output.put_line('Muzi' ||' mezi roky '||rok_od ||' a '||rok_do||' nakoupili za: ' ||v_cena);
    end if;
    if v_pohlavi='zena' then dbms_output.put_line('Zeny' ||' mezi roky '||rok_od ||' a '||rok_do||' nakoupili za: ' ||v_cena);
    end if;
  end if;

  if rok_od=-1 and rok_do>-1 then
    if v_pohlavi='muz' then dbms_output.put_line('Muzi' ||' do roku '||rok_do||' nakoupili za: ' ||v_cena);
    end if;
    if v_pohlavi='zena' then dbms_output.put_line('Zeny' ||' do roku '||rok_do||' nakoupily za: ' ||v_cena);
    end if;
  end if;

  if rok_od>-1 and rok_do=-1 then
      if v_pohlavi='muz' then dbms_output.put_line('Muzi' ||' od roku '||rok_od||' nakoupili za: ' ||v_cena);
    end if;
    if v_pohlavi='zena' then dbms_output.put_line('Zeny' ||' od roku '||rok_od||' nakoupily za: ' ||v_cena);
    end if;
  end if;

   END LOOP;
   CLOSE list;
END;