CREATE OR REPLACE PROCEDURE InsertOrUpdateEmp (
myEmpno IN NUMBER,   myEname IN VARCHAR2,   myJob IN VARCHAR2)

AS
  myCount NUMBER;

BEGIN
  SELECT count(*) INTO myCount FROM Emp   WHERE empno = myEmpno;

 IF myCount !=0 THEN
      UPDATE Emp       SET job = myJob      WHERE empno = myEmpno;
 ELSE
      INSERT INTO Emp(empno, ename, job) VALUES(myEmpno, myEname, myJob);
 END IF;
END;

EXEC InsertOrUpdateEmp(9000, 'Crystal', 'SALESMAN');
SELECT * FROM emp;

EXEC InsertOrUpdateEmp(9000, 'Crystal', 'CLERK');
SELECT * FROM Emp;

CREATE OR REPLACE PROCEDURE DeleteEmp (myEmpno IN NUMBER)
AS
  myCount NUMBER;

BEGIN
  SELECT count(*) INTO myCount FROM Emp WHERE empno = myEmpno;

  IF myCount !=0 THEN
      DELETE FROM Emp WHERE empno = myEmpno;
      DBMS_OUTPUT.PUT_LINE('직원번호가' || myEmpno || '인 직원 정보가 삭제됨');
  ELSE
      DBMS_OUTPUT.PUT_LINE('삭제하려는 직원 정보가 없음');
  END IF; 
     
END;

EXEC DeleteEmp(9000);
SELECT * FROM Emp;

CREATE OR REPLACE PROCEDURE ShowComm (myEmpno IN number)
AS
  vComm NUMBER;

BEGIN
  SELECT comm INTO vComm FROM Emp WHERE empno = myEmpno; 

  IF vComm != NULL THEN
      DBMS_OUTPUT.PUT_LINE('직원번호가 ' || myEmpno || '인 직원의 comm 은 없음');  
   ELSE 
      DBMS_OUTPUT.PUT_LINE('직원번호가 ' || myEmpno || '인 직원의 comm 은' || vComm);
   END IF;

END;

EXEC ShowComm(7369);
EXEC ShowComm(7499);

CREATE OR REPLACE PROCEDURE CalcAvgSal
AS
  vAvgSal NUMBER;
BEGIN
  SELECT avg(sal) INTO vAvgSal FROM Emp;

  DBMS_OUTPUT.PUT_LINE('평균급여 : ' || vAvgSal);  
END;

EXEC CalcAvgSal;