刚刚接触oracle,以前用sql server的,oracle 的存储过程比较麻烦,貌似不能直接select,求大神指教,sql已经写好并执行通过,现在想输入日期就把select的结果插入到另外一张表里。
求帮忙写存储过程和调用过程的代码,感激不尽!
下面是sql,我用的pl/sql
insert into daily_temp
(channel_id ,
staff_num ,
sales_staff_num ,
sales_indent_sum ,
sales_indent_realtime ,
sales_indent_shuaidan ,
creat_date)
select a1.网点编号,a1.网点现有工号数量,a2.使用销售门户系统工号数量,
a3.总订单数,a4.门户系统直接受理业务订单量,a5.甩单至后台受理订单量,'20160511'
from
(select b.channel_id 网点编号, count(1) 网点现有工号数量
from channel_t b,staff_t e
where e.site_id=b.org_id
and exists
(select 1 from staff_t e,channel_t b
where e.site_id=b.org_id)
group by b.channel_id) a1
left join (select b.channel_id 网点编号, count(distinct a.staff_id) 使用销售门户系统工号数量
from channel_t b,sales_indent_t a
where to_char(a.create_date,'yyyymmdd') = '20160511'
and a.site_id=b.org_id
group by b.channel_id) a2
on a1.网点编号=a2.网点编号
left join (select b.channel_id 网点编号, count(1) 总订单数
from channel_t b,sales_indent_t a
where to_char(a.create_date,'yyyymmdd') = '20160511'
and a.site_id=b.org_id
and exists
(select 1
from organization_t t
where t.org_id=a.site_id)
group by b.channel_id) a3
on a1.网点编号=a3.网点编号
left join (select b.channel_id 网点编号, count(1) 门户系统直接受理业务订单量
from channel_t b,sales_indent_t a
where to_char(a.create_date,'yyyymmdd') = '20160511'
and a.accept_type='100005'
and a.site_id=b.org_id
and exists
(select 1
from organization_t t,sales_indent_t a
where t.org_id=a.site_id)
group by b.channel_id ) a4
on a1.网点编号=a4.网点编号
left join (select b.channel_id 网点编号, count(1) 甩单至后台受理订单量
from channel_t b,sales_indent_t a
where a.site_id=b.org_id
and exists
(select *
from gateway_pre_order_log_t s
where s.crm_sales_indent_id= a.sales_indent_id)
and to_char(a.create_date,'yyyymmdd') = '20160511'
and exists
(select 1
from organization_t t
where t.org_id=a.site_id)
group by b.channel_id) a5
on a1.网点编号=a5.网点编号
order by a1.网点编号
求帮忙写存储过程和调用过程的代码,感激不尽!
下面是sql,我用的pl/sql
insert into daily_temp
(channel_id ,
staff_num ,
sales_staff_num ,
sales_indent_sum ,
sales_indent_realtime ,
sales_indent_shuaidan ,
creat_date)
select a1.网点编号,a1.网点现有工号数量,a2.使用销售门户系统工号数量,
a3.总订单数,a4.门户系统直接受理业务订单量,a5.甩单至后台受理订单量,'20160511'
from
(select b.channel_id 网点编号, count(1) 网点现有工号数量
from channel_t b,staff_t e
where e.site_id=b.org_id
and exists
(select 1 from staff_t e,channel_t b
where e.site_id=b.org_id)
group by b.channel_id) a1
left join (select b.channel_id 网点编号, count(distinct a.staff_id) 使用销售门户系统工号数量
from channel_t b,sales_indent_t a
where to_char(a.create_date,'yyyymmdd') = '20160511'
and a.site_id=b.org_id
group by b.channel_id) a2
on a1.网点编号=a2.网点编号
left join (select b.channel_id 网点编号, count(1) 总订单数
from channel_t b,sales_indent_t a
where to_char(a.create_date,'yyyymmdd') = '20160511'
and a.site_id=b.org_id
and exists
(select 1
from organization_t t
where t.org_id=a.site_id)
group by b.channel_id) a3
on a1.网点编号=a3.网点编号
left join (select b.channel_id 网点编号, count(1) 门户系统直接受理业务订单量
from channel_t b,sales_indent_t a
where to_char(a.create_date,'yyyymmdd') = '20160511'
and a.accept_type='100005'
and a.site_id=b.org_id
and exists
(select 1
from organization_t t,sales_indent_t a
where t.org_id=a.site_id)
group by b.channel_id ) a4
on a1.网点编号=a4.网点编号
left join (select b.channel_id 网点编号, count(1) 甩单至后台受理订单量
from channel_t b,sales_indent_t a
where a.site_id=b.org_id
and exists
(select *
from gateway_pre_order_log_t s
where s.crm_sales_indent_id= a.sales_indent_id)
and to_char(a.create_date,'yyyymmdd') = '20160511'
and exists
(select 1
from organization_t t
where t.org_id=a.site_id)
group by b.channel_id) a5
on a1.网点编号=a5.网点编号
order by a1.网点编号
a3.总订单数,a4.门户系统直接受理业务订单量,a5.甩单至后台受理订单量,'20160511'
改成 to_date select a1.网点编号,a1.网点现有工号数量,a2.使用销售门户系统工号数量,
a3.总订单数,a4.门户系统直接受理业务订单量,a5.甩单至后台受理订单量, to_date('20160511')
SQL> create table test(id int , crdate varchar2(20)) ;
Table created-- 这个创建过程,内容换成你的语句
SQL> create or replace procedure sp_data(v_date varchar2)
2 as
3 begin
4 insert into test (id , crdate) values(100,v_date) ;
5 end ;
6 /
Procedure created-- 调用一次
SQL> begin
2 sp_data('20160511') ;
3 end ;
4 /
PL/SQL procedure successfully completed
SQL> select * from test ;
ID CRDATE
--------------------------------------- --------------------
100 20160511
SQL> drop table test purge ;
Table dropped
SQL> drop procedure sp_data ;
Procedure droppedSQL>