Oracle PL/SQL PL01 (Procedural extension to Structured Query Language)

PROCEDURE

매개변수로 RETURN값을 사용할 수 있으며, INSERT, DELETE, UPDATE를 주로 사용한다.

PROCEDURE : 구조

SET SERVEROUTPUT ON

-- (PROCEDURE myProc)
CREATE OR REPLACE PROCEDURE myProc (inNum IN NUMBER, outNum OUT NUMBER)
IS

BEGIN

DBMS_OUTPUT.PUT_LINE('inNum:'||inNum);
outNum := 333;

END;
/
-- (myProc 호출)
VAR val NUMBER;
EXECUTE myProc(111, :val);
PRINT val;

-- (매개변수가 없는 PROCEDURE helloProc)
CREATE OR REPLACE PROCEDURE helloProc
IS
mag VARCHAR2(10);

BEGIN
msg := 'helloProc';
DBMS_OUTPUT.PUT_LINE(msg||'호출');

END;
/
-- (helloProc 호출)
EXEC helloProc;

PROCEDURE : 구조 row 추가

CREATE OR REPLACE PROCEDURE add_dept(
p_deptno IN departments.department_id%TYPE,
p_deptname IN departments.department_name%TYPE,
p_deptloc IN departments.location_id%TYPE)
IS

BEGIN
INSERT INTO departments(department_id, department_name, location_id)
VALUES(p_deptno, p_deptname, p_deptloc);
    
-- COMMIT;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('추가에 실패했습니다');
ROLLBACK;

END;
/

EXEC add_dept(301, '신규부서', 2500);

PROCEDURE : 구조 row 수정

-- (입려받은 사원번호의 급여를 1.3배 인상.)
CREATE OR REPLACE PROCEDURE updateSal(v_empno IN NUMBER)
IS
BEGIN

UPDATE employees
SET salary = salary * 1.3
WHERE employee_id = v_empno;
-- COMMIT;

END;
/

ACCEPT empno PROMPT '급여를 인상할 사원번호:'
EXECUTE updatesal(&empno);
ROLLBACK;

PROCEDURE : 구조 검색 후 console 출력

-- (사원번호를 입력받으면 그 사원의 이름, 사원번호, 사원급여도 출력이 되도록 작성.)
CREATE OR REPLACE PROCEDURE emp_info(p_empno IN employees.employee_id%TYPE)
IS
v_emp employees%ROWTYPE;

BEGIN
SELECT first_name, employee_id, salary INTO v_emp.first_name, v_emp.employee_id, v_emp.salary
FROM employees
WHERE employee_id = p_empno;
    
DBMS_OUTPUT.PUT_LINE('이름:'||v_emp.first_name);
DBMS_OUTPUT.PUT_LINE('사원번호:'||v_emp.employee_id);
DBMS_OUTPUT.PUT_LINE('급여:'||v_emp.salary);    

END;
/
EXEC emp_info(102);