DB Oracle 뷰(View) 테이블 생성 및 저장 프로시저 사용하는 방법

DB Oracle 뷰(View) 테이블 생성 및 저장프로시저 사용하는 방법

▶ 우선 시스템(스키마 유저)의 권한으로 SHOP(스키마 유저)의 MEMBERTBL 테이블을 조회해보도록 하겠습니다.

SELECT * FROM SHOP.MEMBERTBL;

MEMBERID MEMBE MEMBERADDRESS       
-------- ----- --------------------
Son      손흥민   잉글랜드 토트넘     
Lee      이강인   스페인 마요르카     
Hwang    황희찬   잉글랜드 울버햄튼   
Kim      김민재   이탈리아 나폴리

▶ SELECT로 조회 했을 시 MEMBERID, MEMBERNAME, MEMBERADDRESS의 칼럼명으로 데이터가 조회되는 것을 확인할 수 있습니다.
현재 시스템이 아닌 다른 유저에게 가변적이지 않으면서 현재 테이블을 보여줄 수 있는 뷰 테이블을 생성해주도록 하겠습니다.

▶ CREATE 구문을 통해 SHOP.MEMBERTBL_VIEW라는 명칭으로 뷰테이블을 정의하고 SLECT 문을 통해서 해당 테이블을 조회했을 때 나오는 결과 값에 대해서 VIEW 테이블을 생성합니다.

CREATE VIEW SHOP.MEMBERTBL_VIEW AS
SELECT MEMBERNAME, MEMBERADDRESS FROM SHOP.MEMBERTBL ;

View SHOP.MEMBERTBL_VIEW이(가) 생성되었습니다.

▶ SHOP.MEMBERTBL에서 조회한 MEMBERNAME 과 MEBERADDRESS 속성 값의 결과 조회를 통해서 뷰테이블이 생성되었습니다.

▶ SHOP(스키마 유저)에서 자기 자신의 MEMBERTBL 테이블이 아닌 시스템(스키마 유저)에서 생성한 테이블 MEMBERTABL_VIEW라는 뷰 테이블 즉 가상의 테이블을 조회했을 때 동일한 결과 값이 출력되는 것을 확인할 수 있습니다.

SELECT * FROM MEMBERTBL_VIEW;

MEMBE MEMBERADDRESS       
----- --------------------
손흥민   잉글랜드 토트넘     
이강인   스페인 마요르카     
황희찬   잉글랜드 울버햄튼   
김민재   이탈리아 나폴리

▶ 이처럼 뷰테이블을 활용하여 가시성을 확보하는 작업이나 결과를 나타내는 쿼리문을 작성할 경우 사용하면 SQL문 작성시 많은 도움이 될 거 같습니다.

SELECT COUNT(*) FROM MEMBERTBL;

  COUNT(*)
----------
         4

▶ 다음으로는 MEMBERTBL의 COUNT 함수를 통해서 행의 갯수를 출력하면 4개라는 결과 값을 가져옵니다.

SELECT COUNT(*) FROM PRODUCTTBL;

▶ 반대로 PRODUCTTBL테이블의 COUNT 함수를 통해서 행의 갯수를 출력하면 3개라는 결과 값을 가져옵니다.

▶ 이 두테이블의 행의 결과 값을 덧셈하기 위해서 저장프로시저를 통해서 결과 값을 저장하여 실행 시 결과 값만 보여주도록 하는 기능을 사용할 수 있게 됩니다.

CREATE PROCEDURE SHOP.MYPROC AS
var1 INT;
var2 INT;
BEGIN
    SELECT COUNT(*) INTO var1 FROM SHOP.MEMBERTBL;
    SELECT COUNT(*) INTO var2 FROM SHOP.PRODUCTTBL;
    DBMS_OUTPUT.PUT_LINE(var1+var2);
END;

▶ CREATE PROCEDURE를 통해서 SHOP.MYPROC라고 명칭을 정하고 var1, var2에 각각의 인자 값을 INT로 지정합니다. BEGIN ~ END 구문에서 SHOP.MEMBERTBL의 테이블에서 COUNT(*) 한 결과 값을 var1에 지정하고 반대로 SHOP.PRODUCTTBL의 테이블에서 COUNT(*) 한 결과 값을 var2에 지정하여 저장합니다.

SET SERVEROUTPUT ON;

▶ SET SERVEROUTPUT ON을 통해서 활성화 합니다.

EXECUTE MYPROC;

7

PL/SQL 프로시저가 성공적으로 완료되었습니다.

▶ EXECUTE MYPROC라는 프로시저 명칭을 실행하게 되면 3+7의 결과 값을 바로 출력하여 보이게 됩니다.

 

2022.12.22 - [Programming and Database/SQL] - DB Oracle 스키마 생성하는 방법

DB Oracle 스키마 생성하는 방법

2022.12.22 - [Programming and Database/SQL] - DB Oracle 테이블 생성, 데이터 입력/조회하는 방법(1)

DB Oracle 테이블 생성, 데이터 입력/조회하는 방법(1)

2022.12.23 - [Programming and Database/SQL] - DB Oracle 테이블 생성, 데이터 입력/조회하는 방법(2)

DB Oracle 테이블 생성, 데이터 입력/조회하는 방법(2)

 

PS. 포스팅의 내용은 제 기준으로 작성한 것이기 때문에 참고해서 보시면 감사하겠습니다.

포스팅 과정의 오류 및 오타 그리고 궁금한 점에 대해서는 댓글 남겨주세요.