将查询结果保存到另外一张表，并且返回结果。

1、创建表t_circuitCREATE TABLE t_circuit
(
circuitnumber_cr varchar(255),
aendzhandianobjectid_cr varchar(255),
zendzhandianobjectid_cr varchar(255),
AendTransBuildingObjectid_cr varchar(255),
ZendTransBuildingObjectid_cr varchar(255),
routetype_c varchar(255),
aendtimeslot_c varchar(255),
zendtimeslot_c varchar(255),
trailname_t varchar(255),
ratedesc_t varchar(255),
servicestate_t varchar(255),
aendtimeslot_t varchar(255),
zendtimeslot_t varchar(255)
);

 2、创建存储过程

drop procedure test_12;
create procedure test_12(amename varchar(255),aportname varchar(255),aendtimeslot_t INTEGER DEFAULT 0,zendtimeslot_t INTEGER DEFAULT 0)returning char(50);
define objectid_p varchar(255);
define objectid_t varchar(255);
define trailname_t varchar(255);
define ratedesc_t varchar(255);
define servicestate_t varchar(255);
define circuitobjectid_c varchar(255);
define routetype_c varchar(255);
define aendtimeslot_c varchar(255);
define zendtimeslot_c varchar(255);
define circuitnumber_cr varchar(255);
define aendzhandianobjectid_cr varchar(255);
define zendzhandianobjectid_cr varchar(255);
define AendTransBuildingObjectid_cr varchar(255);
define ZendTransBuildingObjectid_cr varchar(255);
define return_value varchar(50);
define i int;
let return_value='出现多个端口情况';
select count(*) into i from ptp p,me m where p.meobjectid = m.objectid and m.mename=amename and p.portname =aportname;
if i = 1 then
--查询端口id
truncate table t_circuit;
select p.objectid into objectid_p from ptp p,me m
where p.meobjectid = m.objectid and m.mename=amename and p.portname =aportname;
--查询通道
select objectid,trailname,ratedesc,servicestate,aendtimeslot,zendtimeslot
into objectid_t,trailname_t,ratedesc_t,servicestate_t,aendtimeslot_c,zendtimeslot_c from trail
where aendptpobjectid=objectid_p and aendtimeslot=aendtimeslot_t and zendtimeslot=zendtimeslot_t;
if servicestate_t = '实占' then
--查询电路路由
select circuitobjectid,routetype,aendtimeslot,zendtimeslot
into circuitobjectid_c,routetype_c,aendtimeslot_c,zendtimeslot_c from circuitroute
where relatedrouteobjectid=objectid_t;
--查询电路信息
select circuitnumber,aendzhandianobjectid,zendzhandianobjectid,AendTransBuildingObjectid,ZendTransBuildingObjectid
into circuitnumber_cr,aendzhandianobjectid_cr,zendzhandianobjectid_cr,AendTransBuildingObjectid_cr,ZendTransBuildingObjectid_cr
from circuit where objectid=circuitobjectid_c;
--将通道、路由、电路相关信息插入表t_circuit中
INSERT INTO t_circuit VALUES (circuitnumber_cr,aendzhandianobjectid_cr,zendzhandianobjectid_cr,AendTransBuildingObjectid_cr,ZendTransBuildingObjectid_cr,routetype_c,aendtimeslot_c,zendtimeslot_c,trailname_t,ratedesc_t,servicestate_t,aendtimeslot_c,zendtimeslot_c);
return servicestate_t;
elif servicestate_t = '预占' then
--查询电路路由
select circuitobjectid,routetype,aendtimeslot,zendtimeslot
into circuitobjectid_c,routetype_c,aendtimeslot_c,zendtimeslot_c from circuitroute
where relatedrouteobjectid=objectid_t;
--查询电路信息
select circuitnumber,aendzhandianobjectid,zendzhandianobjectid,AendTransBuildingObjectid,ZendTransBuildingObjectid
into circuitnumber_cr,aendzhandianobjectid_cr,zendzhandianobjectid_cr,AendTransBuildingObjectid_cr,ZendTransBuildingObjectid_cr
from circuit where objectid=circuitobjectid_c;
--将通道、路由、电路相关信息插入表t_circuit中
INSERT INTO t_circuit VALUES (circuitnumber_cr,aendzhandianobjectid_cr,zendzhandianobjectid_cr,AendTransBuildingObjectid_cr,ZendTransBuildingObjectid_cr,routetype_c,aendtimeslot_c,zendtimeslot_c,trailname_t,ratedesc_t,servicestate_t,aendtimeslot_c,zendtimeslot_c);
return servicestate_t;
if servicestate_t = '空闲' then
return servicestate_t;
end if;
end if;
elif i !=1 then
return return_value;
end if;
end procedure;