之前是SQL数据库中的存储过程脚本,现在把其移植到oracle数据库中,但是提示“该存储过程无效”,请各位帮忙看下谢谢了:--导入工资数据的存储过程
create or replace procedure pr_importwatadata(@par_year char(4),@par_period char(2)) as
begin
delete from xh_watadata;
insert into xh_watadata
(
cyear,
cperiod,
pk_deptdoc,
deptcode,
deptname,
psnid,
psncode,
psnname,
f_16,
f_17,
f_21
)
select
a.cyear,
a.cperiod,
c.pk_deptdoc,
c.deptcode,
c.deptname,
a.psnid,
b.psncode,
b.psnname,
d.jobname , --职务系数,后改成岗位
a.f_59 , --系数
a.f_21 --日工资
from wa_data as a left join bd_psndoc as b on a.psnid = b.pk_psndoc left join bd_deptdoc as c on c.pk_deptdoc = b.pk_deptdoc left join om_job as d on a.pk_om_job=d.pk_om_job
where
a.cyear = @par_year and
a.cperiod = @par_period and
a.classid = (select pk_wa_class from wa_waclass where vwaclassname='工资') and
a.dr = 0 and
b.psnclscope = 0 and
b.dr=0
order by
c.deptcode,
b.psncode
end pr_importwatadata
create or replace procedure pr_importwatadata(@par_year char(4),@par_period char(2)) as
begin
delete from xh_watadata;
insert into xh_watadata
(
cyear,
cperiod,
pk_deptdoc,
deptcode,
deptname,
psnid,
psncode,
psnname,
f_16,
f_17,
f_21
)
select
a.cyear,
a.cperiod,
c.pk_deptdoc,
c.deptcode,
c.deptname,
a.psnid,
b.psncode,
b.psnname,
d.jobname , --职务系数,后改成岗位
a.f_59 , --系数
a.f_21 --日工资
from wa_data as a left join bd_psndoc as b on a.psnid = b.pk_psndoc left join bd_deptdoc as c on c.pk_deptdoc = b.pk_deptdoc left join om_job as d on a.pk_om_job=d.pk_om_job
where
a.cyear = @par_year and
a.cperiod = @par_period and
a.classid = (select pk_wa_class from wa_waclass where vwaclassname='工资') and
a.dr = 0 and
b.psnclscope = 0 and
b.dr=0
order by
c.deptcode,
b.psncode
end pr_importwatadata
SQL数据库中是 dbo用户
是在 pl/sql中执行的脚本,执行过程没问题,执行之后在 pl/sql 左侧的“procedures” 存储过程包中看到刚增加的存储过程是有问题的,打开查看时,提示该存储过程无效!
然后调用该过程,进行调试吧。没法帮。
SQL SERVER的语法很多跟ORACLE不同的。比如左右连接。其他地方抄来一段例子:在Oracle PL-SQL中,左连接和右连接以如下方式来实现
查看如下语句:
SELECT emp_name, dept_name
FORM Employee, Department
WHERE Employee.emp_deptid(+) = Department.deptid 此SQL文使用了右连接,即“(+)”所在位置的另一侧为连接的方向,右连接说明等号右侧的所有记录均会被显示,无论其在左侧是否得到匹配,也就是说上例中无论会不会出现某个部门没有一个员工的情况,这个部门的名字都会在查询结果中出现。
反之:
SELECT emp_name, dept_name
FORM Employee, Department
WHERE Employee.emp_deptid = Department.deptid(+)则是左连接,无论这个员工有没有一个能在Department表中得到匹配的部门号,这个员工的记录都会被显示 。