210414

210414

워크시트 - 왼쪽 바에 마우스 오른쪽 버튼 - 행번호 토글 -> 행 번호 나옴

출력 안되면 SET SERVEROUTPUT ON 쓰고 실행

---

 4) INTO : SELECT로 조회된 컬럼값을 변수에 저장
   예)
SET SERVEROUTPUT ON
DECLARE
	NUM NUMBER(5);
	NAME VARCHAR2(20);
BEGIN
	SELECT EMPNO, ENAME INTO NUM, NAME
	FROM EMP WHERE EMPNO=7369;
	DBMS_OUTPUT.PUT_LINE(NUM||' '||NAME);
END;
   예1) 사원번호가 7369인 사원의 보너스 구하기
    부서번호가 10번이면 급여의 10%
    부서번호가 20번이면 급여의 20%
    부서번호가 30번이면 급여의 30%

DECLARE
	VEMPNO NUMBER(4); -- 사원번호
	VDEPTNO NUMBER(3); -- 부서번호
	VSAL NUMBER(7,2); -- 급여
	BONUS NUMBER(7,2); -- 보너스
BEGIN
	SELECT EMPNO, DEPTNO, SAL INTO VEMPNO, VDEPTNO, VSAL
	FROM EMP
	WHERE EMPNO=7369;
	IF VDEPTNO=10 THEN
		BONUS:=VSAL*0.1;
	ELSIF VDEPTNO=20 THEN
		BONUS:=VSAL*0.2;
	ELSIF VDEPTNO=30 THEN
		BONUS:=VSAL*0.3;
	ELSE
		BONUS:=0;
	END IF;
	DBMS_OUTPUT.PUT_LINE(VEMPNO||' '||VDEPTNO||' '||VSAL);
	DBMS_OUTPUT.PUT_LINE('보너스:'||BONUS);
END;
/

  Q1) 7369사원의 급여가 800이상이면 급여의 50%,2000이상이면 급여의 30%,
      3000이상이면 급여의 10%를 인상하세요.
DECLARE
	VSAL NUMBER(7,2);
	USAL NUMBER(7,2);
BEGIN
	SELECT SAL INTO VSAL
	FROM EMP WHERE EMPNO=7369;
	IF VSAL>=3000 THEN
		USAL:=VSAL*1.1;
	ELSIF VSAL>=2000 THEN
		USAL:=VSAL*1.3;
	ELSIF VSAL>=800 THEN
		USAL:=VSAL*1.5;
	ELSE
		USAL:=VSAL;
	END IF;
	UPDATE EMP SET SAL=USAL WHERE EMPNO=7369;
	COMMIT;
	DBMS_OUTPUT.PUT_LINE('7369 사원의 급여가'||VSAL||'->'||USAL||'로 변경됨');
END;
/

  5) BASIC LOOP
   형식1)
         LOOP
	반복문;
	EXIT WHEN 루프를 빠져나갈 조건절;
END LOOP;

예1)
DECLARE
	TOT NUMBER(5):=0; --합 저장
	I NUMBER(5):=0;
BEGIN
	LOOP
		I:=I+1;
		TOT:=TOT+I;
		EXIT WHEN I>=100;
	END LOOP;
	DBMS_OUTPUT.PUT_LINE('1부터 100까지 합:'||TOT);
END;
/

   형식2)
   WHILE 조건식 LOOP
	반복실행할 구문;
	..
   END LOOP;

예)
DECLARE
	TOT NUMBER(5):=0;
	I NUMBER(5):=1;
BEGIN
	WHILE I<=100 LOOP
		TOT:=TOT+I;
		I:=I+1;
	END LOOP;
	DBMS_OUTPUT.PUT_LINE('1부터 100까지 합:'||TOT);
END;
/

  Q1) 단 입력받아 구구단 출력해 보세요.(WHILE문)
DECLARE
	DAN NUMBER(3):=&DAN;
	I NUMBER(3):=1;
BEGIN
	WHILE I<=9 LOOP
		DBMS_OUTPUT.PUTLINE(DAN||'*'||I||'='DAN*I);
		I:=I+1;
	END LOOP;
END;
/

[3] 저장프로시져(Stored Procedure)

 1) 특정한 처리를 수행하는 PL/SQL 서브프로그램. 파라미터를 받아서 특정 작업을 수행
 2) 형식
 CREATE OR REPLACE PROCEDURE 프로시져명(파라미터,...)
 IS
 	변수선언;
 BEGIN
 	실행구문;
 END;
 /
 3) 프로시져 호출
   형식)
   EXECUTE 프로시져명(매개변수값,..);

 4) 프로시져 삭제
   형식)
   DROP PROCEDURE 프로시져명;
 
 예1) 부서번호,부서명,위치를 파라미터로 전달받아 DEPT테이블에 추가하는 프로시져
CREATE OR REPLACE PROCEDURE ADDEPT
(
	VDEPTNO NUMBER,
	VDNAME VARCHAR2,
	VLOC VARCHAR2
)
IS
BEGIN
	INSERT INTO DEPT VALUES(VDEPTNO, VDNAME, VLOC);
	COMMIT;
	DBMS_OUTPUT.PUT_LINE(VDEPTNO||' 부서 등록완료!');
END;
/

EXECUTE ADDEPT(70, '인사부', '종로'); -- 프로시져 호출

 예2) 사원번호를 파라미터로 전달받아 이름과 직업을 출력하는 프로시져
CREATE OR REPLACE PROCEDURE SHOWJOB (VEMPNO NUMBER)
IS
	VENAME VARCHAR2(20);
	VJOB VARCHAR2(20);
BEGIN
	SELECT ENAME, JOB INTO VENAME, VJOB
	FROM EMP
	WHERE EMPNO=VEMPNO;
	DBMS_OUTPUT.PUT_LINE('이름:'||VENAME||'직업'||VJOB);
END;
/

EXECUTE SHOWJOB(7369);

 Q1) 사원번호와 급여인상률을 파라미터로 전달받아 급여를 인상하는 프로시져를
  만들고 호출해 보세요.
  예) EXECUTE INCREASE(7369,10);

CREATE OR REPLACE PROCEDURE INCREASE (VEMPNO NUMBER, RATE NUMBER)
IS
	VSAL NUMBER(7,2);
	USAL NUMBER(7,2);
BEGIN
	SELECT SAL INTO VSAL FROM EMP WHERE EMPNO=VEMPNO;
	USAL:=VSAL+VSAL*(RATE/100);
	UPDATE EMP SET SAL=USAL WHERE EMPNO=VEMPNO;
	COMMIT;
	DBMS_OUTPUT.PUT_LINE(VEMPNO||'사원의 급여가'||VSAL||'에서 '||USAL||'로 인상됨');
END;
/

 Q2) 사원번호를 파라미터로 전달받아 사원의 급여가 800이상이면 급여의 50%
  급여가 2000이상이면 급여의 30%,3000이상이면 급여의 10%를 인상하세요.
  화면에 XXX사원의 급여가 XXX로 인상되었습니다. 라고 출력되도록 하세요. (IF문 써야할듯)
  예) EXECUTE UPDATESAL(7369);

CREATE OR REPLACE PROCEDURE UPDATESAL (VEMPNO NUMBER)
IS
	VSAL EMP.SAL%TYPE; -- EMP테이블의 SAL 컬럼 타입을 적용 (NUMBER(7,2)은 EMP.SAL%TYPE와 같음)
	USAL EMP.SAL%TYPE; -- (NUMBER(7,2)은 EMP.SAL%TYPE와 같음)
BEGIN
	SELECT SAL INTO VSAL FROM EMP WHERE EMPNO=VEMPNO;
	IF VSAL>=3000 THEN
		USAL:=VSAL*1.1;
	ELSIF VSAL>=2000 THEN
		USAL:=VSAL*1.3;
	ELSIF VSAL>=800 THEN
		USAL:=VSAL*1.5;
	ELSE
		USAL:=VSAL;
	END IF;
	UPDATE EMP SET SAL=USAL WHERE EMPNO=VEMPNO;
	COMMIT;
	DBMS_OUTPUT.PUT_LINE(VEMPNO||'사원의 급여가 '||VSAL||'에서 '||USAL||'로 인상됨');
END;
/

 5) IN/OUT 매개변수
   - IN매개변수는 프로시져 내부에서만 사용되는 매개변수이며 OUT매개변수는 외부의 변수를 참조해서
    가져온다. 
CREATE OR REPLACE PROCEDURE ADD1 
(
	N1 IN NUMBER,
	N2 IN NUMBER,
	N3 OUT NUMBER
)
IS
BEGIN
	N3:=N1+N2;
	DBMS_OUTPUT.PUT_LINE('두 수 합:'||N3);
END;
/
VAR N3 NUMBER; -- 전역변수 선언 (VAR은 변수선언 할 때 키워드)
EXECUTE ADD1(1,2,:N3);

예)
DECLARE
	N NUMBER(4);
BEGIN
	ADD1(10,20,N);
	DBMS_OUTPUT.PUT_LINE('N===>'||N);
END;
/

[4] 사용자정의 함수(Stored Function)
- 사용자가 만든 함수이며 어떤 연산을 수행한 뒤 결과값을 반환한다.
- 형식)
CREATE OR REPLACE FUNCTION 함수명(파라미터,..)
RETURN 리턴형
IS
변수선언;
BEGIN
실행문장;
..
RETURN 값;
END;
/

 예1) 입력받은 값으로부터 10%의 세율을 구하는 함수
CREATE OR REPLACE FUNCTION TAX(N NUMBER)
RETURN NUMBER
IS
	VAL NUMBER(10,3);
BEGIN
	VAL:=N*0.1;
	RETURN VAL;
END;
/

SELECT TAX(1000) FROM DUAL;

 Q1) 파라미터로 정수를 전달받아 그 수만큼 1부터 더해진 값을 리턴하는 함수를
 만들고 사용해 보세요.
CREATE OR REPLACE FUNCTION ADD2(NUM NUMBER)
RETURN NUMBER
IS
	TOT NUMBER(7):=0;
BEGIN
	FOR I IN 1..NUM LOOP
		TOT:=TOT+I;
	END LOOP;
	RETURN TOT;
END;
/

 Q2) 두 수를 파라미터로 전달받아 두 수중 큰값을 리턴하는 함수를 만들고 사용해
 보세요.
CREATE OR REPLACE FUNCTION MAXNUM(NUM1 NUMBER, NUM2 NUMBER)
RETURN NUMBER
IS
BEGIN
	IF NUM1>NUM2 THEN
	RETURN NUM1;
	ELSE
	RETURN NUM2;
	END IF;
END;
/

 Q3) 파라미터로 사원번호를 전달받아 근무하는 부서명을 반환하는 함수를 만들고 
사용해 보세요.
CREATE OR REPLACE FUNCTION WDNAME(VEMPNO EMP.EMPNO%TYPE)
RETURN DEPT.DNAME%TYPE
IS
	VDNAME DEPT.DNAME%TYPE;
BEGIN
	SELECT DNAME INTO VDNAME
	FROM EMP E INNER JOIN DEPT D
	ON E.DEPTNO=D.DEPTNO
	WHERE E.EMPNO=VEMPNO;
	RETURN VDNAME;
END;
/

[5] 커서(Cursor)
  - sql문을 실행할때마다 sql문 처리를 위한 메모리공간을 사용하는데 이를 접근하기
    위해 커서를 사용한다.
  
  - 커서의 단계
    1) 커서 선언하기
2) 커서 오픈하기
    3) 커서에서 추출한 결과 저장하기
4) 커서닫기

 예1)
DECLARE
	VEMPNO NUMBER(5);
	VENAME VARCHAR2(20);
	VSAL NUMBER(7);
	CURSOR C1
	IS
		SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO=10;
	BEGIN
		OPEN C1; -- 커서 오픈
		LOOP
			FETCH C1 INTO VEMPNO, VENAME, VSAL;
			EXIT WHEN C1%NOTFOUND; -- 커서를 통해 데이터가 존재하지 않으면 루프 종료
			DBMS_OUTPUT.PUT_LINE(VEMPNO||' '||VENAME||' '||VSAL);
		END LOOP;
		CLOSE C1; -- 커서 닫기
	END;
	/

 예2) 모든 사원번호,사원이름,급여,급여가3000이상이면 높음,2000이상이면 보통,
      2000미만이면 낮음을 출력하세요.
DECLARE
	VEMPNO EMP.EMPNO%TYPE;
	VENAME EMP.ENAME%TYPE;
	VSAL EMP.SAL%TYPE;
	LEVEL VARCHAR2(10);
	CURSOR C1
	IS
		SELECT EMPNO, ENAME, SAL FROM EMP;
BEGIN
	OPEN C1;
	LOOP
		FETCH C1 INTO VEMPNO, VENAME, VSAL;
		EXIT WHEN C1%NOTFOUND;
		IF VSAL>=3000 THEN
			LEVEL:='높음';
		ELSIF VSAL>=2000 THEN
			LEVEL:='보통';
		ELSIF VSAL<2000 THEN
			LEVEL:='낮음';
		END IF;
		DBMS_OUTPUT.PUT_LINE(VEMPNO||' '||VENAME||' '||VSAL||' '||LEVEL);
	END LOOP;
	CLOSE C1;
END;
/

 Q1) 커서를 사용해서 모든 사원의 이름,급여,전체사원의 급여합을 구해서 출력해 보세요.
DECLARE
	VEMPNO NUMBER(5);
	VENAME VARCHAR2(20);
	VSAL NUMBER(7,2);
	VSUM NUMBER(7,2):=0;
	CURSOR C1
	IS
		SELECT EMPNO, ENAME, NVL(SAL, 0) FROM EMP;
BEGIN
	OPEN C1;
	LOOP
		FETCH C1 INTO VEMPNO, VENAME, VSAL;
		EXIT WHEN C1%NOTFOUND;
		VSUM:=VSUM+VSAL;
		DBMS_OUTPUT.PUT_LINE(VEMPNO||' '||VENAME||' '||VSAL);
	END LOOP;
	DBMS_OUTPUT.PUT_LINE('전체 사원 급여 합:'||VSUM);
	CLOSE C1;
END;
/

 예3) 각 부서의 급여합 구하기
DECLARE
	D1 NUMBER(7,2):=0; --10번 부서합
	D2 NUMBER(7,2):=0; --20번 부서합
	D3 NUMBER(7,2):=0; --30번 부서합
	CURSOR C1
	IS
		SELECT DEPTNO, NVL(SAL, 0) SAL FROM EMP; -- NVL(SAL, 0) SAL FROM EMP->NULL 값인 경우 0으로
BEGIN
	FOR CURVAL IN C1 LOOP
		IF CURVAL.DEPTNO=10 THEN D1:=D1+CURVAL.SAL;
		ELSIF CURVAL.DEPTNO=20 THEN D2:=D2+CURVAL.SAL;
		ELSIF CURVAL.DEPTNO=30 THEN D3:=D3+CURVAL.SAL;
		END IF;
		DBMS_OUTPUT.PUT_LINE('부서번호:'||CURVAL.DEPTNO||'급여'||CURVAL.SAL);
	END LOOP;
	DBMS_OUTPUT.PUT_LINE('10번 부서 급여 합:'||D1);
	DBMS_OUTPUT.PUT_LINE('20번 부서 급여 합:'||D2);
	DBMS_OUTPUT.PUT_LINE('30번 부서 급여 합:'||D3);
END;
/

 Q2) 커서를 사용해서 모든 사원번호,이름,부서명,급여,보너스,부서번호를 출력하는 프로시져를 만들고 사용해 보세요.(FOR 사용)
    보너스는 부서번호가 10번이면 급여의 10%, 부서번호가 20번이면 급여의 20%, 부서번호가 30번이면 급여의 30%
CREATE OR REPLACE PROCEDURE BONUSPROC	
IS 
	CURSOR C1
	IS
		SELECT E1.EMPNO, E1.ENAME, D1.DNAME, NVL(E1.SAL,0) SAL, DEPTNO
		FROM EMP E1 NATURAL JOIN DEPT D1;
	BONUS NUMBER(7,2);
BEGIN
	FOR CURVAL IN C1 LOOP
		IF CURVAL.DEPTNO=10 THEN BONUS:=CURVAL.SAL*0.1;
		ELSIF CURVAL.DEPTNO=20 THEN BONUS:=CURVAL.SAL*0.2;
		ELSIF CURVAL.DEPTNO=30 THEN BONUS:=CURVAL.SAL*0.3;
		ELSE BONUS:=0;
		END IF;
		DBMS_OUTPUT.PUT_LINE(CURVAL.EMPNO||' '||CURVAL.ENAME||' '||CURVAL.DEPTNO||' '||BONUS);
	END LOOP;
END;
/
EXECUTE BONUSPROC;