-- 呵呵,愉懒之人,人人有之!
http://topic.csdn.net/u/20110318/09/8fec23db-a5fc-470e-8efe-be7fe9c2b4f6.html?38863
create or replace procedure sql_create(
i_tbname varchar2, -- 默认大写(可以写成小写,但我会将其转换成大小去比较,这里可以按照你的需求去更改)
i_except_columns varchar2, -- 要排除的字段名,多个字段用逗号(,)隔开(因为Oracle里面的字段默认均为大写,所以输入时要注意,当然可以用upper()函数,那是后话)
o_sql out varchar2
)
is
v_sqls varchar2(4000);
i_except_columns2 varchar2(4000);
v_sql varchar2(4000);
v_cnt number(18,0);
begin
v_sql := '';
i_except_columns2 :=''''||replace(upper(i_except_columns),',',''',''')||'''';
execute immediate 'select count(1) from user_tables where table_name=upper(:i_tbname)' into v_cnt using i_tbname; -- 看是否存在大写名字的表
if v_cnt = 1 then
begin
v_sqls := 'select trim(regexp_replace(wm_concat((case when m.column_name<>upper(m.column_name) then '' "''||m.column_name||''"'' else '' ''||m.column_name end)),'','','',''||chr(10))) as columnNames
from user_tab_columns m
where m.table_name=upper(:i_tbname)
and m.column_name not in ('||i_except_columns2||')
order by column_id asc';
execute immediate v_sqls into v_sql using i_tbname;
o_sql := 'SELECT '||v_sql||chr(10)||' FROM '||i_tbname||';';
end;
else
begin
execute immediate 'select count(1) from user_tables where table_name=:i_tbname' into v_cnt using i_tbname; -- 看是否存在小写名字的表
if v_cnt = 1 then
begin
v_sqls := 'select trim(regexp_replace(wm_concat((case when m.column_name<>upper(m.column_name) then '' "''||m.column_name||''"'' else '' ''||m.column_name end)),'','','',''||chr(10))) as columnNames
from user_tab_columns m
where m.table_name=:i_tbname
and m.column_name not in ('||i_except_columns2||')
order by column_id asc';
execute immediate v_sqls into v_sql using i_tbname; o_sql := 'SELECT '||v_sql||chr(10)||' FROM '||'"'||i_tbname||'"';
end;
else
o_sql := '对不起,表名为 '||i_tbname||'、'||upper(i_tbname)||' 在当前用户 '||user||' 下均不存在! ';
end if;
end;
end if;
end;
/-- select dbms_metadata.get_ddl('TABLE','MUSICMUSIC') from dual;set serveroutput on;
var sqls varchar2(4000);
exec sql_create('musicmusic','musicid',:sqls);
print sqls;
http://topic.csdn.net/u/20110318/09/8fec23db-a5fc-470e-8efe-be7fe9c2b4f6.html?38863
create or replace procedure sql_create(
i_tbname varchar2, -- 默认大写(可以写成小写,但我会将其转换成大小去比较,这里可以按照你的需求去更改)
i_except_columns varchar2, -- 要排除的字段名,多个字段用逗号(,)隔开(因为Oracle里面的字段默认均为大写,所以输入时要注意,当然可以用upper()函数,那是后话)
o_sql out varchar2
)
is
v_sqls varchar2(4000);
i_except_columns2 varchar2(4000);
v_sql varchar2(4000);
v_cnt number(18,0);
begin
v_sql := '';
i_except_columns2 :=''''||replace(upper(i_except_columns),',',''',''')||'''';
execute immediate 'select count(1) from user_tables where table_name=upper(:i_tbname)' into v_cnt using i_tbname; -- 看是否存在大写名字的表
if v_cnt = 1 then
begin
v_sqls := 'select trim(regexp_replace(wm_concat((case when m.column_name<>upper(m.column_name) then '' "''||m.column_name||''"'' else '' ''||m.column_name end)),'','','',''||chr(10))) as columnNames
from user_tab_columns m
where m.table_name=upper(:i_tbname)
and m.column_name not in ('||i_except_columns2||')
order by column_id asc';
execute immediate v_sqls into v_sql using i_tbname;
o_sql := 'SELECT '||v_sql||chr(10)||' FROM '||i_tbname||';';
end;
else
begin
execute immediate 'select count(1) from user_tables where table_name=:i_tbname' into v_cnt using i_tbname; -- 看是否存在小写名字的表
if v_cnt = 1 then
begin
v_sqls := 'select trim(regexp_replace(wm_concat((case when m.column_name<>upper(m.column_name) then '' "''||m.column_name||''"'' else '' ''||m.column_name end)),'','','',''||chr(10))) as columnNames
from user_tab_columns m
where m.table_name=:i_tbname
and m.column_name not in ('||i_except_columns2||')
order by column_id asc';
execute immediate v_sqls into v_sql using i_tbname; o_sql := 'SELECT '||v_sql||chr(10)||' FROM '||'"'||i_tbname||'"';
end;
else
o_sql := '对不起,表名为 '||i_tbname||'、'||upper(i_tbname)||' 在当前用户 '||user||' 下均不存在! ';
end if;
end;
end if;
end;
/-- select dbms_metadata.get_ddl('TABLE','MUSICMUSIC') from dual;set serveroutput on;
var sqls varchar2(4000);
exec sql_create('musicmusic','musicid',:sqls);
print sqls;
解决方案 »
- 刚转ORCLE 有几点疑问
- 关于重复数据的处理?
- 每小时就有近千万的数据,请高手给出个优化方案?
- oracle有没有像db2中Federated Database技术
- oracle中怎么进入管理员用户模式?
- 请教一个问题,可以使用Oracle Enterprise Manager查看oracle session和cursor吗?
- 关于数据抽象层的实现问题,请大家谈谈自己的意见
- oracle安装问题
- 有关ORACLE数据库环境不同的数据记录比较的问题
- tmep表空间
- 关于运算符or的顺序问题
- 在虚拟机Vmware的red hat linux as5.4下安装oracle10g进度停滞不前的问题~~~
-- 我想:如果你懒到GROUP BY、ORDER BY也懒得写的话,你可以不做DBA啦!