表
create table salary(
sid number(8),
sname varchar2(20),
smoney number(10,2),
sdate date,
constraint salary_PK primary key(sid)
)
存储过程(查得某个某个阶段工资总和输出),日期拼写到条件中老是报错,该如何处理,
create or replace procedure sumsalary(sid number,vdate varchar2,sumsall out number)
as
v_Sql varchar2(200);
vv_date date;
date2 date;
begin
vv_date:=to_date(vdate,'YYYY-MM-DD');
date2:=add_months(vv_date,-12);
v_Sql:='select sum(smoney) from salary where usid='||sid||' and sdate>'||date2||', and sdate <='||vv_date;
execute immediate v_Sql into sumsall;
end;
create table salary(
sid number(8),
sname varchar2(20),
smoney number(10,2),
sdate date,
constraint salary_PK primary key(sid)
)
存储过程(查得某个某个阶段工资总和输出),日期拼写到条件中老是报错,该如何处理,
create or replace procedure sumsalary(sid number,vdate varchar2,sumsall out number)
as
v_Sql varchar2(200);
vv_date date;
date2 date;
begin
vv_date:=to_date(vdate,'YYYY-MM-DD');
date2:=add_months(vv_date,-12);
v_Sql:='select sum(smoney) from salary where usid='||sid||' and sdate>'||date2||', and sdate <='||vv_date;
execute immediate v_Sql into sumsall;
end;
CREATE OR REPLACE PROCEDURE SUMSALARY(SID NUMBER,
VDATE VARCHAR2,
SUMSALL OUT NUMBER) AS
V_SQL VARCHAR2(200);
BEGIN
V_SQL := 'SELECT SUM(SMONEY) FROM SALARY WHERE USID=' || SID ||' AND SDATE>ADD_MONTHS(TO_DATE('||VDATE||', ''YYYY-MM-DD''), -12) AND SDATE <= TO_DATE('||VDATE||', ''YYYY-MM-DD'')';
EXECUTE IMMEDIATE V_SQL INTO SUMSALL;
END;
and sdate>to_date(vdate,'YYYY-MM-DD') and sdate<add_months(vv_date,-12)
这样不就行了
select sum(smoney) into sumsall from salary where usid=sid and sdate>date2 and sdate <=vv_date;
其次你这个语句有问题吧:where usid='||sid||'就限定了记录的唯一性,
那结果跟 v_Sql:='select smoney from salary where usid='||sid; 是一样的。自己想想看??