create procedure proForce_Cert
@MonthValue integer,
@CurrentMonthBatch integer output
as
begin
select @CurrentMonthBatch=count(t.force_cert) from t_goods_decl t where to_char(t.decl_date,'yyyy-mm-dd') like (to_char(sysdate,'yyyy')||'-'||'0'||@MonthValue)
end然后我用execute proForce_Cert(6)执行存储过程,却报"无效SQL语句"错误,请问我这样写有什么不妥吗?
@MonthValue integer,
@CurrentMonthBatch integer output
as
begin
select @CurrentMonthBatch=count(t.force_cert) from t_goods_decl t where to_char(t.decl_date,'yyyy-mm-dd') like (to_char(sysdate,'yyyy')||'-'||'0'||@MonthValue)
end然后我用execute proForce_Cert(6)执行存储过程,却报"无效SQL语句"错误,请问我这样写有什么不妥吗?
MonthValue in number,
CurrentMonthBatch out number
is
begin
select count(t.force_cert) into CurrentMonthBatch from t_goods_decl t where to_char(t.decl_date,'yyyy-mm-dd') like (to_char(sysdate,'yyyy')||'-'||'0'||MonthValue);
end proForce_Cert;
MonthValue integer,
CurrentMonthBatch integer
is
begin
select @CurrentMonthBatch=count(t.force_cert) from t_goods_decl t where to_char(t.decl_date,'yyyy-mm-dd') like (to_char(sysdate,'yyyy')||'-'||'0'||@MonthValue)
end;
2 force_cert varchar2(20),
3 decl_date date
4 );表已创建。groups@SZTYORA>
groups@SZTYORA> insert into t_goods_decl(force_cert, decl_date) values('Dog',sysdate-30);已创建 1 行。groups@SZTYORA> insert into t_goods_decl(force_cert, decl_date) values('Bee',sysdate-23);已创建 1 行。groups@SZTYORA> commit;提交完成。groups@SZTYORA> create or replace procedure proForce_Cert(
2 v_MonthValue in number,
3 v_CurrentMonthBatch out number
4 )
5 as
6 begin
7 select count(t.force_cert) into v_CurrentMonthBatch
8 from t_goods_decl t
9 where to_char(t.decl_date,'yyyy')=to_char(sysdate,'yyyy')
10 and to_char(t.decl_date,'mm')=lpad(v_MonthValue,2,'0');
11 end;
12 /过程已创建。groups@SZTYORA> var v_CurrentMonthBatch number;
groups@SZTYORA> exec proForce_Cert(5,:v_CurrentMonthBatch);PL/SQL 过程已成功完成。groups@SZTYORA> print v_CurrentMonthBatch;V_CURRENTMONTHBATCH
-------------------
2groups@SZTYORA>
force_cert varchar2(20),
decl_date date
);insert into t_goods_decl(force_cert, decl_date) values('Dog',sysdate-30);
insert into t_goods_decl(force_cert, decl_date) values('Bee',sysdate-23);
commit;
create or replace procedure proForce_Cert(
v_MonthValue in number,
v_CurrentMonthBatch out number
)
as
begin
select count(t.force_cert) into v_CurrentMonthBatch
from t_goods_decl t
where to_char(t.decl_date,'yyyy')=to_char(sysdate,'yyyy')
and to_char(t.decl_date,'mm')=lpad(v_MonthValue,2,'0');
end;
/var v_CurrentMonthBatch number;
exec proForce_Cert(5,:v_CurrentMonthBatch);
print v_CurrentMonthBatch;
exec proForce_Cert(5,:v_CurrentMonthBatch);
print v_CurrentMonthBatch;这样会报错,存储过程已经按照luoyoumou
大哥的方法创建了
create or replace procedure proForce_Cert(
v_MonthValue in number,
v_CurrentMonthBatch out number
)
as
begin
select count(t.force_cert) into v_CurrentMonthBatch
from t_goods_decl t
where to_char(t.decl_date,'yyyy')=to_char(sysdate,'yyyy')
and to_char(t.decl_date,'mm')=lpad(v_MonthValue,2,'0');
end;
variable c number;
exec proForce_Cert(:m,:c);
print :c;