✿∘˚˳°∘°
23일차 : PL/SQL 본문
2022/12/28
[ 1. PL/SQL ]
: Procedual Language extention to SQL
오라클 자체에 내장되어있는 절차적언어
SQL의 단점을 보완하여 SQL문장내에서 변수정의, 조건처리, 반복처리등을 지원
SET SERVEROUTPUT ON; --접속시마다 해줘야함
-- < 기본구조 >
DECLARE
BEGIN
DBMS_OUTPUT.PUT_LINE('HELLO ORACLE');
END;
/
1 - 1 ) PL/SQL 유형
1. 익명블록 (Anonymous Block)
: 이름없는 블록(블록:SQL코드의 덩어리), 간단한 로직을 처리할 때 사용(일회성작업을 모아서하려고)
2. 프로시저(Procedure)
: 지정된 특정 처리를 실행하는 서브프로그램, 익명블록을 저장해놓고 사용
(JAVA로 따지면 리턴타입이 없는 메소드)
3. 함수(Function)
: 프로시저와 거의동일, 차이점은 반환값이 반드시 존재
( JAVA로 따지면 리턴타입이 있는 메소드)
< PL/SQL 기본구조 >
DECLARE -- 선언부[생략가능]
-- 필요한 변수나 상수를 선언
BEGIN -- 실행부
-- 필요한 기능을 작성(반복, 제어)
END; -- 블록종료
/ -- 실행
PL/SQL에서 변수에 값을 대입하는 대입연산자는 :=
DECLARE
TEST_NO NUMBER; -- 선언방법 : 변수명 자료형
TEST_STR VARCHAR2(30);
BEGIN
TEST_NO := 100; -- 변수에 값 대입
TEST_STR := 'HELLO'; -- 변수에 값 대입
DBMS_OUTPUT.PUT_LINE('TEST_NO : ' || TEST_NO);
DBMS_OUTPUT.PUT_LINE('TEST_STR : ' || TEST_STR);
END;
/
-- EMPLOYEE테이블의 사번이 200번인 직원의 이름, 급여 출력
SELECT EMP_NAME, SALARY FROM EMPLOYEE WHERE EMP_ID = 200;
DECLARE
E_NAME VARCHAR2(20);
SAL NUMBER;
BEGIN
SELECT EMP_NAME, SALARY
-- SELECT와 FROM 사이에 결과를 넣을 변수를 작성
-- SELECT로 나온 결과가 순서에 맞춰서 대입된다
INTO E_NAME, SAL
FROM EMPLOYEE
WHERE EMP_ID = '&사원번호'; -- 스캐너역할, 주석에서도 동작
DBMS_OUTPUT.PUT_LINE('이름 : '||E_NAME);
DBMS_OUTPUT.PUT_LINE('급여 : '||SAL);
END;
/
-- PL/SQL 작성
-- 사원번호를 입력하면 이름, 부서명, 직급명이 출력
-- 부서가 없는 사원은 부서없음으로 출력되도록
DECLARE
E_NAME VARCHAR2(20);
D_TITLE VARCHAR2(35);
J_NAME VARCHAR2(35);
BEGIN
SELECT EMP_NAME, NVL(DEPT_TITLE, '부서없음'), JOB_NAME
INTO E_NAME, D_TITLE, J_NAME
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN JOB USING(JOB_CODE)
WHERE EMP_ID = '&사원번호';
DBMS_OUTPUT.PUT_LINE('이름 : '||E_NAME);
DBMS_OUTPUT.PUT_LINE('부서명 : '||D_TITLE);
DBMS_OUTPUT.PUT_LINE('직급명 : '||J_NAME);
END;
/
1 - 2 ) PL/SQL 변수의 종류
1. 일반변수(스칼라변수)
: SQL의 기본자료형과 유사한타입
2. 상수형변수
: 일반변수와 유사한 형태이지만 CONSTANT라는 키워드가 붙음
: 변수선언 시 값을 대입해야하며, 한번대입된 값은 변경불가능
-- 상수형 변수
DECLARE
USER_NAME CONSTANT VARCHAR2(20) := '테스트1'; -- 선언시 값대입
USER_NAME2 VARCHAR2(20);
BEGIN
-- USER_NAME := '테스트1'; 이러면 에러발생, 선언하면서 값을 넣어줘야한다 / 값수정X
USER_NAME2 := '테스트2';
DBMS_OUTPUT.PUT_LINE('USER_NAME : '||USER_NAME);
DBMS_OUTPUT.PUT_LINE('USER_NAME2 : '||USER_NAME2);
END;
3. 참조형변수(%TYPE)
: 이전에 선언된 다른 변수 또는 테이블의 자료형과 동일한 타입의 변수
: EMPLOYEE에 있는 EMAIL자료형을 가지고싶어 - 이것만 가져옴
-- 사번을 입력받아 이름, 이메일, 급여를 출력하는 PL/SQL
DECLARE
-- E_NAME VARCHAR2(20); 이렇게 선언하는 경우 자료형사이즈가 값보다 작으면 에러가 발생
E_NAME EMPLOYEE.EMP_NAME%TYPE;
MAIL EMPLOYEE.EMAIL%TYPE; -- EMPLOYEE테이블의EMAIL컬럼의 자료형을 가져와라
SAL EMPLOYEE.SALARY%TYPE;
BEGIN
SELECT EMP_NAME, EMAIL, SALARY
INTO E_NAME, MAIL, SAL
FROM EMPLOYEE
WHERE EMP_ID = '&사번';
DBMS_OUTPUT.PUT_LINE('이름 : '||E_NAME);
DBMS_OUTPUT.PUT_LINE('이메일 : '||MAIL);
DBMS_OUTPUT.PUT_LINE('급여 : '||SAL);
END;
/
4. 참조형변수(%ROWTYPE)
: 참조할 테이블의 모든컬럼의 자료형을 한번에 가지고옴
: EMPLOYEE에 있는 EMAIL자료형을 가지고싶어 - 전부다 가져옴
-- 사번을 입력받아 사번, 이름, 주민등록번호, 이메일, 부서코드, 직급코드, 급여 출력
/*
E_ID EMPLOYEE.EMP_ID%TYPE;
E_NAME EMPLOYEE.EMP_NAME%TYPE;
E_NO EMPLOYEE.EMP_NO%TYPE;
E_EMAIL EMPLOYEE.EMAIL%TYPE;
E_DCODE EMPLYOEE.DEPT_CODE%TYPE;
E_JCODE EMPLOYEE.JOB_CODE%TYPE;
E_SAL EMPLOYEE.SALARY%TYPE;
H_DATE EMPLYOEE.HIRE_DATE%TYPE;
이렇게 일일히 선언해주려면 너무힘듬
*/
DECLARE
-- ROWTYPE으로 선언시
-- EMPLOYEE테이블 내부의 모든 컬럼과 동일한 이름으로 변수가 생성
-- 변수명.컬럼명으로 사용
EMP EMPLOYEE%ROWTYPE;
BEGIN
SELECT EMP_ID, EMP_NAME, EMP_NO, EMAIL, DEPT_CODE, JOB_CODE, SALARY, HIRE_DATE
INTO EMP.EMP_ID, EMP.EMP_NAME, EMP.EMP_NO, EMP.EMAIL, EMP.DEPT_CODE,
EMP.JOB_CODE, EMP.SALARY, EMP.HIRE_DATE
FROM EMPLOYEE
WHERE EMP_ID = '&사번';
DBMS_OUTPUT.PUT_LINE('사번 : '||EMP.EMP_ID);
DBMS_OUTPUT.PUT_LINE('이름 : '||EMP.EMP_NAME);
DBMS_OUTPUT.PUT_LINE('주민번호 : '||EMP.EMP_NO);
DBMS_OUTPUT.PUT_LINE('이메일 : '||EMP.EMAIL);
DBMS_OUTPUT.PUT_LINE('부서코드 : '||EMP.DEPT_CODE);
DBMS_OUTPUT.PUT_LINE('직급코드 : '||EMP.JOB_CODE);
DBMS_OUTPUT.PUT_LINE('급여 : '||EMP.SALARY);
DBMS_OUTPUT.PUT_LINE('입사일 : '||EMP.HIRE_DATE);
END;
/
5. RECORD타입
: ROWTYPE은 테이블의 컬럼을 모두 가져온다면 RECORED는 내가 지정한것만
: 사용자지정 ROWTYPE이라고 보면된다
-- 사번을 입력받아서 이름, 주민등록번호, 이메일, 급여 출력
DECLARE
TYPE MY_RECORD_TYPE IS RECORD(
E_NAME EMPLOYEE.EMP_NAME%TYPE,
E_NO EMPLOYEE.EMP_NO%TYPE,
MAIL EMPLOYEE.EMAIL%TYPE,
SAL EMPLOYEE.SALARY%TYPE
);
-- MY_RECORD_TYPE이라는 자료형을 만든 것
USERINFO MY_RECORD_TYPE;
BEGIN
SELECT EMP_NAME, EMP_NO, EMAIL, SALARY
INTO USERINFO -- RECODE타입은 필요한만큼만 생성해주기때문에 INTO가 좀더 편해진다
-- ROWTYPE도 전부다 출력할거면 이렇게 작성해도 되지만,
-- 위처럼 부분출력할 컬럼이 많은 경우 INTO가 길어져서 힘들다.
FROM EMPLOYEE
WHERE EMP_ID = '&사번';
DBMS_OUTPUT.PUT_LINE(' 이름 : '||USERINFO.E_NAME);
DBMS_OUTPUT.PUT_LINE(' 주민등록번호 : '||USERINFO.E_NO);
DBMS_OUTPUT.PUT_LINE(' 이메일 : '||USERINFO.MAIL);
DBMS_OUTPUT.PUT_LINE(' 급여 : '||USERINFO.SAL);
END;
/
-- 실습문제 --
--@실습문제1
--사번, 사원명, 직급코드, 급여를 담을수 있는 참조변수를 통해서 (%TYPE)
--송종기사원의 사번, 사원명, 직급코드,급여를 익명블럭을 통해 스크립트 출력하세요.
DECLARE
E_ID EMPLOYEE.EMP_ID%TYPE;
E_NAME EMPLOYEE.EMP_NAME%TYPE;
E_JCODE EMPLOYEE.JOB_CODE%TYPE;
SAL EMPLOYEE.SALARY%TYPE;
BEGIN
SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY
INTO E_ID, E_NAME, E_JCODE, SAL
FROM EMPLOYEE
WHERE EMP_NAME = '송종기';
DBMS_OUTPUT.PUT_LINE('사번 : '||E_ID);
DBMS_OUTPUT.PUT_LINE('사원명 : '||E_NAME);
DBMS_OUTPUT.PUT_LINE('직급코드 : '||E_JCODE);
DBMS_OUTPUT.PUT_LINE('급여 : '||SAL);
END;
/
--@실습문제2
-- 사번, 사원명, 부서명,직급명을 담을수 있는 참조변수를 통해서 (record)
-- 사원명을 검색하여 해당 사원의 사번, 사원명, 부서명,직급명을
-- 익명블럭을 통해 스크립트 출력하세요.
DECLARE
TYPE MY_R IS RECORD(
E_ID EMPLOYEE.EMP_ID%TYPE,
E_NAME EMPLOYEE.EMP_NAME%TYPE,
D_TITLE DEPARTMENT.DEPT_TITLE%TYPE,
J_NAME JOB.JOB_NAME%TYPE
);
USERINFO MY_R;
BEGIN
SELECT EMP_ID, EMP_NAME, NVL(DEPT_TITLE, '부서없음'), JOB_NAME
INTO USERINFO
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
JOIN JOB USING(JOB_CODE)
WHERE EMP_NAME = '&사원명';
DBMS_OUTPUT.PUT_LINE('사번 : '||USERINFO.E_ID);
DBMS_OUTPUT.PUT_LINE('사원명 : '||USERINFO.E_NAME);
DBMS_OUTPUT.PUT_LINE('부서명 : '||USERINFO.D_TITLE);
DBMS_OUTPUT.PUT_LINE('직급명 : '||USERINFO.J_NAME);
END;
/
--@실습문제3
-- 사원번호를 입력하여 해당 사원을 찾아(%ROWTYPE을 사용)
-- 사원명, 주민번호, 입사일, 부서명을
-- 익명블럭을 통해 스크립트 출력하세요.
DECLARE
EMP EMPLOYEE%ROWTYPE;
DEP DEPARTMENT%ROWTYPE;
BEGIN
SELECT EMP_NAME, EMP_NO, HIRE_DATE, NVL(DEPT_TITLE, '부서없음')
INTO EMP.EMP_NAME, EMP.EMP_NO, EMP.HIRE_DATE, DEP.DEPT_TITLE
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
WHERE EMP_ID = '&사원번호';
DBMS_OUTPUT.PUT_LINE('사원명 : '||EMP.EMP_NAME);
DBMS_OUTPUT.PUT_LINE('주민등록번호 : '||EMP.EMP_NO);
DBMS_OUTPUT.PUT_LINE('입사일 : '||EMP.HIRE_DATE);
DBMS_OUTPUT.PUT_LINE('부서명 : '||DEP.DEPT_TITLE);
END;
/
1 - 3 ) 조건문
IF문
IF(조건식)
THEN 조건일 참일때 수행할문장;
END IF;
-- 조건식에는 오라클의 모든 연산자를 사용할 수 있다.
-- 오라클의 IF문
-- 사원번호를 입력받아서 사번, 이름, 급여, 보너스율을 출력
-- 보너스를 받지않는 직원은 '보너스를 받지 않는 직원입니다.' 추가출력
DECLARE
E_ID EMPLOYEE.EMP_ID%TYPE;
E_NAME EMPLOYEE.EMP_NAME%TYPE;
SAL EMPLOYEE.SALARY%TYPE;
BO EMPLOYEE.BONUS%TYPE;
BEGIN
SELECT EMP_ID, EMP_NAME, SALARY, NVL(BONUS, 0)
INTO E_ID, E_NAME, SAL, BO
FROM EMPLOYEE WHERE EMP_ID= '&사번';
DBMS_OUTPUT.PUT_LINE('사번 : '||E_ID);
DBMS_OUTPUT.PUT_LINE('이름 : '||E_NAME);
DBMS_OUTPUT.PUT_LINE('급여 : '||SAL);
DBMS_OUTPUT.PUT_LINE('보너스 : '||BO*100||'%');
IF(BO = 0)
THEN DBMS_OUTPUT.PUT_LINE('보너스를 받지않는 직원입니다.');
END IF;
END;
/
IF ~ ELSE
DECLARE
E_ID EMPLOYEE.EMP_ID%TYPE;
E_NAME EMPLOYEE.EMP_NAME%TYPE;
SAL EMPLOYEE.SALARY%TYPE;
BO EMPLOYEE.BONUS%TYPE;
BEGIN
SELECT EMP_ID, EMP_NAME, SALARY, NVL(BONUS, 0)
INTO E_ID, E_NAME, SAL, BO
FROM EMPLOYEE WHERE EMP_ID= '&사번';
DBMS_OUTPUT.PUT_LINE('사번 : '||E_ID);
DBMS_OUTPUT.PUT_LINE('이름 : '||E_NAME);
DBMS_OUTPUT.PUT_LINE('급여 : '||SAL);
IF(BO = 0) -- 오라클의 모든연산자 사용가능
THEN DBMS_OUTPUT.PUT_LINE('보너스를 받지않는 직원입니다.');
ELSE DBMS_OUTPUT.PUT_LINE('보너스 : '||BO*100||'%');
END IF;
END;
/-- 사원번호를 입력받아서 사원명, 부서코드, 부서명 출력
-- 부서가 있으면 부서명 출력
-- 만약 부서가 없다면 '부서가 없는 사원입니다' 출력
DECLARE
E_NAME EMPLOYEE.EMP_NAME%TYPE;
E_DCODE EMPLOYEE.DEPT_CODE%TYPE;
D_TITLE DEPARTMENT.DEPT_TITLE%TYPE;
BEGIN
SELECT EMP_NAME, DEPT_CODE, DEPT_TITLE
INTO E_NAME, E_DCODE, D_TITLE
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
WHERE EMP_ID = '&사원번호';
DBMS_OUTPUT.PUT_LINE('사원명 : '||E_NAME);
DBMS_OUTPUT.PUT_LINE('부서코드 : '||E_DCODE);
IF(D_TITLE IS NULL)
THEN DBMS_OUTPUT.PUT_LINE('부서가 없는 사원입니다');
ELSE DBMS_OUTPUT.PUT_LINE('부서명 : '||D_TITLE);
END IF;
END;
/
-- 사원번호를 입력받아 사번, 이름, 부서코드, 직급코드 출력
-- 직급코드가 J1, J2이면 '임원진입니다' 아니면 '일반사원입니다 출력'
DECLARE
E_ID EMPLOYEE.EMP_ID%TYPE;
E_NAME EMPLOYEE.EMP_NAME%TYPE;
E_DCODE EMPLOYEE.DEPT_CODE%TYPE;
E_JCODE EMPLOYEE.JOB_CODE%TYPE;
BEGIN
SELECT EMP_ID, EMP_NAME, DEPT_CODE, JOB_CODE
INTO E_ID, E_NAME, E_DCODE, E_JCODE
FROM EMPLOYEE
WHERE EMP_ID = '&사번';
DBMS_OUTPUT.PUT_LINE('사번 : '||E_ID);
DBMS_OUTPUT.PUT_LINE('이름 : '||E_NAME);
DBMS_OUTPUT.PUT_LINE('부서코드 : '||E_DCODE);
DBMS_OUTPUT.PUT_LINE('직급코드 : '||E_JCODE);
IF(E_JCODE IN('J1', 'J2'))
THEN DBMS_OUTPUT.PUT_LINE('임원진입니다.');
ELSE DBMS_OUTPUT.PUT_LINE('일반사원입니다.');
END IF;
END;
/
DECLARE
E_ID EMPLOYEE.EMP_ID%TYPE;
E_NAME EMPLOYEE.EMP_NAME%TYPE;
E_DCODE EMPLOYEE.DEPT_CODE%TYPE;
E_JCODE EMPLOYEE.JOB_CODE%TYPE;
BEGIN
SELECT EMP_ID, EMP_NAME, DEPT_CODE, JOB_CODE
INTO E_ID, E_NAME, E_DCODE, E_JCODE
FROM EMPLOYEE
WHERE EMP_ID = '&사번';
DBMS_OUTPUT.PUT_LINE('사번 : '||E_ID);
DBMS_OUTPUT.PUT_LINE('이름 : '||E_NAME);
DBMS_OUTPUT.PUT_LINE('부서코드 : '||E_DCODE);
DBMS_OUTPUT.PUT_LINE('직급코드 : '||E_JCODE);
IF(E_JCODE = 'J1')
THEN
DBMS_OUTPUT.PUT_LINE('임원진입니다.');
ELSIF(E_JCODE = 'J2')
THEN
DBMS_OUTPUT.PUT_LINE('임원진입니다.');
ELSE
DBMS_OUTPUT.PUT_LINE('일반사원입니다.');
END IF;
END;
/
1 - 4 ) 반복문
-- 반복문1
-- JAVA의 WHILE()문과 비슷
DECLARE
I NUMBER := 1; --상수는아님
BEGIN
LOOP -- 반복시작지점
DBMS_OUTPUT.PUT_LINE('I : '||I);
I := I+1;
IF(I = 5)
THEN
EXIT; -- 반복종료코드
END IF;
END LOOP; -- 반복끝나는지점
END;
/
-- 반복문 2
-- JAVA의 FOR()문과 비슷
DECLARE
-- 딱히 선언하지않아도 됨
BEGIN
FOR I IN 1..5 LOOP -- 1~5까지 동작
DBMS_OUTPUT.PUT_LINE('I : '||I);
END LOOP;
END;
/
-- 반복문3
DECLARE
BEGIN
FOR I IN REVERSE 1..5 LOOP -- 5~1까지 동작
DBMS_OUTPUT.PUT_LINE('I : '||I);
END LOOP;
END;
/
SELECT * FROM EMPLOYEE;
-- 반복문을 이용하여 전직원의 사번, 이름, 급여, 입사일 출력
DECLARE
E_ID EMPLOYEE.EMP_ID%TYPE;
E_NAME EMPLOYEE.EMP_NAME%TYPE;
SAL EMPLOYEE.SALARY%TYPE;
H_DATE EMPLOYEE.HIRE_DATE%TYPE;
BEGIN
-- 전체 직원을 조회하고 싶을 때 변수는 4개이기때문에
-- 한번에 23명의 데이터를 넣을 수 없음
-- 반복문을 통해 한명씩 넣어줄 것
FOR I IN 0..22 LOOP
SELECT EMP_ID, EMP_NAME, SALARY, HIRE_DATE
INTO E_ID, E_NAME, SAL, H_DATE
FROM EMPLOYEE
WHERE EMP_ID = 200 + I;
DBMS_OUTPUT.PUT_LINE('사번 : '||E_ID);
DBMS_OUTPUT.PUT_LINE('이름 : '||E_NAME);
DBMS_OUTPUT.PUT_LINE('급여 : '||SAL);
DBMS_OUTPUT.PUT_LINE('입사일 : '||H_DATE);
DBMS_OUTPUT.PUT_LINE('-------------------------');
END LOOP;
END;
/
1 - 5 ) PROCEDURE
-- 회원정보를 저장하는 테이블 생성과 INSERT
-- 회원정보를 저장하는 MEMBER_TBL 생성
/*
회원번호 NUMBER PRIMARY KEY
아이디 VARCHAR2(30) UNIQUE NOT NULL
비밀번호 VARCHAR2(30) NOT NULL
이름 VARCHAR2(15) NOT NULL
나이 NUMBER 기본값 0
전화번호 CHAR(13) 기본값 '010-0000-0000'
가입일 DATE
*/
CREATE TABLE MEMBER_TBL(
MEMBER_NO NUMBER PRIMARY KEY,
MEMBER_ID VARCHAR2(30) UNIQUE NOT NULL,
MEMBER_PW VARCHAR2(30) NOT NULL,
MEMBER_NAME VARCHAR2(15) NOT NULL,
MEMBER_AGE NUMBER DEFAULT 0,
MEMBER_PHONE CHAR(13) DEFAULT '010-0000-0000',
JOIN_DATE DATE
);
CREATE SEQUENCE MEMBER_SEQ; -- 회원번호시퀀스
-- 기본값으로 생성됨 ( START 1, INCREMENT 1, 정수최대치까지)
-- 회원 30명 INSERT
DECLARE
BEGIN
FOR I IN 1..30 LOOP
INSERT INTO MEMBER_TBL VALUES
(MEMBER_SEQ.NEXTVAL, 'user'||I, 'PWD'||I, '유저'||I, 20+I,DEFAULT, SYSDATE);
END LOOP;
END;
/
-- 탈퇴할 회원의 정보를 저장하는 테이블
-- 탈퇴한 회원의 정보를 저장하는 테이블생성
CREATE TABLE DEL_MEMBER_TBL(
DEL_NO NUMBER PRIMARY KEY, -- 탈퇴번호
MEMBER_NO NUMBER, -- 회원번호
MEMBER_ID VARCHAR2(30), -- 회원아이디
MEMBER_NAME VARCHAR2(15), -- 회원이름
JOIN_DATE DATE, -- 가입일
DEL_DATE DATE -- 탈퇴일
);
CREATE SEQUENCE DEL_MEMBER_SEQ; --탈퇴한회원번호 시퀀스
-- 탈퇴한 회원의 정보를 멤버테이블에서는 삭제하고, 삭제테이블에는 추가해주는 작업
-- 회원번호로 탈퇴하면, 회원정보를 MEMBER_TBL에서 삭제,
-- DEL_MEMBER_TBL에는 데이터 입력
-- 1번회원이 탈퇴하는 경우
-- 1. 1번회원의 정보를 조회(MEMBER_NO, MEMBER_ID, MEMBER_NAME, JOIN_DATE)
-- DEL_MEMBER_TBL에 INSERT해주기위해
-- 2. 1번회원을 MEMBER_TBL에서 삭제
-- 3. 조회된 정보를 DEL_MEMBER_TBL 테이블에 INSERT
-- 1번작업
SELECT MEMBER_NO, MEMBER_ID, MEMBER_NAME, JOIN_DATE
FROM MEMBER_TBL WHERE MEMBER_NO = 1;
-- 2번작업
DELETE FROM MEMBER_TBL WHERE MEMBER_NO = 1;
-- 3번작업
INSERT INTO DEL_MEMBER_TBL VALUES
(DEL_MEMBER_SEQ.NEXTVAL,조회된회원번호, 조회된아이디, 조회된이름, 조회된가입일, SYSDATE);
-- 위 작업을 PL/SQL(익명블록) 으로 작성
-- 이런작업을 PL/SQL을 통해서 묶어주는 작업을 할 것(익명블록)
DECLARE
MEM_NO MEMBER_TBL.MEMBER_NO%TYPE;
MEM_ID MEMBER_TBL.MEMBER_ID%TYPE;
MEM_NAME MEMBER_TBL.MEMBER_NAME%TYPE;
J_DATE MEMBER_TBL.JOIN_DATE%TYPE;
DEL_NO NUMBER; -- 삭제회원번호
BEGIN
DEL_NO := '&삭제회원번호';
SELECT MEMBER_NO, MEMBER_ID, MEMBER_NAME, JOIN_DATE
INTO MEM_NO, MEM_ID, MEM_NAME, J_DATE
FROM MEMBER_TBL WHERE MEMBER_NO = DEL_NO;
DELETE FROM MEMBER_TBL WHERE MEMBER_NO = DEL_NO;
INSERT INTO DEL_MEMBER_TBL VALUES
(DEL_MEMBER_SEQ.NEXTVAL, MEM_NO, MEM_ID, MEM_NAME, J_DATE, SYSDATE);
COMMIT;
END;
/
SELECT * FROM MEMBER_TBL;
SELECT * FROM DEL_MEMBER_TBL;
>> 하지만 이 경우 탈퇴를 할 때마다 익명블록로직을 작성해주어야한다 - 익명블록의 한계
>> 프로시져를 사용
- 프로시져
: 일련의 작업절차를 정리해서 저장해 둔 것
: 여러 SQL문을 묶어서 미리 정의하고, 하나의 요청으로 실행
: 자주사용되는 복잡한 작업을 미리 만들어두고 계속 재사용
- 프로시져 생성방법(프로시져도 객체이기때문에 CREATE문으로)
CREATE PROCEDURE 프로시져이름(매개변수) -- 매개변수가 없으면 ()생략
IS
변수 선언
BEGIN
실행할 로직 작성
END;
/
-- 익명블록으로 작성했던 로직을 프로시져로 만들기
CREATE PROCEDURE DEL_MEMBER_PRO(DEL_NO MEMBER_TBL.MEMBER_NO%TYPE)
-- 삭제할 회원번호를 매개변수로 받음
IS
MEM_NO MEMBER_TBL.MEMBER_NO%TYPE;
MEM_ID MEMBER_TBL.MEMBER_ID%TYPE;
MEM_NAME MEMBER_TBL.MEMBER_NAME%TYPE;
J_DATE MEMBER_TBL.JOIN_DATE%TYPE;
BEGIN
SELECT MEMBER_NO, MEMBER_ID, MEMBER_NAME, JOIN_DATE
INTO MEM_NO, MEM_ID, MEM_NAME, J_DATE
FROM MEMBER_TBL WHERE MEMBER_NO = DEL_NO;
DELETE FROM MEMBER_TBL WHERE MEMBER_NO = DEL_NO;
INSERT INTO DEL_MEMBER_TBL VALUES
(DEL_MEMBER_SEQ.NEXTVAL, MEM_NO, MEM_ID, MEM_NAME, J_DATE, SYSDATE);
COMMIT;
END;
/
-- 프로시져 실행
--EXECUTE 프로시져명(매개변수);
EXECUTE DEL_MEMBER_PRO(20);
EXEC DEL_MEMBER_PRO(5);
-- 프로시져 삭제
-- DROP PROCEURE 프로시져명;
DROP PROCEDURE DEL_MEMBER_PRO;
>> 익명블록과 프로시저의 차이점 <<
: 사용자계정에 접속이 해제된 후 재접속 시 익명블록은 다시 작성해야하지만,
: 프로시져는 저장이 되어있기 때문에 실행문만 작성하면 바로 동작이된다!
: 테이블을 저장하듯이 프로시져를 저장해두고 사용하는 것
[ 후기 ] -------------------------------------------------------------------------------------------------------------------------------------------------------
오늘 실수한 점
시퀀스 사용시 시퀀스명.NEXTVAL /CURRVAL 을 적어야하는데 깜박함
'국비수업 > DB' 카테고리의 다른 글
| 24일차 : PL/SQL / DB모델링 (0) | 2022.12.29 |
|---|---|
| 22일차 : ALTER, DROP / TCL / OBJECT (0) | 2022.12.27 |
| 21일차 : 서브쿼리 / DML / DDL (0) | 2022.12.26 |
| 20일차 : GROUP BY / JOIN (0) | 2022.12.23 |
| 19일차 : 함수 (0) | 2022.12.22 |
'국비수업/DB' Related Articles
more
Comments