谁能帮我写一个函数,能简单实现这个功能:取PK_USER_ROLE字段中字符串最大的一个值,然后把这个字符串加1,然后返回这个值,我SQLSERVER 可能实现.如下
CREATE function getPKroleid()
returns char(20)
as
begin
declare @PK_roleid char(20)
select @PK_roleid=max(PK_USER_ROLE) from PUB_USER_ROLEset @PK_roleid='PK'+right('000000000000000000'+convert(nvarchar(10),convert(bigint,right(@PK_roleid,18))+1),18)return(@PK_roleid)
end ORACLE函数怎么写啊...帮忙一下...
CREATE function getPKroleid()
returns char(20)
as
begin
declare @PK_roleid char(20)
select @PK_roleid=max(PK_USER_ROLE) from PUB_USER_ROLEset @PK_roleid='PK'+right('000000000000000000'+convert(nvarchar(10),convert(bigint,right(@PK_roleid,18))+1),18)return(@PK_roleid)
end ORACLE函数怎么写啊...帮忙一下...
PK000000000000000124
PK000000000000000125
v_Result VARCHAR2(20);
BEGIN
SELECT lpad(MAX(PK_USER_ROLE) + 1, 18, 0) INTO v_Result FROM PUB_USER_ROLE; RETURN v_Result;
END;
PK000000000000000129
PK00000000000000012A
PK00000000000000012B
....................
PK00000000000000012Z
PK000000000000000130
PK000000000000000131
--try it
create or replace function test(str1 in varchar2(100))
return varchar2
is str2 varchar2(100);
begin
select max(PK_USER_ROLE) into str2 from PUB_USER_ROLE;
str2:=substr(str2,1,2)||(to_number(substr(str2,3,18))+1);
return(str2);
end;
is
PK_roleid varchar2(20);
begin
select max(PK_USER_ROLE) into PK_roleid from PUB_USER_ROLE;
PK_roleid:='PK'||LPAD(SUBSTR(PK_ROLEID,3)+1,18,'0');
return PK_roleid;
end;
SQL> select * from t;
PK_USER_ROLE
--------------------------------------------------------------------------------
PK000000000000000123
PK000000000000000124
PK000000000000000125
SQL>
SQL> create or replace function etPKroleid return varchar2 as
2 result varchar2(100);
3 begin
4 select max(PK_USER_ROLE) into result from t;
5 result := 'PK' || lpad((substr(result, 3) + 1), 18, '0');
6 return result;
7 end;
8 /
Function created
SQL> select etPKroleid from dual;
ETPKROLEID
--------------------------------------------------------------------------------
PK000000000000000126
V_RESULT VARCHAR2(20);
BEGIN
SELECT MAX(PK_USER_ROLE) INTO V_RESULT FROM PUB_USER_ROLE;
SELECT SUBSTR(V_RESULT, 1, 17) ||
TO_CHAR(SUBSTR(V_RESULT, 18, 2) + (CASE
WHEN SUBSTR(V_RESULT, 20, 1) = 'Z' THEN
1
ELSE
0
END)) ||
(CASE
WHEN SUBSTR(V_RESULT, 20, 1) >= 'A' AND
SUBSTR(V_RESULT, 20, 1) < 'Z' THEN
TO_CHAR(CHR(ASCII(SUBSTR(V_RESULT, 20, 1)) + 1))
WHEN SUBSTR(V_RESULT, 20, 1) >= '0' AND
SUBSTR(V_RESULT, 20, 1) < '9' THEN
TO_CHAR(SUBSTR(V_RESULT, 20, 1) + 1)
WHEN SUBSTR(V_RESULT, 20, 1) = '9' THEN
'A'
WHEN SUBSTR(V_RESULT, 20, 1) = 'Z' THEN
'0'
ELSE
NULL
END)
INTO V_RESULT
FROM DUAL;
RETURN V_RESULT;
END;
v_temp varchar2(100);
i number;
begin
select max(PK_USER_ROLE) into str from t; str := substr(str, 3); v_temp := substr(str, 18, 1);
i := 0;
while (i < 18) and (v_temp = 'Z') loop
v_temp := substr(str, (17 - i), 1);
i := i + 1;
end loop;
if substr(str, (18 - i), 1) = '9' then
str := substr(str, 1, (17 - i)) || 'A' || lpad('0', i, '0');
else
str := substr(str, 1, (17 - i)) ||
chr(ascii(substr(str, (18 - i), 1)) + 1) || lpad('0', i, '0');
end if; return 'PK' || str;
end;
PK_USER_ROLE
--------------------------------------------------------------------------------
PK000000000000000129
PK00000000000000012A
PK00000000000000012B
PK00000000000000012Z
PK000000000000000141
PK00000000000000014Z
6 rows selected
SQL> select getPKroleid from dual;
GETPKROLEID
--------------------------------------------------------------------------------
PK000000000000000150
PK_USER_ROLE
--------------------------------------------------------------------------------
PK000000000000000121
PK000000000000000129
SQL> select getPKroleid from dual;
GETPKROLEID
--------------------------------------------------------------------------------
PK00000000000000012A
只要把我的函数中的t改成PUB_USER_ROLE 即可
--TRY IT
create or replace function TEST return varchar2 as str1 varchar2(100);
str2 varchar2(100);
str3 varchar2(100);
i number;
j number;
begin
select max(PK_USER_ROLE) into str1 from PUB_USER_ROLE;
j := 1; for i in 1 .. 18 loop
begin
str2 := substr(str1, -i, 1);
if ascii(str2) + J <= ASCII('Z') THEN
IF ASCII(STR2) + J <= ASCII(9) THEN
STR2 := STR2 ;
ELSE
begin
STR2 := 'A';
J := 0;
end;
END IF;
ELSE
begin
STR2 := '0';
J := 1;
end;
END IF;
STR3 := STR2 || STR3;
END;
END LOOP;
STR3 := 'PK' || str3;
RETURN(STR3);end;
楼主也可以试下我的~测试通过!