SET NOCOUNT ON
DECLARE @schema_id int,
@schema_name nvarchar(255), -- target schema name
@t_schema_name nvarchar(255), -- schema name for table start with 'T'
@t_column_name nvarchar(255), -- column name for table start with 'T'
@column_id int,
@column_name nvarchar(255), -- target column name
@create_view_sql nvarchar(2000) -- sql statements for creating view
DECLARE schemaname_cursor CURSOR FOR
SELECT schemaid,name
FROM arschema
OPEN schemaname_cursor
FETCH NEXT FROM schemaname_cursor
INTO @schema_id,@schema_name
WHILE @@FETCH_STATUS = 0
BEGIN
set @t_schema_name = 'T' + convert(varchar(10),@schema_id)
set @create_view_sql = 'create view ' + replace(@schema_name,' ','_') + ' as select '
DECLARE column_cursor CURSOR FOR
select fieldId, fieldName
from field
where schemaId = @schema_id and fieldtype != 0
OPEN column_cursor
FETCH NEXT FROM column_cursor
INTO @column_id, @column_name
WHILE @@FETCH_STATUS = 0
BEGIN
set @t_column_name = 'C' + convert(varchar(10),@column_id)
set @create_view_sql = @create_view_sql + @t_column_name + ' as ' + replace(@column_name,' ', '_') + ', '
FETCH NEXT FROM column_cursor
INTO @column_id, @column_name
END
set @create_view_sql = substring(@create_view_sql, 0, len(@create_view_sql) - 1)
+ ' from ' + @t_schema_name
print @create_view_sql
CLOSE column_cursor
DEALLOCATE column_cursor
FETCH NEXT FROM schemaname_cursor
INTO @schema_id, @schema_name
END
CLOSE schemaname_cursor
DEALLOCATE schemaname_cursor
DECLARE @schema_id int,
@schema_name nvarchar(255), -- target schema name
@t_schema_name nvarchar(255), -- schema name for table start with 'T'
@t_column_name nvarchar(255), -- column name for table start with 'T'
@column_id int,
@column_name nvarchar(255), -- target column name
@create_view_sql nvarchar(2000) -- sql statements for creating view
DECLARE schemaname_cursor CURSOR FOR
SELECT schemaid,name
FROM arschema
OPEN schemaname_cursor
FETCH NEXT FROM schemaname_cursor
INTO @schema_id,@schema_name
WHILE @@FETCH_STATUS = 0
BEGIN
set @t_schema_name = 'T' + convert(varchar(10),@schema_id)
set @create_view_sql = 'create view ' + replace(@schema_name,' ','_') + ' as select '
DECLARE column_cursor CURSOR FOR
select fieldId, fieldName
from field
where schemaId = @schema_id and fieldtype != 0
OPEN column_cursor
FETCH NEXT FROM column_cursor
INTO @column_id, @column_name
WHILE @@FETCH_STATUS = 0
BEGIN
set @t_column_name = 'C' + convert(varchar(10),@column_id)
set @create_view_sql = @create_view_sql + @t_column_name + ' as ' + replace(@column_name,' ', '_') + ', '
FETCH NEXT FROM column_cursor
INTO @column_id, @column_name
END
set @create_view_sql = substring(@create_view_sql, 0, len(@create_view_sql) - 1)
+ ' from ' + @t_schema_name
print @create_view_sql
CLOSE column_cursor
DEALLOCATE column_cursor
FETCH NEXT FROM schemaname_cursor
INTO @schema_id, @schema_name
END
CLOSE schemaname_cursor
DEALLOCATE schemaname_cursor
CREATE OR REPLACE PROCEDURE 过程名
as
schema_id number;
schema_name nvarchar(255);
.
.
.
cursor c_curs as select schemaid,name FROM arschema ;begin
open c_curs;
loop
fetch c_curs into schema_id;
exit when c_curs%notfound;
t_column_name := 'C' || convert(varchar(10),column_id) ;
.
.
end loop;
close c_curs;end;
大致就是这样了,细节你自己去调吧!