我写了个存储过程,需要带riqi这个参数,从程序中传过来, 不知下面的执行语句 startdate>=' & riqi &[/中如何写!
Create or replace procedure nc350.SHIYE(riqi in char) as
v_num number(10);
v_num1 number(10);
begin
execute immediate 'create table sum_costsubj as
select b.settleunitname,f.pk_settleunit,fb.pk_costsubj,sum(plan_money) as plan_money
from nc350.fp_planbill f left join nc350.bd_settleunit b on f.pk_settleunit=b.pk_settleunit
left join nc350.fp_planbill_b fb on f.pk_planbill=fb.pk_planbill
where 1 = 1 and f.pk_corp = ''1051'' and f.vbillstatus >= ''3'' and startdate>=' & riqi &'group by b.settleunitname,f.pk_settleunit,fb.pk_costsubj';
Create or replace procedure nc350.SHIYE(riqi in char) as
v_num number(10);
v_num1 number(10);
begin
execute immediate 'create table sum_costsubj as
select b.settleunitname,f.pk_settleunit,fb.pk_costsubj,sum(plan_money) as plan_money
from nc350.fp_planbill f left join nc350.bd_settleunit b on f.pk_settleunit=b.pk_settleunit
left join nc350.fp_planbill_b fb on f.pk_planbill=fb.pk_planbill
where 1 = 1 and f.pk_corp = ''1051'' and f.vbillstatus >= ''3'' and startdate>=' & riqi &'group by b.settleunitname,f.pk_settleunit,fb.pk_costsubj';
连接字符串用 || 不是 &
日期型用楼上的转换就对了
execute immediate 'create table sum_costsubj as
select b.settleunitname,f.pk_settleunit,fb.pk_costsubj,sum(plan_money) as plan_money
from nc350.fp_planbill f left join nc350.bd_settleunit b on f.pk_settleunit=b.pk_settleunit
left join nc350.fp_planbill_b fb on f.pk_planbill=fb.pk_planbill
where 1 = 1 and f.pk_corp = ''1051'' and f.vbillstatus >= ''3'' and startdate>=to_date(''' || riqi ||''',''对应的日期格式字符串''group by b.settleunitname,f.pk_settleunit,fb.pk_costsubj';
select b.settleunitname,f.pk_settleunit,fb.pk_costsubj,sum(plan_money) as plan_money
from nc350.fp_planbill f left join nc350.bd_settleunit b on f.pk_settleunit=b.pk_settleunit
left join nc350.fp_planbill_b fb on f.pk_planbill=fb.pk_planbill
where 1 = 1 and f.pk_corp = ''1051'' and f.vbillstatus >= ''3'' and f.startdate>=' || to_date(riqi,'yyyy-mm-dd') ||
'group by b.settleunitname,f.pk_settleunit,fb.pk_costsubj';我是这样写的,执行结果不对!那里错了吗
select b.settleunitname,f.pk_settleunit,fb.pk_costsubj,sum(plan_money) as plan_money
from nc350.fp_planbill f left join nc350.bd_settleunit b on f.pk_settleunit=b.pk_settleunit
left join nc350.fp_planbill_b fb on f.pk_planbill=fb.pk_planbill
where 1 = 1 and f.pk_corp = ''1051'' and f.vbillstatus >= ''3'' and f.startdate>=to_date(''' || riqi || ''',''yyyy-mm-dd'')
group by b.settleunitname,f.pk_settleunit,fb.pk_costsubj';执行: call nc350.shiye(2010-09-01);
这样执行还是不成功,请再帮忙看看吧!
SQL> grant create any table to scott;
Grant succeeded
SQL>
然后创建过程并执行
SQL> create or replace procedure SHIYE(riqi in char) as
2 v_num number(10);
3 begin
4 select count(*) into v_num from user_tables t where t.table_name=upper('t_test_in_char');
5 if v_num=1 then
6 execute immediate 'drop table t_test_in_char';
7 end if;
8 execute immediate 'create table t_test_in_char as
9 select to_date('''||riqi||''',''yyyymmdd'') as aa from dual';
10 end;
11 /
Procedure created
SQL> exec SHIYE('20100901');
PL/SQL procedure successfully completed
SQL> select * from t_test_in_char;
AA
-----------
2010-9-1
SQL>
SQL> exec SHIYE('20100901');
begin SHIYE('20100901'); end;
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.SHIYE", line 8
ORA-06512: at line 2
SQL>