不明白我建的这个oracle函数有什么错误，运行的时候提示程序包或函数get_tablecolumn_except处于无效状态

[hanvslin]

create or replace function get_tablecolumn_except(e_column in varchar2) return varchar2 as
r_column varchar2(200)
begin
select column_name into r_column from user_tab_columns where table_name=upper('student') and column_name=e_column;
return r_column;
exception
when no_data_found then
raise_application_error(-20001, '你输入的ID无效！');
end get_tablecolumn_except;


执行语句select get_tablecolumn_except('SNO') from dual

[真的不知道取什么名]

create or replace function get_tablecolumn_except(e_column in varchar2) return varchar2 as  
r_column varchar2(200) default 'SNO';-- 给个默认值看看
begin  
  select column_name into r_column from user_tab_columns where table_name=upper('student') and column_name=e_column;  
  return r_column;
  exception   
  when no_data_found then   
  raise_application_error(-20001, '你输入的ID无效！');  
  end get_tablecolumn_except;

[心中的彩虹]

[Quote=引用楼主 hanvslin 的回复:]
create or replace function get_tablecolumn_except(e_column in varchar2) return varchar2 as
r_column varchar2(200)
begin
select column_name into r_column from user_tab_columns where table_name=u……
[/Quote]
create or replace function get_tablecolumn_except(e_column in varchar2) return varchar2 as
r_column varchar2(200);
begin
select column_name into r_column from user_tab_columns where table_name=upper('student') and column_name=upper(e_column);
return r_column;
exception
when no_data_found then
raise_application_error(-20001, '你输入的ID无效！');
end get_tablecolumn_except;

[gelyon]

create or replace function get_tablecolumn_except(e_column in varchar2) return varchar2 as
r_column varchar2(200);--语句结束要加分号
begin
select column_name into r_column from user_tab_columns where table_name=upper('student') and column_name=e_column;
return r_column;
exception
when no_data_found then
raise_application_error(-20001, '你输入的ID无效！');
end get_tablecolumn_except;

[hanvslin]

[Quote=引用 1 楼 zhuomingwang 的回复:]

SQL code
create or replace function get_tablecolumn_except(e_column in varchar2) return varchar2 as
r_column varchar2(200) default 'SNO';-- 给个默认值看看
begin
select column_name into r_column from ……
[/Quote]高手，果然可以运行了，为什么要加默认值呢？

[minitoy]

SQL> create or replace function get_tablecolumn_except(e_column in varchar2) return varchar2 as
  2  r_column varchar2(200);
  3  begin
  4    select column_name into r_column from user_tab_columns where table_name=upper('emp') and column_name=e_column;
  5    return r_column;
  6    exception
  7    when no_data_found then
  8    raise_application_error(-20001, '你输入的ID无效！');
  9    end get_tablecolumn_except;
 10  /
 
Function created
 
SQL> select get_tablecolumn_except('EMPNO') from dual;
 
GET_TABLECOLUMN_EXCEPT('EMPNO'
--------------------------------------------------------------------------------
EMPNO
 
SQL> 

[hanvslin]

[Quote=引用 3 楼 gelyon 的回复:]

create or replace function get_tablecolumn_except(e_column in varchar2) return varchar2 as
r_column varchar2(200);--语句结束要加分号
begin
select column_name into r_column from user_tab_columns wher……
[/Quote]加了分号还是一样

[minitoy]

函数内部变量定义少了个分号

[hanvslin]

谢谢大家，经查证，确实是分号的问题........