Everything has an expiration date
[Oracle - PL/SQL] 20231102 [프로그램 소스] 본문
- VIEW
- PL/SQL
- DECLARE
- ACCEPT VAL PROMPT 'MESSAGE'
- DBMS_OUTPUT.PUT_LINE
- 구구단 출력
- LOOP 를 이용한 TBL_INSA 테이블 전체 출력
20231102_01_hr.sql
SELECT USER
FROM DUAL;
--==>> HR
-- [VIEW에 대한 추가 설명]*******************************************************
-- 데이터를 미리 두 개만 넣어놓은 테이블을 VIEW로 만든 후
-- 테이블에 3개를 추가했다면...
-- VIEW에는 그대로 2개만 존재하는 것이 아니라, 추가된 데이터 3개도 반영이 된다.
-- 테이블에 값을 추가할 떄마다 뷰에 하나하나 데이터가 추가된다.
-- VIEW를 생성하고 나면
SELECT *
FROM VIEW_뷰이름;
-- 조촐하게 VIEW의 생성시점을 기반으로 데이터를 확인해 볼 것 같지만...
-- 실제로, 오라클 내부적으로는...
-- 뷰를 구성하는 전체 쿼리문을 실행하는 것과 동일하게 동작.
---★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
-- 데이터가 변경됐으면 변경된 것을 기반으로 VIEW가 반영.
-- 원하는 결과를 얻어내는 쿼리문 자체가 돌아가는 것이기 때문에,
-- 바뀐 데이터가 반영된 형태로 조회되는 것이다.
---★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
-- 민감정보를 저장하는 컬럼이 존재하는 테이블을 VIEW 형태로 만들면
-- 민감정보를 포함하는 컬럼을 사용자에게 노출시키지 않을 수 있다.
-- 즉,
-- [TABLE]
-- ============================================================================
-- COL1 | COL2(민감정보) | COL3 | COL4(민감정보) | COL5 | COL6 | COL7(민감정보)
-- ============================================================================
-- [VIEW]
-- ============================================================================
-- COL1 | COL3 | COL5 | COL6
-- ============================================================================
----▶ 민감정보를 포함하지 않는 COL1, COL3, COL5, COL6 컬럼만 선택하여
-- VIEW에서 조회할 수 있도록 구성 가능.
--******************************************************************************
--■■■ 뷰(VIEW) ■■■--
-- 1. 뷰(VIEW)란 이미 특정한 데이터베이스 내에 존재하는
-- 하나 이상의 테이블에서 사용자가 열기 원하는 데이터들만을
-- 정확하고 편하게 가져오기 위하여 사전에 원하는 컬럼들만을 모아서
-- 만들어놓은 가상의 테이블로 편의성 및 보안에 목적이 있다.
-------------★① -------------------★②
-- 가상의 테이블이란... 뷰가 실제로 존재하는 테이블(객체)이 아니라
-- 하나 이상의 테이블에서 파생된 또 다른 정보를 볼 수 있는 방법이라는 의미이며,
-- 그 정보를 추출해내는 SQL 문장이라고 볼 수 있다.
-- 2. 형식 및 구조
-- CREATE [OR REPLACE] VIEW 뷰이름
-- [(ALIAS,[, ALIAS, ...])]
-- AS
-- 서브쿼리(SUBQUERY)
-- [WITH CHECK OPTION]
-- [WITH READ ONLY]
--○ 뷰(VIEW) 생성
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는 실제 물리적으로 만들어지는 것이 아니라
-- 오라클에게 이 쿼리문을 전달해주는 형식으로 구성하는 것이기 때문에.
-- REPLACE는 테이블 구조 변경시 적용시킬 수 있도록 작성한 키워드.
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이(가) 생성되었습니다.
--★ [HR에게 VIEW를 생성할 수 있는 권한을 GRANT로 이전에 부여해주었기 때문에 작성 가능.]
--○ 뷰(VIEW) 조회
SELECT *
FROM VIEW_EMPLOYEES;
-- 일반 테이블 조회하듯이 데이터를 조회하는 것 가능.
--○ 뷰(VIEW0의 구조 조회
DESC VIEW_EMPLOYEES;
--==>>
/*
--------------- -------- ------------
이름 널? 유형
--------------- -------- ------------
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
DEPARTMENT_NAME VARCHAR2(30)
CITY NOT NULL VARCHAR2(30)
COUNTRY_NAME VARCHAR2(40)
REGION_NAME VARCHAR2(25)
--------------- -------- ------------
*/
-- 뷰 내부의 소스(쿼리문)을 확인하는 것.
-- 어떤 테이블 대상? 어떤 컬럼 조합하여 조회?
--[매우 중요!]
--★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
--○ 뷰(VIEW) 소스 확인 CHECK~!!!
-- 소스를 조회할 때는, TEXT라고 되어있는 컬럼을 복사해서 붙여넣어 보면
-- VIEW를 작성할 때 사용했던 쿼리문이 그대로 보여진다!!
SELECT VIEW_NAME, TEXT
FROM USER_VIEWS
WHERE VIEW_NAME = 'VIEW_EMPLOYEES';
--==>>
/*
"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 를 REPLACE 할 수 있다!!!!
20231102_02_scott(plsql).sql
SELECT USER
FROM DUAL;
--==>> SCOTT
-- 기본은 SQL이지만, 그것의 확장판.
-- 그 앞에 PL이 붙어있는 확장판.
-- P(PROCEDURAL : 프로시저) -- 절차적인.
-- 어떻게 시작해서 어떤 과정, 어떻게 끝났다는 것을 오라클에게 기술해서 작성하는 방법.
-- 자바하고 비슷한 부분이 많아지게 된다.
-- 변수 선언, 조건 분기, 반복문, TRUE / FALSE 분기해가면서 코드 구성.
-- 이것 자체가 절차적...
-- 어떤 블럭단위로 구성해 놓았느냐에 따라 구성이 달라진다.
--■■■ 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
-- 실행문(STARTMENTS)
[EXCEPTON]
-- 예외 처리문(EXCEPTION HANDLERS)
*/
-- 5. 변수 선언
/*
DECLARE
변수명 자료형;
변수명 자료형 := 초기값;
BEGIN
END;
*/
-- [참고사항]
-- SQL 과 PL/SQL 을 구성할 때 시트를 다르게 구성할 것임. (별도로 시트생성.)
-- PL/SQL은 실행할 떄 반드시 블럭을 잡아서 실행하도록 한다.
--※ 블럭(영역)을 잡아(선택하여) 실행~!!! CHECK~!!!
--※ 『DBMS_OUTPUT.PUT_LINE()』 을 통해
-- 화면에 결과를 출력하기 위한 환경변수 설정
-- SESSION처럼 연결이 되어 있을 때 한번만 실행하면 된다.
-- SERVEROUTPUT 을 켜 놓으면... 리소스 소모가 크므로
-- 필요할 때만 켜서 작동시킬 수 있도록 기본 값이 꺼져있는 상태인 것이다.
-- 서버가 켜져있을 땐 계속 사용 가능.
SET SERVEROUTPUT ON;
--==>> 작업이 완료되었습니다.(0.0063초)
--(작업이 완료되었습니다.(0.0063초)) 를 통해 제대로 적용되었음을 알 수 있음.
--==============================================================================
--○ 변수에 임의의 값을 대입하고 출력하는 구문 작성
-- 뼈대부터 작성하고 PL/SQL구문 작성할 것이다.
DECLARE
-- 선언부 (변수, 실행영역에서 사용할 개체들을 선언하는 것.)
--(EX : ⓐ JAVA - 자료형 변수명; )
--( ⓑ ORACLE - 변수명 자료형; )
-- 컬럼의 데이터타입을 지정할 떄도 COL1 NUMBER(10);과 같이 처리했던 것처럼
-- 변수의 선언도 이처럼 변수명, 자료형으로 구성한다.
-- ORACLE '=' : EQUAL로 동작. 대입연산자가 아님.
--**************************************************************
-- ORACLE의 대입연산자 ':=' : □ ← ■ 방향성을 표현하기 위해
-- □ := ■ 이렇게 작성
--*************************************************************
BEGIN
-- 실행부 (연산을 수행하게끔 한다.)
-- (EX : JAVA - System.out.println(V1) );
-- ( ORACLE - DBMS_OUTPUT.PUT_LINE(V1));
-- 결과 출력이 안 되는 이유 : 출력하는 것의 전원이 꺼진 상태이므로.
-- 모니터가 꺼져있는 상태와 마찬가지!
-- (기본값이 꺼져있다.)
END;
DECLARE
-- 선언부
V1 NUMBER := 10;
V2 VARCHAR2(30) := 'HELLO';
V3 VARCHAR2(30) := 'Oracle';
BEGIN
-- 실행부
-- (EX : JAVA - System.out.println(V1);
DBMS_OUTPUT.PUT_LINE(V1);
DBMS_OUTPUT.PUT_LINE(V2);
DBMS_OUTPUT.PUT_LINE(V3);
END;
--==>>
/*
10
HELLO
Oracle
*/
--○ 변수에 임의의 값을 대입하고 출력하는 구문 작성
-- (SQL DEVELOPER : 기본값이 전체 실행이므로 블럭 잡아서 실행.)
DECLARE
-- 선언부
V1 NUMBER := 10;
V2 VARCHAR2(30) := 'HELLO';
V3 VARCHAR2(30) := 'ORACLE';
BEGIN
-- 실행부
--(연산 및 처리)
V1 := V1 + 20; -- NUM1 = NUM1 + 20; → NUM1 += 20; (JAVA에서만 가능. ORACLE에서는 복합대입연산자가 없다.)
V2 := V2 || '정한울';
V3 := V3 || ' World~!!!';
-- (결과 출력) -- DBMS_OUTPUT : System.out.
-- PUT_LINE(V1) : println(V1)
DBMS_OUTPUT.PUT_LINE(V1);
DBMS_OUTPUT.PUT_LINE(V2);
DBMS_OUTPUT.PUT_LINE(V3);
END;
--==>>
/*
30
HELLO정한울
ORACLE World~!!!
*/
/*
-- JAVA
구문
{ --시작
} --끝
-- ORACLE ( {}를 사용하지 않음. )
구문
END 구문
(IF (IF 시작) ~ END IF (IF문 종료))
*/
--○ IF 문(조건문)
-- IF ~ THEN ~ ELSE ~ END IF;
-- 1. PL/SQL 의 IF 문장은 다른 언어의 IF 조건문과 거의 유사하다.
-- 일치하는 조건에 따라 선택적으로 작업을 수행할 수 있도록 한다.
-- TRUE 이면 THEN 과 ELSE 사이의 문장을 수행하고
-- FALSE 나 NULL 이면 ELSE 와 END IF; 사이의 문장을 수행하게 된다.
-- 2. 형식 및 구조
/*
IF 조건
THEN 처리문;
END IF;
*/
--※ ELSE 에는 THEN 을 사용하지 않는다! 주의!
/*
IF 조건
THEN 처리문;
ELSE
처리문;
END IF;
*/
-- IF ELSE 얼마든지 중첩 가능.
/*
[ JAVA ]
IF 조건
THEN 처리문;
ELSE IF
THEN 처리문
ELSE IF
THEN 처리문
ELSE
처리문;
END IF;
[ ORACLE ] : IF ~ END IF를 함께 쓰면 불편하더라.
문법적인 한계를 극복하기 위해 『ELSIF』 라는 키워드를 생성했다.
IF 조건
THEN 처리문;
ELSIF
THEN 처리문
ELSIF
THEN 처리문
ELSE
처리문;
END IF;
*/
/*
IF 조건
THEN 처리문;
ELSE
처리문;
END IF;
*/
--○ 변수에 임의의 값을 대입하고 출력하는 구문 작성
--****************************************************************************************
-- NUMBER : 길이를 명시하지 않았을시, 최대값까지 사용가능. 최대 (38 : 999...999 (38자리))
-- CHAR : 길이를 명시하지 않았을시 최소값을 갖는다. (한 글자 담을거야!)
--****************************************************************************************
DECLARE
-- 선언부
GRADE CHAR;
BEGIN
GRADE := 'B';
--DBMS_OUTPUT.PUT_LINE(GRADE);
IF GRADE = 'A'
THEN DBMS_OUTPUT.PUT_LINE('EXCELLENT');
ELSE
DBMS_OUTPUT.PUT_LINE('FAIL');
END IF;
END;
--==>>
/*
FAIL
PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/
DECLARE
-- 선언부
GRADE CHAR;
BEGIN
GRADE := 'B';
--DBMS_OUTPUT.PUT_LINE(GRADE);
IF GRADE = 'A' -- ⓐ (GRADE와 A가 같다. '=' 사용)
THEN DBMS_OUTPUT.PUT_LINE('EXCELLENT');
--ELSE IF GRADE = 'B' -- ⓑ (ELSE IF → ELSIF로 바꿔야 한다.)
ELSIF GRADE = 'B' -- ⓒ (조건 구성이 온 다음에는 세미콜론 ';' 없음.)
THEN DBMS_OUTPUT.PUT_LINE('BEST'); -- ⓓ (THEN 구절이 끝난 후에는 세미콜론 ';' 사용!)
ELSE
DBMS_OUTPUT.PUT_LINE('FAIL');
END IF;
END;
DECLARE
-- 선언부
GRADE CHAR;
BEGIN
GRADE := 'B';
--DBMS_OUTPUT.PUT_LINE(GRADE);
IF GRADE = 'A'
THEN DBMS_OUTPUT.PUT_LINE('EXCELLENT');
ELSIF GRADE = 'B'
THEN DBMS_OUTPUT.PUT_LINE('BEST');
ELSIF GRADE = 'C'
THEN DBMS_OUTPUT.PUT_LINE('GOOD');
ELSE
DBMS_OUTPUT.PUT_LINE('FAIL');
END IF;
END;
--==>>
/*
BEST
PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/
-- 오라클의 IF문도, 한 가지의 조건에 TRUE가 되면, 그 이후의 ELSIF 문, ELSE 문을 조회하지 않고
-- 바로 END IF 로 간다.
DECLARE
-- 선언부
GRADE CHAR;
BEGIN
GRADE := 'B';
--DBMS_OUTPUT.PUT_LINE(GRADE);
IF GRADE = 'A'
THEN DBMS_OUTPUT.PUT_LINE('EXCELLENT');
ELSIF GRADE = 'B'
THEN DBMS_OUTPUT.PUT_LINE('BEST');
ELSIF GRADE = 'B'
THEN DBMS_OUTPUT.PUT_LINE('GOOD');
ELSE
DBMS_OUTPUT.PUT_LINE('FAIL');
END IF;
END;
/*
BEST
PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/
-- 두 번째 조건 GRADE = 'B' 에 해당하여 'BEST'를 출력하고 나면
-- 세 번째 조건 GRADE = 'B' 에 해당하더라도 이를 실행하지 않고 IF 문 종료
-- SQL 에서는 CASE WHEN THEN ELSE END 였지만, PL/SQL은 END CASE.
-- CASE 문 : JAVA에서의 SWITCH와 유사한 구문.
--○ CASE 문(조건문)
-- CASE - WHEN ~ THEN ~ ELSE ~ END CASE;
-- 1. 형식 및 구조
/*
CASE 변수
WHEN 값1 THEN 실행문;
WHEN 값2 THEN 실행문;
ELSE 실행문;
END CASE;
*/
-- 나중에 자바에서 입력받을 것임.
--『ACCEPT』 : 수용하는 것. 안으로 받아오게끔 한다.
--『PROMPT』 : 사용자에게 안내하는 내용 = '남자1 여자2 입력하세요'
--『NUM』 : 사용자에게 입력받은 값을 NUM이라는 값에 저장하겠다.
ACCEPT NUM PROMPT '남자1 여자2 입력하세요';
DECLARE
-- 선언부
-- (주요 변수 선언)
SEL NUMBER := #
RESULT VARCHAR2(20) := '확인불가';
BEGIN
-- 실행부
-- (테스트)
--DBMS_OUTPUT.PUT_LINE('SEL : ' || SEL);
-- System.out.println("sel : " + sel);
--DBMS_OUTPUT.PUT_LINE('RESULT : ' || RESULT);
-- (연산 및 처리)
/*
--◆[CASE 문 내부 출력]
CASE SEL
WHEN 1
THEN DBMS_OUTPUT.PUT_LINE('남자입니다.');
WHEN 2
THEN DBMS_OUTPUT.PUT_LINE('여자입니다.');
ELSE
DBMS_OUTPUT.PUT_LINE('확인불가');
END CASE;
*/
--◆[CASE 문 내부 변수에 값 대입]
CASE SEL
WHEN 1
THEN RESULT := '남자';
WHEN 2
THEN RESULT := '여자';
ELSE
RESULT := '확인불가';
END CASE;
-- (결과 출력)
DBMS_OUTPUT.PUT_LINE('처리 결과는 ' || RESULT || '입니다.');
END;
--==>>
/*
여자입니다.
PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/
--==>>
/*
처리 결과는 남자입니다.
PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/
--※ 외부 입력 처리
-- ACCEPT 구문
-- ACCEPT 변수명 PROMPT '메세지';
--> 외부 변수로부터 입력받은 데이터를 내부 변수에 전달할 때
-- 『&외부변수명』 형태로 접근하게 된다.
--○ 임의의 정수 2개를 외부로부터(사용자로부터) 입력받아
-- 이들의 덧셈 결과를 출력하는 PL/SQL 구문을 작성한다.
-- 실행 예)
-- 첫 번째 정수를 입력하세요 → 10
-- 두 번째 정수를 입력하세요 → 20
--==>> 10 + 20 = 30
ACCEPT IP_NUM1 PROMPT '첫 번째 정수를 입력하세요 →';
ACCEPT IP_NUM2 PROMPT '두 번째 정수를 입력하세요 →';
DECLARE
VAL1 NUMBER := &IP_NUM1;
VAL2 NUMBER := &IP_NUM2;
RESULT NUMBER;
BEGIN
RESULT := VAL1 + VAL2;
DBMS_OUTPUT.PUT_LINE( VAL1 || ' + ' || VAL2 || ' = ' || RESULT);
-- [문자타입으로 변환 : TO_CHAR()]
-- DBMS_OUTPUT.PUT_LINE( TO_CHAR(VAL1) || ' + ' || TO_CHAR(VAL2) || ' = ' || TO_CHAR(RESULT));
END;
--*********************************************************
-- 출력문 DBMS_OUTPUT.PUT_LINE() 내부에서는
-- 정수 두 개의 값의 덧셈 연산과 같은 연산이 이루어지지 않고
-- 값을 출력하는 기능만 수행할 수 있다?
--*********************************************************
--==>>
/*
10 + 20 = 30
PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/
--[선생님 풀이!!]
ACCEPT N1 PROMPT '첫 번째 정수를 입력하세요';
ACCEPT N2 PROMPT '두 번째 정수를 입력하세요';
DECLARE
-- 주요 변수 선언
NUM1 NUMBER := &N1;
NUM2 NUMBER := &N2;
TOTAL NUMBER := 0;
BEGIN
-- 테스트
-- DBMS_OUTPUT.PUT_LINE('NUM1 : ' || NUM1);
-- DBMS_OUTPUT.PUT_LINE('NUM2 : ' || NUM2);
-- DBMS_OUTPUT.PUT_LINE('TOTAL : ' || TOTAL);
-- 연산 및 처리
TOTAL := NUM1 + NUM2;
-- 결과 출력
DBMS_OUTPUT.PUT_LINE(NUM1 || ' + ' || NUM2 || ' = ' || TOTAL);
DBMS_OUTPUT.PUT_LINE(NUM1 || ' + ' || NUM2 || ' = ' || (NUM1+NUM2));
END;
--==>>
/*
13 + 24 = 37
PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/
--○ 사용자로부터 입력받은 금액을 화폐단위로 구분하여 출력하는 프로그램을 작성한다.
-- 단, 입력에 대한 반환 금액은 편의상 1천원 미만, 10원 이상만 가능하다고 가정한다.
/*
실행 예)
바인딩 변수 입력 대화창 → 금액 입력 : 990
입력받은 금액 총액 : 990원
화폐단위 : 오백원 1, 백원 4, 오십원 1, 십원 4
*/
-- 68000원
-- 5만원 1장 = 68000 / 50000 (1장)
-- 1만원 1장 = [68000 - 50000*(68000/50000)] / 10000 (1장)
-- 5천원 1장 = [68000 - 50000*(68000/50000) - 10000*(68000 - 50000*(68000/50000) / 10000)] / 5000 (1장)
-- 1천원 3장 = [68000 - 50000*(68000/50000) - 10000*(68000 - 50000*(68000/50000) / 10000) - 5000 * (68000 - 50000*(68000/50000) - 10000*(68000 - 50000*(68000/50000) / 5000)
ACCEPT MONEY_INPUT PROMPT '금액 입력';
DECLARE
MONEY NUMBER := &MONEY_INPUT;
M500 NUMBER := TRUNC((MONEY / 500));
M100 NUMBER := TRUNC(MOD(MONEY, 500) / 100);
M50 NUMBER := TRUNC(MOD(MONEY, 100) / 50);
M10 NUMBER := TRUNC(MOD(MOD(MONEY, 100), 50) / 10);
-- 1천원 미만, 10원 이상
/*
바인딩 변수 입력 대화창 → 금액 입력 : 990
입력받은 금액 총액 : 990원
화폐단위 : 오백원 1, 백원 4, 오십원 1, 십원 4
*/
BEGIN
-- DBMS_OUTPUT.PUT_LINE('오백원 ' || MONEY / 500 || ', 백원 ' || (MONEY - 500*(MONEY/500)) / 100 ||
-- ', 오십원 ' || (MONEY - 500*(MONEY/500) - 100*(MONEY - 500*(MONEY/500) / 100)) / 50 ||
-- ', 십원 ' || (MONEY - 500*(MONEY/500) - 100*(MONEY - 500*(MONEY/500) / 100)
-- - 50*((MONEY - 500*(MONEY/500) - 100*(MONEY - 500*(MONEY/500) / 100) / 50)) / 10);
DBMS_OUTPUT.PUT_LINE('오백원 ' || TRUNC((MONEY / 500)) );
--ⓐ [MOD( A, B ) : 『 A / B 』의 나머지 값 반환하는 함수 사용 방식 => 혜성오빠 풀이]
DBMS_OUTPUT.PUT_LINE('백원 ' || TRUNC(MOD(MONEY, 500) / 100));
--ⓑ [전체 MONEY - 500원 - 100원 - ... 풀이 => 완전 이상한 내 풀이]
DBMS_OUTPUT.PUT_LINE('백원 ' || TRUNC((MONEY - 500 * TRUNC((MONEY / 500))) / 100));
--ⓐ [MOD( A, B ) : 『 A / B 』의 나머지 값 반환하는 함수 사용 방식 => 혜성오빠 풀이]
DBMS_OUTPUT.PUT_LINE('오십원 ' || TRUNC(MOD(MONEY, 100) / 50));
--ⓑ [전체 MONEY - 500원 - 100원 - ... 풀이 => 완전 이상한 내 풀이]
DBMS_OUTPUT.PUT_LINE('오십원 ' || TRUNC(TRUNC((MONEY - 500 * TRUNC((MONEY / 500)) - 100 * TRUNC((MONEY - 500 * TRUNC(MONEY / 500)) / 100)) / 50)));
--ⓐ [MOD( A, B ) : 『 A / B 』의 나머지 값 반환하는 함수 사용 방식 => 혜성오빠 풀이]
DBMS_OUTPUT.PUT_LINE('십원 ' || TRUNC(MOD(MOD(MONEY, 100), 50) / 10));
-- [ 680 입력 ]*************
-- ⓐ MOD(680, 100) = 80
-- 80 / 5 = 1 (오십원)
-- ⓑ. MOD(80, 50) = 30
-- 30 / 10 = 3 (십원)
-- *************************
--ⓑ [전체 MONEY - 500원 - 100원 - ... 풀이 => 완전 이상한 내 풀이]
DBMS_OUTPUT.PUT_LINE('십원 ' || TRUNC((MONEY - 500 * TRUNC((MONEY / 500))
- 100 * TRUNC((MONEY - 500 * TRUNC(MONEY / 500)) / 100)
- 50 * TRUNC(TRUNC((MONEY - 500 * TRUNC((MONEY / 500))
- 100 * TRUNC((MONEY - 500 * TRUNC(MONEY / 500)) / 100))
/ 50)))
/ 10));
DBMS_OUTPUT.PUT_LINE( '화폐 단위 : 오백원 ' || M500 || ', 백원 ' || M100 ||
', 오십원 ' || M50 || ', 십원 ' || M10 );
END;
--① 500원을 뺀다.
-- [전체 금액 - 500 * (금액 / 500)]
--② 100원을 뺀다.
-- [전체 금액 - 500 * (금액 / 500)
-- [ - 100 * (전체 금액 - 500 * (금액 / 500)) / 100) ]
--③ 50원을 뺀다.
-- [전체 금액 - 500 * (금액 / 500)
-- [ - 100 * (전체 금액 - 500 * (금액 / 500)) / 100) ]
-- [ - 50 *
-- [((전체 금액 - 500 * (금액 / 500)
-- [ - 100 * (전체 금액 - 500 * (금액 / 500)) / 100)) / 50 ]
--[선생님의 풀이... 진짜 깔끔하고 간단하다]=======================================
ACCEPT INPUT PROMPT '금액 입력';
DECLARE
--○ 주요 변수 선언
MONEY NUMBER := &INPUT; -- 연산을 위해 입력값을 담아둘 변수
MONEY2 NUMBER := &INPUT; -- 결과 출력을 위해 입력값을 담아둘 변수
-- (MONEY 변수가 연산을 처리하는 과정에서 값이 변하기 때문에...)
M500 NUMBER; -- 500원 짜리 개수를 담아둘 변수
M100 NUMBER; -- 100원 짜리 개수를 담아둘 변수
M50 NUMBER; -- 50원 짜리 개수를 담아둘 변수
M10 NUMBER; -- 10원 짜리 개수를 담아둘 변수
BEGIN
--○ 연산 및 처리
-- MONEY 를 500으로 나눠서 몫을 취하고 나머지는 버린다. → 500원의 개수
M500 := TRUNC(MONEY / 500);
-- MONEY 를 500으로 나눠서 몫은 버리고 나머지를 취한다. → 500원의 개수 확인하고 남은 금액
-- 이 금액으로 MONEY 를 갱신.
MONEY := MOD(MONEY, 500);
-- MONEY 를 100으로 나눠서 몫을 취하고 나머지는 버린다. → 100원의 개수
M100 := TRUNC(MONEY / 100);
-- MONEY 를 100으로 나눠서 몫은 버리고 나머지를 취한다. → 100원의 개수 확인하고 남은 금액
-- 이 금액으로 MONEY 를 갱신.
MONEY := MOD(MONEY, 100);
-- MONEY 를 50으로 나눠서 몫을 취하고 나머지는 버린다. → 50원의 개수
M50 := TRUNC(MONEY / 50);
-- MONEY 를 50으로 나눠서 몫은 버리고 나머지를 취한다. → 50원의 개수 확인하고 남은 금액
-- 이 금액으로 MONEY 를 갱신
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;
--==>>
/*
입력받은 금액 총액 : 780원
화폐 단위 : 오백원 1, 백원 2, 오십원 1, 십원 3
PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/
--==============================================================================
--○ 기본 반복문
-- LOOP ~ END LOOP;
-- 1. 조건과 상관없이 무조건 반복하는 구문.
-- 2. 형식 및 구조
-- 조건에 상관 없이 수행하다 보니까, EXIT WHEN이 반드시 필요하다!
-- 일반적인 반복문과 달리, 조건이 참이 되면 반복을 중단하는 형태로 반복문이 구성된다.
/*
LOOP
-- 실행문
EXIT WHEN 조건; -- 조건이 참인 경우 반복문을 빠져나간다.
END LOOP
*/
-- 1부터 10 까지의 수 출력(LOOP문 활용)
DECLARE
N NUMBER;
BEGIN
N := 1;
LOOP
DBMS_OUTPUT.PUT_LINE(N);
EXIT WHEN N >= 10;
N := N + 1; --[JAVA] : N++;, N+=1;
END LOOP;
END;
--==>>
/*
1
2
3
4
5
6
7
8
9
10
PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/
--○ WHILE 반복문
-- WHILE LOOP ~ END LOOP;
-- 1. 제어 조건이 TRUE 인 동안 일련의 문장을 반복하기 위해
-- WHILE LOOP 구문을 사용한다.
-- 조건은 반복이 시작되는 시점에 체크하게 되어
-- LOOP 내의 문장이 한 번도 수행되지 않을 경우도 있다.
-- LOOP 를 시작할 때 조건이 FALSE 이면, 반복 문장을 탈출하게 된다.
-- 2. 형식 및 구조
/*
WHILE 조건 LOOP -- 조건이 참인 경우 반복 수행
-- 실행문;
END LOOP;
*/
-- 1부터 10 까지의 수 출력(WHILE LOOP 문 활용)
DECLARE
N NUMBER := 1;
BEGIN
WHILE (N<=10) LOOP
DBMS_OUTPUT.PUT_LINE(N);
N := N + 1;
END LOOP;
END;
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 프로시저가 성공적으로 완료되었습니다.
*/
-- 모든 LOOP 문들은 끝날 때 END LOOP!!
-- 자바의 향상된 FOR 문 (FOR EACH 문)과 유사하다.
-- [JAVA 에서의 FOR EACH 문]
--FOR( INT N : 자료구조 )
--{
--
--}
--○ FOR 반복문
-- FOR LOOP ~ END LOOP;
-- 1. 『시작수』에서 1씩 증가하여
-- 『끝냄수』가 될 때까지 반복 수행한다.
-- 2. 형식 및 구조
/*
FOR 카운터 IN [REVERSE] 시작수 .. 끝냄수 LOOP
-- 실행문;
END LOOP;
*/
-- 연습
--DECLARE
-- N NUMBER := 1;
-- TCOUNT NUMBER := 0;
--BEGIN
-- FOR TCOUNT IN 0 .. 10 LOOP
-- DBMS_OUTPUT.PUT_LINE(TCOUNT);
-- END LOOP;
--END;
-- 1 부터 10까지의 수 출력(FOR LOOP 문 활용)
DECLARE
N NUMBER;
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 구문을 작성한다.
/*
실행 예)
바인딩 변수 입력 대화창 → 단을 입력하세요 : 2
2 * 1 = 2
2 * 2 = 4
:
2 * 9 = 18
*/
-- 1. LOOP 문의 경우
/*
LOOP
-- 실행문
EXIT WHEN 조건; -- 조건이 참인 경우 반복문을 빠져나간다.
END LOOP
*/
--【LOOP】
--★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
--① 『EXIT WHEN』 을 LOOP ~ END LOOP 사이에 반드시 작성한다.
--② 증감식 (X := X + 1) 을 사용한다.
--★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
ACCEPT INPUT PROMPT '단을 입력하세요';
DECLARE
DAN NUMBER;
X NUMBER := 1;
BEGIN
DAN := &INPUT;
LOOP
DBMS_OUTPUT.PUT_LINE(DAN || ' * ' || X || ' = ' || (DAN * X));
EXIT WHEN X >= 9;
X := X + 1;
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 프로시저가 성공적으로 완료되었습니다.
*/
-- 2. WHERE LOOP 문의 경우
/*
WHILE 조건 LOOP -- 조건이 참인 경우 반복 수행
-- 실행문;
END LOOP;
*/
--【WHILE LOOP】
--★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
--① WHILE (조건식) LOOP 형식으로 구성한다.
--② 증감식 (X := X + 1) 을 사용한다.
--★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
ACCEPT INPUT PROMPT '단을 입력하세요';
DECLARE
DAN NUMBER := &INPUT;
X NUMBER := 1;
BEGIN
WHILE (X <= 9) LOOP
DBMS_OUTPUT.PUT_LINE(DAN || ' * ' || X || ' = ' || (DAN * X));
X := X + 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 프로시저가 성공적으로 완료되었습니다.
*/
-- 3. FOR LOOP 문의 경우
--【FOR LOOP】
--★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
--① 카운터 대상인 X는 초기값 설정 해주지 않아도 된다.
--② 증감식 (X := X + 1) 을 사용하지 않는다. (사용시 에러 발생!)
--★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
ACCEPT INPUT PROMPT '단을 입력하세요';
DECLARE
DAN NUMBER := &INPUT;
X NUMBER;
BEGIN
FOR X IN 1 .. 9 LOOP
DBMS_OUTPUT.PUT_LINE(DAN || ' * ' || X || ' = ' || (DAN * X));
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
-- EXIT WHEN N >= 10;
DBMS_OUTPUT.PUT_LINE(DAN || ' * ' || N || ' = ' || (DAN*N));
EXIT WHEN N >= 9;
N := N + 1;
-- EXIT WHEN N >= 10;
END LOOP;
END;
--==>>
/*
7 * 1 = 7
7 * 2 = 14
7 * 3 = 21
7 * 4 = 28
7 * 5 = 35
7 * 6 = 42
7 * 7 = 49
7 * 8 = 56
7 * 9 = 63
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;
--==>>
/*
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 프로시저가 성공적으로 완료되었습니다.
*/
-- 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;
--==>>
/*
8 * 1 = 8
8 * 2 = 16
8 * 3 = 24
8 * 4 = 32
8 * 5 = 40
8 * 6 = 48
8 * 7 = 56
8 * 8 = 64
8 * 9 = 72
PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/
--○ 구구단 전체(2단~9단)를 출력하는 PL/SQL 구문을 작성한다.
-- 단, 이중 반복문(반복문의 중첩) 구문을 활용한다.
/*
실행 예)
==[2단]==
2 * 1 = 2
2 * 2 = 4
:
==[3단]==
:
9 * 9 = 81
*/
-- 1. 2단일 때 출력
-- [2] 1 2 3 4 5 6 7 8 9
-- 2. 1 증가 (DAN)
-- 3. 3단일 때 출력
-- [3] 1 2 3 4 5 6 7 8 9
-- 1. LOOP 를 이용한 방식.
DECLARE
DAN NUMBER := 2;
X NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('==[' || DAN || '단 ]==');
LOOP
DBMS_OUTPUT.PUT_LINE(DAN || ' * ' || X || ' = ' || (DAN*X));
EXIT WHEN X >= 9;
X := X + 1;
END LOOP;
DAN := DAN + 1;
X := 1;
EXIT WHEN DAN >= 10;
END LOOP;
END;
-- 2. WHILE LOOP 를 이용한 방식
DECLARE
DAN NUMBER := 2;
X NUMBER := 1;
BEGIN
WHILE DAN <= 9 LOOP
DBMS_OUTPUT.PUT_LINE('==[' || DAN || '단 ]==');
WHILE X <= 9 LOOP
DBMS_OUTPUT.PUT_LINE(DAN || ' * ' || X || ' = ' || (DAN*X));
X := X + 1;
END LOOP;
DAN := DAN + 1;
X := 1;
END LOOP;
END;
-- 3. FOR LOOP 를 이용한 풀이
DECLARE
DAN NUMBER;
X NUMBER;
BEGIN
FOR DAN IN 2 .. 9 LOOP
DBMS_OUTPUT.PUT_LINE('==[' || DAN || '단 ]==');
FOR X IN 1 .. 9 LOOP
DBMS_OUTPUT.PUT_LINE(DAN || ' * ' || X || ' = ' || (DAN*X));
END LOOP;
END LOOP;
END;
DECLARE
N NUMBER;
M NUMBER;
BEGIN
FOR N IN 2 .. 9 LOOP
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('==[' || N || '단]==');
FOR M IN 1 .. 9 LOOP
DBMS_OUTPUT.PUT_LINE(N || ' * ' || M || ' = ' || (N*M));
END LOOP;
END LOOP;
END;
--==>>
/*
==[2단]==
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
==[3단]==
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
==[4단]==
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
==[5단]==
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
==[6단]==
6 * 1 = 6
6 * 2 = 12
6 * 3 = 18
6 * 4 = 24
6 * 5 = 30
6 * 6 = 36
6 * 7 = 42
6 * 8 = 48
6 * 9 = 54
==[7단]==
7 * 1 = 7
7 * 2 = 14
7 * 3 = 21
7 * 4 = 28
7 * 5 = 35
7 * 6 = 42
7 * 7 = 49
7 * 8 = 56
7 * 9 = 63
==[8단]==
8 * 1 = 8
8 * 2 = 16
8 * 3 = 24
8 * 4 = 32
8 * 5 = 40
8 * 6 = 48
8 * 7 = 56
8 * 8 = 64
8 * 9 = 72
==[9단]==
9 * 1 = 9
9 * 2 = 18
9 * 3 = 27
9 * 4 = 36
9 * 5 = 45
9 * 6 = 54
9 * 7 = 63
9 * 8 = 72
9 * 9 = 81
PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/
20231102_03_hr(plsql).sql
SELECT USER
FROM DUAL;
--==>> HR
-- 데이터 타입을 통일 시켜주어야 한다.
-- EMPLOYEES 컬럼의 값을 갖고와서, PL/SQL에서 연산하려고 할 때
-- 데이터 타입이 동일해야 한다.
-- [변수명 테이블.컬럼명%TYPE;]
-- 테이블이 갖고 있는 컬럼명의 데이터 타입과
-- 지금 선언하려는 변수의 타입이 동일하도록 처리해 달라는 구문이다.
--○ %TYPE
-- 1. 특정 테이블에 포함되어 있는 컬럼의 데이터타입(자료형)을 참조하는 데이터타입
-- 2. 형식 및 구조
-- 변수명 테이블.컬럼명%TYPE [:= 초기값];
-- HR.EMPLOYEES 테이블의 특정 데이터를 변수에 저장.
-- 새롭게 SESSION 설정해줘야 아웃풋 가능!!!!
-- SET SERVEROUTPUT ON SET SERVEROUTPUT ON SET SERVEROUTPUT ON
SET SERVEROUTPUT ON;
DECLARE
--V_NAME VARCHAR2(20);
-- DESC 로 테이블 묘사하여 데이터타입을 확인하고 직접 설정해 주는 것이 아니라
-- 오라클이 직접 그 테이블의 데이터 타입을 확인하여 지정해 달라고 명령한다!
V_NAME EMPLOYEES.FIRST_NAME%TYPE;
BEGIN
-- # (쿼리문에서 얻어낸 값을, V_NAME에 대입하도록 명령)
-- # SELECT FIRST_NAME, LAST_NAME INTO V_NAME, V_LASTNAME (두 개의 값 받아오기)
SELECT FIRST_NAME INTO V_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 103;
DBMS_OUTPUT.PUT_LINE(V_NAME);
END;
--==>>
/*
Alexander
PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/
-- 테이블의 컬럼값을 가져와서 변수에 대입해주고 싶을 때, 컬럼의 데이터 타입 조회 없이
-- 바로 변수 지정이 가능하도록...
--○ EMPLOYEES 테이블을 대상으로 108번 사원(Nancy)의
-- SALARY 를 변수에 담아 출력하는 PL/SQL 구문을 작성한다.
DECLARE
NANCYSAL EMPLOYEES.SALARY%TYPE;
BEGIN
SELECT SALARY INTO NANCYSAL
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 108;
DBMS_OUTPUT.PUT_LINE(NANCYSAL);
END;
--==>>
/*
12008
PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/
-- DESC EMPLOYEES;
DECLARE
--V_SALARY NUMBER(8,2);
V_SALARY EMPLOYEES.SALARY%TYPE;
BEGIN
SELECT SALARY INTO V_SALARY
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 108;
DBMS_OUTPUT.PUT_LINE(V_SALARY);
END;
--==>>
/*
12008
PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/
--○ EMPLOYEES 테이블의 특정 레코드 항목 여러 개를 변수에 저장
-- 103번 사원의 FIRST_NAME, PHONE_NUMBER, EMAIL 항목을 변수에 저장하여 출력
-- Alexander 590.423.4567 AHUNOLD
-- Alexander, 590.423.4567, AHUNOLD
DECLARE
ALEXNAME EMPLOYEES.FIRST_NAME%TYPE;
ALEXTEL EMPLOYEES.PHONE_NUMBER%TYPE;
ALEXMAIL EMPLOYEES.EMAIL%TYPE;
BEGIN
SELECT FIRST_NAME, PHONE_NUMBER, EMAIL
INTO ALEXNAME, ALEXTEL, ALEXMAIL
FROM EMPLOYEES
WHERE EMPLOYEE_ID = '103';
DBMS_OUTPUT.PUT_LINE(ALEXNAME || ', ' || ALEXTEL || ', ' || ALEXMAIL);
END;
--==>> Alexander, 590.423.4567, AHUNOLD
--○ %ROWTYPE
-- 1. 테이블의 레코드와 같은 구조의 구조체 변수를 선언(여러 개의 컬럼)
-- 2. 형식 및 구조
-- 변수명 테이블명%ROWTYPE;
-- 참조해야 하는 변수가 너무 많으면 참조하는 테이블의 행 데이터 타입을 모두 가져온 후
-- 각각의 타입 적용 가능.
DECLARE
-- V_FIRST_NAME EMPLOYEES.FIRST_NAME%TYPE;
-- V_PHONE_NUMBER; EMPLOYEES.PHONE_NUMBER%TYPE;
-- V_EMAIL; EMPLOYEES.EMAIL%TYPE;
V_EMP EMPLOYEES%ROWTYPE;
BEGIN
SELECT FIRST_NAME, PHONE_NUMBER, EMAIL
INTO V_EMP.FIRST_NAME, V_EMP.PHONE_NUMBER, V_EMP.EMAIL
FROM EMPLOYEES
WHERE EMPLOYEE_ID = '103';
--DBMS_OUTPUT.PUT_LINE(ALEXNAME || ', ' || ALEXTEL || ', ' || ALEXMAIL);
DBMS_OUTPUT.PUT_LINE(V_EMP.FIRST_NAME || ', ' || V_EMP.PHONE_NUMBER || ', ' || V_EMP.EMAIL);
END;
--○ EMPLOYEES 테이블의 전체 레코드 항목 여러 개를 변수에 저장
-- 모든 사원의 FIRST_NAME, PHONE_NUMBER, EMAIL 항목을 변수에 저장하여 출력
DECLARE
V_EMP EMPLOYEES%ROWTYPE; --행 타입 전체 참조
BEGIN
SELECT FIRST_NAME, PHONE_NUMBER, EMAIL
INTO V_EMP.FIRST_NAME, V_EMP.PHONE_NUMBER, V_EMP.EMAIL
FROM EMPLOYEES;
DBMS_OUTPUT.PUT_LINE(V_EMP.FIRST_NAME || ', ' || V_EMP.PHONE_NUMBER || ', ' || V_EMP.EMAIL);
END;
--==>> 에러 발생
-- (ORA-01422: exact fetch returns more than requested number of rows)
-- 반환하는 레코드의 수가 단일 행이 아니라 복수개의 행이므로 오류가 발생.
-- 변수가 하나인데 다수의 값을 저장할 수가 없다.
--> 여러 개의 행(ROWS) 정보를 얻어와 담으려고 하면
-- 단일 변수에 저장하는 것 자체가 불가능하다.
20231102_04_hr.sql
SELECT USER
FROM DUAL;
--==>>HR
SELECT FIRST_NAME, PHONE_NUMBER, EMAIL
FROM EMPLOYEES;
--==>>
/*
FIRST_NAME PHONE_NUMBER EMAIL
Steven 515.123.4567 SKING
Neena 515.123.4568 NKOCHHAR
Lex 515.123.4569 LDEHAAN
Alexander 590.423.4567 AHUNOLD
Bruce 590.423.4568 BERNST
David 590.423.4569 DAUSTIN
Valli 590.423.4560 VPATABAL
Diana 590.423.5567 DLORENTZ
Nancy 515.124.4569 NGREENBE
Daniel 515.124.4169 DFAVIET
John 515.124.4269 JCHEN
Ismael 515.124.4369 ISCIARRA
Jose Manuel 515.124.4469 JMURMAN
Luis 515.124.4567 LPOPP
Den 515.127.4561 DRAPHEAL
Alexander 515.127.4562 AKHOO
Shelli 515.127.4563 SBAIDA
Sigal 515.127.4564 STOBIAS
Guy 515.127.4565 GHIMURO
Karen 515.127.4566 KCOLMENA
Matthew 650.123.1234 MWEISS
Adam 650.123.2234 AFRIPP
Payam 650.123.3234 PKAUFLIN
Shanta 650.123.4234 SVOLLMAN
Kevin 650.123.5234 KMOURGOS
Julia 650.124.1214 JNAYER
Irene 650.124.1224 IMIKKILI
James 650.124.1334 JLANDRY
Steven 650.124.1434 SMARKLE
Laura 650.124.5234 LBISSOT
Mozhe 650.124.6234 MATKINSO
James 650.124.7234 JAMRLOW
TJ 650.124.8234 TJOLSON
Jason 650.127.1934 JMALLIN
Michael 650.127.1834 MROGERS
Ki 650.127.1734 KGEE
Hazel 650.127.1634 HPHILTAN
Renske 650.121.1234 RLADWIG
Stephen 650.121.2034 SSTILES
John 650.121.2019 JSEO
Joshua 650.121.1834 JPATEL
Trenna 650.121.8009 TRAJS
Curtis 650.121.2994 CDAVIES
Randall 650.121.2874 RMATOS
Peter 650.121.2004 PVARGAS
John 011.44.1344.429268 JRUSSEL
Karen 011.44.1344.467268 KPARTNER
Alberto 011.44.1344.429278 AERRAZUR
Gerald 011.44.1344.619268 GCAMBRAU
Eleni 011.44.1344.429018 EZLOTKEY
Peter 011.44.1344.129268 PTUCKER
David 011.44.1344.345268 DBERNSTE
Peter 011.44.1344.478968 PHALL
Christopher 011.44.1344.498718 COLSEN
Nanette 011.44.1344.987668 NCAMBRAU
Oliver 011.44.1344.486508 OTUVAULT
Janette 011.44.1345.429268 JKING
Patrick 011.44.1345.929268 PSULLY
Allan 011.44.1345.829268 AMCEWEN
Lindsey 011.44.1345.729268 LSMITH
Louise 011.44.1345.629268 LDORAN
Sarath 011.44.1345.529268 SSEWALL
Clara 011.44.1346.129268 CVISHNEY
Danielle 011.44.1346.229268 DGREENE
Mattea 011.44.1346.329268 MMARVINS
David 011.44.1346.529268 DLEE
Sundar 011.44.1346.629268 SANDE
Amit 011.44.1346.729268 ABANDA
Lisa 011.44.1343.929268 LOZER
Harrison 011.44.1343.829268 HBLOOM
Tayler 011.44.1343.729268 TFOX
William 011.44.1343.629268 WSMITH
Elizabeth 011.44.1343.529268 EBATES
Sundita 011.44.1343.329268 SKUMAR
Ellen 011.44.1644.429267 EABEL
Alyssa 011.44.1644.429266 AHUTTON
Jonathon 011.44.1644.429265 JTAYLOR
Jack 011.44.1644.429264 JLIVINGS
Kimberely 011.44.1644.429263 KGRANT
Charles 011.44.1644.429262 CJOHNSON
Winston 650.507.9876 WTAYLOR
Jean 650.507.9877 JFLEAUR
Martha 650.507.9878 MSULLIVA
Girard 650.507.9879 GGEONI
Nandita 650.509.1876 NSARCHAN
Alexis 650.509.2876 ABULL
Julia 650.509.3876 JDELLING
Anthony 650.509.4876 ACABRIO
Kelly 650.505.1876 KCHUNG
Jennifer 650.505.2876 JDILLY
Timothy 650.505.3876 TGATES
Randall 650.505.4876 RPERKINS
Sarah 650.501.1876 SBELL
Britney 650.501.2876 BEVERETT
Samuel 650.501.3876 SMCCAIN
Vance 650.501.4876 VJONES
Alana 650.507.9811 AWALSH
Kevin 650.507.9822 KFEENEY
Donald 650.507.9833 DOCONNEL
Douglas 650.507.9844 DGRANT
Jennifer 515.123.4444 JWHALEN
Michael 515.123.5555 MHARTSTE
Pat 603.123.6666 PFAY
Susan 515.123.7777 SMAVRIS
Hermann 515.123.8888 HBAER
Shelley 515.123.8080 SHIGGINS
William 515.123.8181 WGIETZ
*/
-- 다중 행으로 존재하는 데이터를 하나의 변수 안에 저장할 수 없으므로
-- 조금 전과 같은 에러가 발생한것.
20231102_05_scott(plsql).sql
SELECT USER
FROM DUAL;
--==>> SCOTT
SET SERVEROUTPUT ON;
--○ TBL_INSA 테이블의 여러 명의 데이터 여러 개를 변수에 저장하여 출력
-- (반복문 활용)
DECLARE
V_INSA TBL_INSA%ROWTYPE;
V_NUM TBL_INSA.NUM%TYPE := 1001;
BEGIN
-- 반복문 구성
LOOP
-- 조회 → 단일 레코드 얻어오기 → 변수에 담아내기
SELECT NAME, TEL, BUSEO
INTO V_INSA.NAME, V_INSA.TEL, V_INSA.BUSEO
FROM TBL_INSA
WHERE NUM = V_NUM;
-- 출력
DBMS_OUTPUT.PUT_LINE(V_INSA.NAME || ' - ' || V_INSA.TEL || ' - ' || V_INSA.BUSEO);
-- 사원번호 1씩 증가시키는 구문
V_NUM := V_NUM + 1;
-- 반복문을 탈출하게 되는 조건
EXIT WHEN V_NUM > 1060;
END LOOP;
END;
--==>>
/*
홍길동 - 011-2356-4528 - 기획부
이순신 - 010-4758-6532 - 총무부
이순애 - 010-4231-1236 - 개발부
김정훈 - 019-5236-4221 - 영업부
한석봉 - 018-5211-3542 - 총무부
이기자 - 010-3214-5357 - 개발부
장인철 - 011-2345-2525 - 개발부
김영년 - 016-2222-4444 - 홍보부
나윤균 - 019-1111-2222 - 인사부
김종서 - 011-3214-5555 - 영업부
유관순 - 010-8888-4422 - 영업부
정한국 - 018-2222-4242 - 홍보부
조미숙 - 019-6666-4444 - 홍보부
황진이 - 010-3214-5467 - 개발부
이현숙 - 016-2548-3365 - 총무부
이상헌 - 010-4526-1234 - 개발부
엄용수 - 010-3254-2542 - 개발부
이성길 - 018-1333-3333 - 개발부
박문수 - 017-4747-4848 - 인사부
유영희 - 011-9595-8585 - 자재부
홍길남 - 011-9999-7575 - 개발부
이영숙 - 017-5214-5282 - 기획부
김인수 - - 영업부
김말자 - 011-5248-7789 - 기획부
우재옥 - 010-4563-2587 - 영업부
김숙남 - 010-2112-5225 - 영업부
김영길 - 019-8523-1478 - 총무부
이남신 - 016-1818-4848 - 인사부
김말숙 - 016-3535-3636 - 총무부
정정해 - 019-6564-6752 - 총무부
지재환 - 019-5552-7511 - 기획부
심심해 - 016-8888-7474 - 자재부
김미나 - 011-2444-4444 - 영업부
이정석 - 011-3697-7412 - 기획부
정영희 - - 개발부
이재영 - 011-9999-9999 - 자재부
최석규 - 011-7777-7777 - 홍보부
손인수 - 010-6542-7412 - 영업부
고순정 - 010-2587-7895 - 영업부
박세열 - 016-4444-7777 - 인사부
문길수 - 016-4444-5555 - 자재부
채정희 - 011-5125-5511 - 개발부
양미옥 - 016-8548-6547 - 영업부
지수환 - 011-5555-7548 - 영업부
홍원신 - 011-7777-7777 - 영업부
허경운 - 017-3333-3333 - 총무부
산마루 - 018-0505-0505 - 영업부
이기상 - - 개발부
이미성 - 010-6654-8854 - 개발부
이미인 - 011-8585-5252 - 홍보부
권영미 - 011-5555-7548 - 영업부
권옥경 - 010-3644-5577 - 기획부
김싱식 - 011-7585-7474 - 자재부
정상호 - 016-1919-4242 - 홍보부
정한나 - 016-2424-4242 - 영업부
전용재 - 010-7549-8654 - 영업부
이미경 - 016-6542-7546 - 자재부
김신제 - 010-2415-5444 - 기획부
임수봉 - 011-4151-4154 - 개발부
김신애 - 011-4151-4444 - 개발부
PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/
-- 문제점 : 1. 시작 번호와 끝 번호를 알아야 한다.
-- : SERIAL 번호 형식으로 부여되어 있어야 함. (번호가 순서번호여야 함.)
DECLARE
V_INSA TBL_INSA%ROWTYPE;
N TBL_INSA.NUM%TYPE;
BEGIN
N := 1000;
WHILE (N < 1060) LOOP
N := N + 1;
--DBMS_OUTPUT.PUT_LINE(N);
SELECT NAME, TEL, BUSEO
INTO V_INSA.NAME, V_INSA.TEL, V_INSA.BUSEO
FROM TBL_INSA
WHERE NUM = N;
DBMS_OUTPUT.PUT_LINE(V_INSA.NAME || ' - ' || V_INSA.TEL || ' - ' || V_INSA.BUSEO);
END LOOP;
END;
--------------------------------------------------------------------------------
--■■■ FUNCTION(함수) ■■■--
20231102_06_SCOTT.SQL
SELECT USER
FROM DUAL;
--==>> SCOTT
SELECT NUM, NAME, TEL, BUSEO
FROM TBL_INSA
WHERE NUM=1001;
'[Oracle] > PL·SQL (Program source)' 카테고리의 다른 글
| [Oracle - PL/SQL] 20231108 [프로그램 소스] (0) | 2023.11.09 |
|---|---|
| [Oracle - PL/SQL] 20231107 [프로그램 소스] (2) | 2023.11.07 |
| [Oracle - PL/SQL] 20231106 [프로그램 소스] (0) | 2023.11.06 |
| [Oracle - PL/SQL] 20231103 [프로그램 소스] (0) | 2023.11.03 |