-- EMP테이블 복사
CREATE TABLE EMP_DUP
AS
SELECT * FROM EMP;
-- EMP테이블 조회
SELECT * FROM EMP_DUP;
-- EMP_DUP테이블의 데이터를 모두 삭제 하는 프로시저
CREATE OR REPLACE PROCEDURE PROC_DEL_EMP_DUP
IS
BEGIN
DELETE FROM EMP_DUP;
COMMIT;
END;
/
-- 프로시져 실행
EXEC PROC_DEL_EMP_DUP;
/*
<PROCEDURE>
PL/SQL 문을 저장하는 객체이다.
필요할 때마다 복잡한 구문을 다시 입력할 필요 없이 간단하게 호출해서
실행 결과를 얻을 수 있다.
특정 로직을 처리하기만 하고 결과값을 반환하지 않는다.
[표현법]
CREATE PROCEDURE 프로시저명
(
매개변수 1 [IN/OUT] 테이터타입 [:=DEFAULT 값],
매개변수 2 [IN/OUT] 테이터타입 [:=DEFAULT 값],
...
)
IS [AS]
선언부
BEGIN
실행부
EXCEPTION
예외처리부
END [프로시저명];
/
[실행방법]
EXECUTE(EXEC) 프로시저명[(매개값1, 매개값2, ...)];
*/
-- 매개변수가 있는 프로시저
-- 저장프로시저의 경우 생성후
-- 호출문장을 이용하여 프로시저를 실행 할수 있습니다.
-- 익명의 프로시저의 경우 저장되지 않음
CREATE OR REPLACE PROCEDURE PROC_DEL_EMP_ID
(
P_EMP_ID EMP.EMP_ID%TYPE
)
IS
-- 변수선언
-- 변수이름 변수타입
RES NUMBER;
BEGIN
-- 로직작성
DELETE
FROM EMP
WHERE EMP_ID = P_EMP_ID;
RES := SQL%ROWCOUNT;
INSERT INTO PROC_RES
VALUES ('PROC_DEL_EMP_ID'
, RES||'건 삭제되었습니다.'
, SYSDATE);
-- 순서가 중요!!!
COMMIT;
DBMS_OUTPUT.PUT_LINE(RES||'건 삭제되었습니다.');
END;
/
--310, 304, 302, 301
SELECT * FROM EMP ORDER BY EMP_ID DESC;
-- 프로시저 실행
EXEC PROC_DEL_EMP_ID('220');
EXEC PROC_DEL_EMP_ID('&사번');
SELECT * FROM PROC_RES;
-- 테이블 생성
CREATE TABLE PROC_RES(
PROC_NAME VARCHAR2(100)
, RES VARCHAR2(1000)
, REG_DATE DATE
);
-- 프로시저를 관리하는 데이터 딕셔너리
SELECT * FROM USER_SOURCE;
-- 사원정보를 받아서 사원테이블에 입력하는 프로시저를 생성!!
-- 프로시저 이름 : PROC_INS_EMP
-- 파라메터 : 사번, 이름, 주민번호, 직급코드
CREATE OR REPLACE PROCEDURE PROC_INS_EMP
(
-- 매개변수 매개변수타입
P_EMP_ID EMP.EMP_ID%TYPE
, P_EMP_NAME EMP.EMP_NAME%TYPE
, P_EMP_NO EMP.EMP_NO%TYPE
, P_JOB_CODE EMP.JOB_CODE%TYPE
)
IS
RES VARCHAR2(100);
BEGIN
INSERT INTO EMP (EMP_ID, EMP_NAME, EMP_NO, JOB_CODE)
VALUES (P_EMP_ID, P_EMP_NAME, P_EMP_NO, P_JOB_CODE);
RES := SQL%ROWCOUNT || '건 삽입되었습니다.';
INSERT INTO PROC_RES
VALUES('PROC_INS_EMP', RES, SYSDATE);
END;
/
EXEC PROC_INS_EMP('&사번','&이름','&주민번호','&직급코드');
EXEC PROC_INS_EMP('300','공유','111111-1111111','J1');
SELECT * FROM EMP ORDER BY 1 DESC;
/*
2) IN/OUT 매개변수가 있는 프로시저
IN 매개변수 : 프로시저 내부에서 사용될 변수
OUT 매개변수 : 프로시저 호출부(외부)에서 사용될 값을 담아줄 변수
*/
-- 사번을 입력 받아서 사원명, 급여, 보너스를 OUT 매개변수에 담아 봅시다
SELECT EMP_NAME, SALARY, BONUS
FROM EMP
WHERE EMP_ID=200;
CREATE OR REPLACE PROCEDURE PROC_SEL_EMP_ID
(
P_EMP_ID IN EMP.EMP_ID%TYPE
, P_EMP_NAME IN OUT EMP.EMP_NAME%TYPE
, P_SALARY OUT EMP.SALARY%TYPE
, P_BONUS OUT EMP.BONUS%TYPE
)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('P_EMP_ID : '||P_EMP_ID);
DBMS_OUTPUT.PUT_LINE('P_EMP_NAME : ' || P_EMP_NAME);
DBMS_OUTPUT.PUT_LINE('P_SALARY : ' || P_SALARY);
SELECT EMP_NAME, SALARY, BONUS
INTO P_EMP_NAME, P_SALARY, P_BONUS
FROM EMP
WHERE EMP_ID=P_EMP_ID;
END;
/
SELECT EMP_NAME FROM EMP;
SET SERVEROUTPUT ON;
SET AUTOPRINT OFF;
-- 바인드변수 선언(VARIABLE, VAR)
--VAR V_EMP_NAME EMP.EMP_NAME%TYPE;
-- VAR 변수이름 타입;
VAR V_EMP_NAME VARCHAR2(30);
VAR V_SALARY NUMBER;
VAR V_BONUS NUMBER;
-- 바인드 변수에 값을 설정
-- 값 설정
-- EXEC :변수명 := '값';
EXEC :V_EMP_NAME := '나잘난';
PRINT V_EMP_NAME;
-- 프로시저 실행
-- 바인드 변수는 :변수명 형태로 참조 가능
EXEC PROC_SEL_EMP_ID('200', :V_EMP_NAME, :V_SALARY, :V_BONUS);
-- 실행결과 바인드변수 출력
PRINT V_EMP_NAME;
--PRINT V_EMP_SALARY;
--PRINT V_BONUS
SELECT * FROM JOB;
-- 파라메터로 전달받은 직급코드의 직급명을 변경
-- IN P_JOB_CODE
-- IN P_JOB_NAME
-- OUT VARCHAR2(100) (SQL%ROWCNT)
-- 프로시져의 이름 : PROC_UDT_TITLE
CREATE OR REPLACE PROCEDURE PROC_UDT_TITLE
(
-- 매개변수 정의
P_JOB_CODE IN JOB.JOB_CODE%TYPE
, P_JOB_NAME IN JOB.JOB_NAME%TYPE
-- OUT : 외부로 부터 값을 받아오지 않아요!
-- 프로시저 외부로 값을 저장
, P_RES OUT VARCHAR2
)
IS
-- 변수선언
BEGIN
DBMS_OUTPUT.PUT_LINE('P_JOB_CODE : ' || P_JOB_CODE);
DBMS_OUTPUT.PUT_LINE('P_JOB_NAME : ' || P_JOB_NAME);
DBMS_OUTPUT.PUT_LINE('P_RES : ' || P_RES);
-- 실행로직
UPDATE JOB
SET JOB_NAME = P_JOB_NAME
WHERE JOB_CODE = P_JOB_CODE;
P_RES := SQL%ROWCOUNT || '건 처리되었습니다.';
DBMS_OUTPUT.PUT_LINE('P_JOB_CODE : ' || P_JOB_CODE);
DBMS_OUTPUT.PUT_LINE('P_JOB_NAME : ' || P_JOB_NAME);
DBMS_OUTPUT.PUT_LINE('P_RES : ' || P_RES);
END;
/
SELECT * FROM JOB;
-- 1. 프로시저 생성
-- 2. 변수선언 (OUT 파라메터의 값을 확인하기 위해)
VAR P_RES VARCHAR2(100);
-- 3. 프로시저 실행
-- 프로시저 실행할때 :변수명
EXEC PROC_UDT_TITLE('J1','대표이사',:P_RES);
-- 4. 변수값 출력 (OUT 파라메터로 부터 저장된 값을 출력)
PRINT P_RES;
'Study > SQL' 카테고리의 다른 글
27.커서,트리거 (1) | 2023.05.19 |
---|---|
26.plsql예외처리 (0) | 2023.05.18 |
24.plsql함수 (0) | 2023.05.17 |
23.plsql실행부 (0) | 2023.05.16 |
22.plsql 변수 (0) | 2023.05.15 |
댓글