哪位兄弟能帮忙把下面的SQL存储过程修改为oralce的存储过程,万分感激:create procedure pr_quickExtend(@date varchar(6), @endDay varchar(2),@mode varchar(2))
--该存储过程完成快速继承功能
--@date传递年月
--@endDay得到继承的截止日
as
--定义变量
declare @year varchar(4);
set @year = substring(@date,1,4);--年declare @month varchar(2);
set @month = substring(@date,5,6);--月 declare @v_rest_code varchar(20); --休息项目的编码
select @v_rest_code=project_code from xh_project where project_name='休息'; --找出休息的项目编码.declare @psndoc varchar(20); ---人员编码
declare @beginDay varchar(2); ---被继承日期
declare @itemData varchar(30);---被继承项目编码
declare @sql varchar(2000); ----生成的SQL语句---获取所有需要进行继承的人员编码以及最后一日不为空且不为休息的day 以及项目编码
declare my_cursor cursor for
select pk_psndoc,day,itemdata
from vi_data
where
year = substring(@date,1,4) and
period = substring(@date,5,6) and
pk_psndoc+day in
(
select pk_psndoc+max(day) from vi_data
where
year = substring(@date,1,4) and
period = substring(@date,5,6) and
itemdata<>@v_rest_code and
itemdata is not null
group by pk_psndoc
having max(day)<@endDay
);--打开游标,针对每个人员进行生成UPDATE语句;
OPEN my_cursor;
fetch next from my_cursor
into @psndoc, @beginDay,@itemData;
while @@FETCH_STATUS = 0
begin
select @SQL = dbo.fn_getSqlStr (@psndoc,@year,@month,cast((cast(@beginDay as int)+1) as varchar(2)),@endDay,@itemData,@mode);
print @SQL;
exec (@SQL);
fetch next from my_cursor into @psndoc,@beginDay,@itemData;
end
close my_cursor;
deallocate my_cursor; GO
--该存储过程完成快速继承功能
--@date传递年月
--@endDay得到继承的截止日
as
--定义变量
declare @year varchar(4);
set @year = substring(@date,1,4);--年declare @month varchar(2);
set @month = substring(@date,5,6);--月 declare @v_rest_code varchar(20); --休息项目的编码
select @v_rest_code=project_code from xh_project where project_name='休息'; --找出休息的项目编码.declare @psndoc varchar(20); ---人员编码
declare @beginDay varchar(2); ---被继承日期
declare @itemData varchar(30);---被继承项目编码
declare @sql varchar(2000); ----生成的SQL语句---获取所有需要进行继承的人员编码以及最后一日不为空且不为休息的day 以及项目编码
declare my_cursor cursor for
select pk_psndoc,day,itemdata
from vi_data
where
year = substring(@date,1,4) and
period = substring(@date,5,6) and
pk_psndoc+day in
(
select pk_psndoc+max(day) from vi_data
where
year = substring(@date,1,4) and
period = substring(@date,5,6) and
itemdata<>@v_rest_code and
itemdata is not null
group by pk_psndoc
having max(day)<@endDay
);--打开游标,针对每个人员进行生成UPDATE语句;
OPEN my_cursor;
fetch next from my_cursor
into @psndoc, @beginDay,@itemData;
while @@FETCH_STATUS = 0
begin
select @SQL = dbo.fn_getSqlStr (@psndoc,@year,@month,cast((cast(@beginDay as int)+1) as varchar(2)),@endDay,@itemData,@mode);
print @SQL;
exec (@SQL);
fetch next from my_cursor into @psndoc,@beginDay,@itemData;
end
close my_cursor;
deallocate my_cursor; GO
--该存储过程完成快速继承功能
--@date传递年月
--@endDay得到继承的截止日
--定义变量
is
year varchar2(4);
month varchar2(2);
v_rest_code varchar2(20);
psndoc varchar2(20); ---人员编码
beginDay varchar2(2); ---被继承日期
itemData varchar2(30);---被继承项目编码
sql varchar2(2000); ----生成的SQL语句
type cur is ref cursor;
my_cursor cur;
begin
year := substring(date,1,4);--年
month := substring(@date,5,6);--月 select project_code into v_rest_code
from xh_project where project_name='休息'; --找出休息的项目编码. ---获取所有需要进行继承的人员编码以及最后一日不为空且不为休息的day 以及项目编码
open my_cursor for
select pk_psndoc,day,itemdata
from vi_data
where
year = substring(@date,1,4) and
period = substring(@date,5,6) and
pk_psndoc+day in
(
select pk_psndoc+max(day) from vi_data
where
year = substring(date,1,4) and
period = substring(date,5,6) and
itemdata <>v_rest_code and
itemdata is not null
group by pk_psndoc
having max(day) <endDay
); --打开游标,针对每个人员进行生成UPDATE语句; loop
fetch my_cursor into psndoc,beginDay,itemData;
exit when my_cursor%notfound;sql := dbo.fn_getSqlStr (psndoc,year,month,cast((cast(beginDay as int)+1) as varchar(2)),endDay,itemData,mode);dbms_output.put_line(sql);excute immediate sql;end loop;end pr_quickExtend;
不行啊,还是有问题,我是在PL/S里执行的,执行没问题,但是从数据库的存储过程中看该存储过程是有问题的.
CREATE OR REPLACE PROCEDURE pr_quickExtend
(prm_date VARCHAR2, --传递年月
prm_endDay VARCHAR2, --得到继承的截止日
prm_mode VARCHAR2)
IS
--定义变量
s_year VARCHAR2(4); --年
s_month VARCHAR2(2); --月
v_rest_code VARCHAR2(20) --休息项目的编码
s_psndoc VARCHAR2(20); ---人员编码
s_beginDay VARCHAR2(2); ---被继承日期
s_itemData VARCHAR2(30);---被继承项目编码
s_sql VARCHAR2(4000); ----生成的SQL语句
--定义cursor
CURSOR my_cursor(v_rest_code VARCHAR2) IS
SELECT pk_psndoc,
DAY,
itemdata
FROM vi_data
WHERE YEAR = substr(prm_data, 1,4)
AND period = substr(prm_data, 5,2)
AND pk_psndoc||DAY IN(SELECT pk_psndoc||MAX(DAY)
FROM vi_data
WHERE YEAR = substr(prm_data, 1,4)
AND period = substr(prm_data, 5,2)
AND itemdata <> v_rest_code)
AND itemdata IS NOT NULL
group by pk_psndoc
having max(day) <prm_endDay;
BEGIN
--初始化变量
s_year := substr(prm_date, 1, 4);
s_month := substr(prm_date, 5, 2);
--休息项目的编码
SELECT project_code
INTO v_rest_code
FROM xh_project
where project_name='休息';
--打开游标
OPEN my_cursor(v_rest_code)
LOOP
FETCH my_cursor INTO s_psndoc, s_beginDay, s_itemData;
EXIT WHEN my_cursor%NOTFOUND;
s_sql := fn_getsqlstr(s_psndoc,s_year, s_month, to_char(to_number(s_beginday)+1), prm_endDay, s_itemdata, @prm_mode);
EXECUTE IMMEDIATE s_sql;
END LOOP;
CLOSE my_cursor;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
RETURN;
END pr_quickExtend;
你好,请问你个问题,为什么在引用参数的时候报 prm_data 该参数标识符无效?
修改如下:
--该存储过程完成快速继承功能
CREATE OR REPLACE PROCEDURE pr_quickExtend
(prm_date VARCHAR2, --传递年月
prm_endDay VARCHAR2, --得到继承的截止日
prm_mode VARCHAR2) IS
--定义变量
s_year VARCHAR2(4); --年
s_month VARCHAR2(2); --月
v_rest_code VARCHAR2(20) --休息项目的编码
s_psndoc VARCHAR2(20); ---人员编码
s_beginDay VARCHAR2(2); ---被继承日期
s_itemData VARCHAR2(30);---被继承项目编码
s_sql VARCHAR2(4000); ----生成的SQL语句
--定义cursor
CURSOR my_cursor(v_rest_code VARCHAR2) IS
SELECT pk_psndoc,
DAY,
itemdata
FROM vi_data
WHERE YEAR = substr(prm_date, 1,4)
AND period = substr(prm_date, 5,2)
AND pk_psndoc||DAY IN(SELECT pk_psndoc||MAX(DAY)
FROM vi_data
WHERE YEAR = substr(prm_date, 1,4)
AND period = substr(prm_date, 5,2)
AND itemdata <> v_rest_code)
AND itemdata IS NOT NULL
group by pk_psndoc
having max(day) <prm_endDay;
BEGIN
--初始化变量
s_year := substr(prm_date, 1, 4);
s_month := substr(prm_date, 5, 2);
--休息项目的编码
SELECT project_code
INTO v_rest_code
FROM xh_project
where project_name='休息';
--打开游标
OPEN my_cursor(v_rest_code)
LOOP
FETCH my_cursor INTO s_psndoc, s_beginDay, s_itemData;
EXIT WHEN my_cursor%NOTFOUND;
s_sql := fn_getsqlstr(s_psndoc,s_year, s_month, to_char(to_number(s_beginday)+1), prm_endDay, s_itemdata, @prm_mode);
EXECUTE IMMEDIATE s_sql;
END LOOP;
CLOSE my_cursor;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
RETURN;
END pr_quickExtend;