PL/SQL 02

SET SERVEROUTPUT ON;
--------------------------------------------------------
SET VERIFY OFF;
--  SQL명령어나 PL/SQL에서 &를 이용한치환 변수등을 사용할 때
-- 치환되기 전 후의 값을 보일 건지 여부를 결정하는 기능이다. 기본은 ON
ACCEPT p_name PROMPT '이름:';
ACCEPT p_sal PROMPT '급여:';
ACCEPT p_deptno PROMPT '부서번호:';
 
 
DECLARE
    v_name VARCHAR2(30) := UPPER('&p_name');
    v_sal NUMBER(7,2) := &p_sal;
    v_deptno NUMBER(2) := &p_deptno;
BEGIN
    DBMS_OUTPUT.PUT_LINE('v_name :' ||v_name );
    DBMS_OUTPUT.PUT_LINE('v_sal :' ||v_sal );
    DBMS_OUTPUT.PUT_LINE('v_deptno :' ||v_deptno );
    
    IF v_deptno = 30 THEN
        v_sal := v_sal *1.2;
    ELSIF v_deptno = 60 THEN
        v_sal := v_sal *1.1;
    END IF;
    
    INSERT INTO EMPLOYEES(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL,
            PHONE_NUMBER, HIRE_DATE, JOB_ID,SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
    VALUES (EMPLOYEES_SEQ.NEXTVAL,NULL,v_name,'dobbury@naver.com',NULL,TO_DATE('18/10/20','YY/MM/DD'),'IT_PROG',v_sal,NULL,NULL,v_deptno);
END;
/
 
SET VERIFY ON;
SET SERVEROUTPUT OFF;
 
SELECT
    *
FROM EMPLOYEES;
 
-- 부서번호를 입력
-- 입력한 부서의 급여 합을 출력
 
SET SERVEROUTPUT ON;
SET VERIFY OFF;
 
ACCEPT p_deptno PROMPT '부서번호 입력: ';
 
DECLARE
    v_deptno NUMBER := &p_deptno;
    v_total NUMBER :=0;
BEGIN
    SELECT SUM(SALARY) INTO v_total
    FROM EMPLOYEES
    WHERE DEPARTMENT_ID = v_deptno;
    
    DBMS_OUTPUT.PUT_LINE(v_deptno ||'부서의 급여의 총 합: ' || v_total);
END;
/
 
SET VERIFY ON;
SET SERVEROUTPUT OFF;