-
Procedure Language Extension to SQL 의 약자이다.
-
데이터 베이스 상에서 반복문( while, for ), 제어문( if )등을 지원한다.
PL/SQL 의 구성
- 선언부 (선택)
- 실행부(필수)
- 예외처리부(선택)
DECLARE
정의문( 선언부 )
BEGIN
실행문( 실행부 )
EXCEPTION
예외 처리문( 예외 처리부 )
END;
/
PL/SQL 의 사용
SET SERVEROUTPUT ON; -- 출력을 가능하도록 해준다.
DECLARE -- 선언부
V_NAME VARCHAR2(30) := 'PL/SQL';
BEGIN -- 실행부
DBMS_OUTPUT.PUT_LINE('오라클과' || V_NAME);
END; -- 종료
/ -- 항상 이 문자를 마지막으로 해주어야 함
SET SERVEROUTPUT OFF;
- 선언부( DECLARE )에서
V_NAME을VARCHAR2(30)로 선언했다.V_NAME은 변수처럼 이용이 가능하다.
- 실행부( BEGIN )에서
DBMS_OUTPUT.PUT_LINE();함수를 사용해서 문자를 출력하고 있다.V_NAME을 변수처럼 호출해서 출력한다.
PL/SQL 반복문
SET SERVEROUTPUT ON;
DECLARE
V_CNT NUMBER := 0;
V_TOT NUMBER := 0;
BEGIN
LOOP EXIT WHEN V_CNT = 12;
V_CNT :=V_CNT + 3;
V_TOT := V_TOT + V_CNT;
END LOOP;
DBMS_OUTPUT.PUT_LINE('V_CNT : ' || V_CNT);
DBMS_OUTPUT.PUT_LINE('V_TOT : ' || V_TOT);
END;
/ -- 항상 이 문자를 마지막으로 해주어야 함
SET SERVEROUTPUT OFF;
- 선언부( DECLARE )에서
V_CNT, V_TOT를 각각 0으로 초기화한다. - 실행부( BEGIN )에서 반복문을 실행한다.
LOOP~LOOP END;사이에 반복문을 정의한다.EXIT반복문이 종료되는 시점을 의미한다.(V_CNT가 12일때 종료)V_CNT는 루프마다 3씩 증가된다.V_TOT는V_CNT의 총합을 나타낸다.DBMS_OUTPUT.PUT_LINE();함수를 통해 연산 결과를 출력한다.
PL/SQL WHILE문
SET SERVEROUTPUT ON;
DECLARE
V_CNT NUMBER := 0;
V_TOT NUMBER := 0;
BEGIN
WHILE V_CNT < 10
LOOP
V_CNT := V_CNT +1;
V_TOT :=V_TOT + V_CNT;
END LOOP;
DBMS_OUTPUT.PUT_LINE('V_CNT : ' || V_CNT);
DBMS_OUTPUT.PUT_LINE('V_TOT : ' || V_TOT);
END;
/ -- 항상 이 문자를 마지막으로 해주어야 함
SET SERVEROUTPUT OFF;
- WHILE문은 종료 조건으로
V_CNT < 10을 사용하고 있다. - 이 외 부분은 LOOP 문과 같다.
PL/SQL FOR문
SET SERVEROUTPUT ON;
DECLARE
V_CNT NUMBER := 0;
V_TOT NUMBER := 0;
BEGIN
FOR V_CNT IN 1 .. 10
LOOP
V_TOT := V_TOT + V_CNT;
END LOOP;
DBMS_OUTPUT.PUT_LINE('V_CNT : ' || V_CNT);
DBMS_OUTPUT.PUT_LINE('V_TOT : ' || V_TOT);
END;
/ -- 항상 이 문자를 마지막으로 해주어야 함
SET SERVEROUTPUT OFF;
- FOR문은 종료조건을
IN을 사용했다. 1 IN 10은 1부터 10까지를 의미한다.- 루프가 10번 돈다.
PL/SQL CASE문
자바의 swich case 문과 비슷한 기능을 PL/SQL도 제공한다.
SET SERVEROUTPUT ON;
DECLARE
V_NAME VARCHAR2(30) := 'LEE';
V_CASE NUMBER := 1;
BEGIN
CASE WHEN MOD(V_CASE, 2) = 0 THEN GOTO TEST1;
WHEN MOD(V_CASE, 20) = 1 THEN GOTO TEST2;
ELSE GOTO ERR;
END CASE;
<<TEST1>>
DBMS_OUTPUT.PUT_LINE(V_NAME || 'IS WOMAN');
GOTO SUB_END;
<<TEST2>>
DBMS_OUTPUT.PUT_LINE(V_NAME || 'IS MAN');
GOTO SUB_END;
<<ERR>>
DBMS_OUTPUT.PUT_LINE('ERROR');
GOTO SUB_END;
<<SUB_END>>
DBMS_OUTPUT.PUT_LINE('EXIT SUB : ' || V_NAME);
END;
/
SET SERVEROUTPUT OFF;
CASE WHEN 조건을 통해 조건이 만족한다면(THEN)GOTO 이동를 통해 이동한다.<< 구간 이름 >>구간을 정의할 수 있다.- GOTO를 통해 구간으로 이동할 수 있다.
PL/SQL 입력 처리
PL/SQL을 사용해서 입력 프롬프트를 열어 값을 직접 입력받게 할 수 있다.
-- 입력 창을 띄운다.
ACCEPT P_NAME PROMPT 'NAME: '
ACCEPT P_SAL PROMPT 'SALARY: '
ACCEPT P_DEPTNO PROMPT 'DEPTNO: '
DECLARE
V_NAME VARCHAR2(10) := UPPER('&P_NAME');
V_SAL NUMBER(7, 2) := &P_SAL;
V_DEPTNO NUMBER(2) := &P_DEPTNO;
BEGIN
IF V_DEPTNO = 30 THEN
V_SAL := V_SAL * 1.2;
ELSIF V_DEPTNO = 60 THEN
V_SAL := V_SAL * 1.1;
END IF;
INSERT INTO EMPLOYEES(
EMPLOYEE_ID, LAST_NAME, HIRE_DATE, DEPARTMENT_ID, JOB_ID, EMAIL)
VALUES(
EMPLOYEES_SEQ.NEXTVAL, V_NAME, SYSDATE, V_DEPTNO,
'IT_PROG', V_NAME||'@NAVER.COM'
);
END;
/
ACCEPT명령어를 사용해서 직접 입력을 받을 수 있다.- 입력받은 값은
P_NAME, P_SAL, P_DEPTNO에 저장할 수 있다. - 선언부에서 입력받은 값들(
P_NAME, P_SAL, P_DEPTNO)을 통해V_NAME, V_SAL, V_DEPT를 초기화한다. IF~THEN문법을 사용해서 조건문을 작성한다.ELSIF는ELSE IF를 의미한다.IF END를 사용해서 조건문을 닫아준다.
INSERT INTO를 사용해 테이블에 직접 입력을 해준다.
/*
부서 번호를 입력받아서 급여의 함을 출력하는 SCRIPT를 작성하라.
*/
SET SERVEROUTPUT ON;
ACCEPT DEPTNO PROMPT 'DEPT NUMBER:';
DECLARE
V_SAL NUMBER := 0;
BEGIN
SELECT SUM(SALARY) INTO V_SAL
FROM EMPLOYEES WHERE DEPARTMENT_ID = &DEPTNO;
DBMS_OUTPUT.PUT_LINE(&DEPTNO||'부서의 합 : ' || TO_CHAR(V_SAL, '&999,999,999');
END;
/
SET SERVEROUTPUT OFF;
/*
사원 번호가 101인 사원의 급여에 1000을 더하여 갱신하라
*/
SELECT SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID = 101;
SET SERVEROUTPUT ON;
DECLARE
SAL NUMBER := 0;
V_SAL EMPLOYEES.SALARY%TYPE := 1000;
BEGIN
UPDATE EMPLOYEES
SET SALARY = SALARY + V_SAL
WHERE EMPLOYEE_ID=101;
END;
/
SET SERVEROUTPUT OFF;
Comments