8.절차형 SQL (by darkturtle) [2013.10.22]
목차
1. 절차형 SQL 개요
2. PL/SQL 개요
3. T-SQL 개요
4. Procedure의 생성과 활용
5. User Defined Function의 생성과 활용
6. Trigger 의 생성과 활용
7. 프로시저와 트리거의 차이점
1. 절차형 SQL 개요
- 일반적인 개발 언어처럼 SQL에도 절차 지향적인 프로그램이 가능하도록 DBMS 벤더별로
PL(Procedure Language)/SQL(Oracle), SQL/PL(DB2), T-SQL(SQL Server) 등의 절차형 SQL 을 제공
2. PL/SQL 개요
가. PL/SQL 특징
- Block 구조로 되어 있고 Block 내에는 DML 문장과 QUERY 문장, 그리고 절차형 언어(IF, LOOP) 등을 사용할 수 있으며,
절차적 프로그래밍이 가능하게 하는 트랜잭션 언어이다.
* PL/SQL 특징
- PL/SQL 은 Block 구조로 되어 있어 각 기능별로 모듈화가 가능하다.
- 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환한다.
- IF, LOOP 등이 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다.
- DBMS 저으이 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.
- PL/SQL은 Oracle에 내장되어 있으므로 Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다.
- PL/SQL 은 응용 프로그램의 성능을 향상시킨다.
- PL/SQL 은 여러 SQL 문장을 Block 으로 묶고 한 번에 Block 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.
* PL/SQL Architecture
나. PL/SQL 구조
다. PL/SQL 기본 문법(Syntax)
CREATE [OR REPLACE] Procedure [Procedure_name] -- [OR REPLACE] : 같은 이름의 프로시저가 있을 경우, 새로운 내용으로 REPLACE
( argument1 [mode1] data_type1, -- [mode] : IN - 운영체제에서 프로시저에 전달될 변수의 MODE,
-- OUT - 프로시저에서 처리된 결과가 운영체제로 전달되는 MODE
-- INOUT - IN과 OUT 두 가지의 기능 동시 수행
argument2 [mode2] date_type2,
... ... )
IS [AS]
... ...
BEGIN
... ...
EXCEPTION
... ...
END;
/ -- "/" : 데이터베이스에게 프로시저를 컴파일하라는 명령어
* 프로시저 삭제
-- Procedure 삭제
DROP Procedure [Procedure_name];
3. T-SQL 개요
- T-SQL은 근본적으로 SQL Server를 제어하기 위한 언어로서,
- T-SQL 은 MS 사에서 ANSI/ISO 표준의 SQL 에 약간의 기능을 추가해 보완적으로 만든것
T-SQL 특징
- 변수 선언 기능 @@ 이라는 전역변수(시스템함수)와 @이라는 지역변수가 있다.
- 지역변수는 사용자가 자신의 연결 시간 동안만 사용하기 위해 만들어지는 변수이며
전역변수는 이미 SQL서버에 내장된 값이다. - 데이터 유형(Data Type)을 제공한다. 즉, int, float, varchar 등의 자료형 의미
- 연산자(Operator) 산술연산자(+,-,*,/)와, 비교연산자(=,<,>,<>), 논리연산자(and, or, not) 사용이 가능하다.
- 흐름 제어 기능 IF-ELSE와 WHILE, CASE-THEN 사용이 가능하다.
- 주석 기능 한줄주석( -- ), 범위주석(/* */)
나. T-SQL 구조
다. T-SQL 기본 문법(Syntax)
CREATE Procedure [schema_name.]Procedure_name -- 생성은 CREATE, 변경은 ALTER
@parameter1 data_type1[mode], -- [mode] 1. VARING : 결과 집합이 출력 매개 변수로 사용되도록 지정한 CURSOR 매개변수에만 적용
-- 2. DEFAULT : 지정된 매개변수가 프로시저를 호출할 당시 지정되지 않을 경우, 지정된 기본값으로 처리한다.
-- 3. OUT. OUTPUT : 프로시저에서 치리된 결과 값을 EXECUTE 문 호출 시 반환
-- 4. READONLY : 프로시저 본문 내에서 매개 변수를 업데이트 하거나 수정할수 없음을 의미
@parameter2 data_type2[mode],
... ...
WITH <proc_option> -- [proc_option> 1. RECOMPILE : 현재 프로시저의 계획을 캐시하지 않고 프로시저가 런타임에 컴파일
-- 2. ENCRYPTION : 원문 텍스트가 알아보기 어려운 형식으로 변환
-- 3. EXECUTE AS : 해당 저장 프로시저를 실행할 보안 컴텍스트를 지정 { EXEC } | { CALLER | SELF | OWNER | 'user_name' }
AS
... ...
BEGIN
... ...
ERROR 처리
... ...
END;
* 프로시저 삭제
DROP Procedure [schema_name.]Procedure_name ;
4. Procedure의 생성과 활용
- SCOTT유저가 소유하고 있는 DEPT 테이블에 새로운 부서를 등록하는 Procedure 를 작성한다.
CREATE OR REPLACE Procedure p_DEPT_insert << 1
( 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 << 2
FROM DEPT
WHERE DEPTNO = v_DEPTNO
AND ROWNUM = 1 ;
IF CNT >0 THEN << 3
v_result :='이미 등록된 부서번호이다.';
else
INSERT INTO DEPT (DEPTNO, DNAME, LOC) << 4
VALUES ( v_DEPTNO, v_dname, v_loc ) ;
COMMIT ; << 5
v_result := '입력완료!!';
END IF ;
EXCEPTION << 6
WHEN OTHERS THEN
ROLLBACK ;
v_result :='Error 발생';
END ;
/
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> exec p_dept_insert(10,'dev','seoul',:rslt);
PL/SQL procedure successfully completed.
SQL> print rslt;
RSLT
--------------------------------
Error 이미 등록된 부서번호이다
SQL> exec p_dept_insert(50,'NewDev','seoul',:rslt);
PL/SQL procedure successfully completed.
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
SQL>
PL/SQL 및 T-SQL 작성 시, 주의해야 할 문법적 요소
1. CNT 라는 변수를 SCALAR 변수라고 한다. SCALR 변수는 사용자의 임시 데이터를 하나만 저장할 수 있은 변수이며 거의 모든 형태의 데이터 유형 지정 가능
2. PL/SQL 에서 사용하는 SELECT 문장은 결과값이 반드시 있어야 하며, 그 결과 역시 반드시 하나여야 한다.
조회 결과가 없거나 하나 이상인 경우에는 에러를 발생시킨다.
T-SQL 에서는 결과 값이 없어도 에러가 발생하지 않는다.
3. T-SQL을 비롯하여 일반적으로 대입 연산자는 "="을 사용하지만 PL/SQL에서는 ":="을 사용한다.
4. 에러 처리를 담당하는 EXCEPTION 에는 WHEN ~ THEN 절을 사용하여 에러의 종류별로 적절히 처리 한다.
OTHERS 를 이용하여 모든 에러를 처리할 수 있지만 정확하게 에러를 처리하는 것이 좋다.
5. User Defined Function의 생성과 활용
- Function 이 Procedure 와 다른 점은 RETURN을 사용해서 하나의 값을 반드시 되돌려 줘야 한다는 것
* ABS 함수를 사용하여 점수차를 절대값으로 출력
SQL> SELECT SCHE_DATE DA,
HOMETEAM_ID||'-'||AWAYTEAM_ID TEAMS,
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
DA TEAMS SCORE ABS(HOME_SCORE-AWAY_SCORE)
-------- ------- -------------------- --------------------------
20120803 K08-K07 1-0 1
20120803 K01-K03 3-0 3
20120803 K06-K09 2-1 1
20120804 K05-K04 2-1 1
20120804 K10-K02 0-3 3
20120811 K09-K05 0-1 1
20120811 K03-K08 2-0 2
20120811 K07-K10 1-1 0
20120811 K01-K06 0-0 0
20120811 K04-K02 0-2 2
20120818 K06-K03 3-1 2
20120818 K02-K09 1-2 1
20120818 K08-K10 3-1 2
20120818 K04-K07 1-0 1
20120818 K05-K01 0-2 2
20120824 K08-K06 4-3 1
20120824 K05-K03 3-3 0
20120824 K02-K01 1-1 0
20120825 K10-K04 1-1 0
20120825 K09-K07 1-1 0
20120828 K03-K02 0-0 0
20120828 K06-K05 1-1 0
20120828 K04-K08 2-3 1
20120828 K09-K10 2-0 2
20120828 K01-K07 0-1 1
25 rows selected.
SQL>
* ABS 함수를 대체할 User Defined Function 생성
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 ;
CREATE Function dbo.UTIL_ABS
( @v_input int )
RETURNS int
AS
BEGIN
DECLARE @v_return int
SET @v_return = 0
IF @v_input < 0
SET @v_return = @v_input *-1
ELSE
SET @v_return = @v_input
RETURN @v_return ;
END
/
* 동일한 결과 출력
SQL> SELECT SCHE_DATE DA,
HOMETEAM_ID||'-'||AWAYTEAM_ID TEAMS,
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 ;
DA TEAMS SCORE UTIL_ABS(HOME_SCORE-AWAY_SCORE)
-------- ------- -------------------- -------------------------------
20120803 K08-K07 1-0 1
20120803 K01-K03 3-0 3
20120803 K06-K09 2-1 1
20120804 K05-K04 2-1 1
20120804 K10-K02 0-3 3
20120811 K09-K05 0-1 1
20120811 K03-K08 2-0 2
20120811 K07-K10 1-1 0
20120811 K01-K06 0-0 0
20120811 K04-K02 0-2 2
20120818 K06-K03 3-1 2
20120818 K02-K09 1-2 1
20120818 K08-K10 3-1 2
20120818 K04-K07 1-0 1
20120818 K05-K01 0-2 2
20120824 K08-K06 4-3 1
20120824 K05-K03 3-3 0
20120824 K02-K01 1-1 0
20120825 K10-K04 1-1 0
20120825 K09-K07 1-1 0
20120828 K03-K02 0-0 0
20120828 K06-K05 1-1 0
20120828 K04-K08 2-3 1
20120828 K09-K10 2-0 2
20120828 K01-K07 0-1 1
25 rows selected.
6. Trigger 의 생성과 활용
- 특정한 테이블에 INSERT,UPDATE,DELETE 와 같은 DML 문이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록 작성한 프로그램
- 즉 사용자가 직접 호출하여 사용하는 것이 아니고 데이터베이스에서 자동적으로 수행하게 됨
- 이벤트 발생 대상 : 테이블, 뷰, 데이터베이스
- 발생 범위 : 전체트랜잭션 작업, 각 행에 대해서 발생
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 );
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 ; -- NEW는 신규로 입력된 레코드의 정보를 가지고 있는 구조체, OLD는 수정, 삭제되지 전의 레코드를 가지는 구조체
O_PROD := :NEW.PRODUCT ; -- MS SQL : DML triggers use the deleted and inserted logical (conceptual) tables.
UPDATE SALES_PER_DATE << 3
SET QTY = QTY + :NEW.QTY, 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 ;
SQL> SELECT * FROM ORDER_LIST ;
no rows selected
SQL> SELECT * FROM SALES_PER_DATE ;
no rows selected
SQL> INSERT INTO ORDER_LIST VALUES ('20120901','MONOPACK',10,30000 );
1 row created.
SQL> COMMIT ;
Commit complete.
SQL> select * from order_list ;
ORDER_DA PRODUCT QTY AMOUNT
-------- ---------- ---------- ----------
20120901 MONOPACK 10 30000
SQL> select * from sales_per_date ;
SALE_DAT PRODUCT QTY AMOUNT
-------- ---------- ---------- ----------
20120901 MONOPACK 10 30000
* 트리거 테이블에 대한 롤백 확인
SQL> INSERT INTO ORDER_LIST VALUES ('20120901','MONOPACK',10,30000 );
1 row created.
SQL> SELECT * FROM ORDER_LIST ;
ORDER_DA PRODUCT QTY AMOUNT
-------- ---------- ---------- ----------
20120901 MONOPACK 10 30000
20120901 MONOPACK 10 30000
SQL> SELECT * FROM SALES_PER_DATE ;
SALE_DAT PRODUCT QTY AMOUNT
-------- ---------- ---------- ----------
20120901 MONOPACK 20 60000
SQL> rollback ;
Rollback complete.
SQL> SELECT * FROM ORDER_LIST ;
ORDER_DA PRODUCT QTY AMOUNT
-------- ---------- ---------- ----------
20120901 MONOPACK 10 30000
SQL> SELECT * FROM SALES_PER_DATE ;
SALE_DAT PRODUCT QTY AMOUNT
-------- ---------- ---------- ----------
20120901 MONOPACK 10 30000
7. 프로시저와 트리거의 차이점
