新问题,入参在查询REGION_USER 表时,查询出多条记录,而GION_USER表的dn是作为插入条件的,请教该怎么处理,谢谢!
CREATE OR REPLACE PROCEDURE temp_jiankong1
(vdblink in varchar2 )is
t_A1 varchar2(100);
t_A2 varchar2(100);
t_A3 varchar2(100);
t_A4 varchar2(100);
t_A5 varchar2(100);
t_A6 varchar2(100);
t_A7 varchar2(100);
t_A8 varchar2(100);
t_A9 varchar2(100);
t_A10 varchar2(100);
t_A11 varchar2(100);
t_dblink varchar2(100);
t_day varchar2(100);
t_name varchar2(100);
t_dn varchar2(100);
t_region varchar2(100);
t_vdn varchar2(100);
T_IERROR VARCHAR2(300);
T_EXISTS NUMBER;
type t_cur is ref cursor;
cur t_cur;
t_sql varchar2(5000);
BEGIN
--execute immediate 'truncate table temp_yxf';
t_sql:='select
''表空间名称'' A1,
a.a1 A2,
''表空间大小M'' A3,
round(b.b2 / 1024 / 1024,2) A4,
''未使用M'' A5,
ROUND(A.a2 / 1024 / 1024,2) A6 ,
''已使用M'' A7,
ROUND((b.b2 - a.a2) / 1024 / 1024,2) A8,
''表空间利用率'' A9,
ROUND((b.b2 - a.a2) / b.b2 * 100, 2) A10,
sysdate A11
from (select tablespace_name a1, sum(nvl(bytes, 0)) a2
from dba_free_space@'||vdblink||' '||
'group by tablespace_name) a,
(select tablespace_name b1, sum(bytes) b2
from dba_data_files@'||vdblink||' '||
'group by tablespace_name) b,
(select tablespace_name c1, contents c2, extent_management c3
from dba_tablespaces@'||vdblink||') c
where a.a1 = b.b1
and c.c1 = b.b1
and a.a1 in
(''CCATSTEP'', ''INAS'', ''INCK'', ''ODSD_DATA'', ''BDF_ATTATCH'', ''BDF_BASE'',
''BDF_BASE_IDX'', ''BDF_RIGHT'', ''BDF_RIGHT_IDX'', ''BDF_WORKFLOW'')' ;
OPEN cur for t_sql;
FETCH cur INTO t_A1,t_A2,t_A3,t_A4,t_A5,t_A6,t_A7,t_A8,t_A9,t_A10,t_A11;
WHILE cur%FOUND LOOP
if vdblink ='GUANGZHOU'then t_dblink :='广州';
elsIF vdblink ='DONGGUAN'then t_dblink :='东莞';
elsIF vdblink ='FOSHAN'then t_dblink :='佛山';
elsIF vdblink ='YUEBEI'then t_dblink :='粤北';
elsIF vdblink ='YUEDONG'then t_dblink :='粤东';
elsIF vdblink ='YUEXI'then t_dblink :='粤西';
elsIF vdblink ='ZHUSANJIAO'then t_dblink :='珠三角';
elsIF vdblink ='SHENZHEN'then t_dblink :='深圳';
end if;
begin
SELECT name,dn,region into t_name,t_dn,t_region FROM REGION_USER WHERE REGION=VDBLINK ;
end;
select to_char(sysdate,'day')into t_day from dual;
IF t_A10 > '70' and t_day ='星期三'THEN
BEGIN
insert into ivr.Dial_Queue(serialno,areaid,businesstypeid,telephone,usertype,dealtype,content,priority,entrytime,nextdialtime,succtimes,failtimes,lockflag,locktime,batchid,dealstat,dealtimes,specialtimeflag)
values(to_char(systimestamp, 'yyyymmddhh24missff2'),'0001','-1',t_dn,'-1','2',t_dblink||t_a2||'表空间'||t_a10||'%','101',sysdate,sysdate+1/(24*60),'0','2','0',sysdate,'0','0','2','0');
commit;
EXCEPTION
WHEN OTHERS THEN
T_IERROR := SQLCODE;
END;
END IF;
FETCH cur INTO t_A1,t_A2,t_A3,t_A4,t_A5,t_A6,t_A7,t_A8,t_A9,t_A10,t_A11;
END LOOP;
CLOSE cur;
COMMIT;
END temp_jiankong1;