SQLD 4-8 ] 절차형 SQL
❏ 절차형 SQL 개요
- 일반적인 개발 언어처럼 SQL 도 절차 지향 적인 프로그램이 가능하도록 제공하고 있다.
ORACLE : PL/SQL
DB2 : SQL/PL
SQL SERVER : T-SQL
절차형 SQL 을 이용하여 만들수 있는 저장 모듈인 PROCEDURE / USER DEFINED RUNCTION / TRIGGER 에 대해 알아보자!
❏ PL/SQL
- BLOCK 구조로 되어 있고, BLOCK 내에는 DML 문장과, QUERY 문장, 절차형 언어(IF, LOOP) 등을 사용할 수 있다.
1. PL/SQL 은 BLOCK 구조로 되어 있어 각 기능별 모듈화가 가능하다
2. 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환한다
3. IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다
4. DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다
5. PL/SQL 은 ORACLE 에 내장되어 있으므로 ORACLE 과 PL/SQL 을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다
6. PL/SQL 은 응용 프로그램의 성능을 향상시킨다
7. PL/SQL 은 여러 SQL 문장을 BLOCK 으로 묶고 한 번에 BLOCK 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다
기본 문법
CREATE [OR REPLACE] Procedure [Procedure_name]
( argument1 [mode] data_type1,
argument2 [mode] data_type2,
... )
IS [AS]
...
BEGIN
...
EXCEPTION
...
END;
/OR REPLACE : 같은 이름 PROCEDURE 있으면 덮어써라
MODE
1. IN -> 운영체제에서 프로시저에 전달될 변수의 MODE
2. OUT -> 프로시저에서 처리된 결과가 운영체제로 전달되는 MODE
3. INOUT -> IN과 OUT 두 가지의 기능 동시 수행
/ : 데이터베이스에게 프로시저를 컴파일하라는 명령어
프로시저 삭제
DROP PROCEDURE [Procedure_name];
❏ T-SQL
- SQL SERVER 를 제어하기 위한 언어. MS 사에서 ANSI/ISO 표준의 SQL 에 약간의 기능을 추가해 보완적으로 만든 것
1. 변수 선언 기능 @@ 이라는 전역변수(시스템 함수)와 @이라는 지역변수가 있다.
2. 지역변수는 사용자가 자신의 연결 시간 동안만 사용하기 위해 만들어지는 변수이며
전역변수는 이미 SQL 서버에 내장된 값이다.
3. 데이터 유형을 제공한다. 즉 int, float, varchar 등의 자료형을 의미한다
4. 산술연산자(+,-,*,/), 비교연산자(=, <, >, <>), 논리연산자(and, or, not) 사용이 가능하다
5. IF-ELSE 와 WHILE, CASE-THEN 사용이 가능하다.
6. 한줄주석 : -- , 범위주석 : /* */ 사용이 가능하다.
MS 꺼라 그런지.. 오....
PL/SQL 이랑 같네.
T-SQL 의 문법
CREATE Procedure [schema_name.]Procedure_name
@parameter1 data_type1 [mode]
@parameter2 data_type2 [mode],
...
WITH <proc_option>
AS
...
BEGIN
...
ERROR
...
END;
생성은 CREATE, 변경은 ALTER
@parameter : 프로시저가 호출될 때 프로시저 안으로 어떤 값이 들어오거나 혹은 프로시저에서 처리한 결과 값을 리턴 시킬 매개변수
mode
1. VARYING : 결과 집합이 출력 매개변수로 사용되도록 지정한 CURSOR 매개변수에만 적용된다
2. DEFAULT : 지정된 매개변수가 프로시저를 호출할 당시 지정되지 않을 경우 지정된 기본값으로 처리한다
3. OUT, OUTPUT : 프로시저에서 처리된 결과 값을 EXECUTE 문 호출 시 반환한다
4. READONLY : 자주 사용되지는 않는데, 프로시저 본문 내에서 매개 변수를 업데이트 하거나 수정할 수 없을 때
proc_option
1. RECOMPILE : 현재 프로시저의 계획을 캐시하지 않고 프로시저가 런타임에 컴파일
2. ENCRYPTION : 원문 텍스트가 알아보기 어려운 형식으로 변환
3. EXECUTE AS : 해당 저장 프로시저를 실행할 보안 컨텍스트를 지정 { EXEC } | { CALLER | SELF | OWNER | 'user_name' }
프로시저 삭제
DROP PROCEDURE [schema_name.]Procedure_name;
❏ PROCEDURE 의 생성과 활용
앞으로 생성할 PROCEDURE 의 기능을 FLOW CHART 로 나타냈다. 이런거 하나 만들어보자..
SCOTT 유저가 쇼유하고 있는 DEPT 테이블에 새로운 부서를 등록하는 PROCEDURE 를 작성한다.
SCOTT 유저가 기본적으로 소유한 DEPT 테이블의 구조는 다음과 같다.
ORACLE
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;
/
SQL SERVER
CREATE PROCEDURE dbo.p_DEPT_insert --- ①
@v_DEPTNO int,
@v_DNAME varchar(30),
@v_LOC varchar(30),
@v_RESULT varchar(100) OUTPUT
AS
DECLARE @CNT int
SET @CNT = 0
BEGIN
SELECT @CNT=COUNT(*) --- ②
FROM DEPT
WHERE DEPTNO = @v_DEPTNO
IF @CNT > 0 --- ③
BEGIN
SET @v_RESULT = '이미 등록된 부서번호'
RETURN
END
ELSE
BEGIN
BEGIN TRAN
INSERT INTO DEPT (DEPTNO, DNAME, LOC) --- ④
VALUES (@v_DPETNO, @v_DNAME, @v_LOC)
IF @@ERROR<>0
BEGIN
ROLLBACK --- ⑥
SET @v_RESULT = 'ERROR'
RETURN
END
ELSE
BEGIN
COMMIT --- ⑤
SET @v_RESULT = '입력완료'
RETURN
END
END
END
DB 에서 코드 작성하는데까지 오다니..
1. p_DEPT_insert 프로시저를 만든다. 옵션( OR REPLACE ) 가 있으니. 중복되면 덮어 쓰고.
4개의 인자로 만들었네. in 3개, out 1개. in 은 SQL 문장 칠때 그때의 인자라고 생각하면 되고, out 은 내부에서 운영체제 쪽으로 어떤 것을 리턴 해주는 인자.
IS 에는 변수나 상수 쓰는곳이라고 해석했다. CNT 라는 변수명 의 타입은 number 이고 값은 0.
2. 선언할거 다 선언했으니. 하고싶은게.. 부서가 존재하는지 CHECK -> 있으면 안되고 없으면 넣고. 이 과정을 해야한다.
읽는 순서는 항상 select 는 마지막에 보자. FROM 먼저. DEPT 테이블에서 DEPTNO 랑 여기 v_DEPTNO 가 같고
select COUNT(*) 한것을 INTO CNT 변수에 넣어라.
3. IF CNT > 0 이면 내용이 있다는 거니까 THEN 에 v_RESULT 에 등록됬다고 넣고
4. 아니면 없는거니까 INSERT 실행하자
5. COMMIT !
6. 예외가 발생하면 이곳에서 처리하자. 모든 트랜잭션을 취소하고 ERROR 라는 메시지를 v_RESULT 에 넣는다.
주의해야할 요소
| 1. 예제에서 나온 CNT 변수를 SCALAR 변수라고 한다. SCALAR 변수는 임시 데이터를 하나만 저장할 수 있다. 2. PL/SQL 에서 사용하는 SELECT 문장의 결과는 무조건 있어야 한다. 그 결과 역시 하나여야 한다. 그래야 집어넣으니까! INTO 할때. 3. T-SQL 을 비롯하여 일반적으로 대입 연산자는 = 이지만, PL/SQL 에서는 := 를 사용한다 4. EXCEPTION 에서는 WHEN ~ THEN 절을 이용하여 에러를 처리한다. OTHERS 를 이용하여 모든 에러를 처리할 수 있지만. 정확하게 하는게 좋다. |
1. DEPT 테이블 원래 들어있는거
2. PROCEDURE 를 실행한 결과를 담아 둘 변수를 하나 선언했다. rslt 라는 이름의 varchar2(30) 타입. (BIND 변수)
3. EXEC 는 실행하는거. 이미 존재하는 DEPTNO 10 을 넣어봤다 한번. 마지막에 :rslt 변수 보면. ':' 이게 좀 알아둬야할것같네.
뭐 어쨌든 실행은 됬다고 하겠지.
4. rslt 에는 뭐가 담겼을까 봤더니 이미 등록된 부서번호 라고 떴네.
5. 그럼 제대로 해보자. DEPTNO 50 은 없으니. 실행 됬다고는 한다.
6. 결과값을 보자. 입력완료!! 라고 되있네.
7. 오. 들어가있다.
❏ USER DEFINED FUNCTION 의 생성과 활용
- PROCEDURE 비슷 하긴 한데.. 다른 점은 RETURN 을 사용해서 하나의 값을 반드시 되돌려 줘야 하는 것!!
K-리그 8월 경기결과와 두 팀간의 점수차를 ABS 함수를 사용하여 절대값으로 출력한다.
ORACLE
select SCHE_DATE 경기일자,
HOMETEAM_ID || '-' || AWAYTEAM_ID as 팀들,
HOME_SCORE || '-' AWAY_SCORE as SCORE,
ABS(HOME_SCORE - AWAY_SCORE) as 점수차
from SCHEDULE
where GUBUN = 'Y'
and SCHE_DATE between '20120801' and '20120831'
order by SCHE_DATE;여기서 절대값을 구하는 ABS 함수를 손수 만들어보자. 사용자 정의 함수로.
PROCEDURE 랑 구성 내용(구조)은 비슷한데. 목적이 다르구나. 한번 봅시다
ORACLE
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;
/1. input 을 숫자 타입 하나 받네. 반대로 무조건 하나 나간다고 했으니까 그 return 타입도 number 형 선언.
2. 변수 v_return 선언
3. 들어온게 음수면 * -1 해서 v_return 에 넣고 정수면 그냥 넣고
4. v_return 리턴
내가 만든 함수를 써보자
select SCHE_DATE 경기일자,
HOMETEAM_ID || '-' || AWAYTEAM_ID as 팀들,
HOME_SCORE || '-' AWAY_SCORE as SCORE,
UTIL_ABS(HOME_SCORE - AWAY_SCORE) as 점수차
from SCHEDULE
where GUBUN = 'Y'
and SCHE_DATE between '20120801' and '20120831'
order by SCHE_DATE;
❏ 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);
ORACLE
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 --- 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;
/
1. CREATE TRIGGER 으로 SUMMARY_SALES 라는 트리거를 만들었고
AFTER INSERT : 레코드가 입력된 후 트리거가 발생
ON ORDER_LIST : ORDER_LIST 테이블에 트리거 설정
FOR EACH ROW : 각 ROW 마다 트리거 적용
SELECT * FROM ORDER_LIST;
선택된 레코드가 없다.
SELECT * FROM SALES_PER_DATE;
선택된 레코드가 없다.
INSERT INTO ORDER_LIST VALUES('20120901', 'MONOPACK', 10, 300000);
1개의 행이 만들어졌다.
COMMIT;
커밋이 완료되었다.
 |
SALES_PER_DATE 에도 들어갔네?
다시한번 같은 데이터를 입력해보자
 |
오..
이번에는 다른 상품으로 주문해보고
트랜잭션 롤백 했는데 과연 결과는 ?!
 |
롤백 해도 하나의 트랜잭션으로 인식해서 두 테이블 모두 입력취소가 됬다.
트리거는 데이터베이스에 의해 자동 호출되지만 결국 INSERT, UPDATE, DELETE 문과 하나의 트랜잭션 안에서 일어나는 작업이였다
❏ 프로시저와 트리거의 차이점
- 프로시저는 BEGIN ~ END 절 내에 COMMIT, ROLLBACK 쓸 수 있는데
트리거는 BEGIN ~ END 절 내에 사용할 수 없다
