create table temp_127
(
id number(10),
dtime date
);
create or replace PROCEDURE UT_REDIRECTOR_COUNT(id in number)
IS
s_table_name VARCHAR2(32);
s_sql VARCHAR2(2000);
t_count NUMBER;
c_count NUMBER;
BEGIN
--根據組ID生成組資源表的表名
s_table_name := 'EMAILS_' ||id;
--查詢此表是否存在的SQL
--s_sql := 'SELECT COUNT(*) FROM user_tables WHERE TABLE_NAME = ''' || s_table_name || '''';
--EXECUTE IMMEDIATE s_sql INTO t_count;
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME = :t1'
into t_count USING s_table_name;
--如果表是否存在
IF t_count = 1 THEN
--查詢此表是否有此列
--s_sql := 'SELECT COUNT(*) from USER_TAB_COLUMNS WHERE table_name=''' || s_table_name || ''' AND column_name = ''FIRST_NAME''';
--dbms_output.put_line(s_sql);
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME=:t2 AND COLUMN_NAME = ''FIRST_NAME'''
into c_count USING s_table_name;
--EXECUTE IMMEDIATE s_sql INTO c_count;
--dbms_output.put_line('存在表:' || s_table_name);
IF c_count = 0 THEN
s_sql := 'ALTER TABLE ' || s_table_name || ' ADD (REDIRECTOR_COUNT INT,FIRST_NAME VARCHAR2(256),LAST_NAME VARCHAR2(256),CONTENT_0 VARCHAR2(256),CONTENT_1 VARCHAR2(256),CONTENT_2 VARCHAR2(256))';
EXECUTE IMMEDIATE s_sql;
--dbms_output.put_line('沒有字段:' || 'unsubscribe' || s_sql);
s_sql := 'ALTER TABLE ' || s_table_name || ' MODIFY REDIRECTOR_COUNT DEFAULT 0';
EXECUTE IMMEDIATE s_sql;
COMMIT;
END IF;
END IF;
exception
when others then
insert into temp_127(id,dtime)
values(id,sysdate);
commit;
END;
declare
cursor cur is
SELECT *
FROM groups ORDER BY update_time desc;
rec cur%rowtype;
progress_num varchar2(56);
begin
open cur;
loop
fetch cur into rec;
exit when cur%notfound;
UT_REDIRECTOR_COUNT(rec.id);
progress_num:= 'email_'||rec.id;
dbms_application_info.set_client_info(progress_num);
end loop;
close cur;
end;
select client_info from v$session where client_info is not null;