以下该存储过程在PL/s里执行通过之后,编辑该存储过程发现错误提示:
pls - 00201: 必须声明标识符'FN_GETSQLSTR'
请问该如何声明该标识符????????????--------------------------------------------
--该存储过程完成快速继承功能
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(s_sql);
RETURN;
END pr_quickExtend;
pls - 00201: 必须声明标识符'FN_GETSQLSTR'
请问该如何声明该标识符????????????--------------------------------------------
--该存储过程完成快速继承功能
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(s_sql);
RETURN;
END pr_quickExtend;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货