DBMS에 데이터를 정의하고 저장된 데이터를 읽어와 데이터를 변경하는 프로그램을 작성하는 과정
오라클에서는PL/SQL 언어를 사용하여, SQL Server는 T-SQL 이라는 언어를 사용
호스트 언어가 JSP, ASP, PHP 등의 웹 스크립트 언어인 경우 사용
PL/SQL의 종류
Procedure (프로시저)
Trigger (트리거)
Function (사용자 정의 함수로 구성됨)
프로시저(Procedure)
메소드처럼 빈번하게 사용하는 SQL 명령어를프로시저로 만들어서사용 가능
프로시저를 정의하려면 'CREATE PROCEDURE' 문을 사용함 선언부와 실행부(BEGIN - END)로 구성
1. 프로시저 생성
CREATE OR REPLACE PROCEDURE 프로시저명( 매개변수IN자료형 -> 입력 용도 매개변수IN자료형 -> 입력 용도 매개변수IN자료형 -> 입력 용도 ...... [매개변수OUT자료형] -> 출력 용도( OUT 을 쓸 경우 DECLARE로 출력 해야함 ) ...... ) AS 지역변수 BEGIN 동작 (INSERT .. ~) INSERT : 일반INSERT 문 처럼 사용 DELETE : 일반 DELETE 문 처럼 사용 SELECT: 해당 결과 값을 지역변수를 선언하고 INTO로 저장해야함 ex) SELECT 속성 INTO 지역변수 END; /
CREATE OR REPLACE PROCEDURE insertCustomer(
p_custid IN NUMBER,
p_name IN VARCHAR2,
p_address IN VARCHAR2,
p_phone IN VARCHAR2
)
AS
BEGIN
INSERT INTO CUSTOMER VALUES(p_custid,p_name,p_address,p_phone);
END;
/
2. dbms_output.put_line (AS문에서 설정한 출력할 값이 저장된 변수명)
3.set serveroutput on; 입력 하여 설정해줄 것
동작 개념 SELETE 의 결과가 dloc에 담겨있으니 해당 값을 AS문에서 선언한 변수인 p_dloc로 저장 후 출력하는 개념
4. 오류 확인 명령어
SHOW ERRORS
5. DECLARE
( 매개변수OUTNUMBER ) .... ..... .....
DECLARE 변수선언 begin 명령어 end; /
- 매개변수에 OUT 을 써줬기 때문에 변수와 출력문을 DECLARE 에서 따로 선언
고객 번호를 입력 받아 총 구매 가격을 출력
DECLARE 부분에서 result 변수의 역할
= custOrder 함수의 실행 결과를 담을 변수
CREATE OR REPLACE PROCEDURE custOrder(
p_custid IN NUMBER,
p_printprice OUT NUMBER
)
AS
BEGIN
SELECT SUM(saleprice) INTO p_printprice
FROM CUSTOMER c, ORDERs o
WHERE o.custid = c.CUSTID
AND c.custid = 1;
END;
/
DECLARE
result NUMBER;
BEGIN
custOrder(1, result);
dbms_output.put_line(result);
END;
6. CURSOR
프로시저 안에서select 한 행의 수가여러 건(복수)이라면cursor를 사용 해야 함.(하나의 행씩 가져와서 동작하도록 함.)
AS문에 'CURSOR' 선언
AS문에 데이터를 저장할 '변수'를 선언
'CURSOR'를 순회(LOOP) 하면서 한 행씩 뽑아(FETCH)하여 변수에 '저장'
EXIT WHEN으로 LOOP 문 탈출 가능
AS문에서는SELECT를 할때 INTO 써 줄 필요 없음.(BEGIN에서 FETCH 할 때 INTO 사용 해야함)
AS 변수명 자료형; CURSOR커서이름 ISSelect 문 BEGIN OPEN 커서이름; --> 커서 동작 LOOP -->반복하면서 복수의 자료들을 뽑기위한 루프문 FETCH 커서이름 INTO 변수명; --> 커서에 있는 자료들을 가져와 변수에 저장 EXIT WHEN 커서이름%NOTFOUND ; --> 커서에 더이상 자료가 없으면 EXIT dbms_output.put_line(변수명) ; --> EXIT 하지 않았을 경우 '변수명' 출력 END LOOP; CLOSE 커서이름;
관리자의 이름을 전달받아 부하 직원을 출력 (CURSOR 사용)
- 부하직원이 여러 명 반환되므로 cursor를 써야함
/* 쿼리문 */
SELECT e.ename
FROM emp e, emp m
WHERE e.MGR = m.eno
AND m.ename = '관리자명';
/* Procedure */
CREATE OR REPLACE PROCEDURE printEmpMtoC(
p_mname varchar2
)
AS
p_cname VARCHAR2(20);
CURSOR cn IS SELECT e.ename FROM emp e,
emp m WHERE e.MGR = m.eno AND m.ename = p_mname;
BEGIN
OPEN cn;
LOOP
FETCH cn INTO p_cname;
EXIT WHEN cn%NOTFOUND;
dbms_output.put_line(p_cname);
END LOOP;
CLOSE cn;
END;
/
트리거(Trigger)
DML 이벤트(insert, update, delete)가 발생할 때 자동으로 동작하는 프로시저
연쇄하여다른 테이블에도 적용할 필요가존재할 때 사용
프로시저는 직접 호출해야 하는데 트리거는 해당 시점에 자동으로 동작
CREATE OR REPLACE TRIGGER 트리거이름 시점 : BEFORE / AFTER / INSTREA OF (얜 View 용) 명령어 : INSERT UPDATE [OF] [컬럼명] - 만약 [OF] [컬림명]이 붙으면 해당 컬럼의 데이터에 변경이 올 경우 동작 DELETE ON 테이블명 FOR EACH ROW WHEN 조건 DECLARE 변수선언 BEGIN 트리거가 해야할 명령어(들)
[IF문도 사용가능] if UPDATING THEN 속성명 :='변경 값'; elsif DELETING THEN 속성명 :='변경 값2'; END IF;
END; /
시스템 함수 UPDATING = 수정 되었다면 1반환 , if 문 같은데 사용함 시스템 함수 DELETING = 삭제 되었다면 1반환 , if 문 같은데 사용함
:new 와:old
이해하려고 하지말고 외울부분이 있는데,
- UPDATE 또는 DELETE 실행 되기 전에 데이터가 잠깐? :OLD 테이블 로 넘어간다고 생각할 것
- INSERT 실행 되기 전에는 데이터가 :NEW 테이블로 넘어간다고 생각할 것
한 레코드(행)을 기준으로 생각하면 이해하기 쉽다 만약 한 행을 DELETE 하면 현재 기준(=NEW 기준)에는 한 행이 DELETE 되었기 때문에 당연히 NEW(현재 행)가 존재할 수가 없다. 출처 : https://www.youtube.com/watch?v=0KxIvQRPIIs
구분
:NEW
:OLD
INSERT
추가한레코드
없음
UPDATE
수정 후레코드
수정 전 레코드
DELETE
없음
삭제 전레코드 이미 지워진레코드
:new 명령어가 실행된 새로운 레코드(행)를 의미 (INSERT, UPDATE) 만약 INSERT 가 되었다면 INSERT 되고난 후의 레코드(행) 만약 UPDATE 되었다면 UPDATE 되고난 후의 레코드(행) 만약 DELETE 되었다면 이미 삭제되었으니 없음
:old 명령어가 실행되기 전의 레코드 (UDPATE, DELETE) 만약 UPDATE 되었다면 UPDATE 전의 레코드(행) 만약 DELETE 되었다면 DELETE 전의 레코드(행) 만약 INSERT 되었다면 이전 데이터는 당연히 없으니 없음
아래 예시 설명
DEPT 테이블에 INSERT가 일어한 후(AFTER) 해당 행을 가져오고, BEGIN 구문을 실행해라
CREATE OR REPLACE TRIGGER trg_dept_insert
AFTER INSERT ON DEPT FOR EACH ROW
DECLARE
BEGIN
INSERT INTO DEPT_BACK VALUES(:NEW.DNO, :NEW.DNAME, :NEW.DLOC );
END;
/
트리거 조회하기
SELECT * FROM ALL_TRIGGERS;
/*쉬운 예시*/
SELECT * FROM all_triggers WHERE OWNER LIKE '%##%'
트리거 삭제
DROP TRIGGER 트리거명;
PL/SQL 조건식
IF 조건식 THEN ... ELSE ... END IF
책 이름이 존재한다면 가격을 'UPDATE'
책 이름이 존재하지 않는다면 'INSERT'
CREATE OR REPLACE PROCEDURE BookInsertOrUpdate(
p_bookid IN number,
p_bookname IN VARCHAR2,
p_publisher IN varchar2,
p_price IN varchar2
)
AS
mycount NUMBER;
BEGIN
SELECT COUNT(*) INTO mycount FROM book WHERE bookname=p_bookname;
IF mycount !=0 THEN
UPDATE BOOK SET price = p_price WHERE bookname =p_bookname;
ELSE
INSERT INTO book VALUES(p_bookid, p_bookname, p_publisher, p_price);
END IF;
END;
/
주문이 발생하면 상품 테이블의 재고 수량이 감소하는 트리거
CREATE TABLE 주문(
주문번호 NUMBER PRIMARY key,
상품번호 NUMBER REFERENCES 상품(상품번호),
고객번호 NUMBER,
주문수량 NUMBER,
주문일 DATE
);
------- 주문이 발생하면 상품 테이블의 재고 수량이 감소하는 트리거 -------
CREATE OR REPLACE TRIGGER iforderedDecrease재고수량
AFTER INSERT ON 주문 FOR EACH ROW
DECLARE
BEGIN
UPDATE 상품 SET 재고수량 = 재고수량 - :NEW.주문수량
WHERE :new.상품번호 = 상품번호;
END;
/
INSERT INTO 주문 VALUES(1, 100, 1, 10, sysdate);
SELECT * FROM 상품;
SELECT * FROM 주문;
결과
BEFORE 가 꼭 와야할 경우?
예시 자식 테이블에 INSERT가 발생하였음 헌데, INSERT 값이 부모 테이블로 반드시 참조되는 컬럼인 경우에는 부모 테이블에 없다면 추가할 수 없다. 해당 경우에는 먼저 실행되어 부모테이블에 레코드를 추가 해야 할 경우 이때에는 before 시점이 와야 한다.
PL/SQL의 대입 연산자
:= (콜론 =)
PL/SQL의 문자열 + 연산자
|| (파이프 파이프)
FUNCTION (사용자 정의함수)
사용자가 select 절에 사용할 수 있는 사용자가 필요한 함수를 만들 수 있음
SUM, MAX, MIN, COUNT 이러한 함수들은 모두 select절에 사용할 수 있음
select 절에 사용되어야 하니 반드시 반환 값이 필요
CREATE OR REPLACEFUNCTION함수명(매개변수명 자료형)RETURN자료형 IS 변수선언 BEGIN 함수가 해야 할 문장(들) RETURN값: END;
판매금액을 별 이익금 반환 함수
/* 판매금액을 매개변수로 전달받아 이익금을 반환하는 함수
판매금액이 30000원이상이면 10% 그렇지 않으면 5%가 이익금.*/
create or replace function margin(saleprice number)
return number
is
result number;
begin
if saleprice >= 30000 then
result := saleprice * 0.1;
else
result := saleprice * 0.05;
end if;
return result;
end;
/
SELECT orderid, saleprice, margin(saleprice) FROM orders;
댓글