新手求助,oracle函数的写法。
我叫小超 2016-06-28 01:03:20 create or replace function insert_his_big_fake(v_hm_number in varchar2) return integer is
Result integer;
str varchar2(1024) := '';
begin
insert into THEAT_TOURCOPY (tc_id,tc_name,tc_date,hs_id,tc_watertemp_supply,tc_watertemp_back,tc_transient_flow,tc_total_flow,tc_total_heat,hm_number,tc_readmeter_status,tc_roomtemp,tc_counterflow,tc_collector_addr,tc_collectornum,tc_metertype,tc_valvestatus,tc_isnow)
(select salary_seq.nextval,'' tc_name,sysdate tc_date,1 hs_id,
substr(to_char(dbms_random.value(22,24)),1,5) tc_watertemp_supply,
substr(to_char(dbms_random.value(22,24)),1,5) tc_watertemp_back,
'' tc_transient_flow,
substr(to_char(dbms_random.value(1000,1500)),1,7) tc_total_flow,
substr(to_char(dbms_random.value(45,65)),1,5) tc_total_heat,
hm.hm_number,1,23,0,tc.collector_addr,tc.collector_addr,'热量表','正常','1'
from theat_heatmeter hm
left join theat_livebuild lb on hm.lb_id = lb.lb_id
left join tb_dw_collector tc on hm.collector_id = tc.rid
where lb.area_id is not null
and hm.hm_type like '%表'
and hm.hm_number = v_hm_number);
commit;
dbms_output.put_line(str);
return(Result);
end insert_his_big_fake;
我这样写对吗?我单个的将 insert into THEAT_TOURCOPY (tc_id,tc_name,tc_date,hs_id,tc_watertemp_supply,tc_watertemp_back,tc_transient_flow,tc_total_flow,tc_total_heat,hm_number,tc_readmeter_status,tc_roomtemp,tc_counterflow,tc_collector_addr,tc_collectornum,tc_metertype,tc_valvestatus,tc_isnow)
(select salary_seq.nextval,'' tc_name,sysdate tc_date,1 hs_id,
substr(to_char(dbms_random.value(22,24)),1,5) tc_watertemp_supply,
substr(to_char(dbms_random.value(22,24)),1,5) tc_watertemp_back,
'' tc_transient_flow,
substr(to_char(dbms_random.value(1000,1500)),1,7) tc_total_flow,
substr(to_char(dbms_random.value(45,65)),1,5) tc_total_heat,
hm.hm_number,1,23,0,tc.collector_addr,tc.collector_addr,'热量表','正常','1'
from theat_heatmeter hm
left join theat_livebuild lb on hm.lb_id = lb.lb_id
left join tb_dw_collector tc on hm.collector_id = tc.rid
where lb.area_id is not null
and hm.hm_type like '%表'
and hm.hm_number = v_hm_number);拿出来,可以添加数据但是整体的就不行了,是为什么呢,跪求大神指点!!!