22,297
社区成员
发帖
与我相关
我的任务
分享请发表友善的回复…
发表回复
放牛娃娃 2014-05-29
- 打赏
- 举报
我的表里有800多个表，我想用存储过程把表名查出来，但是不要表里的其他字段，应该怎么从上个表里改动？
放牛娃娃 2014-05-29
- 打赏
- 举报
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
出来了。
放牛娃娃 2014-05-29
- 打赏
- 举报
我是初学者，想多弄几个方案
飞啊子 2014-05-28
- 打赏
- 举报
单个表 单独生成create table语句挺好的啊，为什么要 一起弄多个表呢。
放牛娃娃 2014-05-28
- 打赏
- 举报
初学者，希望大家帮帮忙！谢谢各位
放牛娃娃 2014-05-28
- 打赏
- 举报
这是调用一个表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
