데이터베이스 PL/SQL 변수선언

변수선언

프로시저 내 declare에서 변수선언을 한다고 배웠다. 이제 변수를 선언해보도록 하자. 변수의 이름은 보통 두 가지 타입으로 선언된다.

• v_:  내부 변수로서 프로시저 내에서 사용할 변수에 대해 주로 v라는 표현을 붙여 사용한다.

• p_:  위에서 cmd 창에서 입력을 받던 대체 변수들에 대해  p라는 표현을 붙였던 것을 기억할 것이다. 이처럼 입력 변수 표현법으로 사용한다.

이제 예시를 통해서 변수 선언을 어떻게 진행하고, 초기화하는지 살펴보자.

아마 c언어를 다룬적이 있는 분들은 이해하기 쉬울 것이다. 변수를 선언하여 해당 변수를 위한 공간을 만들고, 해당 데이터 공간에 값을 집어 넣는다. 그리고 이 값을 출력하고 있는 형태이다.

출력창을 살펴보면, 우리가 설정해준 변수가 제대로 출력되고 있음을 확인할 수 있다.

아예 데이터를 넣고 출력하는 것도 물론 가능하다.

우리가 위의 예시에서 했던 과정을 하나로 합친다고 생각하면 된다. 게다가 이 과정에서 upper함수와 같은 sql함수를 적용할 수도 있고, constant를 이용하여 어떤 상수값으로 변수를 고정시킬 수 있다. 게다가 not null과 같은 제약 조건을 적용하는 것 또한 가능하다.

상수 / 변수

상수와 변수의 개념을 조금 짚고 넘어가겠다.

위의 예시에서 v_deptno는 변수로 설정되었다. 따라서 다음처럼 20이라는 값을 넣어 출력하면, 문제없이 10이라는 값이 20으로 변경되어 출력된다.

이번에는 상수로 선언된 v_com의 값을 변경시켜보겠다. 1400을 1600으로 변경시켜 출력해보자. 다음처럼 v_com은 피할당자로 사용될 수 없다는 오류 메시지를 출력하고 있다.

이번에는 변수에 데이터를 select를 통해서 넣도록 하겠다. 일단 declare에 변수를 선언한다. 그리고 select문을 작성하여 원하는 데이터에 대해서 가져와 해당 변수에 넣고, 이를 출력해보자.

다음처럼 우리가 검색한 데이터가 변수 안에 잘 들어가 있는 것을 확인할 수 있다.

하지만 이 경우에는 하나의 데이터만을 가져와서 변수에 저장할 수 있기 때문에 select문이 여러 행 데이터를 가져오는 경우 오류를 출력한다. 게다가 데이터가 없는 경우에도 오류를 발생시킬 수 있다.

• 여러 행 데이터를 가져올 경우 에러

• 데이터가 없는 경우 에러

이 두가지 에러가 발생하지 않도록 select문을 작성하도록 하자.

칼럼을 변수로 받아오기

지금까지 다룬 변수 선언을 보시면, 우리가 sql에서 테이블 생성시 했던 칼럼 생성 방식과 유사하다. 그럼 테이블의 칼럼을 변수로 받아올 수는 없을까?

한번 테이블의 칼럼을 변수로 선언해보도록 하겠다. 이를 위해서 ‘%type’을 붙여 사용한다. 이번엔 변수 선언을 아래와 같이 해보자,

출력이 기존 선언 방식과 동일하게 이루어지고 있는 것을 확인할 수 있다.

변수를 객체로 받아오기

우리가 앞에서는 변수를 가져올 때 칼럼에 대응하는 변수를 하나 하나 설정해주었다. 하지만 행 데이터 전체에 대해서 한번에 가져올 수 있다. 이 때 ‘%rowtype’ 을 사용한다.

한번에 변수를 가져와서 해당 변수에서 deptno, dname, loc를 각 각 가져올 수 있다.

문제

사원명을 입력받아 사원번호, 사원명, 급여, 부서번호를 출력하는 스크립트를 작성하기

지금까지 배운 것을 바탕으로 이번엔 입력을 받은 데이터에 대해서 스크립트 내부 변수에 저장하고 이를 통해 select문을 동작시켜 객체를 받아와 출력하는 스크립트를 작성해보겠다.

먼저 이를 위해서 객체를 받을 v_emp라는 rowtype 변수하나를 선언해주고, v_ename이라는 변수에 입력받은 값을 저장하도록 하겠다.

그리고 select문에서 입력받은 변수에 대한 데이터에 대해서 v_emp 객체에 저장하여 각 요소들을 출력하겠다. 참고로 우리가 사용했던 accept문은 declare 위에 선언한다.

값이 제대로 출력되고 있다.

레코드 타입 변수 선언하기

레코드 타입 변수라는 사용자 정의 변수를 사용해보도록 하겠다. 먼저 자료형에 대해 선언하자. v_emp라는 변수를 우리가 정의한 자료형으로 정의하도록 한다. 이제 select문을 다시 실행시켜 행 데이터들을 변수에 저장하자.

앞에서 행 데이터를 직접 가져왔던 것과 달리 레코드 타입 변수의 경우, 사용자가 정의한 변수를 사용하여 원하는 데이터에 접근해야 한다.

기존에는 dept 전체 칼럼 데이터를 받아오기 때문에 v_dept 변수에 접근하여 dept 칼럼행들을 그냥 호출할 수 있었다. 하지만 각 칼럼들을 받아온 변수를 우리가 직접 선언해주었기 때문에 이제 정의한 변수명을 사용해야 한다.

출력이 제대로 이루어지고 있는 것을 확인할 수 있다.

문제

사원 번호를 입력받아서 사원번호, 사원이름, 부서코드, 부서이름, 부서위치 출력하기(단, 사용자 정의 레코드 타입 변수 사용하기)

먼저 사용자 정의 변수를 선언하는데, 이 때 필요한 칼럼들이 사원번호, 사원이름, 부서코드, 부서이름, 부서위치이다. 따라서 각 칼럼을 변수로 가져온다.

가져올 때 여러 테이블로부터 칼럼을 가져오는 것도 가능하다.

그리고 사원번호 입력을 받기 위해 p타입 대체 변수를 가져오는데 이를 v_empno에 저장한다. v_empno가 중복된다고 생각할 수도 있지만. 레코드 타입 내 v_empno에 접근하기 위해서는 v_emp를 통해서 접근해야 하기 때문에 상관없다.

그리고 다음처럼 select문을 작성하여 조인한 테이블로부터 행 데이터를 가져와 v_emp에 저장하고 이를 출력한다. select문은 natural 조인을 사용해도 상관 없다.

지역변수

변수의 범위에 대해서 알아보도록 하자. 일단 declare문은 중복이 가능하다. 중복 선언될 경우 각 declare문에서 선언된 변수의 범위가 중요하다. 이를 위해서 x, y, z라는 변수를 선언하여 어디까지 변수가 적용되고 있는지를 확인해보겠다.

먼저 z 변수를 생각해보자. z 변수는 내부 declare에서 선언되었다. 이 변수를 밖의 declare문에서 호출하면 어떻게 될까?

다음과 같은 오류메시지가 출력된다.

즉 안에서 선언된 변수는 해당 declare문까지만 적용되기 때문에 밖에서 사용될 수 없다.

밖의 declare문에서 호출된 z를 주석처리하고 결과를 다시 살펴보자.

다음과 같이 값들이 출력되는데, 이제 x와 y를 살펴보겠다.

먼저 x에 대해서 살펴보자. 일단 declare문이 중복 선언된 경우, 밖에서 선언된 변수는 안에서 사용할 수 있다. 처음에 선언된 x의 경우 1이지만, 내부 declare에서 3으로 수정된다. 그리고 내부에서 선언할 때와 밖에서 선언될 때 모두 3이 출력된다. 즉 새롭게 변수가 내부에서 선언되지 않았기 때문에 외부 변수와 내부 변수 모두 동일하다.

이번엔 y에 대해서 살펴보면, 밖에 선언된 변수가 안에서 중복 선언되고 있다. 이 경우 그 두 개는 다른 변수가 된다. 따라서 안에서 선언된 변수는 해당 declare문 내에서만 사용가능하고, 밖으로 나갈 경우 밖에서 선언된 변수에 범위에 들어간다. 따라서 y의 경우 서로 다른 값을 출력하고 있다.