Advertisement
VoronVU

Построчная выдача отчета в файл

Apr 2nd, 2015
1,239
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --Построчная выдача отчета в файл
  2. CREATE OR REPLACE
  3. PROCEDURE report_sal_adjustment4 IS
  4.   f utl_file.file_type;
  5. BEGIN
  6.   f := utl_file.fopen ('REPORT_DIR', 'report.dat', 'W') ;
  7.   FOR i IN (
  8.    SELECT e.empno, e.hiredate, e.sal, dept.dname,
  9.    CASE WHEN sal > avg_sal THEN 'Y' ELSE 'N'
  10.    END status
  11.   FROM (
  12.    SELECT empno, hiredate, sal, deptno,
  13.           AVG(sal) over ( PARTITION BY deptno ) AS avg_sal,
  14.           MIN(sal) over ( PARTITION BY deptno ) AS min_sal
  15.    FROM emp ) e, dept
  16.   WHERE e.deptno = dept.deptno
  17.     AND ABS(e.sal - e.avg_sal)/e.avg_sal > 0.10 )
  18. LOOP
  19.   utl_file.put_line(f, i.empnol || i.hiredate || i.sal || i.dname || i.status);
  20. END LOOP;
  21.   utl_file.fclose(f);
  22. END;
Advertisement
RAW Paste Data Copied
Advertisement