create or replace PROCEDURE DEPT_NAME_SEARCH (
V_Cursor OUT SYS_REFCURSOR, CDEPTNM IN VARCHAR2
)AS tq varchar2(4000); tempdeptid varchar2(4000);
tempdeptids varchar2(4000);
tempdeptnm varchar2(4000);
tempdeptlist varchar2(4000);
tempdeptlists varchar2(4000);
deptids varchar2(4000);
deptnms varchar2(4000);
tempcode varchar2(4000);
TMP_CNT int;
Cursor deptIdCursor IS select dept_id, dept_nm into tempdeptid, tempdeptnm from cm_dept where dept_nm like '%' || CDEPTNM || '%';
Cursor TMDCursor IS select VALUETMD, LABELTMD from TMD;
BEGIN select count(*) into TMP_CNT from user_tables where table_name='TMD';
if TMP_CNT > 0 then
EXECUTE IMMEDIATE 'DROP TABLE TMD';
select 0 into TMP_CNT from dual;
end if;
if TMP_CNT = 0 then
tq := 'CREATE GLOBAL TEMPORARY TABLE TMD (
VALUETMD VARCHAR2(1000), --id
LABELTMD VARCHAR2(1000), --name
messageWordKo VARCHAR2(1000),
messageWordEn VARCHAR2(1000),
messageWordZh VARCHAR2(1000),
messageExplain VARCHAR2(1000),
messageId VARCHAR2(1000) ) ON COMMIT DELETE ROWS';
EXECUTE IMMEDIATE tq; end if;
open deptIdCursor;
loop
Fetch deptIdCursor into deptids, deptnms;
exit when deptIdCursor%notFound;
insert into TMD (VALUETMD, LABELTMD , messageId ) values (deptids, deptnms, deptnms); end loop; close deptIdCursor;
open TMDCursor;
loop
Fetch TMDCursor into deptids, deptnms;
exit when TMDCursor%notFound;
select deptids into tempdeptids from dual;
select deptnms into tempdeptlists from dual;
while tempdeptids != 'COMP001' LOOP
select upper_dept_id into tempdeptids from cm_dept where dept_id = tempdeptids;
select dept_nm into tempdeptlist from cm_dept where dept_id = tempdeptids;
tempdeptlists := tempdeptlist || ' > ' || tempdeptlists;
END LOOP;
tq := 'update TMD set LABELTMD = ''' || tempdeptlists ||'''
,messageWordKo = ''' || tempdeptlists ||'''
,messageWordEn = ''' || tempdeptlists ||'''
,messageWordZh = ''' || tempdeptlists ||'''
,messageExplain = ''' || tempdeptlists ||'''
,messageId = ''' || tempdeptlists ||'''
where VALUETMD = ''' ||deptids|| ''' ' ;
--DBMS_OUTPUT.PUT_LINE(tq);
EXECUTE IMMEDIATE tq;
end loop;
close TMDCursor;
tq := ' SELECT
VALUETMD as value,
LABELTMD as label
FROM TMD';
OPEN V_Cursor FOR tq;
END DEPT_NAME_SEARCH;
V_Cursor OUT SYS_REFCURSOR, CDEPTNM IN VARCHAR2
)AS tq varchar2(4000); tempdeptid varchar2(4000);
tempdeptids varchar2(4000);
tempdeptnm varchar2(4000);
tempdeptlist varchar2(4000);
tempdeptlists varchar2(4000);
deptids varchar2(4000);
deptnms varchar2(4000);
tempcode varchar2(4000);
TMP_CNT int;
Cursor deptIdCursor IS select dept_id, dept_nm into tempdeptid, tempdeptnm from cm_dept where dept_nm like '%' || CDEPTNM || '%';
Cursor TMDCursor IS select VALUETMD, LABELTMD from TMD;
BEGIN select count(*) into TMP_CNT from user_tables where table_name='TMD';
if TMP_CNT > 0 then
EXECUTE IMMEDIATE 'DROP TABLE TMD';
select 0 into TMP_CNT from dual;
end if;
if TMP_CNT = 0 then
tq := 'CREATE GLOBAL TEMPORARY TABLE TMD (
VALUETMD VARCHAR2(1000), --id
LABELTMD VARCHAR2(1000), --name
messageWordKo VARCHAR2(1000),
messageWordEn VARCHAR2(1000),
messageWordZh VARCHAR2(1000),
messageExplain VARCHAR2(1000),
messageId VARCHAR2(1000) ) ON COMMIT DELETE ROWS';
EXECUTE IMMEDIATE tq; end if;
open deptIdCursor;
loop
Fetch deptIdCursor into deptids, deptnms;
exit when deptIdCursor%notFound;
insert into TMD (VALUETMD, LABELTMD , messageId ) values (deptids, deptnms, deptnms); end loop; close deptIdCursor;
open TMDCursor;
loop
Fetch TMDCursor into deptids, deptnms;
exit when TMDCursor%notFound;
select deptids into tempdeptids from dual;
select deptnms into tempdeptlists from dual;
while tempdeptids != 'COMP001' LOOP
select upper_dept_id into tempdeptids from cm_dept where dept_id = tempdeptids;
select dept_nm into tempdeptlist from cm_dept where dept_id = tempdeptids;
tempdeptlists := tempdeptlist || ' > ' || tempdeptlists;
END LOOP;
tq := 'update TMD set LABELTMD = ''' || tempdeptlists ||'''
,messageWordKo = ''' || tempdeptlists ||'''
,messageWordEn = ''' || tempdeptlists ||'''
,messageWordZh = ''' || tempdeptlists ||'''
,messageExplain = ''' || tempdeptlists ||'''
,messageId = ''' || tempdeptlists ||'''
where VALUETMD = ''' ||deptids|| ''' ' ;
--DBMS_OUTPUT.PUT_LINE(tq);
EXECUTE IMMEDIATE tq;
end loop;
close TMDCursor;
tq := ' SELECT
VALUETMD as value,
LABELTMD as label
FROM TMD';
OPEN V_Cursor FOR tq;
END DEPT_NAME_SEARCH;
댓글
댓글 쓰기