-- ■■■■ PROCEDURE(프로시저) ■■■■
-- 1. PL/SQL 에서 가장 대표적인 구조인 스토어드 프로시저는
-- 개발자가 자주 작성해야 하는 업무의 흐름을
-- 미리 작성하여 데이터베이스 내에 저장해 두었다가
-- 필요할 때 마다 호출하여 실행할 수 있도록 처리해주는 구문이다.
2. 형식 및 구조
CREATE [OR REPLACE] PROCEDURE 프로시저명
[(매개변수 IN 데이터타입
, 매개변수 OUT 데이터타입
, 매개변수 INOUT 데이터타입
)]
IS
[주요 변수 선언;]
BEGIN
-- 실행구문;
...
[EXCEPTION
-- 예외 처리 구문;]
END;
*/
-- ※ FUNCTION 과 비교했을 때,
-- 『RETURN』 반환자료형 부분이 존재하지 않으며,
-- 『RETURN』 문 자체도 존재하지 않으며,
-- 프로시저 실행 시 넘겨주게 되는 매개변수의 종류는
-- IN, OUT, INOUT 으로 구분된다.
--3. 실행(호출)
/*
EXEC[UTE] 프로시저명[(인수1, 인수2)]
*/
-- ※ 프로시저 실습 진행을 위한 테이블 생성 및 데이터 입력
-- ● INSERT 쿼리 실행을 프로시저로 작성(INSERT 프로시저)
-- 실습 테이블 생성(TBL_STUDENTS)
CREATE TABLE TBL_STUDENTS
(ID VARCHAR2(10)
,NAME VARCHAR2(40)
,TEL VARCHAR2(20)
,ADDR VARCHAR2(100)
);
--==>> Table TBL_STUDENTS이(가) 생성되었습니다.
-- 실습 테이블 생성(TBL_IDPW)
CREATE TABLE TBL_IDPW
(ID VARCHAR2(10)
,PW VARCHAR2(20)
,CONSTRAINT INPW_ID_PK PRIMARY KEY(ID)
);
--==>>Table TBL_IDPW이(가) 생성되었습니다.
-- 한 명의 학생 정보 등록 두 테이블에 데이터 입력
INSERT INTO TBL_STUDENTS(ID,NAME,TEL,ADDR)
VALUES('superman','박정준','010-1111-1111','제주도 서귀포시');
INSERT INTO TBL_IDPW(ID,PW)
VALUES('superman','java006$');
SELECT *
FROM TBL_STUDENTS;
SELECT *
FROM TBL_IDPW;
-- 위의 업무를 프로시저(INSERT프로시저, 입력 프로시저)를 생성하게 되면
EXEC PRC_STUDENTS_INSERT('happyday','java006$','김서현','010-2222-2222','서울 마포구')
-- 이와 같은 구문 한 줄로 양쪽 테이블에 데이터를 모두 제대로 입력할 수 있다.
-- ● INSERT 쿼리 실행을 프로시저로 작성(INSERT 프로시저)
-- 실습 테이블 생성(TBL_STUDENTS) ▶ 20210408_04_SCOTT.SQL 참고
-- 실습 테이블 생성(TBL_IDPW) ▶ 20210408_04_SCOTT.SQL 참고
-- 프로시저 생성
-- 프로시저명 : PRC_STUDENTS_INSERT(아이디, 패스워드, 이름, 전화번호, 주소)
CREATE OR REPLACE PROCEDURE PRC_STUDENTS_INSERT
()
IS
BEGIN
END;
----------------
CREATE OR REPLACE PROCEDURE PRC_STUDENTS_INSERT
( V_ID IN TBL_STUDENTS.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이(가) 컴파일되었습니다.
-- ● 생성한 프로시저(PRC_STUDENTS_INSERT)가 제대로 작동하는지의 여부 확인
--
EXEC PRC_STUDENTS_INSERT('happyday','java006$','김서현','010-2222-2222','서울 마포구');
--==>PL/SQL 프로시저가 성공적으로 완료되었습니다.
/*
superman 박정준 010-1111-1111 제주도 서귀포시
happyday 김서현 010-2222-2222 서울 마포구
superman java006$
happyday java006$
두 테이블 모두에 '김서현'데이터가 잘 들어가있다.
*/
실습을 위한 테이블 생성
-- ● 학번, 이름, 국어점수ㅡ 영어점수 수학점수 데이터 입력받는 테이블 생성
CREATE TABLE TBL_SUNGJUK
(HAKBUN NUMBER
,NAME VARCHAR2(40)
,KOR NUMBER(3)
,ENG NUMBER(3)
,MAT NUMBER(3)
,CONSTRAINT SUNGJUK_HAKBUN_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 NUMBER
NAME VARCHAR2(40)
KOR NUMBER(3)
ENG NUMBER(3)
MAT NUMBER(3)
TOT NUMBER(3)
AVG NUMBER(4,1)
GRADE CHAR(1)
*/
테이블을 토대로 프로시저 구성
-- ● 데이터입력 시
-- 특정 항목의 데이터(학번, 이름, 국어점수,영어점수, 수학점수)만 입력하면
-- 총점, 평균, 등급 항목이 함께 입력 처리될 수 있는 프로시저를 작성한다(생성한다.)
-- 프로시저명 : PRC_SUNGJUK_INSERT()
/*
실행 예
EXEC PRC_SUNGJUK_INSERT(1, '조은선',90,80,70);
프로시저 호출로 처리된 결과)
학번 이름 국어점수 영어점수 수학점수 총점 평균 등급
1 조은선 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
V_TOT NUMBER; --IN TBL_SUNGJUK.TOT%TYPE;
V_AVG NUMBER; --IN TBL_SUNGJUK.AVG%TYPE;
V_GRADE CHAR;--IN TBL_SUNGJUK.GRADE%TYPE;
BEGIN
-- 총점, 평균, 등급 구하기
V_TOT:= V_KOR + V_ENG + V_MAT;
V_AVG:= (V_TOT)/3;
--V_GRADE:= DECODE(TRUNC(V_AVG,-1),90,'A',80,'B',70,'C',60,'D','F');
V_GRADE:= CASE TRUNC(V_AVG,-1) WHEN 100 THEN 'A'
WHEN 90 THEN 'A'
WHEN 80 THEN 'B'
WHEN 70 THEN 'C'
WHEN 60 THEN 'D'
ELSE 'F'
END;
-- 테이블에 데이터 삽입
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);
END;
--==>>Procedure PRC_SUNGJUK_INSERT이(가) 컴파일되었습니다.
위에서 만든 PRC_SUNGJUK_INSERT 의 생성 방식과 다른 방식
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;
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;
--V_GRADE:= DECODE(TRUNC(V_AVG,-1),90,'A',80,'B',70,'C',60,'D','F');
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 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;
INSERT INTO TABLE명 이후에는 꼭 COMMIT 해주기!
SELECT TRUNC(69,-1)
FROM DUAL;
--==>> 성적 범위 잡으려고 69 점 60 점으로 만들어주려고 실험해본거
-- 생성한 프로시저(PRC_SUNGJUK_INSERT) 가 제대로 작동하는지의 여부 확인
-- → 프로시저 호출
EXEC PRC_SUNGJUK_INSERT(1, '조은선',90,80,70);
EXEC PRC_SUNGJUK_INSERT(2, '다람이',100,100,100);
EXEC PRC_SUNGJUK_INSERT(3, '징징이',50,40,20);
EXEC PRC_SUNGJUK_INSERT(4, '이상해',90,80,70);
SELECT *
FROM TBL_SUNGJUK;
/*
HAKBUN NAME KOR ENG MAT TOT AVG GRADE
1 조은선 50 50 50 150 50 F
2 다람이 100 100 100 300 100 A
3 징징이 50 40 20 110 36.7 F
4 이상해 90 80 70 240 80 B
*/
-- ● TBL_SUNGJUK 테이블에서
-- 특정 학생의 점수(학번, 국어점수, 영어점수, 수학점수)
-- 데이터 수정 시 총점, 평균 , 등급까지 수정하는 프로시저를 작성한다.
-- 프로시저명 : PRC_SUNGJUK_UPDATE
/*
실행 예)
EXEC PRC_SUNGJUK_UPDATE(1,50,50,50);
호출로 처리된 결과
학번 이름 국어점수 영어점수 수학점수 총점 평균 등급
1 이상해 50 50 50 150 50 F
*/
CREATE OR REPLACE PROCEDURE PRC_SUNGJUK_UPDATE
(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
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_HAKBUN;
-- 업데이트 결과 COMMIT 하기
COMMIT;
END;
--===>>Procedure PRC_SUNGJUK_UPDATE이(가) 컴파일되었습니다.
-- 생성한 프로시저(PRC_SUNGJUK_UPDATE) 가 제대로 작동하는지의 여부 확인
-- → 프로시저 호출
EXEC PRC_SUNGJUK_UPDATE(1,50,50,50);
SELECT *
FROM TBL_SUNGJUK;
/*1 조은선 50 50 50 150 50 F
--==>> 기존 90 80 70 점수에서 50 50 50 으로 바뀌고 그에따라 총점,평균,등급도 변경되었다.
*/
---● TBL_STUDENTS 테이블에서
-- 전화번호와 주소 데이터를 수정하는 프로시저를 작성한다.
-- 단, ID와 PW 가 일치하는 경우에는 수정을 진행할 수 있도록 한다.
-- 프로시저 명 : PRC_STUDENTS_UPDATE
/*
실행 예)
EXEC PRC_STUDENTS_UPDATE('superman','java006$','010-9999-9999','경기 일산');
프로시저 호출로 처리된 결과
superman 박정준 010-9999-9999 경기 일산
*/
CREATE OR REPLACE PROCEDURE PRC_STUDENTS_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.TEL%TYPE
)
IS
-- 변수 선언 : 따로 할 것이 ,,, 없다!
BEGIN
-- 실행 연산 : "아이디 비밀번호가 TBL_IDPW 와 일치한다면" TBL_STUDENTS를 업데이트 한후 COMMIT 한다.
UPDATE TBL_STUDENTS
SET TEL = V_TEL, ADDR=V_ADDR
WHERE ID=V_ID AND (SELECT PW
FROM TBL_IDPW
WHERE ID=V_ID)=V_PW;
COMMIT;
END;
--==>>Procedure PRC_STUDENTS_UPDATE이(가) 컴파일되었습니다.
EXEC PRC_STUDENTS_UPDATE('superman','java006$','010-9999-9999','경기 일산');
--==>>PL/SQL 프로시저가 성공적으로 완료되었습니다.
SELECT *
FROM TBL_STUDENTS;
/*
superman 박정준 010-9999-9999 경기 일산
happyday 김서현 010-2222-2222 서울 마포구
*/
--만약 다른 PW를 입력한다면????????????
EXEC PRC_STUDENTS_UPDATE('superman','javajava','010-9999-9999','경기 일산');
--==>>PL/SQL 프로시저가 성공적으로 완료되었습니다.
/*
superman 박정준 010-9999-9999 경기 일산
happyday 김서현 010-2222-2222 서울 마포구
*/
-- 데이터가 변경되지 않았다.
근속년수 구하기
MONTHS_BETWEEN 활용하기
예를 들어 2000년 11월에 입사했으면 현재 21년 4월을 기준으로 약 20.4년을 근무했다.
소수점 한 자리까지 나타내기 위해서
변수의 타입을 NUMBER(3,1)로 지정해주거나
TO_CHAR(□ , '999.9') 로 지정
SELECT MONTHS_BETWEEN(SYSDATE,TO_DATE('2000-11-12','YYYY-MM-DD'))/12
FROM DUAL;
--20.4112047926622859418558343289526085225
SELECT TO_CHAR(MONTHS_BETWEEN(SYSDATE,TO_DATE('2000-11-12','YYYY-MM-DD'))/12,'999.9')
FROM DUAL;
-- 20.4'📚Study Note > ORACLE' 카테고리의 다른 글
| [ ORACLE ] PROCEDURE 내에서 예외처리 (0) | 2021.04.12 |
|---|---|
| [ ORACLE ] DML │ DDL │ DCL │ TCL │ 정적/동적PLSQL (0) | 2021.04.09 |
| [ ORACLE ] CREATE OR REPLACE FUNCTION 함수명() (0) | 2021.04.09 |
| [ ORACLE ] 자료형 참조하는 %TYPE 퍼센트타입 │ %ROWTYPE 로우퍼센트타입 (0) | 2021.04.09 |
| [ ORACLE ] 반복문 3가지 │ LOOP │ WHILE LOOP │ FOR LOOP (0) | 2021.04.09 |