CREATE OR REPLACE PROCEDURE "SCOTT"."PROC_GETPAYDETAIL"
(
dtm varchar2,
resultset out sys_refcursor
)
as
begin
open resultset for select
te.empid,
te.ename,
te.basepay,
case
when (select sum(hours*20) from overtime o1 where o1.empid=te.id and to_char(o1.overtimedate,''yyyy-mm''):=dtm) is null then 0
else (select sum(hours*20) from overtime o2 where o2.empid=te.id and to_char(o2.overtimedate,''yyyy-mm''):=dtm)
end overtimepay, case
when (select sum(days*case when p1.personalLeaveType=0 then 100 else 20 end) from personalLeave p1 where p1.empid=te.id
and to_char(p1.personalleavetime,''yyyy-mm''):=dtm) is null then 0
else (select sum(days*case when p2.personalleavetype=0 then 100 else 20 end) from personalLeave p2 where p2.empid=te.id
and to_char(p2.personalleavetime,''yyyy-mm''):=dtm)
end personalleavePay, case
when (select sum(days*150) from absence a1 where a1.empid=te.id and to_char(a1.absenceTime,''yyyy-mm''):=dtm) is null then 0
else (select sum(days*150) from absence a2 where a2.empid=te.id and to_char(a2.absenceTime,''yyyy-mm''):=dtm)
end absencePay
from ta_emp te using dtm,dtm,dtm,dtm,dtm,dtm;
end;
/Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott
SQL>
Warning: Procedure created with compilation errors
SQL> show error
Errors for PROCEDURE SCOTT.PROC_GETPAYDETAIL:
LINE/COL ERROR
-------- -----------------------------
13/107 PL/SQL: ORA-00936: 缺失表达式
8/27 PL/SQL: SQL Statement ignored
请各位大神帮帮我,小弟是oracle初学者,谢谢了
SQL>
(
dtm varchar2,
resultset out sys_refcursor
)
as
begin
open resultset for select
te.empid,
te.ename,
te.basepay,
case
when (select sum(hours*20) from overtime o1 where o1.empid=te.id and to_char(o1.overtimedate,''yyyy-mm''):=dtm) is null then 0
else (select sum(hours*20) from overtime o2 where o2.empid=te.id and to_char(o2.overtimedate,''yyyy-mm''):=dtm)
end overtimepay, case
when (select sum(days*case when p1.personalLeaveType=0 then 100 else 20 end) from personalLeave p1 where p1.empid=te.id
and to_char(p1.personalleavetime,''yyyy-mm''):=dtm) is null then 0
else (select sum(days*case when p2.personalleavetype=0 then 100 else 20 end) from personalLeave p2 where p2.empid=te.id
and to_char(p2.personalleavetime,''yyyy-mm''):=dtm)
end personalleavePay, case
when (select sum(days*150) from absence a1 where a1.empid=te.id and to_char(a1.absenceTime,''yyyy-mm''):=dtm) is null then 0
else (select sum(days*150) from absence a2 where a2.empid=te.id and to_char(a2.absenceTime,''yyyy-mm''):=dtm)
end absencePay
from ta_emp te using dtm,dtm,dtm,dtm,dtm,dtm;
end;
/Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott
SQL>
Warning: Procedure created with compilation errors
SQL> show error
Errors for PROCEDURE SCOTT.PROC_GETPAYDETAIL:
LINE/COL ERROR
-------- -----------------------------
13/107 PL/SQL: ORA-00936: 缺失表达式
8/27 PL/SQL: SQL Statement ignored
请各位大神帮帮我,小弟是oracle初学者,谢谢了
SQL>
(
dtm varchar2,
resultset out sys_refcursor
)
这个头你就写错了。语法有问题。 类型 分号
另外to_char(o1.overtimedate,''yyyy-mm'')
to_char(o1.overtimedate,'yyyy-mm')