SQLP 2-2-5 PL/SQL
in Devlopment on SQLP
2과목 SQL 기본 및 활용
- 20문제
- 각 상황별로 SQL문 작성이 가능할 정도로 연습해야 합니다.
- 기본적인 SQL문법 및 함수는 생략하고 Tuning에 활용될수 있는 것 위주로 정리하고자 합니다.
2장 SQL 활용 (#5)
PL/SQL (Procedural Language/SQL)
특징
- Block 구조로 되어 있어 각 기능별로 모듈화가 가능
- 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환
- IF, LOOP 등의 절차형 언어를 사용하여 프로그램이 가능
- DBMS 정의 에러/사용자 정의 에러를 사용
- Oracle에 내장되어 있으므로 다른 Oracle Server로 옮길수 있음
- 여러 SQL문장을 Block으로 묶어서 한번에 서버로 보내기 때문에 통신량을 줄일수 있음
- Server에서 수행되므로 Application의 성능을 향상
구조
- DECLARE : BEGIN ~ END 사이에 변수, 인수에 대한 정의 및 데이터 타입 선언
- BEGIN : PL/SQL 시작
- EXCEPTION : BEGIN ~ END 에서 실행되는 SQL문에서 발생한 에러를 처리 (선택항목)
- END : PL/SQL 종료
문법 (Syntax)
- 생성
CREATE [OR REPLACE] PROCEDURE 명칭 { argument1 [mode] data_type1, argument2 [mode] data_type2, ... } IS [AS] ... BEGIN ... EXCEPTION ... END; /
mode : IN (입력) , OUT (출력) , INOUT(입출력)
- 삭제
DROP PROCEDURE 명칭;
예제
DEPT table에 새로운 부서를 입력하는데,부서번호가 이미 존재하지 않을때만 입력하고 존재할 경우에는 그냥 종료되는 PROCEDURE를 작성해보자.
CREATE OR REPLACE PROCEDURE INPUT_DEPT
(
v_deptno in NUMBER,
v_dname in VARCHAR2,
v_loc in VARCHAR2,
v_result out VARCHAR2
)
IS
is_exist NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO is_exist FROM DEPT WHERE DEPTNO = v_deptno AND ROWNUM = 1;
IF is_exist > 0 THEN
v_result := '이미 등록된 부서번호';
ELSE
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (v_deptno, v_dname, v_loc);
COMMIT;
v_result := '입력 완료';
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
v_result := '에러 발생';
END;
/
실행을 시켜 보겠다.
EXECUTE INPUT_DEPT(10,'DEV','SEOUL',:result)
EXECUTE INPUT_DEPT(60,'NEW_DEV','SANGAM',:result)
User Defined Function
Function는 Procedure와는 다르게 반드시 1개의 값을 RETURN해야 한다. (SUM, NVL 같은 내장 함수를 생각하면 이해가 쉽다.)
ABS 함수 구현 : 절대값을 RETURN
CREATE OR REPLACE FUNCTION ABS_NUM (v_input IN NUMBER) RETURN NUMBER IS v_return NUMBER := 0; BEGIN if v_input >= 0 THEN v_return := v_input; ELSE v_return := v_input * -1; END IF; RETURN v_return; END; /
SELECT ABS_NUM(-20), ABS_NUM(3) FROM DUAL;
Trigger
- 특정 Table에 DML (INSERT, UPDATE, DELETE)문이 수행했을 때 자동으로 동작 (사용자 실행이 아닌 DBMS가 실행)
DBMS에서 실행을 하는 것이므로 DB에 바로 적용됨. (COMMIT, ROLLBACK이 불가)
- Syntax (문법)
CREATE [OR REPLACE] TRIGGER trigger_name -- TRIGGER 명칭
BEFORE | AFTER -- 아래 작업이 일어나기 전(BEFORE) 또는 후(AFTER)에 실행
INSERT | UPDATE | DELETE ON table_name -- 해당 table에 해당 작업이 일어났을 경우
[ FOR EACH ROW ] -- 행 트리거 (행 데이터 제어가능), 없으면 문장 트리거 (각 행 데이터 제어 불가)
[ WHEN (condition) ] -- 해당 조건에 맞을때에만 실행
PL/SQL block...
- 행의 데이터는
:NEW.column_name
,:OLD.column_name
으로 제어가 가능하다.- INSERT :
:NEW
(입력되는 ROW) - UPDATE :
:NEW
(변경된 새로운 값),:OLD
(변경 전의 값) - DELTE :
:OLD
(삭제된 값)
- INSERT :
- 예
쇼핑몰의 경우
- 하루 수만건의 주문이 들어옴
- 직원들은 일자별, 상품별 총 판매수량 및 주문실적으로 온라인으로 조회함
이럴 경우 매번 수만건의 데이터로 집계를 낼려면 서버 부하가 크다.
Trigger를 이용하여 주문이 들어올 때마다 집계를 계산하여 별도 Table에 보관하면 된다.
- Table 생성 ```sql CREATE TABLE ORDER_LIST ( ORDER_DATE CHAR(8) NOT NULL, PRODUCT VARCHAR2(10) NOT NULL, QTY NUMBER NOT NULL, AMOUNT NUMBER NOT NULL );
CREATE TABLE SALES_PER_DATE ( SALE_DATE CHAR(8) NOT NULL, PRODUCT VARCHAR2(10) NOT NULL, QTY NUMBER NOT NULL, AMOUNT NUMBER NOT NULL );
* Trigger 생성
```sql
CREATE OR REPLACE TRIGGER SUM_SALES
AFTER INSERT
ON ORDER_LIST -- ORDER_LIST table에 INSERT 작업 후에
FOR EACH ROW -- 행 트리거로 실행
DECLARE
o_date ORDER_LIST.ORDER_DATE%TYPE;
o_prod ORDER_LIST.PRODUCT%TYPE;
BEGIN
o_date := :NEW.ORDER_DATE;
o_prod := :NEW.PRODUCT;
UPDATE SALES_PER_DATE
SET QTY = QTY + :NEW.QTY,
AMOUNT = AMOUNT + :NEW.AMOUNT
WHERE SALE_DATE = o_date
AND PRODUCT = o_prod;
IF SQL%NOTFOUND THEN
INSERT INTO SALES_PER_DATE
VALUES (o_date, o_prod, :NEW.QTY, :NEW.AMOUNT);
END IF;
END;
/
- 데이터 입력 및 결과 확인 ```sql INSERT INTO ORDER_LIST VALUES (‘20160127’,’Orange’, 2, 10000); INSERT INTO ORDER_LIST VALUES (‘20160127’,’Orange’, 5, 20000); INSERT INTO ORDER_LIST VALUES (‘20160127’,’PetaSQL’, 1, 100); INSERT INTO ORDER_LIST VALUES (‘20160127’,’ChakraMAX’, 1, 1000);
SELECT * FROM ORDER_LIST; SELECT * FROM SALES_PER_DATE; ```
이 글이 도움이 되셨다면 공감 및 광고 클릭을 부탁드립니다 :)