CV7_DOMACI

CREATE TABLE pomocna
(table_name VARCHAR2(255),
pocetRadku NUMBER);

CREATE OR REPLACE PROCEDURE CV8_POCET_RADKU(p_schema VARCHAR2, p_razeni IN VARCHAR2 DEFAULT 'ASC') AS
  muj_cursor sys_refcursor;
  i_pocet_radku NUMBER;
  i_nazev_tabulky VARCHAR2(30);
BEGIN
  DELETE FROM pomocna;
  FOR X IN (SELECT table_name FROM all_tables WHERE LOWER(owner) = LOWER(p_schema))
    LOOP
      EXECUTE IMMEDIATE 'Select count(*) from ' || p_schema || '.' || x.table_name INTO i_pocet_radku;
      INSERT INTO pomocna VALUES(x.table_name, i_pocet_radku);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Tabulky ze schematu' || p_schema || ';');
  OPEN muj_cursor FOR 'Select table_name, pocetRadku  from pomocna order by pocetRadku  ' || p_razeni;
    LOOP
      FETCH muj_cursor INTO i_nazev_tabulky, i_pocet_radku;
      EXIT WHEN muj_cursor%notFound;
      DBMS_OUTPUT.PUT_LINE( i_nazev_tabulky ||' - ' || i_pocet_radku || ' radku');
    END LOOP;
    CLOSE muj_cursor;
END CV8_POCET_RADKU;

CREATE OR REPLACE  VIEW CV8_PRODUCTS_VS_REGIONS
("PROD_NAME", "OCEANIA_CENA", "MIDDLEEAST_CENA", "EUROPE_CENA", "AFRICA_CENA", "ASIA_CENA", "AMERICAS_CENA")
AS
SELECT "PROD_NAME","OCEANIA_CENA","MIDDLEEAST_CENA","EUROPE_CENA","AFRICA_CENA","ASIA_CENA","AMERICAS_CENA"
FROM(
SELECT prod_name, amount_sold, country_region FROM sh.products
join sh.sales using(prod_id)
join sh.times using(time_id)
join sh.customers using(cust_id)
join sh.countries using(country_id)
join sh.channels using(channel_id)
WHERE calendar_year = 2001 AND channel_desc LIKE 'Direct Sales'
ORDER BY prod_name
)
Pivot
(SUM(amount_sold) AS cena FOR country_region
IN
(
'Oceania' AS Oceania,
'Middle East' AS MiddleEast,
'Europe' AS Europe,
'Africa' AS Africa,
'Asia' AS Asia,
'Americas' AS Americas));

CREATE OR REPLACE VIEW CV8_CREDIT_LIMIT_STATS
("MIN_LOW_CREDIT", "AVG_LOW_CREDIT", "MAX_LOW_CREDIT", "MIN_MAX_CREDIT", "AVG_MAX_CREDIT", "MAX_MAX_CREDIT")
  AS
  SELECT
  ROUND(AVG(sold) keep (DENSE_RANK LAST ORDER BY cust_credit_limit ), 2) AS avg_max_credit,
  ROUND(MAX(sold) keep (DENSE_RANK LAST ORDER BY cust_credit_limit ), 2) AS max_max_credit,
  ROUND(MIN(sold) keep (DENSE_RANK FIRST ORDER BY cust_credit_limit ), 2) AS min_low_credit,
  ROUND(AVG(sold) keep (DENSE_RANK FIRST ORDER BY cust_credit_limit ), 2) AS avg_low_credit,
  ROUND(MAX(sold) keep (DENSE_RANK FIRST ORDER BY cust_credit_limit ), 2) AS max_low_credit,
  ROUND(MIN(sold) keep (DENSE_RANK LAST ORDER BY cust_credit_limit ), 2) AS min_max_credit
FROM(
SELECT
cust_last_name, SUM(AMOUNT_SOLD)
AS
sold, cust_credit_limit
FROM sh.products
join sh.sales using(prod_id)
join sh.times using(time_id)
join sh.customers using(cust_id)
join sh.countries using(country_id)
join sh.channels using(channel_id)
WHERE calendar_year BETWEEN 2000 AND 2002
GROUP BY cust_last_name,
cust_credit_limit
ORDER BY cust_credit_limit ASC);