[Oracle] 오라클 - PL/SQL 프로시저 (삽입/수정/삭제)

[Oracle]
오라클 - PL/SQL 프로시저 (삽입/수정/삭제)

---

✔️ 프로시저란?

⭐ 일단 리턴 값이 없는 분이다. (void 메소드 같은 것) ⭐

: 자주 실행해야 하는 업무 흐름(SQL)을 미리 작성하여 데이터베이스 내에 저장해 두었다가 필요할 때마다 호출하여 실행

-- 테이블 삭제한다고 연관 프로시저가 삭제되는건 아니다.

-- 특정 로직을 처리하고 결과를 반환하지 않는다.

-- 모두 DB에 저장된 객체이므로 스토어드 프로시저라고 부른다.

-- 필요할 때 가져다가 실행하는 것

-- 자동 롤백이 가능하다. (두번째 INSERT 실패시 첫번째 INSERT 도 자동 롤백)

-- 프로시저에서 INSERT , UPDATE , DELETE 는 자동 커밋이 되지 않기 때문에 꼭! COMMIT 구문 추가해야한다. 

 

📓 IN : 프로시저 값을 넘겨 받는 것 (읽기 전용/값 변경 불가)

📓 OUT : 프로시저 값을 넘기는 것 (디폴트 값 지정 불가)

-- USER_DEPENDENCIES : 객체 간 서로 참조하는 정보를 가진 데이터 사전이다.
-- USER_PROCEDURES : 프로시저 목록을 출력

 

 

-- 프로시저 만들기

CREATE OR REPLACE PROCEDURE pInsertTest
IS
	-- 변수 상수 등 선언
BEGIN
    INSERT INTO test(num,name,score,grade) VALUES (test_seq.NEXTVAL, 'a', 80, 'B');
    COMMIT;
END;
/

💡 CREATE OR REPLACE PROCEDURE 프로시저명
IS
BEGIN
END;
/

 

 

⭐ 필수

-- 의존관계 확인

SELECT * FROM user_dependencies;

 

-- 소스확인

SELECT * FROM user_source;

 

-- 프로시저 목록 확인/실행

SELECT * FROM user_procedures;
EXEC pInsertTest;

💡 EXEC 가 아닌 CALL 사용 가능!

 

 

 

🔒 문제

-- 학점 만들기 프로시저

CREATE OR REPLACE PROCEDURE pInsertTest
(	
	pName IN VARCHAR2, -- 파라미터는 크기를 명시하지 않는다.
	pScore  NUMBER -- IN 은 생략 가능

)
IS
	vGrade VARCHAR2(10); 		--⭐ 학점은 점수 넣으면 찍혀야함
BEGIN
	IF pScore >= 90 THEN vGrade := 'A';
	ELSIF pScore >= 80 THEN vGrade := 'B';
	ELSIF pScore >= 70 THEN vGrade := 'C';
	ELSIF pScore >= 60 THEN vGrade := 'D';
	ELSE vGrade := 'F';
	END IF;


    INSERT INTO test(num,name,score,grade) VALUES (test_seq.NEXTVAL, pName, pScore, vGrade);
    COMMIT;
END;
/

EXEC pInsertTest('b', 95);

💡 ❌ 프로시저 파라미터는 크기를 명시하지 않는다.
IN 은 생략 가능하다.
vGrade 변수 지정 (pName , pScore 는 읽기 전용)

 

 

 

-- 프로시저 : 수정 / 예외처리

CREATE OR REPLACE PROCEDURE pUpdateTest
(	
	pNum NUMBER,
	pName VARCHAR2,
	pScore  NUMBER

)
IS
	vGrade VARCHAR2(10); 		--⭐ 학점은 점수 넣으면 찍혀야함
BEGIN
	IF pScore < 0 OR pScore > 100 THEN
		RAISE_APPLICATION_ERROR(-20001, '점수는 0~100 사이만 가능합니다');
	END IF;

	IF pScore >= 90 THEN vGrade := 'A';
	ELSIF pScore >= 80 THEN vGrade := 'B';
	ELSIF pScore >= 70 THEN vGrade := 'C';
	ELSIF pScore >= 60 THEN vGrade := 'D';
	ELSE vGrade := 'F';
	END IF;


  	UPDATE test SET name=pName, score=pScore, grade = vGrade WHERE num=pNum;
    COMMIT;
END;
/


EXEC pUpdateTest(1, '홍길동', 100);  
SELECT * FROM test;

💡 예외처리
RAISE_APPLICATION_ERROR(-20001, '점수는 0~100 사이만 가능합니다');
= 예외는 -20000~-20999 까지만 지정 가능!

UPDATE 절에 WHERE 조건을 주지 않으면 모든 데이터가 수정되니까 주의하기 ㅠㅠ

 

 

 

-- 프로시저 : 삭제 

CREATE OR REPLACE PROCEDURE pDeleteTest
(	
	pNum NUMBER

)
IS
BEGIN
	DELETE FROM test WHERE pNum= num;
	COMMIT;
END;
/

EXEC pDeleteTest(2);  
SELECT * FROM test;

💡 DELETE 프로시저도 마찬가지로 WHERE 조건 필요
INSERT, DELETE, UPDATE 모두 COMMIT 해줘야 에러가 발생안한다.