2장_8절. 절차형SQL
1.절차형 SQL개요
-SQL은 기본적으로 관계형이다. 하지만 절차형 요소를 사용하면 연속적실핵이나 조건에 따른 분기처리를 할 수 있다.
2.PL/SQL개요
가. PL/SQL의 특징
-오라클의 PL/SQL은 블록구조로 되어있다.
-블록내에는 DML, 쿼리, 절차형언어(IF,LOOP)를 사용할 수 있다.
-따라서 블록은 절차적 프로그래밍이 가능한 트랜잭션언어이다.
-따라서 블록을 사용하여 다양한 저장모듈(Stored Module)을 개발할 수 있다.
-저장모듈이랑 PL/SQL문장을 DB서버에 저장하여 사용자와 APP사이에서 공유할 수 있도록 만든 컴포넌트 프로그램이다.
-독립적으로 실행될 수 있는 완전한 S/W이다.
-오라클의 저장모듈에는 프로시져(Procedure), 유저정의함수(User Defined Function), 트리거(Trigger)가 있다.
나. PL/SQL구조
오라클에서 SQL 언어를 확장하기위해 사용하는 언어로서 SQL만으로는 처리하기 힘든 자료저장,프로시저,트리거등을 작성할대 쓰이게된다.
선언(DECLARE) ---- 데이터변수등의 크기,형등을 선언한다.
실행(BEGIN) ---- SELECT,INSERT,UPDATE같은 DML문이나 조건이나 제어문을 사용한다.
예외처리(EXCEPTION) ---- 예외를 처리한다.
종료(END)
다. PL/SQL문법
CREATE[OR REPLACE] Procedure [프로시져이름]
(파라미터1[mode] 데이터타입1, 파라미터2[mode] 데이터타입2,...)<-선언
IS [AS]
... ...
BEGIN<-실행
... ...
EXCEPTION<-예외처리
... ...
END;<-종료
/
#프로시저의 삭제
DROP Procedure [프로시져이름];
3. T-SQL개요
가.T-SQL특징
-T-SQL은 근본적으로 SQL Server를 제어하기 위한 언어이다.
나.T-SQL구조
선언(DECLARE) ---- 데이터변수등의 크기,형등을 선언한다.
실행(BEGIN) ---- SELECT,INSERT,UPDATE같은 DML문이나 조건이나 제어문을 사용한다.
예외처리(EXCEPTION) ---- 예외를 처리한다.
종료(END)
다. T-SQL문법
CREATE Procedure [스키마이름,] 프로시져이름
@파라미터1 데이터타입1[mode]
@파라미터2 데이터타입2[mode] ,...)
WITH < PORC_POTION><-선언
AS
... ...
BEGIN<-실행
... ...
ERROR<-예외처리
... ...
END;<-종료
#프로시저의 삭제
DROP Procedure [스키마이름,] 프로시져이름;
4. 프로시져의 생성과 활용
(1)프로시져의 생성
=>새로운 부서를 등록하는 프로시져를 만들자.
SQL> CREATE OR REPLACE Procedure p_DEPT_insert---------------(1)
2 (v_DEPTNO in number,
3 v_dname in varchar2,
4 v_loc in varchar2,
5 v_result out varchar2)
6 IS
7 cnt number :=0;
8 BEGIN
9 SELECT COUNT(*) INTO CNT----------------------------(2)
10 FROM DEPT
11 WHERE DEPTNO = v_DEPTNO
12 AND ROWNUM = 1;
13 if cnt > 0 then ---------------------------------------(3)
14 v_result :='이미 등록된 부서번호이다.';
15 else
16 INSERT INTO DEPT (DEPTNO, DNAME, LOC)---------------(4)
17 VALUES(v_DEPTNO, v_dname, v_loc);
18 COMMIT;------------------------------------------(5)
19 v_result :='입력완료!';
20 end if;
21 EXCEPTION-------------------------------------------(6)
22 WHEN OTHERS THEN
23 ROLLBACK;
24 v_result :='ERROR 발생';
25 END;
26 /
Procedure created.
(1)DEPT테이블에 들어갈 칼럼값(부서코드, 부서명, 위치)을 입력받는다.
(2)입력 받은 부서코드가 존재하는지 확인한다.
(3)부서코드가 존재하면 '이미 등록된 부서번호입니다'라는 메시지를 출력값에 넣는다.
(4)부서코드가 존재하지 않으면 입력받은 필드 값으로 새로운 부서 레코드를 입력한다.
(5)새로운 부서가 정상적으로 입력됐을 경우에는 COMMIT명령어를 통해 트랜잭션을 종료한다.
(6)에러가 발생하면 모든 트랜잭션을 롤백하고 'ERROR 발생'이라는 메시지를 출력값에 넣는다.
(2)프로시져 실행
=>새로운 부서를 등록하는 프로시져를 실행하자.
실행후
SQL> variable rslt varchar2(30); --------------------------------------(2)
SQL> EXECUTE p_DEPT_insert(10,'dev','seoul',:rslt); -----------------------(3)
PL/SQL procedure successfully completed.
SQL> PRINT RSLT; ------------------------------------------------(4)
RSLT
------------------------------------------------------------------
이미 등록된 부서번호이다.
SQL> EXECUTE p_DEPT_insert(50,'NewDev','seoul',:rslt);---------------------(5)
PL/SQL procedure successfully completed.
SQL> PRINT RSLT;-------------------------------------------------(6)
RSLT
-------------------------------------------------------------------
입력완료!
SQL> SELECT * FROM DEPT;-----------------------------------------(7)
(2)프로시져를 실행한 결과 값을 받을 변수를 선언한다(BIND변수)
(3)존재하는 DEPTNO(10)을 가지고 프로시져를 수행한다.
(4)DEPTNO가 10인부서는 이미 존재하기 때문에 변수 RSLT를 PRINT해보면 '이미 등록된 부서'라고 출력된다.
(5)이번에는 새로운 DEPTNO인 50을 가지고 프로시져를 수행한다.
(6)RSLT를 출력해보면 '입력완료!'가 출력된다.
(7)DEPT테이블을 조회하면 DEPTNO가 50인 데이터가 정확하게 저장되었다.
5. User Defined Function의 생성과 활용
-사용자가 직접 만드는 함수.
-예를들어 SUM함수등도 다 프로시져로 되어있는데 내장되어 있는 것일 뿐임.
-함수와 프로시져가 다른점은 RETURN을 이용해서 하나의 값을 반드시 되돌려 줘야한다는 것이다. 함수는 RETURN값이 있어야 한다. 프로시져는 RETURN값이 없어도 된다. 있어도된다.
6. Trigger의 생성과 활용
-트리거란 특정 테이블에 insert,update, delete와 같은 DML(데이터조작어)가 수행됬을때, 데이터베이스에서 자동으로 동작하도록 작성된 프로그램이다.
-사용자가 직접 함수처럼 EXECUTE로 호출하는 것이 아니고, DBMS에서 자동으로 수행하는 것이다.
-트리거는 테이블, 뷰, 데이터베이스 작업을 대상으로 정의할 수 있다.
-트리거는 전체 트랜잭션 작업기준으로 발생하는 트리거, 각 행 작업기준으로 발생하는 트리거가 있다.
댓글
댓글 쓰기