• 테이블 세팅

DECLARE
    vno emp.empno%TYPE;
    vsal emp.sal%type;
BEGIN
    SELECT empno, sal INTO vno, vsal
    FROM emp
    WHERE empno=7369;
    
    DBMS_OUTPUT.PUT_LINE('empno='|| vno || ', sal=' || vsal);
END;

PL/SQL INSERT

CREATE OR REPLACE PROCEDURE InsertEmp(
    myEmpno IN NUMBER,
    myEname IN VARCHAR2,
    myJob IN VARCHAR2,
    myMgr IN NUMBER)
AS
BEGIN
    INSERT INTO Emp(empno, ename, job, mgr)
        VALUES(myEmpno, myEname,myJob, myMgr);
END;

EXEC InsertEmp(9000, 'Crystal', 'SALESMAN', 7839);

SELECT * FROM emp;

PL/SQL Update

CREATE OR REPLACE PROCEDURE UpdateEmp(
    myEmpno IN NUMBER,
    myDeptno IN NUMBER)
AS
BEGIN
    UPDATE Emp
    SET Deptno = myDeptno
    WHERE empno = myEmpno;
END;

EXEC UpdateEmp(9000, 10);
SELECT * from Emp;

PL/SQL Delete

CREATE OR REPLACE PROCEDURE DeleteEmp(
    myEmpno IN NUMBER)
AS
BEGIN
    DELETE FROM Emp
    WHERE empno = myEmpno;
END;

EXEC DeleteEmp(9000);
SELECT * FROM emp;

직원 정보 추가/수정 프로시저

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;