create view res_gpsstatus as
select * from (select case to_char(sysdate,'Q')
when '1' then 'firq@nxyj_gpslink'
when '2' then 'secq@nxyj_gpslink'
when '3' then 'thiq@nxyj_gpslink'
when '4' then 'forq@nxyj_gpslink'
end from dual)如上:有一个视图,这个视图原来是通过union的方式将四张表联合起来.但是由于数据量太大,我想根据不同季度选择查询相应季度的表,减少查询时间.但是这个语句不知道怎么去写.各位大侠给指定一下.
select * from (select case to_char(sysdate,'Q')
when '1' then 'firq@nxyj_gpslink'
when '2' then 'secq@nxyj_gpslink'
when '3' then 'thiq@nxyj_gpslink'
when '4' then 'forq@nxyj_gpslink'
end from dual)如上:有一个视图,这个视图原来是通过union的方式将四张表联合起来.但是由于数据量太大,我想根据不同季度选择查询相应季度的表,减少查询时间.但是这个语句不知道怎么去写.各位大侠给指定一下.
解决方案 »
- oracle表数据的自增长
- 虚拟机配置oralcle RAC,安装集群件,在 执行root.sh时的错误
- 多表关联问题
- 请问两个不同表中赋值的问题
- 在Oracle中,如何将一张表中的long字段的数据插入到另一张表中的clob的字段中
- 请教一个简单的查询sql语句
- 不同版本的数据库的数据导入和导出的问题
- P4的计算机上为何无法安装Oracle8,操作系统为Windows 2000 Server
- plsql在服务器上导出dmp文件出现如图所示错误!!!
- 为什么dbms_metadata.getddl('TABLE','TEST')取到的表结构不全
- oracle 全盘备份语句问题
- 急!!如何查看上一个版本的视图信息?
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);
-- 'firq@nxyj_gpslink'
-- 引号里面应该用大写!或者用UPPER函数一下!
-- 所以,建议:将该需求写成存储过程!已用时间: 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 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
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
不好意思,回复晚了些,刚试了下,OK !
明白你的思路了,是通过where条件 去过相应表的数据. 我的思路还是有局限,做法也不妥.
谢谢lihui_shine的指点,也谢谢其他各位大侠的帮助.
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;
谢谢 luoyoumou 提供了另外一种思路,不错!
刚才没看着你发帖,没法给你加分了,不好意思了.
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;
/