[이것이 오라클이다] 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 저장 프로시저의 특징
프로시저를 사용하면 이미 쿼리가 서버에 저장되어 있기 때문에
간편한 코드로 쿼리를 계속 실행할 수 있다.
또한 긴 쿼리를 전송하는 것 보다 이미 프로시저를 생성해 놓았으므로 네트워크 전송량도 감소할 수 있어
시스템 성능이 향상된다.
감사합니다.