PL/SQL (Procedual Language SQL)
- 표준 SQL의 기능을 확장한 문법제공(분기, 반복, 변수 등)
- 미리 컴파일되어 저장되기 때문에 처리속도와 효율성 등가
- 모듈화 및 캡슐화 기능 제공
- 표준 문법이 없음
- 각 DBMS에 종속적
- 익명블록(Anonymous block),Procedure,Function, Trigger, Package 등 제공됨
- 익명블록
-
단순 스크립트에서 실행되는 블록(이름이 없음)
-
저장 불가(검증용으로 사용됨)
-
pl/sql의 기본 구조
(사용형식)
DECLARE
선언부: --변수, 상수, 커서 선언
BEGIN
실행부: --문제해결을 위한 비지니스로직 처리 SQL문
[EXCEPTION
예외처리부;]
END;사용예) 키보드로 부서번호를 입력 받아 해당부서의 주소, 인원수, 평균급여를 출력하는 익명블록을 작성하시오
ACCEPT P_DEPT_NO PROMPT '부서코드 입력(10-110) : ' --입력받은 것을 P_DEPT_NO에 저장
DECLARE
V_DEPT_NO HR.DEPT.DEPARTMENT_ID%TYPE := TO_NUMBER('&P_DEPT_NO');--'&'는 변수에 있는 값을 참조할때 사용하는 기호(''안에 써야하며 이는 문자열)
--무슨 타입으로 선언됐는지는 모르지만 컬럼명을 알때 변수를 선언한 것 HR.DEPT.DEPARTMENT_ID%에 같은 TYPE으로 있는 V_DEPT_NO을 선언해주세요(참조타입)
V_CNT NUMBER:=0;
V_ASAL NUMBER:=0;
V_ADDR VARCHAR2(200);
--지역변수라 초기화 했다고 생각(NUMBER는 더욱 초기화 해줘야함) / 초기화하지 않으면 NULL이 자동으로 들어가고 결과값은 나오지 않음
BEGIN --BEGIN안에서만 SELECT, FROM, INTO가 쓰임
SELECT A.STREET_ADDRESS||' '||A.CITY||' '||A.STATE_PROVINCE
INTO V_ADDR --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),--1 ROUND(AVG(SALARY))--2 INTO V_CNT, V_ASAL--1,2할당 FROM HR.EMP WHERE DEPARTMENT_ID = V_DEPT_NO; DBMS_OUTPUT.PUT_LINE('부서번호 : '||V_DEPT_NO); --LINE: 줄바꿈 DBMS_OUTPUT.PUT_LINE('주소 : '||V_ADDR); DBMS_OUTPUT.PUT_LINE('인원수 : '||V_CNT); DBMS_OUTPUT.PUT_LINE('평균급여 : '||V_ASAL);END;
1) 변수와 상수
- SCALR 변수, REFERENCE 변수제공
(선언 형식)
변수(상수)명 [CONSTANT]데이터타입|참조타입 [:=초기값]; - 데이터 타입
. 표준 오라클에서 사용하는 데이터 타입
. PLS_INTEGER, BINARY_INTEGER : 4 BYTE 정수(-2147483648~2147483647)
. BOOLEAN 타입(true, false, null)
-참조타입
.열참조타입 : 테이블명.컬럼명%TYPE
.행참조타입 : 테이블명%ROWTYPE --자바의 AarryList와 c언어의 구조체와 비슷 (사용예)임의의 부서코드를 발생시켜 해당 부서에 속한 사원 중 한 직원의 급여를 받는 사원의
사원번호, 사원명, 입사일, 직책코드, 급여를 출력하는 익명블록을 작성하시오 DECLARE --변수 선언 방법이 중요
V_ENP_ID HR.EMP.EMPLOYEE_ID%TYPE;
V_ENAME HR.EMP.ENP_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); --(하한값, 상한값): 10번부터 100번까지 정수형 난수를 발생 시킴(TRUNC: 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
AND ROWNUM = 1; --바깥족에서 가장 많은 사람 하나 DBMS_OUTPUT.PUT_LINE('사원번호 : '||V_EMP_ID);
DBMS_OUTPUT.PUT_LINE('사원명 : '||V_ENAME);
DBMS_OUTPUT.PUT_LINE('부서코드 : '||V_DEPT_ID);
DBMS_OUTPUT.PUT_LINE('직책 : '||V_JOB);
DBMS_OUTPUT.PUT_LINE('급여 : '||V_SAL);
END;
COMMIT; 사용예) 2005년 7월 29일 'h001'회원이 'P202000012' 제품을 2개 구입했을 경우 이 정보를 CART테이블에 저장하시오.
-- CART에 저장되어질때 카트번호가 필요함(카트 번호: 이미 부여되어진 카트번호보다 하나 높은게 부여되야됨)
-- 데이터 갯수를 새어서 가지고 있어야해서 F,T를 갖는게 아니라 0,1,2,3을 가지는게 유리함
-- 1. 2005년 7월 29일에 매출이 발생했나 안했나 확인해봐야함(확인결과 없음) DECLARE
V_CNT NUMBER:=0; --V_CNT행의 수 보관
V_CART_NO CART.CART_NO%TYPE;
BEGIN
SELECT COUNT(*) INTO V_CNT
FROM CART
WHERE CART_NO LIKE '20050729%';
SELECT *IF V_CNT = 0 THEN -- V_CNT값이 0이면 V_CART_NO:='2005072900001'; -- 카트 번호 만들기 ELSE--그렇지 않다면(값이 이미 존재한다) SELECT CART_NO INTO V_CART_NO --구매 이력이 있는지 없는지 확인(있으면 UPDATE 없으면 INSERT작성) FROM CART WHERE CART_MEMBER = 'h001' AND SUBSTR(CART_NO,1,8)= '20050729' AND CART_PROD = 'P202000012'; END IF;--V_CART가 널과 널이 아닌경우 V_CNT값이 NULL 이거나 아닌경우 IF V_CART_NO IS NULL THEN --V_CART_NO값이 NULL인 경우(날짜와 제품을 사지 않았을 경우) INSERT INTO CART SELECT 'h001', MAX(CART_NO)+1,'P202000012',2 FROM CART WHERE CART_NO LIKE '20050729%' --왜 조건을 부여했을까.. 20050729이 날에 CART번호가 증가해야됨 그러려면 20050729로 시작되어지는 CART번호 중에 가장 큰것 부여 ELSIF V_CNT !=0 THEN --NULL이 아닌경우 , 1이 아닌 경우 UPDATE CART SET CART_QTY:=CART_QTY+2--원래 삿던거에 2더해줌 WHERE CART_MEMBER = 'h001' AND SUBSTR(CART_NO,1,8)= '20050729' AND CART_PROD = 'P202000012'; ELSIF V_CNT =0 THEN --1이면 신규 등록 INSERT INTO CART VALUES('h001',V_CART_NO,'P202000012',2) END IF; END;
FROM CART
WHERE CAER_MEMBER = 'h001'
AND CART_NO LIKE '200507%'
- SCALR 변수, REFERENCE 변수제공
