Oracle - 동적/정적SQL + 프로시저(1)

-- 1. INSERT, UPDATE, DELETE, (MERGE)
-- → DML(Data Maniplulation 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문 사용 가능

--※ 정적 SQL(정적 PL/SQL)
--> 기본적으로 사용하는 SQL 구문과
-- PL/SQL 구문 안에 SQL 구문을 직접 삽입하는 방법
--> 작성이 쉽고 성능이 좋다.

--※ 동적 SQL(정적 PL/SQL)
--> 완성되지 않은 SQL 구믄을 기반으로
--  실행 중 변경 가능한 문자열 변수 또는 문자열 상수를 통해
--  SQL 구문을 동적으로 완성하여 실행하는 방법
--> 사전에 정의되지 않은 SQL 을 실행할 때 완성 및 확정하여 실행할 수 있다.
--  DML, TCL 외에도 DDL, DCL 등의 사용이 가능하다. 

--------------------------------------------------------------------------------
-- ■■■ PROCEDURE(프로시저) ■■■ -- 

-- 1. PL/SQL 에서 가장 대표적인 구조인 스토어드 프로시저는
--    개발자가 자주 작성해야 하는 업문의 흐름을 미리 작성하여
--    데이터베이스 내에 저장해 두었다가 필요할 때 마다 호출하여
--    실행할 수 있도록 처리해 주는 구문이다.

-- 2. 형식 및 구조
/*
CREATE [OR REPLACE] PROCEDURE 프로시저명
[(  매개변수 IN 데이터타입
    ,매개변수 OUT 데이터타입
    ,매개변수 INOUT 데이터타입
)]
IS
    [-- 주요 변수 선언;]
BEGIN
    -- 실행구문;
    ...
    [EXCEPTION
        -- 예외 처리 구문;]
END;
*/

--※ FUNCTION 과 비교했을 때 ...
--   [RETURN 반환자료형] 부분이 존재하지 않으며,
--   [RETURN]문 자체도 존재하지 않고,
--   프로시저 실행 시 넘겨주게 되는 매개변수의 종류는
--   IN, OUT, INPUT 으로 구분된다.

--  3. 실행(호출)
/*
EXEC[UTE] 프로시저명[(인수1, 인수2)];
*/

 

--○ INSERT 프로시저

-- 실습 테이블 생성 → 『20250714_02_scott.sql』 파일 참조 
-- 테이블명 : TBL_STUDENTS
-- 테이블명 : TBL_IDPW 

-- 프로시저 생성
-- 프로시저명 : PRC_STUDENTS_INSERT(아이디, 패스워드, 이름, 전화번호, 주소);

-- 1번 처리                         -- 저 테이블에 ID 데이터타입을 참조해
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 -- 2번 처리 (매개변수 넘겨받은걸로 충분히 작업수행 가능) 
    -- 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); 
    
    -- 커밋 (DML구문이니까)
    COMMIT; 
END; 
--==>> Procedure PRC_STUDENTS_INSERT이(가) 컴파일되었습니다.
-- 다시 2번시트로 가서 프로시저 호출

--○ INSERT 프로시저

-- 실습 테이블 생성 → 『20250714_02_scott.sql』 파일 참조 
-- 문제 
-- 테이블명 : TBL_SUNGJUK

-- 데이터 입력 시 특정 항목의 데이터만 입력하면
-- 내부적으로 나머지 다른 항목이 함께 입력 처리될 수 있는 프로시저를 생성한다.
-- 프로시저명 : PRC_SUNGJUK_INSERT 
/*
실행 예)
EXEC PRC_SUNGJUK_INSERT(1, '조', 90,80,70);

프로시저 호출로 처리된 결과
학번  이름  국어  영어  수학  총점  평균  등급
 1   조  90    80    70   240    80    B 
*/

-- 1   조  90    80    70   240    80    B  
-- (   V_HAKBUN   IN  TBL_SUNGJUK.HAKBUN%TYPE   입력매개변수 

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   TBL_SUNGJUK.TOT%TYPE;
    V_AVG   TBL_SUNGJUK.AVG%TYPE;
    V_GRADE TBL_SUNGJUK.GRADE%TYPE; 
    
BEGIN 
    -- 실행부
    -- 아래의 쿼리문을 수행하기 위해서는
    -- 위에서 선언한 변수들에 값을 담아내야 한다.(V_TOT,V_AVG,V_GRADE)   
    V_TOT := V_KOR + V_ENG + V_MAT;
    V_AVG := V_TOT/3; 

    -- V_GRADE := '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 쿼리문 구성
    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이(가) 컴파일되었습니다.