[Oracle] PL/SQL 문법

programmeaow·2022년 6월 2일
0

Database

목록 보기
1/3

✔️ PL/SQL이란?

PL/SQL : SQL의 확장된 개념, 절차적 프로그래밍 언어

Oracle에서 프로그래밍 언어의 특성을 수용하여 SQL의 단점을 보완한, 절차적 프로그래밍 기능을 지닌 언어이다.


oracle의 함수와 프로시저는 구조가 비슷하지만 호출 방법이 서로 다르다.
Oracld DB는 Oracle만의 프로그래밍 개발 방법이 있는데,

  1. 이름을 부여하지 않고 단순 개발
  2. 프로시저라는 이름으로 개발
  3. 함수라는 이름으로 개발

2번과 3번의 경우 이름을 부여하여 재사용성을 고려할 수 있다.
이름 없이 개발한 경우, 해당 부분을 이용하기 위해서는 코드를 전체 복사해서 붙여넣기 해야 이용할 수 있을 것이다.

PL/SQL에서 사용 가능한 문법은 일반 SQL create 등에서 이용 불가!

프로시저와 함수를 만들어보기 전에 이름을 부여하지 않는 단순 개발부터 알아보도록 하자.

declare
	변수명 (선언부)
begin
	실행 문장 (로직 구현)
    EXCEPTION 실행 (예외 처리) //필수 아님
end;

해당 문법으로 대입 연산자인 := 를 이용하여 간단한 연산을 실행해볼 수 있다.

DECLARE
	num Integer;
BEGIN
	num := 5-2;
    dbms_output.put_line('값 : ' || num);
END;

//값 : 3

가변적인 문자열 type의 varchar2를 이용하여 문자열을 출력할 수도 있다.

DECLARE
	say varchar2(10);
BEGIN
	say := '안녕';
	dbms_output.put_line('출력 : ' || say);
END;

//출력 : 안녕

마지막으로 맨 처음에 실행한 연산 로직에 예외처리를 적용해보았다.

DECLARE
	num Integer := 0;
BEGIN
	num := 2/0;
    EXCEPTION
		WHEN OTHERS THEN   //OTHERS:  발생된 모든 예외 의미
		dbms_output.put_line('계산 오류');
        
    dbms_output.put_line('값 : ' || num);
END;

//출력 : 계산 오류, 값 : 0 

0으로 나눌 수 없기 때문에 예외가 발생하고, OTHERS로 발생된 모든 예외에 "계산 오류"라는 출력 문장이 실행되도록 했기 때문에 위와 같은 결과가 출력된다.




이번에는 함수와 프로시저 구현시 변수 선언 위치에 따른 구분에 대해 알아보자.

BEGIN 안에서 멤버 변수와 로컬 변수를 따로 선언할 수 있는데, 로컬 변수를 선언하려면 중첩 DECLARE를 선언해줘야 한다.

DECLARE
	say1 varchar2(10) := '멤버 변수';
BEGIN
	DECLARE
		say2 varchar2(10) := '로컬 변수';
	BEGIN
		dbms_output.put_line(say1);   
		dbms_output.put_line(say2);	 
	END;
END;

//출력 : 멤버 변수, 로컬 변수

여기서 출력문의 위치를 살짝 바꿔보자.

DECLARE
	say1 varchar2(20) := '멤버 변수';
BEGIN
	DECLARE
		say2 varchar2(20) := '로컬 변수';
	BEGIN
	END;
    dbms_output.put_line(say1); //멤버 변수
	dbms_output.put_line(say2);	//로컬 변수 
END;

위의 실행 결과는 어떻게 나올까? 팝업이 발생하며 로직이 실행되지 않을 것이다.

dbms_output.put_line(say1)은 멤버 변수를 출력하는 문장이기 때문에 중첩 DECLARE문 밖에 둬도 실행이 정상적으로 된다.

하지만 dbms_output.put_line(say2)은 중첩 DECLARE안에 선언한 로컬 변수를 출력하는 문장이다. 따라서 DECLARE문을 벗어나면 실행이 되지 않는다.
왜냐하면 say2가 해당 부분에 존재하지 않기 때문이다.



이미 존재하는 Table로의 column 타입을 그대로 사용할 수도 있다.

Database의 특정 컬럼의 타입을 의미하는 %type을 이용해보자.
Oracle에서 학습용으로 제공한 emp table을 이용했다.

DROP TABLE IF EXISTS emp01; 
CREATE TABLE emp01 AS SELECT * FROM emp; 
SELECT FROM emp01;

emp01이 이미 존재한다면 삭제하고, emp의 모든 정보를 가져와서 emp01을 생성해주었다.
해당 table에서 사번이 7369인 사원의 이름을 검색해보자.

DECLARE
	findempno emp01.empno%TYPE := 7369; //emp01에 있는 empno 타입의 데이터 7369를 findempno에 대입
	findename emp01.ename%TYPE;
BEGIN
	SELECT ename 
    INTO findename //ename에서 찾은 후 findename에 데이터 넣기
	FROM EMP01
	WHERE empno = findempno;
	
	dbms_output.put_line(findename);
END;

//출력 : SMITH

이미 존재하는 table에 있는 모든 column 타입을 활용하려면 %rowtype 을 이용하면 된다.




Oracle에서도 if문을 활용하여 조건문, 다중 조건문을 작성할 수 있다.

- if문

if (조건문) then
	실행될 문장
elsif (조건문) then  //다중 조건문 필요시 작성
	실행될 문장
end if;

if문을 사용해서 이름이 SMITH인 사원의 연봉을 구하는 프로시저를 작성해보자.

연봉은 sal에 12를 곱한 뒤 comm값을 더해서 구하는데, 이 때 comm 값이 null이면 연봉또한 null이 나오게 된다.
따라서 comm이 null이면 0으로 바꿔 계산하도록 if문을 함께 작성했다.

DECLARE
	emp_i emp%rowtype;
	total_sal number(7,2);
BEGIN
	SELECT empno, ename, sal, comm
		INTO emp_i.empno, emp_i.ename, emp_i.sal, emp_i.comm
	FROM EMP e 
	WHERE ename = 'SMITH';

	IF (emp_i.comm IS null) THEN 
		emp_i.comm := 0;
	END IF;

	total_sal := emp_i.sal*12 + emp_i.comm;
	dbms_output.put_line('SMITH의 연봉 : ' || total_sal);
END;

//출력 > SMITH의 연봉 : 9600

이번에는 위 코드에 동적 데이터를 반영하여 사원 이름을 입력하면 해당 사원의 연봉이 조회되게 해보자.

실행시 동적 데이터 반영되게 하기 : & 변수명
ex)&a
DECLARE
	inputname emp.ename%type := &a;
	emp_i emp%rowtype;
	total_sal number(7,2);
BEGIN
	SELECT empno, ename, sal, comm
		INTO emp_i.empno, emp_i.ename, emp_i.sal, emp_i.comm
	FROM EMP e 
	WHERE ename = inputname;

	IF (emp_i.comm IS null) THEN 
		emp_i.comm := 0;
	END IF;

	total_sal := emp_i.sal*12 + emp_i.comm;
	dbms_output.put_line(inputname || '의 연봉 : ' || total_sal);
END;

해당 코드 실행시 이름을 입력받아서 해당 사원의 연봉이 출력되는 것을 확인할 수 있다.

profile
개발이란 뭘까

0개의 댓글