4. Devon SQL Procedure 호출 (테이블용)

농부지기 2017. 1. 25. 17:28

[ 4. Devon SQL Procedure 호출 (테이블용) ]

1. 정의

- sql문장 from절에 Procedure_name을 놓고, 이 Procedure가 수행하고 결과가 레코드로

받으면 테이블처럼 된다.

2. SQL 사용예)

<statement name="retrieveParameterList">
	SELECT program_name
	     , ....
	  FROM TABLE(pkg_name.get_parameter_list(p_program_id => ${programId:integer}
	                                       , p_group_id => ${groupId:integer}
	                                       , p_request_id => ${requestId:integer}
	                                         )
	            )
</statement>

<statement name="retrieveParameterList">
	SELECT xcm_get_pkg.get_plan_date  AS plan_date 
	  FROM dual
</statement>

<statement name="retrieveParameterList">
	SELECT xcm_get_pkg.get_plan_id(${pgCode:integer}, NULL, NULL) 
	  FROM dual
</statement>

<statement name="retrieveCopyData">
	SELECT TO_NUMBER(x.text) division_id
 	     , xcm_get_pkg.get_plan_name(y.dt) plan_name
	     , xcm_get_pkg.get_plan_id(TO_NUMBER(x.text),TO_CHAR(y.dt,'YYYYMMDD')) plan_id
	 FROM TABLE(xjm_mail_get_pkg.seperate_data_return_table(${divisionList})) x
	     , ( SELECT NEXT_DAY(SYSDATE-7,2) dt
	           FROM dual
	       ) y
</statement>

3. From절에 위치할 Function 예) - name : get_parameter_list_copy

FUNCTION get_parameter_list_copy ( p_program_id IN NUMBER
, p_source_parameter_value IN VARCHAR2 DEFAULT NULL
) RETURN VARCHAR2
IS
v_procName VARCHAR2(30) := 'get_parameter_list';
--v_procDesc VARCHAR2(100) := 'get_parameter_list';

v_return_value                 VARCHAR2(500) := NULL;

CURSOR c_param     ( p_program_id                   IN     NUMBER
                   , p_source_parameter_value       IN     VARCHAR2
                   )
IS
    WITH w_programs AS (
          SELECT p.program_id                                    program_id
                ,p.program_name                                  program_name
          FROM   xjm_programs p
          WHERE  1 = 1
          AND    p.program_id = p_program_id
         )
        ,w_seq AS (
          SELECT LEVEL seq
          FROM   w_programs wp 
          CONNECT BY LEVEL <= wp.parameter_count
         )
    )
    SELECT wsp.parameter_list             name
          ,wsp.seq                        seq
          ,wsp.in_out                     in_out
          ,wsp.data_type                  data_type
          ,CASE
               WHEN (SUBSTR(wsp.recommend_default_value, 1, 2) = '%%') THEN wsp.recommend_default_value   
               --ELSE COALESCE(wpp.source_parameter_value, wpp.parameter_value, wsp.recommend_default_value)
               ELSE COALESCE(wpp.source_parameter_value, wpp.parameter_value)
           END                            value
    FROM   w_sys_parm     wsp
          ,w_program_parm wpp
          ,w_programs     wp
    WHERE  1 = 1
    AND    wsp.parameter_list = wpp.parameter_list (+)
    ORDER BY wsp.seq
;
BEGIN

FOR r_param        IN  c_param( p_program_id
                              , p_source_parameter_value
                              ) LOOP
    EXIT WHEN c_param%NOTFOUND;

v_return_value := v_return_value||','||r_param.value;

END LOOP;

RETURN SUBSTR(v_return_value,2);

4. From절에 위치할 Function 예) - name : seperate_data_return_table

CREATE OR REPLACE PACKAGE XSCM.xjm_mail_get_pkg IS

TYPE seperateTabTyp                 IS TABLE  OF seperateRecTyp  INDEX BY BINARY_INTEGER;

FUNCTION  seperate_data_return_table    ( p_data                         IN     VARCHAR2
                                            , p_key                          IN     VARCHAR2 DEFAULT NULL
                                            , p_delimiter                    IN     VARCHAR2 DEFAULT xjm_common_pkg.C_COMMA
                                            ) RETURN xjm_mail_get_pkg.arraySepRec PIPELINED;
END xjm_mail_get_pkg;

n_seperate_cnt                 NUMBER         := 0;
i                              NUMBER         := 0;
BEGIN

n_seperate_cnt := 5;

IF (n_seperate_cnt > 0) THEN
    FOR i        IN  1..n_seperate_cnt LOOP
        t_sepRec(i).key := p_key;
        PIPE ROW(t_sepRec(i));

END LOOP;
END IF;

RETURN;
EXCEPTION
WHEN NO_DATA_NEEDED THEN
NULL;

WHEN OTHERS THEN
    dbms_output.put_line(CHR(10)||
        '[E] >>> Error Status Display Start <<<'||CHR(10)||
        '[E] Procedure : '|| C_MODULE_NAME||'.'||v_procName||':EXCEPTION:OTHERS'     ||CHR(10)||
        '[E] Date : '|| TO_CHAR(SYSDATE, xjm_common_pkg.C_DEFAULT_DISPLAY_DATE_TIME) ||CHR(10)||
        '[E] ERRM : '|| SQLERRM ||CHR(10)||
        '[E] >>> Error Status Display End <<<'||CHR(10)
        );

RAISE;
END seperate_data_return_table;

저작자표시 (새창열림)