各位大哥帮忙看看这个哪里有问题,创建无效,小弟找不出,谢谢各位哈!!!--存储过程
Create Or Replace Procedure Proc_Link_Cc_Dba As
v_Data_Id Res_Alarm_Drive@Res_Alarm.Data_Id%Type; --流水号
v_Region_Id Res_Alarm_Drive@Res_Alarm.Region_Id%Type; --区域ID
v_Region_Name Res_Alarm_Drive@Res_Alarm.Region_Name%Type; --区域名称
v_Station_Id Res_Alarm_Drive@Res_Alarm.Station_Id%Type; --小区ID
v_Station_Name Res_Alarm_Drive@Res_Alarm.Station_Name%Type; --小区名称
v_Res_Type_Id Res_Alarm_Drive@Res_Alarm.Res_Type_Id%Type; --资源类型ID 2510
v_Res_Type_Name Res_Alarm_Drive@Res_Alarm.Res_Type_Name%Type; --资源类型 OLT设备预警
v_Res_Level Res_Alarm_Drive@Res_Alarm.Res_Level%Type; --设备等级 null
v_Res_Id Res_Alarm_Drive@Res_Alarm.Res_Id%Type; --资源ID prop_three o
v_Res_Name Res_Alarm_Drive@Res_Alarm.Res_Name%Type; --资源名称 prop_one o
v_Alarm_Total Res_Alarm_Drive@Res_Alarm.Alarm_Total%Type; --预警资源总量 prop_six o
v_Alarm_On Res_Alarm_Drive@Res_Alarm.Alarm_On%Type; --预警资源占用量 stat_value o
v_Alarm_Per Res_Alarm_Drive@Res_Alarm.Alarm_Per%Type; --预警资源占用比 stat_value o
v_Main_Region Res_Alarm_Drive@Res_Alarm.Main_Region%Type; --维护分公司 sf_get_desc_china(mnt_spec) o
v_Alarm_Tv Res_Alarm_Drive@Res_Alarm.Alarm_Tv%Type; --预警阀值 70
v_Process_State Res_Alarm_Drive@Res_Alarm.Process_State%Type; --流程状态 0 1 2 初始 处理中 归档
v_Alarm_Sw Res_Alarm_Drive@Res_Alarm.Alarm_Sw%Type; --预警开关 0 1 启用 关闭
v_Alarm_Date Res_Alarm_Drive@Res_Alarm.Alarm_Date%Type; --预警时间 prop_nine o
v_Flag Res_Alarm_Data.Alarm_Id%Type; Cursor Cur_Items Is
Select a.Data_Id, i.Region_Id, i.Region_Name, s.Station_Id, s.China_Name, a.Prop_Three, a.Prop_One, a.Prop_Six,
a.Stat_Value,a.Stat_Value, Sf_Get_Desc_China(s.Mnt_Spec), a.Prop_Nine
From Res_Alarm_Data a, Rme_Eqp p, Spc_Room m, Spc_Station s, Spc_Region i
Where p.Posit_Id = m.Room_Id And m.Station_Id = s.Station_Id And s.Region_Id = i.Region_Id And
a.Alarm_Id = '000161010000000000000856' And a.Delete_State = '0' And a.Prop_Three = p.Eqp_Id And
a.Prop_Nine = (Select Max(Aa.Prop_Nine)
From Res_Alarm_Data Aa
Where Aa.Delete_State = '0' And Aa.Alarm_Id = '000161010000000000000856') And
(Exists (Select 2
From Res_Alarm_Drive@Res_Alarm Mm
Where Mm.Res_Id = a.Prop_Three And Mm.Process_State = '2' And Mm.Alarm_Sw = '0') Or Not Exists
(Select 2 From Res_Alarm_Drive@Res_Alarm Mm Where Mm.Res_Id = a.Prop_Three))
Begin
v_Data_Id := ''; --流水号 o
v_Region_Id := ''; --区域ID o
v_Region_Name := ''; --区域名称 o
v_Station_Id := ''; --小区ID o
v_Station_Name := ''; --小区名称 o
v_Res_Type_Id := 2510; --资源类型ID 2510
v_Res_Type_Name := 'OLT设备预警'; --资源类型 OLT设备预警
v_Res_Level := ''; --设备等级 null
v_Res_Id := ''; --资源ID prop_three o
v_Res_Name := ''; --资源名称 prop_one o
v_Alarm_Total := ''; --预警资源总量 prop_six o
v_Alarm_On := ''; --预警资源占用量 stat_value o
v_Alarm_Per := ''; --预警资源占用比 stat_value o
v_Main_Region := ''; --维护分公司 sf_get_desc_china(mnt_spec) o
v_Alarm_Tv := 70; --预警阀值 70
v_Process_State := 0; --流程状态 0 1 2 初始 处理中 归档
v_Alarm_Sw := 0; --预警开关 0 1 启用 关闭
v_Alarm_Date := ''; --预警时间 prop_nine o Open Cur_Items;
Loop
Fetch Cur_Items Into v_Data_Id, v_Region_Id, v_Region_Name, v_Station_Id,v_Station_Name,v_Res_Id,v_Res_Name,v_Alarm_Total,
v_Alarm_On,v_Alarm_Per,v_Main_Region,v_Alarm_Date;
Exit When Cur_Items%Notfound; Insert Into Res_Alarm_Drive@Res_Alarm Values(v_Data_Id, v_Region_Id, v_Region_Name, v_Station_Id, v_Station_Name,
v_Res_Type_Id, v_Res_Type_Name, v_Res_Level, v_Res_Id, v_Res_Name,
v_Alarm_Total, v_Alarm_On, v_Alarm_Per, v_Main_Region, v_Alarm_Tv,
v_Process_State, v_Alarm_Sw, v_Alarm_Date); End Loop;
Close Cur_Items;
Commit;
End Proc_Link_Cc_Dba;
Create Or Replace Procedure Proc_Link_Cc_Dba As
v_Data_Id Res_Alarm_Drive@Res_Alarm.Data_Id%Type; --流水号
v_Region_Id Res_Alarm_Drive@Res_Alarm.Region_Id%Type; --区域ID
v_Region_Name Res_Alarm_Drive@Res_Alarm.Region_Name%Type; --区域名称
v_Station_Id Res_Alarm_Drive@Res_Alarm.Station_Id%Type; --小区ID
v_Station_Name Res_Alarm_Drive@Res_Alarm.Station_Name%Type; --小区名称
v_Res_Type_Id Res_Alarm_Drive@Res_Alarm.Res_Type_Id%Type; --资源类型ID 2510
v_Res_Type_Name Res_Alarm_Drive@Res_Alarm.Res_Type_Name%Type; --资源类型 OLT设备预警
v_Res_Level Res_Alarm_Drive@Res_Alarm.Res_Level%Type; --设备等级 null
v_Res_Id Res_Alarm_Drive@Res_Alarm.Res_Id%Type; --资源ID prop_three o
v_Res_Name Res_Alarm_Drive@Res_Alarm.Res_Name%Type; --资源名称 prop_one o
v_Alarm_Total Res_Alarm_Drive@Res_Alarm.Alarm_Total%Type; --预警资源总量 prop_six o
v_Alarm_On Res_Alarm_Drive@Res_Alarm.Alarm_On%Type; --预警资源占用量 stat_value o
v_Alarm_Per Res_Alarm_Drive@Res_Alarm.Alarm_Per%Type; --预警资源占用比 stat_value o
v_Main_Region Res_Alarm_Drive@Res_Alarm.Main_Region%Type; --维护分公司 sf_get_desc_china(mnt_spec) o
v_Alarm_Tv Res_Alarm_Drive@Res_Alarm.Alarm_Tv%Type; --预警阀值 70
v_Process_State Res_Alarm_Drive@Res_Alarm.Process_State%Type; --流程状态 0 1 2 初始 处理中 归档
v_Alarm_Sw Res_Alarm_Drive@Res_Alarm.Alarm_Sw%Type; --预警开关 0 1 启用 关闭
v_Alarm_Date Res_Alarm_Drive@Res_Alarm.Alarm_Date%Type; --预警时间 prop_nine o
v_Flag Res_Alarm_Data.Alarm_Id%Type; Cursor Cur_Items Is
Select a.Data_Id, i.Region_Id, i.Region_Name, s.Station_Id, s.China_Name, a.Prop_Three, a.Prop_One, a.Prop_Six,
a.Stat_Value,a.Stat_Value, Sf_Get_Desc_China(s.Mnt_Spec), a.Prop_Nine
From Res_Alarm_Data a, Rme_Eqp p, Spc_Room m, Spc_Station s, Spc_Region i
Where p.Posit_Id = m.Room_Id And m.Station_Id = s.Station_Id And s.Region_Id = i.Region_Id And
a.Alarm_Id = '000161010000000000000856' And a.Delete_State = '0' And a.Prop_Three = p.Eqp_Id And
a.Prop_Nine = (Select Max(Aa.Prop_Nine)
From Res_Alarm_Data Aa
Where Aa.Delete_State = '0' And Aa.Alarm_Id = '000161010000000000000856') And
(Exists (Select 2
From Res_Alarm_Drive@Res_Alarm Mm
Where Mm.Res_Id = a.Prop_Three And Mm.Process_State = '2' And Mm.Alarm_Sw = '0') Or Not Exists
(Select 2 From Res_Alarm_Drive@Res_Alarm Mm Where Mm.Res_Id = a.Prop_Three))
Begin
v_Data_Id := ''; --流水号 o
v_Region_Id := ''; --区域ID o
v_Region_Name := ''; --区域名称 o
v_Station_Id := ''; --小区ID o
v_Station_Name := ''; --小区名称 o
v_Res_Type_Id := 2510; --资源类型ID 2510
v_Res_Type_Name := 'OLT设备预警'; --资源类型 OLT设备预警
v_Res_Level := ''; --设备等级 null
v_Res_Id := ''; --资源ID prop_three o
v_Res_Name := ''; --资源名称 prop_one o
v_Alarm_Total := ''; --预警资源总量 prop_six o
v_Alarm_On := ''; --预警资源占用量 stat_value o
v_Alarm_Per := ''; --预警资源占用比 stat_value o
v_Main_Region := ''; --维护分公司 sf_get_desc_china(mnt_spec) o
v_Alarm_Tv := 70; --预警阀值 70
v_Process_State := 0; --流程状态 0 1 2 初始 处理中 归档
v_Alarm_Sw := 0; --预警开关 0 1 启用 关闭
v_Alarm_Date := ''; --预警时间 prop_nine o Open Cur_Items;
Loop
Fetch Cur_Items Into v_Data_Id, v_Region_Id, v_Region_Name, v_Station_Id,v_Station_Name,v_Res_Id,v_Res_Name,v_Alarm_Total,
v_Alarm_On,v_Alarm_Per,v_Main_Region,v_Alarm_Date;
Exit When Cur_Items%Notfound; Insert Into Res_Alarm_Drive@Res_Alarm Values(v_Data_Id, v_Region_Id, v_Region_Name, v_Station_Id, v_Station_Name,
v_Res_Type_Id, v_Res_Type_Name, v_Res_Level, v_Res_Id, v_Res_Name,
v_Alarm_Total, v_Alarm_On, v_Alarm_Per, v_Main_Region, v_Alarm_Tv,
v_Process_State, v_Alarm_Sw, v_Alarm_Date); End Loop;
Close Cur_Items;
Commit;
End Proc_Link_Cc_Dba;
解决方案 »
- oracle能存多大的数据
- Oracle Database 11g(11.1.0.7.0)(第1版) perl.exe进程占用?
- 请教大家一个关于分布式数据库的问题
- Oracle存储进程
- 表数据合并问题
- 取oracle 存储过程 报错:当前提供程序不支持从单一执行返回多个记录集
- oracle物化视图在刷新期间没有数据怎么办?
- 怎样用一个查询语句查询一个表中有几个字段?要求得出的是一个数字而不是字段名。
- ORACLE的触发器如何调用存储过程!急!
- 如何安装Oracle xml的开发组件?
- vc ado oracle9i 存储过程大数据blob参数传空值
- 本人装oracle 10g 遇到的各种问题,求助!!!
(Select 2 From Res_Alarm_Drive@Res_Alarm Mm Where Mm.Res_Id = a.Prop_Three));
--少个分号同学
Begin
和begin之前要加分号。 Cursor Cur_Items Is
select * from tab ;begin
你的逻辑处理。
end;应该这样。
但是我要是点右键测试或者用exec运行,就说无校,像这样:
Create Or Replace Procedure Proc_Link_Cc_Dba As
v_Data_Id Res_Alarm_Drive@Res_Alarm.Data_Id%Type; --流水号
: Cursor Cur_Items Is
select a.Data_Id... from ....;
Begin
Open Cur_Items;
Loop
Fetch Cur_Items Into v_Data_Id ...
Exit When Cur_Items%Notfound; Insert Into Res_Alarm_Drive@Res_Alarm Values(...)....;
End Loop;
Close Cur_Items;
Commit;
End Proc_Link_Cc_Dba;
去掉试试