하위^^

INSERT 예

SET SERVEROUTPUT ON;
-- 특정 사원 추가
SELECT * FROM emp;
-- insert_date(ename, job, deptno)
INSERT INTO emp(ename, job, deptno) VALUES('kim', 'staff', 3);

CREATE OR REPLACE PROCEDURE insert_date(v_ename IN emp.ename%TYPE, v_job IN emp.job%TYPE, v_deptno IN emp.deptno%TYPE)
    IS
    BEGIN
        INSERT INTO emp(ename, job, deptno) VALUES(v_ename, v_job, v_deptno);
        COMMIT;
        DBMS_OUTPUT.PUT_LINE(v_ename||'/'||v_job||'/'||v_deptno);
        DBMS_OUTPUT.PUT_LINE('데이터 입력 성공');
    END;
/

EXEC insert_date('kim', 'staff', 3);

UPDATE 예

-- 특정 사원의 급여 올리기
-- 급여가 인상된 직원의 데이터 출력(사원번호, 이름, 급여)
-- 테이블 : employees
SELECT * FROM employees;
CREATE OR REPLACE PROCEDURE update_date(v_no IN NUMBER, v_rate IN NUMBER)
    IS
        v_emp employees%ROWTYPE;
    BEGIN
        UPDATE employees SET salary = salary + (salary * (v_rate / 100))WHERE emp_no = v_no;
        COMMIT;
        
        SELECT emp_no, family_name, first_name, salary
        INTO v_emp.emp_no, v_emp.family_name, v_emp.first_name, v_emp.salary
        FROM employees WHERE emp_no = v_no;
        
        DBMS_OUTPUT.PUT_LINE('사원번호 : '||v_emp.emp_no);
        DBMS_OUTPUT.PUT_LINE('이름 : '||v_emp.family_name||''||v_emp.first_name);
        DBMS_OUTPUT.PUT_LINE('봉급 : '||v_emp.salary);
    END;
/
EXEC update_date(117, 10);

DELETE 예

-- 특정 사원 번호의 데이터 삭제 프로시저 제작
-- 대상 테이블 : employees
SELECT * FROM employees;
CREATE OR REPLACE PROCEDURE delete_date(v_deptno IN employees.emp_no%TYPE)
    IS
    BEGIN
        DELETE FROM emp WHERE emp_no = v_deptno;
        COMMIT;
    END;
/

EXEC delete_date(127);

Function은 예전에 JAVA에서 사용하던 메소드, C에서 함수와 거의 비슷한 개념이다.

Procedure와 차이점은 Return값이 있다는 것으로 

반환값이 필요할 때 사용한다.

JAVA에서 메소드를 만들 때 처럼 역시 반환 타입을 반드시 Return문에 선언해주어야 한다, 생략할 수 없다.

또한 OUT, IN OUT을 쓸 수 없고 IN만 사용 가능하다.

예제를 볼 때 실행 부분에서 ':' 부분을 보면 msg 앞에도 붙어있는데

이것이 없으면 msg라는 함수를 찾으므로 msg가 변수임을 알리는 키워드이다.

나머지는 프로시져와 거의 비슷한 편

SET SERVEROUTPUT ON;
-- 함수 선언
CREATE OR REPLACE FUNCTION hello
    RETURN VARCHAR2
    IS
        msg VARCHAR2(20);
    BEGIN
        msg := 'hello, world';
        RETURN msg;
    END;
/

VARIABLE message VARCHAR2(20);

EXEC :message := hello;
PRINT message;


-- 사원번호를 받아서 해당 사원의 급여를 10% 올리고 출력 하기
CREATE OR REPLACE FUNCTION print1(emp_num IN employees.salary%TYPE)
    RETURN employees.salary%TYPE
    IS
        find_salary employees.salary%TYPE;
    BEGIN
        UPDATE employees SET salary = salary * 1.1 WHERE emp_no = emp_num;
        COMMIT;
        SELECT salary
        INTO find_salary
        FROM employees WHERE emp_no = emp_num;
        RETURN find_salary;
    END;
/

VARIABLE msg NUMBER;

EXEC :msg := print1(118);
PRINT msg;


-- Function 조회
SELECT object_name, object_type FROM USER_PROCEDURES WHERE object_type = 'FUNCTION';

-- Function 삭제
-- DROP FUNCTION [function name]
DROP FUNCTION hello;

%ROWTYPE은 전의 Scalar타입보다 약간 효율적인 데이터 타입 선언이다.

이 타입 선언문은 선언 시 지정한 테이블의 컬럼 선언 부분을 전부 가져와 사용한다.

예제에서 사용법을 확인하자.

-- 테이블의 모든 컬럼을 통채로 들고 온다.
-- 테이블명%ROWTYPE
SET SERVEROUTPUT ON;

DECLARE
    v_emp employees%ROWTYPE;
    BEGIN
        SELECT emp_no, first_name, family_name, reg_date
        INTO v_emp.emp_no, v_emp.first_name, v_emp.family_name, v_emp.reg_date
        FROM employees WHERE emp_no = 118;
        DBMS_OUTPUT.PUT_LINE('사원번호 : '||v_emp.emp_no);
        DBMS_OUTPUT.PUT_LINE('이름 : '||v_emp.family_name||''||v_emp.first_name);
        DBMS_OUTPUT.PUT_LINE('입사일 : '||v_emp.reg_date);
    END;
/

CREATE OR REPLACE PROCEDURE emp_row(ref_emp_no IN employees.emp_no)
    IS
    v_emp employees%ROWTYPE;
    BEGIN
        SELECT emp_no, first_name, family_name, reg_date
        INTO v_emp.emp_no, v_emp.first_name, v_emp.family_name, v_emp.reg_date
        FROM employees WHERE emp_no = ref_emp_no;
        DBMS_OUTPUT.PUT_LINE('사원번호 : '||v_emp.emp_no);
        DBMS_OUTPUT.PUT_LINE('이름 : '||v_emp.family_name||''||v_emp.first_name);
        DBMS_OUTPUT.PUT_LINE('입사일 : '||v_emp.reg_date);
    END;
/

EXEC emp_row(117);