1. Creating Stored Procedures
PROCEDURE / FUNCTION
: 차이점은 리턴값 여부??
-. sqlplus 시작시마다 매번 "sql>define _editor=vi" 귀찮으니깐...
login.sql 파일을 생성하여, 해당 구문을 넣으면 항상 실행된다.
(set serveroutput on size unlimited 도 포함하면 좋지....)
-. procedure 작성하고,
-. 에러나면 "show error" 로 확인하고,
-. execute "프로시져 이름" 실행
-. 외부/프로그램에서 실행시에는 BEGIN my_proc; END; 식으로 호출하면 됨.
* Procedure Parameter Mode
-. IN : 외부에서 전달된 파라미터를 정의할 때
-. OUT : 생성된 데이터를 외부로 전달 할때
-. INOUT : IN OUT 변수를 하나로 ...
-. 외부/프로그램에서 정의한 BIND변수를 실행할 때는 ":"을 사용한다.
sqlplus 또는 Pro*C 에서 직접 지정한 변수는 BIND변수다.
-- my_proc 프로시져 생성 CREATE OR REPLACE PROCEDURE my_proc (p_id IN NUMBER, p_name OUT VARCHAR2) IS v_last_name employees.last_name%TYPE; BEGIN SELECT last_name INTO v_last_name FROM employees WHERE employee_id = p_id; p_name := v_last_name; END; /
-- my_proc 프로시저의 OUT리턴값을 확인하기 위해서 다음을 실행 DECLARE v_name VARCHAR2(30) := 'HELLO'; BEGIN DBMS_OUTPUT.PUT_LINE(v_name); my_proc(102, v_name); DBMS_OUTPUT.PUT_LINE(v_name); END; /
-- IN OUT 변수를 하나의 파라미터로 사용하기 -- JOBS 테이블에서 job_id로 조회하고, job_title를 리턴받는다. CREATE OR REPLACE PROCEDURE pr_get_job (p_job IN OUT VARCHAR2) IS BEGIN SELECT job_title INTO p_job FROM jobs WHERE job_id = p_job; END; /
-- pr_get_job 프로시저의 OUT리턴값을 확인하기 위해서 다음을 실행 DECLARE v_job VARCHAR2(30) := 'SA_MAN'; BEGIN DBMS_OUTPUT.PUT_LINE(v_job); my_proc(v_job); DBMS_OUTPUT.PUT_LINE(v_job); END; /
* 삭제시에는
DROP PROCEDURE procedure_name;
2. Creating Stored Functions
* FUNCTION
-. 리턴값이 할당되지 않는 곳에서는 사용 불가.
-. SQL 안에서 함께 사용 가능하다.
-. 프로시져와 비교해서 RETURN 절만이 추가된다.
-- FUNCTION 사용하기 -- join 으로 할 거 테스트하기 (이렇게 사용하지 말자) : 실제 Nested Loop Join을 발생시킨다.. CREATE OR REPLACE FUNCTION fn_get_dept(p_dept IN NUMBER) RETURN VARCHAR2 IS v_name VARCHAR2(50);> BEGIN SELECT department_name INTO v_name FROM departments WHERE department_id = p_dept; RETURN(v_name); END; /
-- fn_get_dept FUNCTION 사용하기 --column last_name format A20 --column dname format A25 SELECT employee_id, last_name, department_id, fn_get_dept(department_id) dname FROM employees; TIP) 위 처럼 하면, 모든 쿼리가 2번씩 실행되어, 성능에 지대한 영향을 끼친다. 차라리 Sub Query 를 사용하자! (sub query는 같은 department_id 에 대해서는 쿼리가 한번만 실행된다!!) SELECT employee_id, last_name, department_id, ( SELECT department_name INTO v_name FROM departments WHERE department_id = A.department_id ) dname FROM employees A;* 삭제시에는
DROP FUNCTION procedure_name;
* Stored Function 제약사항!
1) Parameter Mode : IN
2) Parameter Data Type : Database Data Type
-. Varchar2, Number : 정상
-. Boolean, PLS-Integer : 사용불가
3) Return Value Data Type "2)" 동일
4) DML 불가
5) Commit , Rollback 불가
6) 같은 테이블을 Fuction으로 만들고, 해당 Function을 같은 테이블로 처리시에는 에러!
예) select ..., f(x) from emp 그리고 f(x) = select ... from emp 인 경우
--> 실제 컴파일은 정상으로 처리되나, SQL과 함께 사용시에는 에러 발생됨!!! (Runtime Error)
* PROCEDURE 와 FUNCTION 차이
-. standalone 형태로 사용할거면, procedure!! (예 - 배치성 업무)
-.
3. Creating Packages
-. Oracle 패키지에서는 자주 사용 됨? (Forms, 기타 ... )
-. OOP개념이 포함됨 (캡슐화)
-. Specification + (Body) 로 구성됨.
: spec에 정의한 내용들은 모두 public! ex) public 변수, public procedure/function
body에 정의한 내용들은 모두 private! ex) private 변수, private proceduer/function
body 내에서도 package 변수는 접근가능하나, local 변수라면 접근 불가.
body없이 패키지만 사용하여, 상수 클래스와 같은 효과를 낼 수 있음.
-. Anomymous, Procedure, Function 에서 사용 가능
: Spec 접근 가능, Body 접근 불가
-. 장단점.
: 하나의 패키지로 묶으면, 한번의 IO만 발생하여, 성능이 좋다?
대신 메모리 점유율 높아짐.
여러개의 패키지로 안 묶고 단인 프로시져로 하면, 메모리는 낮지만 IO가 높아짐.
그래서 관련있는 넘들만 패키지로 잘~ 묶을 것!
* Spec
-. public 성격, 외부 접근 가능
-. Declaration : 선언
* Body
-. private 성격 : 외부 접근 불가
-. Definition
* 삭제
DROP PACKAGE package_name;
* BULK COLLECT
* RETURNING ...
* ...
10. Trigger
-. DML/DDL Trigger 등이 있다.
1) Statement Trigger
: "For each Row" 사용하지 않음.
DML 작업에 영향 받은 Row 수에 관계없이 1개 실행마다 1회만 fire
2) Row Trigger
: For each Row 키워드 사용
영향받은 Row의 건수만큼 fire
* Creating a DML Statement Trigger
1) Trigger Timing : After, Before, (Instead of)
2) Trigger Event : Insert, Update, Delete
3) Trigger Table : ON <table명>
4) Trigger Type : for each Row
TIP) Trigger 를 이용해서 DML을 막을 수도 있다.
-. 가령 업무 시간 이외에는 Insert 작업을 못 하도록 ... (Page 10-11)
-. 감사용도로써, DML을 일으키는 작업자와 시간, 예전 데이터 등을 기록 할 수도 있다. (Page 10-15)
