表
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;
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;
直接
select sum(smoney) into sumsallfrom salary where usid=sid
and sdate>to_date(vdate,'YYYY-MM-DD') and sdate<add_months(vv_date,-12)这样不就行了
LZ的动态sql拼写明显有问题,后面明显少了一个'后,中间的单引号也应该用双引号替换,另外and前面还有一个逗号,其实你这个sql完全可以直接写出来,没必要用动态sql
select sum(smoney) into sumsall from salary where usid=sid and sdate>date2 and sdate <=vv_date;
-- TRY IT .. 好像是逗号问题 '||date2||', and sdate
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;
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;
/* DBMS_OUTPUT.PUT_LINE(V_SQL);*/
execute immediate v_Sql into sumsall;
END;这样应该OK了。
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); SELECT SUM(SMONEY)
INTO SUMSALL
FROM SALARY
WHERE SID = SID
AND SDATE > DATE2
AND SDATE <= VV_DATE;EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || ' ' || SQLERRM);
END;
强烈支持8楼