17,377
社区成员
发帖
与我相关
我的任务
分享
CREATE OR REPLACE PACKAGE pkg_t_se
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE t_se_proc(p_rc OUT myrctype);
END pkg_t_se;
/
CREATE OR REPLACE PACKAGE BODY pkg_t_se
AS
PROCEDURE t_se_proc(p_rc OUT myrctype) IS
v_seson VARCHAR2(30); -- 用以获取当前的季节
v_sql VARCHAR2(1000);
BEGIN
SELECT decode(to_char(sysdate,'Q'),'1','T1','2','T2','3','T3','4','T4','DUAL') INTO v_seson FROM dual;
v_sql := 'SELECT * FROM '||v_seson;
OPEN p_rc FOR v_sql;
END t_se_proc;
END pkg_t_se;
/
set serveroutput on;
var v_cur refcursor;
exec pkg_t_se.t_se_proc(:v_cur);
print v_cur;
---------------------------- Part2: 楼主的需求(简化版) ----------------------------
CREATE OR REPLACE PACKAGE pkg_seson_se
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE seson_se_proc(p_rc OUT myrctype);
END pkg_seson_se;
/
CREATE OR REPLACE PACKAGE BODY pkg_seson_se
AS
PROCEDURE seson_se_proc(p_rc OUT myrctype) IS
v_seson VARCHAR2(30); -- 用以获取当前的季节
v_sql VARCHAR2(1000);
BEGIN
SELECT decode(to_char(sysdate,'Q'),'1','FIRQ@NXYJ_GPSLINK','2','SECQ@NXYJ_GPSLINK','3','THIQ@NXYJ_GPSLINK','4','FORQ@NXYJ_GPSLINK','DUAL')
INTO v_seson FROM dual;
v_sql := 'SELECT * FROM '||v_seson;
OPEN p_rc FOR v_sql;
END seson_se_proc;
END pkg_seson_se;
/
---------------------------- Part1: 测试 ----------------------------
CREATE TABLE t1(Id NUMBER(18,0), name VARCHAR2(20));
CREATE TABLE t2(Id NUMBER(18,0), name VARCHAR2(20));
CREATE TABLE t3(Id NUMBER(18,0), name VARCHAR2(20));
CREATE TABLE t4(Id NUMBER(18,0), name VARCHAR2(20));
INSERT INTO t1 VALUES(1,'T1');
INSERT INTO t2 VALUES(1,'T2');
INSERT INTO t3 VALUES(1,'T3');
INSERT INTO t4 VALUES(1,'T4');
COMMIT;
CREATE OR REPLACE PACKAGE pkg_t_se
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE t_se_proc(p_rc OUT myrctype);
END pkg_t_se;
/
CREATE OR REPLACE PACKAGE BODY pkg_t_se
AS
PROCEDURE t_se_proc(p_rc OUT myrctype) IS
v_q VARCHAR2(4); -- 用以获取当前的季节
v_tbname VARCHAR2(30); -- 用以存储根据当前季节获取的表的名称
v_sql VARCHAR2(1000);
BEGIN
SELECT to_char(sysdate,'Q') INTO v_q FROM dual;
IF v_q = '1' THEN
v_sql := 'SELECT * FROM T1';
ELSIF v_q = '2' THEN
v_sql := 'SELECT * FROM T2';
ELSIF v_q = '3' THEN
v_sql := 'SELECT * FROM T3';
ELSE
v_sql := 'SELECT * FROM T4';
END IF;
OPEN p_rc FOR v_sql;
END t_se_proc;
END pkg_t_se;
/
set serveroutput on;
var v_cur refcursor;
exec pkg_t_se.t_se_proc(:v_cur);
print v_cur;
---------------------------- Part2: 楼主的需求 ----------------------------
CREATE OR REPLACE PACKAGE pkg_seson_se
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE seson_se_proc(p_rc OUT myrctype);
END pkg_seson_se;
/
CREATE OR REPLACE PACKAGE BODY pkg_seson_se
AS
PROCEDURE seson_se_proc(p_rc OUT myrctype) IS
v_q VARCHAR2(4); -- 用以获取当前的季节
v_tbname VARCHAR2(30); -- 用以存储根据当前季节获取的表的名称
v_sql VARCHAR2(1000);
BEGIN
SELECT to_char(sysdate,'Q') INTO v_q FROM dual;
IF v_q = '1' THEN
v_sql := 'SELECT * FROM FIRQ@NXYJ_GPSLINK';
ELSIF v_q = '2' THEN
v_sql := 'SELECT * FROM SECQ@NXYJ_GPSLINK';
ELSIF v_q = '3' THEN
v_sql := 'SELECT * FROM THIQ@NXYJ_GPSLINK';
ELSE
v_sql := 'SELECT * FROM FORQ@NXYJ_GPSLINK';
END IF;
OPEN p_rc FOR v_sql;
END seson_se_proc;
END pkg_seson_se;
/
set serveroutput on;
var v_cur refcursor;
exec pkg_seson_se.seson_se_proc(:v_cur);
print v_cur;
create view res_gpsstatus as
select t1.*
from T1 t1, (select to_char(sysdate, 'Q') q from dual) q1
where q1.q = 1
union all
select t2.*
from T2 t2, (select to_char(sysdate, 'Q') q from dual) q2
where q2.q = 2
union all
select t3.*
from T3 t3, (select to_char(sysdate, 'Q') q from dual) q3
where q3.q = 3
union all
select t4.*
from T4 t4, (select to_char(sysdate, 'Q') q from dual) q4
where q4.q = 4
create view res_gpsstatus as
select t1.*
from T1, (select to_char(sysdate, 'Q') q from dual) q1
where q1.q = 1
union all
select t1.*
from T2, (select to_char(sysdate, 'Q') q from dual) q2
where q2.q = 2
union all
select t1.*
from T3, (select to_char(sysdate, 'Q') q from dual) q3
where q3.q = 3
union all
select t1.*
from T4, (select to_char(sysdate, 'Q') q from dual) q4
where q4.q = 4
-- 呵呵:没仔细看,还是不行! 它只是返回了case when语句里面的结果,没有返回表里面的内容!
-- 所以,建议:将该需求写成存储过程!
已用时间: 00: 00: 00.01
scott@SZTYORA> select * from (select case to_char(sysdate,'Q')
2 when '1' then 'T1'
3 when '2' then 'T2'
4 when '3' then 'T3'
5 when '4' then 'T4'
6 end from dual);
CASE
----
T2
-- 我测试了一下:你的语句应该是没有问题的!
CREATE TABLE t1(Id NUMBER(18,0), name VARCHAR2(20));
CREATE TABLE t2(Id NUMBER(18,0), name VARCHAR2(20));
CREATE TABLE t3(Id NUMBER(18,0), name VARCHAR2(20));
CREATE TABLE t4(Id NUMBER(18,0), name VARCHAR2(20));
INSERT INTO t1(1,'T1');
INSERT INTO t2(1,'T2');
INSERT INTO t3(1,'T3');
INSERT INTO t4(1,'T4');
COMMIT;
select * from (select case to_char(sysdate,'Q')
when '1' then 'T1'
when '2' then 'T2'
when '3' then 'T3'
when '4' then 'T4'
end from dual);