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;