[SQLD] SQL 활용 2-8. 절차형 SQL

8절. 절차형 SQL

1. 절차형 SQL 개요

: 절차 지향적인 프로그램이 가능하도록 연속적인 실행이나 조건에 따른 분기처리를 이용해

-> 특정기능을 수행하는 저장 모듈 수행 [Procedure, User Defined Function, Trigger]

2. PL/SQL 개요

가. PL/SQL 특징

: Oracle의 경우 Block 구조로, 

Block 내에는 DML 문장과 QUERY 문장, 그리고 절차형 언어(IF, LOOP) 등을 사용 가능하며,

절차적 프로그래밍을 가능하게 하는 트랜잭션 언어

 

 

절차형 SQL

: PL/SQL을 이용해 개발한 저장 모듈로 데이터베이스 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램

-> 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램

 

특징

• 각 기능별로 모듈화가 가능
• 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환
• IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능
• DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용 가능
• Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있고, 응용 프로그램의 성능을 향상 가능
• 여러 SQL문을 Block으로 묶고 한 번에 Block 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.

Block 프로그램을 입력시 SQL 문장과 프로그램 문장을 구분하여 처리하는데
프로그램 문장은 PL/SQL 엔진이 처리,
SQL 문장은 Oracle 서버의 SQL Statement Executor가 실행하도록 작업을 분리하여 처리

 

나. PL/SQL 구조

DECLARE :  변수와 인수에 대한 정의 및 데이터 타입을 선언하는 선언부

BEGIN ~ END : SQL문과 여러 가지 비교문, 제어문을 이용해 필요한 로직 처리하는 실행부

EXCEPTION :  BEGIN~END까지의 SQL문이 실행될 때 에러 처리부

다. PL/SQL 기본 문법

Stored Procedure 기본 문법

CREATE [OR REPLACE] Procedure [Procedure_name] ( argument1 [mode] data_type1, argument2 [mode] date_type2, ... ... ) IS [AS] ... ... BEGIN ... ... EXCEPTION ... ... END; /

[OR REPLACE] 절 : 데이터베이스 내에 같은 이름의 프로시저가 있을 경우, 기존의 프로시저를 무시하고 새로운 내용으로 덮어쓰기

Argument : 프로시저가 호출될 때 프로시저 안으로 어떤 값이 들어오거나 혹은 프로시저에서 처리한 결과값을 운영 체제로 리턴시킬 매개 변수를 지정

[mode] 매개변수 유형

IN : 운영체제에서 프로시저로 전달될 변수

OUT : 프로시저에서 처리된 결과가 운영체제로 전달

INOUT : 두가지 기능을 동시에 수행

'/' : 데이터베이스에게 프로시저를 컴파일하라는 명령어

DROP Procedure [Procedure_name];

-생성된 프로시저를 삭제하는 명령어

3. T-SQL 개요

 

가. T-SQL 특징

나. T-SQL 구조

다. T-SQL 기본 문법

4. Procedure의 생성과 활용

(1) DEPT 테이블에 새로운 부서를 등록하는 Procedure 작성

CREATE OR REPLACE Procedure p_DEPT_insert -------------① ( v_DEPTNO in number, v_dname in varchar2, v_loc in varchar2, v_result out varchar2) IS cnt number := 0; BEGIN SELECT COUNT(*) INTO CNT -------------② FROM DEPT WHERE DEPTNO = v_DEPTNO AND ROWNUM = 1; if cnt >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 := 'ERROR 발생'; END; /

DEPT 테이블은 DEPTNO 칼럼이 PRIMARY KEY로 설정되어 있으므로, DEPTNO 칼럼에 는 유일한 값을 넣어야만 한다.

① DEPT 테이블에 들어갈 칼럼 값(부서코드, 부서명, 위치)을 입력 받는다.

② 입력 받은 부서코드가 존재하는지 확인한다.

③ 부서코드가 존재하면 '이미 등록된 부서번호입니다'라는 메시지를 출력 값에 넣는다.

④ 부서코드가 존재하지 않으면 입력받은 필드 값으로 새로운 부서 레코드를 입력한다.

⑤ 새로운 부서가 정상적으로 입력됐을 경우에는 COMMIT 명령어를 통해서 트랜잭션을 종료한다.

⑥ 에러가 발생하면 모든 트랜잭션을 취소하고 'ERROR 발생'라는 메시지를 출력값에 넣는다.

 

PL/SQL 주요 문법

• SCALAR 변수는 사용자의 임시 데이터를 하나만 저장할 수 있는 변수이며 거의 모든 형태의 데이터 유형을 지정
• SELECT 문장은 다르다.
  PL/SQL에서 사용하는 SELECT 문장은 결과값이 반드시 있어야 하며, 그 결과 역시 반드시 하나여야 한다
• 일반적으로 대입 연산자는 “=”을 사용하지만 PL/SQL에서는 “:=”를 사용
• 에러 처리를 담당하는 EXCEPTION에는 WHEN ~ THEN 절을 사용하여 에러의 종 류별로 적절히 처리한다. OTHERS를 이용하여 모든 에러를 처리할 수 있지만 정확하게 에 러를 처리하는 것이 좋다

(2) 프로시저 기능 테스트

SQL> SELECT * FROM DEPT; -----------------① DEPTNO DNAME LOC ------- ------- --------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> variable rslt varchar2(30); -----------------② SQL> EXECUTE p_DEPT_insert(10,'dev','seoul',:rslt); -----------------③ PL/SQL 처리가 정상적으로 완료되었다. SQL> print rslt; -----------------④ RSLT -------------------------------- 이미 등록된 부서번호이다 SQL> EXECUTE p_DEPT_insert(50,'NewDev','seoul',:rslt); ----------------⑤ PL/SQL 처리가 정상적으로 완료되었다. SQL> print rslt;----------------⑥ RSLT -------------------------------- 입력 완료!! SQL> SELECT * FROM DEPT; ----------------⑦ DEPTNO DNAME LOC ------ -------- --------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 NewDev SEOUL 5개의 행이 선택되었다.

① DEPT 테이블을 조회하면 총 4개 행의 결과가 출력된다.

② Procedure를 실행한 결과 값을 받을 변수를 선언한다. (BIND 변수)

③ 존재하는 DEPTNO(10)를 가지고 Procedure를 실행한다.

④ DEPTNO가 10인 부서는 이미 존재하기 때문에 변수 rslt를 print해 보면 '이미 등록된 부서번호이다' 라고 출력된다. ⑤ 이번에는 새로운 DEPTNO(50)를 가지고 입력한다.

⑥ rslt를 출력해 보면 '입력 완료!!' 라고 출력된다.

⑦ DEPT 테이블을 조회하여 보면 DEPTNO가 50인 데이터가 정확하게 저장되었음을 확인할

5. User Defined Function의 생성과 활용 [사용자 정의 함수]

:Procedure처럼 절차형 SQL을 로직과 함께 데이터베이스 내에 저장해 놓은 명령문의 집합

 

Function은 Procedure와 달리 RETURN을 사용해서 하나의 값을 반드시 되돌려 줘야 한다는 것이다.

즉, Function은 Procedure와는 달리 SQL 문장에서 특정 작업을 수행하고 반드시 수행결과 값을 리턴한다.

 

(2) K-리그 8월 경기결과와 두 팀간의 점수차를 ABS 함수를 사용하여 절대값으로 출력

SELECT SCHE_DATE 경기일자, HOMETEAM_ID || ' - ' || AWAYTEAM_ID 팀들, HOME_SCORE || ' - ' || AWAY_SCORE SCORE, ABS(HOME_SCORE - AWAY_SCORE) 점수차 FROM SCHEDULE WHERE GUBUN = 'Y' AND SCHE_DATE BETWEEN '20120801' AND '20120831' ORDER BY SCHE_DATE;

(3) ABS 함수를 만드는데, INPUT 값으로 숫자만 들어온다고 가정

CREATE OR REPLACE Function UTIL_ABS (v_input in number) ---------------- ① return NUMBER IS v_return number := 0; ---------------- ② BEGIN if v_input <0 then ---------------- ③ v_return := v_input * -1; else v_return := v_input; end if; RETURN v_return; ---------------- ④ END; /

UTIL_ABS Function의 처리 과정

① 숫자 값을 입력 받는다. 예제에서는 숫자 값만 입력된다고 가정한다.

② 리턴 값을 받아 줄 변수인 v_return를 선언한다.

③ 입력 값이 음수이면 -1을 곱하여 v_return 변수에 대입한다.

④ v_return 변수를 리턴한다.

 

(4) 함수를 이용하여 앞의 SQL을 수정하여 실행

SELECT SCHE_DATE 경기일자, HOMETEAM_ID || ' - ' || AWAYTEAM_ID 팀들, HOME_SCORE || ' - ' || AWAY_SCORE SCORE, UTIL_ABS(HOME_SCORE - AWAY_SCORE) 점수차 FROM SCHEDULE WHERE GUBUN = 'Y' AND SCHE_DATE BETWEEN '20120801' AND '20120831' ORDER BY SCHE_DATE;

ABS 내장함수와 같은 결과를 나타낸다.

 

6. Trigger의 생성과 활용

: 특정 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록 작성된 프로그램

테이블과 뷰, 데이터베이스 작업을 대상으로 정의할 수 있다.

 

-> 데이터베이스에서 자동적으로 수행

 

종류

• 전체 트랜잭션 작업에 대해 발생되는 Trigger
• 각 행에 대해서 발생되는 Trigger

(5) 상품별 총 판매수량과 총 판매가격으로 구성된 주문 실적을 출력

-> 주문한 건이 입력될 때마다, 일자별 상품별로 판매수량 과 판매금액을 집계

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);

 

(6) 트리거 작성

-> ORDER_LIST에 주문 정보가 입력되면 주문 정보의 주문 일자 (ORDER_LIST.ORDER_DATE)와 주문 상품(ORDER_LIST.PRODUCT)을 기준으로 판매 집계 테이블(SALES_PER_DATE)에 해당 주문 일자의 주문 상품 레코드가 존재하면 판매 수량과 판매 금액을 더하고 존재하지 않으면 새로운 레코드를 입력

CREATE OR REPLACE Trigger SUMMARY_SALES ---------------- ① AFTER INSERT ON ORDER_LIST 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; /

① Trigger를 선언한다.

CREATE OR REPLACE Trigger SUMMARY_SALES : Trigger 선언문

AFTER INSERT : 레코드가 입력이 된 후 Trigger 발생

ON ORDER_LIST : ORDER_LIST 테이블에 Trigger 설정

FOR EACH ROW : 각 ROW마다 Trigger 적용

 

② o_date(주문일자), o_prod(주문상품) 값을 저장할 변수를 선언하고, 신규로 입력된 데이터를 저장한다.

: NEW는 신규로 입력된 레코드의 정보를 가지고 있는 구조체

: OLD는 수정, 삭제되기 전의 레코드를 가지고 있는 구조체

③ 먼저 입력된 주문 내역의 주문 일자와 주문 상품을 기준으로 SALES_PER_DATE 테이 블에 업데이트한다.

④ 처리 결과가 SQL%NOTFOUND이면 해당 주문 일자의 주문 상품 실적이 존재하지 않으 며, SALES_ PER_DATE 테이블에 새로운 집계 데이터를 입력

(7) ORDER_LIST 테이블에 주문 정보를 입력

SQL> SELECT * FROM ORDER_LIST; 선택된 레코드가 없다. SQL> SELECT * FROM SALES_PER_DATE; 선택된 레코드가 없다. SQL> INSERT INTO ORDER_LIST VALUES('20120901', 'MONOPACK', 10, 300000); 1개의 행이 만들어졌다. SQL> COMMIT; 커밋이 완료되었다.

 

(8) 주문 정보와 판매 집계 테이블에 같은 데이터가 들어왔는지 확인

SQL> SELECT * FROM ORDER_LIST; ORDER_DATG PRODUCT QTY AMOUNT --------- -------- -------- ------- 20120901 MONOPACK 10 300000 SQL> SELECT * FROM SALES_PER_DATE; SALE_DATG PRODUCT QTY AMOUNT -------- -------- -------- -------- 20120901 MONOPACK 10 300000

 

(9) 다시 한 번 같은 데이터를 입력해보고, 두 테이블의 데이터를 확인

SQL> INSERT INTO ORDER_LIST VALUES('20120901','MONOPACK',20,600000); 1개의 행이 만들어졌다. SQL> COMMIT; 커밋이 완료되었다. SQL> SELECT * FROM ORDER_LIST; ORDER_DATG PRODUCT QTY AMOUNT --------- ---------- ------ ------- 20120901 MONOPACK 10 300000 20120901 MONOPACK 20 600000 SQL> SELECT * FROM SALES_PER_DATE; SALE_DATG PRODUCT QTY AMOUNT -------- --------- ----- ------- 20120901 MONOPACK 30 900000

 

(10) 다른 상품으로 주문 데이터를 입력한 후 두 테이블의 결과를 조회해 보고 트랜잭션을 ROLLBACK 수행한다. 판매 데이터의 입력 취소가 일어나면, 주문 정보 테이블 과 판매 집계 테이블에 동시에 입력(수정) 취소가 일어나는지 확인

SQL> INSERT INTO ORDER_LIST VALUES('20120901','MULTIPACK',10,300000); 1개의 행이 만들어졌다. SQL> SELECT * FROM ORDER_LIST; ORDER_DA PRODUCT QTY AMOUNT -------- -------- ------ ------- 20120901 MONOPACK 10 300000 20120901 MONOPACK 20 600000 20120901 MULTIPACK 10 300000 SQL> SELECT * FROM SALES_PER_DATE; SALE_DATG PRODUCT QTY AMOUNT -------- -------- ------ ------- 20120901 MONOPACK 30 900000 20120901 MULTIPACK 10 300000 SQL> ROLLBACK; 롤백이 완료되었다. SQL> SELECT * FROM ORDER_LIST; ORDER_DATG PRODUCT QTY AMOUNT -------- -------- ------ ------- 20120901 MONOPACK 10 300000 20120901 MONOPACK 20 600000 SQL> SELECT * FROM SALES_PER_DATE; SALE_DATG PRODUCT QTY AMOUNT -------- --------- ------ ------- 20120901 MONOPACK 30 900000

ROLLBACK을 하면 하나의 트랜잭션이 취소가 되어 Trigger로 입력된 정보까지 하나의 트 랜잭션으로 인식하여 두 테이블 모두 입력 취소가 되는 것을 보여 주고 있다.

Trigger는 데이터베이스에 의해 자동 호출되지만 결국 INSERT, UPDATE, DELETE 문과 하나의 트랜 잭션 안에서 일어나는 일련의 작업들이라 할 수 있다.

Trigger는 데이터베이스 보안의 적 용, 유효하지 않은 트랜잭션의 예방, 업무 규칙 자동 적용 제공 등에 사용

 

7. 프로시저와 트리거의 차이점

프로시저 :BEGIN ~ END 절 내에 COMMIT, ROLLBACK과 같은 트랜잭션 종료 명령어를 사용할 수 있지만,

트리거 :BEGIN ~ END 절 내에 사용할 수 없다.