본 문서의 내용은 한국데이터산업진흥원에서 펴낸 SQL 전문가 가이드를 기반으로 자격증 취득에 도움이 될 개념을 정리한 것입니다.
 |
|
8. 절차형 SQL
1. 절차형 SQL 개요
절차형 SQL 을 이용하면 SQL 문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈을 생성할 수 있다.
절차적 데이터 조작어는 어떻게 (How) 데이터를 접근해야 하는지 명세한다. 절차적 데이터 조작어로는 PL/SQL(오라클), T-SQL(SQL Server) 등이 있다.
비절차적 데이터 조작어는 사용자가 무슨 (What) 데이터를 원하는지만을 명세한다.
2. PL/SQL 개요
가. PL/SQL 특징
Oracle 의 PL/SQL 은 Block 구조로 되어있고 Block 내에는 DML 문장과 QUERY 문장, 그리고 절차형 언어(IF, LOOP) 등을 사용할 수 있으며, 절차적 프로그래밍을 가능하게 하는 트랜잭션 언어이다. 이런 PL/SQL 을 이용하여 다양한 저장 모듈(Stored Module)을 개발할 수 있다. 저장 모듈이란 PL/SQL 문장을 데이터베이스 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램이며, 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램이다.
PL/SQL 에서는 동적 SQL 또는 DDL 문장을 실행할 때, EXECUTE IMMEDIATE를 사용하여야 한다.
-- 예시
CREATE OR REPLACE PROCEDURE dynamic_sql_01
IS
str VARCHAR2(200);
BEGIN
str := 'CREATE TABLE sum (sum number)';
EXECUTE IMMEDIATE str;
END;
/
- PL/SQL 은 Block 구조로 되어있어 각 기능별로 모듈화가 가능하다.
- 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환한다.
- IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다.
- DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.
- PL/SQL 은 Oracle 에 내장되어 있으므로 Oracle 과 PL/SQL 을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다.
- PL/SQL 은 응용 프로그램의 성능을 향상시킨다.
- PL/SQL 은 여러 SQL 문장을 Block 으로 묶고 한 번에 Block 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.
PL/SQL Block 프로그램을 입력받으면 SQL 문장과 프로그램 문장을 구분하여 처리한다. 즉 프로그램 문장은 PL/SQL 엔진이 처리하고 SQL 문장은 Oracle 서버의 SQL Statement Executor 가 실행하도록 작업을 분리하여 처리한다.
나. PL/SQL 구조
- DECLARE
- BEGIN ~ END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입을 선언하는 선언부이다.
- BEGIN ~ END
- 개발자가 처리하고자 하는 SQL 문과 여러 가지 비교문, 제어문을 이용하여 필요한 로직을 처리하는 실행부이다.
- EXCEPTION
- BEGIN ~ END 절에서 실행되는 SQL 문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할 것이지를 정의하는 예외 처리부이다.
다. PL/SQL 기본 문법 (Syntax)
-- PROCEDURE 생성
CREATE OR REPLACE Procedure [Procedure name](argument [mode] data_type, ...)
IS (AS)
.... -- 변수, 상수 선언
BEGIN
.... -- SQL 실행
EXCEPTION
.... -- 예외 처리
END; /
-- PROCEDURE 제거
DROP Procedure [Procedure name];
- CREATE 명령어로 데이터베이스 내에 프로시저를 생성할 수 있다. 이렇게 생성한 프로시저는 데이터베이스 내에 저장된다. 프로시저는 개발자가 자주 실행해야 하는 로직을 절차적인 언어를 이용하여 작성한 프로그램 모듈이기 때문에 필요할 때 호출하여 실행할 수 있다.
- [OR REPLACE] 절은 데이터베이스 내에 같은 이름의 프로시저가 있을 경우, 기존의 프로시저를 무시하고 새로운 내용으로 덮어쓰기 하겠다는 의미이다.
- [mode] 부분에 지정할 수 있는 매개 변수의 유형은 3 가지가 있다.
- IN 은 운영 체제에서 프로시저로 전달될 변수의 MODE
- OUT 은 프로시저에서 처리된 결과가 운영체제로 전달되는 MODE
- INOUT MODE 는 IN 과 OUT 두 가지의 기능을 동시에 수행하는 MODE
- 슬래쉬(“/”)는 데이터베이스에게 프로시저를 컴파일하라는 명령어
3. T-SQL 개요
가. T-SQL 특징
T-SQL 은 근본적으로 SQL Server 를 제어하기 위한 언어로서, T-SQL 은 엄격히 말하면, MS 사에서 ANSI/ISO 표준의 SQL 에 약간의 기능을 더 추가해 보완적으로 만든 것이다.
- 변수 선언 기능 @@이라는 전역변수(시스템 함수)와 @이라는 지역변수가 있다.
- 지역변수는 사용자가 자신의 연결 시간 동안만 사용하기 위해 만들어지는 변수이며 전역변수는 이미 SQL 서버에 내장된 값이다.
- 데이터 유형(Data Type)을 제공한다. 즉 int, float, varchar 등의 자료형을 의미한다.
- 연산자(Operator)
- 산술연산자( +, -, *, /)와 비교연산자(=, <, >, <>) 논리연산자(and, or, not) 사용이 가능하다.
- 흐름 제어 기능
- IF-ELSE 와 WHILE, CASE-THEN 사용이 가능하다.
- 주석 기능
- 한줄 주석 : -- 뒤의 내용은 주석
- 범위 주석 : /* 내용 */ 형태를 사용하며, 여러 줄도 가능함
나. T-SQL 구조
PL/SQL 과 유사한 구조이다.
다. T-SQL 기본 문법 (Syntax)
-- PROCEDURE 생성
CREATE Procedure [schema_name.][Procedure name] @param data_type [mode], ...
AS
DECLARE
.... -- 변수, 상수 선언
BEGIN
.... -- SQL 실행
.... -- 에러 처리
END;
-- PROCEDURE 제거
DROP Procedure [schema_name.]Procedure_name;
- Oracle 은 [CREATE OR REPLACE]와 같이 하나의 구문으로 처리하지만 SQL Server 는 CREATE 구문을 ALTER 구문으로 변경하여야 한다.
- @parameter 는 프로시저가 호출될 때 프로시저 안으로 어떤 값이 들어오거나 혹은 프로시저에서 처리한 결과 값을 리턴 시킬 매개 변수를 지정할 때 사용한다.
- [mode] 부분에 지정할 수 있는 매개변수(@parameter)의 유형
- VARYING: 결과 집합이 출력 매개 변수로 사용되도록 지정, CURSOR 매개변수에만 적용된다.
- DEFAULT: 지정된 매개변수의 기본값으로 처리한다.
- OUT: OUTPUT 프로시저에서 처리된 결과 값을 EXECUTE 문 호출 시 반환한다.
- READONLY: 프로시저 본문 내에서 매개 변수를 업데이트하거나 수정할 수 없음을 나타낸다.
4. Procedure 의 생성과 활용
-- Oracle
CREATE OR REPLACE Procedure p_DEPT_insert(v_DEPTNO in number, -- 1)
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 -- 2)
AND ROWNUM = 1;
if cnt > 0 then
v_result := '이미 등록된 부서번호이다'; -- 3)
else
INSERT
INTO DEPT (DEPTNO, DNAME, LOC)
VALUES (v_DEPTNO, v_dname, v_loc); -- 4)
COMMIT;
v_result := '입력 완료!!'; -- 5)
end if;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK; v_result := 'ERROR 발생'; -- 6)
END; /
- DEPT 테이블에 들어갈 컬럼 값을 입력받는다.
- 입력 받은 부서코드가 존재하는지 확인한다.
- 부서코드가 존재하면 '이미 등록된 부서번호입니다'를 출력한다.
- 부서코드가 존재하지 않으면 입력받은 필드 값으로 새로운 부서 레코드를 입력한다.
- 정상적으로 입력됐을 경우, COMMIT 명령어를 통해 트랜잭션을 종료한다.
- 에러가 발생하면 모든 트랜잭션을 취소하고 'ERROR 발생' 메시지를 출력값에 넣는다.
- cnt 라는 변수를 SCALAR 변수라고 한다. SCALAR 변수는 사용자의 임시 데이터를 하나만 저장할 수 있는 변수이며 거의 모든 형태의 데이터 유형을 지정할 수 있다.
- PL/SQL 에서 사용하는 SELECT 문장은 결과값이 반드시 있어야 하며, 그 결과 역시 반드시 하나여야 한다. 조회 결과가 없거나 하나 이상인 경우에는 에러를 발생시킨다. T-SQL 에서는 결과 값이 없어도 에러가 발생하지 않는다.
- T-SQL 을 비롯하여 일반적으로 대입 연산자는 “=”을 사용하지만 PL/SQL 에서는 “:=”를 사용한다.
- 에러 처리를 담당하는 EXCEPTION 에는 WHEN ~ THEN 절을 사용하여 에러의 종류별로 적절히 처리한다. OTHERS 를 이용하여 모든 에러를 처리할 수 있지만 정확하게 에러를 처리하는 것이 좋다.
5. User Defined Function 의 생성과 활용
Function 이 Procedure 와 다른 점은 RETURN 을 사용해서 하나의 값을 반드시 되돌려 줘야 한다는 것이다. 즉 Function 은 Procedure 와는 달리 SQL 문장에서 특정 작업을 하고 결과를 리턴한다.
저장형 함수는 단독적으로 실행되기 보다는 다른 SQL문을 통하여 호출되고 그 결과를 리턴하는 SQL의 보조적인 역할을 한다.
CREATE OR REPLACE Function UTIL_ABS(v_input in number) -- 1)
return NUMBER IS
v_return number := 0; -- 2)
BEGIN
if v_input < 0 then
v_return := v_input * -1; -- 3)
else
v_return := v_input;
end if;
RETURN v_return; -- 4)
END; /
- 숫자 값을 입력받는다.
- 리턴 값을 받아줄 변수인 v_return 선언한다.
- 입력값이 음수이면 -1을 곱하여 v_return 변수에 대입한다.
- v_return 변수를 반환한다.
6. Trigger 의 생성과 활용
Trigger 란 특정한 테이블에 INSERT, UPDATE, DELETE 와 같은 DML 문이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록 작성된 프로그램이다. 즉 사용자가 직접 호출하여 사용하는 것이 아니고 데이터베이스에서 자동적으로 수행하게 된다.
Trigger 는 테이블과 뷰, 데이터베이스 작업을 대상으로 정의할 수 있으며, 전체 트랜잭션 작업에 대해 발생되는 Trigger 와 각 행에 대해서 발생되는 Trigger 가 있다.
즉, Trigger는 데이터베이스에 로그인하는 작업에도 정의할 수 있다.
트리거(Trigger)를 사용하여 주문한 건이 입력될 때마다, 일자별 상품별로 판매수량과 판매 금액을 집계하여 집계자료를 보관하도록 한다.
CREATE OR REPLACE Trigger SUMMARY_SALES -- 1)
AFTER INSERT
ON ORDER_LIST
FOR EACH ROW
DECLARE -- 2)
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, -- 3)
amount = amount + :NEW.amount
WHERE sale_date = o_date
AND product = o_prod;
if SQL%NOTFOUND then -- 4)
INSERT INTO SALES_PER_DATE
VALUES (o_date, o_prod, :NEW.qty, :NEW.amount);
end if;
END; /
-- 생성 이후 테스트 쿼리
SELECT * FROM ORDER_LIST;
INSERT INTO ORDER_LIST VALUES('20120901', 'MONOPACK', 10, 300000);
COMMIT;
SELECT * FROM ORDER_LIST;
SELECT * FROM SALES_PER_DATE;
INSERT INTO ORDER_LIST VALUES('20120901','MONOPACK',20,600000);
SELECT * FROM ORDER_LIST;
SELECT * FROM SALES_PER_DATE;
- Trigger 를 선언한다.
- 주문일자와 주문상품 값을 저장할 변수를 선언하고, 신규로 입력된 데이터를 저장한다.
- 먼저 입력된 주문 내역의 주문일자와 주문상품을 기준으로 SALES_PER_DATE 테이블에 업데이트한다.
- 처리 결과가 SQL%NOTFOUND 이면 해당 주문일자의 실적이 존재하지 않으며, 테이블에 새로운 집계 데이터를 입력한다.
Trigger에서 사용하는 레코드 구조체 비교
7. 프로시저와 트리거의 차이점
프로시저는 BEGIN ~ END 절 내에 COMMIT, ROLLBACK 과 같은 트랜잭션 종료 명령어를 사용할 수 있지만, 데이터베이스 트리거는 BEGIN ~ END 절 내에 사용할 수 없다.
Procedure, User Defined Function은 작성자의 기준으로 트랜잭션을 분할할 수 있으며, 또한 프로시저 내에서 다른 프로시저를 호출할 경우에 호출 프로시저의 트랜잭션과는 별도로 PRAGMA AUTONOMOUS_TRANSACTION을 선언하여 자율 트랜잭션 처리를 할 수 있다.
↓SQL 전문가 가이드 요약 목록
1장. 데이터 모델링의 이해
Part 1. 데이터 모델링의 이해
Part 2. 데이터 모델과 성능
2장. SQL 기본 및 활용
Part 1. SQL 기본
Part 2. SQL 활용
Part 3. SQL 최적화 기본원리
따로 PDF 파일이 필요하신 분은 댓글을 통해 메일 주소 적어주시기 바랍니다.
'데이터 사이언스 > SQL' 카테고리의 다른 글
| [SQLD 학습 자료 요약] SQL 기본 및 활용 3.2. 인덱스 기본 (2) | 2020.11.28 |
|---|---|
| [SQLD 학습 자료 요약] SQL 기본 및 활용 3.1. 옵티마이저와 실행계획 (0) | 2020.11.28 |
| [SQLD 학습 자료 요약] SQL 기본 및 활용 2.7. DCL (0) | 2020.11.27 |
| [SQLD 학습 자료 요약] SQL 기본 및 활용 2.6. 윈도우 함수 (0) | 2020.11.27 |
| [SQLD 학습 자료 요약] SQL 기본 및 활용 2.5. 그룹 함수 (0) | 2020.11.26 |
