create or replace procedure orderTemp
Authid Current_User is
str varchar2(500);
begin
str:=' here';
execute immediate str;
end;
--here--
Create Global Temporary Table t_order ON COMMIT DELETE ROWS as ( select to_char(to_date(t3.trade_time,'yyyy-MM-dd hh24:mi:ss'),'yyyy-mm-dd' ) as time,t6.province_id,count(t3.id) as num from t_cas_trade t3
left join t_city t6 on t3.local_id=t6.id
group by t6.province_id, to_char(to_date(t3.trade_time,'yyyy-MM-dd hh24:mi:ss'),'yyyy-mm-dd'));
----就是上面这句我要如何赋值给STR,转译'和拼接么?----
Authid Current_User IS
str VARCHAR2(500);
BEGIN
str:='Create Global Temporary Table t_order ON COMMIT DELETE ROWS as
( select to_char(to_date(t3.trade_time,''yyyy-MM-dd hh24:mi:ss''),''yyyy-mm-dd'' )
as time,t6.province_id,count(t3.id) as num from t_cas_trade t3
left join t_city t6 on t3.local_id=t6.id
group by t6.province_id, to_char(to_date(t3.trade_time,''yyyy-MM-dd hh24:mi:ss''),''yyyy-mm-dd''))';
EXECUTE IMMEDIATE str;
END;
'yyyy-MM-dd hh24:mi:ss' || '''),''' || 'yyyy-mm-dd' ||
''' ) as time,t6.province_id,count(t3.id) as num from t_cas_trade t3 left join t_city t6 on t3.local_id=t6.id group by t6.province_id, to_char(to_date(t3.trade_time,''' ||
'yyyy-MM-dd hh24:mi:ss' || '''),''' || 'yyyy-mm-dd' || '''))';
NVL(r.num,0)as regist_num,NVL(o.num,0)as num from
( select to_char(to_date(t3.trade_time,'yyyy-MM-dd hh24:mi:ss'),'yyyy-mm-dd' ) as time,t6.province_id,count(t3.id) as num from t_cas_trade t3
left join t_city t6 on t3.local_id=t6.id
group by t6.province_id, to_char(to_date(t3.trade_time,'yyyy-MM-dd hh24:mi:ss'),'yyyy-mm-dd')) o full join
(select to_char(to_date(t2.create_time,'yyyy-MM-dd hh24:mi:ss'),'yyyy-mm-dd' ) as time,t6.province_id,NVL(count(t2.id),0) as num from t_cas_userst t2 left join t_city t6 on t2.local_id=t6.id
group by t6.province_id, to_char(to_date(t2.create_time,'yyyy-MM-dd hh24:mi:ss'),'yyyy-mm-dd' )) r on o.time=r.time and o.province_id=r.province_id这样 坑爹的就是不行..只能查一半 然后卡住了。。