[Oracle] PL/SQL 익명 프로시저

💡익명 프로시저

---

declare
-- 선언부
begin
-- 구현부
end;

declare부터 begin까지 선언부, begin부터 end까지가 구현부이다.

 

serveroutput on

set serveroutput on;

set serveroutput off;

dbms.output이 콘솔로 보이게 하려면 먼저 serveroutput을 

반대로 꺼줄 때에는 off 키워드를 사용한다.

 

익명 프로시저의 사용

DECLARE
	num number;
	name varchar2(30);
	today DATE;
BEGIN
	num := 10;
	dbms_output.put_line(num);
	
	name := 'Isaac';
	dbms_output.put_line(name);

	today := sysdate;
	dbms_output.put_line(today);
END;

dbms.output이라는 객체가 put_line이라는 메서드를 가지고 있다.
이는 실행하고 저장되지 않는 일회용 코드이다.

 

우리 모두 일회용품 사용을 줄이는 운동에 참여하도록 하자.

우리는 일 년에 약 11.5kg의 플라스틱을 소비한다고 한다. 이는 연간 소비량으로 치면 586,500톤이다!

 

declare
	num1 NUMBER;
	num2 NUMBER;
	num3 NUMBER := 30;
	num4 NUMBER DEFAULT 40;
	num5 NUMBER NOT NULL := 50; -- DECLARE 블럭에서 초기화를 해야 한다. (구현부 X)
begin

	dbms_output.put_line(num1); --null
	
	num2 := 20;
	dbms_output.put_line(num2);
	
	dbms_output.put_line(num3);

	dbms_output.put_line(num4);
    
	num4 := 400;
	dbms_output.put_line(num4);

	--num5 := 500;
	dbms_output.put_line(num5);

end;

Oracle에서는 변수를 초기화하지 않고 출력되면 null 값이 들어가 출력되진 않지만 실행은 된다.

declare에서 선언과 동시에 초기화하는 것도 가능하다.

not null을 변수에 설정할 경우, 반드시 declare에서 초기화를 해 주어야 한다.

 

변수의 사용

변수는 select 결과를 담는 용도로 사용한다.
변수에 select 결과를 담을 때에는 select into절(PL/SQL 구문)을 사용한다.

 

DECLARE
	vbuseo varchar2(15);
BEGIN
	-- vbuseo := (SELECT buseo FROM tblInsa WHERE name = '홍길동');
	
	SELECT buseo INTO vbuseo FROM tblInsa WHERE name = '홍길동';
	dbms_output.put_line(vbuseo);

END;

홍길동의 buseo '기획부'를 vbuseo 변수에 넣고 싶다.
변수 이름을 그냥 buseo라고 지을 경우 나중에 컬럼 이름과 헷갈릴 가능성이 높다. 따라서 'v'와 같은 접두어를 붙이는 편이다.

 

select into

--an INTO clause is expected in this SELECT statement
BEGIN
    select buseo from tblInsa where name = '홍길동';
end;

select절에 into절을 사용해야 한다는 오류가 발생하고 있다.

오류가 발생하는 이유는 PL/SQL 프로시저에서는 순수한 select문은 절대로 올 수 없기 때문이다.
PL/SQL 프로시저 안에서는 select into문만을 사용해야 한다.

 

성과급을 받는 직원명 구하기

CREATE TABLE tblName(
	name VARCHAR2(15)
);

위 테이블에 개발부이면서 부장인 직원의 이름을 넣어보도록 하자

 

ANSI/SQL 서브 쿼리 사용

INSERT INTO tblName (name)
	VALUES ((SELECT name FROM tblInsa WHERE buseo = '개발부' AND jikwi = '부장'));

서브 쿼리는 반드시 소괄호()로 묶어 주어야 한다는 것을 명심하자.

 

PL/SQL 사용

DECLARE
	vname varchar2(15);
BEGIN
	-- 1. 개발부의 부장을 찾아서 select로 name을 검색한다.
	SELECT name INTO vname FROM tblInsa WHERE buseo = '개발부' AND jikwi = '부장';
	
	-- 2. tblName > name > INSERT
	INSERT INTO tblName (name) VALUES (vname);
	
END;

서브 쿼리와 PL/SQL의 결과가 둘 다 잘 들어간 것을 확인할 수 있다.

 

컬럼이 여러 개인 select into

DECLARE
	vname VARCHAR2(15);
	vbuseo VARCHAR2(15);
	vjikwi VARCHAR2(15);
	vbasicpay NUMBER;
BEGIN
	
	--select into
	SELECT name, buseo, jikwi, basicpay INTO vname, vbuseo, vjikwi, vbasicpay FROM tblInsa WHERE num = 1001;

	dbms_output.put_line(vname);
	dbms_output.put_line(vbuseo);
	dbms_output.put_line(vjikwi);
	dbms_output.put_line(vbasicpay);

END;

into는 변수마다 작성하는 게 아니라 한 번만 작성한다.

 

into 사용 조건

1. 컬럼의 개수와 변수의 개수는 일치해야 한다.
2. 컬럼의 순서와 변수의 순서는 일치해야 한다.
3. 컬럼과 변수의 자료형은 일치해야 한다.

 

타입 참조

type

%type

• a. 자료형
• b. 길이

사용하는 테이블의 특정 컬럼값의 스키마를 알아내서 변수에 적용한다.

복사되는 정보이며, 컬럼 1개를 참조한다.

 

타입 참조의 사용

DECLARE
	--vbuseo varchar2(15);
	vbuseo tblInsa.buseo%TYPE;
BEGIN
	SELECT buseo INTO vbuseo FROM tblInsa WHERE name = '홍길동';
	dbms_output.put_line(vbuseo);
END;

 

DECLARE
	/*
	vname VARCHAR2(15);
	vbuseo VARCHAR2(15);
	vjikwi VARCHAR2(15);
	vbasicpay NUMBER;
	*/

	vname tblInsa.name%TYPE;
	vbuseo tblInsa.buseo%TYPE;
	vjikwi tblInsa.jikwi%TYPE;
	vbasicpay tblInsa.basicpay%TYPE;
BEGIN
	
	--select into
	SELECT name, buseo, jikwi, basicpay INTO vname, vbuseo, vjikwi, vbasicpay FROM tblInsa WHERE num = 1001;

	dbms_output.put_line(vname);
	dbms_output.put_line(vbuseo);
	dbms_output.put_line(vjikwi);
	dbms_output.put_line(vbasicpay);

END;

명시적으로 선언하지 않고 타입 참조를 하면 유지보수에 용이하다.

 

직원 중 일부에게 보너스를 지급하여 내역 저장

CREATE TABLE tblBonus (
	seq NUMBER PRIMARY KEY,
	num NUMBER(5) NOT NULL REFERENCES tblInsa(num), --직원번호(FK)
	bonus NUMBER NOT NULL
);

DECLARE
	vnum tblInsa.num%TYPE;
	vbasicpay tblInsa.basicpay%TYPE;
BEGIN

	SELECT num, basicpay INTO vnum, vbasicpay
		FROM tblInsa WHERE city = '서울' AND jikwi = '부장' AND buseo = '영업부';

	INSERT INTO tblBonus (seq, num, bonus)
		VALUES ((SELECT nvl(max(seq), 0) + 1 FROM tblBonus), vnum, vbasicpay * 1.5);

END;

SELECT * FROM tblBonus b
		INNER JOIN tblInsa i
			ON i.num = b.num;

 

'무명씨' 데이터의 테이블 이동

1. 무명씨 > tblMen > select
2. tblWomen > insert
3. tblMen > delete

DECLARE
	vname tblMen.name%TYPE;
	vage tblMen.age%TYPE;
	vheight tblMen.height%TYPE;
	vweight tblMen.weight%TYPE;
	vcouple tblMen.couple%TYPE;
BEGIN
	
	-- 1.
	SELECT
		name, age, height, weight, couple
		INTO
		vname, vage, vheight, vweight, vcouple
	FROM tblMen WHERE name = '무명씨';

	-- 2.
	INSERT INTO tblWomen (name, age, height, weight, couple)
		VALUES (vname, vage, vheight, vweight, vcouple);

	--3.
	DELETE FROM tblMen WHERE name = '무명씨';
	
END;

무명씨의 성별이 잘못 기재되어서 다른 테이블로 옮기는 작업을 하였다.

 

rowtype

%rowtype

행 전체를 참조한다.
여러 개의 컬럼을 한 번에 참조할 수 있다.
%type의 집합형이다.

 

'아무개' 데이터의 테이블 이동

DECLARE
	vrow tblMen%rowtype; -- vrow: tblMen의 레코드 1개(모든 컬럼값)을 저장할 수 있는 변수
BEGIN
	
	-- 1.
	SELECT
		* INTO vrow
	FROM tblMen WHERE name = '아무개';

	-- 2.
	INSERT INTO tblWomen (name, age, height, weight, couple)
		VALUES (vrow.name, vrow.age, vrow.height, vrow.weight, vrow.couple);

	--3.
	DELETE FROM tblMen WHERE name = '아무개';
	
END;

 

코드 템플리트

환경설정 > 코드 편집기 > 코드 템플리트에서 코드조각을 만들 수 있다.

• ID: syso
• 템플리트: dbms_output.put_line();

Ctrl + Space를 입력하자 코드가 자동완성 된다.