012. UPDATE 예제, DELETE, 뷰(VIEW), [PL/SQL ] IF 문, ACCEPT(&외부변수), 기본 반복문(LOOP), WHILE LOOP , FOR LOOP

SsY/Class 2023. 4. 6. 17:55

728x90

2023.04.06 (목)

HR 계정 실습

UPDATE 실습 이어서...

--○ EMPLOYEES 테이블에서 JOB_TITLE 이 『Sales Manager』인 사원들의
--   SALARY 를 해당 직무(직종)의 최고 급여(MAX_SALARY)로 수정한다.
--   단, 입사일이 2006년 이전(해당 년도 제외) 입사자에 한하여
--   적용할 수 있도록 처리한다.
--   (어제와 마찬가지로 쿼리문을 작성 및 실행하여 결과를 확인한 후 ROLLBACK)

-- 내 풀이
UPDATE EMPLOYEES
SET SALARY 를 해당 직무(직종)의 최고 급여(MAX_SALARY)로 수정
WHERE JOB_TITLE 이 『Sales Manager』인 사원
      입사일이 2006년 이전(해당 년도 제외) 입사자;

UPDATE EMPLOYEES
SET SALARY =  Sales Manager 최고 급여(MAX_SALARY)
WHERE JOB_ID = 'SA_MAN'
      HIRE_DATE 가 2006년 이전 

UPDATE EMPLOYEES
SET SALARY = (SELECT MAX_SALARY
              FROM JOBS
              WHERE JOB_TITLE='Sales Manager')
WHERE JOB_ID = ( SELECT JOB_ID
                 FROM JOBS
                 WHERE JOB_TITLE ='Sales Manager')
  AND EXTRACT(YEAR FROM HIRE_DATE) < 2006; 
--==>> 3개 행 이(가) 업데이트되었습니다.

SELECT *
FROM EMPLOYEES
WHERE JOB_ID = ( SELECT JOB_ID
                 FROM JOBS
                 WHERE JOB_TITLE ='Sales Manager');
--==>>
/*
145	John	Russell	    JRUSSEL	    011.44.1344.429268	2004-10-01	SA_MAN	20080	0.4	100	80
146	Karen	Partners    	KPARTNER	011.44.1344.467268	2005-01-05	SA_MAN	20080	0.3	100	80
147	Alberto	Errazuriz	AERRAZUR	011.44.1344.429278	2005-03-10	SA_MAN	20080	0.3	100	80
148	Gerald	Cambrault	GCAMBRAU	011.44.1344.619268	2007-10-15	SA_MAN	11000	0.3	100	80
149	Eleni	Zlotkey	    EZLOTKEY	011.44.1344.429018	2008-01-29	SA_MAN	10500	0.2	100	80
*/

ROLLBACK;
--==>> 롤백 완료.

UPDATE EMPLOYEES
SET SALARY = (Sales Manager 의 MAX_SALARY)
WHERE JOB_ID = (Sales Manager 의 JOB_ID)
      HIRE_DATE = 2006년 이전;
      
UPDATE EMPLOYEES
SET SALARY = (Sales Manager 의 MAX_SALARY)
WHERE JOB_ID = (Sales Manager 의 JOB_ID)
      TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY')) < 2006;
      
--(Sales Manager 의 MAX_SALARY)
SELECT MAX_SALARY
FROM JOBS
WHERE JOB_TITLE = 'Sales Manager';
--==>>20080

--(Sales Manager 의 JOB_ID)
SELECT JOB_ID
FROM JOBS
WHERE JOB_TITLE = 'Sales Manager';
--==>> SA_MAN

SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE JOB_ID = (SELECT JOB_ID
                FROM JOBS
                WHERE JOB_TITLE = 'Sales Manager')
  AND TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY')) < 2006;
--==>>
/*
John	14000
Karen	13500
Alberto	12000
*/

UPDATE EMPLOYEES
SET SALARY = 20080
WHERE JOB_ID = (SELECT JOB_ID
                FROM JOBS
                WHERE JOB_TITLE = 'Sales Manager')
  AND TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY')) < 2006;

UPDATE EMPLOYEES
SET SALARY = (SELECT MAX_SALARY
              FROM JOBS
              WHERE JOB_TITLE = 'Sales Manager')
WHERE JOB_ID = (SELECT JOB_ID
                FROM JOBS
                WHERE JOB_TITLE = 'Sales Manager')
  AND TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY')) < 2006;
--==>> 3개 행 이(가) 업데이트되었습니다.

SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE JOB_ID = (SELECT JOB_ID
                FROM JOBS
                WHERE JOB_TITLE = 'Sales Manager')
  AND TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY')) < 2006;
--==>>
/*
John	20080
Karen	20080
Alberto	20080
*/

ROLLBACK;
--==>> 롤백 완료.

--○ EMPLOYEES 테이블에서 SALARY 를
--   각 부서의 이름 별로 다른 인상률을 적용하여 수정할 수 있도록 한다.
--   Finance → 인상률 10%
--   Executive → 인상률 15%
--   Accounting → 인상률 20%
--   (이 또한 쿼리문을 작성하고 수행하여 결과 확인 후 ROLLBACK 할 것)

SELECT FIRST_NAME, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
                        FROM DEPARTMENTS
                        WHERE DEPARTMENT_NAME IN ('Finance','Executive','Accounting'));
--==>>
/*
Steven	90	24000
Neena	90	17000
Lex	90	17000
Nancy	100	12008
Daniel	100	9000
John	100	8200
Ismael	100	7700
Jose Manuel	100	7800
Luis	100	6900
Shelley	110	12008
William	110	8300
*/

-- CASE WHEN THEN ELSE END 
UPDATE EMPLOYEES 
SET SALARY = CASE DEPARTMENT_ID WHEN (SELECT DEPARTMENT_ID
                             FROM DEPARTMENTS
                             WHERE DEPARTMENT_NAME IN ('Finance'))
                       THEN SALARY*1.1 
                       WHEN (SELECT DEPARTMENT_ID
                             FROM DEPARTMENTS
                             WHERE DEPARTMENT_NAME IN ('Executive')) 
                       THEN SALARY*1.15  
                       WHEN (SELECT DEPARTMENT_ID
                             FROM DEPARTMENTS
                             WHERE DEPARTMENT_NAME IN ('Accounting')) 
                       THEN SALARY*1.2  
                       ELSE SALARY
             END
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
                        FROM DEPARTMENTS
                        WHERE DEPARTMENT_NAME IN ('Finance','Executive','Accounting'));
-- DECODE()로 
UPDATE EMPLOYEES
SET SALARY = DECODE(DEPARTMENT_ID,(SELECT DEPARTMENT_ID
                         FROM DEPARTMENTS
                         WHERE DEPARTMENT_NAME ='Finance')
                        , SALARY*1.1
                        ,(SELECT DEPARTMENT_ID
                         FROM DEPARTMENTS
                         WHERE DEPARTMENT_NAME ='Executive')
                       , SALARY*1.15
                       ,(SELECT DEPARTMENT_ID
                         FROM DEPARTMENTS
                         WHERE DEPARTMENT_NAME ='Accounting')
                       , SALARY*1.2)
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
                        FROM DEPARTMENTS
                        WHERE DEPARTMENT_NAME IN ('Finance','Executive','Accounting'));


-- 확인
SELECT FIRST_NAME, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
                        FROM DEPARTMENTS
                        WHERE DEPARTMENT_NAME IN ('Finance','Executive','Accounting'));
--==>>
/*
Steven	90	27600
Neena	90	19550
Lex	90	19550
Nancy	100	13208.8
Daniel	100	9900
John	100	9020
Ismael	100	8470
Jose Manuel	100	8580
Luis	100	7590
Shelley	110	14409.6
William	110	9960
*/
--==>> 11개 행 이(가) 업데이트되었습니다.

ROLLBACK;
--==>> 롤백 완료.

UPDATE EMPLOYEES
SET SALARY = 조건에 따른 분기
WHERE 부서 아이디가 ('Finance','Executive','Accounting');

UPDATE EMPLOYEES
SET SALARY = CASE DEPARTMENT_ID WHEN ('Finance'의 부서아이디) 
                                THEN SALARY * 1.1 
                                WHEN ('Executive'의 부서아이디) 
                                THEN SALARY * 1.15 
                                WHEN ('Accounting'의 부서아이디) 
                                THEN SALARY * 1.2 
                                ELSE SALARY
             END
WHERE 부서 아이디가 ('Finance','Executive','Accounting');

UPDATE EMPLOYEES
SET SALARY = CASE DEPARTMENT_ID WHEN ('Finance'의 부서아이디) 
                                THEN SALARY * 1.1 
                                WHEN ('Executive'의 부서아이디) 
                                THEN SALARY * 1.15 
                                WHEN ('Accounting'의 부서아이디) 
                                THEN SALARY * 1.2 
                                ELSE SALARY
             END
WHERE DEPARTMENT_ID가 ('Finance'의 부서아이디, 'Executive'의 부서아이디, 'Accounting'의 부서아이디);

UPDATE EMPLOYEES
SET SALARY = CASE DEPARTMENT_ID WHEN ('Finance'의 부서아이디) 
                                THEN SALARY * 1.1 
                                WHEN ('Executive'의 부서아이디) 
                                THEN SALARY * 1.15 
                                WHEN ('Accounting'의 부서아이디) 
                                THEN SALARY * 1.2 
                                ELSE SALARY
             END
WHERE DEPARTMENT_ID가 ('Finance'의 부서아이디, 'Executive'의 부서아이디, 'Accounting'의 부서아이디);
--------------------------------------------------------------------------------------------------------------
-- ('Finance'의 부서아이디)
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME='Finance';
--==>100

-- ('Executive'의 부서아이디)
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME='Executive';
--==>90

-- ('Accounting'의 부서아이디))
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME='Accounting';
--==>110
--------------------------------------------------------------------------------------------------------------
UPDATE EMPLOYEES
SET SALARY = CASE DEPARTMENT_ID WHEN (SELECT DEPARTMENT_ID
                                      FROM DEPARTMENTS
                                      WHERE DEPARTMENT_NAME='Finance') 
                                THEN SALARY * 1.1 
                                WHEN (SELECT DEPARTMENT_ID
                                      FROM DEPARTMENTS
                                      WHERE DEPARTMENT_NAME='Executive') 
                                THEN SALARY * 1.15 
                                WHEN (SELECT DEPARTMENT_ID
                                      FROM DEPARTMENTS
                                      WHERE DEPARTMENT_NAME='Accounting') 
                                THEN SALARY * 1.2 
                                ELSE SALARY
             END
WHERE DEPARTMENT_ID가 ('Finance'의 부서아이디, 'Executive'의 부서아이디, 'Accounting'의 부서아이디);
--------------------------------------------------------------------------------------------------------------
-- ('Finance'의 부서아이디, 'Executive'의 부서아이디, 'Accounting'의 부서아이디)
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME IN ('Finance','Executive','Accounting');
--==>> 
/*
90
100
110
*/
--------------------------------------------------------------------------------------------------------------
-- 최종 쿼리문
UPDATE EMPLOYEES
SET SALARY = CASE DEPARTMENT_ID WHEN (SELECT DEPARTMENT_ID
                                      FROM DEPARTMENTS
                                      WHERE DEPARTMENT_NAME='Finance') 
                                THEN SALARY * 1.1 
                                WHEN (SELECT DEPARTMENT_ID
                                      FROM DEPARTMENTS
                                      WHERE DEPARTMENT_NAME='Executive') 
                                THEN SALARY * 1.15 
                                WHEN (SELECT DEPARTMENT_ID
                                      FROM DEPARTMENTS
                                      WHERE DEPARTMENT_NAME='Accounting') 
                                THEN SALARY * 1.2 
                                ELSE SALARY
             END
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
                        FROM DEPARTMENTS
                        WHERE DEPARTMENT_NAME IN ('Finance','Executive','Accounting'));
--==>> 11개 행 이(가) 업데이트되었습니다.

-- 확인
SELECT FIRST_NAME, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
                        FROM DEPARTMENTS
                        WHERE DEPARTMENT_NAME IN ('Finance','Executive','Accounting'));
--==>>
/*
Steven	90	27600
Neena	90	19550
Lex	90	19550
Nancy	100	13208.8
Daniel	100	9900
John	100	9020
Ismael	100	8470
Jose Manuel	100	8580
Luis	100	7590
Shelley	110	14409.6
William	110	9960
*/

ROLLBACK;
--==>> 롤백 완료.
--------------------------------------------------------------------------------------------------------------
--// 아래 구문만 수행해도 동일한 결과물을 얻을 수 있다. 
--// 다만, 이 경우에는 11개 행을 업데이트 하는데 107개 행을 업데이트 함으로써 메모리를 더 많이 사용하게 된다.
--// 따라서, 위 구문을 통해서 수행하는 것이 바람직하다.
UPDATE EMPLOYEES
SET SALARY = CASE DEPARTMENT_ID WHEN (SELECT DEPARTMENT_ID
                                      FROM DEPARTMENTS
                                      WHERE DEPARTMENT_NAME='Finance') 
                                THEN SALARY * 1.1 
                                WHEN (SELECT DEPARTMENT_ID
                                      FROM DEPARTMENTS
                                      WHERE DEPARTMENT_NAME='Executive') 
                                THEN SALARY * 1.15 
                                WHEN (SELECT DEPARTMENT_ID
                                      FROM DEPARTMENTS
                                      WHERE DEPARTMENT_NAME='Accounting') 
                                THEN SALARY * 1.2 
                                ELSE SALARY
             END;
--==>> 107개 행 이(가) 업데이트되었습니다.

DELETE

--■■■ DELETE ■■■--

-- 1. 테이블에서 지정된 행(레코드)을 삭제하는 데 사용하는 구문.

-- 2. 형식 및 구조
-- DELETE [FROM] 테이블명
-- [WHERE 조건절];

SELECT *
FROM EMPLOYEES
WHERE EMPLOYEE_ID=198;

DELETE
FROM EMPLOYEES
WHERE EMPLOYEE_ID=198;

--○ EMPLOYEES 테이블에서 직원들의 데이터를 삭제한다.
--   단, 부서명이 'IT'인 경우로 한정한다. 

--※ 실제로는 EMPLOYEES 테이블의 데이터가(삭제하고자 하는 대상)
--   다른 테이블(혹은 자기 자신 테이블)에 의해 참조당하고 있는 경우
--   삭제되지 않을 수 있다는 사실을 염두해야 하며...
--   그에 대한 이유도 알아야 한다.

SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                       FROM DEPARTMENTS
                       WHERE DEPARTMENT_NAME='IT');
--==>>
/*
103	Alexander	Hunold	    AHUNOLD	    590.423.4567	2006-01-03	IT_PROG	9000		102	60
104	Bruce	    Ernst	    BERNST	    590.423.4568	2007-05-21	IT_PROG	6000		103	60
105	David	    Austin	    DAUSTIN	    590.423.4569	2005-06-25	IT_PROG	4800		103	60
106	Valli	    Pataballa	VPATABAL	590.423.4560	2006-02-05	IT_PROG	4800		103	60
107	Diana	    Lorentz	    DLORENTZ	590.423.5567	2007-02-07	IT_PROG	4200		103	60
*/

DELETE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                       FROM DEPARTMENTS
                       WHERE DEPARTMENT_NAME='IT');
--==>> 에러 발생 // DEPARTMENT 의 MANAGER_ID 가 FK 로 해당 사원을 참조하고 있기 때문에
/*
ORA-02292: integrity constraint (HR.DEPT_MGR_FK) violated - child record found
*/

SELECT *
FROM DEPARTMENTS;
--==>>
/*
        :
60	IT	103	1400
        :
*/

SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;

DELETE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;

SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID = ('IT'의 부서번호);

--('IT'의 부서번호)
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'IT';

SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID = ( SELECT DEPARTMENT_ID
                        FROM DEPARTMENTS
                        WHERE DEPARTMENT_NAME = 'IT');
--==>>
/*
103	Alexander	Hunold	AHUNOLD	590.423.4567	2006-01-03	IT_PROG	9000		102	60
104	Bruce	Ernst	BERNST	590.423.4568	2007-05-21	IT_PROG	6000		103	60
105	David	Austin	DAUSTIN	590.423.4569	2005-06-25	IT_PROG	4800		103	60
106	Valli	Pataballa	VPATABAL	590.423.4560	2006-02-05	IT_PROG	4800		103	60
107	Diana	Lorentz	DLORENTZ	590.423.5567	2007-02-07	IT_PROG	4200		103	60
*/

DELETE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = ( SELECT DEPARTMENT_ID
                        FROM DEPARTMENTS
                        WHERE DEPARTMENT_NAME = 'IT');
--==>> 에러 발생
/*
ORA-02292: integrity constraint (HR.DEPT_MGR_FK) violated - child record found
*/
--// 물론 이 경우에도 ON DELETE CASCADE 조건이 없더라도 강제로 CASCADE 로 지울 수는 있지만 
--// 참조하고 있는 데이터가 모두 날라가기 때문에 바람직하지 않다.

뷰(VIEW)

--■■■ 뷰(VIEW) ■■■--

--1. 뷰(VIEW)란 이미 특정한 데이터베이스 내에 존재하는
--   하나 이상의 테이블에서 사용자가 얻기 원하는 데이터들만을
--   정확하고 편하게 가져오기 위해서 사전에 원하는 컬럼들만 모아
--   만들어놓은 가상의 테이블로 편의성 및 보안에 목적이 있다.

--   가상의 테이블이란 뷰가 실제로 존재하는 테이블(객체)이 아니라
--   하나 이상의 테이블에서 파생된 또다른 정보를 볼 수 있는 방법이며
--   그 정보를 추출해내는 SQL 문장이라고 볼 수 있다.

--2. 형식 및 구조
-- CREATE [OR REPLACE] VIEW 뷰이름
-- [(ALIAS[, ALIAS, ...])]
-- AS
-- 서브쿼리(SUBQUERY)
-- [WITH CHECK OPTION] --// 뷰를 볼 수 있는 옵션을 제한
-- [WITH READ ONLY];   --// 볼 수 만 있게 만드는 것(변경 등 X)

생성, 조회, 구조 확인

--○ 뷰(VIEW) 생성
CREATE OR REPLACE VIEW VIEW_EMPLOYEES
AS
SELECT E.FIRST_NAME, E.LAST_NAME
     , D.DEPARTMENT_NAME, L.CITY
     , C.COUNTRY_NAME, R.REGION_NAME
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L, COUNTRIES C, REGIONS R
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID(+)
  AND D.LOCATION_ID = L.LOCATION_ID(+)
  AND L.COUNTRY_ID = C.COUNTRY_ID(+)
  AND C.REGION_ID = R.REGION_ID(+);
--==>> View VIEW_EMPLOYEES이(가) 생성되었습니다.

--○ 뷰(VIEW) 조회
SELECT *
FROM VIEW_EMPLOYEES;
--// 위의 구문은 아래와 동일함
SELECT *
FROM
(
    SELECT E.FIRST_NAME, E.LAST_NAME
         , D.DEPARTMENT_NAME, L.CITY
         , C.COUNTRY_NAME, R.REGION_NAME
    FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L, COUNTRIES C, REGIONS R
    WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID(+)
      AND D.LOCATION_ID = L.LOCATION_ID(+)
      AND L.COUNTRY_ID = C.COUNTRY_ID(+)
      AND C.REGION_ID = R.REGION_ID(+)
);

--○ 뷰(VIEW) 구조 확인
DESC VIEW_EMPLOYEES;
--==>>
/*
이름              널?       유형           
--------------- -------- ------------ 
FIRST_NAME               VARCHAR2(20) 
LAST_NAME       NOT NULL VARCHAR2(25) 
DEPARTMENT_NAME          VARCHAR2(30) 
CITY                     VARCHAR2(30) 
COUNTRY_NAME             VARCHAR2(40) 
REGION_NAME              VARCHAR2(25) 
*/

소스확인

--○ 뷰(VIEW) 소스 확인             -- CHECK
SELECT VIEW_NAME, TEXT              -- TEXT // 해당 컬럼의 확인 필요
FROM USER_VIEWS                     -- USER_VIEWS // 확인할 수 있는 데이터 딕셔너리
WHERE VIEW_NAME = 'VIEW_EMPLOYEES';

-- TEXT (Ctrl+C → Ctrl+V)
/*
"SELECT E.FIRST_NAME, E.LAST_NAME
     , D.DEPARTMENT_NAME, L.CITY
     , C.COUNTRY_NAME, R.REGION_NAME
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L, COUNTRIES C, REGIONS R
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID(+)
  AND D.LOCATION_ID = L.LOCATION_ID(+)
  AND L.COUNTRY_ID = C.COUNTRY_ID(+)
  AND C.REGION_ID = R.REGION_ID(+)"
*/

SCOTT 계정 실습

PL/SQL

--■■■ PL/SQL ■■■-- 
-- // 오라클의 프로그래밍파트 라고 생각하면 됨 
-- // ★ 특정 영역을 선택(드래그)해서 실행해야 함

--1. PL/SQL(Procedural Language extension to SQL) 은
--   프로그래밍 언어의 특성을 가지는 SQL 의 확장이며, 
--   데이터 조작과 질의 문장은 PL/SQL 의 절차적 코드 안에 포함된다.
--   또한, PL/SQL 을 사용하면 SQL 로 할 수 없는 절차적 작업이 가능하다.
--   여기에서 『절차적』 이라는 단어가 가지는 의미는
--   어떤 것이 어떤 과정을 거쳐 어떻게 완료되는지
--   그 방법을 정확하게 코드에 기술한다는 것을 의미한다.

--2. PL/SQL 은 절차적으로 표현하기 위해
--   변수를 선언할 수 있는 기능,
--   참과 거짓을 구별할 수 있는 기능,
--   실행 흐름을 컨트롤할 수 있는 기능 등을 제공한다.

--3. PL/SQL 은 블럭 구조로 되어있으며
--   블럭은 크게 선언 부분, 실행 부분, 예외 처리 부분의
--   세 부분으로 구성되어 있다.
--   또한, 반드시 실행 부분은 존재해야 하며, 구조는 다음과 같다.
--// 선언이나 예외처리부분은 없을 수 있지만 실행부분은 반드시 있어야한다.

--4. 형식 및 구조
/*
[DECLARE]
    -- 선언문(declarations)
BEGIN
    -- 실행문(statements)
    
    [EXCEPTION]
        -- 예외 처리문(exception handlers)
END;
*/

--5. 변수 선언
/*
DECLARE
    변수명 자료형;
    변수명 자료형 : =초기값;
BEGIN
    PL/SQL 구문;
END;
*/

관찰

SET SERVEROUTPUT ON;
--==>> 작업이 완료되었습니다.(0.xxx초)
--> 『DBMS_OUTPUT.PUT_LINE()』을 통해
--  화면에 결과를 출력하기 위한 환경변수 설정
--// 최초에 한 번만 실행해주면 된다.

--○ 변수에 임의의 값을 대입하고 출력하는 구문 작성
DECLARE
    -- 선언부 // 변수 등 선언 // 데이터 타입이 뒤에 붙게 된다. 
            --// 선언시에는 단위별 세미콜론을 붙인다. // 대입연산자는 := (오른쪽에 있는걸 왼쪽에 대입)
    D1 NUMBER:=10;
    D2 VARCHAR2(30):='HELLO';
    D3 VARCHAR2(20):='Oracle';
BEGIN
    -- 실행부 // 출력 
    --// DBMS_OUTPUT 이라는 패키지 내에서 PUT_LINE() 함수를 꺼내 쓰는 것
    DBMS_OUTPUT.PUT_LINE(D1);
    DBMS_OUTPUT.PUT_LINE(D2);
    DBMS_OUTPUT.PUT_LINE(D3);
    
END;    --//DECLARE ~ END; 이기때문에 END 뒤에만 세미콜론
        --// PL/SQL 을 구성할 때 가장 기본적인 틀
--// 환경변수 설정 전 결과 : 완료되긴 했는데 아무것도출력되지 않음
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.

--// 환경변수 설정 후 결과
--==>>
/*
10
HELLO
Oracle


PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/


--○ 변수에 임의의 값을 대입하고 출력하는 구문 작성
DECLARE
    -- 선언부 
    D1 NUMBER:=10;
    D2 VARCHAR2(30):='HELLO';
    D3 VARCHAR2(20):='Oracle';
BEGIN
    -- 실행부 
    D1 := D1 * 10;              --// SQL은 복합대입연산자는 없다 // 대입연산자는 자바와 같이 우측부터 처리한다.
    D2 := D2 || '건우';
    D3 := D3 || 'World';
    DBMS_OUTPUT.PUT_LINE(D1);
    DBMS_OUTPUT.PUT_LINE(D2);
    DBMS_OUTPUT.PUT_LINE(D3);
    
END;
--==>>
/*
100
HELLO건우
OracleWorld


PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/

IF문(조건문)

--○ IF문(조건문)
--// 틀을 잡아놓고 시작하기
-- IF ~ END IF;
-- IF ~ THEN ~ ELSE ~ END IF;
-- IF ~ THEN ~ ELSIF ~  THEN ~ ELSE ~ END IF;

-- 1. PL/SQL 의  IF 문장은 다른 언어의 IF 조건문과 거의 유사하다.
--    일치하는 조건에 따라 선택적으로 작업을 수행할 수 있도록 한다.
--    TRUE 이면 THEN 과 ELSE 사이의 문장을 수행하고,
--    FALSE 나 NULL 이면 ELSE 와 END 사이의 문장을 수행하게 된다.

-- 2. 형식 및 구조
/*
IF 조건   --// TRUE FALSE 로 반환되는 조건
   THEN 처리구문;
ELSIF 조건
   THEN 처리구문;
ELSIF 조건
   THEN 처리구문;
ELSE
    처리구문;
END IF;
*/

관찰

--○ 변수에 들어있는 값에 따라...
--   Excellent, Good, Fail 로 구분하여
--   결과를 출력하는 PL/SQL 구문을 작성한다.
DECLARE
    GRADE   CHAR;
BEGIN
    GRADE := 'C';
    
    IF GRADE ='A'
        THEN DBMS_OUTPUT.PUT_LINE('Excellent');
    ELSIF GRADE = 'B'
        THEN DBMS_OUTPUT.PUT_LINE('Good');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Fail');
    END IF;
END;
--==>>
/*
Fail


PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/

ACCEPT ( 외부변수 받아오기)

※ 스크립트 출력을 통해서 확인하는 것으로, 추후 실무에서 사용할 일은 없음...
기본 PL/SQL문법과는 다르다.

--○ 외부 입력 처리

-- 1. ACCEPT 문
-- 『ACCEPT 변수명 PROMPT '메세지';』
-- 외부 변수로부터 입력받은 데이터를 내부 변수에 전달할 때
-- 『&외부변수명』 형태로 접근하게 된다.

-- 정수 2개를 외부로부터(사용자로부터) 입력받아
-- 이들의 덧셈 결과를 출력하는 PL/SQL 구문을 작성한다.

ACCEPT N1 PROMPT '첫 번째 정수를 입력하세요';
ACCEPT N2 PROMPT '두 번째 정수를 입력하세요';

ACCEPT N1 PROMPT '첫 번째 정수를 입력하세요';  --// 입력시에 N1 변수에 임시 저장됨
ACCEPT N2 PROMPT '두 번째 정수를 입력하세요';

--// PL/SQL의 기본구문은 아래와같고 위의 구문은 별도라고 생각하면 된다.
DECLARE
    -- 주요 변수 선언 및 초기화
    NUM1    NUMBER :=&N1;   --// 임시 저장된 외부변수를 다시 담는 것
    NUM2    NUMBER :=&N2;
    TOTAL   NUMBER :=0;
BEGIN
    -- 연산 및 처리
    TOTAL := NUM1 + NUM2;
    
    -- 결과 출력
    DBMS_OUTPUT.PUT_LINE(TOTAL);
END;
--==>>
/*
110


PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/

--○ 사용자로부터 입력받은 금액을 화폐 단위로 출력하는 프로그램을 작성한다.
--   단, 반환 금액은 편의상 1천원 미만, 10원 이상만 가능하다고 가정한다.
/*
실행 예)
바인딩 변수 입력 대화창 → 금액 입력 : 990

입력받은 금액 총액 : 990원
화폐단위 : 오백원 1, 백원 4, 오십원 1, 십원 4
*/

ACCEPT N1 PROMPT '금액 입력'

DECLARE
    TOTAL   NUMBER := &N1;
    MONEY   NUMBER := &N1;
    N500    NUMBER :=0;
    N100    NUMBER :=0;
    N50     NUMBER :=0;
    N10     NUMBER :=0;
BEGIN
    IF TRUNC(MONEY/500)>0
        THEN N500 := N500+TRUNC(MONEY/500);
             MONEY := MONEY-(500*TRUNC(MONEY/500));
    ELSE
        N500 := N500;
    END IF;
    
    IF TRUNC(MONEY/100)>0
        THEN N100 := N100 + TRUNC(MONEY/100);
             MONEY := MONEY-(100*TRUNC(MONEY/100));
    ELSE
        N100 := N100;
    END IF;
    
    IF TRUNC(MONEY/50)>0
        THEN N50 := N50 + TRUNC(MONEY/50);
             MONEY := MONEY-(50*TRUNC(MONEY/50));
    ELSE
        N50 := N50;
    END IF;
    
    N10 := TRUNC(MONEY/10);
    
    --출력
    DBMS_OUTPUT.PUT_LINE('입력받은 금액 총액 '||TOTAL||' 원');
    DBMS_OUTPUT.PUT_LINE('화폐단위 : 오백원'||N500||', 백원 '||N100||', 오십원'||N50||', 십원'||N10);

END;
--==>>
/*
입력받은 금액 총액 890 원
화폐단위 : 오백원1, 백원 3, 오십원1, 십원4

ACCEPT INPUT PROMPT '금액 입력';

DECLARE
    --○ 주요 변수 선언 및 초기화
    MONEY   NUMBER := &INPUT;   -- 연산을 위해 담아둘 변수
    MONEY2  NUMBER := &INPUT;   -- 최종 결과 출력을 위해 담아둘 변수 (연산 과정에서 값이 변하기 때문에)
    
    M500    NUMBER;             -- 500 원 짜리 갯수를 담아둘 변수
    M100    NUMBER;             -- 100 원 짜리 갯수를 담아둘 변수
    M50     NUMBER;             --  50 원 짜리 갯수를 담아둘 변수 
    M10     NUMBER;             --  10 원 짜리 갯수를 담앋둘 변수
    
BEGIN
    --○ 연산 및 처리
    -- MONEY 를 500 으로 나눠서 몫을 취하고 나머지는 버린다. → 500 원의 갯수
    M500 := TRUNC(MONEY/500);
    
    -- MONEY 를 500 으로 나눠서 몫은 버리고 나머지만 취한다
    MONEY := MOD(MONEY,500);
    
    -- MONEY 를 100 으로 나눠서 몫을 취하고 나머지는 버린다. → 100 원의 갯수
    M100 := TRUNC(MONEY/100);
    
    -- MONEY 를 100 으로 나눠서 몫은 버리고 나머지만 취한다
    MONEY := MOD(MONEY,100);
    
    -- MONEY 를 50 으로 나눠서 몫을 취하고 나머지는 버린다. → 50 원의 갯수
    M50 := TRUNC(MONEY/50);
    
    -- MONEY 를 100 으로 나눠서 몫은 버리고 나머지만 취한다
    MONEY := MOD(MONEY,50);
    
    -- MONEY 10으로 나눠서 몫을 취하고 나머지는 버린다. → 10원의 갯수
    M10 := TRUNC(MONEY/10);
    
    
    --○ 결과 출력
    DBMS_OUTPUT.PUT_LINE('입력받은 금액 총액 : '|| MONEY2 || ' 원');
    DBMS_OUTPUT.PUT_LINE('화폐단위 : 오백원'||M500 || ', 백원'||M100
                              ||', 오십원'||M50 || ', 십원' || M10);

END;
--==>>
/*
입력받은 금액 총액 : 980 원
화폐단위 : 오백원1, 백원4, 오십원1, 십원3


PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/

반복문

기본 반복문 LOOP

--○ 기본 반복문
-- LOOP ~ END LOOP;

--1. 조건과 상관없이 무조건 반복하는 구문.

--2. 형식 및 구조
/*
LOOP
    -- 실행문;
    EXIT WHEN 조건;       -- 조건이 참인 경우 반복문을 빠져나간다.//자바의 BREAK 와 같은 역할
END LOOP;
*/

- 관찰

--○ 1 부터 10 까지의 수 출력(LOOP 활용)

DECLARE
    N   NUMBER;
BEGIN
    N := 1;
    LOOP
        DBMS_OUTPUT.PUT_LINE(N);
        EXIT WHEN N>=10;
        N := N + 1;              -- N++;    N+=1;
    END LOOP;
END;
--==>>
/*
1
2
3
4
5
6
7
8
9
10


PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/

WHILE 반복문

--○ WHILE 반복문
--  WHILE LOOP ~ END LOOP;


-- 1. 제어 조건이 TRUE 인 동안 일련의 문장을 반복하기 위해
--    WHILE LOOP 문을 사용한다.
--    조건은 반복이 시작할 때 체크하게 되어어
--    LOOP 내의 문장이 한 번도 수행되지 않을 수 도 있다.
--    다시 LOOP 를 시작할 때 조건이 FALSE 이면 반복 문장을 탈출하게 된다.

-- 2. 형식 및 구조
/*
WHILE 조건 LOOP   -- 조건이 참인 경우 반복 수행
    -- 실행문;
END LOOP;
*/

- 관찰

--○ 1부터 10 까지의 수 출력(WHILE LOOP 문 활용)

DECLARE
    N   NUMBER;
BEGIN
    N := 0;
    WHILE N<10 LOOP
        N := N + 1;
        DBMS_OUTPUT.PUT_LINE(N);
    END LOOP;
END;
--==>>
/*
1
2
3
4
5
6
7
8
9
10


PL/SQL 프로시저가 성공적으로 완료되었습니다.

*/

FOR 반복문

--○ FOR 반복문 
-- FOR LOOP ~ END LOOP;

-- 1. 『시작 수』에서 1씩 증가하여
--    『끝냄 수』가 될 때 까지 반복 수행한다. 

-- 2. 형식 및 구조 // 자바의 FOR EACH 구문과 비슷
/*
FOR 카운터 IN [REVERSE] 시작수 .. 끝냄수 LOOP     // '..'도 문법임 // 카운터는 변수임   
    -- 실행문;
END LOOP;
*/

- 관찰

--○ 1 부터 10 까지의 수 출력(FOR LOOP 문 활용)
DECLARE
    N   NUMBER;     --FOR   LOOP 의 특성상 시작수 .. 끝냄수 까지 표현해주기 때문에 초기화 하지 않아도 됨.
BEGIN
    FOR N IN 1 .. 10 LOOP
        DBMS_OUTPUT.PUT_LINE(N);
    END LOOP;
END;
--==>>
/*
1
2
3
4
5
6
7
8
9
10


PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/

--○ 사용자로부터 임의의 단(구구단)을 입력받아
--   해당 단수의 구구단을 출력하는 PL/SQL 구문을 작성한다
--   LOOP, WHILE LOOP, FOR LOOP 를 통해 해결한다. 
/*
실행 예)
바인딩 변수 입력 대화창 → 단을 입력하세요 : 2

2 * 1 = 2
2 * 2 = 4
  :
2 * 9 = 18
*/

-- LOOP 이용
ACCEPT DAN PROMPT '단을 입력하세요';

DECLARE
    DANN    NUMBER := &DAN;
    MULTI   NUMBER;
BEGIN
    MULTI := 1;
    LOOP
        DBMS_OUTPUT.PUT_LINE(DANN||' * '||MULTI||' = '|| DANN*MULTI);
        MULTI := MULTI + 1;
        EXIT WHEN MULTI>=10;
    END LOOP;
END;
--==>>
/*
2 * 1 = 2
2 * 2 = 4
2 * 3 = 6
2 * 4 = 8
2 * 5 = 10
2 * 6 = 12
2 * 7 = 14
2 * 8 = 16
2 * 9 = 18


PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/

-- WHILE 이용
ACCEPT DAN PROMPT '단을 입력하세요';
DECLARE
    DANN    NUMBER := &DAN;
    MULTI   NUMBER := 1;
BEGIN
    WHILE MULTI<10 LOOP
        DBMS_OUTPUT.PUT_LINE(DANN||' * '||MULTI||' = '||DANN*MULTI);
        MULTI := MULTI + 1;
    END LOOP;
END;
--==>>
/*
2 * 1 = 2
2 * 2 = 4
2 * 3 = 6
2 * 4 = 8
2 * 5 = 10
2 * 6 = 12
2 * 7 = 14
2 * 8 = 16
2 * 9 = 18


PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/

-- FOR 이용

ACCEPT DAN PROMPT '단을 입력하세요';

DECLARE
    DANN    NUMBER := &DAN;
    MULTI   NUMBER;
BEGIN
    FOR MULTI IN 1 .. 9 LOOP
        DBMS_OUTPUT.PUT_LINE(DANN||' * '||MULTI||' = '||DANN*MULTI);
    END LOOP;
END;
--==>>
/*
2 * 1 = 2
2 * 2 = 4
2 * 3 = 6
2 * 4 = 8
2 * 5 = 10
2 * 6 = 12
2 * 7 = 14
2 * 8 = 16
2 * 9 = 18


PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/

--1.LOOP 문의 경우
ACCEPT NUM PROMPT '단을 입력하세요';

DECLARE
    DAN NUMBER := &NUM;
    N   NUMBER;
BEGIN
    N :=1;
    LOOP
        DBMS_OUTPUT.PUT_LINE(DAN || ' * ' || N || ' = ' || (DAN*N));
        EXIT WHEN N>=9;
        N := N+1;
    END LOOP;
END;
--==>>
/*
3 * 1 = 3
3 * 2 = 6
3 * 3 = 9
3 * 4 = 12
3 * 5 = 15
3 * 6 = 18
3 * 7 = 21
3 * 8 = 24
3 * 9 = 27


PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/
-------------------------------------------------------------------------------
--2.WHILE LOOP 문의 경우
ACCEPT NUM PROMPT '단을 입력하세요';

DECLARE
    DAN NUMBER := &NUM;
    N   NUMBER;
BEGIN
    N := 0;
    WHILE N<9 LOOP
        N := N + 1;
        DBMS_OUTPUT.PUT_LINE(DAN || ' * ' || N || ' = ' || (DAN*N));
    END LOOP;
END;
--==>>
/*
4 * 1 = 4
4 * 2 = 8
4 * 3 = 12
4 * 4 = 16
4 * 5 = 20
4 * 6 = 24
4 * 7 = 28
4 * 8 = 32
4 * 9 = 36


PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/
-------------------------------------------------------------------------------
--3.FOR LOOP 문의 경우
ACCEPT NUM PROMPT '단을 입력하세요';

DECLARE
    DAN NUMBER := &NUM;
    N   NUMBER;
BEGIN
    FOR N IN 1 .. 9 LOOP
        DBMS_OUTPUT.PUT_LINE(DAN || ' * ' || N || ' = ' || (DAN*N));
    END LOOP;
END;
--==>>
/*
5 * 1 = 5
5 * 2 = 10
5 * 3 = 15
5 * 4 = 20
5 * 5 = 25
5 * 6 = 30
5 * 7 = 35
5 * 8 = 40
5 * 9 = 45


PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/

HR 계정 실습

TYPE

--○ 『%TYPE』

-- 1. 특정 테이블에 포함되어 있는 컬럼의 자료형을 참조하는 데이터타입

-- 2. 형식 및 구조
-- 변수명 테이블.컬럼명%TYPE [:= 초기값];

- 테이블 내에 있는 컬럼의 데이터를 참조해서 갖고오기 위해서는 만들어준 변수의 데이터 타입을 지정해주어야한다.
해당 컬럼의 데이터를 모두 다 담기 위해서는 컬럼의 데이터타입과 동일하게 해주는 것이 바람직하다.
하지만, 매번 변수의 데이터 타입을 지정해주기 위해서 DESC 구문을 통하여 하는 것은 번잡하다.
이를 간단하게 해결할 수 있는것이 『%TYPE』 이다.
- 내일 이어서...

728x90

저작자표시 비영리 변경금지

'SsY > Class' 카테고리의 다른 글

014. [PL/SQL] PROCEDURE(프로시저),EXCEPTION,TRANSACTION (0)	2023.04.10
013. [PL/SQL] %TYPE / %ROWTYPE (자료형 참조 데이터타입), FUNCTION(함수), PROCEDURE (0)	2023.04.07
011. DEFAULT 표현식, UPDATE (0)	2023.04.05
010. 무결성, 제약 조건(PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK) 추가/ 변경/ 삭제 (0)	2023.04.04
009. 정규화(Normalization), Primary Key, 관계 (0)	2023.04.03

ABOUT ME

Planet for Coding

'SsY > Class' 카테고리의 다른 글

티스토리툴바

ABOUT ME

'SsY > Class' 카테고리의 다른 글

관련글 관련글 더보기

티스토리툴바