[Oracle]/PL·SQL (Program source)
[Oracle - PL/SQL] 20231103 [프로그램 소스]
Jelly-fish
2023. 11. 3. 17:49
20231103_01_scott(plsql).sql
SELECT USER
FROM DUAL;
--==>> SCOTT
-- 코드의 재사용성을 위해, 함수로 생성 후 필요성에 위해 수정하여 사용할 수 있는 것이 함수이다.
-- VIEW : REPLACE 사용하는 것도 추후에 수정을 하여 반영할 수 있도록 하는 것이다.
-- 매개변수를 받지 않는 함수도 있기 때문에 매개변수 선언은 생략 가능하다.
--■■■ FUNCTION(함수) ■■■--
-- 1. 함수란 하나 이상의 PL/SQL 문으로 구성된 서브 루틴으로
-- 코드를 다시 사용할 수 있도록 캡슐화 하는데 사용된다.
--- 오라클에서는 오라클에 정의된 기본 제공 함수를 사용하거나
-- 직접 스토어드 함수를 만들 수 있다.(→ 사용자 정의 함수)
-- 이 사용자 정의 함수는 시스템 함수처럼 쿼리에서 호출하거나
-- 저장 프로시저처럼 EXECUTE 문을 통해 실행할 수 있다.
-- 2. 형식 및 구조
/*
CREATE [OR REPLACE] FUNCTION 함수명
[(매개변수명 1 자료형
, 매개변수명2 자료형
)]
RETURN 데이터타입
IS
-- 주요 변수 선언
BEGIN
-- 실행문;
...
RETURN (값);
[EXCEPTION]
-- 예외 처리 구문;
-- END;
*/
--※ 사용자 정의 함수 (스토어드 함수)는
-- IN 파라미터(입력 매개변수)만 사용할 수 있으며
-- 반드시 반환될 값의 데이터타입을 RETURN 문에 선언해야 하고,
-- FUNCTION 은 반드시 단일 값만 반환한다.
--==============================================================================
-- 함수 생성 기본 뼈대 구문
--****************************
--RETURN 데이터타입 -- (반환값)
--
--IS -- DECLARE
--BEGIN -- BEGIN
--END; -- END
--****************************
-- RETURN 데이터타입
--
--[JAVA]
--
--반환자료형 함수명()
--{
--
--
-- RETURN XXX;
--}
--
--반환 자료형을 확인하려면, 함수명 앞에 명시하지 않을 경우
--함수 내부를 들여다봐야 하므로 자바에서는 함수명 앞에 리턵자료형을 명시해 주었었다.
--
--상자 안에 어떤것이 들어있는지 알 수 없으니까
--상자 밖에 어떤 유형의 데이터가 존재하는지 바깥에 기재해 놓은 형식이다.
--
--
--오라클에서도 마찬가지로, 함수 앞에 리턴 자료형을 명시한 것이다.
--RETURN 데이터타입
--IS
--BEGIN
--END;
-- 입력 매개변수 방식. 매개변수에 어떤 값을 넣어준 건지 명시한다.
--★ 입력 매개변수 : 내가 어떤 변수를 줄 테니까 그걸 기준으로 일을 처리해 달라는 형식이다.
---- EX ) 떡을 건네는 것...
--
--★ 출력 매개변수 : 내가 전달해준 변수에 함수 처리 결과를 저장해서 반환해 주는 형식이다.
---- EX ) 빈 통에 반찬을 담는 것...
--
--★ 입출력 매개변수 : 내가 어떤 변수에 뭘 담아서 줄 테니까 안에 담겨있는 걸 쓰고
-- 처리된 결과를 다시 그 안에 넣어서 돌려줘.
---- EX ) 고구마를 담은 통을 갖다드리고... 올 때 김치를 담아 오는 느낌...
--******************************************************************************
--
--함수에서는 입력 매개변수만 사용할 수 있다.
--RETURN 문은 빼먹으면 안 된다.
--리턴 자료형은 오로지 하나이므로, 반환하는 값도 단일 값이다!
--
--
--코드 안에 수행해야 될 명령을 순차적으로 처리 : PROCEDURAL!!
--==============================================================================
-- 오라클의 함수는 테이블에 의존적이지 않다!!
--○ TBL_INSA 테이블 전용 성별 확인 함수 정의(생성)
-------------------
-- 매개변수 구조(형태) → '771212-1022432' → 'YYMMDD-NNNNNNN'
-- 함수명 : FN_GENDER()
-- │ 주민등록번호 → '771212-1022432'
-- CREATE OR REPLACE FUNCTION (함수명) (매개변수명 매개변수타입)
-- ( V_SSN VARCHAR2 ← 뒤에 세미콜론 붙이지 않는다!!!
-- , V_VAL NUMBER(10) ← 뒤에 세미콜론 붙이지 않는다!!!
-- )
CREATE OR REPLACE FUNCTION FN_GENDER(V_SSN VARCHAR2) -- 매개변수 : 자릿수(길이) 지정 안 함
RETURN VARCHAR2 -- 반환자료형 : 자릿수(길이) 지정 안 함
IS
-- 선언부 → 주요 변수 선언(및 초기화)
V_RESULT VARCHAR2(20);
BEGIN
-- 실행부 → 연산 및 처리(그리고 결과값 반환)
IF ( SUBSTR(V_SSN, 8, 1) IN ('1', '3') )
THEN V_RESULT := '남자';
ELSIF ( SUBSTR(V_SSN, 8, 1) IN ('2', '4') )
THEN V_RESULT := '여자';
ELSE
V_RESULT := '성별확인불가';
END IF;
-- 결과값 반환 CHECK~!!!
RETURN V_RESULT; -- RETURN 구문을 작성하지 않으면, 이 값이(IF문의 결과값)
-- 함수를 호출한곳에 전달되지 않는다.
END;
--==>> Function FN_GENDER이(가) 컴파일되었습니다.
--○ 임의의 정수 두 개를 매개변수(입력 파라미터)로 넘겨받아 → (A, B)
-- A 의 B 승의 값을 반환하는 사용자 정의 함수를 작성한다.
-- 단,기존의 오라클 내장 함수를 이용하지 않고, 반복문을 활용하여 작성한다.
-- 함수명 : FM_POW()
/*
사용 예)
SELECT FN_POW(10, 3)
FROM DUAL;
--==>> 1000
*/
SELECT FN_POW(10, -2)
FROM DUAL;
-- 두 번째 매개변수가 음수일 때, 1이
CREATE OR REPLACE FUNCTION FN_POW(A NUMBER, B NUMBER)
RETURN NUMBER
IS
-- 선언부
RESULT NUMBER := 1;
N NUMBER := 1;
BEGIN
-- 실행부
IF (B > 1)
THEN
LOOP
RESULT := RESULT * A;
N := N + 1;
EXIT WHEN N > B;
END LOOP;
ELSIF (B < 0)
THEN
LOOP
RESULT := RESULT / A;
N := N + 1;
EXIT WHEN N > B;
END LOOP;
END IF;
IF (B = 0)
THEN RESULT := 1;
N := B;
ELSIF (B = 1)
THEN RESULT := A;
N := B;
END IF;
RETURN RESULT;
END;
/*
FN_POW(10, 3) → 1000
기준 1 * 10 * 10 * 10 → 1000
기준 0 * 10 * 10 * 10 → 0
*/
--[선생님 풀이!! : FOR LOOP 를 이용한 풀이]***************************************
-- IF 문을 통해 구성하지 않고도, 값이 제대로 출력됨을 확인 가능!!
CREATE OR REPLACE FUNCTION FN_POW2(A NUMBER, B NUMBER)
RETURN NUMBER
IS
V_RESULT NUMBER := 1; -- 반환 결과값 변수 → 1로 초기화~!!! CHECK~!!!
V_NUM NUMBER; -- 루프변수
BEGIN
-- 반복문 구성
FOR V_NUM IN 1 .. B LOOP
V_RESULT := V_RESULT * A; -- [JAVA] : V_RESULT *= A;
END LOOP;
-- 최종 결과값 반환
RETURN V_RESULT;
END;
--○ TBL_INSA 테이블의 급여 계산 전용 함수를 정의한다.
-- 급여는 『(기본급*12) + 수당』 기반으로 연산을 수행한다.
-- 함수명 : FN_PAY(기본급, 수당)
CREATE OR REPLACE FUNCTION FN_PAY(A NUMBER, B NUMBER)
RETURN NUMBER
IS
-- 선언부
RESULT NUMBER := 0;
BEGIN
-- 실행부
RESULT := A * 12 + B;
RETURN RESULT;
END;
--[선생님 풀이!!]***************************************************************
CREATE OR REPLACE FUNCTION FN_PAY2(VBASICPAY NUMBER, VSUDANG NUMBER)
RETURN NUMBER
IS
-- 주요 변수 선언
VRESULT NUMBER;
BEGIN
-- 연산 및 처리
--★ 항상 NULL 값이 들어올 수 있음을 예상하고 함수를 정의하도록 한다!!
-- 만약, 값이 둘 중 하나라도 NULL 일 경우 제대로 된 처리가 이루어지지 않는다는 것을 생각하지 못했다...
VRESULT := (NVL(VBASICPAY, 0) * 12) + NVL(VSUDANG, 0);
-- 최종 결과값 반환
RETURN VRESULT;
END;
--○ TBL_INSA 테이블에서
-- 입사일을 기준으로 현재까지의 근무년수를 반환하는 함수를 정의한다.
-- 단, 근무년수는 소수점 이하 한자리까지 계산한다.
-- 함수명 : FN_WORKYEAR(입사일)
CREATE OR REPLACE FUNCTION FN_WORKYEAR(IBSADATE DATE)
RETURN VARCHAR2
IS
-- 선언부
YEAR NUMBER;
MONTH NUMBER;
CHAR_RESULT VARCHAR2(100);
BEGIN
YEAR := TRUNC((MONTHS_BETWEEN(SYSDATE, IBSADATE) / 12));
MONTH := TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, IBSADATE) , 12));
CHAR_RESULT := YEAR || '년' || MONTH || '개월';
RETURN CHAR_RESULT;
END;
SELECT FN_WORKYEAR(IBSADATE)
, TRUNC(FN_WORKYEAR(IBSADATE)) "년"
, FN_WORKYEAR(IBSADATE) - TRUNC(FN_WORKYEAR(IBSADATE)) "남은것"
, ROUND((FN_WORKYEAR(IBSADATE) - TRUNC(FN_WORKYEAR(IBSADATE))) * 12) "개월"
, IBSADATE
, SYSDATE
, MOD(TRUNC((MONTHS_BETWEEN(SYSDATE, IBSADATE) / 12)))
FROM TBL_INSA;
SELECT 12 / 9
FROM DUAL;
-- 1.
SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('1998-10-11', 'YYYY-MM-DD')) / 12 "RESULT"
FROM DUAL;
--==>> 25.06315891328156113102349661489446435683
-- 2.
SELECT TRUNC( MONTHS_BETWEEN(SYSDATE,TO_DATE('1998-10-11', 'YYYY-MM-DD')) / 12) || '년' ||
TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, TO_DATE('1998-10-11', 'YYYY-MM-DD')), 12)) || '개월'
FROM DUAL;
--==>> 25년0개월
CREATE OR REPLACE FUNCTION FN_WORKYEAR2(VIBSADATE DATE)
RETURN VARCHAR2
IS
VRESULT VARCHAR2(20);
BEGIN
VRESULT := TRUNC(MONTHS_BETWEEN(SYSDATE, VIBSADATE) / 12) || '년' ||
TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, VIBSADATE), 12)) || '개월';
RETURN VRESULT;
END;
--(* TRUNCATE 레코드를 잘라내서 삭제해 버리는 구문)
--------------------------------------------------------------------------------
--※ 참고
-- 1. INSERT, UPDATE, DELETE, (MERGE)
--==>> DML(Data Manipulation Language)
-- COMMIT / ROLLBACK 이 필요하다.
-- 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문 사용 가능하다.
--------------------------------------------------------------------------------
-- 프로시저 : 리턴 자료형이 VOID인 메소드와 비슷하다.
-- 매개변수를 받지 않더라도 호출한 자리에 연산 결과값을 두고 간다.
-- 내부적인 처리 일어난 후...
-- 오라클에서 정의하는 함수는 RETURN 자료형이 없는 함수가 없다.
-- 입력 파라미터, 출력용 파라미터, 입출력 파라미터를 다 쓸 수 있도록 구성.
-- 함수 내부에서 절차적으로 진행하는 것이 중요하므로 프로시저 저으이.
-- 절차적으로 진행하지 않으면 문제가 발생할 수 있으므로 안에다 묶어 놓는 개념...
--■■■ PROCEDURE(프로시저) ■■■--
-- 1. PL/SQL 에서 가장 대표적인 구조인 스토어드 프로시저는
-- 개발자가 자주 작성해야 하는 업무의 흐름을
-- 미리 작성하여 데이터베이스 내에 저장해 두었다가
-- 필요할 떄마다 호출하여 실행할 수 있도록 처리해 주는 구문이다.
-- 2. 형식 및 구조
/*
CREATE [OR REPLACE] PROCEDURE 프로시저명
[( 매개변수 IN 데이터타입
, 매개변수 OUT 데이터타입
, 매개변수 INOUT 데이터타입
)]
IS
[-- 주요 변수 선언]
BEGIN
-- 실행 구문;
...
[EXCEPTION
-- 예외 처리 구문;]
END;
*/
--※ FUNCTION 과 비교했을 때 『RETURN 반환자료형』 부분이 존재하지 않으며,
-- 『RETURN』문 자체도 존재하지 않고,
-- 프로시저 실행 시 넘겨주게 되는 매개변수의 종류는
-- IN(입력), OUT(출력), INOUT(입출력)으로 구분된다.
-- 3. 실행(호출)
/*
EXE[CUTE] 프로시저명[(인수1, 인수2, ...)];
*/
SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2000-09-07', 'YYYY-MM-DD')) / 12
FROM DUAL;
--※ 프로시저 실습을 위한 테이블 생성은...
-- 『20231103_02_scott.sql』 파일 참조~!!!
--○ 프로시저 생성
-- 프로시저명 : PRC_STUDENT_INSERT(아이디, 패스워드, 이름, 전화, 주소)
CREATE OR REPLACE PROCEDURE PRC_STUDENT_INSERT
( 아이디
, 패스워드
, 이름
, 전화
, 주소
)
IS
BEGIN
END;
DESC TBL_IDPW;
DESC TBL_STUDENTS;
-- 모두 다 입력받아서 테이블에 값을 INSERT TO 해 주려는 목적이므로
-- 모든 변수는 'IN'이다.
CREATE OR REPLACE PROCEDURE PRC_STUDENT_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
INSERT INTO TBL_IDPW(ID, PW)
VALUES(V_ID, V_PW);
-- TBL_STUDENTS 테이블에 데이터 입력 → INSERT
INSERT INTO TBL_STUDENTS(ID, NAME, TEL, ADDR)
VALUES(V_ID, V_NAME, V_TEL, V_ADDR);
-- 커밋 (프로시저 내부에서 테이블에 값을 입력하므로 커밋이 안에 들어가 주어야 함.)
COMMIT;
END;
--==>> Procedure PRC_STUDENT_INSERT이(가) 컴파일되었습니다.
--※ 프로시저 실습을 위한 테이블 생성은...
-- 『20231103_02_scott.sql』 파일 참조~!!!
--○ 데이터 입력 시 특정 항목의 데이터만 입력하면
-- 내부적으로 다른 추가항목에 대한 처리가 함께 이루어질 수 있도록 하는
-- 프로시저를 작성한다.(생성한다.)
-- 프로시저명 : PRC_SUNGJUK_INSERT()
/*
문제인식)
현재 TBL_SUNGJUK 테이블은
HAKBUN, NAME, KOR, ENG, MAT, TOT, AVG, GRADE
(학번, 이름, 국어점수, 영어점수, 수학점수, 총점, 평균, 등급)
컬럼으로 구성되어 있다.
이 테이블을 대상으로 특정 항목(학번, 이름, 국어점수, 영어점수, 수학점수)만 입력하면
추가 항목(총점, 평균, 등급)은 알아서 처리될 수 있도록 프로시저를 구성하라는 것이다.
실행 예)
EXEC PRC_SUNGJUK_INSERT(1, '김다슬', 90, 80, 70);
→ 이와 같은 프로시저 호출로 처리된 결과
학번 이름 국어점수 영어점수 수학점수 총점 평균 등급
1 김다슬 90 80 70 240 80 B
*/
DESC TBL_SUNGJUK;
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
V_TOT TBL_SUNGJUK.TOT%TYPE := 0;
V_AVG TBL_SUNGJUK.AVG%TYPE := 0;
V_GRADE TBL_SUNGJUK.GRADE%TYPE;
BEGIN
-- 2. 총점 연산
V_TOT := V_KOR + V_ENG + V_MAT;
-- 3. 평균 연산
V_AVG := (V_TOT / 3);
-- 4. 등급 연산
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;
-- 5. 계산한 값들 INSERT INTO
-- HAKBUN, NAME, KOR, ENG, MAT, TOT, AVG, GRADE
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;
-- 내가 못했던거 다시 풀어보기!!!!!!!!!!!!! 꼭!!!!!!!!!!!!!!!!!!
-- 입력받은 데이터 동전 분류하기
-- 날짜 계산해서 입사일로부터 현재날짜까지 몇 년 몇 개월 지났는지 구하기
--[선생님 풀이!!!!]=============================================
CREATE OR REPLACE PROCEDURE PRC_SUNGJUK_INSERT2
( 학번
, 성명
, 국어점수
, 영어점수
, 수학점수
)
IS
BEGIN
END;
CREATE OR REPLACE PROCEDURE PRC_SUNGJUK_INSERT2
( 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 쿼리문을 수행하기 위해 필요한 추가 변수 구성
-- 매개변수로 받는 변수가 아니기 떄문에, IN, OUT, INOUT 작성하지 않는다!!!
-- 아래 선언부에서는 각각 변수 선언 부분이 끝날 때마다 세미콜론을 작성해 주어야 한다.
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문의 조건식이 괄호로 감싸져있지 않아도 문법적으로 문제가 되지 않는다.
-- 하지만, 괄호를 씌워주는 편이 더욱 가독성이 쉬워 문제를 풀 때 도움이 되므로 그렇게 구성하자.
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_INSERT2이(가) 컴파일되었습니다.
--○ TBL_SUNGJUK 테이블에서 특정 학생의 점수
-- (학번, 국어점수, 영어점수, 수학점수) 데이터 수정 시
-- 총점, 평균, 등급까지 함께 수정되는 프로시저를 생성한다.
-- 프로시저명 : PRC_SUNGJUK_UPDATE()
/*
실행 예)
EXEC PRC_SUNGJUK_UPDATE(2, 50, 50, 50);
→ 프로시저 호출로 처리된 결과
학번 이름 국어점수 영어점수 수학점수 총점 평균 등급
1 김다슬 90 80 70 240 80 B
2 노은하 50 50 50 150 50 F
HAKBUN, NAME, KOR, ENG, MAT, TOT, AVG, GRADE
(학번, 이름, 국어점수, 영어점수, 수학점수, 총점, 평균, 등급)
*/
CREATE OR REPLACE PROCEDURE PRC_SUNGJUK_UPDATE
( V_HAKBUN IN TBL_SUNGJUK.HAKBUN%TYPE
, V_NKOR IN TBL_SUNGJUK.KOR%TYPE
, V_NENG IN TBL_SUNGJUK.ENG%TYPE
, V_NMAT IN TBL_SUNGJUK.MAT%TYPE
)
IS
V_NAME TBL_SUNGJUK.NAME%TYPE;
V_NEWTOT TBL_SUNGJUK.TOT%TYPE := 0;
V_NEWAVG TBL_SUNGJUK.AVG%TYPE := 0;
V_NEWGRADE TBL_SUNGJUK.GRADE%TYPE;
BEGIN
-- #1 총점(NEW)
V_NEWTOT := V_NKOR + V_NENG + V_NMAT;
-- #2 평균(NEW)
V_NEWAVG := V_NEWTOT / 3;
-- #3 등급(NEW)
IF (V_NEWAVG >= 90)
THEN
V_NEWGRADE := 'A';
ELSIF (V_NEWAVG >= 90)
THEN
V_NEWGRADE := 'B';
ELSIF (V_NEWAVG >= 80)
THEN
V_NEWGRADE := 'C';
ELSIF (V_NEWAVG >= 60)
THEN
V_NEWGRADE := 'D';
ELSE
V_NEWGRADE := 'F';
END IF;
--HAKBUN, NAME, KOR, ENG, MAT, TOT, AVG, GRADE
UPDATE TBL_SUNGJUK
SET KOR = V_NKOR,
ENG = V_NENG,
MAT = V_NMAT,
TOT = V_NEWTOT,
AVG = V_NEWAVG,
GRADE = V_NEWGRADE
WHERE HAKBUN = V_HAKBUN;
COMMIT;
END;
20231103_02_scott.sql
SELECT USER
FROM DUAL;
--==>> SCOTT
-- 코드의 재사용성을 위해, 함수로 생성 후 필요성에 위해 수정하여 사용할 수 있는 것이 함수이다.
-- VIEW : REPLACE 사용하는 것도 추후에 수정을 하여 반영할 수 있도록 하는 것이다.
-- 매개변수를 받지 않는 함수도 있기 때문에 매개변수 선언은 생략 가능하다.
--■■■ FUNCTION(함수) ■■■--
-- 1. 함수란 하나 이상의 PL/SQL 문으로 구성된 서브 루틴으로
-- 코드를 다시 사용할 수 있도록 캡슐화 하는데 사용된다.
--- 오라클에서는 오라클에 정의된 기본 제공 함수를 사용하거나
-- 직접 스토어드 함수를 만들 수 있다.(→ 사용자 정의 함수)
-- 이 사용자 정의 함수는 시스템 함수처럼 쿼리에서 호출하거나
-- 저장 프로시저처럼 EXECUTE 문을 통해 실행할 수 있다.
-- 2. 형식 및 구조
/*
CREATE [OR REPLACE] FUNCTION 함수명
[(매개변수명 1 자료형
, 매개변수명2 자료형
)]
RETURN 데이터타입
IS
-- 주요 변수 선언
BEGIN
-- 실행문;
...
RETURN (값);
[EXCEPTION]
-- 예외 처리 구문;
-- END;
*/
--※ 사용자 정의 함수 (스토어드 함수)는
-- IN 파라미터(입력 매개변수)만 사용할 수 있으며
-- 반드시 반환될 값의 데이터타입을 RETURN 문에 선언해야 하고,
-- FUNCTION 은 반드시 단일 값만 반환한다.
--==============================================================================
-- 함수 생성 기본 뼈대 구문
--****************************
--RETURN 데이터타입 -- (반환값)
--
--IS -- DECLARE
--BEGIN -- BEGIN
--END; -- END
--****************************
-- RETURN 데이터타입
--
--[JAVA]
--
--반환자료형 함수명()
--{
--
--
-- RETURN XXX;
--}
--
--반환 자료형을 확인하려면, 함수명 앞에 명시하지 않을 경우
--함수 내부를 들여다봐야 하므로 자바에서는 함수명 앞에 리턵자료형을 명시해 주었었다.
--
--상자 안에 어떤것이 들어있는지 알 수 없으니까
--상자 밖에 어떤 유형의 데이터가 존재하는지 바깥에 기재해 놓은 형식이다.
--
--
--오라클에서도 마찬가지로, 함수 앞에 리턴 자료형을 명시한 것이다.
--RETURN 데이터타입
--IS
--BEGIN
--END;
-- 입력 매개변수 방식. 매개변수에 어떤 값을 넣어준 건지 명시한다.
--★ 입력 매개변수 : 내가 어떤 변수를 줄 테니까 그걸 기준으로 일을 처리해 달라는 형식이다.
---- EX ) 떡을 건네는 것...
--
--★ 출력 매개변수 : 내가 전달해준 변수에 함수 처리 결과를 저장해서 반환해 주는 형식이다.
---- EX ) 빈 통에 반찬을 담는 것...
--
--★ 입출력 매개변수 : 내가 어떤 변수에 뭘 담아서 줄 테니까 안에 담겨있는 걸 쓰고
-- 처리된 결과를 다시 그 안에 넣어서 돌려줘.
---- EX ) 고구마를 담은 통을 갖다드리고... 올 때 김치를 담아 오는 느낌...
--******************************************************************************
--
--함수에서는 입력 매개변수만 사용할 수 있다.
--RETURN 문은 빼먹으면 안 된다.
--리턴 자료형은 오로지 하나이므로, 반환하는 값도 단일 값이다!
--
--
--코드 안에 수행해야 될 명령을 순차적으로 처리 : PROCEDURAL!!
--==============================================================================
-- 오라클의 함수는 테이블에 의존적이지 않다!!
--○ TBL_INSA 테이블 전용 성별 확인 함수 정의(생성)
-------------------
-- 매개변수 구조(형태) → '771212-1022432' → 'YYMMDD-NNNNNNN'
-- 함수명 : FN_GENDER()
-- │ 주민등록번호 → '771212-1022432'
-- CREATE OR REPLACE FUNCTION (함수명) (매개변수명 매개변수타입)
-- ( V_SSN VARCHAR2 ← 뒤에 세미콜론 붙이지 않는다!!!
-- , V_VAL NUMBER(10) ← 뒤에 세미콜론 붙이지 않는다!!!
-- )
CREATE OR REPLACE FUNCTION FN_GENDER(V_SSN VARCHAR2) -- 매개변수 : 자릿수(길이) 지정 안 함
RETURN VARCHAR2 -- 반환자료형 : 자릿수(길이) 지정 안 함
IS
-- 선언부 → 주요 변수 선언(및 초기화)
V_RESULT VARCHAR2(20);
BEGIN
-- 실행부 → 연산 및 처리(그리고 결과값 반환)
IF ( SUBSTR(V_SSN, 8, 1) IN ('1', '3') )
THEN V_RESULT := '남자';
ELSIF ( SUBSTR(V_SSN, 8, 1) IN ('2', '4') )
THEN V_RESULT := '여자';
ELSE
V_RESULT := '성별확인불가';
END IF;
-- 결과값 반환 CHECK~!!!
RETURN V_RESULT; -- RETURN 구문을 작성하지 않으면, 이 값이(IF문의 결과값)
-- 함수를 호출한곳에 전달되지 않는다.
END;
--==>> Function FN_GENDER이(가) 컴파일되었습니다.
--○ 임의의 정수 두 개를 매개변수(입력 파라미터)로 넘겨받아 → (A, B)
-- A 의 B 승의 값을 반환하는 사용자 정의 함수를 작성한다.
-- 단,기존의 오라클 내장 함수를 이용하지 않고, 반복문을 활용하여 작성한다.
-- 함수명 : FM_POW()
/*
사용 예)
SELECT FN_POW(10, 3)
FROM DUAL;
--==>> 1000
*/
SELECT FN_POW(10, -2)
FROM DUAL;
-- 두 번째 매개변수가 음수일 때, 1이
CREATE OR REPLACE FUNCTION FN_POW(A NUMBER, B NUMBER)
RETURN NUMBER
IS
-- 선언부
RESULT NUMBER := 1;
N NUMBER := 1;
BEGIN
-- 실행부
IF (B > 1)
THEN
LOOP
RESULT := RESULT * A;
N := N + 1;
EXIT WHEN N > B;
END LOOP;
ELSIF (B < 0)
THEN
LOOP
RESULT := RESULT / A;
N := N + 1;
EXIT WHEN N > B;
END LOOP;
END IF;
IF (B = 0)
THEN RESULT := 1;
N := B;
ELSIF (B = 1)
THEN RESULT := A;
N := B;
END IF;
RETURN RESULT;
END;
/*
FN_POW(10, 3) → 1000
기준 1 * 10 * 10 * 10 → 1000
기준 0 * 10 * 10 * 10 → 0
*/
--[선생님 풀이!! : FOR LOOP 를 이용한 풀이]***************************************
-- IF 문을 통해 구성하지 않고도, 값이 제대로 출력됨을 확인 가능!!
CREATE OR REPLACE FUNCTION FN_POW2(A NUMBER, B NUMBER)
RETURN NUMBER
IS
V_RESULT NUMBER := 1; -- 반환 결과값 변수 → 1로 초기화~!!! CHECK~!!!
V_NUM NUMBER; -- 루프변수
BEGIN
-- 반복문 구성
FOR V_NUM IN 1 .. B LOOP
V_RESULT := V_RESULT * A; -- [JAVA] : V_RESULT *= A;
END LOOP;
-- 최종 결과값 반환
RETURN V_RESULT;
END;
--○ TBL_INSA 테이블의 급여 계산 전용 함수를 정의한다.
-- 급여는 『(기본급*12) + 수당』 기반으로 연산을 수행한다.
-- 함수명 : FN_PAY(기본급, 수당)
CREATE OR REPLACE FUNCTION FN_PAY(A NUMBER, B NUMBER)
RETURN NUMBER
IS
-- 선언부
RESULT NUMBER := 0;
BEGIN
-- 실행부
RESULT := A * 12 + B;
RETURN RESULT;
END;
--[선생님 풀이!!]***************************************************************
CREATE OR REPLACE FUNCTION FN_PAY2(VBASICPAY NUMBER, VSUDANG NUMBER)
RETURN NUMBER
IS
-- 주요 변수 선언
VRESULT NUMBER;
BEGIN
-- 연산 및 처리
--★ 항상 NULL 값이 들어올 수 있음을 예상하고 함수를 정의하도록 한다!!
-- 만약, 값이 둘 중 하나라도 NULL 일 경우 제대로 된 처리가 이루어지지 않는다는 것을 생각하지 못했다...
VRESULT := (NVL(VBASICPAY, 0) * 12) + NVL(VSUDANG, 0);
-- 최종 결과값 반환
RETURN VRESULT;
END;
--○ TBL_INSA 테이블에서
-- 입사일을 기준으로 현재까지의 근무년수를 반환하는 함수를 정의한다.
-- 단, 근무년수는 소수점 이하 한자리까지 계산한다.
-- 함수명 : FN_WORKYEAR(입사일)
CREATE OR REPLACE FUNCTION FN_WORKYEAR(IBSADATE DATE)
RETURN VARCHAR2
IS
-- 선언부
YEAR NUMBER;
MONTH NUMBER;
CHAR_RESULT VARCHAR2(100);
BEGIN
YEAR := TRUNC((MONTHS_BETWEEN(SYSDATE, IBSADATE) / 12));
MONTH := TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, IBSADATE) , 12));
CHAR_RESULT := YEAR || '년' || MONTH || '개월';
RETURN CHAR_RESULT;
END;
SELECT FN_WORKYEAR(IBSADATE)
, TRUNC(FN_WORKYEAR(IBSADATE)) "년"
, FN_WORKYEAR(IBSADATE) - TRUNC(FN_WORKYEAR(IBSADATE)) "남은것"
, ROUND((FN_WORKYEAR(IBSADATE) - TRUNC(FN_WORKYEAR(IBSADATE))) * 12) "개월"
, IBSADATE
, SYSDATE
, MOD(TRUNC((MONTHS_BETWEEN(SYSDATE, IBSADATE) / 12)))
FROM TBL_INSA;
SELECT 12 / 9
FROM DUAL;
-- 1.
SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('1998-10-11', 'YYYY-MM-DD')) / 12 "RESULT"
FROM DUAL;
--==>> 25.06315891328156113102349661489446435683
-- 2.
SELECT TRUNC( MONTHS_BETWEEN(SYSDATE,TO_DATE('1998-10-11', 'YYYY-MM-DD')) / 12) || '년' ||
TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, TO_DATE('1998-10-11', 'YYYY-MM-DD')), 12)) || '개월'
FROM DUAL;
--==>> 25년0개월
CREATE OR REPLACE FUNCTION FN_WORKYEAR2(VIBSADATE DATE)
RETURN VARCHAR2
IS
VRESULT VARCHAR2(20);
BEGIN
VRESULT := TRUNC(MONTHS_BETWEEN(SYSDATE, VIBSADATE) / 12) || '년' ||
TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, VIBSADATE), 12)) || '개월';
RETURN VRESULT;
END;
--(* TRUNCATE 레코드를 잘라내서 삭제해 버리는 구문)
--------------------------------------------------------------------------------
--※ 참고
-- 1. INSERT, UPDATE, DELETE, (MERGE)
--==>> DML(Data Manipulation Language)
-- COMMIT / ROLLBACK 이 필요하다.
-- 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문 사용 가능하다.
--------------------------------------------------------------------------------
-- 프로시저 : 리턴 자료형이 VOID인 메소드와 비슷하다.
-- 매개변수를 받지 않더라도 호출한 자리에 연산 결과값을 두고 간다.
-- 내부적인 처리 일어난 후...
-- 오라클에서 정의하는 함수는 RETURN 자료형이 없는 함수가 없다.
-- 입력 파라미터, 출력용 파라미터, 입출력 파라미터를 다 쓸 수 있도록 구성.
-- 함수 내부에서 절차적으로 진행하는 것이 중요하므로 프로시저 저으이.
-- 절차적으로 진행하지 않으면 문제가 발생할 수 있으므로 안에다 묶어 놓는 개념...
--■■■ PROCEDURE(프로시저) ■■■--
-- 1. PL/SQL 에서 가장 대표적인 구조인 스토어드 프로시저는
-- 개발자가 자주 작성해야 하는 업무의 흐름을
-- 미리 작성하여 데이터베이스 내에 저장해 두었다가
-- 필요할 떄마다 호출하여 실행할 수 있도록 처리해 주는 구문이다.
-- 2. 형식 및 구조
/*
CREATE [OR REPLACE] PROCEDURE 프로시저명
[( 매개변수 IN 데이터타입
, 매개변수 OUT 데이터타입
, 매개변수 INOUT 데이터타입
)]
IS
[-- 주요 변수 선언]
BEGIN
-- 실행 구문;
...
[EXCEPTION
-- 예외 처리 구문;]
END;
*/
--※ FUNCTION 과 비교했을 때 『RETURN 반환자료형』 부분이 존재하지 않으며,
-- 『RETURN』문 자체도 존재하지 않고,
-- 프로시저 실행 시 넘겨주게 되는 매개변수의 종류는
-- IN(입력), OUT(출력), INOUT(입출력)으로 구분된다.
-- 3. 실행(호출)
/*
EXE[CUTE] 프로시저명[(인수1, 인수2, ...)];
*/
SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2000-09-07', 'YYYY-MM-DD')) / 12
FROM DUAL;
--※ 프로시저 실습을 위한 테이블 생성은...
-- 『20231103_02_scott.sql』 파일 참조~!!!
--○ 프로시저 생성
-- 프로시저명 : PRC_STUDENT_INSERT(아이디, 패스워드, 이름, 전화, 주소)
CREATE OR REPLACE PROCEDURE PRC_STUDENT_INSERT
( 아이디
, 패스워드
, 이름
, 전화
, 주소
)
IS
BEGIN
END;
DESC TBL_IDPW;
DESC TBL_STUDENTS;
-- 모두 다 입력받아서 테이블에 값을 INSERT TO 해 주려는 목적이므로
-- 모든 변수는 'IN'이다.
CREATE OR REPLACE PROCEDURE PRC_STUDENT_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
INSERT INTO TBL_IDPW(ID, PW)
VALUES(V_ID, V_PW);
-- TBL_STUDENTS 테이블에 데이터 입력 → INSERT
INSERT INTO TBL_STUDENTS(ID, NAME, TEL, ADDR)
VALUES(V_ID, V_NAME, V_TEL, V_ADDR);
-- 커밋 (프로시저 내부에서 테이블에 값을 입력하므로 커밋이 안에 들어가 주어야 함.)
COMMIT;
END;
--==>> Procedure PRC_STUDENT_INSERT이(가) 컴파일되었습니다.
--※ 프로시저 실습을 위한 테이블 생성은...
-- 『20231103_02_scott.sql』 파일 참조~!!!
--○ 데이터 입력 시 특정 항목의 데이터만 입력하면
-- 내부적으로 다른 추가항목에 대한 처리가 함께 이루어질 수 있도록 하는
-- 프로시저를 작성한다.(생성한다.)
-- 프로시저명 : PRC_SUNGJUK_INSERT()
/*
문제인식)
현재 TBL_SUNGJUK 테이블은
HAKBUN, NAME, KOR, ENG, MAT, TOT, AVG, GRADE
(학번, 이름, 국어점수, 영어점수, 수학점수, 총점, 평균, 등급)
컬럼으로 구성되어 있다.
이 테이블을 대상으로 특정 항목(학번, 이름, 국어점수, 영어점수, 수학점수)만 입력하면
추가 항목(총점, 평균, 등급)은 알아서 처리될 수 있도록 프로시저를 구성하라는 것이다.
실행 예)
EXEC PRC_SUNGJUK_INSERT(1, '김다슬', 90, 80, 70);
→ 이와 같은 프로시저 호출로 처리된 결과
학번 이름 국어점수 영어점수 수학점수 총점 평균 등급
1 김다슬 90 80 70 240 80 B
*/
DESC TBL_SUNGJUK;
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
V_TOT TBL_SUNGJUK.TOT%TYPE := 0;
V_AVG TBL_SUNGJUK.AVG%TYPE := 0;
V_GRADE TBL_SUNGJUK.GRADE%TYPE;
BEGIN
-- 2. 총점 연산
V_TOT := V_KOR + V_ENG + V_MAT;
-- 3. 평균 연산
V_AVG := (V_TOT / 3);
-- 4. 등급 연산
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;
-- 5. 계산한 값들 INSERT INTO
-- HAKBUN, NAME, KOR, ENG, MAT, TOT, AVG, GRADE
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;
-- 내가 못했던거 다시 풀어보기!!!!!!!!!!!!! 꼭!!!!!!!!!!!!!!!!!!
-- 입력받은 데이터 동전 분류하기
-- 날짜 계산해서 입사일로부터 현재날짜까지 몇 년 몇 개월 지났는지 구하기
--[선생님 풀이!!!!]=============================================
CREATE OR REPLACE PROCEDURE PRC_SUNGJUK_INSERT2
( 학번
, 성명
, 국어점수
, 영어점수
, 수학점수
)
IS
BEGIN
END;
CREATE OR REPLACE PROCEDURE PRC_SUNGJUK_INSERT2
( 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 쿼리문을 수행하기 위해 필요한 추가 변수 구성
-- 매개변수로 받는 변수가 아니기 떄문에, IN, OUT, INOUT 작성하지 않는다!!!
-- 아래 선언부에서는 각각 변수 선언 부분이 끝날 때마다 세미콜론을 작성해 주어야 한다.
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문의 조건식이 괄호로 감싸져있지 않아도 문법적으로 문제가 되지 않는다.
-- 하지만, 괄호를 씌워주는 편이 더욱 가독성이 쉬워 문제를 풀 때 도움이 되므로 그렇게 구성하자.
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_INSERT2이(가) 컴파일되었습니다.
--○ TBL_SUNGJUK 테이블에서 특정 학생의 점수
-- (학번, 국어점수, 영어점수, 수학점수) 데이터 수정 시
-- 총점, 평균, 등급까지 함께 수정되는 프로시저를 생성한다.
-- 프로시저명 : PRC_SUNGJUK_UPDATE()
/*
실행 예)
EXEC PRC_SUNGJUK_UPDATE(2, 50, 50, 50);
→ 프로시저 호출로 처리된 결과
학번 이름 국어점수 영어점수 수학점수 총점 평균 등급
1 김다슬 90 80 70 240 80 B
2 노은하 50 50 50 150 50 F
HAKBUN, NAME, KOR, ENG, MAT, TOT, AVG, GRADE
(학번, 이름, 국어점수, 영어점수, 수학점수, 총점, 평균, 등급)
*/
CREATE OR REPLACE PROCEDURE PRC_SUNGJUK_UPDATE
( V_HAKBUN IN TBL_SUNGJUK.HAKBUN%TYPE
, V_NKOR IN TBL_SUNGJUK.KOR%TYPE
, V_NENG IN TBL_SUNGJUK.ENG%TYPE
, V_NMAT IN TBL_SUNGJUK.MAT%TYPE
)
IS
V_NAME TBL_SUNGJUK.NAME%TYPE;
V_NEWTOT TBL_SUNGJUK.TOT%TYPE := 0;
V_NEWAVG TBL_SUNGJUK.AVG%TYPE := 0;
V_NEWGRADE TBL_SUNGJUK.GRADE%TYPE;
BEGIN
-- #1 총점(NEW)
V_NEWTOT := V_NKOR + V_NENG + V_NMAT;
-- #2 평균(NEW)
V_NEWAVG := V_NEWTOT / 3;
-- #3 등급(NEW)
IF (V_NEWAVG >= 90)
THEN
V_NEWGRADE := 'A';
ELSIF (V_NEWAVG >= 90)
THEN
V_NEWGRADE := 'B';
ELSIF (V_NEWAVG >= 80)
THEN
V_NEWGRADE := 'C';
ELSIF (V_NEWAVG >= 60)
THEN
V_NEWGRADE := 'D';
ELSE
V_NEWGRADE := 'F';
END IF;
--HAKBUN, NAME, KOR, ENG, MAT, TOT, AVG, GRADE
UPDATE TBL_SUNGJUK
SET KOR = V_NKOR,
ENG = V_NENG,
MAT = V_NMAT,
TOT = V_NEWTOT,
AVG = V_NEWAVG,
GRADE = V_NEWGRADE
WHERE HAKBUN = V_HAKBUN;
COMMIT;
END;
CREATE OR REPLACE PROCEDURE PRC_SUNGJUK_UPDATE
( 학번
, 국어점수
, 영어점수
, 수학점수
)
IS
BEGIN
END;
--[선생님 풀이!!!!!!]
CREATE OR REPLACE PROCEDURE PRC_SUNGJUK_UPDATE2
( V_HAKBUN 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
-- 선언부
-- UPDATE 쿼리문을 수행하기 위해 필요한 추가 변수 구성
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 쿼리문 수행
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_HAKBUN;
-- 커밋
COMMIT;
END;
--==>> Procedure PRC_SUNGJUK_UPDATE이(가) 컴파일되었습니다.
--○ TBL_STUDENTS 테이블에서 전화번호와 주소 데이터를 수정하는(변경하는)
-- 프로시저를 작성한다.
-- 단, ID 와 PW 가 일치하는 경우에만 수정을 진행할 수 있도록 처리한다.
-- 프로시저명 : PRC_STUDENT_UPDATE()
/*
실행 예)
EXEC PRC_STUDENT_UPDATE('moon', 'java000', '010-9999-9999', '강원도 횡성');
--==>> 데이터 수정 Ⅹ
EXEC PRC_STUDENT_UPDATE('moon', 'java006$', '010-9999-9999', '강원도 횡성');
--==>> 데이터 수정 ○
*/
DESC TBL_IDPW;
DESC TBL_STUDENTS;
CREATE OR REPLACE PROCEDURE PRC_STUDENT_UPDATE
( V_ID IN TBL_IDPW.ID%TYPE
, V_PW IN TBL_IDPW.PW%TYPE
, V_TEL IN TBL_STUDENTS.TEL%TYPE
, V_ADDR IN TBL_STUDENTS.ADDR%TYPE
)
IS
BEGIN
--★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
--★[INNER JOIN과 UPDATE를 이용한 방식]
UPDATE
(
SELECT TS.ADDR, TS.TEL, IP.PW, IP.ID
FROM TBL_STUDENTS TS
INNER JOIN
TBL_IDPW IP
ON TS.ID = IP.ID
)
SET TEL = V_TEL, ADDR = V_ADDR
WHERE ID = V_ID
AND
PW = V_PW;
COMMIT;
--★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
--★[WHERE 절의 중첩 서브쿼리를 이용한 방식]
-- UPDATE TBL_STUDENTS
-- SET TEL = V_TEL, ADDR = V_ADDR
-- WHERE ID = V_ID
-- AND
-- V_PW
-- IN
-- (
-- SELECT PW
-- FROM TBL_IDPW
-- WHERE ID = V_ID
-- );
END;