[이것이 오라클이다] 10장 2교시: [Oracle] 저장 프로시저 실습, 저장 프로시저 특징

* Youtube Link - https://www.youtube.com/watch?v=PB6i20Fwas8&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=28

 

예제 1) 입력 파라미터와 기본 변수를 설정한 프로시저

저장 프로시저를 생성할 때 스크립트 실행을 통해서 통째로 실행을 해야 한다.

여러 SQL문들을 실행하기 때문에 한꺼번에 실행을 해야 한다.

() 안에 파라미터를 선언하고

AS 다음에 변수를 선언한다.

선언한 파라미터와 변수를 프로시저 내에서 사용할 수 있다.

BEGIN END 안에 프로시저를 선언한다.

- 시퀀스 생성

CREATE SEQUENCE userSEQ; 명령어를 통해서 userSEQ라는 이름의 시퀀스를 생성할 수 있다.

userSEQ.NEXTVAL 함수를 통해서 userSEQ의 그 다음 변수를 호출할 수 있다.

- 출력 파라미터의 활용

그리고 익명 프로시저를 통해서

기존의 출력 파라미터를 사용한 프로시저를 호출해본다.

미리 출력 파라미터를 받을 변수를 선언해준다.

그리고 DMBS_OUTPUT.PUT_LINE(outData) 를 통해서 콘솔에 출력한다.

- 시퀀스를 출력하는 저장 프로시저

익명 프로시저를 실행하면 할 수록

다음 시퀀스를 콘솔에 출력한다.

- IF/ELSE 문

- 띠 출력 프로시저

- 구구단 프로시저

v_str = '' 초기화를 실행하고

2중 WHILE 반복문을 통해서

v_str에 계속 append를 시키는 구조이다.

- 회원 여부 확인 프로시저

DECLARE로 선언한 변수를

프로시저의 아웃풋 파라미터로 준다.

그리고 결과를 아웃풋 파라미터에 저장을 하고

출력을 해본다.

'은지원'은 회원이다.

- 오류가 발생하는 경우

오류 종류의 확인이 필요하다.

- 오류처리

오류를 처리하니

'그런 사람 없어요 ㅠㅠ'라는 내용이 콘솔에 출력된다.

- 입력 출력 파라미터 설정

pio_userName IN OUT NVARCAHR2 로 설정을 하면

이 변수를 입력으로 사용할 수도 있고'

출력으로도 사용할 수 있다.

변수를 재할당하는 것이다.

한 변수에 용도가 두개인 것이다.

- USER_OBJECTS

USER_OBJECTS 에서 만든 OBJECT의 목록을 확인할 수 있다.

OBJECT_TYPE = 'PROCEDURE' 라는 조건을 주면

프로시저만 볼 수 있다.

- USER_SOURCE

USER_SOURCE를 통해서 TEXT로 저장된 소스들을 각 행별로 볼 수 있다.

계층으로 이렇게 처리를 하면

각 행에 저장된 변수들을 실행할 수 있다.

- 사용자명 암호화 프로시저

사용자명 암호화

- 문자열 프로시저 

문자열 소스를 만들어서

EXECUTE IMMEDIATE DBMS_DDL.WRAP(DDL => v_source);

명령어를 통해서 WRAP 패키지 함수를 사용해서 DDL문으로 변경한다.

그리고 프로시저의 소스코드를 확인해보면 암호화가 되어있는 것을 볼 수 있다.

- 테이블을 파라미터로 넘겨서 실행하는 프로시저

이렇게 없는 테이블을 그대로 쿼리로 사용을 하면 오류가 발생한다.

테이블이 없기 때문에 먼저 오류가 발생하는 것이다.

문자열 테이블 이름을 전달해서

EXECUTE IMMEDIATE v_sql INTO v_count; 명령어로 처리를 해서

쿼리를 실행할 수 있다.

최종적으로 전달된 문자열 테이블 이름의 테이블을 조회하는 것을 볼 수 있다.

- 변수의 초기화 및 변수들 데이터 삽입

v_constant CONSTANT NCHAR(3) := '-->'; 명령어를 통해서 상수를 바로 초기화하였다.

또한 v_userData usertbl%ROWTYPE; 명령어를 사용해서 usertbl의 모든 열 타입을 v_userData에 넣을 수 있다. 그러면 v_userData.userName, v_userData.addr 등 다양하게 변수가 생길 수 있다.

SELECT userName, addr, height INTO v_userData.userName, v_userData.addr, v_userData.height FROM userTBL WHERE userID = pi_userid;

명령어를 통해서 해당 조회된 결과를 변수들에 삽입할 수 있다.

- IS RECORD

TYPE myRecordType IS RECORD (r_userName NVARCHAR2(20), r_addr NCHAR(2), r_height NUMBER(3));

v_record myRecordType;

이렇게 변수의 타입을 생성하여서 사용할 수 있다.

그러면 v_record.r_userName, v_record.r_addr, v_record.r_height 이렇게 사용할 수 있다.

- 타입의 Collection

Collection을 타입으로 만들 수 있다.

TYPE myVarrayType IS VARRAY(3) OF NUMBER(10); -- NUMBER 타입의 배열 3개를 지정한다.

TYPE myNestType IS TABLE OF NVARCHAR2(10); -- 갯수와 상관없이 배열을 만들 수 있다.

TYPE myAssocType IS TABLE OF NUMBER(5) INDEX BY STRING(10); -- INDEX로 STRING(10)을 줄 수 있다. 인덱스가 있는 자료구조의 타입을 만들 수 있다.

 

그리고 해당 타입의 변수를 초기화할 수 있다.

v_varray := myVarrayType(10, 20, 30); -- NUMBER(10) 배열 변수를 초기화 하였다.

v_nest := myNestType('이것이', '오라클', '학습 중'); -- NVARCHAR2(10) 배열 변수를 초기화하였다.

v_assoc('짜장') := 4500; -- 변수에 '짜장' : 4500 를 입력하였다.

v_assoc('피자') := 12000; -- 변수에 '피자' : 12000 를 입력하였다.

v_assoc('치킨') := 19000; -- 변수에 '치킨' : 19000 를 입력하였다.

 

10.1.2 저장 프로시저의 특징

 

프로시저를 사용하면 이미 쿼리가 서버에 저장되어 있기 때문에

간편한 코드로 쿼리를 계속 실행할 수 있다.

또한 긴 쿼리를 전송하는 것 보다 이미 프로시저를 생성해 놓았으므로 네트워크 전송량도 감소할 수 있어

시스템 성능이 향상된다.

 

감사합니다.