<!--<br /> <br /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br /> http://www.CodeHighlighter.com/<br /> <br /> -->
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 ;
(
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 ;