-
728x90
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 */
728x90'SsY > Class' 카테고리의 다른 글