013. [PL/SQL] %TYPE / %ROWTYPE (자료형 참조 데이터타입), FUNCTION(함수), PROCEDURE

PROCEDURE(프로시저)2023.04.07 (금)

HR 계정 실습 (어제 이어서...)

SELECT USER
FROM DUAL;
--==>> HR

SET SERVEROUTPUT ON;
--==>> 작업이 완료되었습니다.

• %TYPE

--○ 『%TYPE』

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

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

• 관찰

SELECT FIRST_NAME
FROM EMPLOYEES;

DESC EMPLOYEES;
--==>>
/*
            :
FIRST_NAME              VARCHAR2(20)
            :
*/

SELECT EMPLOYEE_ID,FIRST_NAME
FROM EMPLOYEES;

--○ HR.EMPLOYEES 테이블의 특정 데이터를 변수에 저장하여 출력
DECLARE
    --// EMPLOYEES FIRST_NAME 컬럼에 있는 데이터를 담을 예정 → 데이터의 길이 : FIRST_NAME의 컬럼길이
    --VNAME       VARCHAR2(20); --// 직접 찾아 적는 방법
    VNAME       EMPLOYEES.FIRST_NAME%TYPE;  -- VARCHAR2(20) --// 테이블명.컬럼명%TYPE(데이터 타입)을 담겠다!
BEGIN
                                       --// 변수명 := 값; <- 단일 값을 대입할 때
    SELECT FIRST_NAME INTO VNAME       --// <--단일 값이 아닐 때 'INTO 변수명' 으로 담는다
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = 103;    
    
    DBMS_OUTPUT.PUT_LINE(VNAME);
END;
--==>>
/*
Alexander


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

---

• %ROWTYPE

--○ 『%ROWTYPE』

-- 1. 테이블의 레코드와 같은 구조의 구조체 변수를 선언(여러 개의 컬럼)

-- 2. 형식 및 구조
--    변수명 테이블명%ROWTYPE

• 관찰

DESC EMPLOYEES;
--==>>
/*
이름             널?       유형           
-------------- -------- ------------ 
EMPLOYEE_ID    NOT NULL NUMBER(6)    
FIRST_NAME              VARCHAR2(20) 
LAST_NAME      NOT NULL VARCHAR2(25) 
EMAIL          NOT NULL VARCHAR2(25) 
PHONE_NUMBER            VARCHAR2(20) 
HIRE_DATE      NOT NULL DATE         
JOB_ID         NOT NULL VARCHAR2(10) 
SALARY                  NUMBER(8,2)  
COMMISSION_PCT          NUMBER(2,2)  
MANAGER_ID              NUMBER(6)    
DEPARTMENT_ID           NUMBER(4)
*/

--○ HR.EMPLOYEES 테이블의 데이터 여러개를 변수에 저장

DECLARE
--    VNAME   VARCHAR2(20);
--    VPHONE  VARCHAR2(20);
--    VEMAIL  VARCHAR2(25);
    
--    VNAME   EMPLOYEES.FIRST_NAME%TYPE;
--    VPHONE  EMPLOYEES.PHONE_NUMBER%TYPE;
--    VEMAIL  EMPLOYEES.EMAIL%TYPE;

    VEMP    EMPLOYEES%ROWTYPE;
BEGIN
    SELECT FIRST_NAME, PHONE_NUMBER, EMAIL
      INTO VEMP.FIRST_NAME, VEMP.PHONE_NUMBER, VEMP.EMAIL
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = 103;
    
    DBMS_OUTPUT.PUT_LINE(VEMP.FIRST_NAME || ' - '
                        || VEMP.PHONE_NUMBER || ' - '
                        || VEMP.EMAIL);
END;

---

• 주의사항

--//※ 주의사항
--○ HR.EMPLOYEES 테이블의 여러명 데이터 여러개를 변수에 저장

DECLARE
    VEMP    EMPLOYEES%ROWTYPE;
    
BEGIN
    SELECT FIRST_NAME, PHONE_NUMBER, EMAIL
      INTO VEMP.FIRST_NAME, VEMP.PHONE_NUMBER, VEMP.EMAIL
    FROM EMPLOYEES;
    
    DBMS_OUTPUT.PUT_LINE(VEMP.FIRST_NAME || ' - '
                        || VEMP.PHONE_NUMBER || ' - '
                        || VEMP.EMAIL);
END;
--==>> 에러 발생
/*
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 5
01422. 00000 -  "exact fetch returns more than requested number of rows"
*Cause:    The number specified in exact fetch is less than the rows returned.
*Action:   Rewrite the query or change number of rows requested
*/
--> 여러 개의 행(ROWS) 정보를 얻어와 담으려고 하면
--  변수에 저장하는 것 자체가 불가능한 상황

-- 불가~!!!

SCOTT 계정 실습

SELECT USER
FROM DUAL;
--==>> SCOTT

SET SERVEROUTPUT ON;
--==>> 작업이 완료되었습니다.

SELECT *
FROM TBL_INSA;

- 반복문의 활용과 여러개의 변수 참조해서 넣기

--○ SCOTT.TBL_INSA 테이블의 여러 명의 데이터 여러 개를 변수에 저장
--   (반복문 활용)
DECLARE
    VINSA   TBL_INSA%ROWTYPE;
    VNUM    TBL_INSA.NUM%TYPE :=1001;
BEGIN
    LOOP
        SELECT NAME, TEL, BUSEO
          INTO VINSA.NAME, VINSA.TEL, VINSA.BUSEO
        FROM TBL_INSA
        WHERE NUM = VNUM;
        
        DBMS_OUTPUT.PUT_LINE(VINSA.NAME || ' - ' || VINSA.TEL || ' - ' || VINSA.BUSEO);
        
        EXIT WHEN VNUM >=1060;
        
        VNUM := VNUM + 1;   -- VNUM 을 1만큼 증가 → VNUM++;  VNUM+=1;
        
    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. 어디서 시작하는지, 어디까지 반복해야하는지 직접 알아내야함
--//        2. 일련번호가 중간에 빠져 있으면 문제가 생길 수 있다
--// -> 추후 커서 를 도입해서 해결 하게 된다 .

---

• FUNCTION(함수)

--■■■ FUNCTION(함수) ■■■--

-- 1. 함수란 하나 이상의 PL/SQL 문으로 구성된 서브루틴으로
--    코드를 다시 사용할 수 있도록 캡슐화 하는데 사용된다.    --// 1차원적인 캡슐화
--    오라클에서는 오라클에 정의된 기본제공 함수를 사용하거나
--    직접 스토어드 함수를 만들 수 있다 (→ 사용자 정의 함수)

-- 2. 형식 및 구조
/*
CREAT [OR REPLACE] FUNCTION 함수명
[(
    매개변수1 자료형
  , 매개변수2 자료형
  )]
RETUNRN 데이터타입                          --// 반환자료형 이라고 생각하면 쉽다
IS
    -- 주요 변수 선언(지역 변수)
BEGIN
    -- 실행문;
    ...
    RETURN 값;
    
    [EXCEPTION]
        -- 예외 처리 구문;
END;

*/
-- ※ 사용자 정의 함수(스토어드 함수)는
--    IN 파라미터(입력 매개변수)만 사용할 수 있으며
--    반드시 반환될 값의 데이터타입을 RETURN 문에 선언해야 하고
--    FUNCTION 은 반드시 단일 값만 반환한다.
--// 출력매개변수 // 아웃파라미터   // 김치 받아오기 위한 빈 통
--// 입력매개변수 // 인파라미터  // 갖다드리는 홍삼~
--// 입출력매개변수 // 인아웃파라미터 // 김치받아오기 위한 통에 김치 담글때 필요한 고춧가루 담아가서 전달

 

• 관찰

SCOTT 계정 실습(새 시트 생성)

SELECT USER
FROM DUAL;
--==>> SCOTT

-- TBL_INSA 테이블을 대상으로
-- 주민번호를 활용하여 성별을 조회한다.
SELECT NAME, SSN, DECODE(SUBSTR(SSN,8,1),'1','남자','2','여자','확인불가') "성별"
FROM TBL_INSA;
--==>>
/*
홍길동	771212-1022432	남자
이순신	801007-1544236	남자
이순애	770922-2312547	여자
김정훈	790304-1788896	남자
한석봉	811112-1566789	남자
이기자	780505-2978541	여자
장인철	780506-1625148	남자
김영년	821011-2362514	여자
나윤균	810810-1552147	남자
김종서	751010-1122233	남자
유관순	801010-2987897	여자
정한국	760909-1333333	남자
조미숙	790102-2777777	여자
황진이	810707-2574812	여자
이현숙	800606-2954687	여자
이상헌	781010-1666678	남자
엄용수	820507-1452365	남자
이성길	801028-1849534	남자
박문수	780710-1985632	남자
유영희	800304-2741258	여자
홍길남	801010-1111111	남자
이영숙	800501-2312456	여자
김인수	731211-1214576	남자
김말자	830225-2633334	여자
우재옥	801103-1654442	남자
김숙남	810907-2015457	여자
김영길	801216-1898752	남자
이남신	810101-1010101	남자
김말숙	800301-2020202	여자
정정해	790210-2101010	여자
지재환	771115-1687988	남자
심심해	810206-2222222	여자
김미나	780505-2999999	여자
이정석	820505-1325468	남자
정영희	831010-2153252	여자
이재영	701126-2852147	여자
최석규	770129-1456987	남자
손인수	791009-2321456	여자
고순정	800504-2000032	여자
박세열	790509-1635214	남자
문길수	721217-1951357	남자
채정희	810709-2000054	여자
양미옥	830504-2471523	여자
지수환	820305-1475286	남자
홍원신	690906-1985214	남자
허경운	760105-1458752	남자
산마루	780505-1234567	남자
이기상	790604-1415141	남자
이미성	830908-2456548	여자
이미인	810403-2828287	여자
권영미	790303-2155554	여자
권옥경	820406-2000456	여자
김싱식	800715-1313131	남자
정상호	810705-1212141	남자
정한나	820506-2425153	여자
전용재	800605-1456987	남자
이미경	780406-2003214	여자
김신제	800709-1321456	남자
임수봉	810809-2121244	여자
김신애	810809-2111111	여자
*/

--○ TBL_INSA 테이블을 대상으로
--   주민번호를 가지고 성별을 조회하는 함수를 정의한다.
--   함수명 : FN_GENDER(  )
--                      ↑
--                  주민등록번호 → SSN → 'YYMMDD-NNNNNNN'


CREATE OR REPLACE FUNCTION FN_GENDER    -- 함수 생성
( 
    VSSN VARCHAR2                       -- 매개변수 : 자릿수(길이) 지정 안함
)   
RETURN VARCHAR2                         -- 반환 자료형 : 자릿수(길이) 지정 안함
IS
    -- 주요 변수 선언
    VRESULT VARCHAR2(20);
BEGIN
    -- 연산 및 처리(기능)
    IF ( SUBSTR(VSSN,8,1) IN ('1','3'))
        THEN VRESULT := '남자';
    ELSIF ( SUBSTR(VSSN,8,1) IN ('2','4'))
        THEN VRESULT := '여자';
    ELSE
        VRESULT := '성별확인불가';
    END IF;
    
    -- 최종 결과값 반환
    RETURN VRESULT;
END;
--==>> Function FN_GENDER이(가) 컴파일되었습니다.

- 상기 작성한 함수가 제대로 작성하는지 새 워크시트에서 확인해보기

SELECT *
FROM TBL_INSA;
--==>>
/*
1001	홍길동	771212-1022432	1998-10-11	서울	011-2356-4528	기획부	부장	2610000	200000
1002	이순신	801007-1544236	2000-11-29	경기	010-4758-6532	총무부	사원	1320000	200000
1003	이순애	770922-2312547	1999-02-25	인천	010-4231-1236	개발부	부장	2550000	160000
1004	김정훈	790304-1788896	2000-10-01	전북	019-5236-4221	영업부	대리	1954200	170000
1005	한석봉	811112-1566789	2004-08-13	서울	018-5211-3542	총무부	사원	1420000	160000
1006	이기자	780505-2978541	2002-02-11	인천	010-3214-5357	개발부	과장	2265000	150000
1007	장인철	780506-1625148	1998-03-16	제주	011-2345-2525	개발부	대리	1250000	150000
1008	김영년	821011-2362514	2002-04-30	서울	016-2222-4444	홍보부	사원	950000	145000
1009	나윤균	810810-1552147	2003-10-10	경기	019-1111-2222	인사부	사원	840000	220400
1010	김종서	751010-1122233	1997-08-08	부산	011-3214-5555	영업부	부장	2540000	130000
1011	유관순	801010-2987897	2000-07-07	서울	010-8888-4422	영업부	사원	1020000	140000
1012	정한국	760909-1333333	1999-10-16	강원	018-2222-4242	홍보부	사원	880000	114000
1013	조미숙	790102-2777777	1998-06-07	경기	019-6666-4444	홍보부	대리	1601000	103000
1014	황진이	810707-2574812	2002-02-15	인천	010-3214-5467	개발부	사원	1100000	130000
1015	이현숙	800606-2954687	1999-07-26	경기	016-2548-3365	총무부	사원	1050000	104000
1016	이상헌	781010-1666678	2001-11-29	경기	010-4526-1234	개발부	과장	2350000	150000
1017	엄용수	820507-1452365	2000-08-28	인천	010-3254-2542	개발부	사원	950000	210000
1018	이성길	801028-1849534	2004-08-08	전북	018-1333-3333	개발부	사원	880000	123000
1019	박문수	780710-1985632	1999-12-10	서울	017-4747-4848	인사부	과장	2300000	165000
1020	유영희	800304-2741258	2003-10-10	전남	011-9595-8585	자재부	사원	880000	140000
1021	홍길남	801010-1111111	2001-09-07	경기	011-9999-7575	개발부	사원	875000	120000
1022	이영숙	800501-2312456	2003-02-25	전남	017-5214-5282	기획부	대리	1960000	180000
1023	김인수	731211-1214576	1995-02-23	서울		영업부	부장	2500000	170000
1024	김말자	830225-2633334	1999-08-28	서울	011-5248-7789	기획부	대리	1900000	170000
1025	우재옥	801103-1654442	2000-10-01	서울	010-4563-2587	영업부	사원	1100000	160000
1026	김숙남	810907-2015457	2002-08-28	경기	010-2112-5225	영업부	사원	1050000	150000
1027	김영길	801216-1898752	2000-10-18	서울	019-8523-1478	총무부	과장	2340000	170000
1028	이남신	810101-1010101	2001-09-07	제주	016-1818-4848	인사부	사원	892000	110000
1029	김말숙	800301-2020202	2000-09-08	서울	016-3535-3636	총무부	사원	920000	124000
1030	정정해	790210-2101010	1999-10-17	부산	019-6564-6752	총무부	과장	2304000	124000
1031	지재환	771115-1687988	2001-01-21	서울	019-5552-7511	기획부	부장	2450000	160000
1032	심심해	810206-2222222	2000-05-05	전북	016-8888-7474	자재부	사원	880000	108000
1033	김미나	780505-2999999	1998-06-07	서울	011-2444-4444	영업부	사원	1020000	104000
1034	이정석	820505-1325468	2005-09-26	경기	011-3697-7412	기획부	사원	1100000	160000
1035	정영희	831010-2153252	2002-05-16	인천		개발부	사원	1050000	140000
1036	이재영	701126-2852147	2003-08-10	서울	011-9999-9999	자재부	사원	960400	190000
1037	최석규	770129-1456987	1998-10-15	인천	011-7777-7777	홍보부	과장	2350000	187000
1038	손인수	791009-2321456	1999-11-15	부산	010-6542-7412	영업부	대리	2000000	150000
1039	고순정	800504-2000032	2003-12-28	경기	010-2587-7895	영업부	대리	2010000	160000
1040	박세열	790509-1635214	2000-09-10	경북	016-4444-7777	인사부	대리	2100000	130000
1041	문길수	721217-1951357	2001-12-10	충남	016-4444-5555	자재부	과장	2300000	150000
1042	채정희	810709-2000054	2003-10-17	경기	011-5125-5511	개발부	사원	1020000	200000
1043	양미옥	830504-2471523	2003-09-24	서울	016-8548-6547	영업부	사원	1100000	210000
1044	지수환	820305-1475286	2004-01-21	서울	011-5555-7548	영업부	사원	1060000	220000
1045	홍원신	690906-1985214	2003-03-16	전북	011-7777-7777	영업부	사원	960000	152000
1046	허경운	760105-1458752	1999-05-04	경남	017-3333-3333	총무부	부장	2650000	150000
1047	산마루	780505-1234567	2001-07-15	서울	018-0505-0505	영업부	대리	2100000	112000
1048	이기상	790604-1415141	2001-06-07	전남		개발부	대리	2050000	106000
1049	이미성	830908-2456548	2000-04-07	인천	010-6654-8854	개발부	사원	1300000	130000
1050	이미인	810403-2828287	2003-06-07	경기	011-8585-5252	홍보부	대리	1950000	103000
1051	권영미	790303-2155554	2000-06-04	서울	011-5555-7548	영업부	과장	2260000	104000
1052	권옥경	820406-2000456	2000-10-10	경기	010-3644-5577	기획부	사원	1020000	105000
1053	김싱식	800715-1313131	1999-12-12	전북	011-7585-7474	자재부	사원	960000	108000
1054	정상호	810705-1212141	1999-10-16	강원	016-1919-4242	홍보부	사원	980000	114000
1055	정한나	820506-2425153	2004-06-07	서울	016-2424-4242	영업부	사원	1000000	104000
1056	전용재	800605-1456987	2004-08-13	인천	010-7549-8654	영업부	대리	1950000	200000
1057	이미경	780406-2003214	1998-02-11	경기	016-6542-7546	자재부	부장	2520000	160000
1058	김신제	800709-1321456	2003-08-08	인천	010-2415-5444	기획부	대리	1950000	180000
1059	임수봉	810809-2121244	2001-10-10	서울	011-4151-4154	개발부	사원	890000	102000
1060	김신애	810809-2111111	2001-10-10	서울	011-4151-4444	개발부	사원	900000	102000
*/

--○ 생성한 함수(→ FN_GNEDER()) 가 제대로 작동하는지의 여부 확인
SELECT '910101-2123456' "주민번호", FN_GENDER('910101-2123456') "성별확인"
FROM DUAL;
--==>> 910101-2123456	여자

SELECT NAME, SSN, FN_GENDER(SSN) "함수호출결과"
FROM TBL_INSA;
--==>>
/*
홍길동	771212-1022432	남자
이순신	801007-1544236	남자
이순애	770922-2312547	여자
김정훈	790304-1788896	남자
한석봉	811112-1566789	남자
이기자	780505-2978541	여자
장인철	780506-1625148	남자
김영년	821011-2362514	여자
나윤균	810810-1552147	남자
김종서	751010-1122233	남자
유관순	801010-2987897	여자
정한국	760909-1333333	남자
조미숙	790102-2777777	여자
황진이	810707-2574812	여자
이현숙	800606-2954687	여자
이상헌	781010-1666678	남자
엄용수	820507-1452365	남자
이성길	801028-1849534	남자
박문수	780710-1985632	남자
유영희	800304-2741258	여자
홍길남	801010-1111111	남자
이영숙	800501-2312456	여자
김인수	731211-1214576	남자
김말자	830225-2633334	여자
우재옥	801103-1654442	남자
김숙남	810907-2015457	여자
김영길	801216-1898752	남자
이남신	810101-1010101	남자
김말숙	800301-2020202	여자
정정해	790210-2101010	여자
지재환	771115-1687988	남자
심심해	810206-2222222	여자
김미나	780505-2999999	여자
이정석	820505-1325468	남자
정영희	831010-2153252	여자
이재영	701126-2852147	여자
최석규	770129-1456987	남자
손인수	791009-2321456	여자
고순정	800504-2000032	여자
박세열	790509-1635214	남자
문길수	721217-1951357	남자
채정희	810709-2000054	여자
양미옥	830504-2471523	여자
지수환	820305-1475286	남자
홍원신	690906-1985214	남자
허경운	760105-1458752	남자
산마루	780505-1234567	남자
이기상	790604-1415141	남자
이미성	830908-2456548	여자
이미인	810403-2828287	여자
권영미	790303-2155554	여자
권옥경	820406-2000456	여자
김싱식	800715-1313131	남자
정상호	810705-1212141	남자
정한나	820506-2425153	여자
전용재	800605-1456987	남자
이미경	780406-2003214	여자
김신제	800709-1321456	남자
임수봉	810809-2121244	여자
김신애	810809-2111111	여자
*/

---

--○ 임의의 정수 두 개를 매개변수(입력 파라미터)로 넘겨받아
--   A 의 B 승 값을 반환하는 사용자 정의 함수를 작성한다.
--   함수명 : FN_POW()
/*
사용 예)
SELECT FN_POW(10,3)
FROM DUAL;
--==>> 1000
*/

CREATE OR REPLACE FUNCTION FN_POW
(
    VNUM1   NUMBER
,   VNUM2   NUMBER
)
RETURN  NUMBER
IS
    VN      NUMBER :=1;
    VRESULT NUMBER;
BEGIN
    VRESULT := VNUM1;
    LOOP
        EXIT WHEN VN >= VNUM2;  
        VRESULT := (VRESULT * VNUM1);
        VN := VN+1;              
    END LOOP;
    RETURN VRESULT;
END;
--==>> Function FN_POW이(가) 컴파일되었습니다.
----------------------------------------------------------------------------
--○ 확인
SELECT FN_POW(10,3)
FROM DUAL;
--==>>1000

/*
FN_POW(10,3)
       -----
       10 * 10 * 10 = 1000
   1 * 10 * 10 * 10 = 1000  -- 누적곱 초기화 '1'
   0 * 10 * 10 * 10 = 0
*/

CREATE OR REPLACE FUNCTION FN_POW
(   A   NUMBER                      -- 10
,   B   NUMBER                      -- 3
)
RETURN NUMBER                       -- 1000
IS 
    VRESULT NUMBER := 1;            -- 누적곱에 대한 연산 결과
    VNUM    NUMBER;                 -- 루프 변수
BEGIN
    FOR VNUM IN 1 .. B LOOP         -- 1 ~ 3
        VRESULT := VRESULT * A;     -- 1 * 10 * 10 * 10
    END LOOP;
    
    RETURN VRESULT;
    
END;
--==>> Function FN_POW이(가) 컴파일되었습니다.
----------------------------------------------------------------------
--○ 생성한 함수(→ FN_POW()) 가 제대로 작동하는지의 여부 확인
SELECT FN_POW(10,3) "함수호출결과"
FROM DUAL;
--==>> 1000

SELECT FN_POW(5,3) "함수호출결과"
FROM DUAL;
--==>> 125

---

--○ TBL_INSA 테이블의 급여 계산 전용 함수를 정의한다.
--   급여는 『(기본급*12)+수당』 기반으로 연산을 수행한다.
--   함수명 : FN_PAY(기본급, 수당)

CREATE OR REPLACE FUNCTION FN_PAY
(   VBPAY       NUMBER
,   VSUDANG     NUMBER
)
RETURN NUMBER
IS
    VRES    NUMBER :=0 ;
BEGIN
    VRES := ((VBPAY * 12) + NVL(VSUDANG,0));
    
    RETURN VRES;
END;
--==>> Function FN_PAY이(가) 컴파일되었습니다.

--// 함수 확인
SELECT FN_PAY(BASICPAY,SUDANG) "급여"
FROM TBL_INSA;
--==>>
/*
31520000
16040000
30760000
23620400
17200000
27330000
15150000
11545000
10300400
30610000
12380000
10674000
19315000
13330000
12704000
28350000
11610000
10683000
27765000
10700000
10620000
23700000
30170000
22970000
13360000
12750000
28250000
10814000
11164000
27772000
29560000
10668000
12344000
13360000
12740000
11714800
28387000
24150000
24280000
25330000
27750000
12440000
13410000
12940000
11672000
31950000
25312000
24706000
15730000
23503000
27224000
12345000
11628000
11874000
12104000
23600000
30400000
23580000
10782000
10902000
*/

---

--○ TBL_INSA 테이블의 입사일을 기준으로
--   현재까지의 근무 년수를 반환하는 함수를 정의한다.
--   단, 근무 년수는 소수점 이하 한자리까지 계산한다.
--   함수명 : FN_WORKYEAR(입사일)

CREATE OR REPLACE FUNCTION FN_WORKYEAR
( 
    NAL DATE
)
RETURN NUMBER
IS
    VRES    NUMBER;
BEGIN
    
    VRES := TRUNC(MONTHS_BETWEEN(SYSDATE,NAL)/12,1);
    
    RETURN VRES;
END;
--==>> Function FN_WORKYEAR이(가) 컴파일되었습니다.

SELECT NAME,FN_WORKYEAR(IBSADATE) "근무년수"
FROM TBL_INSA;
--==>>
/*
홍길동	24.5
이순신	22.4
이순애	24.1
김정훈	22.5
한석봉	18.7
이기자	21.2
장인철	25.1
김영년	20.9
나윤균	19.5
김종서	25.7
유관순	22.8
정한국	23.5
조미숙	24.8
황진이	21.1
이현숙	23.7
이상헌	21.4
엄용수	22.6
이성길	18.7
박문수	23.3
유영희	19.5
홍길남	21.6
이영숙	20.1
김인수	28.1
김말자	23.6
우재옥	22.5
김숙남	20.6
김영길	22.5
이남신	21.6
김말숙	22.6
정정해	23.5
지재환	22.2
심심해	22.9
김미나	24.8
이정석	17.5
정영희	20.9
이재영	19.7
최석규	24.5
손인수	23.4
고순정	19.3
박세열	22.6
문길수	21.3
채정희	19.5
양미옥	19.5
지수환	19.2
홍원신	20.1
허경운	23.9
산마루	21.7
이기상	21.8
이미성	23
이미인	19.8
권영미	22.8
권옥경	22.5
김싱식	23.3
정상호	23.5
정한나	18.8
전용재	18.7
이미경	25.2
김신제	19.7
임수봉	21.5
김신애	21.5
*/

---

※ 참고

--※ 참고

-- 1. INSERT, UPDATE, DELET, (MERGE)
--==>> DML(Data Manipulation Language)
-- COMMIT / ROLL BACK 이 필요하다.

-- 2. CREATE, DROP, ALTER, (TRUNCATE)
--==>> DDL(Data Definition Language)
-- 실행하면 자동으로 COMMIT 된다.

-- 3. GRANT, REVOKE
--==>> DCL(Data Control Language)
-- 실행하면 자동으로 COMMIT 된다.

-- 4. COMMIT, ROLLBACK
--==>> TCL(Transaction Control Language)

-- 정적 PL/SQL 문 → DML문, TCL문 만 사용 가능하다.
-- 동적 PL/SQL 문 → DML문, DDL문, DCL문, TCL문 사용 가능하다.

--※ 정적 SQL (정적 PL/SQL)
--   기본적으로 사용하는 SQL 구문과
--   PL/SQL 구문 안에 SQL 구문을 직접 삽입하는 방법
--   작성이 쉽고 성능이 좋다.

--※ 동적 SQL (동적 PL/SQL)
--   완성되지 않는 SQL 구문을 기반으로
--   실행 중 변경 가능한 문자열 변수 또는 문자열 상수를 통해
--   SQL 구문을 동적으로 완성하여 실해앟는 방법
--   사전에 정의되지 않은 SQL 구문을
--   실행할 때 완성 및 확정하여 실행할 수 있다.
--   DML, TCL 외에도 DDL, DCL 사용이 가능하다

---

• PROCEDURE(프로시저)

--■■■ PROCEDURE(프로시저) ■■■--
--//★ 개발자로서 PL/SQL 에서 가장 중요한 파트 ★

--1. PL/SQL 에서 가장 대표적인 구조인 스토어드 프로시저는
--   개발자가 자주 작성해야 하는 업무의 흐름을
--   미리 작성하여 데이터베이스 내에 저장해 두었다가
--   필요할 때 마다 호출하여 실행할 수 있도록 처리해 주는 구문이다.

--2. 형식 및 구조
/*
CREATE [OR REPLACE] PROCEDURE 프로시저명
[( 매개변수 IN 데이터타입
 , 매개변수 OUT 데이터타입
 , 매개변수 INOUT 데이터타입
)]
IS
    [--주요 변수 선언;]
BEGIN
    -- 실행구문;
    ...
    [EXCEPTION
        -- 예외 처리 구문;]
END;
*/

--※ 앞에서 다룬 FUNCTION 과 비교했을 때..
--   『RETURN 반환자료형』 부분이 존재하지 않으며,
--   『RETURN』구문 자체도 존재하지 않으며,
--   프로시저를 실행하는 과정에서 넘겨주게 되는
--   매개 변수의 종류는 IN, OUT, INOUT 으로 구분된다.

--// 기능한다는 점에 있어서는 함수하고 다를 바가 없지만 RETURN 값이 없다!!!
--// 매개변수 종류가 다르다! (다만, 출력매개변수나 입출력매개변수의 경우 개발자가 사용할 필요가 거의 없다)

--3. 실행(호출)
/*
EXEC[UTE] 프로시저명[(인수1, 인수2)];
*/

• 관찰

- 실습을 위한 테이블 생성 및 데이터 입력

--※ 프로시저 실습 진행간 테이블 생성 및 데이터 입력

--○ INSERT 쿼리 실행을 프로시저로 작성(INSERT 프로시저)

-- 실습 테이블 생성(TBL_STUDENTS)
CREATE TABLE TBL_STUDENTS
( ID        VARCHAR2(20)
, NAME      VARCHAR2(40)
, TEL       VARCHAR2(30)
, ADDR      VARCHAR2(100)
);
--==>> Table TBL_STUDENTS이(가) 생성되었습니다.

-- 실습 테이블 생성(TBL_IDPW)
CREATE TABLE TBL_IDPW
( ID    VARCHAR2(20)
, PW    VARCHAR2(40)
, CONSTRAINT IDPW_ID_PK PRIMARY KEY(ID)
);
--==>> Table TBL_IDPW이(가) 생성되었습니다.
--// (1:1관계) 바람직한 구조는 아니나, 
--// INSERT 프로시저를 확인하기 위해 좋은 개념이기 때문에 만들어 둠
--// 원래는 IDPW 를 복합인덱스로 구성하여 보면 됨

-- 한 명의 학생 정보 등록   → 두 테이블에 데이터 입력 --//상황
INSERT INTO TBL_STUDENTS(ID,NAME,TEL,ADDR)
VALUES ('superman','재혁킴','010-1111-1111','제주도 서귀포시');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_IDPW(ID,PW)
VALUES ('superman', 'java002$');
--==>> 1 행 이(가) 삽입되었습니다.

SELECT *
FROM TBL_STUDENTS;
--==>> superman	재혁킴	010-1111-1111	제주도 서귀포시
SELECT *
FROM TBL_IDPW;
--==>> superman	java002$

-- 위의 업무를 프로시저(INSERT 프로시저, 입력 프로시저)를 생성하여 활용하게 되면
EXEC PRC_STUDENTS_INSERT('happyday','java002$','서영유','010-2222-2222','서울 마포구');
-- 이와 같은 구문 한 줄로 양쪽 테이블에 데이터를 각자 모두 제대로 입력할 수 있다.

--○ INSERT 쿼리 실행을 프로시저로 작성(INSERT 프로시저)

-- 실습 테이블 생성(TBL_STUDENTS) → 2023.0407_02_scott.sql 참조
-- 실습 테이블 생성(TBL_IDPW) → 2023.0407_02_scott.sql 참조

-- 프로시저 생성
-- 프로시저명 : PRC_STUDENTS_INSERT(아이디, 패스워드, 이름, 전화번호, 주소)
CREATE OR REPLACE PROCEDURE PRC_STUDENTS_INSERT
( V_ID      IN  TBL_IDPW.ID%TYPE
, V_PW      IN  TBL_IDPW.PW%TYPE
, V_NAME    IN  TBL_STUDENTS.NAME%TYPE
, V_TEL     IN  TBL_STUDENTS.TEL%TYPE
, V_ADDR    IN  TBL_STUDENTS.ADDR%TYPE
)
IS
BEGIN
    --TBL_IDPW 테이블에 데이터 입력
    INSERT INTO TBL_IDPW(ID,PW)
    VALUES(V_ID,V_PW);
    --TBL_STUDENTS 테이블에 데이터 입력
    INSERT INTO TBL_STUDENTS(ID,NAME,TEL,ADDR)
    VALUES(V_ID,V_NAME,V_TEL,V_ADDR);
    --커밋
    COMMIT;
END;
--==>> Procedure PRC_STUDENTS_INSERT이(가) 컴파일되었습니다.

--○ 생성한 프로시저가 제대로 작동하는지의 여부 확인
EXEC PRC_STUDENTS_INSERT('happyday','java002$','서영유','010-2222-2222','서울 마포구');
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.

SELECT *
FROM TBL_STUDENTS;
--==>>
/*
superman	재혁킴	010-1111-1111	제주도 서귀포시
happyday	서영유	010-2222-2222	서울 마포구
*/
SELECT *
FROM TBL_IDPW;
--==>>
/*
superman	java002$
happyday	java002$
*/

---

--○ 학번, 이름, 국어점수, 영어점수, 수학점수 데이터를
--   입력받을 수 있는 실습 테이블 생성(TBL_SUNGJUK)
CREATE TABLE TBL_SUNGJUK
( HAKBUN    CHAR(10)
, NAME      VARCHAR2(40)
, KOR       NUMBER(3)
, ENG       NUMBER(3)
, MAT       NUMBER(3)
, CONSTRAINT SUNGJUK_HAK_PK PRIMARY KEY(HAKBUN)
);
--==>> Table TBL_SUNGJUK이(가) 생성되었습니다.

--○ 생성된 테이블에 컬럼 구조 추가
--   (총점→TOT, 평균→AVG, 등급→GRADE)
ALTER TABLE TBL_SUNGJUK
ADD( TOT NUMBER(3), AVG NUMBER(4,1), GRADE CHAR );
--==>> Table TBL_SUNGJUK이(가) 변경되었습니다.

--※ 여기서 추가한 컬럼에 대한 항목들은 모두
--   프로시저 실습을 위해 추가하는 것일 뿐
--   실제 테이블 구조에 적합하지도, 바람직하지도 않은 내용입니다.

--○ 변경된 테이블 구조 확인
DESC TBL_SUNGJUK;
--==>>
/*
이름     널?       유형           
------ -------- ------------ 
HAKBUN NOT NULL CHAR(10)     
NAME            VARCHAR2(40) 
KOR             NUMBER(3)    
ENG             NUMBER(3)    
MAT             NUMBER(3)    
TOT             NUMBER(3)    
AVG             NUMBER(4,1)  
GRADE           CHAR(1)
*/

-- 실습 테이블 생성(TBL_SUNGJUK) → 20230407_02_scott.sql 참조

--○ 데이터 입력 시
--   특정 항목의 데이터
--   (→ 기존 컬럼의 데이터 즉, 학번, 이름, 국어점수, 영어점수, 수학점수) 만 입력하면
--   내부적으로 총점, 평균, 등급 항목이 함께 처리되어 입력될 수 있도록 하는
--   프로시저를 작성한다.(생성한다.)
--   프로시저명 : PRC_SUNGJUK_INSERT()
/*
실행 예)
EXEC PRC_SUNGJUK_INSERT('2312120','연수양',90,80,70);

→ 프로시저 호출로 처리된 결과
학번    이름    국어점수 영어점수 수학점수 총점 평균 등급
2312120 연수양  90       80       70       240  80    B
*/

CREATE OR REPLACE PROCEDURE PRC_SUNGJUK_INSERT
( V_HAK     IN  TBL_SUNGJUK.HAKBUN%TYPE
, V_NAME    IN  TBL_SUNGJUK.NAME%TYPE
, V_KOR     IN  TBL_SUNGJUK.KOR%TYPE
, V_ENG     IN  TBL_SUNGJUK.ENG%TYPE
, V_MAT     IN  TBL_SUNGJUK.MAT%TYPE
)
IS
    V_TOT   TBL_SUNGJUK.TOT%TYPE;
    V_AVG   TBL_SUNGJUK.AVG%TYPE;
    V_GRADE TBL_SUNGJUK.GRADE%TYPE;
BEGIN
    V_TOT := V_KOR + V_ENG + V_MAT;
    V_AVG := V_TOT / 3;
    
    IF (V_AVG >=90)
        THEN V_GRADE := 'A';
    ELSIF (V_AVG >=80)
        THEN V_GRADE := 'B';
    ELSIF (V_AVG >=70)
        THEN V_GRADE := 'C';
    ELSE
        V_GRADE := 'F';
    END IF;

    INSERT INTO TBL_SUNGJUK(HAKBUN,NAME,KOR,ENG,MAT,TOT,AVG,GRADE)
    VALUES (V_HAK, V_NAME, V_KOR, V_ENG, V_MAT
           , V_TOT, V_AVG, V_GRADE);
    
    COMMIT;
END;
--==>> Procedure PRC_SUNGJUK_INSERT이(가) 컴파일되었습니다.
--------------------------------------------------------------------------------------
EXEC PRC_SUNGJUK_INSERT('2312120','연수양',90,80,70);
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.
SELECT *
FROM TBL_SUNGJUK;
--==>> 2312120   	연수양	90	80	70	240	80	B

CREATE OR REPLACE PROCEDURE PRC_SUNGJUK_INSERT
( V_HAKBUN  IN TBL_SUNGJUK.HAKBUN%TYPE
, V_NAME    IN TBL_SUNGJUK.NAME%TYPE
, V_KOR     IN TBL_SUNGJUK.KOR%TYPE
, V_ENG     IN TBL_SUNGJUK.ENG%TYPE
, V_MAT     IN TBL_SUNGJUK.MAT%TYPE
)
IS
    -- INSERT 쿼리문을 수행하는데 필요한 주요 변수 선언
    -- V_TOT    NUMBER(3);
    V_TOT   TBL_SUNGJUK.TOT%TYPE;
    -- V_AVG    NUMBER(4,1);
    V_AVG   TBL_SUNGJUK.AVG%TYPE;
    -- V_GRADE  CHAR;
    V_GRADE TBL_SUNGJUK.GRADE%TYPE;
    
BEGIN
    -- 아래의 쿼리문을 실행하기 위해서는 
    -- 선언한 변수에 값을 담아내야 한다. (V_TOT, V_AVG)
    V_TOT := V_KOR + V_ENG + V_MAT;
    V_AVG := V_TOT/3;
    
    IF (V_AVG >= 90)
        THEN V_GRADE := 'A';
    ELSIF (V_AVG >= 80)
        THEN V_GRADE := 'B';
    ELSIF (V_AVG >= 70)
        THEN V_GRADE := 'C';
    ELSIF (V_AVG >= 60)
        THEN V_GRADE := 'D';
    ELSE
        V_GRADE := 'F';
    END IF;

    -- INSERT 쿼리문 구성
    INSERT INTO TBL_SUNGJUK(HAKBUN,NAME,KOR,ENG,MAT,TOT,AVG,GRADE)
    VALUES(V_HAKBUN, V_NAME, V_KOR, V_ENG, V_MAT, V_TOT, V_AVG, V_GRADE);

    -- 커밋
    COMMIT;
END;
--==>> Procedure PRC_SUNGJUK_INSERT이(가) 컴파일되었습니다.

--○ 생성한 프로시저(→PRC_SUNGJUK_INSERT())가 제대로 작동하는지의 여부 확인
-- 프로시저 호출
EXECUTE PRC_SUNGJUK_INSERT('2312120','연수양',90,80,70);
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.

SELECT *
FROM TBL_SUNGJUK;
--==>> 2312120   	연수양	90	80	70	240	80	B

---

--○ TBL_SUNGJUK 테이블에서
--   특정 학생의 점수(학번, 국어점수, 영어점수, 수학점수)
--   데이터 수정 시 총점, 평균, 등급까지 수정되는 프로시저를 작성한다.
--   프로시저 명 : PRC_SUNGJUK_UPDATE
/*
실행 예)
EXEC PRC_SUNGJUK_UPDATE('2312120',100,100,100);

→ 프로시저 호출로 처리된 결과)
학번        이름    국어점수 영어점수 수학점수 총점 평균 등급
2312120   	연수양	100	     100	  100	   300	100	 A
*/

CREATE OR REPLACE PROCEDURE PRC_SUNGJUK_UPDATE
( V_HAK IN TBL_SUNGJUK.HAKBUN%TYPE
, V_KOR IN TBL_SUNGJUK.KOR%TYPE
, V_ENG IN TBL_SUNGJUK.ENG%TYPE
, V_MAT IN TBL_SUNGJUK.MAT%TYPE
)
IS
    V_TOT TBL_SUNGJUK.TOT%TYPE;
    V_AVG TBL_SUNGJUK.AVG%TYPE;
    V_GRADE TBL_SUNGJUK.GRADE%TYPE;
    
BEGIN
    V_TOT := V_KOR + V_ENG + V_MAT;
    V_AVG := V_TOT/3;
    
    IF (V_AVG >= 90)
        THEN V_GRADE := 'A';
    ELSIF (V_AVG >= 80)
        THEN V_GRADE := 'B';
    ELSIF (V_AVG >= 70)
        THEN V_GRADE := 'C';
    ELSIF (V_AVG >= 60)
        THEN V_GRADE := 'D';
    ELSE
        V_GRADE := 'F';
    END IF;

    UPDATE TBL_SUNGJUK
    SET KOR = V_KOR , ENG = V_ENG, MAT = V_MAT
       , TOT = V_TOT , AVG = V_AVG, GRADE = V_GRADE
    WHERE HAKBUN = V_HAK;
    
    COMMIT;
END;
--==>> Procedure PRC_SUNGJUK_UPDATE이(가) 컴파일되었습니다
------------------------------------------------------------------------------------
EXEC PRC_SUNGJUK_UPDATE('2312120',100,100,100);
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.

SELECT *
FROM TBL_SUNGJUK;
--==>>
/*
2312120   	연수양	100	100	100	300	100	A
*/