CV_8_spolecne

CREATE PACKAGE CV9_DATUMY AS
   FUNCTION datumy_rozdil(datum_od DATE, datum_do DATE, typ CHAR) RETURN NUMBER;
   FUNCTION pracovni_den(datum DATE) RETURN NUMBER;
   FUNCTION den_v_mesici(datum DATE, prvni NUMBER) RETURN DATE;
END CV9_DATUMY;


/*
*/

CREATE OR REPLACE PACKAGE BODY CV9_DATUMY AS
/*
function datumy_rozdil(datum_od DATE, datum_do DATE, typ CHAR)
return number as
v_difference NUMBER;
begin
if
datum_od IS NOT NULL
and datum_do is not null then
SELECT round(to_date(datum_od)-to_date(datum_do),2) into v_difference FROM DUAL;
else
dbms_output.put_line('Error 20404, Spatne parametry!');
end if;
exception
WHEN datum_od is NULL
and datum_do is NULL  THEN
      dbms_output.put_line('Error 20404, Spatne parametry!');
end;
return v_difference;
end CV9_DATUMY;
*/

FUNCTION datumy_rozdil(datum_od DATE, datum_do DATE, typ CHAR)
RETURN NUMBER AS
v_difference NUMBER;
BEGIN
IF
datum_od IS NOT NULL
AND datum_do IS NOT NULL
AND typ IN ('D','H','M','S') THEN
v_difference:= datum_do-datum_od;
IF typ = 'D' THEN
RETURN ROUND(v_difference,2);
ELSIF typ = 'H' THEN
RETURN ROUND(v_difference*24,2);
ELSIF typ = 'M' THEN
RETURN ROUND(v_difference*24*60,2);
ELSE RETURN ROUND(v_difference*24*60*60,2);
END IF;
ELSE
raise_application_error(-20000,'Spatne parametry!');
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END datumy_rozdil;

FUNCTION pracovni_den(datum DATE) RETURN NUMBER AS
v_day_number NUMBER;
BEGIN
v_day_number := TO_NUMBER (TO_CHAR(datum,'D'));
IF V_DAY_NUMBER BETWEEN 1 AND 5 THEN
RETURN 1;
ELSE RETURN 0;
END IF;
END pracovni_den;

FUNCTION den_v_mesici(datum DATE, prvni NUMBER) RETURN DATE
 AS
BEGIN
IF prvni = 1 THEN
RETURN TRUNC(datum,'M');
ELSIF prvni = 0 THEN
RETURN LAST_DAY(datum);
ELSE RETURN NULL;
RETURN NULL;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END den_v_mesici;

END CV9_DATUMY;

CREATE OR REPLACE
PROCEDURE cv9_prodluz_varchar
(P_TABULKA IN VARCHAR2, P_O_KOLIK IN NUMBER) AS
i_sloupce VARCHAR2(1500);
i_prikaz VARCHAR2(1500);
i_pocet NUMBER(3,0) := 0;
BEGIN
FOR x IN (SELECT column_name, data_length FROM user_tab_columns WHERE LOWER(table_name) = LOWER(p_tabulka) AND data_type = 'VARCHAR2')
LOOP
  i_sloupce := i_sloupce || x.column_name || ' ' || 'Varchar2(' || TO_CHAR(x.data_length + p_o_kolik) || '),';
  i_pocet := i_pocet + 1;
  END LOOP;
IF i_pocet > 0 THEN
  i_prikaz := 'ALTER TABLE ' || p_tabulka || ' modify (' || RTRIM(i_sloupce, ',') || ')';
  DBMS_OUTPUT.put_line(i_prikaz);
  EXECUTE IMMEDIATE i_prikaz;
END IF;
  DBMS_OUTPUT.put_line('Modifikovano ' || i_pocet || ' sloupců tabulky ' || p_tabulka);
EXCEPTION
  WHEN OTHERS THEN
  DBMS_OUTPUT.put_line(SQLERRM);
END cv9_prodluz_varchar;