背景
报表系统需要创建一个临时表,且临时表要定期更新,临时表包含三部分:
- drop table
- create table
- create index
思路:
- 创建存储过程
- 创建job,设定定时来跑
步骤
1. 创建存储过程
CREATE OR REPLACE PROCEDURE CREATE_T_SUBS_I AS
v_count number(10);
D_SQL VARCHAR(3000);
STR_SQL VARCHAR(3000);
V_SQL VARCHAR(3000);
BEGIN
SELECT count(*)
into v_count
FROM USER_TABLES
WHERE TABLE_NAME = UPPER('T_SUBS_bi');
if v_count >= 1 then
D_SQL := 'drop table T_SUBS_bi';
execute immediate D_SQL;
end if;
STR_SQL := 'create table T_SUBS_bi AS
SELECT
A.SUBS_ID, A.AREA_ID, C.AREA_NAME, PROD_STATE
FROM SUBS@LINK_CC A, PROD@LINK_CC