> # PL/SQL (Procedual Language SQL)
-
표준 SQL에 기능을 확장한 문법제공(분기,반복, 변수등)
-
미리 컴파일되어 저장되기 때문에 처리속도와 효율성 증가
-
모듈화 및 캡슐화 기능 제공
-
표준 문법이 없음
-
각 DBMS에 종속적
-
익명블록(Anonymous block), Procedure, Function, Trigger, Package 등 제공됨
1. 익명블록
-
단순 스크립트에서 실행되는 블록(이름이 없음)
-
저장불가
-
pl/sql의 기본구조
(사용형식)
DECLARE
선언부; --변수, 상수, 커서 선언
BEGIN
실행부;-- 문제해결을 위한 비즈니스 로직 처리 SQL문
[EXCEPTION
예외처리부;]
END;
사용예) 키보드로 부서번호를 입력 받아 해당부서의 주소, 인원수, 평균급여를 출력하는 익명블록을 작성하시오
ACCEPT P_DEPT_NO PROMPT '부서코드 입력(10-110) : '
DECLARE
V_DEPT_NO HR.DEPT.DEPARTMENT_ID%TYPE := TO_NUMBER('&P_DEPT_NO'); --% 참조타입: 해당되어지는 컬럼의 데이터타입과 똑같은 변수선언가능
V_CNT NUMBER:=0; --NUMBER 타입 초기화하지 않고 사용하면 결과안나오기 때문에 초기화
V_ASAL NUMBER:=0;
V_ADDR VARCHAR2(200);
BEGIN --BEGIN절에서만 SELECT-INTO-FROM절 사용
SELECT A.STREET_ADDRESS||A.CITY||' '||A.STATE_PROVINCE
INTO V_ADDR
FROM HR.LOCATIONS A, HR.DEPT B
WHERE A.LOCATION_ID=B.LOCATION_ID
AND B.DEPARTMENT_ID=V_DEPT_NO;
SELECT COUNT(EMPLOYEE_ID),
ROUND(AVG(SALARY))
INTO V_CNT, V_ASAL
FROM HR.EMP
WHERE DEPARTMENT_ID=V_DEPT_NO;
--DBMS_OUTPUT.PUT_LINE 자바에서의 SOP랑 동일
DBMS_OUTPUT.PUT_LINE('부서번호 : '||V_DEPT_NO);
DBMS_OUTPUT.PUT_LINE('주소 : '||V_ADDR);
DBMS_OUTPUT.PUT_LINE('인원수 : '||V_CNT);
DBMS_OUTPUT.PUT_LINE('평균급여 : '||V_ASAL);
END;
1) 변수와 상수
- SCALR 변수, REFERENCES 변수제공
(선언형식)
변수(상수)명 [CONSTANT] 데이터타입|참조타입 [:=초기값]; --반드시 초기값을 지정- 데이터 타입
. 표준 오라클에서 사용하는 데이터 타입
. PLS_INTEGER, BINARY_INTEGER : 4 BYTE 정수(-2147483648~2147483647)
. BOOLEAN 타입(true, false, null)- 참조타입
. 열참조타입: 테이블명.컬럼명%TYPE
. 행참조타입: 테이블명%ROWTYPE --자바의 ArrayList와 같음
사용예) 임의의 부서코드를 발생시켜 해당 부서에 속한 사원 중 가장 많은 급여를 받는 사원하나의
사원번호, 사원명, 입사일, 직책코드, 급여를 출력하는 익명블록을 작성하시오
DECLARE
V_EMP_ID HR.EMP.EMPLOYEE_ID%TYPE;
V_ENAME HR.EMP.EMP_NAME%TYPE;
V_JOB HR.EMP.JOB_ID%TYPE;
V_SAL HR.EMP.SALARY%TYPE;
V_DEPT_ID HR.DEPT.DEPARTMENT_ID%TYPE;
BEGIN
V_DEPT_ID:=TRUNC(DBMS_RANDOM.VALUE(10,110),-1); --정수형 난수 발생.
SELECT A.EMPLOYEE_ID, A.EMP_NAME, A.JOB_ID, A.SALARY
INTO V_EMP_ID, V_ENAME, V_JOB, V_SAL
FROM(SELECT EMPLOYEE_ID, EMP_NAME, JOB_ID, SALARY
FROM HR.EMP
WHERE DEPARTMENT_ID = V_DEPT_ID
ORDER BY 4 DESC) A
WHERE ROWNUM=1;
DBMS_OUTPUT.PUT_LINE('사원번호 :' ||V_EMP_ID);
DBMS_OUTPUT.PUT_LINE('사원명 :' ||V_ENAME);
DBMS_OUTPUT.PUT_LINE('직책 :' ||V_JOB);
DBMS_OUTPUT.PUT_LINE('부서코드 :' ||V_DEPT_ID);
DBMS_OUTPUT.PUT_LINE('급여 :' ||V_SAL);
END;
사용예) 2005년 7월 29일 'h001' 회원이 'P202000012' 제품을 2개구입 했을 경우 이 정보를 CART 테이블에 저장하시오.
--COUNT로 구매여부 조회후 1이면 UPDATE 0이면 INSERT
DECLARE
V_CNT NUMBER:=0; --V_CNT는 행의 수를 체크해서 보관할 변수. 거래가 없으면 초기값인 0의값을 가짐
V_CART_NO CART.CART_NO%TYPE;
BEGIN --젤먼저 검색할것은 2005년7월29일에 거래가 있었나 확인
SELECT COUNT(*) INTO V_CNT
FROM CART
WHERE CART_NO LIKE '20050729%';
IF V_CNT=0 THEN
V_CART_NO:='2005072900001'; -- 거래가 없다면 CART_NO에 '2005072900001' 부여
ELSE -- 거래가 있다면
SELECT CART_NO INTO V_CART_NO --V_CART_NO=1
FROM CART
WHERE CART_MEMBER='h001' -- 'h001'회원이 'P202000012' 상품을 구매한 이력이 있는지. 있으면 UPDATE 없으면 INSERT
AND SUBSTR(CART_NO,1,8) = '20050729'
AND CART_PROD='P202000012';
END IF;
IF V_CART_NO IS NULL THEN
INSERT INTO CART
SELECT 'h001', MAX(CART_NO)+1, 'P202000012',2 --카트번호가 증가되어야함. 카트번호중 젤 큰번호를 찾음
FROM CART
WHERE CART_NO LIKE '20050729%';
ELSIF V_CNT !=0 THEN --20050729에 판매흔적있고 P202000012를 구매하였다면 = 1번이아니면 수량만UPDATE
UPDATE CART
SET CART_QTY:=CART_QTY+2
WHERE CART_MEMBER='h001'
AND SUBSTR(CART_NO,1,8) = '20050729'
AND CART_PROD='P202000012';
ELSIF AND V_CNT=0 THEN --20050729에 판매흔적은 있지만 P202000012를 구매한적이 없다면 INSERT
INSERT INTO CART
VALUES('h001', V_CART_NO, 'P202000012', 2);
END IF;
END;
삐약..뺙뺙