[Oracle] 오라클 - PL/SQL 프로시저 (SELECT / IN OUT 파라미터)

[Oracle]
오라클 - PL/SQL 프로시저 (SELECT / IN OUT 파라미터)

---

✔️ PL/SQL 프로시저 (SELECT)

-- 하나의 레코드를 출력하는 프로시저를 만들어보자

CREATE OR REPLACE PROCEDURE pSelectOneTest
(
	pNum NUMBER
)
IS
	--rec test%ROWTYPE; 이렇게 해도 되지만 아래처럼 하기
	
	-- 사용자 정의 레코드 타입 선언
	TYPE mytype IS RECORD
	(
		num test.num%TYPE,
		name test.name%TYPE,
		score test.score%TYPE,
		grade test.grade%TYPE

	);
	rec MYTYPE; -- 변수선언


BEGIN

	SELECT num, name, score, grade INTO rec
	FROM test
	WHERE num = pNum;

	DBMS_OUTPUT.PUT_LINE(rec.num || ' : ' || rec.name || ' : ' || rec.score || ' : ' || rec.grade);

END;
/


EXEC PSELECTONETEST (2);

💡SELECT 는 INTO 가 필요하다!!
해당 프로시저는 pNum 파라미터 설정 = 입력 받은 num 과 일치하는 레코드 하나만 출력한다.

TYPE mytype IS RECORD
mytype 이란사용자 정의 레코드 타입을 만들었다. 
rec MYTYPE; (rec 에 나의 레코드 타입 선언)

 

 

-- 모든 레코드를 출력하는 프로시저를 만들어보자

CREATE OR REPLACE PROCEDURE pSelectListTest
	IS
	BEGIN
		FOR rec IN (SELECT * FROM test) LOOP
	DBMS_OUTPUT.PUT_LINE(rec.num || ' : ' || rec.name || ' : ' || rec.score || ' : ' || rec.grade);
		END LOOP;
	END;
	/
    
    
EXEC pSelectListTest;

💡 FOR ~ LOOP 문을 사용
rec 에 test 테이블의 모든 레코드를 반복해서 입력받음

DBMS_OUTPUT.PUT_LINE : 화면에 출력하는 문법

 

 

 

🔒 3개의 테이블을 이용해서 프로시저 만들기

-- 아래는 사전 준비 (테이블 3개)

	CREATE TABLE ex1 (
		num NUMBER PRIMARY KEY,
		name VARCHAR2(30) NOT NULL
	);

	CREATE TABLE ex2 (
		num NUMBER PRIMARY KEY,
		birth DATE NOT NULL,
		FOREIGN KEY(num) REFERENCES ex1(num)
	);

	CREATE TABLE ex3 (
		num NUMBER PRIMARY KEY,
		score NUMBER(3) NOT NULL,
		grade VARCHAR2(10) NOT NULL,
		FOREIGN KEY(num) REFERENCES ex1(num)
	);


CREATE SEQUENCE ex_seq
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE;

 

 

-- 데이터 추가 프로시저

CREATE OR REPLACE PROCEDURE pInsertEx
(	
	pName IN VARCHAR2,
	pBirth IN 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 >= 80 THEN vGrade := '우수';
	ELSIF pScore >= 60 THEN vGrade := '보통';
	ELSE vGrade := '미달';
	END IF;


INSERT INTO ex1(num, name) VALUES (ex_seq.NEXTVAL, pName);
    INSERT INTO ex2(num, birth) VALUES (ex_seq.CURRVAL, TO_DATE(pBirth, 'YYYY-MM-DD'));
    INSERT INTO ex3(num, score, grade) VALUES (ex_seq.CURRVAL, pScore, vGrade);
    COMMIT;
END;
/

💡 이름, 생년월일, 점수만 입력 받는다. (파라미터 3개)
필요한 것 : vGrade 변수 / 예외 처리 / INSERT 문

 

 

-- 데이터 수정 프로시저

CREATE OR REPLACE PROCEDURE pUpdateEx
(	
    pNum NUMBER,
	pName VARCHAR2,
	pBirth 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 >= 80 THEN vGrade := '우수';
	ELSIF pScore >= 60 THEN vGrade := '보통';
	ELSE vGrade := '미달';
	END IF;

  	UPDATE ex1 SET name= pName WHERE num = pNum;
    UPDATE ex2 SET birth = pBirth WHERE num = pNum ;
    UPDATE ex3 SET score = pScore, grade = vGrade WHERE num = pNum;
    COMMIT;
END;
/

💡 이번에는 num 값 까지 받아야한다. (그래야 UPDATE 절에 조건을 주기 때문)

 

 

 

🔒 조건을 만족하는 하나의 레코드 출력

CREATE OR REPLACE PROCEDURE pSelectOneEx
(
	pNum IN NUMBER
)
IS
	TYPE mytype IS RECORD
		(
			num ex1.num%TYPE,
			name ex1.name%TYPE,
			birth ex2.birth%TYPE,
			score ex3.score%TYPE,
			grade ex3.grade%TYPE
		);

		rec MYTYPE;
        
BEGIN

	SELECT e1.num, name, birth, score, grade INTO rec
		FROM ex1 e1	
		JOIN ex2 e2 ON e1.num = e2.num
		JOIN ex3 e3 ON e1.num = e3.num
		WHERE e1.num = pNum;

	DBMS_OUTPUT.PUT(rec.num || ' : ' );
	DBMS_OUTPUT.PUT(rec.name || ' : ' );
	DBMS_OUTPUT.PUT(rec.birth || ' : ' );
	DBMS_OUTPUT.PUT(rec.score || ' : ' );
	DBMS_OUTPUT.PUT_LINE(rec.grade);

	END;
	/
    
EXEC pSelectOneEx (4);

💡 위에서 만든 3개의 테이블을 조인해서 원하는 레코드 하나만 출력하는 프로시저다.
사용자 정의 레코드 타입 rec 을 만들고 SELECT 절에서 따로 또 조인해줘야한다.

SELECT ~INTO 뒤에는 파라미터가 온다. 

 

 

 

✔️ OUT 파라미터

-- OUT 파라미터 테스트용 ⭐

SQL developer 에서는 out 확인이 불가하다. (그래서 다른 작업 거쳐야함)

CREATE OR REPLACE PROCEDURE pSelectOutTest
(
	pNum IN NUMBER,
	pName OUT VARCHAR2,
	pScore OUT NUMBER

)
IS
BEGIN
	--pNum := 1;  -- 에러. IN 파라미터는 읽기 전용이라 변경 불가하다. 
	
	SELECT name, score INTO pName, pScore
	FROM test
	WHERE num = pNum;
 
END;
/

-- OUT 파라미터 테스트용 ⭐ 디벨로퍼에서는 out 확인이 불가하다. 
CREATE OR REPLACE PROCEDURE pResultTest
(
    pNum IN NUMBER
)
IS
    vName VARCHAR2(30);
    vScore NUMBER(3);
    
BEGIN
    -- 다른 프로시져 호출 ⭐ 할 때는 exec 생략 가능
    pSelectOutTest(pNum, vName, vScore);
    DBMS_OUTPUT.PUT_LINE(vName || ' : ' || vScore);
END;
/

exec pResultTest(1);