[TeraData] 한가지 커서로 여러 조건 적용.
REPLACE PROCEDURE LDVIDB.UP_NOTICE_GETNOTICELIST --Notice_GetNoticeList
(
IN SearchOp VARCHAR(7)
,IN SearchText VARCHAR(1000)
)
/*
수정일: 2014-1-10
작성자: 김우종
시스템명: VIP
업무명: 시스템관리
화면ID: CM0100M
메뉴: 시스템관리 -> 공지사항 관리 -> 공지사항
SP설명: 권한 코드
SP실행: CALL LDVIDB.UP_NOTICE_GETNOTICELIST('ALL','명동점');
*/
DYNAMIC RESULT SETS 1
BEGIN
DECLARE STRSQL VARCHAR(8000);
SET STRSQL = ' ';
IF SearchOp = 'ALL' THEN-- 전체
SET STRSQL =
'SELECT
notice_id
,sys_cd
, CASE a.sys_cd WHEN ''00'' THEN ''전체''
ELSE COALESCE ((SELECT b_code_nm FROM LDVMDB.VI_BASE_CODE_V WHERE p_code = ''CM006'' AND use_yn = ''Y'' AND b_code = a.sys_cd), '''') END sys_nm
,Ntitle
, usr_id
, SUBSTR(startdt,1, 4) || ''-'' || SUBSTR(startdt, 5,2) || ''-'' || SUBSTR(startdt, 7,2) || '' ~ '' ||
SUBSTR(enddt,1, 4) || ''-'' || SUBSTR(enddt, 5,2) || ''-'' || SUBSTR(enddt, 7,2) as term
FROM LDVMDB.VI_NOTICE_V a
WHERE use_yn = ''Y''
ORDER BY notice_id DESC; ';
END IF;
IF SearchOp = 'TITLE' THEN-- 제목
SET STRSQL =
'SELECT
notice_id
,sys_cd
, CASE a.sys_cd WHEN ''00'' THEN ''전체''
ELSE COALESCE ((SELECT b_code_nm FROM LDVMDB.VI_BASE_CODE_V WHERE p_code = ''CM006'' AND use_yn = ''Y'' AND b_code = a.sys_cd), '''') END sys_nm
, NTitle
, usr_id
, SUBSTR(startdt,1, 4) || ''-'' || SUBSTR(startdt, 5,2) || ''-'' || SUBSTR(startdt, 7,2) || '' ~ '' ||
SUBSTR(enddt,1, 4) || ''-'' || SUBSTR(enddt, 5,2) || ''-'' || SUBSTR(enddt, 7,2) as term
FROM LDVMDB.VI_NOTICE_V a
WHERE
use_yn = ''Y'' AND NTitle LIKE ''%' || SearchText || '%''
ORDER BY notice_id DESC;';
END IF;
IF SearchOp = 'CONTE' THEN-- 내용
SET STRSQL =
'SELECT
notice_id
,sys_cd
, CASE a.sys_cd WHEN ''00'' THEN ''전체''
ELSE COALESCE ((SELECT b_code_nm FROM LDVMDB.VI_BASE_CODE_V WHERE p_code = ''CM006'' AND use_yn = ''Y'' AND b_code = a.sys_cd), '''') END sys_nm
, NTitle
, usr_id
, SUBSTR(startdt,1, 4) || ''-'' || SUBSTR(startdt, 5,2) || ''-'' || SUBSTR(startdt, 7,2) || '' ~ '' ||
SUBSTR(enddt,1, 4) || ''-'' || SUBSTR(enddt, 5,2) || ''-'' || SUBSTR(enddt, 7,2) as term
FROM LDVMDB.VI_NOTICE_V a
WHERE
use_yn = ''Y'' AND contents LIKE ''%' || SearchText || '%''
ORDER BY notice_id DESC;';
END IF;
IF SearchOp = 'USER' THEN -- 사용자
SET STRSQL =
'SELECT
notice_id
,sys_cd
, CASE a.sys_cd WHEN ''00'' THEN ''전체''
ELSE COALESCE ((SELECT b_code_nm FROM LDVMDB.VI_BASE_CODE_V WHERE p_code = ''CM006'' AND use_yn = ''Y'' AND b_code = a.sys_cd), '''') END sys_nm
, NTitle
, usr_id
, SUBSTR(startdt,1, 4) || ''-'' || SUBSTR(startdt, 5,2) || ''-'' || SUBSTR(startdt, 7,2) || '' ~ '' ||
SUBSTR(enddt,1, 4) || ''-'' || SUBSTR(enddt, 5,2) || ''-'' || SUBSTR(enddt, 7,2) as term
FROM LDVMDB.VI_NOTICE_V a
WHERE
use_yn = ''Y'' AND usr_id LIKE ''%' || SearchText || '%''
ORDER BY notice_id DESC;';
END IF;
IF SearchOp = 'Total' THEN -- 전체 공지만 조회
SET STRSQL =
'SELECT
notice_id
,sys_cd
, CASE a.sys_cd WHEN ''00'' THEN ''전체''
ELSE COALESCE ((SELECT b_code_nm FROM LDVMDB.VI_BASE_CODE_V WHERE p_code = ''CM006'' AND use_yn = ''Y'' AND b_code = a.sys_cd), '''') END sys_nm
, NTitle
, usr_id
, SUBSTR(startdt,1, 4) || ''-'' || SUBSTR(startdt, 5,2) || ''-'' || SUBSTR(startdt, 7,2) || '' ~ '' ||
SUBSTR(enddt,1, 4) || ''-'' || SUBSTR(enddt, 5,2) || ''-'' || SUBSTR(enddt, 7,2) as term
FROM LDVMDB.VI_NOTICE_V a
WHERE
use_yn = ''Y'' AND usr_id LIKE ''%' || SearchText || '%''
AND sys_cd = 00
ORDER BY notice_id DESC;';
END IF;
IF SearchOp = 'Sys' THEN -- 시스템 공지만 조회
SET STRSQL =
'SELECT
notice_id
,sys_cd
, CASE a.sys_cd WHEN ''00'' THEN ''전체''
ELSE COALESCE ((SELECT b_code_nm FROM LDVMDB.VI_BASE_CODE_V WHERE p_code = ''CM006'' AND use_yn = ''Y'' AND b_code = a.sys_cd), '''') END sys_nm
, NTitle
, usr_id
, SUBSTR(startdt,1, 4) || ''-'' || SUBSTR(startdt, 5,2) || ''-'' || SUBSTR(startdt, 7,2) || '' ~ '' ||
SUBSTR(enddt,1, 4) || ''-'' || SUBSTR(enddt, 5,2) || ''-'' || SUBSTR(enddt, 7,2) as term
FROM LDVMDB.VI_NOTICE_V a
WHERE
use_yn = ''Y''
AND usr_id LIKE ''%' || SearchText || '%''
AND sys_cd <> ''00''
ORDER BY notice_id DESC;';
END IF;
BEGIN
DECLARE CUR1 CURSOR WITH RETURN ONLY TO CLIENT FOR SQL_STATEMENT;
PREPARE SQL_STATEMENT FROM STRSQL;
OPEN CUR1;
END;
END;