개발자인생

with emp  as (

select '7782' EMPNO, 'CLARK' ENAME  , 2450 SAL  ,'10' DEPTNO from dual union all

select '7839', 'KING'    , 5000   ,'10' from dual union all

select '7934', 'MILLER'  , 1300   ,'10' from dual union all

select '7566', 'JONES'   , 2975   ,'20' from dual union all

select '7902', 'FORD'    , 3000   ,'20' from dual union all

select '7876', 'ADAMS'   , 1100   ,'20' from dual union all

select '7369', 'SMITH'   , 800    ,'20' from dual union all

select '7788', 'SCOTT'   , 3000   ,'20' from dual union all

select '7521', 'WARD'    , 1250   ,'30' from dual union all

select '7844', 'TURNER'  , 1500   ,'30' from dual union all

select '7499', 'ALLEN'   , 1600   ,'30' from dual union all

select '7900', 'JAMES'   , 950    ,'30' from dual union all

select '7698', 'BLAKE'   , 2850   ,'30' from dual union all

select '7654', 'MARTIN'  , 1250   ,'30' from dual 

)

select empno, ename, sal,

        avg(sal) over( ) avg_sal

from emp;

결과

empno ename sal avg_sal

7782 CLARK 2450.00000 2073.21429

7839 KING         5000.00000 2073.21429

7934 MILLER 1300.00000 2073.21429

:

:

7900 JAMES 950.00000 2073.21429

7698 BLAKE 2850.00000 2073.21429

7654 MARTIN 1250.00000 2073.21429

select empno, ename, sal, deptno

        , avg(sal) over(PARTITION BY deptno) avg_sal

from emp ;

결과

7934 MILLER 1300.00000 10 2916.66667

7839 KING         5000.00000 10 2916.66667

7782 CLARK 2450.00000 10 2916.66667

7876 ADAMS 1100.00000 20 2175.00000

:

:

7900 JAMES 950.00000 30 1566.66667

7698 BLAKE 2850.00000 30 1566.66667

7654 MARTIN 1250.00000 30 1566.66667

select  *

from    (

        select empno, ename, sal, deptno

                , count(*) over(PARTITION BY null) cnt 

                , rank() over(order BY sal DESC) col_rank

        from emp

        )

where   col_rank = 1         --최고연봉자

or      ( cnt = col_rank )     --최저연봉자

결과

7369 SMITH 800.00000 20 14.00000 1.00000

7839 KING       5000.00000 10 14.00000 14.00000

        String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";    

        String user = "muse";

        String passwd = "muse";

        Connection conn;

        Statement stat; 

        ResultSet rs = null;

        int rowcount = 0;

        Class.forName("oracle.jdbc.driver.OracleDriver");

        conn = DriverManager.getConnection (url, user, passwd);

        stat = conn.prepareStatement("select * from tab1 where col1 = ?",

          ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE)

        stat.setString(1,"ddd");

  //이러한  방법으로 rowcount를 구한다..

rs.last();

rowcount = rs.getRow();

rs.beforeFirst();

crontab -l : 예약작업 리스트 보기

crontab -e: 예약작업 수정

* * * * *

#매주 월요일 새벽  5시 30분 /root/aaaa.sh 실행

30 5 * * 1 /root/aaaa.sh