-
012. UPDATE 예제, DELETE, 뷰(VIEW), [PL/SQL ] IF 문, ACCEPT(&외부변수), 기본 반복문(LOOP), WHILE LOOP , FOR LOOPSsY/Class 2023. 4. 6. 17:55728x90
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