저장 프로시저(Stored Procedure) 활용

AI Designer 설명 > AI Designer 활용 > 데이터베이스 활용

▶

사용 개요

데이터베이스의 저장 프로시저(Stored Procedure)를 호출하여 보고서 데이터로 활용할 수 있도록 지원합니다.
저장 프로시저를 활용하면 SQL 구문 실행에 따른 시간 단축 및 네트웍 트래픽을 줄일 수 있습니다.

▶

저장 프로시저의 구분

1. SQL Server Type
- 일반적인 SQL 구문을 통하여 레코드 셋을 추출합니다.( MS SQL Server, Sybase 등)

ex 1) Input 파라미터가 없을 경우

ALTER procedure sp_customer_list()
result(id integer,company_name char(35))
begin
    select id,company_name from customer
end

ex 2) Input 파라미터가 있을 경우

ALTER procedure sp_customer_products(inout customer_id integer)
result(id integer,quantity_ordered integer)
begin
    select product.id,sum(sales_order_items.quantity)
    from product, sales_order_items, sales_order
    where sales_order.cust_id = customer_id
    and sales_order.id = sales_order_items.id
    and sales_order_items.prod_id = product.id
    group by product.id
end

2. Oracle Type
- 일반적인 SQL 구문에 Ref Cursor를 추가하여 레코드 셋을 추출합니다.

ex 1) Input 파라미터가 있을 경우

create or replace package Employee as
cursor c1 is select * from emp;
type empCur is ref cursor return c1%rowtype;

procedure GetEmpData(indeptno in NUMBER, EmpCursor in out empCur);
end Employee;

procedure GetEmpData(indeptno in NUMBER, EmpCursor in out empcur) is
begin
open EmpCursor for
select * from emp where deptno = indeptno;
end GetEmpData;

▶

저장 프로시저 자료형(활용 가능한 자료형)

데이터베이스 자료형과 보고서 소스(jsp, asp, java, aspx) 자료형과의 매핑을 위해 내부적으로 문자형, 정수형, 실수형으로 범주화된 단순 자료형 ( 데이터 타입 ) 을 사용하고 있으며 , 이 규칙은 스토어드 프로시저에서도 동일하게 적용됩니다.
자료형은 다음 식별자 중의 하나를 사용해야 합니다.

식별자	자료형	참고
문자형	Char
정수형	Integer
실수형	Float
Cursor	Cursor	Oracle

▶

저장 프로시저 사용 방법

1. 저장 프로시저 호출
1-1. Oracle Type(Input Parameter가 있는 경우)
- 호출할 저장 프로시저명을 선택 후 실행합니다.

- Oracle 저장 프로시저는 반드시 사용자가 파라미터 갯수를 입력해야 하며, 입력 시 Cursor Type 파라미터를 무조건 포함시킵니다.

파라미터 갯수(n+1) = Input 파라미너 갯수(n) + Cursor 갯수(1)

- 파라미터의 데이터형과 값을 입력합니다. 인덱스별로 '파라미터 적용' 버튼을 클릭하여 각각 적용해야 합니다.
- 파라미터 값에는 고정된 상수나 AI Designer 영역에 이미 선언된 Parameter를 사용할 수 있습니다
  (※ 사전에 파라미터 설정이 되어 있지 않았더라도 특정 파라미터명을 값에 등록하면 자동 추가됩니다.)
- 인덱스 영역의 모든 파라미터 값 셋팅을 완료한 뒤 하단의 '확인' 버튼을 누릅니다.

- 파라미터에 기본 값 설정 후 '확인' 버튼을 클릭합니다.
  (※ 기본 값을 넣을 필요가 없는 경우 빈 공란으로 처리하셔도 됩니다.)

- Input 파라미터 값에 따라 여러 개의 레코드 셋을 생성하여 활용할 수 있으므로 구분할 수 있는 식별자를 입력해 줍니다.
  (※ 식별자는 영문 또는 숫자만 사용합니다.)

- 생성된 저장프로시저 데이터 셋은 Database 정보창 해당 프로시저명 하단에 생성됩니다.
- 이후 사용 방법은 쿼리변수 활용법과 동일합니다.
☞ AI Designer 설명 > AI Designer 활용 > 데이터베이스 활용 > 쿼리디자이너 활용 > ▶ 쿼리 변수를 보고서 작업창에 적용하기 참조

1.2 SQL Server Type(Input Parameter가 있는 경우)
- Oracle Type(Input Parameter가 있는 경우) 사용 방법과 동일(별도의 파라미터 갯수 입력 창 없음)

▶

저장 프로시저 활용 시 유의사항

1. Input 파라미터만 지원(Output 파라미터는 지원하지 않습니다.)

2. char, integer, float 데이타형만 지원
아래의 예와 같이 데이타형이 %TYPE으로 선언된 파라미터는 지원하지 못함.
(테이블이 수정될 때마다 프로시저를 수정해주어야 하는 불편함이 있음)

예) v_cd_plant_delivery PO_ASN.CD_PLANT_DELIVERY%TYPE; = Input 파라미너 갯수(n) + Cursor 갯수(1)

3. pakage 내에 생성된 프로시저만 지원
pakage 로 구성하지 않은 프로시저는 인식하지 못합니다.

예)
CREATE OR REPLACE PACKAGE BODY PKG_ASN_CONTROL
AS
v_asn_cnt number; //보통 이런 인자는 프로시저 내에 선언하지만 아래 ① 의 위치(프로시저 내)에 선언하면 에러 발생

PROCEDURE GetAsnData(in_xasn_id in VARCHAR2, in_xpo_no in VARCHAR2, AsnCursor out asnCur)
IS

①

   BEGIN
      SELECT COUNT(*)
      INTO v_asn_cnt
      FROM PO_ASN_PRINT
      WHERE XASN_ID = in_xasn_id;
   PEN AsnCursor FOR
      SELECT * FROM PO_ASN_PRINT WHERE XASN_ID = in_xasn_id;
   END GetAsnData;
END PKG_ASN_CONTROL;