SQLD 4-8 ] 절차형 SQL
절차형 SQL 개요
- 일반적인 개발 언어처럼 SQL 도 절차 지향 적인 프로그램이 가능하도록 제공하고 있다.
ORACLE : PL/SQL
DB2 : PL/SQL
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
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 를 이용하여 모든 에러를 처리할 수 있지만. 정확하게 하는게 좋다.
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 절 내에 사용할 수 없다
댓글을 사용할 수 없습니다.