alter PROCEDURE mk_table( in @all_table_name varchar(1000))
BEGIN
DECLARE @v_string varchar(10000);
declare @v_sql varchar(1000);
declare @v_type varchar(100);
declare @v_col varchar(100);
declare @v_width varchar(10);
declare @v_scale varchar(10);
declare @table_name varchar(100);
declare @if_first int;
declare @i_first int;
declare @cl_count int;
set @if_first=1;
set @i_first =1;
set @all_table_name = upper(@all_table_name);
BB: LOOP
if charindex(',',@all_table_name) = 0
then
set @table_name=@all_table_name;
else
set @table_name=substr(@all_table_name,1,charindex(',',@all_table_name)-1);
end if;
set @v_sql='select count(*) into @cl_count from t_column where table_name='''||@table_name||'''';
execute immediate @v_sql;
if @cl_count <> 0
then
if @i_first =1
then
set @i_first=0;
SET @v_string='create table '||@table_name||'(\n';
else
SET @v_string=@v_string||';\n\ncreate table '||@table_name||'(\n';
end if;
-- 游标处理-开始
BEGIN
DECLARE @youbiao CURSOR FOR select trim(column_name) from t_column where table_name=@table_name;
OPEN @youbiao;
AA: LOOP
FETCH NEXT @youbiao INTO @v_col;
IF SQLCODE <> 0 THEN -- 判断游标是否结束
LEAVE AA;
END IF;
set @v_sql='select trim(domain_name) into @v_type from t_column where table_name='''||@table_name||''' and column_name='''||@v_col||'''';
execute immediate @v_sql;
set @v_sql='select width into @v_width from t_column where table_name='''||@table_name||''' and column_name='''||@v_col||'''';
execute immediate @v_sql;
set @v_sql='select scale into @v_scale from t_column where table_name='''||@table_name||''' and column_name='''||@v_col||'''';
execute immediate @v_sql;
if @v_type = 'char' or @v_type = 'varchar'
then
set @v_type=@v_type||'('||@v_width||')';
else
if @v_type = 'decimal'
then
set @v_type=@v_type||'('||@v_width||','||@v_scale||')';
else
set @v_type=@v_type;
end if;
end if;
if @if_first =1
then
set @if_first=0;
SET @v_string = @v_string||@v_col||' '||@v_type||'\n';
else
SET @v_string = @v_string||','||@v_col||' '||@v_type||'\n';
end if;
END LOOP;
CLOSE @youbiao;
END;
set @v_string=@v_string||')';
end if;
if charindex(',',@all_table_name) = 0
then
LEAVE BB;
end if;
set @all_table_name = replace(@all_table_name,@table_name||',','');
END LOOP;
-- 游标处理-结束
select @v_string;
END
出来了。
这是调用一个表ACCOUNT的,我想一起调用两个表或者多个表怎么办?
alter PROCEDURE mk_table( in @table_name varchar(100))
BEGIN
DECLARE @v_string varchar(10000);
declare @v_sql varchar(1000);
declare @v_type varchar(100);
declare @v_col varchar(100);
declare @v_width varchar(10);
declare @v_scale varchar(10);
declare @if_first int;
set @if_first=1;
set @v_string='create table '||@table_name||'(\n';
set @table_name = upper(@table_name);
-- 游标处理-开始
BEGIN
DECLARE @youbiao CURSOR FOR select trim(column_name) from t_column where table_name=@table_name;
OPEN @youbiao;
AA: LOOP
FETCH NEXT @youbiao INTO @v_col;
IF SQLCODE <> 0 THEN -- 判断游标是否结束
LEAVE AA;
END IF;
set @v_sql='select trim(domain_name) into @v_type from t_column where table_name='''||@table_name||''' and column_name='''||@v_col||'''';
execute immediate @v_sql;
set @v_sql='select width into @v_width from t_column where table_name='''||@table_name||''' and column_name='''||@v_col||'''';
execute immediate @v_sql;
set @v_sql='select scale into @v_scale from t_column where table_name='''||@table_name||''' and column_name='''||@v_col||'''';
execute immediate @v_sql;
if @v_type = 'char' or @v_type = 'varchar'
then
set @v_type=@v_type||'('||@v_width||')';
else
if @v_type = 'decimal'
then
set @v_type=@v_type||'('||@v_width||','||@v_scale||')';
else
set @v_type=@v_type;
end if;
end if;
if @if_first =1
then
set @if_first=0;
SET @v_string = @v_string||@v_col||' '||@v_type||'\n';
else
SET @v_string = @v_string||','||@v_col||' '||@v_type||'\n';
end if;
END LOOP;
CLOSE @youbiao;
END;
-- 游标处理-结束
set @v_string=@v_string||')';
select @v_string;
END