SQL817> create or replace function f_getmaxid( stbl in varchar2, sfld in varchar2 )
2 return varchar2
3 is
4 strid varchar2(50);
5 begin
6 select max(id) into strid from jbxx;
7 return(strid);
8 end f_getmaxid;
9 /函数已创建。SQL817> select f_getmaxid('a','b') from dual;F_GETMAXID('A','B')
--------------------------------------------------------------------------------
3你这个函数本身没问题,可能问题出在别的地方
2 return varchar2
3 is
4 strid varchar2(50);
5 begin
6 select max(id) into strid from jbxx;
7 return(strid);
8 end f_getmaxid;
9 /函数已创建。SQL817> select f_getmaxid('a','b') from dual;F_GETMAXID('A','B')
--------------------------------------------------------------------------------
3你这个函数本身没问题,可能问题出在别的地方
create or replace function f_getmaxid( stbl in varchar2, sfld in varchar2 )
return varchar2
is
strid number;
v_sql_string varchar2(500);
begin
v_sql_string := 'select max('||sfld||') into strid from stbl';
execute immediate v_sql_string into strid ;return strid ;
end f_getmaxid;
/
return varchar2
is
strid number;
v_sql_string varchar2(500);
begin
v_sql_string := 'select max('||sfld||') into strid from stbl';
execute immediate v_sql_string into strid ;return TO_CHAR(strid) ;
end f_getmaxid;
/select出来是个number,你赋给一个varchar2,存在强制转换
请注意,stbl是表,sfld是字段,字段是字符型的!
create or replace function f_getid(strtb in varchar2,strfld in varchar2)
return varchar2
is
strid varchar2;
v_sql_string varchar2(500);
begin
v_sql_string := 'select to_char(sysdate,''YYYYMMDD'')||ltrim(to_char(nvl(to_number(substr(max('||sfld||'),9,4)),0)+1,''0000'')) into strid from '||stbl||' where substr('||sfld||',1,8)=to_char(sysdate,''YYYYMMDD'')';
execute immediate v_sql_string into strid ;
return(strid);
end f_getid;
/
return varchar2
is
strid varchar2;
v_sql_string varchar2(500);
begin
v_sql_string := 'select to_char(sysdate,''YYYYMMDD'')||ltrim(to_char(nvl(to_number(substr(max('||sfld||'),9,4)),0)+1,''0000'')) into strid from '||stbl||' where substr('||sfld||',1,8)=to_char(sysdate,''YYYYMMDD'')';
return(v_sql_string);
end f_getid;
这样改一下,看看输出的语句是不是你的查询,有问题的话改改就好了
1 create or replace function f_getid(strtb in varchar2,strfld in varchar2)
2 return varchar2
3 is
4 strid varchar2;
5 v_sql_string varchar2(500);
6 begin
7 v_sql_string := 'select to_char(sysdate,''YYYYMMDD'')||ltrim(to_char(nvl(to_number(substr(max('||sfld||')
8 return(v_sql_string);
9* end f_getid;
SQL> /警告: 创建的函数带有编译错误。
怎么这句少了很多东西?
create or replace function f_getid(strtb in varchar2,strfld in varchar2)
return varchar2
is
strid varchar2;
v_sql_string varchar2(500);
begin
v_sql_string := 'select to_char(sysdate,''YYYYMMDD'')||ltrim(to_char(nvl(to_number(substr(max('||sfld||'),9,4)),0)+1,''0000'')) into strid from '||stbl||' where substr('||sfld||',1,8)=to_char(sysdate,''YYYYMMDD'')';
execute immediate v_sql_string into strid ;
return(strid);
end f_getid;
/
return varchar2
is
strid varchar2(100);
v_sql_string varchar2(500);
begin
v_sql_string := 'select to_char(sysdate,''YYYYMMDD'')||ltrim(to_char(nvl(to_number(substr(max('
strfld||
'),9,4)),0)+1,''0000'')) into strid from '||
strtb||
' where substr('||
strfld||
',1,8)=to_char(sysdate,''YYYYMMDD'')';
execute immediate v_sql_string into strid ;
return(strid);
end f_getid;
return varchar2
is
strid varchar2(100);
v_sql_string varchar2(500);
begin
v_sql_string := 'select to_char(sysdate,''YYYYMMDD'')||ltrim(to_char(nvl(to_number(substr(max('
strfld||
'),9,4)),0)+1,''0000'')) into strid from '||
strtb||
' where substr('||
strfld||
',1,8)=to_char(sysdate,''YYYYMMDD'')';
execute immediate v_sql_string;
return(strid);
end f_getid;
已写入 file afiedt.buf 1 create or replace function f_getid(strtbl in varchar2,strfld in varchar2)
2 return varchar2
3 is
4 strid varchar2(100);
5 v_sql_string varchar2(500);
6 begin
7 v_sql_string := 'select to_char(sysdate,''YYYYMMDD'')||ltrim(to_char(nvl(to_number(substr(max('
8 ||strfld||
9 '),9,4)),0)+1,''0000'')) into strid from '||
10 strtbl||
11 ' where substr('||
12 strfld||
13 ',1,8)=to_char(sysdate,''YYYYMMDD'')';
14 execute immediate v_sql_string into strid ;
15 return(strid);
16* end f_getid;
SQL> /函数已创建。SQL> commit;提交完成。SQL> select f_getid('t1','id') from dual;
select f_getid('t1','id') from dual
*
第 1 行出现错误:
ORA-00905: 缺少关键字
ORA-06512: 在"HCGS.F_GETID", line 14
SQL>
2 return varchar2
3 is
4 strid varchar2(100);
5 v_sql_string varchar2(500);
6 begin
7 v_sql_string := 'select to_char(sysdate,''YYYYMMDD'')||ltrim(to_char(nvl(to_number(substr(max('
8 ||strfld||
9 '),9,4)),0)+1,''0000'')) into strid from '||
10 strtbl||
11 ' where substr('||
12 strfld||
13 ',1,8)=to_char(sysdate,''YYYYMMDD'')';
14 execute immediate v_sql_string into strid ;
15 return strid; --将这里改成这样试试
16* end f_getid;
create or replace function f_getid1
return varchar2
is
strid varchar2(12);
v_sql varchar2(500);
begin
v_sql :='select max(id) into strid from t1';
execute immediate v_sql into strid;
return(strid);
end f_getid1;
/select f_getid1 from dual
*
第 1 行出现错误:
ORA-00905: 缺少关键字
ORA-06512: 在"HCGS.F_GETID1", line 8
这题不是那么简单,前面没考虑清,再想想
return varchar2
is
TYPE curmax IS REF CURSOR;
cur curmax;
strid varchar2(100);
v_sql_string varchar2(500);
begin
v_sql_string := 'select to_char(sysdate,''YYYYMMDD'')||ltrim(to_char(nvl(to_number(substr(max('
strfld||
'),9,4)),0)+1,''0000'')) from '||
strtb||
' where substr('||
strfld||
',1,8)=to_char(sysdate,''YYYYMMDD'')';
open cur for v_sql_string;
fetch cur into strid;
close cur;
return(strid);
end f_getid;
return varchar2
is
strid varchar2(100);
v_sql_string varchar2(500);
begin
v_sql_string := 'select to_char(sysdate,''YYYYMMDD'')||ltrim(to_char(nvl(to_number(substr(max('
strfld||
'),9,4)),0)+1,''0000'')) from '||
strtb||
' where substr('||
strfld||
',1,8)=to_char(sysdate,''YYYYMMDD'')';
execute immediate v_sql_string into strid ;
return(strid);
end f_getid;