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);
'Language > SQL' 카테고리의 다른 글
Oracle PL/SQL PL03 (Procedural extension to Structured Query Language) (0) | 2019.12.20 |
---|---|
Oracle PL/SQL PL02 (Procedural extension to Structured Query Language) (0) | 2019.12.20 |
Oracle PL/SQL PL00 (Procedural extension to Structured Query Language) (0) | 2019.12.20 |
Oracle SQL DDL 02 (Data Definition Language 02) (0) | 2019.12.18 |
Oracle SQL DDL 01 (Data Definition Language 01) (0) | 2019.12.18 |