//创建一个对象
create or replace type Structure_table as object
(
TableName varchar2(30),
ColumnNumber number,
ColumnName varchar2(30),
ColumnType varchar2(106),
ColumnLength number,
ColumnDecimals number,
IsUniqueKey varchar2(5),
IsPrimaryKey varchar2(5),
Memo varchar2(4000),
IsNull varchar2(1),
DefaultValue long
)
//修改表对象类型的定义
create or replace type Table_Structure as table of Structure_table
//创建一个函数(不知道这个函数有没有写错,俺是新手,写这个函数的目的就是通过传入一个表名称,输出表结构信息)
create or replace function F_TableStructure(tabName varchar2(30))
return Table_Structure pipelined as v Structure_table
begin
insert into v
select
(case when a.column_id=1 then a.table_name else '' end),
a.column_id,a.column_name,a.data_type,
decode(a.data_type,'NUMBER',a.data_precision,a.data_length),a.data_scale,
decode(e.uniqueness,'UNIQUE','Y','N'),decode(e.key,'Y','Y','N'),
f.comments,a.nullable,a.data_default
from user_tab_columns a, user_col_comments f,
(select b.table_name, b.index_name,b.uniqueness, c.column_name,
decode(d.constraint_name,NULL,'N','Y') key
from user_indexes b, user_ind_columns c,
( select constraint_name from user_constraints where constraint_type='P' ) d
where b.index_name=c.index_name and b.index_name=d.constraint_name(+) ) e
where a.table_name=tabName and a.table_name=e.table_name(+) and a.column_name=e.column_name(+)
and a.table_name=f.table_name and a.column_name=f.column_name
order by a.column_id
return;
end F_TableStructure我在pl/sql develpoer里面执行了,也没报错,
但是我用下面语句执行时:
select count(*) from all_objects where object_type='Function' and object_name='F_TableStructure'
找不到这个函数?
有点不解,求解达人帮忙看一下那里错了?
create or replace type Structure_table as object
(
TableName varchar2(30),
ColumnNumber number,
ColumnName varchar2(30),
ColumnType varchar2(106),
ColumnLength number,
ColumnDecimals number,
IsUniqueKey varchar2(5),
IsPrimaryKey varchar2(5),
Memo varchar2(4000),
IsNull varchar2(1),
DefaultValue long
)
//修改表对象类型的定义
create or replace type Table_Structure as table of Structure_table
//创建一个函数(不知道这个函数有没有写错,俺是新手,写这个函数的目的就是通过传入一个表名称,输出表结构信息)
create or replace function F_TableStructure(tabName varchar2(30))
return Table_Structure pipelined as v Structure_table
begin
insert into v
select
(case when a.column_id=1 then a.table_name else '' end),
a.column_id,a.column_name,a.data_type,
decode(a.data_type,'NUMBER',a.data_precision,a.data_length),a.data_scale,
decode(e.uniqueness,'UNIQUE','Y','N'),decode(e.key,'Y','Y','N'),
f.comments,a.nullable,a.data_default
from user_tab_columns a, user_col_comments f,
(select b.table_name, b.index_name,b.uniqueness, c.column_name,
decode(d.constraint_name,NULL,'N','Y') key
from user_indexes b, user_ind_columns c,
( select constraint_name from user_constraints where constraint_type='P' ) d
where b.index_name=c.index_name and b.index_name=d.constraint_name(+) ) e
where a.table_name=tabName and a.table_name=e.table_name(+) and a.column_name=e.column_name(+)
and a.table_name=f.table_name and a.column_name=f.column_name
order by a.column_id
return;
end F_TableStructure我在pl/sql develpoer里面执行了,也没报错,
但是我用下面语句执行时:
select count(*) from all_objects where object_type='Function' and object_name='F_TableStructure'
找不到这个函数?
有点不解,求解达人帮忙看一下那里错了?
Error: PLS-00103: 發現了符號 "(" 當您等待下列事項之一發生時:
:= . ) , @ %
default character
符號 ":=" 取代了 "(" 才可以繼續作業.
Line: 1
Text: create or replace function F_TableStructure(tabName varchar2(30))Error: PLS-00103: 發現了符號 "PIPELINED" 當您等待下列事項之一發生時:
. @ % ;
is authid deterministic parallel_enable as character
符號 "." 取代了 "PIPELINED" 才可以繼續作業.
Line: 2
Text: return Table_Structure pipelined as v Structure_tableError: PLS-00103: 發現了符號 "BEGIN" 當您等待下列事項之一發生時:
:= . ( @ % ;
not null range default character
符號 ";" 取代了 "BEGIN" 才可以繼續作業.
Line: 3
Text: beginError: PLS-00103: 發現了符號 "CASE" 當您等待下列事項之一發生時:
( - + mod not
null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>
Line: 6
Text: (case when a.column_id=1 then a.table_name else '' end),
select count(*) from all_objects where object_type='FUNCTION' and object_name='F_TABLESTRUCTURE'