안녕하세요.
https://blog.naver.com/sysysy0302 여니입니다 :)
/*
<PL/SQL>
PROCEDURE LANGUAGE EXTENSION TO SQL
오라클자체에 내장되어 있는 절차적 언어
SQL문장 내에서 변수의 정의, 조건처리(IF), 반복처리(LOOP,FOR,WHILE), 예외처리등을 지원하여
SQL의 단점을 보완.
다수의 SQL문을 한번에 실행가능(BLOCK구조)
* PL/SQL구조
- [선언부 (DECLARE SECTION)] : DECLARE로 시작, 변수나 상수를 선언 및 초기화하는 부분
- [실행부 (EXECUTABLE SECTION)] : BEGIN으로 시작(--;END로 끝남),SQL문(SELECT,UPDATE,DELETE,..) 또는 제어문(조건문,반복문)
등의 로직을 기술하는 부분
- [예외처리부 (EXCEPTION SECTION)] : EXCEPTION로 시작, 예외발생시 해결하기 위한 구문을 미리 기술해둘수 있는 부분.
*/
-- 서버 아웃풋 옵션 켜주기
SET SERVEROUTPUT ON;
BEGIN
DBMS_OUTPUT.PUT_LINE('HELLO_ORACLE');
END;
/
/*
1. DECLARE 선언부
변수 및 상수 선언하는 공간(선언과 동시에 초기화도 가능)
일반타입 변수, 레퍼런스 변수, ROW타입 변수
1_1) 일반타입 변수 선언 및 초기화
[표현법]
변수명 [CONSTANT] 자료형 [:=값];
*/
DECLARE
EID NUMBER;
ENAME VARCHAR2(20);
PI CONSTANT NUMBER := 3.14;
BEGIN
-- EID := 800;
EID := &번호;
-- 내가 값을 입력
-- ENAME := '김소연';
ENAME := &이름;
DBMS_OUTPUT.PUT_LINE('EID : ' || EID);
DBMS_OUTPUT.PUT_LINE('ENAME : ' || ENAME);
DBMS_OUTPUT.PUT_LINE('PI : ' || PI);
END;
/
-- 이게 있어야 블록종결로 간주되어서 다음 PL문 실행 가능함. ( / 뒤에 아무것도 없어야 실행된다.)
-- 1_2) 래퍼런스 타입 변수 선언 및 초기화(어떤 테이블의 어떤 칼럼의 데이터타입을 참조해서 그 타입으로 지정)
-- [표현법]
-- 변수명 테이블명.컬럼명%TYPE;
DECLARE
EID EMPLOYEE.EMP_ID%TYPE;
ENAME EMPLOYEE.EMP_NAME%TYPE;
SAL EMPLOYEE.SALARY%TYPE;
BEGIN
EID := '300';
ENAME := '김소연';
SAL := 3000000;
-- 사번이 200번인 각 사원의 사번, 사원명, 연봉을 대입해보기
SELECT
EMP_ID, EMP_NAME, SALARY
INTO EID, ENAME, SAL
FROM EMPLOYEE
-- WHERE EMP_ID = 200;
WHERE EMP_ID = &사번;
DBMS_OUTPUT.PUT_LINE('EID : ' || EID);
DBMS_OUTPUT.PUT_LINE('ENAME : ' || ENAME);
DBMS_OUTPUT.PUT_LINE('SAL : ' || SAL);
END;
/
----------------------------------- 실습문제 ----------------------------------------------------
/*
레퍼런스 타입 변수로 EID, ENAME, JCODE, SAL, DTITLE을 선언하고
각 자료형 EMPLOYEE(EMP_ID, EMP_NAME, JOB_CODE, SALARY)
DEPARTMENT(DEPT_TITLE)들을 참조하도록
사용자가 입력한 사번인 사원의 사번, 사원명, 직급코드, 급여, 부서명 조회후 변수에 담아서 출력
*/
DECLARE
EID EMPLOYEE.EMP_ID%TYPE;
ENAME EMPLOYEE.EMP_NAME%TYPE;
JCODE EMPLOYEE.JOB_CODE%TYPE;
SAL EMPLOYEE.SALARY%TYPE;
DTITLE DEPARTMENT.DEPT_TITLE%TYPE;
BEGIN
SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY, DEPT_TITLE
INTO EID, ENAME, JCODE, SAL, DTITLE
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
WHERE EMP_ID = &사번;
DBMS_OUTPUT.PUT_LINE(EID || ', ' || ENAME || ', ' || JCODE || ', ' || SAL || ', ' || DTITLE);
END;
/
-----------------------------------------------------------------------------------
-- 1-3) ROW타입 변수 선언
-- 테이블의 한 행에 대한 모든 칼럼값을 한꺼번에 담을 수 있는 변수
-- [표현식] 변수명 테이블명&ROWTYPE
DECLARE
E EMPLOYEE&ROWTYPE;
BEGIN
SELECT *
INTO E
FROM EMPLOYEE
WHERE EMP_ID = &사번;
DBMS_OUTPUT.PUT_LINE('사원명 : ' || E.EMP_NAME);
DBMS_OUTPUT.PUT_LINE('급여 : ' || E.SALARY);
DBMS_OUTPUT.PUT_LINE('보너스 : ' || NVL(E.BONUS,0));
END;
/
---------------------------------------------------------------------------
-- 2. BEGIN 실행부
-- <조건문>
-- ; 201 송종기로 입력 후 출력 확인
-- 1) IF 조건식 THEN 실행내용 END IF;
-- 사번 입력받은 후 해당 사원의 사번, 이름, 급여, 보너스율을 출력
-- 단, 보너스를 받지 않는 사원은 보너스율 출력전 '보너스를 지급받지 않는 사원입니다.' 출력
DECLARE
EID EMPLOYEE.EMP_ID%TYPE;
ENAME EMPLOYEE.EMP_NAME%TYPE;
SALARY EMPLOYEE.SALARY%TYPE;
BONUS EMPLOYEE.BONUS%TYPE;
BEGIN
SELECT EMP_ID, EMP_NAME, SALARY, NVL(BONUS,0)
INTO EID, ENAME, SALARY, BONUS
FROM EMPLOYEE
WHERE EMP_ID = &사번;
DBMS_OUTPUT.PUT_LINE('사번 : ' || EID);
DBMS_OUTPUT.PUT_LINE('이름 : ' || ENAME);
DBMS_OUTPUT.PUT_LINE('급여 : ' || SALARY);
IF BONUS = 0
THEN DBMS_OUTPUT.PUT_LINE('보너스를 지급받지 않는 사원입니다.');
END IF;
DBMS_OUTPUT.PUT_LINE('보너스 : ' || BONUS*100 || '%');
END;
/
-- 2) IF 조건식 THEN 실행내용 ELSE 실행내용 END IF;
--; 200 사번으로 조회 연습
DECLARE
EID EMPLOYEE.EMP_ID%TYPE;
ENAME EMPLOYEE.EMP_NAME%TYPE;
SALARY EMPLOYEE.SALARY%TYPE;
BONUS EMPLOYEE.BONUS%TYPE;
BEGIN
SELECT EMP_ID, EMP_NAME, SALARY, NVL(BONUS,0)
INTO EID, ENAME, SALARY, BONUS
FROM EMPLOYEE
WHERE EMP_ID = &사번;
DBMS_OUTPUT.PUT_LINE('사번 : ' || EID);
DBMS_OUTPUT.PUT_LINE('이름 : ' || ENAME);
DBMS_OUTPUT.PUT_LINE('급여 : ' || SALARY);
IF BONUS = 0
THEN DBMS_OUTPUT.PUT_LINE('보너스를 지급받지 않는 사원입니다.');
ELSE
DBMS_OUTPUT.PUT_LINE('보너스 : ' || BONUS*100 || '%');
END IF;
DBMS_OUTPUT.PUT_LINE('보너스 : ' || BONUS*100 || '%');
END;
/
------------------------------- 실습문제 -----------------------------------------
DECLARE
-- 레퍼런스타입변수 (EID, ENAME, DTITLE, NCODE)
-- 참조할 칼럼 (EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_CODE)
-- 일반타입 변수 TEAM 문자열 : 국내팀, 해외팀
EID EMPLOYEE.EMP_ID%TYPE;
ENAME EMPLOYEE.EMP_NAME%TYPE;
DTITLE DEPARTMENT.DEPT_TITLE%TYPE;
NCODE LOCATION.NATIONAL_CODE%TYPE; -- KH.NATIONAL도 가능 (이미 어디선가쓰이고 있어서 오류)
TEAM VARCHAR2(10);
BEGIN
-- 사용자가 입력한 사번의 사원이 사번, 이름, 부서명, 근무국가코드 조회후 각 변수에 대입
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_CODE
INTO EID, ENAME, DTITLE, NCODE
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_ID = DEPT_CODE)
JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
JOIN NATIONAL USING (NATIONAL_CODE)
WHERE EMP_ID = &사번;
-- NCODE의 값이 KO인 경우 TEAM 변수에 한국팀 대입
-- 그게 아닐 경우 TEAM 해외팀 대입
IF NCODE = 'KO'
THEN TEAM := '한국팀';
ELSE
TEAM := '해외팀';
END IF;
-- 사번, 이름, 부서, 소속(TEAM)을 출력
DBMS_OUTPUT.PUT_LINE('사번 : ' || EID);
DBMS_OUTPUT.PUT_LINE('이름 : ' || ENAME);
DBMS_OUTPUT.PUT_LINE('부서 : ' || DTITLE);
DBMS_OUTPUT.PUT_LINE('소속 : ' || TEAM);
END;
/
-- 3) IF 조건식 1 THEN 실행내용 ELSIF 조건식2 THEN 실행내용2 [ELSE 실행내용 N] END IF;
-- 급여가 500만원 이상이면 고급
-- 급여가 300만원 이상이면 중급
-- 그외 초급
-- 출력문 : 해당 사원의 급여등급은 XX입니다.
DECLARE
SAL EMPLOYEE.SALARY%TYPE;
GRADE VARCHAR2(10);
BEGIN
SELECT SALARY
INTO SAL
FROM EMPLOYEE
WHERE EMP_ID = &사번;
IF SAL >= 5000000 THEN GRADE := '고급';
ELSIF SAL >= 3000000 THEN GRADE := '중급';
ELSE GRADE := '초급';
END IF;
DBMS_OUTPUT.PUT_LINE('해당사원의 급여등급은 ' || GRADE || '입니다.');
END;
/
-- 4) CASE 비교대상자 WHEN 동등비교값1 THEN 결과값1 WHEN 비교값2 THEN 결과값2 ELSE 결과값3 END;
DECLARE
EMP EMPLOYEE%ROWTYPE;
DNAME VARCHAR2(10);
BEGIN
SELECT *
INTO EMP
FROM EMPLOYEE
WHERE EMP_ID = &사번;
DNAME := CASE EMP.DEPT_CODE
WHEN 'D1' THEN '인사팀'
WHEN 'D1' THEN '회계팀'
WHEN 'D1' THEN '마케팅팀'
WHEN 'D1' THEN '국내영업팀'
WHEN 'D1' THEN '총무팀'
ELSE '해외팀'
END;
DBMS_OUTPUT.PUT_LINE(EMP.EMP_NAME || '은 ' || DNAME || '입니다.');
END;
/
------------------------------------------------------------------------------
-- 반복문
/*
1) BASIC LOOP문
[표현식]
LOOP
반복적으로 실행한 구문;
* 반복문을 빠져나갈수 있는 구문
END LOOP;
* 반복문을 빠져나갈 수 있는 구문
1) IF 조건식 THEN EXIT; END IF;
2) EXIT WHEN 조건식;
*/
-- 1~5까지 순차적으로 1씩 증가하는 값을 출력
DECLARE
I NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(I);
I := I +1;
-- IF I = 6 THEN EXIT; END IF;
EXIT WHEN I = 6;
END LOOP;
END;
/
/*
2) FOR LOOP문
[표현식]
FOR 변수 IN [REVERSE] 초기값 .. 최종값
LOOP
반복적으로 수행할 구문;
END LOOP;
*/
BEGIN
FOR I IN 10..15
LOOP
DBMS_OUTPUT.PUT_LINE(I);
END LOOP;
END;
/
DROP TABLE TEST;
CREATE TABLE TEST(
TNO NUMBER PRIMARY KEY,
TDATE DATE
);
CREATE SEQUENCE SEQ_TNO
INCREMENT BY 2
MAXVALUE 10000;
BEGIN /*★ BEGIN과 END문 사이에 SQL문은 반드시 실행된다.*/
FOR I IN 1..1000
LOOP
INSERT INTO TEST VALUES(SEQ_TNO.NEXTVAL, SYSDATE);
END LOOP;
END;
/
SELECT * FROM TEST;