참고

2020-09-01-02)저장프로시져.sql
2020-09-01-03)저장프로시져2.sql

저장프로시져 (= 프로시져)

• 절차형 SQL을 활용하여 특정 기능을 수행하는 일종의 트랜잭션 언어
• 프로시져: 하나의 결과를 도출하기 위한 코드의 집합
• 비지니스 로직을 처리하는 모듈을 작성하여 컴파일하고 해당 기능이 필요한 곳에서 함수처럼 호출 할 수 있는 단위
• 컴파일된 프로시져는 캐시메모리에 저장되어 처리 속도가 빠르고, 네트워크의 트래픽 감소 효과
• 사용자에게 내부 모듈을 감추기 때문에 보안확보
• 프로시져는 반환값이 없으나, 매개변수를 통해서 빼낼 수 있다.
• 용도: 시스템의 일일 마감 작업, 일괄(Batch) 작업
• 오라클 - 프로시져 : 자바 - void 메서드
• 오라클 - function: 자바 - return 있는 메서드

사용형식

생성

• 
• 변수명 : 매개변수명으로 보통 P_로 시작
• 모드
  • IN -> 입력용 (default값: 생략시 IN으로 간주)
  • OUT -> 출력용
  • INTOUT->입/출력 공용 (쓰지마)
• 타입명 : 매개변수의 타입으로 크기 설정 안함

실행

• EXEC | EXECUTE 프로시져명[(매개변수|값,...)];
• PL/SQL에서 프로시져 실행시 'EXEC|EXECUTE'는 생략
• SELECT문의 WHERE절에 쓸 수 없음
  • 반환값이 없어서 데이터가 들어가지 않음
  • 함수는 사용 가능

예시1 - IN 매개변수

1. 회원테이블에서 'd001'회원의 회원번호를 입력받아 회원명,주소,직업을 출력하는 프로시져 작성

• 매개변수 : 회원번호
• 하는일: 출력 -> 반환안해도 되니 프로시져 사용 (반환이 필요하다면 function)
• 
• 
• 
• 

2. 상품테이블에서 분류코드 'P301'에 속한 상품의 상품명, 분류코드, 매입가격, 매출가격을 출력하는 프로시져

잘못된 예시

• 
• 우선 컴파일은 됨
• 
• 
• P301의 물품 종류가 4개임, 변수는 1개만 담을 수 있는데 4개를 담으려니 오류가 나는 것

코드 수정

-PROD_LGU를 PROD_ID로 수정

• 
• 
• 
• 결과가 하나 이상 생기면 실행문에 커서를 만들어서 한개씩 프로시져의 매개변수로 넣어줌

3. 2005년 6월 가장 많이 판매된 상품의 상품코드와 수량을 입력받아 재고수불 테이블에 해당제품에 대한 재고를 수정하시오

프로시져 만들기 전에 2005년 6월달에 제일 많이 판매된 상품 정보를 얻어야함

• 

프로시져 생성

• 
• 매개변수 NUMBER에 크기 지정해주면 100% 오류
• 복합키로 구성된 테이블: 키가 3개라면 WHERE절도 3개
  • 이유: 3개 모두를 가지고 저장주소가 만들어지기때문에 하나라도 빠지면 오래걸림

프로시져 실행

• 
• ROWNUM: ORDER BY랑 같이 못 씀. WHERE절 먼저 실행되기때문

예시2 - OUT 매개변수

• 실행이 BLOCK에 기술

1. 회원번호를 입력받아 이름과 직업을 출력하는 프로시져 작성

• 단, 이름과 직업은 OUT매개변수 사용

프로시져 생성

• 

프로시져 실행

• OUT이 있으니 변수에 담아줘야하는데, 일반PL/SQL에서는 변수를 쓸수 없으니 익명블록을 사용한다
• 
• 

결과

•