help1

Oracle CURSOR

CREATE OR REPLACE PROCEDURE lolec11(usp IN VARCHAR2)
AS
jmeno VARCHAR2(50);
poc int;

CURSOR jmeno_list IS SELECT z.jmeno FROM Zakaznik z ORDER BY z.jmeno ASC;

CURSOR nakup_list IS SELECT COUNT(n.zID),z.jmeno FROM Zakaznik z, Nakup n WHERE z.zID = n.zID
GROUP BY z.jmeno ORDER BY COUNT(n.zID) DESC;

CURSOR rek_list IS SELECT COUNT(r.nID),z.jmeno FROM  Zakaznik z, Nakup n, Reklamace r WHERE
z.zID = n.zID AND r.nID = n.nID GROUP BY z.jmeno ORDER BY COUNT(r.nID) DESC;

BEGIN
  IF(usp = 'jmeno') THEN
   OPEN jmeno_list;
    LOOP
     FETCH jmeno_list INTO jmeno;
     EXIT WHEN jmeno_list%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(jmeno);
     END LOOP;
    CLOSE jmeno_list;
  END IF;

  IF(usp = 'nakup') THEN
   OPEN nakup_list;
    LOOP
     FETCH nakup_list INTO poc,jmeno;
     EXIT WHEN nakup_list%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(jmeno || ' ' || poc);
     END LOOP;
    CLOSE nakup_list;
  END IF;


   IF(usp = 'reklamace') THEN
   OPEN rek_list;
    LOOP
     FETCH rek_list INTO poc,jmeno;
     EXIT WHEN rek_list%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(jmeno || ' ' || poc);
     END LOOP;
    CLOSE rek_list;
  END IF;

END;



vzorac

ALTER PROCEDURE VypisZakazniku (@rok_od int = -1, @rok_do int = -1)
AS
DECLARE @muzi VARCHAR(20)
DECLARE @zeny VARCHAR(20)
SET @muzi = 0
SET @zeny = 0
BEGIN
IF @rok_od != -1 AND @rok_do != -1
BEGIN
 SELECT @muzi = SUM(n.cena)
    FROM test.Nakup n
    left join test.Zakaznik z ON n.zID = z.zID
    WHERE z.pohlavi = 'muz' AND YEAR(n.den) BETWEEN @rok_od AND @rok_do

 SELECT @zeny = SUM(n.cena)
    FROM test.Nakup n
    left join test.Zakaznik z ON n.zID = z.zID
    WHERE z.pohlavi = 'zena' AND YEAR(n.den) BETWEEN @rok_od AND @rok_do

print 'Muzi mezi roky ' + CAST(@rok_od AS VARCHAR) + ' a ' + CAST(@rok_do AS VARCHAR) + ' nakoupili za celkovou castku '
        + @muzi + ' Kc. Zeny mezi roky ' + CAST(@rok_od AS VARCHAR) + ' a ' + CAST(@rok_do AS VARCHAR) + ' nakoupily za celkovou castku ' + @zeny + ' Kc.'
END

IF @rok_od = -1 AND @rok_do != -1
BEGIN
 SELECT @muzi = SUM(n.cena)
    FROM test.Nakup n
    left join test.Zakaznik z ON n.zID = z.zID
    WHERE z.pohlavi = 'muz' AND YEAR(n.den) <= @rok_do

 SELECT @zeny = SUM(n.cena)
    FROM test.Nakup n
    left join test.Zakaznik z ON n.zID = z.zID
    WHERE z.pohlavi = 'zena' AND YEAR(n.den) <= @rok_do

    print 'Muzi do roku ' + CAST(@rok_do AS VARCHAR) + ' nakoupili za celkovou castku '
        + @muzi + ' Kc. Zeny do roku ' + CAST(@rok_do AS VARCHAR) + ' nakoupily za celkovou castku ' + @zeny + ' Kc.'
END

IF @rok_od != -1 AND @rok_do = -1
BEGIN
 SELECT @muzi = SUM(n.cena)
    FROM test.Nakup n
    left join test.Zakaznik z ON n.zID = z.zID
    WHERE z.pohlavi = 'muz' AND YEAR(n.den) >= @rok_od

 SELECT @zeny = SUM(n.cena)
    FROM test.Nakup n
    left join test.Zakaznik z ON n.zID = z.zID
    WHERE z.pohlavi = 'zena' AND YEAR(n.den) >= @rok_od

    print 'Muzi od roku ' + CAST(@rok_od AS VARCHAR) + ' nakoupili za celkovou castku '
        + @muzi + ' Kc. Zeny od roku ' + CAST(@rok_od AS VARCHAR) + ' nakoupily za celkovou castku ' + @zeny + ' Kc.'
END

IF @rok_od = -1 AND @rok_do = -1
BEGIN
 SELECT @muzi = SUM(n.cena)
    FROM test.Nakup n
    left join test.Zakaznik z ON n.zID = z.zID
    WHERE z.pohlavi = 'muz'

 SELECT @zeny = SUM(n.cena)
    FROM test.Nakup n
    left join test.Zakaznik z ON n.zID = z.zID
    WHERE z.pohlavi = 'zena'

print 'Muzi nakoupili za celkovou castku ' + @muzi + ' Kc. Zeny nakoupily za celkovou castku ' + @zeny + ' Kc.'
END

END



EXECUTE VypisZakazniku 2013




vzorac v oracle

CREATE OR REPLACE PROCEDURE VypisZakazniku(rok_od INT := -1, rok_do INT := -1)
AS
muz NUMBER;
zena NUMBER;

BEGIN

IF (rok_od != -1 AND rok_do != -1) THEN
  SELECT SUM(n.cena) INTO muz FROM NAKUP n,ZAKAZNIK z WHERE n.zid = z.zid
  AND z.pohlavi = 'muz'
  AND  EXTRACT(YEAR FROM n.den) BETWEEN rok_od AND rok_do;

  SELECT SUM(n.cena) INTO zena FROM NAKUP n, ZAKAZNIK z WHERE n.zid = z.zid
  AND z.pohlavi = 'zena'
  AND  EXTRACT(YEAR FROM n.den) BETWEEN rok_od AND rok_do;

  DBMS_OUTPUT.PUT_LINE('Muzi mezi roky ' || rok_od || ' a ' || rok_do || ' nakoupili za celkovou castku ' || muz || ' Kc. Zeny mezi roky ' || rok_od || ' a ' || rok_do || ' nakoupily za celkovou castku ' || zena || ' Kc.');
END IF;


IF (rok_od != -1 AND rok_do = -1) THEN
  SELECT SUM(n.cena) INTO muz FROM NAKUP n, ZAKAZNIK z WHERE n.zid = z.zid
  AND z.pohlavi = 'muz'
  AND EXTRACT(YEAR FROM n.den) > rok_od;

   SELECT SUM(n.cena) INTO zena FROM NAKUP n, ZAKAZNIK z WHERE n.zid = z.zid
  AND z.pohlavi = 'zena'
  AND EXTRACT(YEAR FROM n.den) > rok_od;

 DBMS_OUTPUT.PUT_LINE('Muzi od roku ' || rok_od ||  ' nakoupili za celkovou castku ' || muz || ' Kc. Zeny od roku ' || rok_od || ' nakoupily za celkovou castku ' || zena || ' Kc.');

END IF;

IF (rok_od = -1 AND rok_do != -1) THEN
  SELECT SUM(n.cena) INTO muz FROM NAKUP n, ZAKAZNIK z WHERE n.zid = z.zid
  AND z.pohlavi = 'muz'
  AND EXTRACT(YEAR FROM n.den) < rok_do;

  SELECT SUM(n.cena) INTO zena FROM NAKUP n, ZAKAZNIK z WHERE n.zid = z.zid
  AND z.pohlavi = 'zena'
  AND EXTRACT(YEAR FROM n.den) < rok_do;

  DBMS_OUTPUT.PUT_LINE('Muzi do roku ' || rok_do ||  ' nakoupili za celkovou castku ' || muz || ' Kc. Zeny do roku ' || rok_do || ' nakoupily za celkovou castku ' || zena || ' Kc.');

END IF;

IF (rok_od = -1 AND rok_do = -1) THEN
  SELECT SUM(n.cena) INTO muz FROM NAKUP n, ZAKAZNIK z WHERE n.zid = z.zid
  AND z.pohlavi = 'muz';

  SELECT SUM(n.cena) INTO zena FROM NAKUP n, ZAKAZNIK z WHERE n.zid = z.zid
  AND z.pohlavi = 'zena';

  DBMS_OUTPUT.PUT_LINE('Muzi nakoupili za celkovou castku ' || muz || ' Kc. Zeny nakoupily za celkovou castku ' || zena || ' Kc.');

END IF;

END;