DECLARE
g_period_set_name VARCHAR2(20) := '山推日历';
g_period_type VARCHAR2(20) := 21;
l_period_begin VARCHAR2(15) := '2012-01';
l_period_end VARCHAR2(15) := '2013-01';
l_start_period_year NUMBER;
l_end_period_year NUMBER;
l_start_period_num NUMBER;
l_end_period_num NUMBER;
CURSOR cur_period(p_start_period_year NUMBER, p_start_period_num NUMBER, p_end_period_year NUMBER, p_end_period_num NUMBER) IS
SELECT gpd.period_name
FROM gl.gl_periods gpd
WHERE 1 = 1
AND gpd.period_year BETWEEN p_start_period_year AND p_end_period_year
AND (CASE
WHEN gpd.period_year = p_start_period_year THEN
p_start_period_num
ELSE
gpd.period_num
END) <= gpd.period_num
AND (CASE
WHEN gpd.period_year = p_end_period_year THEN
p_end_period_num
ELSE
gpd.period_num
END) >= gpd.period_num
AND gpd.period_set_name = g_period_set_name
AND gpd.period_type = g_period_type;
BEGIN
BEGIN
SELECT t.period_year,
t.period_num
INTO l_start_period_year,
l_start_period_num
FROM gl.gl_periods t
WHERE t.period_set_name = g_period_set_name
AND t.period_type = g_period_type
AND t.period_name = l_period_begin;
SELECT t.period_year,
t.period_num
INTO l_end_period_year,
l_end_period_num
FROM gl.gl_periods t
WHERE t.period_set_name = g_period_set_name
AND t.period_type = g_period_type
AND t.period_name = l_period_end;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('输入期间异常,请检查!');
--RAISE fnd_api.g_exc_error;
END;
FOR rec IN cur_period(l_start_period_year, l_start_period_num, l_end_period_year, l_end_period_num) LOOP
dbms_output.put_line(rec.period_name);
END LOOP;
END;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25543703/viewspace-753465/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25543703/viewspace-753465/