PL/SQL
참조 : %TYPE
참조 : %ROWTYPE,
'&사용자입력받기'
변수명 설정
조건문
IF ~ THEN ~ END IF
IF ~ THEN ~ END IF = 자바 단일 IF문
IF ~ THEN ~ ELSE ~ END IF
IF ~ ELSE문
IF~ THEN~ ELSIF~ THEN~ ELSE~ END IF
IF~ THEN~ ELSIF~ THEN~ ELSE~ END IF
== IF~ ELSE IF~ ELSE문
------------------------------------------------------------------------------
--------------------------------PL/SQL(절차형 SQL)-----------------------------
------------------------------------------------------------------------------
/*
PL/SQL ( Procedural Language extension to SQL) = 절차형 SQL
오라클 자체에 내장되어있는 절차적 언어
절차적 언어(Procedural Language)란?
반복문,if문 같은 것들이 절차적으로 사용되는 코드들임
절차적 논리, 계산 같은 것들이 sql에서 사용되는 것
변수의 정의, 조건처리, 반복처리(반복문) 등을 지원하여 PL/SQL을 통해서 SQL의 단점 보완
PL/SQL의 구조
1.선언부(DECLARE SECTION) : DECLARE
2.실행부(EXECUTABLE SECTION) : BEGIN
3.예외처리부(EXCEPTION SECTION) : EXCEPTION
1.선언부(DECLARE SECTION) : 변수, 상수 선언하는 부분
-> DECLARE로 시작
2.실행부(EXECUTABLE SECTION) : 제어문, 함수 정의 등 로직을 기술 하는 부분
-> BEGIN으로 시작
3.예외처리부(EXCEPTION SECTION) : 예외 발생 시, 해결할 수 있는 문장 기술 부분
-> EXCEPTION으로 시작
선언부,예외처리부 생략가능
- 필요에 따라 안 쓸 수도 있다는 의미
자바로 보면
System.out.println("안녕");
String hello = "안녕";
System.out.println(hello);
*/
SET SERVEROUTPUT ON;
BEGIN
DBMS_OUTPUT.PUT_LINE('HELLOW WORLD');
END;
/
SET SERVEROUTPUT ON;
BEGIN
DBMS_OUTPUT.PUT_LINE('HELLOW WORLD');
END;
/
-- / 포함해서 위 코드 옆에 주석 달면 에러 발생
-- SET SERVEROUTPUT ON; : 출력 결과에 뜨게끔 하는 코드
-- SP2-0265: serveroutput은 ON 또는 OFF로 설정되어야 합니다.
-- -> 주석이 위아래 옆 없어야한다. 시작부터 끝까지 안에 주석도 없어야해서 밖으로 빼줄 것
-- PL/SQL 프로시저가 성공적으로 완료되었습니다.
-- 엔드로 끝나는게 맞는데 여기 문장이 끝이다라는 의미로 /까지 적어줘야함
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- 선언부 작성
/*
-- 자바코드로 예시
-- 이 자바코드를 선언부로 작성해 볼 것임
-선언부
int empId;
String empName;
final int PI = 3.14; -- 선언 초기화 같이하면 선언부 쪽
-실행부
empId = 888; -- 값 초기화 부분도 실행부로 본다.
empName = "도대담";
System.out.println("empId : " + empId;
System.out.println("empName : " + empName);
System.out.println("PI : " + PI);
*/
-- 선언부
DECLARE -- 변수설정 // 변수 3개
EMP_ID NUMBER; -- 얘는 따로따로 직접 지정해서 쓴 것
EMP_NAME VARCHAR(30); -- 따로 쓰는게 아닌 참조해서 쓰는 방법도 있음
PI CONSTANT NUMBER := 3.14; -- 상수 넘버. 오라클에서의 상수는 CONSTANT
-- 오라클의 대입연산자 : :=
-- 실행부
BEGIN
EMP_ID := 888;
EMP_NAME := '도대담';
DBMS_OUTPUT.PUT_LINE('EMP_ID ' || EMP_ID);
DBMS_OUTPUT.PUT_LINE('EMP_NAME : ' || EMP_NAME);
DBMS_OUTPUT.PUT_LINE('PI : ' || PI);
END;
/
-- / 옆에 주석달면 안된다
-- 강의시간 14:00-14:20
-- EMP_ID의 정보를 참조해 올 것임
-- 선언부
DECLARE
EMP_ID EMPLOYEE.EMP_ID%TYPE;
EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
-- 실행부
BEGIN
SELECT EMP_ID, EMP_NAME
INTO EMP_ID, EMP_NAME
FROM EMPLOYEE
-- WHERE EMP_ID = 200;
WHERE EMP_ID = '&사원';
DBMS_OUTPUT.PUT_LINE('사번 : '||EMP_ID);
DBMS_OUTPUT.PUT_LINE('이름 : '||EMP_NAME);
END;
/
/* 문제
레퍼런스 변수로 EMP_ID, EMP_NAME, DEPT_CODE, JOB_CODE, SALARY를 선언하고
EMPLOYEE 테이블에서 사번, 이름, 직급코드, 부서코드, 급여를 조회하고
선언한 레퍼런스 변수에 담아 출력하시오
단, 입력받은 이름과 일치하는 조건의 직원을 조회하세요.
*/
DECLARE
EMP_ID EMPLOYEE.EMP_ID%TYPE;
EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
DEPT_CODE EMPLOYEE.DEPT_CODE%TYPE;
JOB_CODE EMPLOYEE.JOB_CODE%TYPE;
SALARY EMPLOYEE.SALARY%TYPE;
BEGIN
SELECT EMP_ID, EMP_NAME, DEPT_CODE, JOB_CODE, SALARY
INTO EMP_ID, EMP_NAME, DEPT_CODE, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE EMP_NAME = '&이름';
DBMS_OUTPUT.PUT_LINE('사번 : '||EMP_ID);
DBMS_OUTPUT.PUT_LINE('이름 : '||EMP_NAME);
DBMS_OUTPUT.PUT_LINE('부서CODE : '||DEPT_CODE);
DBMS_OUTPUT.PUT_LINE('잡코드 : '||JOB_CODE);
DBMS_OUTPUT.PUT_LINE('연봉 : '||SALARY);
END;
/
/* -- 출력 결과
EMP_ID 209
EMP_NAME 심봉선
DEPT_CODE D5
JOB_CODE J3
SALARY 3500000
*/
--변수를 많이 쓴다면?
-- 한 행 전체를 가지고 오는 레퍼런스
-- %ROWTYPE
DECLARE
E EMPLOYEE%ROWTYPE;
BEGIN
SELECT *
INTO E
FROM EMPLOYEE
WHERE EMP_ID = '&사번';
DBMS_OUTPUT.PUT_LINE('사번 : '||E.EMP_ID);
DBMS_OUTPUT.PUT_LINE('EMP_NAME : '||E.EMP_NAME);
DBMS_OUTPUT.PUT_LINE('SALARY : '||E.SALARY);
END;
/
-- 한 행 전체 참조 : %ROWTYEP + INTO
DECLARE
E EMPLOYEE%ROWTYPE;
BEGIN
SELECT *
INTO E
FROM EMPLOYEE
WHERE EMP_ID = '&사번';
DBMS_OUTPUT.PUT_LINE('사번 : ' ||E.EMP_ID);
END;
/
-- 선언부 생략 가능하니 각각 세미콜론(;)으로 마침을 해줘야한다
---RE
------------------------------------------------------------------------------
-----------------------------------조건문--------------------------------------
------------------------------------------------------------------------------
-- 조건문
-- IF ~ THEN ~ END IF;
-- IF ~ THEN ~ END IF= 자바 단일 IF문
-- EMP_ID를 입력받아 해당 사원의 사번, 이름, 급여, 보너스율 출력
-- 보너스를 받지 않는 사원은 보너스율 출력 전 '보너스를 지급받지 않는 사원입니다.' 출력
-- 일단 IF문 없이
--선언부
DECLARE -- 변수설정 // 변수
EMP_ID EMPLOYEE.EMP_ID%TYPE;
EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
SALARY EMPLOYEE.SALARY%TYPE;
BONUS EMPLOYEE.BONUS%TYPE;
--실행부
BEGIN
SELECT EMP_ID,EMP_NAME,SALARY, BONUS
INTO EMP_ID,EMP_NAME,SALARY, BONUS -- 별칭인지 여부 확인하자
FROM EMPLOYEE
WHERE EMP_ID = '&사번';
DBMS_OUTPUT.PUT_LINE('사번:' || EMP_ID);
END;
/
-- 변수명 설정
DECLARE -- 변수설정 // 변수 4개
EI EMPLOYEE.EMP_ID%TYPE; -- EI
EN EMPLOYEE.EMP_NAME%TYPE;
SAL EMPLOYEE.SALARY%TYPE;
BNS EMPLOYEE.BONUS%TYPE;
BEGIN
SELECT EMP_ID,EMP_NAME,SALARY, BONUS
INTO EI,EN,SAL, BNS -- INTO에 들어갈 것들은 변수명임!!
FROM EMPLOYEE -- INTO에 EMP_ID를 받을 변수명 설정 : EI
WHERE EMP_ID = '&사번';
DBMS_OUTPUT.PUT_LINE('사번:' || EI); -- EI
END;
/
-- IF문 추가
-- 변수명 설정
DECLARE -- 변수설정 // 변수 4개
EI EMPLOYEE.EMP_ID%TYPE; -- EI
EN EMPLOYEE.EMP_NAME%TYPE;
SAL EMPLOYEE.SALARY%TYPE;
BNS EMPLOYEE.BONUS%TYPE;
BEGIN
SELECT EMP_ID,EMP_NAME,SALARY, BONUS
INTO EI,EN,SAL, BNS -- INTO에 들어갈 것들은 변수명임!!
FROM EMPLOYEE -- INTO에 EMP_ID를 받을 변수명 설정 : EI
WHERE EMP_ID = '&사번';
IF BNS IS NULL
THEN DBMS_OUTPUT.PUT_LINE('보너스 지급X 사원' || BNS); -- EI
END IF;
DBMS_OUTPUT.PUT_LINE('사번:' || EI); -- EI
END;
/
-- NVL 추가 응용
DECLARE -- 변수설정 // 변수 4개
EI EMPLOYEE.EMP_ID%TYPE; -- EI
EN EMPLOYEE.EMP_NAME%TYPE;
SAL EMPLOYEE.SALARY%TYPE;
BNS EMPLOYEE.BONUS%TYPE;
BEGIN
SELECT EMP_ID,EMP_NAME,SALARY, NVL(BONUS, 0) -- NVL
INTO EI,EN,SAL, BNS -- INTO에 들어갈 것들은 변수명임!!
FROM EMPLOYEE -- INTO에 EMP_ID를 받을 변수명 설정 : EI
WHERE EMP_ID = '&사번';
IF BNS = 0
THEN DBMS_OUTPUT.PUT_LINE('보너스 지급X 사원' || BNS); -- EI
END IF;
DBMS_OUTPUT.PUT_LINE('사번:' || EI); -- EI
DBMS_OUTPUT.PUT_LINE('EMP_NAME ' || EN);
DBMS_OUTPUT.PUT_LINE('SALARY ' || SAL);
DBMS_OUTPUT.PUT_LINE('BONUS ' || BNS);
END;
/
------------------------------------------------------------------------------
-- IF ~ THEN ~ ELSE ~ END IF
-- IF ~ THEN ~ ELSE ~ END IF == IF ~ ELSE문
-- EMP_ID를 입력받아 해당 사원의 사번, 이름, 부서명, 소속 출력
-- TEAM 변수를 만들어 소속이 KO인 사원은 국내팀, 아닌 사원은 해외팀으로 저장
DECLARE
EMP_ID EMPLOYEE.EMP_ID%TYPE;
EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
DEPT_TITLE DEPARTMENT.DEPT_TITLE%TYPE;
NATIONAL_CODE LOCATION.NATIONAL_CODE%TYPE;
TEAM VARCHAR(10);
BEGIN
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_CODE
INTO EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_CODE
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
LEFT JOIN LOCATION ON(LOCATION_ID = LOCAL_CODE)
WHERE EMP_ID = '&사번';
IF national_code = 'KO'
THEN TEAM := '국내팀';
ELSE TEAM := '해외팀';
END IF;
DBMS_OUTPUT.PUT_LINE('사번 : ' || EMP_ID);
DBMS_OUTPUT.PUT_LINE('이름 : ' || EMP_NAME);
DBMS_OUTPUT.PUT_LINE('부서 : ' || DEPT_TITLE);
DBMS_OUTPUT.PUT_LINE('소속 : ' || TEAM);
END;
/
-- EMP_ID를 입력받아 해당 사원의 사번, 이름, 부서명, 소속 출력
-- TEAM 변수를 만들어 소속이 KO인 사원은 국내팀, 아닌 사원은 해외팀으로 저장
-- 변수명 바꿔서 다시한번
DECLARE
EI EMPLOYEE.EMP_ID%TYPE;
EN EMPLOYEE.EMP_NAME%TYPE;
DT DEPARTMENT.DEPT_TITLE%TYPE;
NC LOCATION.NATIONAL_CODE%TYPE;
TEAM VARCHAR2(10);
BEGIN
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_CODE
INTO EI, EN, DT, NC
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
LEFT JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
WHERE EMP_ID = '&사번';
IF NC = 'KO'
THEN TEAM := '국내팀';
ELSE TEAM := '해외팀';
END IF;
DBMS_OUTPUT.PUT_LINE('사번 : ' || EI);
DBMS_OUTPUT.PUT_LINE('이름 : ' || EN);
DBMS_OUTPUT.PUT_LINE('부서 : ' || DT);
DBMS_OUTPUT.PUT_LINE('소속 : ' || NC);
END;
/
-- 사용자에게 사번을 받아와 그 사원의 전체 정보를 VEMP에 저장
-- VEMP를 이용하여 연봉 계산(보너스가 있는 사원은 보너스도 포함하여 계산)
-- 연봉 계산 결과 값은 YSALARY에 저장
-- 급여 이름 연봉(\1,000,000 형식)으로 출력
-- (IF없이 NVL문으로도 가능)
-- 사원 1명의 전체정보니까 한 행 전체 -> %ROWTYPE
DECLARE
VEMP EMPLOYEE%ROWTYPE;
YSALARY NUMBER;
BEGIN
SELECT *
INTO VEMP
FROM EMPLOYEE
WHERE EMP_ID = '&사원번호';
IF VEMP.BONUS IS NOT NULL
THEN YSALARY := VEMP.SALARY * (1+VEMP.BONUS)*12;
ELSE YSALARY := VEMP.SALARY*12;
END IF;
DBMS_OUTPUT.PUT_LINE(VEMP.EMP_ID);
-- SELECT에서 *전체 불러오기 때문에 변수명.컬럼명으로 명시해서 불러와야한다
DBMS_OUTPUT.PUT_LINE(VEMP.SALARY||' '||VEMP.EMP_NAME||' '||YSALARY||
TO_CHAR(YSALARY,'FML999,999,999'));
END;
/
-- 사용자에게 사번을 받아와 그 사원의 전체 정보를 VEMP에 저장
-- VEMP를 이용하여 연봉 계산(보너스가 있는 사원은 보너스도 포함하여 계산)
-- 연봉 계산 결과 값은 YSALARY에 저장
-- 급여 이름 연봉(\1,000,000 형식)으로 출력
-- (IF없이 NVL문으로도 가능)
DECLARE
VEMP EMPLOYEE%ROWTYPE;
YSALARY NUMBER;
BEGIN
SELECT *
INTO VEMP
FROM EMPLOYEE
WHERE EMP_ID = '&사번';
IF VEMP.BONUS IS NOT NULL
THEN YSALARY := VEMP.SALARY*(1+VEMP.BONUS)*12;
ELSE YSALARY := VEMP.SALARY*12;
END IF;
DBMS_OUTPUT.PUT_LINE(VEMP.SALARY||' '||vemp.EMP_NAME||' '||
TO_CHAR(YSALARY,'FML999,999,999'));
END;
/
------------------------------------------------------------------------------
-- IF~ THEN~ ELSIF~ THEN~ ELSE~ END IF
-- IF~ THEN~ ELSIF~ THEN~ ELSE~ END IF == IF~ ELSE IF~ ELSE문
-- 점수를 입력받아 SCORE 변수에 저장
-- 90점 이상이면 A, 80이상이면 B, 70이상은 C, 60점 이상은 D, 그 미만은 F 처리하여 GRADE 변수에 저장
-- 출력형식 : 당신의 점수는 N점이고, 학점은 M학점입니다.
DECLARE
SCORE NUMBER;
GRADE VARCHAR2(1);
BEGIN
SCORE := '&점수';
IF SCORE >= 90
THEN GRADE := 'A';
ELSIF SCORE >= 80
THEN GRADE := 'B';
ELSIF SCORE >= 70
THEN GRADE := 'C';
ELSIF SCORE >= 60
THEN GRADE := 'D';
ELSE GRADE := 'F';
END IF;
DBMS_OUTPUT.PUT_LINE('당신의 점수는 '||SCORE||'점이고, 학점은 '||GRADE||'점 입니다');
END;
/
-- 점수를 입력받아 SCORE 변수에 저장
-- 90점 이상이면 A, 80이상이면 B, 70이상은 C, 60점 이상은 D, 그 미만은 F 처리하여 GRADE 변수에 저장
-- 출력형식 : 당신의 점수는 N점이고, 학점은 M학점입니다.
DECLARE
SCORE NUMBER;
GRADE VARCHAR2(1);
BEGIN
SCORE := '&점수';
IF SCORE >= 90
THEN GRADE := 'A';
ELSIF SCORE >= 80
THEN GRADE := 'B';
ELSIF SCORE >= 70
THEN GRADE := 'C';
ELSIF SCORE >= 60
THEN GRADE := 'D';
ELSE GRADE := 'F';
END IF;
DBMS_OUTPUT.PUT_LINE('당신의 점수는 '||SCORE||'점이고, 학점은 '||GRADE||'학점 입니다.');
END;
/
------------------------------------------------------------------------------
------------------------------------------------------------------------------
