내 남자의 길~!

WITH TEST AS
(
    select 'A' grade, 10 cnt from dual union all
    select 'A' grade, 20 cnt from dual union all
    select 'A' grade, 15 cnt from dual union all 
    select 'B' grade, 20  from dual union all
    select 'B' grade, 30  from dual union all
    select 'D' grade, 15  from dual union all
    select 'F' grade, 10  from dual
)
SELECT
    grade 등급,
    cnt 학생수,
    sum(cnt) over () 갯수,
    row_number() over(partition by grade order by cnt) 로넘,
    rank() over(partition by grade order by cnt) 랭크,
    round((cnt / sum(cnt) over ())*100,2) 구성비,
    sum(cnt) over (partition by grade  order by grade) 누적학생수,
    sum(cnt) over (partition by grade,cnt  order by grade) 그레카운,
    sum(cnt) over (order by grade) 누적,
    round((sum(cnt) over (order by grade)/sum(cnt) over ())*100,2) 누적구성비
FROM TEST;

/*

  rank() over(partition by grade order by cnt) 랭크,

  표현식 over (partition by 컬럼1 order by 컬럼2)

  이 의미는

  "(컬럼1값이 같은놈들로 묶은것을 컬럼2로 정렬한) 각 파티션별로 표현식을 수행해준다."

*/

복잡하군... :(

CREATE or REPLACE FUNCTION sum_emp //sum_emp 이름으로 함수 생성
(v_deptno IN emp.deptno%TYPE) //입력받는값 변수에 empTB에 deptno랑 같은 타입으로받겟다
//v_deptno 란 변수에 emp.deptno 와 같은 타입으로 선언
return number 변수를 넘버타입으로 리턴
IS v_sum_sal emp.sal;%TYPE; //변수와 데이타 타입

BEGIN
 SELECT SUM(sal) //SUM(sal)은 살레코드의의 값을 다 더하겟다
 INTO v_sum_sal
 FROM emp
 WHERE dept=v_deptno; //입력받은 부서번호와 일치한다면
 RETURN (v_sum_sal); //리턴하라
END;
/
excute example)SELECT sum_emp(20) FROM dual;

CREATE or REPLACE PROCEDURE 이름
(v_deptno IN emp.deptno%TYPE,
 v_sum_sal OUT emp.sal%TYPE) //IN:mode is IN in function, OUT:argument result is return
IS
BEGIN
 SELECT SUM(sal)
 INTO v_sum_sal   //
 FROM emp
 WHERE deptno = v_deptno;//입력받은 값과 같은때 그걸 가주와서 섬에서 다 더하고 v_sum_sal 이란 변수에 너케따
END;
/
프로시져 실행
VARIABLE x number //프로시저를 실행시켜서 값이 나오면 저장시킬 변수를 일단 선언
execute  프로시져네임(10, :x)
//10번이 파라미터값으로 들어가고 x 에 v_sum_sal 에 결과값이 들어간다
PRINT x // v_sum_sal 의 값이 x 에 저장되 있으니 x  를 출력하라

IF  조건  THEN
    문장;
ELSE
    문장;]
END IF;

LOOP
    문장1;
    문장2;
    ···
    EXIT[WHEN 조건];   --EXIT 문
END LOOP;

CREATE or REPLACE PROCEDURE loop_basic1
IS
 v_num number(2) := 1;
 v_sum varchar(10) := NULL;
BEGIN
 LOOP
  v_sum := v_sum||'*';
  DBMS_OUTPUT.PUT_LINE(v_sum);
  v_num := v_num+1;
  EXIT WHEN v_num > 10;
 END LOOP;
END loop_basic1;
/

FOR 인덱스 IN [REVERSE] 하한..상한 LOOP
    문장1;
    문장2;
    ···
END LOOP;

CREATE or REPLACE PROCEDURE for_basic1
IS
 v_char varchar2(10) := NULL;
BEGIN
 FOR I IN 1..10 LOOP
  v_char := v_char||'*';
  DBMS_OUTPUT.PUT_LINE(v_char);
 END LOOP;
END for_basic1;
/

WHILE  조건  LOOP
    문장1;
    문장2;
    ···
END LOOP;

CREATE or REPLACE PROCEDURE while_basic1
IS
 v_num number(2) := 1;
 v_char varchar2(10) := NULL;
BEGIN
 WHILE v_num <= 10 LOOP
  v_char := v_char||'*';
  DBMS_OUTPUT.PUT_LINE(v_char);
  v_num := v_num+1;
 END LOOP;
END while_basic1;
/

- 논리적인 테이블

- 물리적으로도 연결되어 있기 때문에 원본과 VIEW가

  서로 상호적으로 데이터 변경 시 각자에 반영된다.

- SELECT를 단순하게 하기 위해 WIEW씀

  (내가 정의해서 저장해놓고 쓰고 싶을 때마다 호출하여 쓸 수 있다.)

- 보안상으로 VIEW씀

// 뷰 생성

CREATE VIEW EMP_VIEW(EMPNO, ENAME, JOB) AS

SELECT EMPNO, ENAME, JOB FROM EMP WHERE JOB='SALESMAN';

// 뷰 생성 OR 수정

  -> 없는 뷰 이름을 써주면 뷰가 생성되고 있는 뷰 이름을 써주면 뷰가 수정된다.

CREATE OR REPLACE VIEW EMP_VIEW(EMPNO, ENAME, JOB) AS

SELECT EMPNO, ENAME, JOB FROM EMP WHERE JOB='SALESMAN';

컬럼명은 달라도 되지만 나중에 알아봐야 하기 때문에 같은 이름으로

주는것을 지향한다.

CREATE 해야 나중에 종료 후에도 쓸 수 있다.

// 그룹 뷰 생성 시 꼭 별명을 써야한다. 아래 에러

CREATE VIEW V1(DEPTNO, AVG(SAL), SUM(SAL)) AS
SELECT DEPTNO, AVG(SAL), SUM(SAL) FROM EMP GROUP BY DEPTNO;

// 그룹 뷰 생성 시  생성 방법

CREATE VIEW V1(DEPTNO, 평균, 합) AS
SELECT DEPTNO, AVG(SAL) AS 평균, SUM(SAL) 합 FROM EMP GROUP BY DEPTNO;

// 내가 만든 VIEW의 내용 조회

SELECT VIEW_NAME, TEXT FROM USER_VIEWS;

VIEW_NAME                                                    TEXT
-------------------          --------------------------------------------------------
EMP_VIEW                       SELECT EMPNO, ENAME, JOB FROM EMP

                                       WHERE JOB='SALESMAN'

V1                                   SELECT DEPTNO, AVG(SAL) AS 평균, SUM(SAL) 합

                                       FROM EMP GROUP BY DEPTNO

// 복합 VIEW 생성

CREATE VIEW EMPVI(EMPNO, ENAME, JOB, DEPTNO, DNAME) AS
SELECT E.EMPNO, E.ENAME, E.JOB, D.DEPTNO, D.DNAME FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND JOB='SALESMAN';