/
PL/SQL
PROCEDUAL LANGUAGE extention TO SQL
-> 오라클 자체에 내장되어 있는 절차적 언어
-> SQL의 단점을 보완하여 SQL 문장내에서 변수정의, 조건처리, 반복처리 등을 지원
/
SET SERVEROUTPUT ON;
DECLARE
BEGIN
DBMS_OUTPUT.PUT_LINE('HELLO ORACLE');
END;
/
/*
PL/SQL 유형
1. 익명블록(Anonymous Block)
-> 이름없는 블록, 간단한 로직 처리할때 사용
2. 프로시저(Procedure)
-> 지정된 특정 처리를 실행하는 서브프로그램
-> 익명블록을 저장해놓고 사용
3. 함수(Function)
-> 프로시저와 거의 동일
-> 차이점은 반환값이 반드시 존재
PL/SQL 기본구조
DECLARE : 선언부[생략가능](필요한 변수나 상수를 선언)
BEGIN : 실행부(필요한 기능을 작성 - 반복, 제어)
END; : 블록종료
/ : 실행
PL/SQL에서 변수에 값을 대입하는 대입연산자는 :=
*/
DECLARE
--변수는 변수명 자료형; 형태로 선언
TEST_NO NUMBER;
TEST_STR VARCHAR2(30);
BEGIN
TEST_NO := 100; -- 변수에 값 대입
TEST_STR := 'HELLO'; -- 변수에 값 대입
DBMS_OUTPUT.PUT_LINE('TEST_NO :' || TEST_NO);
DBMS_OUTPUT.PUT_LINE('TEST_STR : ' || TEST_STR);
END;
/
-- EMPLOYEE 테이블의 사번이 200번인 직원의 이름, 급여 출력
SELECT EMP_NAME, SALARY FROM EMPLOYEE WHERE EMP_ID=200;
DECLARE
E_NAME VARCHAR2(20);
SAL NUMBER;
BEGIN
SELECT EMP_NAME, SALARY
INTO E_NAME, SAL
FROM EMPLOYEE
WHERE EMP_ID='&사원번호';
-- SHIFT+7 = & : SCANNER 기능
DBMS_OUTPUT.PUT_LINE('이름 : ' || E_NAME);
DBMS_OUTPUT.PUT_LINE('급여 : ' || SAL);
END;
/
-- PL/SQL 작성
-- 사원번호를 입력하면 이름, 부서명, 직급명이 출력
SELECT EMP_NAME, DEPT_TITLE, JOB_NAME
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
JOIN JOB USING(JOB_CODE)
WHERE EMP_ID=200;
DESC DEPARTMENT;
DECLARE
E_NAME VARCHAR2(20);
DPT VARCHAR2(35);
POS VARCHAR2(35);
BEGIN
SELECT EMP_NAME, NVL(DEPT_TITLE,'부서없음'), JOB_NAME
INTO E_NAME, DPT, POS
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
JOIN JOB USING(JOB_CODE)
WHERE EMP_ID='&사번';
DBMS_OUTPUT.PUT_LINE('이름 : ' || E_NAME);
DBMS_OUTPUT.PUT_LINE('부서명 : ' || DPT);
DBMS_OUTPUT.PUT_LINE('직급명 : ' || POS);
END;
/
/
PL/SQL 변수의 종류
1. 일반변수(스칼라변수)
- SQL의 기본자료형과 유사한 타입
2. 상수형변수
- 일반변수와 유사한형태지만 CONSTANT라는 키워드가 붙음
- 변수선언시 값을 대입해야하며, 한번대입된값은 변경이 불가능
3. 참조형변수(%TYPE)
- 이전에 선언된 다른 변수 또는 테이블의 자료형과 동일한 타입의 변수
4. 참조형변수(%ROWTYPE)
- 참조할 테이블의 모든컬럼의 자료형을 한번에 가지고옴
5. RECORD 타입
- ROWTYPE은 테이블의 컬럼을 모두 가져온다면 RECORED 내가 지정
/
DECLARE
USER_NAME CONSTANT VARCHAR2(20) := '테스트1';
--상수형변수는 선언하면서 값을 넣어야한다.
USER_NAME2 VARCHAR2(20);
BEGIN
-- USER_NAME := '테스트1'; --그리고 그 값을 수정할 수 없다.
USER_NAME2 := '테스트2';
DBMS_OUTPUT.PUT_LINE('USER_NAME : ' || USER_NAME);
DBMS_OUTPUT.PUT_LINE('USER_NAME : ' || USER_NAME2);
END;
/
-- 사번입력받아 이름, 이메일, 급여를 출력하는 PL/SQL
DECLARE
E_NAME EMPLOYEE.EMP_NAME%TYPE;
MAIL EMPLOYEE.EMAIL%TYPE;
SAL EMPLOYEE.SALARY%TYPE;
-- EMPLOYEE 테이블의 SALARY의 타입을 그대로 가져오라는 명령
-- VARCHAR2를 안써줘도 됨. 길이를 그대로 가져옴
BEGIN
SELECT EMP_NAME, EMAIL, SALARY
INTO E_NAME, MAIL, SAL
FROM EMPLOYEE
WHERE EMP_ID = '&사번';
DBMS_OUTPUT.PUT_LINE('이름 : ' || E_NAME);
DBMS_OUTPUT.PUT_LINE('이메일 : ' || MAIL);
DBMS_OUTPUT.PUT_LINE('급여 : ' || SAL);
END;
/
-- 사번의 입력받아서 사번, 이름, 주민등록번호, 이메일, 부서코드, 직급코드, 급여
SELECT EMP_ID, EMP_NAME, EMP_NO, EMAIL, DEPT_CODE, JOB_CODE, SALARY, HIRE_DATE
FROM EMPLOYEE WHERE EMP_ID=200;
DECLARE
-- ROW TYPE으로 선언시, EMPLOYEE 테이블 내부의 모든 컬럼과 동일한 이름으로 변수가 생성
-- 변수명.컬럼명
EMP EMPLOYEE%ROWTYPE;
BEGIN
SELECT EMP_ID, EMP_NAME, EMP_NO, EMAIL, DEPT_CODE, JOB_CODE, SALARY, HIRE_DATE
INTO EMP.EMP_ID, EMP.EMP_NAME, EMP.EMP_NO, EMP.EMAIL, EMP.DEPT_CODE, EMP.JOB_CODE,
EMP.SALARY, EMP.HIRE_DATE
FROM EMPLOYEE WHERE EMP_ID='&사번';
DBMS_OUTPUT.PUT_LINE('사번 : ' || EMP.EMP_ID);
DBMS_OUTPUT.PUT_LINE('이름 : ' || EMP.EMP_NAME);
DBMS_OUTPUT.PUT_LINE('주민번호 : ' || EMP.EMP_NO);
DBMS_OUTPUT.PUT_LINE('이메일 : ' || EMP.EMAIL);
DBMS_OUTPUT.PUT_LINE('부서코드 : ' || EMP.DEPT_CODE);
DBMS_OUTPUT.PUT_LINE('직급코드 : ' || EMP.JOB_CODE);
DBMS_OUTPUT.PUT_LINE('급여 : ' || EMP.SALARY);
DBMS_OUTPUT.PUT_LINE('입사일 : ' || EMP.HIRE_DATE);
END;
/
-- 사번입력받아서 이름, 주민등록번호, 이메일, 급여 출력
SELECT EMP_NAME, EMP_NO, EMAIL, SALARY
FROM EMPLOYEE WHERE EMP_ID=200;
DECLARE
TYPE MY_RECORD_TYPE IS RECORD(
E_NAME EMPLOYEE.EMP_NAME%TYPE,
E_NO EMPLOYEE.EMP_NO%TYPE,
MAIL EMPLOYEE.EMAIL%TYPE,
SAL EMPLOYEE.SALARY%TYPE
);
USERINFO MY_RECORD_TYPE;BEGIN
SELECT EMP_NAME, EMP_NO, EMAIL, SALARY
INTO USERINFO
FROM EMPLOYEE WHERE EMP_ID='&사번';
DBMS_OUTPUT.PUT_LINE('이름 : ' || USERINFO.E_NAME);
DBMS_OUTPUT.PUT_LINE('사번 : ' || USERINFO.E_NO);
DBMS_OUTPUT.PUT_LINE('주민번호 : ' || USERINFO.MAIL);
DBMS_OUTPUT.PUT_LINE('급여 : ' || USERINFO.SAL);
END;
/
/실습문제/
--@실습문제1
--사번, 사원명, 직급코드, 급여를 담을수 있는 참조변수를 통해서 (%TYPE)
--송종기사원의 사번, 사원명, 직급코드,급여를 익명블럭을 통해 스크립트 출력하세요.
DECLARE
E_ID EMPLOYEE.EMP_ID%TYPE;
E_NAME EMPLOYEE.EMP_NAME%TYPE;
J_CODE EMPLOYEE.JOB_CODE%TYPE;
SAL EMPLOYEE.SALARY%TYPE;
BEGIN
SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY
INTO E_ID, E_NAME, J_CODE, SAL
FROM EMPLOYEE
WHERE EMP_NAME = '송종기';
DBMS_OUTPUT.PUT_LINE('사번 : '|| E_ID);
DBMS_OUTPUT.PUT_LINE('사원명 : '|| E_NAME);
DBMS_OUTPUT.PUT_LINE('직급코드 : '|| J_CODE);
DBMS_OUTPUT.PUT_LINE('급여 : '|| SAL);
END;
/
--@실습문제2
--사번, 사원명, 부서명,직급명을 담을수 있는 참조변수를 통해서 (record)
-- 사원명을 검색하여 해당 사원의 사번, 사원명, 부서명,직급명을
-- 익명블럭을 통해 스크립트 출력하세요.
DECLARE
TYPE MY_RECORD_TYPE IS RECORD(
E_ID EMPLOYEE.EMP_ID%TYPE,
E_NAME EMPLOYEE.EMP_NAME%TYPE,
D_TITLE DEPARTMENT.DEPT_TITLE%TYPE,
J_NAME JOB.JOB_NAME%TYPE
);
USERINFO MY_RECORD_TYPE;
BEGIN
SELECT EMP_ID, EMP_NAME, NVL(DEPT_TITLE,'부서없음'), JOB_NAME
INTO USERINFO
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
JOIN JOB USING(JOB_CODE)
WHERE EMP_NAME = '&사원명';
DBMS_OUTPUT.PUT_LINE('사번 : ' || USERINFO.E_ID);
DBMS_OUTPUT.PUT_LINE('사원명 : ' || USERINFO.E_NAME);
DBMS_OUTPUT.PUT_LINE('부서명 : ' || USERINFO.D_TITLE);
DBMS_OUTPUT.PUT_LINE('직급명 : ' || USERINFO.J_NAME);
END;
/
--@실습문제3
-- 사원번호를 입력하여 해당 사원을 찾아 (%ROWTYPE을 사용)
-- 사원명, 주민번호, 입사일, 부서명을
-- 익명블럭을 통해 스크립트 출력하세요.
DECLARE
EMP EMPLOYEE%ROWTYPE;
DPT DEPARTMENT%ROWTYPE;
BEGIN
SELECT EMP_NAME, EMP_NO, HIRE_DATE, DEPT_TITLE
INTO EMP.EMP_NAME, EMP.EMP_NO, EMP.HIRE_DATE, DPT.DEPT_TITLE
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
WHERE EMP_ID = '&사원번호';
DBMS_OUTPUT.PUT_LINE('사원명 : ' || EMP.EMP_NAME);
DBMS_OUTPUT.PUT_LINE('주민번호 : ' || EMP.EMP_NO);
DBMS_OUTPUT.PUT_LINE('입사일 : ' || EMP.HIRE_DATE);
DBMS_OUTPUT.PUT_LINE('부서명 : ' || DPT.DEPT_TITLE);
END;
/
IF-ELSE
--사원번호를 입력받아서 사번, 이름, 급여, 보너스율을 출력
--만약에 보너스를 받지않는 직원은 '보너스를 받지 않는 직원입니다.' 출력
DECLARE
E_ID EMPLOYEE.EMP_ID%TYPE;
E_NAME EMPLOYEE.EMP_NAME%TYPE;
SAL EMPLOYEE.SALARY%TYPE;
BO EMPLOYEE.BONUS%TYPE;
BEGIN
SELECT EMP_ID, EMP_NAME, SALARY, NVL(BONUS, 0)
INTO E_ID, E_NAME, SAL, BO
FROM EMPLOYEE WHERE EMP_ID = '&사번';
DBMS_OUTPUT.PUT_LINE('사번 :' || E_ID);
DBMS_OUTPUT.PUT_LINE('이름 :' || E_NAME);
DBMS_OUTPUT.PUT_LINE('급여 :' || SAL);
IF(BO = 0)
THEN DBMS_OUTPUT.PUT_LINE('보너스를 받지 않는 직원입니다.' );
ELSE DBMS_OUTPUT.PUT_LINE('보너스 :' || BO*100 ||'%');
END IF;
END;
/
/IF-ELSE 실습/
-- 사원번호를 입력받아서 사원명, 부서코드, 부서명 출력
-- 부서가 있으면 부서명 출력
-- 만약에 부서가 없다면 '부서가 없는 사원입니다.' 출력
DECLARE
E_NAME EMPLOYEE.EMP_NAME%TYPE;
D_CODE EMPLOYEE.DEPT_CODE%TYPE;
D_TITLE DEPARTMENT.DEPT_TITLE%TYPE;
BEGIN
SELECT EMP_NAME, DEPT_CODE, DEPT_TITLE
INTO E_NAME, D_CODE, D_TITLE
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
WHERE EMP_ID = '&사원번호';
DBMS_OUTPUT.PUT_LINE('사원명 :' || E_NAME);
IF(D_CODE IS NOT NULL)
THEN
DBMS_OUTPUT.PUT_LINE('부서코드 :' || D_CODE); DBMS_OUTPUT.PUT_LINE('부서명 :' || D_TITLE);
ELSE DBMS_OUTPUT.PUT_LINE('부서가 없는 사원입니다.' );
END IF;
END;
/
-- 사원번호를 입력받아서 사번, 이름, 부서코드, 직급코드 출력
-- 이때 직급코드가 J1, J2이면 '임원진입니다.' 아니면 '일반사원입니다.' 출력
DECLARE
E_ID EMPLOYEE.EMP_ID%TYPE;
E_NAME EMPLOYEE.EMP_NAME%TYPE;
D_CODE EMPLOYEE.DEPT_CODE%TYPE;
J_CODE EMPLOYEE.JOB_CODE%TYPE;
BEGIN
SELECT EMP_ID, EMP_NAME, DEPT_CODE, JOB_CODE
INTO E_ID, E_NAME, D_CODE, J_CODE
FROM EMPLOYEE
WHERE EMP_ID = '&사원번호';
IF(J_CODE IN ('J1', 'J2'))
THEN DBMS_OUTPUT.PUT_LINE('임원진입니다.' );
ELSE DBMS_OUTPUT.PUT_LINE('일반사원입니다.' );
END IF;
END;
/
ELSE IF를 쓸거면, ELSIF 라는 것 주의하자! E가빠진다!(어이없음)
/
IF(EMP.JOB_CODE = 'J1')
THEN DBMS_OUTPUT.PUT_LINE('임원진입니다.' );
ELSIF(EMP.JOB_CODE = 'J2')
THEN DBMS_OUTPUT.PUT_LINE('임원진입니다.' );
ELSE DBMS_OUTPUT.PUT_LINE('일반사원입니다.' );
/
반복문
-- 무한반복문
DECLARE
I NUMBER := 1;
BEGIN
LOOP -- QKSQHRTLWKRWLWJA
DBMS_OUTPUT.PUT_LINE('I :'||I);
I := I+1;
IF(I=5)
THEN
EXIT; -- LOOP를 종료하는 코드
END IF;
END LOOP; -- 반복끝나는 지점
END;
/
-- FOR 문과 유사
DECLARE
BEGIN
FOR I IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('I : '|| I);
END LOOP;
END;
/ -- 1 2 3 4 5 출력
-- FOR문과 유사 + REVERSE
DECLARE
BEGIN
FOR I IN REVERSE 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('I : '|| I);
END LOOP;
END;
/ -- 5 4 3 2 1 출력
DECLARE
E_ID EMPLOYEE.EMP_ID%TYPE;
E_NAME EMPLOYEE.EMP_NAME%TYPE;
SAL EMPLOYEE.SALARY%TYPE;
H_DATE EMPLOYEE.HIRE_DATE%TYPE;
BEGIN
FOR I IN 0..22 LOOP
SELECT EMP_ID, EMP_NAME, SALARY, HIRE_DATE
INTO E_ID,E_NAME,SAL,H_DATE
FROM EMPLOYEE
WHERE EMP_ID = 200 + I;
DBMS_OUTPUT.PUT_LINE('사번 : '||E_ID);
DBMS_OUTPUT.PUT_LINE('이름 : '||E_NAME);
DBMS_OUTPUT.PUT_LINE('급여 : '||SAL);
DBMS_OUTPUT.PUT_LINE('입사일 : '||H_DATE);
DBMS_OUTPUT.PUT_LINE('----------------------');
END LOOP;END;
/
